Automate Excel Monotony
Ah, end of the month again and I need to setup those fucking reports. Every month it’s the same thing with one little change. Fuck it, it’s the same thing. Input the data into this sheet, run that model, update a sheet. There it is. One of 20 reports is done. Oh sorry, I don’t want you to think updating a report was as quick and simple as that short sentence. No, this is a painstaking task that requires double checking every piece before and after. Now if that process fails in the model, well shit, that adds another headache to this already marathon-esque exercise. Should I clear my schedule today? Just put in — monotonous task at hand.
I’m sure you’ve previously, or currently, had monthly reports that require the same repetitive process. Sure you can handle a good portion of it with formulas but eventually you hit a wall. Luckily Excel offers a scripting language, VBA, that will allow you to program events to dynamically handle modeling, reporting, and really anything you can do in Excel. I have used VBA to create everything from heat maps of the Northeast to automating data validation between systems.
There are three main structures that can be created in the VBA environment: Modules, Forms, and Classes. Classes allow developers to create an object structure that can house variables and sub routines. So you could create a Class structure to represent a car. The variables could be the color, model, make, etc. The sub routines could be drive, brake, turn, etc. Modules are the structure where developers can write scripts. In other words this is where your executable code goes. So you could write a routine in a Module to create a new Car Class. Now you have a new Car class that you can set to be a Red Ferrari 458 Italia. Forms provide a UI (User Interface) to the end user. So now that you have created a Car Class and built that Car in the Module you can provide a Form for the user to make the car drive, brake, or turn.
When developing VBA scripts and models it is not required to use all 3 structures in the environment. You can feel free to use Modules alone to create smaller scripts (preferably 1000 or fewer lines). Classes provide a structure that help when organizing and creating efficient larger models but are not always needed. Forms also are not always necessary. If you are the sole user of a workbook and dont want a UI you can run the scripts from the VBA IDE (Integrated Development Environment) or from the Developer Tab under Macros.
This is a brief and general overview of VBA and some of its capabilities. If you would like to take a deeper dive follow me as I help you explore the possibilities of automating your Excel problems.
