VBAからExcelのワークシート関数を使う

Excelのワークシート関数をVBAで使うには？

Excelには便利なワークシート関数があります。VLOOKUP関数やSUM関数など300以上あります。これらのワークシート関数はVBAでも使うことが出来ます。

もしワークシート関数と同じ処理をVBAで実装しようとするとかなりのコード量になり、結構面倒です。そのため、ワークシート関数を使って実行できるものはVBAで同じ処理を実装するのではなく、素直にワークシート関数を利用しましょう。

ただ、ワークシート関数と同じ機能をVBAで実装した場合、ワークシート関数より劇的に高速に動作することもあるため、高速性を求められる場合は自作することも検討しましょう。

ワークシート関数をVBAで使うには４つの方法があります。

Formulaプロパティを使う（セルに数式を入力する方法）
WorksheetFunctionオブジェクトを使う（ワークシート関数を使う方法）
Evaluateメソッドを使う（WorksheetFunctionオブジェクトで処理できないワークシート関数用）★変数を使う場合はこちらがおすすめ
[]を使う（全てのワークシート関数で使える）　★変数を使わない場合はおススメです

それぞれの方法には一長一短がありますので、用途に合わせて使い分けを行う必要があります。以下で特徴や用途などをサンプルコードも含めて説明します。

1. Formulaプロパティでセルに数式を入力する

Formulaプロパティを利用するとセルに数式を入力することができます。

Formulaプロパティの構文

Range.Formula = 数式文字列

数式が単純な場合の使い方

VBAでなくてもセルに数式を入力しても同じ結果が得られます。後述しますが、対象セル範囲が可変の場合はVBAでその部分を実装した上でワークシート関数を利用した方がよい場合があります。

セルに値を入力する場合はRangeオブジェクトのValueプロパティを使いますが、数式の場合はFormulaプロパティを利用します。

Sub FormulaTest()
    Range("A1").Value = 1
    Range("A2").Value = 2
    Range("A3").Formula = "=SUM(A1:A2)"
End Sub

Sub FormulaTest()

Range("A1").Value = 1

Range("A2").Value = 2

Range("A3").Formula = "=SUM(A1:A2)"

End Sub

このコードを実行すると、A1セルに1、A2セルに2が入力され、A3セルにFormulaプロパティでA1セルからA2セルまでの合計を求めるSUM関数の数式が入力されます。

数式が可変状態になる場合の使い方

上のサンプルコードはA1セルとA2セルの合計を求める単純なものですので、VBAでコードを書かなくてもシートにSUM関数を手入力してもいいため、あまりFormulaプロパティを使う必要性を感じません。

ところが、対象セルが可変の場合は手入力では対応が困難な場合があります。

そういう場合はVBAで可変部分の処理を行い、その上でFormulaプロパティで数式を設定することで作業の効率化が図れます。

以下のコードはA列のA1セルから連続しているセルの合計を取得するサンプルコードです。

Sub FormulaTest2()
    Dim i
    Dim s
    Dim cellStart
    Dim cellEnd
    
    cellStart = "A1"
    Range(cellStart).Select
    
    Do
        '// セル値を取得
        s = ActiveCell.Offset(i, 0).Value
        
        '// セル値が未設定の場合はここでループ処理終了
        If (s = "") Then
            Exit Do
        End If
        
        '// 最終セルを更新
        cellEnd = ActiveCell.Offset(i, 0).Address(False, False)
        i = i + 1
    Loop
    
    '// 最終行の次の行に合計値を求めるSUM関数を設定
    ActiveCell.Offset(i, 0).Formula = "=SUM(" & cellStart & ":" & cellEnd & ")"
End Sub

Sub FormulaTest2()

Dim i

Dim s

Dim cellStart

Dim cellEnd

cellStart = "A1"

Range(cellStart).Select

'// セル値を取得

s = ActiveCell.Offset(i, 0).Value

'// セル値が未設定の場合はここでループ処理終了

If (s = "") Then

Exit Do

End If

'// 最終セルを更新

cellEnd = ActiveCell.Offset(i, 0).Address(False, False)

i = i + 1

Loop

'// 最終行の次の行に合計値を求めるSUM関数を設定

ActiveCell.Offset(i, 0).Formula = "=SUM(" & cellStart & ":" & cellEnd & ")"

End Sub

実行前（A1セルからA6セルまで入力している）

実行後（A7セルにSUM関数が設定される）

2. WorksheetFunctionプロパティでワークシート関数を使う

WorksheetFunctionプロパティを使うとワークシート関数の一部を使うことが出来ます。

一部とは言ってもWorksheetFunctionプロパティで使えるワークシート関数は300以上あります。利用可能なワークシート関数については「VBAのWorksheetFunctionプロパティのワークシート関数一覧」をご参照ください。

WorksheetFunctionプロパティの構文

戻り値 = Application.WorksheetFunction.ワークシート関数(引数)

ワークシート関数の引数はワークシート関数ごとに異なります。

