強引にセルを再計算させる
セルの再計算が通用しない場面に対応する
自作ワークシート関数
めちゃくちゃ条件分岐の多い、死ぬほどダルい表を作る必要があった。別に私の仕事でも何でもないんだけれど、泣きついて来られて仕方なく……。
自分の仕事でも何でもないから、仕事中にやるわけにも行かず、休みの日にやっている。もうお人好しのバカ過ぎて自分が嫌になるｗ
んで、アホみたいにややこしい数式を組む必要があってめんどくさかったので、
計算ロジックを全部隠蔽しちまって、簡単な関数にしちまえ！
と。まあ、そんなことをしたが最後、この仕事が延々私につきまといそうな嫌な予感もするんやが……。
ちゃっちゃと片付けてしまいたいので、自分のやりやすいようにやったｗ
後は野となれ、ですよ。こんなこと、人に頼るやつが悪いんですから。
困ったこと
ただ、一つ困ることがあった。自作したワークシート関数は、
行の中の２つのセルを引数として受け取って、計算した結果を返す関数
なんだが、計算の過程の中で、同じ行の別のセルの値を使うようになっている。
で、引数に指定したセルの値を変更したら、それに追随して返り値も変更されるが、計算の過程で用いる引数以外のセルを変更しても、返り値が変わらないのである。ま、当たり前なのだけれど。
……って、何のことか分からんな……。
ちょいと例を挙げてみよう。
こんな表があったとする。
んで、標準モジュールに、次のようなFunctionを作る。
リスト１ 標準モジュール
Public Function assembleWord(ByVal Cell1 As Range, _
ByVal Cell2 As Range) As String '……(1)'
Dim str As String
str = Cell1.Value & Cell2.Value '……(2)'
str = str & Cell2.Offset(0, 1).Value '……(3)'
assembleWord = str
End Function
説明するまでもないけれど、一応。
(1)では、引数にCell1とCell2の２つのセルを受け取るように設定。
(2)でCell1とCell2の値を連結して、
(3)でさらにCell2の右隣のセルの値を連結して返す、というアホみたいなFunction。
んで、こんなふうにＤ列のセルにこの自作関数を入力。
引数にＡ列のセルとＢ列のセルを指定。
無事、Ｄ列にＡ～Ｃ列の文字を連結した値が返された。
ところが、
こんなふうに、引数以外のＣ列のセルの値を変更しても、
Ｄ列の返り値は変化なし……。
もちろん、引数に指定しているたとえば、Ａ列の値を変更すると、
こんなふうに、変更が反映される。
つまり、Ｃ列を変更しても何も起こらず、Ａ列やＢ列を変更して初めてＣ列への変更がＤ列の返り値に反映される、ということ。これでは不便すぎる。
仕方がないので、
ワークシートのイベントで、セルの値が変更されたときに、自作ワークシート関数を書き込んだセルを再計算したらいいんじゃね？
と思い、再計算させたいセル（この場合はＤ２セル）にカーソルを置いて、イミディエイト・ウインドウに
Activecell.Calculate
としてみたが、ダメだった。ってことは、再計算させたいセルを指定してCalculateメソッドを実行してもダメだということだ。
強引な解決策
んで、強引な解決を考えた。
ワークシートのイベントを用いて、
自作関数の入ったセルから数式を一旦消して書き直す
という荒技ですよ！ グッヒッヒッ。
表のあるシートのモジュールに以下のコードを書く。
リスト２ シートモジュール
Private Sub Worksheet_Change(ByVal Target As Range)
With Target '……(1)'
If .Column < 3 Then Exit Sub
If .Column > 4 Then Exit Sub
If .Row < 2 Then Exit Sub
If .Row > 6 Then Exit Sub
End With
Dim targetRow As Integer
targetRow = Target.Row
Application.EnableEvents = False
Dim Sh As Worksheet
Set Sh = Target.Parent
Dim str As String
With Sh.Range("D" & targetRow) '……(2)'
str = .Formula
.Formula = ""
.Formula = str
End With
Application.EnableEvents = True
End Sub
まず、(1)からの６行はガード節。今回、変更に対応しないといけないのはＣ列の２～６行目だけなので、それ以外の箇所が変更されたときには何もせずに処理を抜けるようにした。
後は、見ての通り。
(2)の
With Sh.Range("D" & targetRow)
str = .Formula
.Formula = ""
.Formula = str
End With
がポイント。自作ワークシート関数を書き込んだＤ列について、一旦数式文字列を変数strにぶち込んでから、数式を削除し、改めてstrにぶち込んでおいた数式を書き込み直している。
実行結果
Ｃ列をこんなふうに変更して、[Enter]をポチッ！
ほれ、再計算された返り値がＤ列に返っている。
おわりに
しっかし、いかにも強引な解決方法で、美しくないんだよなあ。まあ、計算に使うセルは全て引数に指定しろよ、ってことなのかも知れん。
