【Excel VBA】マクロの実行ログをExcelシート上に出力する方法

はじめに
動作環境
処理の流れとVBAのコード
おわりに

はじめに

こんにちは。
今回はVBAの実行ログ出力方法についての記事です。
VBAでマクロを作成して一通りの処理を完成させた後、運用していく上で意図した通りに動作しているか確認したいときってありますよね。
実行ログだけテキスト形式のファイル(.txt)で出力する、というのも良いのですが、今回はマクロを組み込んでいるExcelシートの中にログを出力して確認したかったので、その実現方法を記載しています。

動作環境

・OS：Windows10
・Excel 2019

処理の流れとVBAのコード

では実際のVBAコードについて記載していきます。
様々なマクロの作成時に繰り返し使用できるように、
ログ書き込み処理の部分は「writeLog」という関数でメイン処理の外に出し、独立させています。
writeLogにはログを出力させるExcelシート（Worksheet型）と出力メッセージ（String型）を引数として渡す設計です。

メインの処理の方では、まず「outputLogSheet.Cells.ClearContents」によってログ出力シートの内容をクリアしています。
マクロを実行させる度にログを新たに出力させるようにしたかったので上記処理を入れています。
マクロ実行の度にログを追記していきたい場合は上記の処理は書かないようにしましょう。

そして、ログを出力させたい部分で「Call writeLog(outputLogSheet, logMessage)」によってログ出力用関数を呼び出しています。
第1引数にはログを書き込むExcelワークシート、第2引数には出力させたい文字列、をそれぞれ格納してwriteLogを呼び出しましょう。

Option Explicit
 
Sub main()
     
    '実行時Log出力用
    Dim outputLogSheet As Worksheet
    Dim logMessage As String
       
    Dim i As Integer
           
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
     
    
        
    '実行時ログ出力先シートを取得
    Set outputLogSheet = ThisWorkbook.Worksheets("Log")
    
    'ログ出力先シートのクリア
    outputLogSheet.Cells.ClearContents
    
        
    Call writeLog(outputLogSheet, "処理開始")
    
    logMessage = "Forループに入ります"
    Call writeLog(outputLogSheet, logMessage)
    
    For i = 1 To 5
        logMessage = CStr(i) + "回目のループ処理です"
        Call writeLog(outputLogSheet, logMessage)
        
    Next
        
    logMessage = "Forループを抜けました"
    Call writeLog(outputLogSheet, logMessage)
    
    
    Call writeLog(outputLogSheet, "処理終了")
     

End Sub



' Log出力用Function

Function writeLog(ByVal logSheet As Worksheet, ByVal logMessage As String)
    Dim n As Integer
    
    'Logシートのheader入力'
    logSheet.Range("A1").Value = "時刻"
    logSheet.Range("B1").Value = "出力メッセージ"
        
    'Logシートの最終行取得
    n = logSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    '時刻とログメッセージ書き込み
    logSheet.Cells(n, "A").Value = Now()
    logSheet.Cells(n, "B").Value = logMessage
    
End Function

上記のコードを実行すると、マクロが記載されているExcelブックの「Log」という名前のワークシートに日時とログメッセージが出力されます。
f:id:J-back:20211006225223p:plain:w600

日時の書式に関して、VBAの中では「yyyy/m/d h:mm:ss」で出力されるので、Excelシート上でもこの形式で書き込まれています。
しかし、デフォルトだとシートの書式が「yyyy/m/d h:mm」となっているようで、秒数が表示されていなかったので、
時刻を出力する列（今回の例だとA列）のみ以下のように書式を変更しています。
f:id:J-back:20211006225259p:plain:w600