みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについて連載でお伝えしています。
前回の記事はコチラ。
ほぼ、完成して来ました。
続いてですが、実際に作る請求書に載せる取引先名、郵便番号、住所を「取引先マスタ」から引っ張って来たいので、プログラム内でVlookupを使います。
しかし、「取引先マスタ」に検索値が存在していなかったらエラーが発生しVBAプログラムが停止をしてしまいます。
今回はエクセルVBAでVlookupを使ったときに発生し得るエラーを回避する方法についてお伝えします。
前回までに作ったプログラム
前回までに作成したプログラムはこちらです。
Sub 請求書作成()
’***** 各シートの準備 *****
Dim wsData As Worksheet '「請求データ」シート
Set wsData = ThisWorkbook.Worksheets("請求データ")
Call wsDataSort '「請求データ」シートを取引先名、納品日でソート
Dim startRow As Long 'コピー範囲の最初の行と最終行を格納
startRow = 2
Dim wsInvoice As Worksheet '「請求書ひな形」シート
Dim strFile As String '保存先フォルダパス＆ファイル名（拡張子抜き）
Dim i As Long
i = 2
Do While wsData.Cells(i, 1).Value <> ""
i = i + 1
'***** 「請求データ」の年月×取引先の塊ごとに処理 *****
If firstDay(wsData.Cells(i, 1).Value) <> firstDay(wsData.Cells(startRow, 1).Value) Or _
wsData.Cells(i, 2).Value <> wsData.Cells(startRow, 2).Value Then
'***** 年月×取引先ごとに請求書ファイルの作成、シートの調整
Workbooks.Add '新規ワークブックを作成
ThisWorkbook.Worksheets("請求書ひな形").Copy before:=ActiveWorkbook.Sheets(1) '新規ワークブックのsheet1の前にひな形をコピー
Set wsInvoice = ActiveSheet 'コピーしたシートを変数にセット
wsInvoice.Name = "請求書" 'シート名を変更
Application.DisplayAlerts = False '確認メッセージをオフにする
ActiveWorkbook.Worksheets("Sheet1").Delete 'Sheet1を削除する
Application.DisplayAlerts = True '確認メッセージをオンにする
strFile = ThisWorkbook.Path & "\" & Format(wsData.Cells(startRow, 1).Value, "YYYYMM") & "請求書_" & wsData.Cells(startRow, 2).Value & "御中"
'***** PDF出力設定 *****
With wsInvoice.PageSetup
.Zoom = False '倍率をクリア
.FitToPagesWide = 1 '横方向に1ページに収める
.FitToPagesTall = 1 '縦方向に1ページに収める
.CenterHorizontally = True '水平方向に中央配置
.TopMargin = Application.CentimetersToPoints(1) '上マージンを1cm
.BottomMargin = Application.CentimetersToPoints(1) '下マージンを1cm
End With
'***** 「請求書」シートを作成 *****
wsData.Range(wsData.Cells(startRow, 3), wsData.Cells(i - 1, 5)).Copy wsInvoice.Range("A21") '範囲をコピペ
'***** 請求書ファイルをPDF出力し元のファイルを保存して閉じる *****
wsInvoice.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFile & ".pdf" '選択したシートをPDF出力
ActiveWorkbook.Close savechanges:=True, Filename:=strFile & ".xlsx" 'アクティブブックを名前を付けて保存して閉じる
startRow = i
End If
Loop
End Sub
ちょっと長いですがこれまでの集大成なのでご容赦下さい。
内部で使用している関数wsDataSortについてはコチラ
firstDay()に関ししてはコチラの記事をご覧ください。
Vlookupで取引先情報を取得する
今回のお題ですが、請求書の以下の場所に送り先の取引先名、郵便番号、住所を表示していたいと思います。
取引先名はわかっているとして、それ以外の郵便番号、住所1、住所2を取引先名をキーにして以下「取引先シート」からVlookupで引っ張って来ます。
エクセルVBAでのVlookupの使い方
エクセルVBAでVlookupを使うときは
とします。
今回の場合、WordsheetオブジェクトがwsInvoice、検索範囲となるRangeオブジェクトを
Dim rngAccount As Range '「取引先マスタ」検索範囲
Set rngAccount = ThisWorkbook.Worksheets("取引先マスタ").Range("A:D")
としてrngAccountにセットしたとすると
strAccount = wsData.Cells(startRow, 2).Value '取引先名
wsInvoice.Range("A3").Value = strAccount & " 御中" '取引先名
wsInvoice.Range("A5").Value = "〒" & WorksheetFunction.VLookup(strAccount, rngAccount, 2, False) '郵便番号
wsInvoice.Range("A6").Value = WorksheetFunction.VLookup(strAccount, rngAccount, 3, False) '住所1
wsInvoice.Range("A7").Value = WorksheetFunction.VLookup(strAccount, rngAccount, 4, False) '住所2
とすることで、各情報をVlookupで取り出して表示することができます。
Vlookupで検索値がない場合
しかし、取引先マスタの取引先名に半角スペースが入っているなど検索がうまくいかなかった場合はどうなるでしょうか？
試しに、取引先マスタの「株式会社ホゲホゲ」を「株式会社 ホゲホゲ」と、ちょっといたずらして半角スペースを入れちゃいます。
これで実行しますと
やっぱりエラーが出ちゃいました。
これを回避する方法についてお伝えしなければなりません。
On Error Resume Nextでエラーを無視する
エラーが発生してもそれを無視する魔法の言葉があります。
この一文を入れた以降は、エラーを無視してエラーが発生した次の行から処理を続行します。
これを先ほどのVlookup命令の前に入れてあげますと、プログラムは最後まで実行され、本来エラーが発生している取引先については
というように、郵便番号、住所はデフォルトのままになっています。
でも、ちょっとエラーが発生したかどうかが分かりづらいですね。
このまま取引先に送ってしまわないか心配になっちゃいます。
Errオブジェクト
そんな時のためにErrオブジェクトを使って、もう少しエラーが発生したことをわかりやすくしてあげましょう。
Errオブジェクトには、エラーが発生したときにどんなエラーが発生したのかという情報が格納されます。
これを使ってエラーが発生したときに何らかのアクションを起こすという処理を追加することができます。
Err.Numberでエラーが発生したときにメッセージを表示
ErrオブジェクトのNumberプロパティには初期値は0が格納されていて、エラーが発生するとエラーの種類に応じて0より大きい番号が格納されます。
ですから例えば
If Err.Number <> 0 Then 'エラーが発生したときにメッセージを表示
MsgBox strAccount & "の情報を取得するVlookupでエラーが発生しました"
End If
とすれば、この構文の前にエラーが発生していればエラーメッセージが表示されます。
ClearメソッドでErrオブジェクトをリセットする
しかし、エラーが発生したときのErrオブジェクトのNumberプロパティは、別のエラーが発生しない限りはプロシージャが終了するまでリセットされません。
従って、今回のプログラムではDo Whileの処理内にありますので、直前でエラーが発生しようがしまいがメッセージが表示されてしまうという仕組みになってしまいます。
そんなときは、ErrオブジェクトのClearメソッドを使ってあげます。
この命令を入れておくことで、Errオブジェクトを初期状態にリセットできます。
前述のIf文内を
If Err.Number <> 0 Then 'エラーが発生したときにメッセージを表示
MsgBox strAccount & "の情報を取得するVlookupでエラーが発生しました"
Err.Clear
End If
としてあげればエラーメッセージが表示されるたびにエラーがクリアされます。
まとめ
エクセルVBAでVlookupを使ったときのエラーを回避する方法についてお伝えしました。
エクセルVBAの場合は、ユーザーがシートやデータをいじれくれる場合が多いので、人為的なミスが発生する要因がけっこう多かったりします。
ちょっとの文字間違いでもスペースのありなしでもプログラムとしてはエラーが出る要因になりますので、よく作成するVBAプログラムとそれを使う際の状況と業務プロセスなどを予想して、先回りでエラー処理を入れておけると良いですよね。
また機会があれば、その他のエラー処理についてもお知らせできればと思います。
次回はいよいよ脱エクセルVBAの集大成、請求書作成プログラムの仕上げをしていきます。
どうぞお楽しみに！
連載目次：【脱エクセルVBA初心者】請求書を自動で作る際のテクニック集
「請求書を自動で作る」というニーズはとても多いですから、その際に便利な機能やテクニックをシリーズにてお伝えしていきます。脱エクセルVBA初心者をターゲットにしています。- 【脱エクセルVBA初心者】新規のワークブックを作成、シートのコピーと名前の変更
- 【脱エクセルVBA初心者】確認メッセージを表示させずにワークシートを削除する方法
- 【脱エクセルVBA初心者】PDFでの出力とファイル名の指定保存そして閉じる
- 【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え
- 【脱エクセルVBA初心者】コピペはRangeが効率的なのでその範囲を見つけるプログラム
- 【脱エクセルVBA初心者】Copyメソッドで範囲まるごとを一発でコピペする
- 【脱エクセルVBA初心者】Vlookupで出てしまうエラーをいい感じに回避する方法
- 【脱エクセルVBA初心者】請求書作成マクロの全体おさらいと総仕上げ
