0

I have a UDF in excel which technically works perfect. However, occasionally, when switching to a sheet that have cells which call that UDF, it returns #N/A. It happens consistently when I open the file and then switch to that sheet.
The reason why I say that it "works perfectly" is because when I go into that cell and press enter (i.e. manually run the function) it does return the correct value! As well as any time a change a value in one of the depending cells.
I've tried troubleshooting to find what's causing the problem, and I realized that my udf is not even getting called! (The debug.print statement that I have in there is not getting printed)

Here is my udf: 

Function AvgPrice(myWS As String, r As Range, bCol As Range, szCol As Range, ttlCol As Range)
    Application.Volatile

    Dim cell As Range, MinArb As Boolean
    Dim val As Variant, inc As Variant, thisRow As Long, iSum As Long, thisCol As Long, total As Long, price As Long
    Dim fabric As String, book As String, size As String, Lookup As String, fab As String
    Dim rLookUp As Range, cIndex As Variant

    Dim ws As Worksheet, prices As Worksheet
    Set ws = ThisWorkbook.Worksheets(myWS)
    Set prices = ThisWorkbook.Worksheets("Prices")

    Debug.Print (ws.Name)

    Set r = ws.Range(r.Address)
    Set bCol = ws.Range(bCol.Address)
    Set szCol = ws.Range(szCol.Address)
    Set ttlCol = ws.Range(ttlCol.Address)

    thisRow = r.Cells(1).row
    book = bCol.Value
    size = szCol.Value
    total = ttlCol.Value


    For Each cell In r.Cells
        inc = cell.Value
        If inc = vbNullString Then GoTo NXT
        If Not (IsNumeric(inc)) Then GoTo NXT

        thisCol = cell.Column
        fabric = getFabric2(cell, ws)

        Debug.Print (ws.Parent.Name)

        fabric = Mid(fabric, 1, 1) & LCase(Mid(fabric, 2))
        Lookup = book & "-" & size & "-" & fabric
        Set rLookUp = prices.Range("A:A")

        cIndex = Application.Match(Lookup, rLookUp, 0)
        If IsError(cIndex) Then
            Debug.Print ("1 -- Error: " & Lookup)
            Debug.Print (Err.Description)
            GoTo QuitIt
        End If
        val = prices.Cells(cIndex, 3).Value
        If val = vbNullString Or Not (IsNumeric(val)) Then
            val = prices.Cells(cIndex, 2).Value
            If val = vbNullString Or Not (IsNumeric(val)) Then GoTo NXT
        End If

        inc = inc * val
        iSum = iSum + inc
NXT:
    Next cell

QuitIt:
    If iSum > 0 Then
        AvgPrice = iSum / total
    Else
        AvgPrice = ""
    End If

End Function
Community
  • 1
  • 1
DaveyD
  • 337
  • 1
  • 5
  • 15
  • how bout putting an `SheetActivate` event handler to fire the `application.calculate` for the target sheet? – Rosetta Nov 03 '16 at 16:15
  • @Rosetta, sorry for late response... That won't help because even doing a manual "Calculate Now" on the sheet does not help. I have to go into the cell and hit enter, or copy the formula over into all the cells... Really weird - any other ideas? – DaveyD Nov 06 '16 at 16:37
  • try force calculation `Application.CalculateFull`. – Rosetta Nov 07 '16 at 06:06

1 Answers1

0

I had the same problem and solved by passing an additional parameter to the UDF function. Yes, the parameter is the Sheet Name in which UDF is being called.

Nimal
  • 53
  • 1
  • 8
  • 1
    It would be nice if you could add a one line of code to show your solution, based on the code in the question – Dinkheller Aug 20 '20 at 08:27