Excel VBAの自作関数の効率的なテスト方法について

今回は自作関数のテスト方法について、私のお気に入りの方法を紹介する。

そもそもテストパターンを作成して関数毎にテストするなんて、現役のプログラマからしてみたら当たり前のことだと思うが、VBAの現場でそこまでキッチリテストしている人は少数派なのではないだろうか。

エクセルVBAならではの方法だが、お手軽にテストできるので、もし自作関数に手を出した人は使ってみてほしい。

はじめに

そもそも、Excel VBAにおいて、

標準モジュールにPublic Functionで宣言したユーザー定義関数はワークシート上から使用すことが出来る

のはご存知だろうか。

・EXCEL関数は良くわからないからVBAで書いてる。

という人もいれば（←昔の私です）

・トラブルの元だから使うべきではない。

という人もいると思います。

・使うべきではないが、便利なので仕方なく使う時もある。

という人もいるかもしれません。（←今の私です）

使い方

まずは自作関数のテスト方法をお見せします。

せっかくなので、検証には前の記事の自作関数の比較表を使いたいと思う。

www.excel-chunchun.com

f:id:Kotori-ChunChun:20181231034156p:plain — GetAbsolutePathNameテスト表

特に大した事をしているわけではなく、適当なシートにテーブルを定義（Ctrl+T）してA列にbasePath、B列にrefPath、C列にfsoを使った自作関数を呼び出す式、D列にゴリゴリ書いた自作関数を呼び出す式を書いただけだ。

一応書いておく。

C列：=GetAbsolutePathNameExFso([@basePath],[@refPath])
D列：=GetAbsolutePathNameEx([@basePath],[@refPath])

これで、テストパターンをA列とB列に追記していくという、たったこれだけの作業でリアルタイムで一連のテストパターンの実行結果を表示してくれるツールの完成だ。

なんなら適切な実行結果の列と、チェックする式を書けば、テストパターンの正当性も一目で分かるようになる。

一般的なプログラミング言語のテストでは、別途ファイルを作成したりMergeしたりするのだと思うが、VBAではEXCEL一本で完結する。

これがExcel VBAの大きな強みなんじゃないかと思っている。

しかしVBAコードを直したら即座に更新がされることはないので、再計算（F9）を実行する必要がある点だけは注意してほしい。

また、前のページで書いたfso.GetAbsolutePathNameのように、Excelに応答を返すのに時間のかかるコードが混ざっていると、フリーズして泣くことになるので注意したほうが良いと思われる。

他のテスト方法との比較

さて今更感はあるが、テスト作業をVBE上でやるとどうなるのだろうか。

Private Sub Test_GetAbsolutePathNameEx()
    'C:\hoge\fuga\同じフォルダの.xlsxが返って欲しい。
    Debug.Print GetAbsolutePathNameEx("C:\hoge\fuga\", ".\同じフォルダの.xlsx")

    'C:\hoge\上のフォルダの.xlsxが返って欲しい。
    Debug.Print GetAbsolutePathNameEx("C:\hoge\fuga\", "..\上のフォルダの.xlsx")

    'C:\２つ上の.xlsxが返って欲しい。
    Debug.Print GetAbsolutePathNameEx("C:\hoge\fuga\", "..\..\２つ上の.xlsx")
・・・
End Sub

この場合の問題点を適当に上げていく。

とにかくコピペが面倒くさい。
コピペ後の書き換えミスやタイプミスが起こりやすい。
テストパターンが増えるほど、イミディエイトが流れて効率が悪い。
かといってコメントアウトをしたり、解除したりするのも面倒。

したがって、あまり数が多いならDebug.Print するよりもシートに書き出した方がマシだろう。

でも、先述のようにワークシート上から関数を呼ぶのに比べると、テストコード用のプロシージャを作らなければならない分、どうしても効率は落ちる。

補足

たまに再計算されなくなる時がある。
- Alt+Ctrl+F9またはAlt+Ctrl+Shift+F9・・・強制再計算を実施しましょう。
- VBAから行うときは、Application.CalculateFull
ユーザー定義関数を揮発性関数にする。
- これはあまりオススメしません。
- プロシージャにApplication.Volatileを記述すると事あるごとに再計算が実行される揮発性関数になります。
ワークブックが破損する。
- ユーザー定義関数を使ったブックを保存して、閉じて、開き直すと破損していますと出やすい。
- 関数が不完全だったり、計算が重いと出やすい気がする。
- 開けないときはマクロを無効化してから開くと良いかも。
アドインの関数と喧嘩する。
- 同一の名称の関数が、アドインとブックに存在すると、関数の参照先はアドインが優先される仕様らしい。
- したがって、一度アドインとブックを開いた状態で保存し、アドインを開かずにブックを開いた時、破損していますと出ることがある。
- 共有フォルダ等で複数のPCで共有する時に問題となりやすい。
- 一度アドインにリンクされてしまった関数を直すのは置換でもできるが地味にめんどくさい。