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 | トピック |
| 2番目のタスク | 0.05 | 0.15 | 参加者 |
示されているように、人物 1 はトピックごとに 1 時間、参加者ごとに 0.05 時間が必要です。人物 2 はトピックごとに 0.25 時間、参加者ごとに 0.15 時間が必要です。
トピックと参加者の数は、名前付き範囲 (つまり、「_topics」、「_participants」) に含まれています。「トピック」と「参加者」はプレースホルダー名です。それが何であるかは重要ではありません (「犬」や「猫」である可能性があります)。重要なのは、それぞれに 1 つの数値が割り当てられていることです。タスクは乗数を共有できます (つまり、複数の行で「トピック」を使用できます)。
目的は、非常に単純な入力（トピックの数、参加者の数などを変更する）を使用して、複雑な予算（多くのメンバーと多くのタスク）の要件をモデル化することです。
これが小さな表であれば、次のようなセル内数式を使用します。
=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)。
私の場合、行と乗数が多く、タスクをプロジェクト フェーズに割り当てる 2 番目の列もあるため、セル内のコンテンツを読みやすくし、将来的に項目を追加しやすくするための 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,[person1の範囲],[タスクフェーズの範囲],[タスク乗数の範囲]) | =SUM_MEMBER_VALUES($A2,[person2の範囲],[タスクフェーズの範囲],[タスク乗数の範囲]) | =SUM(B2:B3) |
この関数は動作します。問題は、名前付き変数の値を変更しても自動的に更新されないことです。そのため、_topic が 5 から 10 に変更されても、結果はSUM_MEMBER_VALUES変わりません。
計算オプションは自動 F9、SHIFT+F9 に設定されており、再計算するためのリボン ボタンでは更新がトリガーされません。ファイルはマクロが有効になっており、ファイルを閉じて再度開き、コンピューターを再起動しようとしました。
結果を更新できる唯一の方法は、 を呼び出す各セルに手動で入力してリターンを押すことですSUM_MEMBER_VALUES。これを無視するのは面倒ですし、セルの更新を忘れやすいので危険です (特に、私以外の誰かが意図したモデルを使用している場合)。
私が考えられるのは、関数によって値が渡される方法または参照される方法が原因で、値が変更されたことを認識していない可能性があるということです (つまり、値を範囲として渡したが、範囲の内容が変更されても範囲は変更されませんでした)。ただし、回避策は思いつきません。
質問：入力値が変更されると、VBA 関数が自動的に更新されるようにする方法はありますか?
アップデート
SHIFT+CTRL+ALT+F9依存関係ツリーを再構築し、完全な再計算を実行します。また、Application.Volatile関数の先頭に追加すると、ワークシートに変更が加えられるたびに再計算が強制されます。ただし、更新が不要な場合でも更新が強制されるため、一部の貢献者はこれに対して警告しています。1
これは私の当面のニーズを解決しますが、2 つのソリューションの間に解決策があるかどうか知りたいです (手動での再計算は必要なく、フォントが変更されるたびに再計算する必要もありません)。
答え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
もちろん、個々の変数の代わりに配列を使用することもできますが、この例は概念的なものです。
関数は引き続き実行されますが、少なくとも古い値と現在の値を比較するのに多くの処理時間はかかりません。