I have a requirement to log all the changes made to a particular worksheet. I cringe whenever I'm faced with that particular task. I could use a Worksheet_Change event coupled with a Worksheet_SelectionChange event to log the old cell value and the new cell value. I really hate that option for a couple of reasons. First, I would have code running all the time which would be a drag on the system. Also, it logs way too much stuff. For instance, if a user changes a cell value from 1 to 2, then changes it back from 2 to 1, you have two log entries that don't add much to the log. It makes the log file messy and lessens its value in total. Another problem occurs when the user changes a whole slew of cells at once. Make a Worksheet_Change log procedure then delete the used range of the worksheet. With just a 10 column x 10 row worksheet, you've just made a 100 log entries with a noticeable lag in performance.
Speaking of changing a bunch of cells, I would have to save the old value in a module level variable using the SelectionChange event so that I could log it when the user fires the Change event. That wouldn't be such a big deal if I could use the Target variable from each of those event procedures, but when the user selects a range of cells, I have to save the "old value" from every cell in the selection. What a pain.
Normally, I would recommend that people don't log their changes. But I have situation that's hard to defend. There's a one worksheet workbook with a fairly limited UsedRange. It's an important table that a lot of other sheets will use and that many users will need to update as necessary. Everything happens (or should happen) in this limited UsedRange and there will never be any other sheets in the workbook. It's a prime candidate for change logging because many users can change the data and that data affects every other user. If things get screwy, there needs to be some accountability.
Nevertheless, I wasn't going to use a Worksheet_Change event. There's just too much to track and too many problems that can arise. So I settled on a different tack, which I (finally) describe here.
The basic structure is that I save a copy of the file when it's opened and I compare the current file with this saved copy to see what's changed. This has the benefit of keeping the log file fairly clean because the whole change/change back scenario isn't logged. Only the final changes against the original are logged. It also has the advantage that the code is running less. With a Change event, the code runs whenever you do absolutely anything. With this, it only runs on Open, on Save, and on Close (as described in a bit).
I'm not saying there aren't challenges with this because there are. At first, I decided that I would save a copy on Open and compare on Close. I'm sure the brighter of you have already figured out the problem with that. You can close a file without saving it. So now I have to determine if the user cancels the close, closes without saving, or closes and saves. That's all well and good, but I only have a Before_Close event and these user decisions happen after that fires. Ultimately, I had to settle on logging at every save. If my goal was to only log real changes, this takes me farther away from that goal. Now a user can change a cell, save, change it back, and save it again. It's still less than the alternative. Here's the basic structure:
When the file is opened, I use the SaveCopyAs method to save it in its current state, heretofore referred to as OldCopy
When the file is saved, I compare the two workbooks and log the changes. Then I close the OldCopy, delete it with a Kill statement, and resave the NewCopy so OldCopy is now current.
When the file is closed, I kill the OldCopy. Not so fast my friend, as Lee Corso might say. I had to control the hell out of the BeforeClose event for this to work. The long and the short of it is that I had to eliminate the Cancel option and program my own "Save and Close", "Don't Save and Close" options. Either way, that file was closing. Not ideal, but that's life.
Figuring out which cells changed posed another problem. Do I loop through the newer file and compare it to the older file or vice versa. Or do I compare all 65,000 rows x 256 columns to make sure I don't miss something. I don't think so. I ended up looping through the newer and comparing to the older, but I didn't necessarily use the UsedRange of the newer. I counted the cells in the UsedRange from both sheets and used the Address property of the larger of the two to define the corpus of cells to change. This, however is not foolproof.
The main issue with differences in the UsedRange was deleting rows and columns. If the user deleted half the rows in the newer file, then the newer file's UsedRange might be quite a bit smaller that the UsedRange of OldCopy. If I just loop through the UsedRange of NewCopy, then I'm not actually logging all the changes because I'm missing all the zeros in NewCopy that had values in OldCopy. Counting the cells gets close, but it's not perfect. If I delete a bunch of columns in NewCopy but add a bunch of rows, the UsedRange may have a higher count, but still not catch every change.
Now that I type this, I can see that I should count the rows and columns separately and use the greater of each independent count to determine which range I should compare. Okay, I'll change the code, but I don't guarantee that I won't screw it up.
I haven't commented this code very well (count of comments = 0) so you'll have to rely on the above description. I think the variables speak for themselves. Anything that starts with a 'g' is a global variable. Anything in all caps is a constant, proper case is a variable. The error handling scheme is straight out of Professional Excel Development. The variable gsIdentStandardsOpen holds the name of the person who opened. This global variable is set when the workbook is opened and a password is supplied. I'll describe the password scheme in a future post. I don't know if any of that helps, but there it is.
Public Function LogStandardsChanges
() As Boolean
Dim bReturn
As Boolean
Dim lFnum
As Long
Dim sOutput
As String
Dim wbOldStan
As Workbook
Dim wbNewStan
As Workbook
Dim rCell
As Range
Dim bChanges
As Boolean
Dim lMaxRow
As Long
Dim lMaxCol
As Long
Dim rRng
As Range
Const sSOURCE
As String =
"LogStandardsChanges()"
On Error GoTo ErrorHandler
Application.EnableCancelKey = xlErrorHandler
Application.ScreenUpdating = False
bReturn = True
Set wbNewStan = Workbooks
(gsSTAN
)
Set wbOldStan = Workbooks.
Open(gsTemplateFldr & gsOLDSTAN
)
lMaxRow = Application.WorksheetFunction.
Max(wbNewStan.Sheets
(1).UsedRange.Rows.
Count, _
wbOldStan.Sheets
(1).UsedRange.Rows.
Count)
lMaxCol = Application.WorksheetFunction.
Max(wbNewStan.Sheets
(1).UsedRange.Columns.
Count, _
wbOldStan.Sheets
(1).UsedRange.Columns.
Count)
With wbNewStan.Sheets
(1)
Set rRng = .Range
(.Cells
(1,
1), .Cells
(lMaxRow, lMaxCol
))
End With
bChanges = False
sOutput =
String(60,
"-") &
vbNewLine
sOutput = sOutput &
"Saved: " &
Format(Now,
"yyyy-mm-dd hh:mm:ss") & _
vbTab &
"By: " & gsIdentStandardsOpen &
vbNewLine
For Each rCell
In rRng.Cells
If rCell.Value <> wbOldStan.Sheets
(1).Range
(rCell.Address
).Value
Then
sOutput = sOutput & rCell.Address &
vbTab & _
"Old Value: " & wbOldStan.Sheets
(1).Range
(rCell.Address
).Value &
vbTab & _
"New Value: " & rCell.Value &
vbNewLine
bChanges = True
End If
Next rCell
sOutput = sOutput &
String(60,
"-")
If bChanges
Then
lFnum =
FreeFile
Open gsTemplateFldr & gsSTANLOG
For Append As lFnum
Print #lFnum, sOutput
Close lFnum
End If
ErrorExit:
On Error Resume Next
LogStandardsChanges = bReturn
wbOldStan.
Close False
Kill gsTemplateFldr & gsOLDSTAN
wbNewStan.SaveCopyAs gsTemplateFldr & gsOLDSTAN
Application.ScreenUpdating = True
Exit Function
ErrorHandler:
bReturn = False
If bCentralErrorHandler
(msMODULE, sSOURCE
) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
So there it is. I'm tired of typing, but if there's something I didn't explain be sure to let me know. You comments on this strategy, as always, are welcome and expected.
