Possible to tell which workbook called a function in an Excel Add-In (xla)

Question

I want to write a little logging function in an excel add-in that I will be calling from many different workbooks. I'd like to be able to just call it by passing only the log text, and the log function itself could handle the timestamp, workbookname, etc.

However, I cannot use either ThisWorkbook or ActiveWorkbook to determine which workbook was responsible for making the call, as Thisworkbook will return a reference to the add-in itself, whereas VBA code running in a workbook other than the workbook with active focus in Excel could make the call, but the ActiveWorkbook will return the one that has focus in the window.

Application.Caller looked like a possible solution, but this seems to work only when the function is called from a cell, not from VBA.

Is what I'm trying to do impossible?

Update

According to > 1 person, this is in fact impossible. If anyone happens to know some clever workaround please speak up.

Answer 1

Ok, so having read the question properly I'll try again...

So, to state the problem:

you want a routine written in an addin, that when called from vba in another workbook can work out (among other things) which workbook contains the vba that made the call, without having to pass this information explicitly.

As stated this is not possible (this is a similar question to accessing the call stack from code: something that is to my knowledge not possible)

However you can almost get what you want like this

Declare your log function like this:

Sub MyLogger(wb as Workbook, LogText as String)
    Dim CallerName as String
    CallerName = wb.name
    ' your code...
End Sub

Then wherever you call the sub use

MyLogger ThisWorkbook, "Log Text"

Not quite as good as passing nothing, but at least its always the same