Excel VBA determine calling cell 3

[StuckInTheMiddle]

Hi All,

I have a User Defined Function setup in VBA and would like to know how I can determine the cell/sheet that called my function.

The function is nonvolatile and is used in several places throughout my workbook and works great (well works good anyway), but I'd like to be able to determine which cell and sheet at any one time I'm calculating for.

Also, is it possible to 'Cancel' and exit the function without returning a new value to the worksheet?

A,

"If you can stay calm, while all around you is chaos...then you probably haven't completely understood the seriousness of the situation.

 

[CautionMP]

StuckInTheMiddle,
[tt]Application.Caller[/tt]

The only way I know to cancel would be to throw up a message box asking the user if they want to cancel.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

Have a look at the Application.Caller property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[StuckInTheMiddle]

Thank you, you guys rock!

Application.Caller.Address was just what I needed for the first part of my question.

CautionMP, what I want to do is not really prompt the user to cancel, but rather now that I know the caller address, exit the funtion if the address is not in a specified range without having the UDF return a ZERO to XL for that call.

I'm thinking much like the Workbook_BeforeClose event which has a cancel property you can set to true which prevents the XL from closing the book. I'd like to be able to say cancel now and do not update the value for the callee.

A,

"If you can stay calm, while all around you is chaos...then you probably haven't completely understood the seriousness of the situation.

 

[firefytr]

Hi there,

You can do that, but you should check for the sheet as well and workbook for that matter, but in the end, it's your UDF and you know the entire scope of it...

Function MyUDF() As Variant
    If Application.Caller.Address = "$A$1" Then
        MyUDF = 0
        Exit Function
    End If
    MyUDF = 1 'or whatever
End Function

For checking the sheet, it's the Parent of the Caller that you want to use..

    If Application.Caller.Parent.Name = "Sheet1" Then
        'Sheet1
    ElseIf Application.Caller.Parent.Name = "Sheet2" Then
        'Sheet2
    ElseIf Application.Caller.Parent.Name = "Sheet3" Then
        'Sheet3
    End If

And the workbook of the Caller is the Parent.Parent..

    If Application.Caller.Parent.Parent.Name = "Book1.xls" Then
        'Book1
    ElseIf Application.Caller.Parent.Parent.Name = "Book2.xls" Then
        'Book2
    ElseIf Application.Caller.Parent.Parent.Name = "Book3.xls" Then
        'Book3
    End If

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[StuckInTheMiddle]

Thanks firefytr, very useful info.

"If you can stay calm, while all around you is chaos...then you probably haven't completely understood the seriousness of the situation.