VBAで作るユーザー定義関数の第三弾。
ここまでユーザー定義関数を推しておいて今更だが、私は多用するのはオススメしていない。
今回はユーザー定義関数の何が危険なのかを説明する。
はじめに
第一弾では安定したユーザー定義関数の作り方について
第二弾ではユーザー定義関数を揮発性にする方法について
そして、第三弾ではユーザー定義関数の何が危険なのかを説明する・・・つもりでしたが、私のブログを初めた最初期にこんな記事を書いていたことを、下書きが書き終わってから気が付きました。
というわけで、今回のシリーズはおしまいです。
読んで頂いてありがとうございました。ちゅんちゅん(・8・)
まてまてまてぃ
なんてことにしようかと思いましたが、それはあまりにも投げやりなので、今回は当時「補足」に書いた部分と、抜けている部分について、画像とかを使ってもう少し詳しく説明します。
ユーザー定義関数を使わないほうが良いのはなぜか
最大の理由というか唯一の理由はこれです。
Excelが不安定になるから
これをもうちょっと詳しく細分化すると以下のような例が挙げられます。
- 「マクロの有効化」をしないと使えないから
- ユーザー定義関数を使わなくても同様の事ができるから
- 計算がすごく遅いから
- たまに再計算されなくなる時があるから
- ワークブックが破損するから
- 関数名が喧嘩するから
- 再計算が暴走してデバッグがしづらいから
「マクロの有効化」をしないと使えないから
言うまでも無い事ですが、ユーザー定義関数はVBAを使ったマクロですから、マクロの有効化がされていないと使えません。
つまり、ユーザー定義関数で自動的に計算しても、ボタン等を設置して何らかのイベントで計算しても、結局は「マクロの有効化」が必要になるのは変わりません。
相手がマクロを有効化してくれなさそう≒xlsmで渡せない場合は、事前に配布するブックにユーザー定義関数が含まれていないか確認して、削除しなければなりません。
相手に送るブックで固有のアドインのユーザー定義を含めた場合、相手も同じアドインを所有していないと動かないということになります。
つまり、ユーザー定義関数が使える場面は以下に限られると考えます。
- そのブックを自分しか使わない
- 利用者全員がマクロ入りなのを把握しており、必ず有効化してくれると信頼している
- 利用者全員が導入済みのアドインのユーザー定義関数を使用している
- 配布時に「値として貼り付け」等でユーザー定義関数を除去するつもり
ユーザー定義関数を使わなくても同様の事ができるから
これはユーザー定義関数を使わずに、同じ事が全てできるという意味ではありません。
（不安定な問題を避けるために）同じようなことを他の手段で実現できそうなときは、ユーザー定義関数を出来る限り避けたほうが良いという考えを前提として、使わないほうが良いという意味です。
例えば表の構造を変えたり必要な要件を見直したりすることで、組み込みの数式でも複雑にならずに記述できるかもしれません。
作業列を使ったり式変形によって、複雑な関数の入れ子を解消できるかもしれません。
ユーザーに「マクロの有効化」を求めるのであれば、イベント駆動型のVBAマクロでも良いはずです。
例えばWorksheet_Changeイベントを使えば、値の変化と同時に再計算させることだって可能です。
ユーザー定義関数の計算は遅いので、発動条件（セル）を絞ったイベントならこちらのほうが高速になりやすいです。そもそも安定しています。
※代わりに「元に戻す」の履歴が消えますが。
例えばのVBAでの実装例
TBL部署
| 部署コード | 部署名 |
|---|---|
| 10 | 総務部 |
| 20 | 営業部 |
| 30 | 開発部 |
シート（B2入力時）
| A | B | C | |
|---|---|---|---|
| 1 | 社員番号 | 部署名 | 社員名 |
| 2 | 1000 | 10 | あいうえお |
↓ イベントにより変換後
| A | B | C | |
|---|---|---|---|
| 1 | 社員番号 | 部署名 | 社員名 |
| 2 | 1000 | 総務部 | あいうえお |
計算がすごく遅いから
ワークシートから呼び出したユーザー定義関数は遅いです。とてつもなく遅いです。
どのくらい遅いかと言うと、同じ関数で1万セルに計算結果を表示するような際に、VBAでForを回して1万回呼ぶと0.1秒かからないのに対して、数式を1万セルに埋め込むと計算に10秒くらいかかったりします。
実に100倍～1000倍遅いです。 ※環境依存
エクセルの再計算のタイミングは、前記事でお伝えしたように基本的には引数に含まれているセルの値が変化した時に行われますから、常に1万セルが再計算されるようなことは無いですが、ちょっとした拍子に全体が再計算されるので、ブックがすごく重くなること間違いなしです。
揮発性関数なら問答無用で再計算されますけどね。しかも1セル当たり5回くらい。😨
遅い原因はVBAなら問題ないことから分かるように、ワークシートからの呼び出しに相当量のコストがかかっているようです。
経験上、対象のセルが多いほど再計算に時間がかかるようです。
例えば、巨大なテーブルの列全体に使用するなんて使い方は避けたほうが良いでしょう。
逆に単一のセル、任意の数セルで使用するような場合では問題とならないでしょう。
確かに、ワークシートからユーザー定義関数を呼ぶのは便利な場面は結構ありますが、一度検討する価値はあると思います。
たまに再計算されなくなる時があるから
画面左下のステータスバーに再計算と表示されて、式が計算されなくなってしまうときがあります。
普通であれば
- 自動計算の設定を確認
- 数式が循環参照になっていないか確認
- VBEがデバッグモード（中断）になっていないか確認
で解決する話ですが、ユーザー定義関数を使うと解消されなくことがあります。
- 再計算ボタンを押しても反応がない
なんて状態になることもあります。
このような現象は「計算量が多いユーザー定義関数」を大量に使用すればするほど起こりやすい気がします。
ユーザー定義関数が適切に抽象化されていることはもちろん、できる限り処理の軽い関数を作ることが求められます。
それでも発生するときは発生します。法則はよく分かりません。後述のVBAとの喧嘩が関係してそうな気もしますが。
最終手段としてはExcelを再起動したり、Alt+Ctrl+F9で強制的な再計算を実施することが考えられます。
ワークブックが破損するから
ワークシートからユーザー定義関数を呼んでいるブックはファイルが破損しやすくなります。
これは経験上、ユーザー定義関数の出来栄えには関係ないような気がしていて、一日仕事して保存して後日ブックを開いたら破損していたと言うような感じです。
もし壊れてしまったらバージョン管理システムから壊れていない最新のファイルを探し出す必要がありますね。
バックアップが一切無いときは・・・ｶﾞｸｶﾞｸ(((( ;ﾟДﾟ))))ﾌﾞﾙﾌﾞﾙ
壊れ方は数種類ありますが、例えば下記のような壊れ方は本当に良く見かけます。
（100%再現させる方法を特定したはずなのですが、その時のメモを見失いました）
関数名が喧嘩するから
稀な例ですが、同じ名前の関数がアドインとブックに存在する場合、セルに書き込まれた関数はアドインの方を参照します。
例えば
=Func()
は、一旦保存してブックを開き直すと
=Addin.xlam!Func()
になったりします。
いやらしいのは次に保存する時にアドインのパスごと保存してしまうということ。
このせいで外部リンクエラーを引き起こすことになります。
たとえ保存前にアドインを閉じてもアドインの参照が途切れないので、別の方法で切断しなければなりません。
ユーザー定義関数を呼んだブックを保存して、閉じて、開き直すと破損していますと出やすいです。
- 同一の名称の関数が、アドインとブックに存在すると、関数の参照先はアドインが優先される仕様らしい。
- したがって、一度アドインとブックを開いた状態で保存し、アドインを開かずにブックを開いた時、破損していますと出ることがある。
- 共有フォルダ等で複数のPCで共有する時に問題となりやすい。
- 一度アドインにリンクされてしまった関数を直すのは置換でもできるが地味にめんどくさい。
再計算が暴走してデバッグがしづらいから
実際に使い始めると分かるのですが、VBAのコーディング中に再計算が走るので、デバッグ中に思わぬところでステップが飛んでいったり、実行時エラー無限ループに陥ったり、とにかく悲惨なことになります。
ユーザー定義関数を開発するときは、自動計算をOFFにしておいたほうが良いかもしれません。
また、作り方次第では「ブックの破損」によりマクロがすべて消失することが多々あります。
ブックを保存するたびにバージョンをバックアップしましょう。
（その割には関数のデバッグをするためにワークシートにユーザー定義関数を埋め込む事はよくあるんですけどね・・・。）
まとめ
ユーザー定義関数の危険性をお分かり頂けたでしょうか。
私自身も経験上、色々と不安定なのは把握していますが、説明しようとしたら中々苦労しました。
それに再現性のある具体例を上げようとすると、少量のセルで使う分には問題なく動いてしまうのでブログでの説明では難しいです。
練習のときは問題なかったのに本番導入して使っているうちに問題が表面化してVBAに戻した。なんてことが多いような気がします。
というわけで、ユーザー定義関数は注意して使ってね！ということで、このシリーズは完結に致します。
以上
何か御座いましたらコメント欄、またはTwitterからどうぞ♪
それではまた来週♪ ちゅんちゅん(・8・)
