[Excel VBA]いろんな関数をマクロで使おう！エラー回避も考慮！

VBA(Excel)

2021.12.012024.12.20

この記事は約4分で読めます。

記事内に広告が含まれていることがあります。

今回はExcelのセルで使う数式や関数をVBAでも扱う方法を紹介します。

方法としては大きく2種類あります。
　１．WorksheetFunctionオブジェクトを使う方法
　２．Evaluateメソッドを使う方法

これらは、使う場面や用途によって使い分ける事が多いですが、最も使い分ける盤面としては関数エラーを考慮する必要があるときにEvaluateメソッドを使う盤面が多くなるでしょう。

WorksheetFunctionオブジェクト
Evaluateメソッド

WorksheetFunctionオブジェクト

使い方

まずはWorksheetFunctionを使っていきます。
ちょっとした関数を使いたいときはWorksheetFunctionのほうが便利で、コードの見た目も良いのでおすすめです。

以下、コードサンプル

Sub MaxNumber()
    '結果は5
    MsgBox Application.WorksheetFunction.Max(2, 5, 3)
End Sub

Sub MaxNumber()

'結果は5

MsgBox Application.WorksheetFunction.Max(2, 5, 3)

End Sub

もし関数を入れ子にする場合は、WithでWorksheetFunctionを囲いましょう。
見た目がスッキリします。

Sub MaxNumber()
    '結果は5
    With Application.WorksheetFunction
        MsgBox .Max(2, 5, .Min(8, 4, 6))
    End With
End Sub

Sub MaxNumber()

'結果は5

With Application.WorksheetFunction

MsgBox .Max(2, 5, .Min(8, 4, 6))

End With

End Sub

WorksheetFunctionの大きなメリットは、VBAで設定した変数や定数などを関数の引数としてそのまま使える点にあります。

Sub MaxNumber()

    '定数
    Const LNG_SUMPLE1   As Long = 3

    '変数
    Dim lngSumple2      As Long
    
    '5
    lngSumple2 = 1 + 4
    
    '結果は5
    MsgBox Application.WorksheetFunction.Max(2, lngSumple2, LNG_SUMPLE1)
    
End Sub

Sub MaxNumber()

'定数

Const LNG_SUMPLE1 As Long = 3

'変数

Dim lngSumple2 As Long

lngSumple2 = 1 + 4

'結果は5

MsgBox Application.WorksheetFunction.Max(2, lngSumple2, LNG_SUMPLE1)

End Sub

しかし、WorksheetFunctionオブジェクトにもデメリットがあります。

もしWorksheetFunctionを使った時にエラー値（#N/A等）が返るとVBAエラーが起きて処理が中断してしまいます。そのため、エラー値が起き得ないときに使用することがポイントです。VLookUp関数などの検索系の関数を使うのは控えたほうがいいかもしれません。

Evaluateメソッド

使い方

次にEvaluateメソッドを使っていきましょう。
Evaluateでは基本的に関数を含めた文字列をダブルクオーテーション囲みます。

早速以下、コードサンプル

Sub MaxNumber()

    '結果は5
    MsgBox Evaluate("Max(2,5,3)")
    
    '入れ子
    '結果は5
    MsgBox Evaluate("Max(2,5,Min(8,4,6))")
    
End Sub

Sub MaxNumber()

'結果は5

MsgBox Evaluate("Max(2,5,3)")

'入れ子

'結果は5

MsgBox Evaluate("Max(2,5,Min(8,4,6))")

End Sub

Evaluateは省略できます。省略するときは[]で囲みます。変数などを使用しない時に使えます。

Sub MaxNumber()

    '結果は5
    MsgBox [Max(2,5,3)]
    
    '入れ子
    '結果は5
    MsgBox [Max(2,5,Min(8,4,6))]
    
End Sub

Sub MaxNumber()

'結果は5

MsgBox [Max(2,5,3)]

'入れ子

'結果は5

