【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法

photo credit: Keith Allison Yasmany Tomas via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAでバラバラの経費精算書を一つのデータベースにまとめるマクロの作り方についてお伝えしています。

前回の記事はコチラ。

【エクセルVBA入門】マクロでVLookupメソッドを使ってデータを検索する方法

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズの7回目です。今回は、業務で有効なテクニックとしてもう一つ、マスタシートからVLookupメソッドでデータを取得してくる方法についてお伝えしていきます。

VLookupメソッドを使って、マスタからデータを抽出する方法についてお伝えしました。

ですが、VLOOKUP関数に慣れている方はこう思うはずです。

「ヒットしなければエラーになってしまうのでは…？」

そうなんです。

ということで、今回はエクセルVBAのVLookupメソッドを使ったときに発生するエラーを回避する方法についてお伝えします。

では、行ってみましょう！

前回までのおさらい
VLookupメソッドを失敗させてみる
On Errorステートメント
On Error Resume Nextでエラーを無視する
まとめ
連載目次：エクセルVBAで経費データをデータベースに集約する

前回までのおさらい

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
            
            Dim period As String: period = Left(f.Name, 6)
            Dim month As Date: month = DateSerial(Left(period, 4), Right(period, 2), 1)
                                    
            Dim staffId As Long: staffId = Val(Right(Replace(f.Name, ".xlsx", ""), 4))
            Dim staffName As String: staffName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 2, False)
            Dim departmentId As Long: departmentId = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 3, False)
            Dim departmentName As String: departmentName = WorksheetFunction.VLookup(staffId, wsStaff.Range("A:D"), 4, False)
            
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = ""  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

15行目でファイル名からスタッフIDを抜き出しています。

また、16～18行目にVLookupメソッドを使って、社員マスタのシートwsStaffからスタッフIDをキーとして社員名、部署ID、部署名を取得しています。

ですが、以下のような場合はVLookupメソッドが成功しません。

ファイル名が間違っていてスタッフIDが正しく取得できなかった場合
社員マスタに不備がありスタッフIDがヒットしなかった場合

この場合、どうなっちゃうんでしょうか？

VLookupメソッドを失敗させてみる

試しに、ファイル名を以下のように変更してみました。

本来は4桁ないとダメなんですが、3桁になっちゃってます。

これで実行しますと、以下のように「実行時エラー ‘1004’: WorksheetFunction クラスの VLookup プロパティを取得できません。」というエラーが発生します。

「デバッグ」ボタンを押してみましょう。

変数staffIdの内容を見てみると、スタッフIDの値は「0」、マスタに「0」は存在しないので、VLookupメソッドでエラーが発生したということになります。

これを回避する方法についてお伝えしなければなりません。

On Errorステートメント

さて、VBAでは、一般的に実行時エラーの発生はエラーメッセージの表示と、処理の停止を意味します。

しかし、その実行時エラー発生時の処理をコントロールするためのOn Errorステートメントという構文が用意されています。

その使い方は以下の通り、3種類があります。

ステートメント	内容
On Error Goto 行数(またはラベル)	エラー発生時に行数で指定した行番号またはラベルに処理を分岐する
On Error Resume Next	以降エラーが発生しても続行し、次のステートメントに処理を移す
On Error Goto 0	以降のエラーハンドラーを無効にする