強引にセルを再計算させる

セルの再計算が通用しない場面に対応する

自作ワークシート関数

めちゃくちゃ条件分岐の多い、死ぬほどダルい表を作る必要があった。別に私の仕事でも何でもないんだけれど、泣きついて来られて仕方なく……。

自分の仕事でも何でもないから、仕事中にやるわけにも行かず、休みの日にやっている。もうお人好しのバカ過ぎて自分が嫌になるw

んで、アホみたいにややこしい数式を組む必要があってめんどくさかったので、

計算ロジックを全部隠蔽しちまって、簡単な関数にしちまえ!

と。まあ、そんなことをしたが最後、この仕事が延々私につきまといそうな嫌な予感もするんやが……。

ちゃっちゃと片付けてしまいたいので、自分のやりやすいようにやったw

後は野となれ、ですよ。こんなこと、人に頼るやつが悪いんですから。

困ったこと

ただ、一つ困ることがあった。自作したワークシート関数は、

行の中の2つのセルを引数として受け取って、計算した結果を返す関数

なんだが、計算の過程の中で、同じ行の別のセルの値を使うようになっている。

で、引数に指定したセルの値を変更したら、それに追随して返り値も変更されるが、計算の過程で用いる引数以外のセルを変更しても、返り値が変わらないのである。ま、当たり前なのだけれど。

……って、何のことか分からんな……。

ちょいと例を挙げてみよう。

f:id:akashi_keirin:20171229224440j:plain

こんな表があったとする。

んで、標準モジュールに、次のようなFunctionを作る。

リスト1 標準モジュール
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つのセルを受け取るように設定。

(2)でCell1とCell2の値を連結して、

(3)でさらにCell2の右隣のセルの値を連結して返す、というアホみたいなFunction。

f:id:akashi_keirin:20171229224449j:plain

んで、こんなふうにD列のセルにこの自作関数を入力。

f:id:akashi_keirin:20171229224456j:plain

引数にA列のセルとB列のセルを指定。

f:id:akashi_keirin:20171229224512j:plain

無事、D列にA~C列の文字を連結した値が返された。

ところが、

f:id:akashi_keirin:20171229224525j:plain

こんなふうに、引数以外のC列のセルの値を変更しても、

f:id:akashi_keirin:20171229224538j:plain

D列の返り値は変化なし……。

もちろん、引数に指定しているたとえば、A列の値を変更すると、

f:id:akashi_keirin:20171229224551j:plain

f:id:akashi_keirin:20171229224605j:plain

こんなふうに、変更が反映される。

つまり、C列を変更しても何も起こらず、A列やB列を変更して初めてC列への変更がD列の返り値に反映される、ということ。これでは不便すぎる。

仕方がないので、

ワークシートのイベントで、セルの値が変更されたときに、自作ワークシート関数を書き込んだセルを再計算したらいいんじゃね?

と思い、再計算させたいセル(この場合はD2セル)にカーソルを置いて、イミディエイト・ウインドウに

Activecell.Calculate

としてみたが、ダメだった。ってことは、再計算させたいセルを指定してCalculateメソッドを実行してもダメだということだ。

強引な解決策

んで、強引な解決を考えた。

ワークシートのイベントを用いて、

自作関数の入ったセルから数式を一旦消して書き直す

という荒技ですよ! グッヒッヒッ。

表のあるシートのモジュールに以下のコードを書く。

スト2 シートモジュール
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)からの6行はガード節。今回、変更に対応しないといけないのはC列の2~6行目だけなので、それ以外の箇所が変更されたときには何もせずに処理を抜けるようにした。

後は、見ての通り。

(2)の

With Sh.Range("D" & targetRow)
  str = .Formula
  .Formula = ""
  .Formula = str
End With

がポイント。自作ワークシート関数を書き込んだD列について、一旦数式文字列を変数strにぶち込んでから、数式を削除し、改めてstrにぶち込んでおいた数式を書き込み直している。

実行結果

f:id:akashi_keirin:20171229224619j:plain

C列をこんなふうに変更して、[Enter]をポチッ!

f:id:akashi_keirin:20171229224628j:plain

ほれ、再計算された返り値がD列に返っている。

おわりに

しっかし、いかにも強引な解決方法で、美しくないんだよなあ。まあ、計算に使うセルは全て引数に指定しろよ、ってことなのかも知れん。

@akashi_keirin on Twitter