How to set name of a excel cell using VBA

Question

hi,

I need to set a unique name for the cell which calls particular user defined function. so I tried to set a name within the function as follows

Dim r As Range
set r = Application.Caller

r.Name = "Unique"

But this is not working. Please help

Thank You

Answer 1

The following code sets cell A1 to have the name 'MyUniqueName':

Private Sub NameCell()

Dim rng As Range
Set rng = Range("A1")
rng.Name = "MyUniqueName"

End Sub

Does that help?

EDIT

I am not sure how to achieve what you need in a simple way, elegant way. I did manage this hack - see if this helps but you'd most likely want to augment my solution.

Suppose I have the following user defined function in VBA that I reference in a worksheet:

Public Function MyCustomCalc(Input1 As Integer, Input2 As Integer, Input3 As Integer) As Integer

MyCustomCalc = (Input1 + Input2) - Input3

End Function

Each time I call this function I want the cell that called that function to be assigned a name. To achieve this, if you go to 'ThisWorkbook' in your VBA project and select the 'SheetChange' event then you can add the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Left$(Target.Formula, 13) = "=MyCustomCalc" Then
    Target.Name = "MyUniqueName"
End If
End Sub

In short, this code checks to see if the calling range is using the user defined function and then assigns the range a name (MyUniqueName) in this instance.

As I say, the above isn't great but it may give you a start. I couldn't find a way to embed code into the user defined function and set the range name directly e.g. using Application.Caller.Address or Application.Caller.Cells(1,1) etc. I am certain there is a way but I'm afraid I am a shade rusty on VBA...

Answer 2

Hi,

Thanks for reply. What I need to do is that I have a custom FUNCTION,and once this function is called, I need to set a cell name for calling cell. please tell me how to achieve this with my function

Thanks

EDIT

Thanks, This is a useful hack. Actually I'm storing the addresses of the calling cells, but these addresses become invalid once user delete/insert row/column, However, by keeping name of the calling cell unique, we can refer to same cell even a row/column delete/insert. Is there any better way ?