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

とりあえず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ぐらいまで打つと変換候補が絞り込まれるので、簡単に探すことができます。
WorksheetFunction.Suぐらいまで打つと変換候補が絞り込まれるので、簡単に探すことができます。

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

VBAに同様・同名の関数が存在するものは使用できません。具体的に言うとDate、Day、Month、Year、Today、Left、Mid、RightなどはVBA上で使用することができません。
またmode.snglのように途中に.が入る関数は、.を_に置き換える必要があります。
またmode.snglのように途中に.が入る関数は、.を_に置き換える必要があります。
Evaluateメソッドの使い方
VBAでワークシート関数を使用②
Application.Evaluate.(“関数”)
※Applicationは省略可能
Application.Evaluate.(“関数”)
※Applicationは省略可能

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

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

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

実行結果です。

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

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

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

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

実行結果です。

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

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

関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。
コメント