CODEWITHSUNDEEP
excelvbauser-defined-functions
mbmast
mbmast

Reputation: 1100

Excel VBA UDF: Application.ThisCell vs. Application.Caller. Which to use when a UDF needs to know from which cell it was invoked?

There are many posts here on Stackoverflow and its related sites that center around the question of how a UDF can get ahold of the cell from which the UDF was called.

Many of the accepted answers involve the use of the Range object returned by Application.ThisCell. Several accepted answers include language like "you can use either Application.ThisCell or Application.Caller."

However, the documentation for Application.ThisCell (Application.ThisCell property (Excel)) contains this remark:

Users should not access properties or methods on the Range object when inside the user-defined function.

What does this mean? What is the point of providing a property that returns the cell from which the UDF is called if you're not supposed to use that property within the UDF? Can anyone shed more light on this remark? And... should I be using Application.Caller while actively avoiding using Application.ThisCell?

Upvotes: 3

Views: 804

Answers (1)

Gardoglee
Gardoglee

Reputation: 71

One difference between Application.Caller and Application.ThisCell that I encountered is an odd situation. I have a very simple UDF to return sheet name for the worksheet from which the function is invoked.

Function GetSheetName()
    GetSheetName = Application.Caller.Parent.Name
End Function

I was using Application.Caller.Parent to reference the worksheet from which the function was called. When I typed the UDF reference

=GetSheetName()

into the worksheet cell, everything worked fine. However, when I tried to do a change all on the worksheet to replace one literal elsewhere in the formulas with another literal, the UDF with Application.Caller.Parent would fail, saying that Application.Caller.Parent was an invalid property, not defined. I replaced Application.Caller.Parent with Application.ThisCell.Parent,

Function GetSheetName()
    GetSheetName = Application.ThisCell.Parent.Name
End Function

and the problem was cured, even when using the Change All dialog.

I have no idea of the internal details of why the difference, just that there is apparently some difference in how they do what they do.

So, my advice is if one does not work, try the other, it might work.

Just my experience, your mileage may vary.

Upvotes: 0

Related Questions