【Excel】VBAのWorksheetFunction経由でExcel関数を呼ぶ!マクロ内でVLOOKUPを使う方法

Excelマクロを開発する際、Excelの標準関数をVBAから利用したい場面は多いでしょう。

特にVLOOKUP関数は、データ集計や参照で頻繁に使用されます。

しかし、VBAから直接Excel関数を呼び出すと、エラーの原因となることがあります。

この記事では、VBAのWorksheetFunctionオブジェクトを経由してExcel関数を安全に呼び出す方法を解説します。

WorksheetFunctionを使うことで、VLOOKUP関数をマクロ内で確実に実行できます。

WorksheetFunctionオブジェクトの役割と利点

Excel VBAでは、標準のExcel関数をマクロのコード内で利用するための仕組みが用意されています。

それがWorksheetFunctionオブジェクトです。

WorksheetFunctionオブジェクトを使用することで、Excelのセルに直接関数を入力するのと同じように、VBAコード内からExcelの豊富な関数群を呼び出すことができます。

これにより、複雑な計算やデータ操作をVBAで効率的に実装することが可能になります。

WorksheetFunctionオブジェクトを利用する主な利点は、Excelの計算エンジンをそのまま活用できる点にあります。

VBAの標準機能だけでは実現が難しい高度な計算も、Excel関数を使えば容易に実装できます。

例えば、VLOOKUP関数やSUMIFS関数、IFERROR関数など、日常的に使用する多くの関数がWorksheetFunctionを通じて利用可能です。

これにより、VBAコードの可読性と保守性を向上させることができます。

VBAからVLOOKUP関数を呼び出す基本構文

VBAからExcelのVLOOKUP関数を呼び出すには、WorksheetFunctionオブジェクトの後にVLOOKUP関数名を指定します。

基本的な構文は以下の通りです。

WorksheetFunction.VLookup(Arg1, Arg2, Arg3, Arg4)

各引数は、ExcelのVLOOKUP関数と同じ意味を持ちます。

Arg1は検索値、Arg2は検索範囲、Arg3は結果を返す列番号、Arg4は検索方法(TRUEまたはFALSE)を指定します。

この構文を理解することで、VBAコード内でVLOOKUP関数を効果的に使用できます。

例えば、特定のIDに対応する商品名を取得する際に利用できます。

ここで、各引数の詳細について説明します。

Arg1(検索値):検索したい値を指定します。これはセル参照、変数、または直接入力された値でも構いません。

Arg2(検索範囲):検索値が含まれる列と、結果を返す列を含む範囲を指定します。通常はRangeオブジェクトやCellsコレクションで指定します。

Arg3(列番号):検索範囲内で、結果を返したい列の番号を指定します。左端の列を1として数えます。

Arg4(検索方法):完全一致(FALSE)または近似一致(TRUE)を指定します。通常は完全一致(FALSE)が推奨されます。

VLOOKUP関数をマクロで実行する具体的な手順

実際にVBAマクロ内でWorksheetFunction.VLookupを使用する手順を説明します。

ここでは、シート1のA列に商品ID、B列に商品名がリストされており、別のシート(例えばシート2)のA列に入力された商品IDに対応する商品名を、シート1から検索してシート2のB列に表示するシナリオを想定します。

1. VBAエディタを開く
   ExcelでAlt + F11キーを押してVBAエディタ(Microsoft Visual Basic for Applications)を開きます。
2. 標準モジュールを挿入する
   VBAエディタのメニューから「挿入」>「標準モジュール」を選択して、新しいモジュールを作成します。
3. VBAコードを入力する
   作成した標準モジュールに、以下のVBAコードを入力します。このコードは、シート2のA列にある各商品IDを検索し、シート1のB列に対応する商品名を取得してシート2のB列に書き出します。

