Excel関数をVBAから呼び出す方法をお探しですね。
Excel VBAでマクロを書いていると、「シート上ではSUMやVLOOKUPで簡単にできるのに、VBAでは同じ計算をどう書けばよいのだろう」と迷う場面があります。
実はVBAでは、`WorksheetFunction`を使うことで、Excelのワークシート関数をマクロ内から呼び出せるんです。
この記事では、マクロの中で関数を使う基本の書き方から、`Application.WorksheetFunction`と`Application.関数名`の違い、エラーが出やすい関数の扱い方まで、初心者にも分かりやすく解説していきます。
広告
マクロの中で関数を使おう！VBAでWorksheetFunctionを使ってExcel関数を呼び出す基本
VBAには、文字列を扱う`Left`や日付を返す`Date`など、最初から用意されている「VBA関数」があります。
一方で、Excelのワークシート上で使う`SUM`、`MAX`、`VLOOKUP`、`MATCH`、`COUNTIF`などは「ワークシート関数」と呼ばれています。
このワークシート関数をVBAから使いたいときに活躍するのが、`Application.WorksheetFunction`です。
たとえば、セル範囲A1:A10の最大値を求めたい場合、ワークシートでは`=MAX(A1:A10)`と入力しますよね。
これをVBAでは次のように書きます。
“`vb
Sub SampleMax()
MsgBox Application.WorksheetFunction.Max(Range(“A1:A10”))
End Sub
“`
このコードを実行すると、A1からA10までの中で最も大きい値がメッセージボックスに表示されます。
ポイントは、Excel関数名の前に`Application.WorksheetFunction.`を付けることです。
`Application`はExcelアプリケーション全体を表すオブジェクトで、`WorksheetFunction`はその中からワークシート関数を使うための入口だと考えると分かりやすいですよ。
関数の引数、つまり関数に渡す値の指定方法は、基本的にはシート上で使うときとほぼ同じです。
そのため、Excel関数に慣れている人ほど、VBAでも短いコードで集計や検索を実装しやすくなります。
ただし、すべてのExcel関数が`WorksheetFunction`から使えるわけではありません。
文字列関数や日付関数の一部は、VBA側に同じ役割の関数が用意されているため、候補に出てこないことがあります。
たとえば、ワークシート関数の`LEFT`に相当する処理は、VBAでは`Left`関数を使います。
VBEで`Application.WorksheetFunction.`まで入力すると、利用できる関数の候補が表示されるので、まずは候補に出るかどうかを確認してみましょう。
`WorksheetFunction`は長い単語ですが、VBEでは途中まで入力して`Ctrl + Space`を押すと入力候補を表示できて、入力ミスを減らせます。
WorksheetFunctionの実用例：SUM・MAX・COUNTIF・VLOOKUPをVBAで使う
`WorksheetFunction`は、単純な集計処理で特に便利です。
たとえば、売上表の合計を求めるなら`Sum`、平均を求めるなら`Average`、最大値を求めるなら`Max`を使えます。
VBAでループを回して1セルずつ加算することもできますが、すでにExcel関数として用意されている処理であれば、`WorksheetFunction`を使ったほうがコードが短く、意図も読み取りやすくなります。
次の例では、A列の売上合計と最大値を取得しています。
“`vb
Sub SampleSummary()
Dim total As Double
Dim maxValue As Double
total = Application.WorksheetFunction.Sum(Range(“A2:A100”))
maxValue = Application.WorksheetFunction.Max(Range(“A2:A100”))
MsgBox “合計：” & total & vbCrLf & “最大値：” & maxValue
End Sub
“`
条件付きで件数を数えたい場合は、`CountIf`もよく使われます。
たとえば、B列に入力されたステータスのうち「完了」が何件あるかを数えるなら、次のように書けます。
ワークシート上では`=COUNTIF(B2:B100,”完了”)`と書く処理を、VBA内でそのまま利用するイメージですね。
“`vb
Sub SampleCountIf()
Dim doneCount As Long
doneCount = Application.WorksheetFunction.CountIf(Range(“B2:B100”), “完了”)
MsgBox “完了件数：” & doneCount
End Sub
“`
検索処理では、`VLookup`や`Match`がよく使われます。
たとえば、商品コードをもとに商品名を取得したい場合、シート上の`VLOOKUP`と同じ考え方で記述できます。
ただし、VLOOKUPは検索値が見つからないとエラーになりやすいので、後で説明するエラー処理を必ず意識する必要があります。
“`vb
Sub SampleVLookup()
Dim productName As String
productName = Application.WorksheetFunction.VLookup( _
Range(“A2”).Value, Range(“D:E”), 2, False)
MsgBox productName
End Sub
“`
ここで注意したいのは、VBAでは関数の戻り値を変数に受け取る場合、通常の関数呼び出しとして`関数名(引数)`の形で書く点です。
`Call`を使って関数を呼び出す書き方もありますが、戻り値を利用する場合には`Call`は使いません。
`WorksheetFunction`は「結果を取得して変数に入れる」使い方が多いので、基本形としては`変数 = Application.WorksheetFunction.関数名(引数)`を覚えておくと迷いにくくなりますよ。
WorksheetFunctionとApplication.関数名の違いを理解しよう
VBAでワークシート関数を呼び出す方法には、`Application.WorksheetFunction.Max(…)`のように書く方法と、`Application.Max(…)`のように`WorksheetFunction`を省略して書く方法があります。
単純な集計関数では、どちらでも同じ結果になることが多いため、初心者にとっては「どちらを使えばよいのか」が分かりにくい部分ですよね。
結論から言うと、通常は`WorksheetFunction`を使う書き方を基本にしつつ、エラー値を戻り値として受け取りたい場面では`Application.関数名`を使うと考えると整理しやすくなります。
違いがはっきり表れるのは、`VLookup`や`Match`のように、条件に一致する値が見つからない可能性がある関数です。
`Application.WorksheetFunction.Match`を使った場合、検索値が見つからないとVBAの実行時エラーになり、処理が止まってしまいます。
一方、`Application.Match`を使った場合は、処理自体は止まらず、戻り値としてExcelのエラー値が返ります。
この違いを知らずに検索系の関数を使うと、データが1件見つからないだけでマクロ全体が停止してしまうことがあるんです。
“`vb
Sub SampleMatchApplication()
Dim ret As Variant
ret = Application.Match(“商品A”, Range(“A:A”), 0)
If IsError(ret) Then
MsgBox “該当データが見つかりません”
Else
MsgBox ret & “行目付近に見つかりました”
End If
End Sub
“`
この例では、戻り値を`Variant`型で受け取っています。
`Variant`は数値や文字列だけでなく、エラー値も受け取れる柔軟な型です。
`Application.Match`の結果がエラー値かどうかを`IsError`で判定し、見つからなかった場合の処理を自分で分岐させています。
検索結果が存在しないことも業務上あり得る場合は、このように「見つからないこと」を異常終了ではなく通常の分岐として扱う設計が向いています。
一方、データが必ず存在する前提で、存在しなければマクロを止めて原因を確認したい場合は、`WorksheetFunction`を使うほうが問題に気づきやすいこともあります。
つまり、どちらが常に正解というより、エラーを止めたいのか、値として受け取って判定したいのかで使い分けるのが実務的です。
集計系は`WorksheetFunction`、検索系やエラーが想定される処理は`Application.関数名`も検討する、という方針にすると安定したコードを書きやすくなります。
エラー対策と使い分けのコツ：止まらないマクロを書くために
`WorksheetFunction`を使うときに最も重要なのは、関数の結果がExcel上で`#N/A`や`#DIV/0!`になるケースを想定しておくことです。
ワークシート上ならセルにエラー表示が出るだけですが、VBAでは実行時エラーとしてマクロが停止する場合があります。
特に`VLookup`、`Match`、割り算を含む計算、範囲指定が不正になる処理では注意が必要です。
業務で使うマクロは、データの入力漏れや検索対象の不足が起こる前提で作るほうが安全ですよ。
代表的な対策は、関数を実行する前に条件を確認する方法です。
たとえば、VLOOKUPで検索する前に`CountIf`で検索値が存在するかを確認しておけば、見つからない場合の実行時エラーを避けられます。
この方法はコードが少し長くなりますが、何を確認してから処理しているのかが読みやすく、後から保守する人にも意図が伝わりやすいのが利点です。
“`vb
Sub SafeVLookup()
Dim key As String
Dim result As Variant
key = Range(“A2”).Value
If Application.WorksheetFunction.CountIf(Range(“D:D”), key) > 0 Then
result = Application.WorksheetFunction.VLookup(key, Range(“D:E”), 2, False)
Range(“B2”).Value = result
Else
Range(“B2”).Value = “未登録”
End If
End Sub
“`
もう一つの方法は、`On Error Resume Next`を使ってエラーを一時的に無視し、直後に`Err.Number`で確認する方法です。
ただし、この書き方は便利な反面、エラーを見落としやすくなるので、使う範囲をできるだけ短くすることが大切です。
処理が終わったら`On Error GoTo 0`で通常のエラー処理に戻す習慣を付けると、予期しない不具合を見逃しにくくなります。
“`vb
Sub ErrorHandledMatch()
Dim pos As Long
On Error Resume Next
pos = Application.WorksheetFunction.Match(“商品A”, Range(“A:A”), 0)
If Err.Number <> 0 Then
MsgBox “商品Aは見つかりませんでした”
Err.Clear
Else
MsgBox pos & “番目に見つかりました”
End If
On Error GoTo 0
End Sub
“`
実務では、処理の目的に合わせて次のように使い分けると分かりやすくなります。
– 合計、平均、最大値など、エラーが起きにくい集計処理は`WorksheetFunction`で簡潔に書く
– 検索値が見つからない可能性がある処理は、事前確認または`Application.Match`などでエラー値を判定する
– `On Error Resume Next`を使う場合は、対象範囲を短くし、必ず`Err.Clear`や`On Error GoTo 0`を忘れずに
また、VBAでは文字列連結に`&`を使う、戻り値を返す独自関数では`関数名 = 値`と書く、オブジェクト代入には`Set`が必要になるなど、Excel関数とは別にVBA特有の作法もあります。
`WorksheetFunction`そのものの使い方は難しくありませんが、VBAの文法と組み合わせることでつまずきやすくなるので、まずは「関数の結果を変数に受け取る」「エラーが出る可能性を考える」という2点を押さえることが大切です。
マクロの中でExcel関数を活用できるようになると、集計、検索、判定の処理を短く書けるようになり、手作業の置き換えだけでなく、読みやすく保守しやすいVBAコードにもつながります。
広告
