TARU-NET

エクセル変更履歴

[Excel VBA]で作る、エクセルの変更履歴を取得するツールの作り方

Excel | ExcelVBA エクセル変更履歴

このページで学習出来る事

　以前、[Excel VBA]で作成した、エクセルの変更履歴を取得するツール「Excel Logger」を公開しましたが、今回はその内容の解説をしたいと思います。

学習内容

  • エクセルファイルを開いた時の処理

  • エクセルファイルを閉じたときの処理

  • セルを選択した時の処理

  • セルの値を変更した時の処理

  • 現在時刻の取得

  • コンピュータ情報の取得

  • ファイルへの追記方法

関連記事
ExcelLogger-[エクセルの変更履歴を取得するツール]

NAME ExcelLogger-- [Excelの変更履歴を取得するツール（モジュール）] 作成日:2021/2/4更新日:2021/2/4動作環境:[Excel 2000-2016] SYNOPSIS […]

ExcelLogger

サンプルコード[完成版]

Const CONST_FILE_PATH As String = "c:\temp\hoge.log"
Dim arrayOldValue As New Collection

Private Sub Workbook_Open()
    Dim arrayLog(8) As String
    Call getNetworkInfo(arrayLog())
    
    arrayLog(5) = "Open"
    Call setWriteLog(arrayLog())
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim arrayLog(8) As String
    Call getNetworkInfo(arrayLog())
    
    arrayLog(5) = "Close"
    Call setWriteLog(arrayLog())
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim objCells As Range
    Set arrayOldValue = Nothing

    For Each objCells In Target
        arrayOldValue.Add objCells.Formula, objCells.Address
    Next objCells
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim arrayLog(8) As String
    Dim objCells As Range
    Dim toggle As String
    
    Call getNetworkInfo(arrayLog())
    For Each objCells In Target
        On Error Resume Next
        If arrayOldValue(objCells.Address) = "" Then
            toggle = "New"
        ElseIf arrayOldValue(objCells.Address) <> "" And objCells.Formula <> "" Then
            toggle = "Change"
        Else
            toggle = "Delete"
        End If
        
        arrayLog(4) = Target.Worksheet.Name
        arrayLog(5) = toggle
        arrayLog(6) = objCells.Address
        arrayLog(7) = arrayOldValue(objCells.Address)
        arrayLog(8) = objCells.Formula
    Next objCells

    Set arrayOldValue = Nothing
    For Each objCells In Target
        arrayOldValue.Add objCells.Formula, objCells.Address
    Next objCells
    
    Call setWriteLog(arrayLog())
End Sub

Private Sub getNetworkInfo(ByRef arrayLog() As String)
    Dim nowTime As String
    Dim objNetworkObject As Object
    Set objNetworkObject = CreateObject("WScript.Network")

    arrayLog(0) = Format(Now, "yyyy/mm/dd hh:mm:ss")
    arrayLog(1) = objNetworkObject.ComputerName
    arrayLog(2) = objNetworkObject.UserName
    arrayLog(3) = ActiveWorkbook.FullName
End Sub

Private Sub setWriteLog(ByRef arryaLog() As String)
    Dim FileNumber As Integer
    FileNumber = FreeFile
    
    Open CONST_FILE_PATH For Append As #FileNumber
        Print #FileNumber, Join(arryaLog, vbTab)
    Close #FileNumber
End Sub

　　

枠組み（スケルトン）の作成

　ツールやアプリを作る場合、大体のイメージをつかむため、ある程度の機能に分けた空っぽの関数を作成していきます。
　今回作りたいツールとしては「エクセルファイルに変更があったら、その変更箇所と変更内容をログに保存する」ですので、「ファイルに書き込む処理」「コンピュータ情報を取得する処理」「エクセルファイルを開いたり閉じたりした時の処理」などの関数の骨格を作っていきます。

Const CONST_FILE_PATH As String = "c:\temp\hoge.log"
Dim arrayOldValue As New Collection

Private Sub Workbook_Open()
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

Private Sub getNetworkInfo(ByRef arrayLog() As String)
End Sub

Private Sub setWriteLog(ByRef arryaLog() As String)
End Sub

コードの解説　変数宣言部分

‘ログを保存するためのテキストファイルの場所を定数として保存しておきます。
Const CONST_FILE_PATH As String = “c:\temp\hoge.log”

‘変更前の情報を取得しておく入れ物を用意しておきます。
Dim arrayOldValue As New Collection

　　

コードの解説　関数部分の解説

‘エクセルファイルが開かれたときに実行される関数を作ります。
Private Sub Workbook_Open()
End Sub

