Excel VBA: Answer gets "stuck"

Question

I'm new to VBA in Excel 2003. I've written a UDF that takes in two strings: one filter and one that will be a cell reference. When I am debugging and call the function a couple times in a row in the VBA editor, everything works as I expect. But when I use the UDF multiple times in a spreadsheet, all uses show the exact same answer, despite there being different input.

The function is below. It updates a pivot table and then copies the value out of a cell. But I must be misusing it:

Function UpdatePivotAndFetchCell(catcode As String, theCell As String) As Variant
Dim ws, pt, catField, pi, theval, finalVal
Set ws = Worksheets("Reporting")
Set pt = ws.PivotTables("MyReport")
pt.RefreshTable
Set catField = pt.PivotFields("Category")

For Each pi In catField.PivotItems
    If InStr(pi.Value, catcode) Then
        pt.PivotFields("Category").CurrentPage = pi.Value
        theval = ws.Range(theCell).Value
        On Error Resume Next
        If (TypeName(theval) <> "Error") Then
            finalVal = theval
        End If
        Exit For
    End If
Next pi
UpdatePivotAndFetchCell = finalVal
End Function

If I run it with the VBA editor, I get:

Debug.Print (UpdatePivotAndFetchCell("C001", "K284"))
    'Returns 0.48
Debug.Print (UpdatePivotAndFetchCell("C002", "K284"))
    'Returns 0.52

But within a worksheet:

=UpdatePivotAndFetchCell("C001", "K284")
    (displays 0.52)
=UpdatePivotAndFetchCell("C002", "K284")
    (displays 0.52)

Am I not specifying the function properly, maybe? It's a mystery to me.

Answer 1

Functions called from a worksheet are not allowed to change sheets. Otherwise you could easily create infinite loops in the calculation tree. So everything that changes the sheet is ignored (such as RefreshTable() or .CurrentPage =).