How far can you go at reducing the tedium in your spreadsheet usage? On May 1, 2015, nearly twenty people attended a day-long seminar “Visual Basic for Financial Professionals,” held on location at the CFA Society of Toronto. The seminar was conducted by Andrew Nikolishyn, CFA, of Vesta Solutions.
I often use Microsoft Excel as a scratch pad and a tally sheet, but lately I’ve had to do connected tasks on a regular basis, involving recent financial data. I prepare a graph from the data, and I also run a simple present value calculation, to compare with the previous month’s estimate. From experience, I know my client will request numerous “tweaks” to the graph—its size, axis labels, colour, symbol, and the range of data used will all be changed a few times over—before the graph reaches its final stage.
I regarded the seminar with mixed feelings. I love learning new things (or re-learning old things) but I felt a little rusty. I knew my Visual Basic for Application (VBA) skills needed to be brought up to date, since they were firmly rooted in versions of Microsoft Office circa 2003. In other words, I never had to worry about things like switching to the Developer Tab, since versions of Excel before 2007 did not have “tabs” or “ribbons.”
I was looking forward to a well-organized seminar. I had never systematically learned Visual Basic; instead I just plunged into it when I became the keeper of some highly useful legacy code at a previous position. My first few tries at Visual Basic felt like I was the substitute second violin in an orchestra, called upon to perform, who did not know the song (but knew a few comparable songs), who said, “hum a few bars and I’ll fake it.”
I recall spending hours immersed in John Walkenbach’s wonderful book, Excel 2002 Power Programming With VBA. Although it’s fun to learn things in a hunt-and-gather style, I felt chagrined at learning Visual Basic in such a haphazard way. Some people boast about their self-taught coding, but I’m not one of them. Put it this way: would you rather your surgeon be self-taught? Or be taught according to some systematic, all-encompassing plan?
“Programming is no longer a ‘nice to have,’ it’s now a mandatory skill in the competitive labour market that’s upon us,” Nikolishyn said. No matter the skill level, he and his assistant were quick to put attendees at ease as we spent the day stepping through the course material. We began with a general survey of the benefits of VBA, which controls the objects in the application (such as PowerPoint, Word, and Excel). Within Excel itself, the most common objects are the Workbook, the Worksheet, and the Range.
I didn’t see the Developer tab appearing when I first started up MS Excel. I needed to turn on the Developer tab, which I did through choosing File > Options > Customize the Ribbon > Developer. The Developer tab, rife with possibilities, was beautiful to behold! (Figure 1)
For one thing, the Developer tab is a quick way to access the Visual Basic Editor (VB Editor) or a selection of prerecorded subroutines known as “macros.” Or, you can begin recording (or typing in) your own set of commands in a macro.
Nikolishyn described the basic layout of the VB Editor (Project Explorer, Code area, and Immediate Window). Participants who were totally new to VBA became familiar with navigating the VB Editor, the Project Explorer, and typing in code. He had all attendees type in a function (as opposed to a subroutine). Functions return a value or an array, whereas subroutines have multiple statements and can perform a number of actions.
For the first example function, he chose the H-Model, which relates the equity index price level to a combination of several parameters: the current annual dividend, high growth stage, growth of the mature company, duration of the growth period, and the cost of equity. (Clearly chosen for an audience of financial charter holders.)
For the first example subroutine, he dusted off a variant of “hello world,” thereby introducing the message box (or MsgBox), which is a built-in VBA function.
The real strength of VBA, said Nikolishyn, is the ability to record a macro, which automatically generates code that can be modified to do other things. Nikolishyn asked us to choose Developer > Record Macro and then carry out some activities such as typing in text, and a formula, and then changing the appearance of the output. After executing these Excel commands, we clicked on the Stop Recording button.
Once a macro has been recorded, “take out all code that is redundant or not necessary,” he said. We opened the macro we had just recorded with the VB Editor and changed the code to modify the instructions, testing each time to see the effect.
Within an hour, attendees became comfortable with flipping between the spreadsheet and the VB Editor. We began to imagine the VBA macros we could create to relieve the tedium of oft-repeated actions.ª