CODEWITHSUNDEEP
vbaexcel
DaveyD
DaveyD

Reputation: 379

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

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

Upvotes: 0

Views: 1458

Answers (3)

Comintern
Comintern

Reputation: 22185

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

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

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

Upvotes: 1

Mr. Mascaro
Mr. Mascaro

Reputation: 2733

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

Upvotes: 2

Related Questions