エクセルVBAで中級から上級にステップしようとアップするシリーズの3回目です。今回はVBAによる自動化で頻発する、エクセルブックを開く動作の周辺のエラー撲滅とユーザビリティの向上を目指します。
この記事は中級です。
レベルについてはExcel VBAの実力(レベル)を定義してみる 初心者~三段をご参照ください。
目次
ユーザーが操作する場面はエラー対策を入念に
VBAで自動化ツールを作成する場合、メッセージボックスやダイアログボックスを使って対話的に動かせるツールを作る事ができます。ユーザーが操作できると言うことは、その分だけ操作に応じた対策を取っておく必要があります。
まずは普通にファイル選択をしてもらうダイアログボックスを出しましょう。エクセルVBA中級者から上級者へのステップアップ②で触れたようにキャンセルや×を推されたときの対策を盛り込んだ形で作成します。
今回、開くファイルのサンプルはこのような売上実績データとします。
それでは開きます。
Sub ブックを開く中級編() Dim msg As Long 'メッセージボックスの戻り値を代入する Dim full_path As String '選択されたファイルのフルパスを代入する msg = MsgBox("売上実績データを選択して下さい", vbOKCancel, "売上管理ツール") full_path = Application.GetOpenFilename("売上実績データ, *.xlsx") End Sub
ここまでは最低限の記述です。ここから、下記の対策を順に盛り込んでいきます。
- ファイル選択を促すメッセージボックスでキャンセルを押された場合の対策
- すでに開かれているブック対策
- 開いたブックが正しくない場合の対策
- 再度選択させる画面に戻るようにして利便性UP
ファイル選択を促すメッセージボックスでキャンセルを押された場合の対策
このシリーズの2回目で解説したようにメッセージボックス関数の戻り値を使います。
Sub ブックを開く中級編() Dim msg As Long 'メッセージボックスの戻り値を代入する Dim full_path As String '選択されたファイルのフルパスを代入する msg = MsgBox("売上実績データを選択して下さい", vbOKCancel, "売上管理ツール") 'OK以外以外でメッセージボックスを開いたときの対策 If msg <> vbOK Then MsgBox "キャンセルしました", vbInformation, "売上管理ツール" End End If full_path = Application.GetOpenFilename("売上実績データ, *.xlsx") Workbooks.Open Filename:=full_path End Sub
Endで実行中のマクロを終了することができます。vbOKの所を、If msg<>1 then としても構いませんが、vbOKとした方が見やすくて良いと思います。
ダイアログボックスでキャンセルや×を推されたときの対策
次にダイアログボックスでキャンセルを押されたときの対策を記述します。
ファイルを選択するタイミングでキャンセルや×を押すと、Falseが返ります。本来、Falseは真偽値という特別な値なのですが、今回full_pathという変数をString型で宣言しているので、文字列としてのFalseにVBEが自動で変換してくれています。なので、文字列としてのFalseの時をトラップしてあげればOKです。
Sub ブックを開く中級編() Dim msg As Long 'メッセージボックスの戻り値を代入する Dim full_path As String '選択されたファイルのフルパスを代入する msg = MsgBox("売上実績データを選択して下さい", vbOKCancel, "売上管理ツール") 'OK以外以外でメッセージボックスを開いたときの対策 If msg <> vbOK Then MsgBox "キャンセルしました", vbInformation, "売上管理ツール" End End If full_path = Application.GetOpenFilename("売上実績データ, *.xlsx") 'キャンセルや×を押されたときの対策 If full_path = "False" Then MsgBox "キャンセルしました", vbInformation, "売上管理ツール" End End If Workbooks.Open Filename:=full_path End Sub
場合によっては、「キャンセルしますか?」とさらに確認を促してもいいと思います。
すでに開かれているブック対策
少し長いですが、コードを紹介してから解説します。
Sub ブックを開く中級編() ’<中略> '既に開かれていないか確認する On Error Resume Next Open full_path For Append As #1 '選ばれたデータをブックとしてではなく、内部的に開こうとする Close #1 'そして閉じる If Err.Number > 0 Then 'エラー番号が0でないということはエラーがあったということ、つまり既に開いているファイルを開こうとした MsgBox "選択されたデータは開かれている状態です。" & vbCrLf & "先に編集を終了して下さい。処理を中断します", vbInformation, "売上管理ツール" End End If On Error GoTo 0 If Err.Number = 0 Then Workbooks.Open Filename:=full_path End If End Sub
既に開かれていないか確認する意向の部分を追加し、開くところをIf分の中に入れています。Workbooks.OpenメソッドではなくOpenステートメントで開くことを試みます。エクセルアプリケーション外で開くことができます。本来テキストを開くためのステートメントでエクセルとして正しく開くことはできませんが、開けるかどうか状態をチェックすることが目的なので、これで構いません。
開けない場合はエラーに成るため、On Error Resume Nextでエラーが起きたらその部分は実行しないで次に移る、としています。
エラーが発生した場合はErr.Numberに値が格納されます。この値は初期値が0なので、0以外だったときに、既にブックが開かれていると判断して、すでにファイルが開いているときの処理を記述しています。Errはエラーを格納しているオブジェクトという意味なのですが、オブジェクト指向は初段~二段レベルなので、今は「E.Numberの値が0以外はエラーなんだな」位の理解でOKです。
開いたブックが正しくない場合の対策
これは開くファイルのヘッダー部分が毎回同じフォーマットの時に使えます。先ほどのコードに追記します。
Sub ブックを開く中級編() Dim msg As Long 'メッセージボックスの戻り値を代入する Dim full_path As String '選択されたファイルのフルパスを代入する Dim wb As Workbook '開いたファイルを格納する '<中略> '正しいフォーマットか確認する Set wb = ActiveWorkbook If Cells(1, 1) <> "店舗名" Then MsgBox "選択したファイルが正しくありません。" & vbCrLf & "処理を中断します。", vbInformation, "売上管理ツール" wb.Close SaveChanges:=False End If End Sub
開いたファイルはいずれ閉じることになる為、このタイミングでブックを変数に格納しておきます。
再度選択させる画面に戻るようにして利便性UP
以上で一通りできているのですが、GoTo文を使って、再度選択できるようにします。プログラミングは上から下に順に流れていくのが自然なので、GoTo文は不自然なことをしていると言う面もあります。むやみに使うのでは無く、今回のように用途を限って使うようにしましょう。ちなみにGoToやCallであっちに行ったりこっちに行ったり流れが分かりづらいプログラムのことを「スパゲッティ」と言います。
上の段落で追記したIfの中身を書き換えます。
If Cells(1, 1) <> "店舗名" Then 'ココを書き換えます MsgBox "選択したファイルが正しくありません。" & vbCrLf & "処理を中断します。", vbInformation, "売上管理ツール" wb.Close SaveChanges:=False End If
書き換えた後のコードはこのようになっています。
If Cells(1, 1) <> "店舗名" Then MsgBox "選択したファイルが正しくありません。" & vbCrLf & "ファイルを選択し直して下さい。", vbInformation, "売上管理ツール" wb.Close SaveChanges:=False GoTo ファイル選択 End If
Application.GetOpenFilenameでfile_pathにフルパスを代入する直前に「ファイル選択:」と記述します。この部分をラベルと言います。GoTo ラベル名 でプログラムの実行がそこに飛びます。
最後にこのプロシジャを通しで書いておきます。
Sub ブックを開く中級編() Dim msg As Long 'メッセージボックスの戻り値を代入する Dim full_path As String '選択されたファイルのフルパスを代入する Dim wb As Workbook '開いたファイルを格納する msg = MsgBox("売上実績データを選択して下さい", vbOKCancel, "売上管理ツール") 'OK以外以外でメッセージボックスを開いたときの対策 If msg <> vbOK Then MsgBox "キャンセルしました", vbInformation, "売上管理ツール" End End If ファイル選択: full_path = Application.GetOpenFilename("売上実績データ, *.xlsx") 'キャンセルや×を押されたときの対策 If full_path = "False" Then MsgBox "キャンセルしました", vbInformation, "売上管理ツール" End End If '既に開かれていないか確認する On Error Resume Next Open full_path For Append As #1 '選ばれたデータをブックとしてではなく、内部的に開こうとする Close #1 'そして閉じる If Err.Number > 0 Then 'エラー番号が0でないということはエラーがあったということ、つまり既に開いているファイルを開こうとした MsgBox "選択されたデータは開かれている状態です。" & vbCrLf & "先に編集を終了して下さい。処理を中断します", vbInformation, "売上管理ツール" End End If On Error GoTo 0 If Err.Number = 0 Then Workbooks.Open Filename:=full_path End If '正しいフォーマットか確認する Set wb = ActiveWorkbook If Cells(1, 1) <> "店舗名" Then MsgBox "選択したファイルが正しくありません。" & vbCrLf & "ファイルを選択し直して下さい。", vbInformation, "売上管理ツール" wb.Close SaveChanges:=False GoTo ファイル選択 End If End Sub
これを一度作ってしまえば、今後は便利に使い回しが利きそうですね。次の記事ではVLOOKUPが当たらない現象を防ぐ方法について見て行きます。
<関連記事>