Application.Caller calls from ActiveWorkbook not the actual cell workbook

[Xander Harris]

New poster, so please ask simple questions if this makes no sense.

I've discovered Application.Caller as I want to call data from adjacent cells.

i.e. In A1 I want it to say Save if C1 says True

I have a long list of Select Case based on number ranges.

However, the Application.Caller brings up #Value if i move out of the sheet.

I corrected this with Application.Caller.Parent.Name to call the Tab.

Next, I found the same error when I move out of the file to a new workbook.

However, Application.Caller.Parent.Parent.Name calls the ActiveWorkbook.

Please can anyone help? If I'm going about this all wrong, any suggestions are welcome.

Examples:

Enter these in a new book:

Function TestA()
Application.Volatile
TestA = Application.Caller.Address
End Function
Function TestB()
Application.Volatile
TestB = Application.Caller.Parent.Name
End Function
Private Function TestC()
Application.Volatile
OffsetCell = Application.Caller.Column - 2
TestC = Cells(2, OffsetCell)
End Function

Rename Sheet1 to TestSheet
A2 = "Subject 1"
C2 = TestA() - Will show $C$2
C3 = TestB() - Will show TestSheet
C4 = TestC() - Will show Subject 1

Open a new book and activate any cell (type "Help Me" in A2 if you like)

View the first workbook, preferably do not activate, and you will see that C4 has changed because it is looking at the new active book.

 

[Xander Harris]

Ok so the answer is out there, I just didn't understand it.

I need to focus on the original cell, so =TestC(C4) for the example above

Function TestC(C as Range)
Application.Volatile
OffsetCell = C.Column - 2
TestC = Workbooks(C.Parent.Parent.Name).Sheets(Parent.Name).Cells(2, OffsetCell)
End Function

Obviously, this can be tidied up a bit.

So as you are focusing on the cell that the formula is in, this carries all the original information needed.