Application.Caller gives Error 2023 in Excel 97

[Rajesh Honnawarkar]

Hi,
I am having trouble calling the Application.Caller function in a vba
function in Excel 97. Basically in my function, I want to know how the
function was called, whether from a cell in spreadsheet or from another vba
function...

Thus I call Application.Caller in my function..which gives Error 2023..Am I
doing anything incorrect?

Thanks in advance

Rajesh Honnawarkar

[Chip Pearson]

Rajesh,

Try something like

Dim R As Range
On Error Resume Next
Set R = Application.Caller
If R Is Nothing Then
Debug.Print "Called By VBA"
Else
Debug.Print "Called By Worksheet"
End If

When a function is called by another VBA function, it isn't a range, and
therefore the Set statement will fail.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Rajesh Honnawarkar" <raj...@synygy.com> wrote in message
news:uCpp4jZTAHA.247@cppssbbsa03...

[john_g...@my-deja.com]

Chip,

In Excel 97...

I'm having the same problem as Rajesh; in my case I am calling
Worksheet.Calculate from within VBA which call forces the execution
of a cell containing the function

=Hyperlink(myFunction1(), myFunction2())

myFunction1, myFunction2 are volatile functions.

myFunction2 needs to know where it is called from in order to return an
appropriate "friendly message" for the link but for reasons I can't
determine application.caller is returning Error 2023 rather than the
range containing this formula.

I'm about to add a parameter to myFunction1,myFunction2 that lets me
pass a range (say A1) which will allow me to get the sheet name from
the passed in range rather than from application.caller (hope this
works) but what I really want is just Application.Caller to return the
range, not this error.

John

P.S. I appreciate your many valuable contributions to this forum.

In article <#6lsprZTAHA.277@cppssbbsa05>,

Sent via Deja.com http://www.deja.com/
Before you buy.

[Trent Kaiser]

Thanks a lot Chip.
I was having the same problem as the others in Excel2000 (and previously in
Excel97), because I was just using the Caller property directly, e.g.
MyRowCount = Application.Caller.Rows.Count.
What confused me while debugging was finding out that
typename(application.caller)="double". I thought it was a bug and had almost
given up. Excel Help perpetuates this error with their example:
Select Case TypeName(Application.Caller)

Looking at your post I noticed you were setting a Range variable to
application.caller and working with that variable, so I gave it a try and it
worked. I suspect mine is a fairly common error, so I thought it worth
posting a thanks along with emphasizing the important lines of your post:

Dim R as Range
Set R = Application.Caller
(now R has all the information the programming needs for the destination
of his result)

Cheers,
Trent

"Chip Pearson" <ch...@cpearson.com> wrote in message
news:#6lsprZTAHA.277@cppssbbsa05...