エクセルれんと(6) - やねうらおブログ(移転しました)

CountColorに相当する関数は先日紹介した「Excel Hacks」にも載っている。しかし、この欠点について何も書かれていない。それをここで詳しく書いておきたいと思う。

CountColorマクロの欠点とは、Excelの再計算の仕様によるものである。ユーザーが定義した関数がいつ再計算されるか、Excelは無関心である。



=CountColor(A1,B2:C3)

とやった場合、セルA1とセルB2〜C3を参照している。よって、これらのセルのどれか1つでも変更された場合、この式は再計算される。

ここで二つ問題が発生する。

ひとつは、内部的にこれら以外のセルを参照し、計算している場合である。この場合、それらのセルが変更されても再計算されない。これは、(少しダサイが)ダミーの引数をとるようにして、その引数でそれらのセルをとるようにすれば解決できる。

さもなくば、Ctrl+Alt+F9を使って集計前に「強制再計算」をさせることである。集計にマクロを用いているならば、



	Application.CalculateFull

とやって「強制再計算」を行なわせる。

もうひとつの方法はCountColorを「自動再計算」メソッドに指定する方法である。「自動再計算」メソッドに指定すれば何かのアクションごとに「自動再計算」される。そのためには、CountColor関数の最初の行に次のように書けば良い。



    Application.Volatile

「こんなところに書いてホンマに大丈夫なんかいな？」と思われるかも知れないが、大丈夫である。Excelは、この位置に上記のように書かれている関数は「自動再計算」関数であるとみなし、何らかのアクションごとに「再計算」をするようにしてくれる。(当然、どこかのセルの値をひとつ変更しただけでCountColor関数を使っているところはすべて再計算されてしまうので非常にレスポンスが悪くなる。出来ることならば使わないほうが無難である)

ここまではたいした問題ではない。最大の難関は、そこではない。やっかいなのはもうひとつのほうの問題で、私はこれは現在のExcelではどうやっても解決できないのではないかと思っている。

それは、セルの色の変更(セルをコピーして貼り付けた場合は除く)に対してはイベントが発生しないというExcelの仕様である。

つまり、上記のようにCountColorを「自動再計算」メソッドに指定しようが、セルの色変更に対して何らイベントが発生しない以上、「自動再計算」メソッドに意味がないのだ。

仕方ないので選択範囲を変更されたときのイベントハンドラ内で再計算を促すようにして、CountColorを「自動再計算」メソッドに指定する。



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.Calculate

End Sub

こうしておけば、色を変更後、カーソルをどこか移動させたりした瞬間にCountColorでの再計算が行なわれる。これは確かにほぼ期待通りの動作だが、カーソルの移動ごとにCountColorを用いているセルすべてで再計算が行なわれるので非常に遅い。

ここで問題となってくるのは、「色の変更に対して何故、イベントが発生しないのか」ということである。セルの貼り付けに対してはイベントが発生する。CountColorで参照しているセルにセルの貼り付けが行なわれた場合、CountColorは再計算される。しかし罫線が崩れると嫌なので出来ればそんなことはしたくない。

よく考えると「色の変更に対してイベントが発生しない」という仕様に何らかの合理性があるとしても、「セルの貼り付けでイベントが(常に)発生する」のはおかしな話だ。いまのExcel(Excel95から2003まですべて！)では貼り付け前と貼り付け後でセルの“内容”(そこに書かれている数字、数式)が変化していなくともそのセルを参照している関数はすべて再計算対象になる。これは明らかに無駄だし、「値が変化したので再計算を行なう。セルの色の変更は“内容”の変化ではないので再計算を行なわない」という一貫した理念ではなく、セルの色変更されたときにイベントハンドラを呼び出すのを単にExcelが忘れているだけのようにしか思えない。言うなればExcelの設計上のミスではないかと思う。

しかしこの問題を何らかのhackで解決できるかも知れない。
誰か何とか　や・ら・な・い・か。