VBA WorksheetFunction：エクセル関数の呼び出し

はじめに

Excel VBA マクロで WorksheetFunction からエクセル関数の呼び出し方を紹介します。

WorksheetFunction.Sum() のようにして、エクセルの SUM 関数を使用できます。

エクセルの SUM 関数や COUNT 関数など、VBA に存在しない関数を使用したいときに便利です。

WorksheetFunction

WorksheetFunction.エクセル関数名() のように入力します。WorksheetFunction の後ろにエクセルの関数名を入力して呼び出せます。

「エクセル SUM 関数：合計を求める」を使用して、セルの範囲「A1」～「B2」の合計を取得するには次のようにします。

Dim l As Long
l = WorksheetFunction.Sum(Range("A1:B2"))
Debug.Print(l) ' セル A1 ～ B2 の合計

「エクセル ROUND 関数：四捨五入する」など、VBA では苦手な四捨五入や切り捨て、切り上げが簡単にできるので便利です。

使用できない関数

すべての関数を使用できるわけではありません。Int や Len など VBA に同様の関数が存在しているものなど、一部の関数は使用できません。

WorksheetFunction から使用できない関数を実行するには「Evaluate 関数」を使用して、数式から実行できます。

Dim i As Integer
i = Evaluate("LEN(""ABC"")") ' 数式
Debug.Print(i) ' 3

引数を渡す

「セルを範囲指定して取得する」方法からセルを指定できるので、それをエクセル関数の引数に渡します。

基本はエクセルから関数を使用するのと同じです。

1 つの値を渡す

Range からセルを指定して、その値を渡せます。これはエクセルの数式から関数を使用するときにセル参照するのと同じです。

数値や文字列などを直接入力できます。

Dim d As Double
d = WorksheetFunction.Sum(Range("A1"))       ' セル「A1」
d = WorksheetFunction.Sum(Range("A1").Value) ' セル「A1」の値
d = WorksheetFunction.Sum(10.5)              ' 数値や文字列など

複数の値を渡す

Range からセルの範囲を指定して、その値を渡せます。

配列を入力できます。

対応している引数なら , 区切りで複数の値を渡せます。

Dim l As Long
l = WorksheetFunction.Sum(Range("A1:B2"))    ' セル「A1」～「B2」の範囲
l = WorksheetFunction.Sum(10, 20, 30)        ' 複数の数値

Dim a(2) As Integer
a(0) = 10
a(1) = 20
a(2) = 30
l = WorksheetFunction.Sum(a)                 ' 配列
l = WorksheetFunction.Sum(Array(10, 20))     ' 配列
l = WorksheetFunction.Sum(Range("A1:B2"), 10, 20, a) ' カンマで組み合わせられます。

スピル

1 つの値を受け取る引数にセルの範囲を渡してスピルするとき、「エラー 13 型が一致しません。」が発生します。

スピルを使用するには「Evaluate 関数」を使用するか、Range.Formula2 に数式を入力します。

Dim v As Variant
v = WorksheetFunction.Large(Range("A1:C1"), Array(1, 2)) ' エラー、型が一致しません
v = Evaluate("LARGE(A1:C1, {1, 2})")           ' Evaluate ならスピルできる
Range("A2").Formula2 = "=LARGE(A1:C1, {1, 2})" ' 数式ならスピルできる

不正な引数

その関数が受け取れない引数を渡すと「エラー 1004 アプリケーション定義またはオブジェクト定義のエラーです。」などのエラーが発生します。エクセルの数式から使用したときに #VALUE! などのセルのエラーが表示されるような渡し方は、すべてエラーが発生します。

Dim l As Long
l = WorksheetFunction.Sum("Tips") ' エラー