ワークシート関数をVBAで使用する(WorksheetFunctionプロパティ)｜Excel VBA

ツイート

ワークシート関数はWorksheetFunctionクラスのメンバです。VBAからワークシート関数を呼び出すにはApplicationオブジェクトのWorksheetFunctionプロパティを使用します。
主なワークシート関数を利用できますが、利用できない関数もあります。詳しくは、ヘルプの「Visual Basic で使用できるワークシート関数一覧」を参照してください。

構文　Object.WorksheetFunction 取得のみ

次のサンプルでは、MAXワークシート関数を用いて指定した範囲の最大値を求めます。

●サンプル1●

Sub Sample1()
   MsgBox Application.WorksheetFunction.Max(Range("A1:D10"))  '---(1)
   MsgBox Application.Max(Range("A1:D10"))                    '---(2)
End Sub

(1)のステートメントは、セル範囲A1:D10の値の最大値をメッセージボックスに表示します。これは、次のようにワークシートに数式を入力したのと同じ値になります。

=MAX(A1:D10)

なお、(2)のステートメントでは「WorksheetFunction」の記述を省略していますが、こちらでも同様の結果が得られます。
サンプル1の(2)のステートメントのような記述が許可されているのは、Excel 95との互換性を保つためです。WorksheetFunctionオブジェクトはExcel 97以降に追加されたオブジェクトで、それまでワークシート関数はApplicationオブジェクトに関連付けられていました。Excel 95では(1)のステートメントはエラーとなります。

■ワークシート関数がエラーを返す場合

サンプル1ではどちらの記述方法でも同じ結果が得られましたが、VLOOKUP関数やMATCH関数のようにワークシート関数がエラーを返す場合、挙動が異なります。

セルA1に指定した検索値がセル範囲B1:B5にないとき、次のサンプル2を実行するとステートメントそのものがエラーであると判断されます。
実行時エラーを回避するには、エラー処理を記述する必要があります。詳しくは「MATCHワークシート関数を使用してセル範囲を検索するを参照してください。

●サンプル2●

Sub Sample2()
    MsgBox Application.WorksheetFunction.Match(Range("A1"), Range("B1:B5"), 0)  
End Sub

一方、WorksheetFunctionオブジェクトを省略した記述ではエラー値が返され、実行時エラーは発生しません。検索値がないときは、IsError関数やIsNumeric関数などを使ってエラーを判定する処理を記述してください。

●サンプル3●

Sub Sample3()
    Dim ret As Variant
    ret = Application.Match(Range("A1"), Range("B1:B10"), 0)
    If IsError(ret) Then
        MsgBox "該当データが見つかりません"
    Else
        MsgBox ret & "番めのデータです"
    End If
End Sub