EXCEL+VBA：シート全体が再計算されてしまうのを防ぐには？

質問

簡単な例で状況を説明します。
------------------------------
[A1]山田[B1]フルネーム　
[A2]太郎[B2]=MyFunction(A2,A1)
[A3]花子[B3]=MyFunction(A3,A1)
------------------------------
といったシートと
Public Function MyFunction(str1 As String, str2 As String) As String
MyFunction = str1 & str2
End Function

といったモジュールで[B2]、[B3]に姓＋名のフルネームが入る様なシートを作成したところ、
1.[A1]の値を変更した場合
2.[A2]または[A3]のいずれかの値を変更した場合
1の場合は期待通りの処理となるのですが、2の場合でもすべて2回ずつ再計算が行われてしまいました。

この例では2行なので特に…という気もしますが、これが何十行、何百行となった場合、ワークシート全体に存在する行の分だけ、再計算が行われることになり、処理が重くなってしまうと思います。

変更のあった行だけ再計算をおこなう方法はありますでしょうか？

回答No.3

noname#79209

実際のVBAはもっと複雑なのでしょうが、
もし、ユーザ定義関数が質問文通りで、単に「姓」と「名前」を逆にして連結するだけのものなら、（太郎山田など）
ユーザー定義関数など使わなくても、
B2には、
=A2&A$1
B3には
=A3&A$1
と、すれば良いだけだとおもいますが．．．
また、もし「間に『,』や『 』（スペース）を入れたい」のなら、
=A2&","&A$1
とすれば出来ますが、でも、違うんでしょうね。

回答No.2

Wendy02

こんばんは。

実際に、

>これが何十行、何百行となった場合

VBAのユーザー定義関数で、そのような場面になることはありえません。もし、そうなるなら、プロシージャで作成します。

通常、ユーザー定義関数は、C言語やVB6で本格的なものは作りますが、再計算(Calculate)で働かないようにするには、イベントドリブン型のマクロを置いて、再帰する時に割り込みで逃がしてあげればよいはずです。

どのようなコードでも良いとは思いますが、
ThisWorkbook モジュールに、以下のようなものはどうでしょうか？
これによって、別な影響がないとは言えませんが、当面の目的は果たせるはずです。End というのは、すべてのプロシージャをこの後に中止させる命令です。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　Application.EnableEvents = False
　Application.EnableEvents = True
　End
End Sub

回答No.1

imogasi

多分無いのだと思います。
私も自分でユーザー関数を作ってみて解答の検証をする中で経験します。
VBAは（エクセル関数をVBAの中で使わないと仮定して）実行は１回がぎりで、実行時のデータを用いて処理は終わりです。
それに比べエクセル関数は引数になっているデータを変えると、瞬時に再計算してくれるので、オンライン的な感覚を味わえます。
これがこの質問コーナーで、関数で関数でという理由には関数しか使えない人という理由のほかに、直感的にこのメリットを好ましく思っていて関数というのだと思います。
表計算と再計算は表裏一体だと思います。
＞変更のあった行だけ再計算をおこなう方法はありますでしょうか
これは極く簡単な例を勝手にイメージして、質問者の感覚で言っていることだと思います。色々なケースを考えてみてください。
式が複雑になると何段にも波及して（参照して）計算されていないとは言い切れますか。とてもいえません。
現在はパソコンのCPUの処理速度も飛躍的に向上してデータが少ないと遅いと感じませんが何万何十万の式になると大変時間がかかると思います。エクセルシステムの構成技術の中で、ある引数セルが変更されたとき、どのセルだけを再計算したらよいのか、管理しているのか、しておらず、式のあるセルを総なめして再計算しているのかわからないが、している場合そのアルゴリズムに個人的に興味があります。
ユーザー関数にはMsgbox　ｘｘｘを余分に入れると、再計算する過程が見えて、こりゃ大変だわ、といつも思います。一般の関数も同じように再計算をやっているのではないかな。
そのやっていることは、エクセルの最大のメリット（自動的に値が計算されて正しくなる）を享受するための
やむをえないコストだと思います。
だから場合によっては、VBAで処理して、値をセルに戻したほうが早いこともありえると思う。
質問本題の件は、全て止めるという形で再計算の時期はユーザーにコントロールさせてくれますが、
（１）どこのセル（範囲）の式とか
（２）どの式とか
はコントロールさせてくれません。実施するエクセル内部や使う側でも、ものすごく複雑化するように思います。
利用運用する上で、手動計算でさえ勘違いのもとではないかと思うくらいです。