‘エクセルファイルが閉じられるときに実行される関数を作ります。
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

‘セルが選択されたときに実行される関数を作ります。
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

‘セル値が変更されたときに実行される関数を作ります。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

‘コンピュータやユーザ情報を取得する関数を作ります。
Private Sub getNetworkInfo(ByRef arrayLog() As String)
End Sub

‘取得した情報をログファイルに書き込む関数を作ります。
Private Sub setWriteLog(ByRef arryaLog() As String)
End Sub

　　

　エクセルファイル上のイベント「ファイルが開く」「ファイルが閉じる」「セルが選択される」などの動作が発生した時に呼び出される関数は、[ThisWorkbook]上で選択することが来ます。
※下記図参照

logger-01

　　

現在時刻、ユーザ情報取得処理

　エクセルファイルが、いつ、だれが、変更したかを特定できるように、コンピュータやユーザ情報を取得する関数を作成します。
取得したデータは、参照渡しで呼ばれた配列に入れて処理を行います。

Private Sub getNetworkInfo(ByRef arrayLog() As String)
    Dim nowTime As String
    Dim objNetworkObject As Object
    Set objNetworkObject = CreateObject("WScript.Network")

    arrayLog(0) = Format(Now, "yyyy/mm/dd hh:mm:ss")
    arrayLog(1) = objNetworkObject.ComputerName
    arrayLog(2) = objNetworkObject.UserName
    arrayLog(3) = ActiveWorkbook.FullName
End Sub

コードの解説　Private Sub getNetworkInfo()部分

‘配列を参照渡しで受け取れるよう記述しています。
Private Sub getNetworkInfo(ByRef arrayLog() As String)

‘コンピュータ情報、ユーザ情報を取得するためのオブジェクトを宣言しています。
Dim objNetworkObject As Object
Set objNetworkObject = CreateObject(“WScript.Network”)

‘現在時刻を取得しています。日付のフォーマットは[“yyyy/mm/dd hh:mm:ss”]です。
arrayLog(0) = Format(Now, “yyyy/mm/dd hh:mm:ss”)

‘オブジェクトからコンピュータ名を取得しています。
arrayLog(1) = objNetworkObject.ComputerName

‘オブジェクトからユーザ名を取得しています。
arrayLog(2) = objNetworkObject.UserName

‘実行しているエクセルファイルのフルパスを入力しています。
arrayLog(3) = ActiveWorkbook.FullName

　　

ファイルへの書き込み処理（追記）

　ログファイルへの書き込み処理を行う関数を作成します。
　変更履歴が格納されたデータは配列にて渡されます。

Private Sub setWriteLog(ByRef arryaLog() As String)
    Dim FileNumber As Integer
    FileNumber = FreeFile
    
    Open CONST_FILE_PATH For Append As #FileNumber
        Print #FileNumber, Join(arryaLog, vbTab)
    Close #FileNumber
End Sub

コードの解説　Private Sub setWriteLog()部分

‘書き込む情報を配列で受け取れるようにして関数を宣言します。
Private Sub setWriteLog(ByRef arryaLog() As String)

‘ファイルへ書き込み処理を行うため、空いている処理番号を取得しています。
Dim FileNumber As Integer
FileNumber = FreeFile

‘[Open]でログファイルを開き、[For Append]で追記モードで書き込み処理を行うよう設定しています。
Open CONST_FILE_PATH For Append As #FileNumber

‘受け取った配列をタブ区切りで結合し[Print]にて書き込み処理をしています。
Print #FileNumber, Join(arryaLog, vbTab)

‘[Close]でファイルを閉じています。
Close #FileNumber

　　

エクセルファイルを開いた時の処理

　エクセルファイルが開かれたタイミングで、現在時刻やユーザ情報などを取得し、ログファイルへの書き込みを行う関数を作成します。

Private Sub Workbook_Open()
    Dim arrayLog(8) As String
    Call getNetworkInfo(arrayLog())
    
    arrayLog(5) = "Open"
    Call setWriteLog(arrayLog())
End Sub

コードの解説　Private Sub Workbook_Open()部分

‘エクセルファイルが開いた時に呼び出されるよう記述しています。
Private Sub Workbook_Open()

‘ログ格納用配列を作成し、現在時刻などを取得する関数を呼び出しています。
Dim arrayLog(8) As String
Call getNetworkInfo(arrayLog())

‘変更履歴項目に[Open]と保存しています。
arrayLog(5) = “Open”

‘ログファイルへの書き込み関数を呼び出しています。
Call setWriteLog(arrayLog())

　　

エクセルファイルを閉じた時の処理