“`vb
Sub VLookupExample()
Dim wsSource As Worksheet ‘ 検索元シート
Dim wsTarget As Worksheet ‘ 検索結果表示シート
Dim lastRowSource As Long ‘ 検索元シートの最終行
Dim lastRowTarget As Long ‘ 検索結果表示シートの最終行
Dim i As Long ‘ ループカウンタ
Dim lookupValue As Variant ‘ 検索値
Dim result As Variant ‘ 検索結果

‘ シートの設定
Set wsSource = ThisWorkbook.Sheets(“シート1”) ‘ 検索元のシート名を指定
Set wsTarget = ThisWorkbook.Sheets(“シート2”) ‘ 検索結果を表示するシート名を指定

‘ 各シートの最終行を取得
lastRowSource = wsSource.Cells(Rows.Count, “A”).End(xlUp).Row
lastRowTarget = wsTarget.Cells(Rows.Count, “A”).End(xlUp).Row

‘ 検索結果表示シートのB列にヘッダーを設定(任意)
wsTarget.Cells(1, “B”).Value = “商品名”

‘ シート2のA列の各行に対してVLOOKUPを実行
For i = 1 To lastRowTarget
lookupValue = wsTarget.Cells(i, “A”).Value ‘ シート2のA列の値を検索値とする

‘ 検索値が空白でない場合のみVLOOKUPを実行
If Not IsEmpty(lookupValue) Then
On Error Resume Next ‘ エラーが発生しても続行する
‘ WorksheetFunction.VLookup を使用して検索
result = WorksheetFunction.VLookup(lookupValue, wsSource.Range(“A1:B” & lastRowSource), 2, False)
On Error GoTo 0 ‘ エラーハンドリングを元に戻す

‘ 検索結果をシート2のB列に書き出す
If Not IsError(result) Then
wsTarget.Cells(i, “B”).Value = result ‘ 検索成功時
Else
wsTarget.Cells(i, “B”).Value = “見つかりません” ‘ 検索失敗時
End If
Else
wsTarget.Cells(i, “B”).Value = “”
End If
Next i

MsgBox “VLOOKUP処理が完了しました。”, vbInformation

End Sub
“`

このコードでは、以下の処理が行われています。

まず、検索元シート(シート1)と検索結果表示シート(シート2)を変数に設定しています。

次に、それぞれのシートの最終行を取得し、ループ処理の範囲を決定します。

ループ内では、シート2のA列にある各値を検索値として、シート1のA1からB列の最終行までの範囲でVLOOKUP関数を実行しています。

結果はシート2のB列に書き込まれます。

エラー処理: #N/A エラーへの対応

VLOOKUP関数を使用する際、最もよく遭遇するエラーの一つが#N/Aエラーです。

これは、指定した検索値が検索範囲に見つからなかった場合に発生します。

VBAでWorksheetFunction.VLookupを使用した場合、この#N/Aエラーが発生すると、マクロの実行が停止してしまうことがあります。

これを防ぐためには、適切なエラー処理を実装する必要があります。

VBAで#N/Aエラーを処理する一般的な方法は、`On Error Resume Next` ステートメントを使用することです。

このステートメントをエラーが発生する可能性のあるコードの直前に置くと、エラーが発生してもマクロの実行は停止せず、次の行に進みます。

エラーが発生したかどうかは、`Err`オブジェクトのプロパティや`IsError`関数で確認できます。

エラーが発生しなかった場合は、検索結果をそのまま利用します。

エラーが発生した場合は、あらかじめ用意しておいたメッセージ(例:「見つかりません」)をセルに書き込むなどの処理を行います。

上記のコード例では、`On Error Resume Next`と`On Error GoTo 0`を使用してエラーハンドリングを行っています。

具体的には、`WorksheetFunction.VLookup`を実行する直前に`On Error Resume Next`を記述し、エラーが発生してもマクロが停止しないようにしています。

その後、`IsError(result)`関数を使って、`result`変数に格納された値がエラーであるかどうかを判定しています。

もしエラーでなければ(つまり、検索が成功すれば)、その結果をセルに書き込みます。エラーであれば、「見つかりません」という文字列をセルに書き込みます。

最後に`On Error GoTo 0`で、通常の(エラー発生時に停止する)エラーハンドリングに戻しています。

