Excel Work Macros

What I created and how...

Some background information first, at my job we have to follow a strict set of rules that is audited by an international governing body. One of the strict rules is that whatever we write down in our methods and procedures has to be followed exactly, or an exception has to be raised and justified.

One day I was reading one of our methods, something I was learning for the first time. I came across a section that specified that all changes made in one of our worksheets need to be logged and made available for audit purposes. I asked around and my supervisor told me that they had tried to get this logging to work but it ultimately failed and now it's just a rule we don't follow.

I thought this was absolutely crazy, because we get audited regularly, like at least twice a year, and this rule has not been flagged or challenged at all. I set out to create a macro that could accomplish this task for me, as the worksheets we use are in excel I was sure this could be easily fixed. At least I thought it would be.

So I begin to create a macro that will log any change that happens on a separate worksheet. Initially I thought of just copying any change that occurred onto another sheet. The changelog sheet had to be locked to avoid removing or adding changes that did not occur, so the macro had to unlock the sheet, write the change, then lock the sheet again. The macro would also undo the users change so it could see the old value was and then put back the new value. This worked great for very minor changes but failed to correctly log changes when filling down cells in excel and failed to work on older computers with outdated versions of excel. I felt like the undo step in my macro was very clunky and wished for a better solution.

I had to try again with a different approach. My next thought was to have a copy of the worksheet, hidden from the user, and when a change is made in a range, check all the cells that changed on the hidden copy, paste them into the changelog, then copy the new values into the changelog and hidden copy. This solution worked perfectly, except now the file size is automatically doubled, which took longer to open and the number of operations the macro performed meant a noticable stutter on older computers.

In between these two iterations of my macro, there was some tensions and conflicts arising amongst my colleagues. Since it was possible for the same worksheet needed to be open on multiple computers, saving changes led to many "copy of" worksheets. This would boil over one day when a "copy of copy of copy of" worksheet was made and knowing which was the most up to date worksheet became convoluted.

Excel has an in-built feature of sharing a file, allowing multiple users to open and edit a file and all the changes are compiled upon saving. The issue I had to solve was having the changelog be protected from deliberate changes by the user, sharing a file meant the macro was unable to unprotect the workbook to write in a change in the log. Thankfully the solution to this was quite simple, just hide the changelog sheet, then I can make the changelog viewable as a pdf at the click of a button.

The next issue was when multiple people have the shared file open at the same time, upon saving the file, any changes made by others since you opened the file can overwrite your changes and they would be lost. The way I went about solving this issue was to log all changes into an array as the user fills in the worksheet, then when the save button is clicked the file will update with any changes made by others, logging them into the changelog then logging the changes stored in the array, again saving the file.

So the final iteration of my the worksheet ended up having a copy of the main worksheet that was hidden from the user and a changelog that was hidden from the user. It was able to be a shared file, which made everyone happy. Changes would be logged after updating the shared file, so no information was lost when multiple users are making changes.