なお、引数にセル範囲を指定する場合、ワークシート上で設定する場合は「SUM(A1:C3)」と入力しますがWorksheetFunctionプロパティの場合は「SUM(Range(“A1:C3”)」のようにRangeオブジェクトを使って指定します。

WorksheetFunctionプロパティのサンプルコード

ここではワークシート関数の中でも利用頻度が高いと思われるVLOOKUP関数を例にしたサンプルコードを紹介します。

なお、VLOOKUP関数は事前に用意されているIDと値を持つ表形式のデータから、指定したIDに一致する値を取得する関数です。

以下のようにA列とB列でドメインの表を用意している状態で、C2セルには例として手入力でVLOOKUP関数を設定しています。

手入力でのVLOOKUP関数と同じように、VBAで指定したID（ドメインID）に一致する値（ドメイン）を取得するサンプルです。

Sub WorksheetFunctionTest()
    Dim ret
    Dim key
    
    key = "domain8"
    
    '// A列の"domain8"に一致する値（domain）を取得
    ret = Application.WorksheetFunction.VLookup(key, Range("A2:B9"), 2, False)
    
    Debug.Print ret
End Sub

Sub WorksheetFunctionTest()

Dim ret

Dim key

key = "domain8"

'// A列の"domain8"に一致する値（domain）を取得

ret = Application.WorksheetFunction.VLookup(key, Range("A2:B9"), 2, False)

Debug.Print ret

End Sub

コードを実行するとdomain8に一致するB列の値「.co.jp」が取得できます。

3. Evaluateメソッドを使う

上記のWorksheetFunctionプロパティでは多くのワークシート関数が使えますが、使えないワークシート関数も存在します。

例えばDATE関数やDATEDIF関数などです。

DATE関数はワークシート関数とVBA関数の両方がありますが、ワークシート関数のDATE関数はシリアル値を返しますが、VBA関数のDATE関数は現在のシステム日付を返すため機能が異なります。

DATEDIF関数はワークシート関数にしかありませんがWorksheetFunctionプロパティでは使用できません。

このように、同名関数やWorksheetFunctionプロパティで利用できない関数などのワークシート関数を使いたい場合はApplicationオブジェクトのEvaluateメソッドを利用します。

Evaluateメソッドの構文

戻り値 = Application.Evaluate(ワークシート関数文字列)

Evaluateメソッドのサンプルコード

Evaluateメソッドの引数にワークシート関数を設定しますが、その際は文字列として設定することになります。

また、セル範囲の指定はWorksheetFunctionプロパティとは異なり、Rangeオブジェクトの指定は不要です。「SUM(A1:C3)」であればそのまま文字列化して「Evaluate(“SUM(A1:C3)”)」と書きます。

先のWorksheetFunctionプロパティでのVLookup関数をEvaluateメソッドを使って書くと以下のようになります。

Sub EvaluateTest1()
    Dim ret
    Dim key
    
    key = "domain8"
    
    ret = Application.Evaluate("VLookup(""" & key & """, A2:B9, 2, False)")
    
    Debug.Print ret
End Sub

Sub EvaluateTest1()

Dim ret

Dim key

key = "domain8"

ret = Application.Evaluate("VLookup(""" & key & """, A2:B9, 2, False)")

Debug.Print ret

End Sub

以下はEvaluateメソッドでワークシート関数のDATEDIF関数を利用するサンプルです。

Sub EvaluateTest2()
    Dim diff
    
    diff = Application.Evaluate("DATEDIF(""2018/1/1"", TODAY(), ""D"")")
    Debug.Print diff
End Sub

Sub EvaluateTest2()

Dim diff

diff = Application.Evaluate("DATEDIF(""2018/1/1"", TODAY(), ""D"")")

Debug.Print diff

End Sub

実行すると2018/1/1から今日までの日数を出力します。

Evaluateメソッドの引数全体が文字列として扱う必要があるため、ワークシート関数の引数に文字列がある場合、文字列のダブルクォーテーションを２つ繋げてエスケープする必要があります。

4. []を使う

Evaluateメソッドと同じように全てのワークシート関数を角括弧の[]で書くことも出来ます。

[]の構文

戻り値 = [ワークシート関数]

ワークシート関数部分は文字列ではなく、数式の=を除いた部分を書きます。

[]のサンプルコード

ワークシート関数が複雑に組み合わさった数式の場合は[]を使った方が実装がラクになることが多いです。

その理由は、セルの数式の場合は「=SUM(A1:A3)」のように書きますが、=を外して「[SUM(A1:A3)]」と実装することが出来るためです。

Evaluateメソッドの場合はワークシート関数の部分を文字列にする必要があったためダブルクォーテーションのエスケープが必要でしたが、[]で書く場合はセルに数式を書く場合と同じ書き方が出来ます。

以下はEvalueateメソッドのサンプルコードを[]で書いた場合のサンプルです。

Sub EvaluateTest3()
    Dim diff
    
    diff = [DATEDIF("2018/1/1", TODAY(), "D")]
    Debug.Print diff
End Sub

Sub EvaluateTest3()

Dim diff

diff = [DATEDIF("2018/1/1", TODAY(), "D")]

Debug.Print diff

End Sub

4行目の書き方をセルに書いた場合の数式はコードとほとんど同じになります。以下はセルの数式ですが、=があるかないかの違いしかありません。

入り組んだワークシート関数をVBAで実行する場合は[]を使った方がラクなため、こちらをお勧めします。

Excelのワークシート関数をVBAで使うには？

1. Formulaプロパティでセルに数式を入力する

Formulaプロパティの構文

数式が単純な場合の使い方

数式が可変状態になる場合の使い方

2. WorksheetFunctionプロパティでワークシート関数を使う

WorksheetFunctionプロパティの構文

WorksheetFunctionプロパティのサンプルコード

3. Evaluateメソッドを使う

Evaluateメソッドの構文

Evaluateメソッドのサンプルコード

4. []を使う

[]の構文

[]のサンプルコード

関連記事