ゆんの業務改善ブログ

①生産性向上 ②業務改善 ③自動化 について情報発信しています。VBAプログラムは本当の初心者から他のアプリケーションを呼び出して使う上級者的な使い方まで幅広いレベルで解説していきます。

VBA 中級者から上級者へ③ ブックを開く周辺を堅牢にする

エクセル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

ここまでは最低限の記述です。ここから、下記の対策を順に盛り込んでいきます。

  1. ファイル選択を促すメッセージボックスでキャンセルを押された場合の対策
  2. すでに開かれているブック対策
  3. 開いたブックが正しくない場合の対策
  4. 再度選択させる画面に戻るようにして利便性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が当たらない現象を防ぐ方法について見て行きます。

<関連記事>