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

office-932926_1280 IT

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

Advertisements

WorksheetFunctionプロパティの使い方

VBAでワークシート関数を使用①
Application.WorksheetFunction.関数
※Applicationは省略可能
worksheetfunction1
とりあえずSum関数でやります。A1からA5までを合計したい場合、ワークシート関数なら=Sum(A1:A5)と入力すれば済みます。
worksheetfunction2
今度はVBA上でA1からA5までの合計を求め、結果を変数に代入してみたいと思います。直接A6セルを参照はしないものとします。
worksheetfunction3
変数を宣言し、これに対してApplication.WorksheetFunctionとまず入力して、ワークシート関数を使う旨指定します。
worksheetfunction4
その後にSum(Range(“A1:A5”))と入力します。Sum(A1:A5)ではない点に注意してください。Rangeが必要です。
worksheetfunction5
変数をメッセージボックスで表示すると、正しい結果が得られていることが確認できました。
worksheetfunction6
MsgBox同様、WorksheetFunctionもオブジェクト(Application)を省略しても動きます。

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

worksheetfunction7
VBEでWorksheetFunction.まで打つと、予測変換が表示されます。ここにあるものは使用できる関数です。
worksheetfunction8
スクロールするとちゃんとSum関数が表示されていることがわかります。
WorksheetFunction.Suぐらいまで打つと変換候補が絞り込まれるので、簡単に探すことができます。
worksheetfunction9
候補をクリックするか、カーソルキーので選択してEnterでそのまま入力することができます。
worksheetfunction10
VBAに同様・同名の関数が存在するものは使用できません。具体的に言うとDate、Day、Month、Year、Today、Left、Mid、RightなどはVBA上で使用することができません。
またmode.snglのように途中に.が入る関数は、.を_に置き換える必要があります。

Evaluateメソッドの使い方

VBAでワークシート関数を使用②
Application.Evaluate.(“関数”)
※Applicationは省略可能
evalute1
同じことを、今度はEvaluateメソッドでやってみたいと思います。
WorksheetFunctionとちがってこちらはメソッドですが、使うイメージは大体似たようなものです。
evalute2
同じくApplicationは省略可能なので、今回は先に取っときます。
evalute3
Evaluateの場合、後ろに()を付け、その中に””で囲んだ文字列としての関数を記入します。
Rangeなどは必要ありません。
evalute4
実行結果です。
evalute5
また、EvaluateメソッドではWorksheetFunctionで使えないワークシート関数も使用できます。
(普通にこのコードを実行するとシリアル値が表示されるので、例表ではB1セルの書式を予め変更しています)
evalute6
ひとつ注意点として、Evaluateメソッド内で関数の引数に文字列を入力する場合、””で囲っただけだとエラーになります。
evalute7
もうひとつ増やして、””で囲むようにするとちゃんと動きます。
たとえば空白の場合、””””と入力することになります。

[]の使い方

evalute8
このやりかたが一番シンプルです。
[]で閉じ、その内側に関数を記入します。Rangeなどのセル指定は不要、””で閉じる必要もありません。
evalute9
実行結果です。
evalute10
文字列を入れる場合も、Excel上で関数を入力する時と同様””で囲むだけでOKです。
やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント