How To: Log Activity in Microsoft Excel

At home and work, I spend a lot of time in Microsoft Excel. Most of the important details of my life are stored somewhere in an Excel Workbook.

For some time, I wanted a way to save a log of the changes that were made to some of the workbooks.

But I didn’t make that change!

The primary reason for needing an excel audit log is Shared Workbooks. Inevitably, when groups collaborate in a single workbook, someone makes a change and then when it turns out to be wrong nobody remembers making the change.

I also think it is insightful to see how you use an application. Is there something you do often that could be scripted into efficiency? This logging gives you the peak behind the curtains.

What gets logged?

The code below will store the Date, Time, Username, Machine Name, Sheet Name and Message about each change. Feel free to track other info as needed, or change around the order of columns to suit your needs.

But only I need to see what happened!

Although I didn’t have a need to hide the Log sheet from my users, you might.

If so, use this code to show/hide the Log sheet. (Reference)

[sourcecode language=”vb”]
Sub ViewLog()
Sheets("Log").Visible = True
Sheets("Log").Select
End Sub
Sub HideLog()
Sheets("Log").Visible = xlVeryHidden
End Sub
[/sourcecode]

The Code

Below you’ll find the code that you can pop into one of your modules in the Excel VBA editor (Alt+F11).

[sourcecode language=”vb”]
Public Function LogChange(Optional Message)
Dim StartTime As Double
Dim TempArray() As Variant
Dim TheRange As Range

Application.ScreenUpdating = False

‘ How Long Does This Take to Run?
‘ StartTime = Timer

‘ Redimension temporary array
ReDim TempArray(0, 5)

‘ Which row is this going in?
Lastrow = Sheets("Log").UsedRange.Rows.Count + 1

‘ Set the destination range
FirstCell = "A" & Lastrow
LastCell = "F" & Lastrow

‘Store the tracked data in an array
TempArray(0, 0) = FormatDateTime(Now, vbShortDate)
TempArray(0, 1) = FormatDateTime(Now, vbLongTime)
TempArray(0, 2) = Environ$("username")
TempArray(0, 3) = Environ$("computername")
TempArray(0, 4) = ActiveSheet.Name
TempArray(0, 5) = Message

‘ Transfer temporary array to worksheet
Set TheRange = Sheets("Log").Range(FirstCell, LastCell)
TheRange.Value = TempArray

‘ Display elapsed time
‘MsgBox Format(Timer – StartTime, "00.00") & " seconds"
Application.ScreenUpdating = True
End Function
[/sourcecode]

In a past iteration of this tracking code, I tried to use Cell.Value assignments. It was horribly slow. Like 2 seconds per change slow. That may not sound like much, but when you try to enter a range of data and after each cell change you have to wait 2 seconds, you’ll want to pull your hair out. Hence the array assignments which reduced the run time from 2 seconds down to about .06 seconds.

I put this code in Excel and nothing is happening.

Well, I wasn’t done yet, but I commend you on your vigor to try this code out!

You can now call this function leveraging various Events (Open, Close, Save, Print, etc) that are baked into Excel. You do this in the ThisWorkbook code section.

First create some variables to store previous and current values. Place this at the top of ThisWorkbook:

[sourcecode language=”vb”]
Dim PreviousValue As String ‘ For Logging
Dim CurrentValue As String ‘ For Logging
[/sourcecode]

Then you can choose from the list of Workbook Events to trigger the function.

Example: Log when the file is opened

[sourcecode language=”vb”]
Private Sub Workbook_Open()
LogChange ("Opened " & ActiveWorkbook.Name)
End Sub
[/sourcecode]

Tracking Cell Changes

As you’ll see in the sample code at the end of this article, there is some extra code in the Workbook_SheetChange section.

[sourcecode language=”vb”]
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
CurrentValue = "" ‘ Reset the current value

If Sh.Name = "Log" Then Exit Sub

On Error Resume Next
If Err.Number = 13 Then
PreviousValue = 0
Else
CurrentValue = Target.Value
End If
On Error GoTo 0

‘ If there is no values, don’t run the following. This fixed a custom macro to add 10 formatted blank lines in a detail sheet
If PreviousValue = "" And CurrentValue = "" Then Exit Sub

If VarType(PreviousValue) = VarType(CurrentValue) Then
If CurrentValue <> PreviousValue Then
If Err.Number = 13 Then
PreviousValue = 0
End If

If PreviousValue = "" Then
PreviousValue = "EMPTY"
End If
If CurrentValue = "" Then
CurrentValue = "EMPTY"
End If

LogChange (Target.Address & " changed from " & PreviousValue & " to " & CurrentValue)
End If
End If
PreviousValue = 0
End Sub
[/sourcecode]

If you just plop the LogChange() function call in this section, you’ll get errors when the variant types on the CurrentValue and PreviousValue aren’t the same. You also won’t get any values for what was changed.

That’s what all the extra code is about; error checking and value tracking.

Note the code in Workbook_SheetSelectionChange as well as it is required to get cell changes to log properly.

Okay, I called the function where I wanted it, but I’m getting an error

Well, I didn’t script the creation of your Log sheet, so go create a sheet, rename it Log and put the headers in at the top of the sheet.

I’d recommend you also put an auto filter on the headers so you can quickly drill down in the future.

What’s the Catch?

Of course, this isn’t perfect code, here’s are the issues:

1. It doesn’t track copy/paste actions. I do a lot of copying/pasting in these workbooks and I have not found a way to trigger the LogChange() when a paste occurs.
2. Same problem for range deletions. If you select an all cells in a sheet and clear the contents, there is no log entry created.If you know how to capture either of these events or other missing events (like formatting changes) , let me know in the comments.
3. It doesn’t track Sheet Deletions.
4. Because I call the function from the Worksheet BeforeClose and Worksheet BeforeSave events, closing the workbook prompts for a save, even if you just saved it. I don’t remove the save call because it is enlightening to see users who NEVER save the workbook, except when they close it.
5. I’m not a programmer. There are probably many other improvements to be made to the code. I should probably re-comment as some of the comments aren’t really all that enlightening.

Even with these issues, I still find it very helpful to see a log of what updates have been made in an Excel workbook.

Sample Workbook

Rather than force you to try to copy and paste from the above text, you’re better off downloading the sample workbook and copying the code from there.

Click here to download a copy of the sample workbook.

Happy Tracking!