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屋！