VBAでeval（Evaluate関数）を使ってみる

2010年現在でも、中小零細企業では表計算から請求書や納品書等の書類にいたるあらゆる業務で Microsoft Excel が活躍する。したがって、Excel VBA でのプログラミングをすることも多い。

VBA の場合、セルにデータを入力しておくことでプログラムを制御するのが一つのマイベストプラクティス。たとえば一番最後のワークシートにプログラム上の定数を記載しておく。後日ユーザーがプログラムの挙動を変えたくなったらそのシートをユーザー自身に編集してもらうことで、自分の仕事を減らせる。

この考えをもう一歩進めると、データだけではなく「手続き」や「関数」までもセルに記述しておきたくなる。たとえばある金額の計算過程で現在は 5%増しの計算をしているのだが、来年になったら (8%増し + 100円) に変えたい、といった具合である。

以下ではこの例題をサンプルとして、変更を見越した関数を設計し、VBAの "Evaluate"関数と組み合わせることによって目的が達成できることを示す。

計算関数を定義する

引数は2つ（利率、上乗せ固定額）

Function f計算(利率、上乗せ固定額)
    f計算 = 金額 * (100 + 利率) / 100 + 上乗せ固定額
End Function

VBAでは日本語が利用可能なので、変数名や関数名になるべく日本語を使う。

グローバル変数を定義する

上記の関数内には、自由な変数、つまり仮引数として定義されていない変数「金額」がある。この値は実行時にワークシートのセルに入力されているデータであるのだが、残念ながら上記の関数内から参照することができない（Evaluateにより実行されるコードからはセルの値を"Cell(1, 1)"というような形で参照できない）。したがって関数実行前にセルから読み込み、グローバル変数に設定しておく。そのための入れ物を定義するのが次のコード。

Public 金額 As Long

このPublic文は標準モジュール等のトップレベルに書く。

計算を行うマクロを定義する

1. グローバル変数に、名前付きセル「入力セル」を代入
2. 「関数セル」に入力されているコードを適用
3. 結果を「出力セル」に入力

Sub Macro1()
  金額 = Range("入力セル").value
  計算結果 = Application.Evaluate(Range("関数セル").value)
  Range("出力セル").value = 計算結果
End Sub

ワークシートのセルに必要なデータおよび関数呼出しコードを書く

1. 任意のセルに「1000」と入力し、セルの名前を「入力セル」とする
2. 〃 「f計算(5, 0)」と入力し、 〃 「関数セル」とする
3. 任意のセルを選択して、セルの名前を「出力セル」とする

マクロを実行

1. [f8]キーを押すなどしてマクロ「Macro1」を実行
2. うまくいけば「出力セル」に 1050 と出力される

計算を変えてみる

1. 「関数セル」の内容を「f計算(8, 100)」に変更
2. マクロを実行
3. うまくいけば「出力セル」に 1180 と出力される

まとめ

以上で、セル内のプログラムコード書き換えによりプログラムの挙動を変えることができるようになった。

「f計算」以外にもっと関数を定義してやることで、「関数セル」に書けるコードも自由度が増していくはず。

ただし、入力データを参照するためにグローバル変数が必要になるのであまりきれいとは言えない。

また、( )を多用したようなコードをユーザーに書き換えてもらうのは難しいから、変更はプログラム担当者の仕事になるだろう。

参考

• エクセルVBAからのワークシート関数呼び出しメモ(Hishidama's Excel Memo "WorksheetFunction")