Excel VBA:答案“卡住" [英] Excel VBA: Answer gets "stuck"

问题描述

我是 Excel 2003 中 VBA 的新手.我编写了一个 UDF，它接受两个字符串:一个过滤器和一个将作为单元格引用的字符串.当我在 VBA 编辑器中调试并连续调用该函数几次时，一切都按我的预期工作.但是当我在电子表格中多次使用 UDF 时，尽管输入不同，但所有使用都显示完全相同的答案.

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

如果我用 VBA 编辑器运行它，我得到:

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

但在工作表中:

=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.

推荐答案

从工作表调用的函数是 不允许更改工作表.否则，您可以轻松地在计算树中创建无限循环.因此，更改工作表的所有内容都将被忽略(例如 RefreshTable() 或 .CurrentPage =)并导致函数停止.

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 =) and causes the function to stop.

为了完整起见，有一个解决方法，但您不应该使用它，如果这样做，您可能遇到问题.

For completeness sake, there is a workaround, but you should not use it, and you may run into problems if you do.

这篇关于Excel VBA:答案“卡住"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！