WorksheetFunction.VLookupとApplication.VLookupの違い

VBAからExcel関数を呼び出す方法として、WorksheetFunctionオブジェクト以外に、Applicationオブジェクトを使用する方法もあります。

Application.VLookupもVLOOKUP関数を呼び出すことができますが、両者には重要な違いがあります。

WorksheetFunction.VLookupは、Excelのワークシート上で関数が計算されるのと同様に動作します。

そのため、ワークシート関数が返すエラー(#N/A、#VALUE!など)をそのまま返します。

一方、Application.VLookupは、ワークシート関数がエラーを返した場合、VBAのエラー(Runtime Error 1004など)として扱います。

この違いは、エラー処理の方法に影響します。

WorksheetFunction.VLookupで#N/Aエラーが発生した場合、前述のように`On Error Resume Next`と`IsError`関数で処理するのが一般的です。

しかし、Application.VLookupで同様のエラーが発生した場合は、`On Error Resume Next`で捕捉し、`Err.Number`プロパティなどでエラーコードを確認して処理する必要があります。

一般的には、エラーが発生しないことが期待される場合や、エラー発生時にマクロを停止させたい場合はWorksheetFunctionを、エラー発生時にVBAのエラーとして捕捉して細かく制御したい場合はApplicationを使用することが推奨されます。

ただし、多くの場合、WorksheetFunctionを使用する方が、Excelの関数としての挙動に近く、直感的に理解しやすいでしょう。

VLOOKUP以外の関数もWorksheetFunctionで利用可能

WorksheetFunctionオブジェクトは、VLOOKUP関数だけでなく、Excelで利用可能なほぼ全ての標準関数に対応しています。

これにより、VBAコード内で複雑な計算やデータ処理を、Excelの強力な関数群をそのまま利用して記述できます。

例えば、SUMIFS関数、AVERAGEIFS関数、COUNTIFS関数などの条件付き集計関数もWorksheetFunctionを通じて利用可能です。

これらの関数は、複数の条件に基づいてデータを集計する際に非常に役立ちます。

また、IFERROR関数をWorksheetFunction経由で呼び出すことで、VLOOKUP関数などのエラー発生時に表示されるエラー値を、より分かりやすい文字列や特定の値に置き換えることができます。

具体的な例として、SUMIFS関数をWorksheetFunctionで呼び出す場合を考えてみましょう。

例えば、特定の月の売上合計を計算したい場合、以下のようなコードで実現できます。

“`vb
Sub SumIfsExample()
Dim ws As Worksheet
Dim totalSales As Variant

Set ws = ThisWorkbook.Sheets(“売上データ”) ‘ 売上データシート

‘ 2023年1月の売上合計を計算
totalSales = WorksheetFunction.SumIfs(ws.Range(“C:C”), ws.Range(“A:A”), “2023/01/01”, ws.Range(“B:B”), “商品A”)

MsgBox “1月の商品Aの売上合計: ” & totalSales

End Sub
“`

このコードは、売上データシートのA列に日付、B列に商品名、C列に売上金額が記録されていると仮定しています。

WorksheetFunction.SumIfsを使って、特定の日付(2023/01/01)かつ特定の商品名(商品A)に該当する売上金額の合計を計算しています。

このように、WorksheetFunctionを利用することで、Excelの高度な関数機能をVBAコードに容易に組み込むことができます。

まとめ

本記事では、Excel VBAでWorksheetFunctionオブジェクトを使用してVLOOKUP関数を呼び出す方法を解説しました。

WorksheetFunctionを経由することで、Excelの関数をマクロ内で安全かつ効率的に利用できます。

特に、#N/AエラーなどのExcel関数固有のエラーを適切に処理する方法は、マクロの安定稼働に不可欠です。

今回紹介したWorksheetFunction.VLookupの構文とエラー処理の方法を理解することで、より強力で信頼性の高いExcelマクロを作成できるようになります。

ぜひ、ご自身の業務改善のために、このテクニックを活用してください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】