【ExcelVBA】ワークシート関数を使用する-WorksheetFunction

ワークシート関数をVBA上で計算する「WorksheetFunctionオブジェクト」の使い方を解説します。

基本構文
使い方
使いどころ
注意点
まとめ

基本構文

' 基本構文
WorksheetFunction.関数名(引数1, 引数2, 引数3, …)

' ※ 正式には↓。Applicationは省略してOKです。
Application.WorksheetFunction.関数名(引数1, 引数2, 引数3, …)

使い方

WorksheetFunction用の表

' 使用例　SUM関数
Dim 売上合計 As Long
売上合計 = WorksheetFunction.Sum(Range("D2:D5"))

' 使用例　SUMIF関数
Dim みかんの売上合計 As Long
みかんの売上合計 = WorksheetFunction.SumIf _
    (Range("A2:A5"), "みかん", Range("D2:D5"))

ワークシートでセルに入力するときとほとんど同じ書き方なので、
特別何が勉強が必要なことはないと思います。

普段シートで書いているときと同じように使ってください。

ワークシート関数との一番の違いは、
「引数に渡すセル・範囲を、セルアドレスではなくRangeオブジェクトで渡す」
ところです。

売上合計 = WorksheetFunction.Sum("D2:D5")

これでは動きませんので、ご注意ください。

Rangeオブジェクトで渡せるということは、

Cellsプロパティを活用した位置の取得
Rangeを格納している変数そのものを渡す

など、プログラミング記法を活用した書き方ができます。

' SUM関数に「最終行の取得」を組み込んで、合計範囲を動的に
Dim データの最終行 As Long: データの最終行 = なんらかの最終行取得コード
Dim 売上合計 As Long
売上合計 = WorksheetFunction.Sum(Range(Cells(2, 4), Cells(データの最終行, 4)))

' SUMIF関数を「データエリアの何列目」で指定して動かす
Dim データエリア As Range: Set データエリア = Range("A2:D5")
Dim みかんの売上合計 As Long
みかんの売上合計 = WorksheetFunction.SumIf _
    (データエリア.Columns(1), "みかん", データエリア.Columns(4))

うまく組み合わせることで、シート上で書いているときより読みやすい関数式にすることができます。

いろいろ研究してみてください。

使いどころ

「自分でコードを書こうと思うと大変だけど、関数だと1発」という、
コードを書く手間が大幅に削減できるのが一番のメリットです。

↑で紹介した「SUMIF」がまさにそうですね。
ゴリゴリFor文とIF文で書けって言われるとげんなりします。

VLOOKUP、COUNTIF、MAX、MIN、SUMPRODUCTあたりが非常に強力です。
どんどん使っていきましょう。

もう一つ重要なメリットは、「自分で書くより圧倒的に処理が高速」である点です。

ただの足し算ですら、WorksheetFunction.Sumに書き換えると高速化しますし、
MATCH、VLOOKUPなどの検索系関数は、手作業は当然のこと「検索先Range.Find」などのVBAのメソッドより高速です。

WorksheetFunctionは、マクロを高速化しようと思ったら真っ先に使用を検討すべきものですので、意識しておきましょう。

注意点

WorksheetFunctionを使う際の注意点ですが、
関数の計算結果がエラー値の場合は、マクロがエラーで止まってしまいます。

シート上でやるようにISERROR関数で回避することはできませんので、
On Error Resume Nextでスキップするなどで対応します。

' ↓こんな書き方をしても、VBAは()内から計算されるため、回避できない
If WorksheetFunction.IsError(WorksheetFunction.VLookup(エラーの出る検索)) Then


' 回避策①：エラーをスキップし、後でエラーがあったかどうかを判定
On Error Resume Next
検索結果 = WorksheetFunction.VLookup(エラーの出る検索)
If Err.Number <> 0 Then
    検索結果 = "該当なし"
End If
On Error GoTo 0

' 回避策②：変数をエラー時の値で初期化したあとスキップする
On Error Resume Next
検索結果 = "該当なし" ' ←ループなどでこの初期化を忘れると「前の検索結果」が残るので注意
検索結果 = WorksheetFunction.VLookup(エラーの出る検索)
On Error GoTo 0