Excel - User defined function getting is being called even when not active

Question

I have a user defined function in excel. The function contains Application.Volatile at the top and it works great.

The problem I am experiencing now is that when I have the workbook open (lets call it workbook 1) together with another workbook (call it workbook 2), every time I make a change to workbook 2, all cells in workbook 1 that call this UDF gets a #VALUE! error. Why is this happening?

I hope I provided enough info. If not please let me know. Thanks David

Hi guys, thanks for the help. Sorry about that... here is the code:

Function getTotalReceived(valCell As Range) As Variant
    Application.Volatile

    If ActiveWorkbook.Name <> "SALES.xlsm" Then Return
    Dim receivedWs As Worksheet, reportWs As Worksheet
    Dim items As Range
    Set reportWs = Worksheets("Report")
    Set receivedWs = Worksheets("Received")

    Dim myItem As String, index As Long
    myItem = valCell.Value
    Set items = receivedWs.Range("A:A")
    index = Application.Match(myItem, items, 0)
    If IsError(index) Then
        Debug.Print ("Error: " & myItem)
        Debug.Print (Err.Description)
        GoTo QuitIt
    End If
    Dim lCol As Long, Qty As Double, mySumRange As Range
    Set mySumRange = receivedWs.Range(index & ":" & index)
    Qty = WorksheetFunction.Sum(mySumRange)
QuitIt:
    getTotalReceived = Qty
End Function

You will need to show your UDF code. If your UDF fails when the containing workbook isn't the ActiveWorkbook then that possibly points to a coding issue in how your UDF addresses ranges. — Tim Williams
– Tim Williams, Commented May 12, 2015 at 19:39
In a UDF you cannot use normal addressing. You need to use the Application.Caller object for all Excel references. — Mr. Mascaro
– Mr. Mascaro, Commented May 12, 2015 at 19:46

Mr. Mascaro · Accepted Answer · 2015-05-12 20:09:32Z

2

Your problem is with the use of ActiveWorkbook,ActiveWorksheet or ActiveCell or other Active_____ objects in your UDF. Notice that Application.Volitile is an application-level property. Anytime you switch sheets, books, cells, charts, etc. the corresponding "active" object changes.

As an example of proper UDF coding practice I put together this short example:

Function appCallerTest() As String
    Dim callerWorkbook As Workbook
    Dim callerWorksheet As Worksheet
    Dim callerRange As Range

    Application.Volatile True

    Set callerRange = Application.Caller
    Set callerWorksheet = callerRange.Worksheet
    Set callerWorkbook = callerWorksheet.Parent

    appCallerTest = "This formula is in cell: " & callerRange.Address(False, False) & _
                    " in the sheet: " & callerWorksheet.Name & _
                    " in the workbook: " & callerWorkbook.Name
End Function

answered May 12, 2015 at 20:09

Mr. Mascaro

2,7431 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DaveyD Over a year ago

Thanks for the example. I never knew about these "caller" options. However, I still can't get it to work properly. As of now, the cell was calling this function while passing the actual cell A and row number. E.g., getTotalReceived($A2). I changed it to getTotalReceived("A") and then I changed the function to myItem = callerSheet.Range(valCell & callerRange.row).Value. But I am still getting the same behavior.

Comintern Over a year ago

Application.Caller creates buggy behaviour (sometimes complains of a circular reference) if you use it in a function that already has a Range as a parameter. Resolving the Workbook from the Range is always safer.

Mr. Mascaro Over a year ago

@Comintern, I've been using it for more than a decade and never gotten any buggy behavior. User error?

Tim Williams · Accepted Answer · 2015-05-13 05:07:57Z

1

Function getTotalReceived(valCell As Range) As Variant
    Application.Volatile

    Dim index, v, Qty

    v = valCell.Value

    'do you really need this here?
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then Exit Function

    If Len(v) > 0 Then

        index = Application.Match(v, _
              ThisWorkbook.Sheets("Report").Range("A:A"), 0)

        If Not IsError(index) Then
            Qty = Application.Sum(ThisWorkbook.Sheets("Received").Rows(index))
        Else
            Qty = "no match"
        End If
    Else
        Qty = ""
    End If

    getTotalReceived = Qty
End Function

answered May 13, 2015 at 5:07

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Comments

Comintern · Accepted Answer · 2015-05-13 12:16:32Z

1

You actually have 2 errors in your function. The first was partially addressed by Mr. Mascaro - you need to use the Range reference that was passed to the function to resolve the Workbook that it is from. You can do this by drilling down through the Parent properties.

The second issue is that you are testing to see if Application.Match returned a valid index with the IsError function. This isn't doing what you think it's doing - IsError checks to see if another cell's function returned an error, not the previous line. In fact, if Application.Match raises an error, it is in your function so you have to handle it. I believe the error you need to trap is a type mismatch (error 13).

This should resolve both issues:

Function getTotalReceived(valCell As Range) As Variant
    Application.Volatile

    Dim book As Workbook
    Set book = valCell.Parent.Parent

    If book.Name <> "SALES.xlsm" Then Exit Function

    Dim receivedWs As Worksheet, reportWs As Worksheet
    Dim items As Range
    Set reportWs = book.Worksheets("Report")
    Set receivedWs = book.Worksheets("Received")

    Dim myItem As String, index As Long
    myItem = valCell.Value

    Set items = receivedWs.Range("A:A")

    On Error Resume Next
    index = Application.Match(myItem, items, 0)
    If Err.Number = 13 Then GoTo QuitIt
    On Error GoTo 0

    Dim lCol As Long, Qty As Double, mySumRange As Range
    Set mySumRange = receivedWs.Range(index & ":" & index)
    Qty = WorksheetFunction.Sum(mySumRange)
QuitIt:
    getTotalReceived = Qty
End Function

edited May 13, 2015 at 12:16

answered May 12, 2015 at 23:36

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

2 Comments

DaveyD Over a year ago

THANKS! Works perfectly! - - - Regarding Error #1, I see what you mean by using parent. It seems that the error was being caused by the lines of the lookup and not in the actual processing of the received cell as the parameter - true? - - - Regarding Error #2, Correct, I was looking for a mismatch and your code handles it great - Thanks! And I understand my mistake - thanks for explaning

Tim Williams Over a year ago

The second error you describe in the poster's code would have been fine if they'd defined index as Variant instead of Long. When you don't use WorksheetFunction in the call, Application.Match will return an error value if it doesn't make a match, which you can test for using IsError(), thus avoiding the need for On Error resume Next Like the poster you're also using Return, which is not a way to exit a function in VBA.

Collectives™ on Stack Overflow

Excel - User defined function getting is being called even when not active

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related