☰

单元格内调用 Excel VBA 函数未更新

tag-icon • tag-icon microsoft-excel worksheet-function vba
单元格内调用 Excel VBA 函数未更新

我在使用从 Excel 单元格调用的 VBA 函数时遇到困难，无法自动更新或使用 F9、Shift+F9 等手动更新。

我在 Windows 10 上使用“Microsoft® Excel® for Microsoft 365 MSO（版本 2207 内部版本 16.0.15427.20166）32 位”。

我有一张用于编制预算的各种值的表格。每列代表一名团队成员，每行代表一项任务。每个用户都被分配了每项任务的小时数。每个任务都按名称分配了一个乘数。

举一个简单的例子：

任务 [cellA1] 人 1 人 2 乘数
第一个任务 1.00 0.25 话题
第二项任务 0.05 0.15 參與者

如图所示，人员 1 每个主题需要 1 小时，每个参与者需要 0.05 小时。人员 2 每个主题需要 0.25 小时，每个参与者需要 0.15 小时。

主题和参与者的数量包含在命名范围中（即“_topics”、“_participants”）。“主题”和“参与者”是占位符名称。它们是什么并不重要（它们可能是“狗”或“猫”）。重要的是每个都有一个分配给它的数值。任务可以共享乘数（即，多行可以使用“主题”）。

目标是通过非常简单的输入（改变主题数量、参与者数量等）来模拟复杂预算（许多成员和许多任务）的要求。

如果这是一张小表格，我会使用如下单元格内公式：

=sumif(D1:D2,"Topic",B1:B2) * _topic + sumif(D1:D2,"Participant",B1:B2) * _participants

如果 _topic = 5 且 _participant = 10，则第 1 个人的总分将是 5.5 (1 * 5 + 0.05 * 10)。

在我的例子中，有许多行和许多乘数，还有第二列将任务分配给项目阶段，因此我编写了一个 VBA 函数来保持单元格内容清晰易读，以便将来更容易添加项目。虽然不是很优雅，但它满足了我的需求。

Function SUM_MEMBER_VALUES(phase As String, data_range As Range, phase_range As Range, mult_range As Range)

p = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Project")
mc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_collections") * _
    Range("_markets_collections").Value
mr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Market_calls") * _
    Range("_markets_calls").Value
c = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Caller") * _
    Range("_callers").Value
b = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand") * _
    Range("_brands").Value
bmc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_collections") * _
    Range("_brand_markets_collections").Value
bmr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market_calls") * _
    Range("_brand_markets_calls").Value
bmpc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_collections") * _
    Range("_brand_market_products_collections").Value
bmpr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Market-Product_calls") * _
    Range("_brand_market_products_calls").Value
bp = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Product") * _
    Range("_brand_products").Value
uc = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Unsuccessful Calls") * _
    Range("_calls_unsuccessful").Value
br = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Brand-Responses") * _
    Range("_brand_responses").Value
pr = WorksheetFunction.SumIfs(data_range, phase_range, phase, mult_range, "Product-Responses") * _
    Range("_product_responses").Value

SUM_MEMBER_VALUES = p + mc + mr + c + b + bmc + bmr + bmpc + bmpr + bp + uc + br + pr

End Function

该函数在表格中的单元格中调用，如下所示：

阶段 [cellA1] 人 1 人 2 全部的
开球 =SUM_MEMBER_VALUES($A2,[person1 的范围],[任务阶段范围],[任务乘数范围]) =SUM_MEMBER_VALUES($A2,[person2 的范围],[任务阶段范围],[任务乘数范围]) =SUM(B2:B3)

此函数有效。我遇到的问题是，当我更改命名变量的值时，它不会自动更新。因此，当 _topic 从 5 更改为 10 时，结果SUM_MEMBER_VALUES不会改变。

计算选项设置为自动 F9、SHIFT+F9，并且重新计算的功能区按钮不会触发更新该文件已启用宏，我已尝试关闭/重新打开该文件并重新启动计算机。

我能够更新结果的唯一方法是手动输入并按回车键来更新每个调用的单元格SUM_MEMBER_VALUES。忽略这一点很麻烦，也很危险，因为很容易错过更新单元格（尤其是当其他人使用本意是该模型时）。

我所能想到的就是，它可能无法识别值的变化，因为值是如何传递给函数或被函数引用的（即，我将值作为 Range 传递，并且 Range 没有变化，即使 Range 中的内容发生了变化）。不过我想不出解决办法。

问题：有没有办法确保随着输入值的变化，VBA 函数会自动更新？

更新 SHIFT+CTRL+ALT+F9重建依赖关系树并进行全面重新计算。此外，Application.Volatile每次对工作表进行更改时，添加到函数顶部应该会强制重新计算；但是一些贡献者警告不要这样做，因为它会强制更新，即使不需要更新。1

这解决了我迫切的需求，但我很好奇是否有人有解决方案（不需要手动重新计算，也不需要在每次字体更改时重新计算）。

答案1

一个可能的折衷方案是向包含表中“旧”值的函数添加静态变量，如果没有变化则跳过其余代码……清楚吗？

假设 ListObject 名称为 Tbl1，ListColumn 名称是列标题名称 (Task、Person 1、Person 2、Multiplier)。

Static sTaskOld$, sP1Old$, sP2Old$, sMultOld$
Dim sTask$, sP1$, sP2$, sMult$

Application.Volatile
sTask = Join(Application.Transpose([Tbl1[Task]]),"")
sP1 = Join(Application.Transpose([Tbl1[Person 1]]),"")
sP2 = Join(Application.Transpose([Tbl1[Person 2]]),"")
sMult = Join(Application.Transpose([Tbl1[Multiplier]]),"")

'first recalc, static variables will be null
if sTask<>sTaskOld or sP1<>sP1Old or sP2<>sP2Old or sMult<>sMultOld then
...
end if

'save existing values to static variables
sTaskOld = sTask
sP1Old = sP1
sP2Old = sP2
sMultOld = sMult

当然，您可以使用数组而不是单个变量，但这个例子只是概念性的。

该函数仍会触发，但至少您不会花费太多的处理时间来比较旧值和当前值。

相关内容