How to ID calling worksheet if it isn't the active sheet?

[cate]

I have a udf that uses .ActiveSheet to identify the sheet calling it.
Works great as long as the sheet doing the calculating is the active
one.

This sheet with that udf has now been replicated - various what if
version copies. Each of these copies comes along with its own copy of
that udf in its cells. Question is, how does the udf ID the calling
sheet if it's not the currently active one?

There are global controls that change input values used by these
copied sheets. All must recalculate using this global value and their
own local variables.

 

[Chip Pearson]

Application.Caller will be a Range object pointing to the cell from
which the UDF as called. E.g.,

Function Test() As String
Test = Application.Caller.Address
End Test

If, though, the function is called by other VBA code,
Application.Caller will not be a Range object. If the function is to
be called both from a worksheet cell and by other code, you should
test Application.Caller. E.g,

Function Test() As String
If IsObject(Application.Caller) Thne
If TypeOf Application.Caller Is Range Then
' called from a cell
End If
End If
End Function



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tim Williams]

To extend Chip's reply - here's a UDF which just returns the name of
the sheet it's on.

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

Tim