MsgBox [Max(2,5,Min(8,4,6))]

End Sub

Evaluateメソッドのデメリットとしては変数を関数の引数として使う時に文字列との境に&を入れる必要があります。そのため、コードが分かりにくくなったり、書き方が難しくなったりします。

Sub MaxNumber()

    '定数
    Const LNG_SUMPLE1   As Long = 3
    
    '変数
    Dim lngSumple2      As Long
    
    '5
    lngSumple2 = 1 + 4

    '結果は5
    MsgBox Evaluate("Max(2," & lngSumple2 & "," & LNG_SUMPLE1 & ")")
    
End Sub

Sub MaxNumber()

'定数

Const LNG_SUMPLE1 As Long = 3

'変数

Dim lngSumple2 As Long

lngSumple2 = 1 + 4

'結果は5

MsgBox Evaluate("Max(2," & lngSumple2 & "," & LNG_SUMPLE1 & ")")

End Sub

しかしEvaluateには大きなメリットがあります。エラー回避ができる点です。
Excelの関数にはエラーになった場合にエラー値ではなく任意の文字を返すIfError関数や、エラーの場合にTrueを返すIsError関数があります。これらをうまく使うことでエラー回避ができるのです。

まずは以下のコードを見てみましょう。

Sub MaxNumber()
    'lngSumple2、LNG_SUMPLE1はただの文字列なのでエラー
    MsgBox Evaluate("Max(2,lngSumple2,LNG_SUMPLE1)")
End Sub

Sub MaxNumber()

'lngSumple2、LNG_SUMPLE1はただの文字列なのでエラー

MsgBox Evaluate("Max(2,lngSumple2,LNG_SUMPLE1)")

End Sub

この場合、エラー値が返るので処理が止まってしまいます。(Max関数に文字列を入れた為。)

しかし、以下のコードのようにIfError関数を入れてエラー時に任意の文字列を返すことでエラー回避することができます。（ダブルクオーテーションに囲まれた文字列の中で文字列としてダブルクオーテーションを使いたい場合はダブルクオーテーションを２つ続けて書きます。）

Sub MaxNumber()
    MsgBox Evaluate("IfError(Max(2,lngSumple2,LNG_SUMPLE1),""エラーだよ！！！"")")
End Sub

Sub MaxNumber()

MsgBox Evaluate("IfError(Max(2,lngSumple2,LNG_SUMPLE1),""エラーだよ！！！"")")

End Sub

そのため、エラーが出た場合の処理を作ってあげることでうまくエラー回避をすることができる且つ、見やすいコードを作ってあげることが可能となります。

Sub MaxNumber()
    'IsError ・・・ エラーが出た場合にTrue(真)を返す
    If Evaluate("IsError(Max(2,lngSumple2,LNG_SUMPLE1))") Then
        'エラーが出た場合の処理
        MsgBox "エラーが出た"
    Else
        'エラーではなかった場合の処理
        MsgBox "エラーは出なかった"
    End If
End Sub

Sub MaxNumber()

'IsError ・・・エラーが出た場合にTrue(真)を返す

If Evaluate("IsError(Max(2,lngSumple2,LNG_SUMPLE1))") Then

'エラーが出た場合の処理

MsgBox "エラーが出た"

Else

'エラーではなかった場合の処理

MsgBox "エラーは出なかった"

End If

End Sub

応用編

Evaluateを応用することでいろんな事ができます。
過去記事がいくつかあるのでぜひ見てみてください。

シートの存在確認

たった１行でできるシートの存在確認

名前の定義の存在確認

こちらは(なぜか)WorksheetFunctionでは使えないIsRef関数を使って名前の定義の存在を確認しています。

いかがだったでしょうか？
実際に使っていくことでコツが掴めるようになると思うのでぜひどちらの方法も使ってみてください！

WorksheetFunctionオブジェクト

使い方

Evaluateメソッド

使い方

応用編

シートの存在確認

名前の定義の存在確認

---コメント---