White Tiger

Microsoft Excel VBA - ワークシート関数をVBAコード内で使用する方法

◆概要

このページは、Excelで ワークシート関数をVBAコード内で使用する方法について記載しています。


WorkSheetFunctionプロパティは、ワークシートで使用する関数郡をVBAのコードから使用することができる、WorksheetFunctionオブジェクトを取得するプロパティです。


このプロパティを使用してWorksheetFunctionオブジェクトへの参照を行い、続けてワークシート関数を記述すると、関数の戻り値をコードの式で使用できます。


次のプロシージャは、ワークシートSheet2のセルA1からA10に入力されている値の平均値を、ワークシート関数「Average」を使用して算出し、その結果をメッセージボックスで表示します。

◆Sample code

Sub アベレージの算出()

  With Worksheets("Sheet2")
    Result = Application.WorksheetFunction.Average(.Range("A1:A10"))
    MsgBox "平均値は " & Result
  End With

End Sub

このように、「WorksheetFunction」オブジェクトを使用すると、ワークシートで使用できるExcelの関数をVBAのコード内でも実行できます。そして関数の実行結果も、コード内の式で使用することができます。 各ワークシート関数の書式は、基本的にはワークシート上で使用する場合と同じ書式です。通常、ワークシート上でExcelの関数を使用する場合は、関数の引数にセル番地を直接記述しています。

=Average(A1:A10)

しかし、VBAのコードで関数の引数にセル番地を指定する場合は、Rangeオブジェクトとして指定する必要があります。

WorksheetFunction.Average(.Range("A1:A10"))

また、ワークシート関数の引数に直接、数値や変数を指定して計算させることもできます。


次の例は、合計計算を実行する統計関数のひとつ、SUM関数をVBAのコード内で実行しています。引数に直接数値を指定した場合と、変数を指定した場合の２通りの方法で計算を行っています。

Sub 合計計算1()
    Result = Application.WorksheetFunction.Sum(1, 2, 3)
    MsgBox "合計は" & Result
End Sub

Sub 合計計算2()
    Dim NumA As Integer
    Dim NumB As Integer
     
    NumA = 100
    NumB = 200 * 5

    Result = Application.WorksheetFunction.Sum(NumA, NumB)

    MsgBox "合計は" & Result

End Sub

このように、統計関数や財務関数をVBAのコード内で使用すれば、複雑な計算式を作成しなくても単純な書式で計算が実行できます。

Link