It’s a good habit to track the data your process has changed. Most programs should have some type of logging mechanism. Sometimes in the haste to roll a tool out the door, simple tracking pieces are left off. I ran across that recently when working with some of our Macro-enabled spreadsheets. Using the COM APIs provided with the software we use for our billing system, we’ve built some tools to process data in a different fashion than the standard jobs from the system.
I noticed that there wasn’t any output from these tools though. That bothered me when updating numerous records that may have needed to been rolled back. Doing some digging online, I made some mods to the VBA coding to allow for a ‘log’ file and track what the code did.
For this entry, here is the setup: An excel spreadsheet with a button that will run some VBA script. There is one tab that has the data points to process and the button to fire off the code. The other tab is for configuration: log output directory, username, etc. This is a rudimentary way to set up where the file goes and how to name it.
There will be two important variables:
- “Operator” (who is running the process)
- “FilePath” (where the log file will be written)
The key with these fields is to name [B1 and B2 in the pic] appropriately. These will be used in the code. In the example above, click on B1, then click on the namebox – Type the name of the cell (like FilePath) and hit Enter. This aliases the cell where you will put the output path of the log.
In the code editor (Developer -> Visual Basic), add the following to the code in order to create and write to the log:
Using some variables, capture the configuration data before going on to the rest of the code. The vFileName combines the configuration operator value with a date stamp and file name to distinguish the function of the process. It uses the cell aliases from the config tab.
Creating the log file
This creates the file using the filepath variable and filename using some built-in VBA code.
Writing to the log
In this case, I wrote the data to a variable first then used the VBA API to write to the log using the variable. Use this at any point in the code to log an action on the data. In the example above, I’m logging that I updated one value to another for a particular account.
Based on the config, I can check my output.
Opening on the file I know I just created, I can review the details of the process.
That’s about it! A pretty simple solution to help keep track on changes made to data via the VBA processing.
Thanks for reading! TG