Excelのワークシートで使う関数（ワークシート関数）の一部は、VBAでも使うことが出来ます。
当記事では、VBAでワークシート関数を使う方法、およびVBAの関数との違いについて、説明いたします。
ワークシート関数の使い方
Application.WorksheetFunction.関数名（引数）ただし、「Application.」は省略可能ですので、
WorksheetFunction.関数名（引数）のようにも記述できます。
ワークシート関数の使用例
わかりやすい例として、お馴染みのSum関数を使って、以下の表のセル[B1]～[B5]の合計を、セル[B6]に出す方法を説明いたします。
Excel上で関数を使う場合
=SUM(B1:B5)VBAの場合
Range("B6") = WorksheetFunction.Sum(Range("B1:B5"))引数「B1:B5」を「Range」で囲む点に注意して下さい。（もちろん、「Cells」を使っても構いません。）
なお、シート名を指定する場合は、以下のような記述方法になります。
Sheets("Sheet1").Range("B6") = WorksheetFunction.Sum(Sheets("Sheet1").Range("B1:B5"))実行結果
これを、関数を使わずに実現すると、以下のようにゴリゴリとコーディングする必要がありますが、ワークシート関数により、シンプルに実現できます。
また、ワークシート関数の方が、処理が高速だそうです。
Dim IxRow As Integer
Dim Total As Integer
Total = 0
For IxRow = 1 To 5
Total = Total + Range("B" & IxRow)
Next
Range("B6") = TotalVBAで使える主なワークシート関数
他にも、代表的なものとして以下のようなワークシート関数を使えます。
- Sum
- Average
- Vlookup
- Max
- Min
- Count
- Countif
VBAで使えない主なワークシート関数
ここからがちょっとややこしいのですが、ワークシート関数とVBA関数の違いについて、3つのパターンに分けて説明いたします。
パターン①：ワークシート関数とVBA関数が別名の例
以下のように、同じ用途でもワークシート関数とVBA関数で別名の関数になっているものがあります。
| 用途 | ワークシート関数 | VBA関数 |
|---|---|---|
| 文字列の検索 | FIND | Instr |
| 文字列の置換 | SUBSTITUTE | Replace |
| 書式変換 | TEXT | Format |
| 現在の日付を取得 | TODAY | Date |
気を付けないといけないのは、FIND関数とInstr関数のように、引数の順序が違う場合があります。
FIND関数の使い方
FIND(検索文字列, 対象, 開始位置)例
=FIND("い",A2,1)B2セルに上記数式を入力した結果
Instr関数の使い方
InStr(開始位置, 対象, 検索文字列)例
Range("B2") = InStr(1, Range("A2"), "い")パターン②：ワークシート関数とVBA関数が同名の例
以下のように、ワークシート関数とVBA関数で同じ名前になっている関数があります。
これらについては、VBAではワークシート関数(WorksheetFunction)は使えません。
- Left
- Mid
- Right
- Len
- Now
誤：Range("B1") = WorksheetFunction.Left(Range("A1"), 2)
正：Range("B1") = Left(Range("A1"), 2)結果
パターン③：ワークシート関数とVBA関数が同名だが、用途が異なる例
以下の関数（他にもあるかもしれません）は、ワークシート関数とVBA関数で同じ名前になっていますが、用途が異なります。
Asc関数
ワークシート関数
全角英数字を半角に変換する
=ASC(A1)VBA関数
指定した文字の1文字目の文字コードを返す
Range("B2") = Asc(Range("A1"))結果
- B1セルにワークシート関数の数式を入力
⇒ 「ＡＢＣ」を半角変換した文字列「ABC」が返されます。 - B2セルにVBA関数の結果を表示
⇒ 文字「Ａ」の文字コード「-32160」が返されます。
Round関数
ワークシート関数
指定した桁数で四捨五入する
=ROUND(A2,1)VBA関数
指定した桁数で「銀行丸め」形式で四捨五入する。
四捨五入する対象の数字が「5」の場合、必ず切り上げをするのではなく、丸めたあとの数値が偶数になるように丸めます。
言葉では伝わりにくいので、具体例を見てみましょう。
Range("C2") = Round(Range("A2"), 1)結果
B列にワークシート関数の数式を表示
C列にVBA関数の結果を表示した結果です。
このように、切り上げた位置の値が偶数になってしまうため、一般的な四捨五入になっていませんね。
これでは困る、という場合は、以下のようにWorksheetfunction.Roundを使う必要があります。
Range("D2") = WorksheetFunction.Round(Range("A2"), 1)
コメント