☰

對 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 位元」。

我有一張用於準備預算的各種值的表格。每列是一個團隊成員，每一行都是一個任務。每個使用者都被指派了執行每項任務的時間。每個任務都按名稱分配一個乘數。

舉個簡單的例子：

任務[單元格A1] 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，則 Person 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

該函數在表格中的單元格內調用，如下所示：

相[細胞A1] 1人 2人 全部的
開球 =SUM_MEMBER_VALUES($A2,[人員1的範圍],[任務階段範圍],[任務乘數範圍]) =SUM_MEMBER_VALUES($A2,[人員2的範圍],[任務階段範圍],[任務乘數範圍]) =總和(B2:B3)

這個功能有效。我遇到的問題是，當我更改命名變數的值時，它不會自動更新。所以當_topic從5變成10時，結果SUM_MEMBER_VALUES不會改變。

計算選項設定為自動 F9、SHIFT+F9，重新計算的功能區按鈕不會觸發更新 該檔案已啟用宏，我已嘗試關閉/重新開啟該檔案並重新啟動電腦。

我能夠更新結果的唯一方法是手動輸入每個調用的單元格並按下回車鍵SUM_MEMBER_VALUES。忽略這一點很麻煩，也很危險，因為很容易錯過更新單元格（特別是如果我以外的人使用意圖的模型）。

我能想到的是，它可能無法識別由於值如何傳遞給函數或被函數引用而改變的值（即，我將值作為範圍傳遞，並且範圍沒有改變，即使Range 中的內容確實如此）。但我想不出解決方法。

問題：有沒有辦法確保當輸入值改變時，VBA 函數會自動更新？

更新 SHIFT+CTRL+ALT+F9重建依賴樹並進行完整的重新計算。此外，添加到Application.Volatile函數頂部應該在每次對工作表進行更改時強制重新計算；然而，一些貢獻者對此提出警告，因為即使不需要更新，它也會強制更新。1

這解決了我的直接需求，但我很好奇是否有人在這兩個解決方案之間有解決方案（不需要手動重新計算，也不需要每次字體更改時重新計算）。

答案1

一個可能的折衷方案是將靜態變數添加到包含表中“舊”值的函數中，並在沒有變化的情況下跳過其餘程式碼...清楚如泥？

假設 ListObject 名稱為 Tbl1，ListColumn 名稱為列標題名稱（任務、人員 1、人員 2、乘數）。

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

當然，您可以使用數組而不是單一變量，但此範例是概念性的。

該函數仍然會觸發，但至少您不會花太多處理時間來比較舊值與當前值。

相關內容