Excel VBA:答案被“卡住".

本文介绍了Excel VBA:答案被“卡住".的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是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.