ExcelVBA（マクロ）でExcelの関数（ワークシート関数）を使う方法/WorksheetFunction、Evaluate、[]の使い方

VBAにはExcelで通常使う関数（ワークシート関数）とは別にVBA関数が用意されていますが、ワークシート関数を使いたい場面もままあります。一番簡単なのはExcel上で普通に関数を使ってそれを参照する方法なんですが、そういう作業セルを用意せず、VBA上で直接ワークシート関数を使うことも可能です。
[]の使い方だけでも覚えておくと便利かと思います。

WorksheetFunctionプロパティの使い方

とりあえずSum関数でやります。A1からA5までを合計したい場合、ワークシート関数なら=Sum(A1:A5)と入力すれば済みます。

今度はVBA上でA1からA5までの合計を求め、結果を変数に代入してみたいと思います。直接A6セルを参照はしないものとします。

変数を宣言し、これに対してApplication.WorksheetFunctionとまず入力して、ワークシート関数を使う旨指定します。

その後にSum(Range(“A1:A5”))と入力します。Sum(A1:A5)ではない点に注意してください。Rangeが必要です。

変数をメッセージボックスで表示すると、正しい結果が得られていることが確認できました。

MsgBox同様、WorksheetFunctionもオブジェクト（Application）を省略しても動きます。

ワークシート関数がWorksheetFunctionで使えるかの確認方法

VBEでWorksheetFunction.まで打つと、予測変換が表示されます。ここにあるものは使用できる関数です。

スクロールするとちゃんとSum関数が表示されていることがわかります。
WorksheetFunction.Suぐらいまで打つと変換候補が絞り込まれるので、簡単に探すことができます。

候補をクリックするか、カーソルキーの ↓ で選択してEnterでそのまま入力することができます。

VBAに同様・同名の関数が存在するものは使用できません。具体的に言うとDate、Day、Month、Year、Today、Left、Mid、RightなどはVBA上で使用することができません。
またmode.snglのように途中に.が入る関数は、.を_に置き換える必要があります。

Evaluateメソッドの使い方

同じことを、今度はEvaluateメソッドでやってみたいと思います。
WorksheetFunctionとちがってこちらはメソッドですが、使うイメージは大体似たようなものです。

同じくApplicationは省略可能なので、今回は先に取っときます。

Evaluateの場合、後ろに()を付け、その中に””で囲んだ文字列としての関数を記入します。
Rangeなどは必要ありません。

実行結果です。

また、EvaluateメソッドではWorksheetFunctionで使えないワークシート関数も使用できます。
（普通にこのコードを実行するとシリアル値が表示されるので、例表ではB1セルの書式を予め変更しています）

ひとつ注意点として、Evaluateメソッド内で関数の引数に文字列を入力する場合、””で囲っただけだとエラーになります。

もうひとつ増やして、””で囲むようにするとちゃんと動きます。
たとえば空白の場合、””””と入力することになります。

[]の使い方

このやりかたが一番シンプルです。
[]で閉じ、その内側に関数を記入します。Rangeなどのセル指定は不要、””で閉じる必要もありません。

実行結果です。

文字列を入れる場合も、Excel上で関数を入力する時と同様””で囲むだけでOKです。

やりたいことから方法を探すエクセル（Excel）操作・関数・VBA（マクロ）逆引きまとめ

逆引き（やりたいことから探す）Excel記事まとめ

sunagitsune.com

2018.11.04

関数・演算子・メソッド・プロパティ名から探すExcel/VBA（マクロ）使い方・組み合わせ方まとめ

こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

sunagitsune.com

2018.12.30