　エクセルファイルが閉じたタイミングで、現在時刻やユーザ情報などを取得し、ログファイルへの書き込みを行う関数を作成します。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim arrayLog(8) As String
    Call getNetworkInfo(arrayLog())
    
    arrayLog(5) = "Close"
    Call setWriteLog(arrayLog())
End Sub

コードの解説　Private Sub Workbook_BeforeClose()部分

‘エクセルファイルが閉じた時に呼び出されるよう記述しています。
Private Sub Workbook_BeforeClose(Cancel As Boolean)

‘ログ格納用配列を作成し、現在時刻などを取得する関数を呼び出しています。
Dim arrayLog(8) As String
Call getNetworkInfo(arrayLog())

‘変更履歴項目に[Close]と保存しています。
arrayLog(5) = “Close”

‘ログファイルへの書き込み関数を呼び出しています。
Call setWriteLog(arrayLog())

　　

セルを選択した時の処理

　変更前のセルの値を取得するために、対象のセルが選択されたときに実行される関数を作成します。

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim objCells As Range
    Set arrayOldValue = Nothing

    For Each objCells In Target
        arrayOldValue.Add objCells.Formula, objCells.Address
    Next objCells
End Sub

コードの解説　Private Sub Workbook_SheetSelectionChange()部分

‘セルが選択されたときに実行されるよう記述します。
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

‘セルのオブジェクトを格納する変数を設定します。
Dim objCells As Range

‘変更前の値を格納するコレクション配列を初期化しています。
Set arrayOldValue = Nothing

‘[Range]から選択されているセル情報取ってきて、セルのアドレスをKeyとし、値[Formula]を配列に格納しています。
For Each objCells In Target
　　arrayOldValue.Add objCells.Formula, objCells.Address
Next objCells

　　

セルの値を変更した時の処理

　セルの値が変更されたときに実行される関数を作成します。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim arrayLog(8) As String
    Dim objCells As Range
    Dim toggle As String
    
    Call getNetworkInfo(arrayLog())
    For Each objCells In Target
        On Error Resume Next
        If arrayOldValue(objCells.Address) = "" Then
            toggle = "New"
        ElseIf arrayOldValue(objCells.Address) <> "" And objCells.Formula <> "" Then
            toggle = "Change"
        Else
            toggle = "Delete"
        End If
        
        arrayLog(4) = Target.Worksheet.Name
        arrayLog(5) = toggle
        arrayLog(6) = objCells.Address
        arrayLog(7) = arrayOldValue(objCells.Address)
        arrayLog(8) = objCells.Formula
    Next objCells

    Set arrayOldValue = Nothing
    For Each objCells In Target
        arrayOldValue.Add objCells.Formula, objCells.Address
    Next objCells
    
    Call setWriteLog(arrayLog())
End Sub

コードの解説　Private Sub Workbook_SheetSelectionChange()部分

セル値が変更されたときに実行されるよう記述します。
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

‘ログ格納用配列を作成し、現在時刻などを取得する関数を呼び出しています。
Dim arrayLog(8) As String
Call getNetworkInfo(arrayLog())

‘値を選択せずに変更があった場合エラーとなりますので、その場合処理を抜けるよう設定しておきます。
On Error Resume Next

‘変更履歴が新規か削除か変更なのかを確認するための処理を記述します。
If arrayOldValue(objCells.Address) = “” Then
　　toggle = “New”
ElseIf arrayOldValue(objCells.Address) <> “” And objCells.Formula <> “” Then
　　toggle = “Change”
Else
　　toggle = “Delete”
End If

‘変更処理をしているシート名を取得しています。
arrayLog(4) = Target.Worksheet.Name

‘変更情報を取得しています。
arrayLog(5) = toggle

‘変更されたセルのアドレスを取得しています。
arrayLog(6) = objCells.Address

‘変更前の値を取得しています。
arrayLog(7) = arrayOldValue(objCells.Address)

‘変更後の値を取得しています。
arrayLog(8) = objCells.Formula

‘変更前の値を取得しておきます。
‘[Range]から選択されているセル情報取ってきて、セルのアドレスをKeyとし、値[Formula]を配列に格納しています。
Set arrayOldValue = Nothing
For Each objCells In Target
　　arrayOldValue.Add objCells.Formula, objCells.Address
Next objCells

‘ログファイルへの書き込み関数を呼び出しています。
Call setWriteLog(arrayLog())

　　

最後に

　以上で「エクセルの変更履歴を取得するツール」の説明になります。
　[ThisWorkbook]を上手く使用することで、エクセルファイル内のイベントを処理することが出来ますので、この辺のコマンドを覚えておくと便利です。

Excel | ExcelVBAの最新記事8件