How to obtain the name of the workbook & sheet that called a UDF?

[JenniferMurphy]

In a UDF I am able to determine the address of the calling cell using

Application.Caller.Address

Is there something similar that I can use to determine the name of the sheet and workbook?

Thanks

 

[MickG]

Try :-

Function ws()
ws = Application.Caller.Address(external:=True)
End Function

see "Address" in VB help.

 

[DanteAmor]

Try:

wSheet = Application.Caller.Parent.Name
wBook = Application.Caller.Worksheet.Parent.Name

 

[Fluff]

Or, variations on Dante's code

Debug.Print "Sheet", Application.Caller.Worksheet.Name
Debug.Print "Workbook", Application.Caller.Parent.Parent.Name

 

[JenniferMurphy]

Try:

wSheet = Application.Caller.Parent.Name
wBook = Application.Caller.Worksheet.Parent.Name

That works. Thanks

 

[JenniferMurphy]

Or, variations on Dante's code

Debug.Print "Sheet", Application.Caller.Worksheet.Name
Debug.Print "Workbook", Application.Caller.Parent.Parent.Name

That works, too. Thanks.

 

[JenniferMurphy]

Try :-

Function ws()
ws = Application.Caller.Address(external:=True)
End Function

see "Address" in VB help.

I'll have to study that one as I don't understand it. Thanks

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.