Logging Excel Use through Add-ins
Yesterday, we had a request for an Excel file that would log user activity. It begged the question…how can you track Excel use in a master spreadsheet?
Firstly, any solution would have to be present whenever Excel was open. You can automatically open spreadsheets when Excel opens by putting them in Office’s XL Start folder. However, if a full spreadsheet were to load, it would quickly become an irritation and a user would understandably want to close it to get it out the way.
A better idea would be to create an add-in. Add-ins are Excel files that simply contain macros and Visual Basic code. They can be managed from the Add-Ins tab under Excel options. The best bit is that add-ins are designed to reside permanently within Excel and they can be set up to auto-load when Excel starts. Which means we’ve now got a program with the potential to run code when certain events occur.
The easiest events are Workbook_Open and Workbook_BeforeClose. As applied to an add-in, you can make something happen when Excel as a whole is opened and shut, because the add-in only opens and closes with Excel as a whole. One simple application would be to send the open and close time to a master spreadsheet saved elsewhere. So far, so good…but we’re not doing anything revolutionary and our add-in certainly isn’t doing much tracking.
We can extend things further by use of Class Modules. For this, we need to open a class module e.g. “EventModule” upon opening Excel. The key lines of code are to define our class module, and to set it up when the add-in opens…
Dim eventGo As New EventModule
Private Sub Workbook_Open()
Set eventGo.App = Application
End Sub
This takes us away from Visual Basic for Applications (for VBA) and towards the broader coding language of Visual Basic. The point of this is to enable our add-in to pick up actions taken within Excel as a whole, instead of limiting ourselves to stuff that occurs within the add-in itself. We can then fire off code from within EventModule e.g. when a sheet changes…
Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
‘put code here
End Sub
Again, we could open our master spreadsheet and record the time at which sheets were activated. We could also say what sheets were activated. The limit to what can be achieved is primarily the point at which all this background code/event recording will start to slow down the tasks you are trying to complete within Excel. Anyway, it was certainly an interesting problem and our consultants enjoy a new challenge.
