[Excel VBA] ファイル保存時に内容チェック | VBA プログラミングで社内アプリ開発

今まで教えてきたプログラムは、Visual studioを開いて実行するか、ボタンを押して実行だったけど、今回はExcelファイルを保存した時に自動的に実行される方法を教えるよ。

これが出来れば、例えば保存する時に、データの重複があったり、数字しか入ってはいけないところに文字が入っていた場合に、警告メッセージを出して保存させないということが出来るよ。

これをガッチリ作れば、もうただのExcelファイルではなくて、ちょっとしたアプリになるね。

特にマスタ情報の管理にはとても役に立つと思うよ。それでは見ていきましょう。

いつものように開発タブからVisual Studioを開いて、↓のThisWorkBookを開いてね。Sheet1とかModule1では無いから気を付けてね。

　そしたら↓をThisWorkBookの中に貼り付けてね。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If MsgBox("保存しますか？", vbOKCancel) = vbCancel Then
        Cancel = True
    End If

End Sub

　そのあとに試しにそのExcelファイルを保存してみて。↓のようなメッセージボックスが表示されたかな？

　OKをクリックしてメッセージボックスを閉じた後に、今度は保存してからの動きを確認するために、↓のようにさっき貼り付けたプログラムの一番最初の行にブレイクポイントを設置して、再度保存して、ステップ実行で動きを確認してみよう。ステップ実行はF8キーで1行ずつ進むよ。

保存しますかのメッセージでキャンセルをクリックすると、Cancel = Trueを通るけど、そうするとファイルの保存がキャンセルされて、保存されていない状態になるよ。

　　それでは、さっき貼り付けた分は一先ず削除するか、コメント化しておいて、次に↓を同じようにThisWorkbookに貼り付けてね。これは、顧客番号に数値以外が入っていた場合、保存させないというコードだよ。

　ソースコードの中にもコメントで書いてあるけど、Function CustNoNumberOnly()が、顧客番号に数値が含まれているかチェックする関数で、もし含まれていなかったらTrue、含まれていたらFalseを返すよ。

　ちなみにTrue、Falseというのは特殊な型で、Booleanという型だよ。この型はTrueかFalseしかないから、数値や文字列とは違って、Yes/Noだけを判別するために使います。まあピンと来なくてもとりあえずそのまま使ってみてね。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    If CustNoNumberOnly = False Then
        Cancel = True
        MsgBox ("保存しませんでした。")
    End If

End Sub


'顧客番号に数値以外が入っているかチェック。入っていなかったらTrue、入っていたらFalseを返す
Function CustNoNumberOnly()

    Const SheetName As String = "顧客マスタ"
     
    '行数カウンター
    Dim RowCounter As Integer
    
    'データが始まるのは2行目から
    RowCounter = 2
    
    '1列目が空白になるまでループ
    '2行目の1列目からチェックするよ。途中で空白があったらそこで繰り返しはおしまいになるよ。
    Do Until Sheets(SheetName).Cells(RowCounter, 1) = ""
    
        'IsNumeric関数は数値だった場合はTrue、それ以外だった場合はFalseを返す関数です。
        '1列目の顧客番号を1行ずつチェックするよ
        'もし数値以外が含まれていた場合、Functionの処理を終了して、Falseを返します。
        If IsNumeric(Sheets(SheetName).Cells(RowCounter, 1)) = False Then
            CustNoNumberOnly = False
            MsgBox (RowCounter & "行目の顧客番号に数値が含まれています。")
            Exit Function
        End If
        
        RowCounter = RowCounter + 1
    
    Loop

    'もし顧客番号のチェックの中で数値以外が入っていたら、Functionの処理は終了して、Falseを返すけど、
    'Loop処理を最後まで行ったということは、数値しかなかったということだから、Trueを返す。
    CustNoNumberOnly = True


End Function

　次に、顧客番号のどれかに試しに数値以外を入れて、保存してみてね。何行目に数値以外が入っていることと、保存できなかったことがメッセージボックスで表示されるよね。

　これを応用すれば、様々な保存前チェックが出来るようになるよ。以前の記事でも書いたけど、Excelでマスタ管理をしている場合、データの正確性は必須になるから、これは使えるよ。Excelはどうしても何でもできてしまうから、こういった縛りを付けるのはとても重要だよ！