When I first started this blog, I thought to myself: VBA is wonderful and amazing—there’s nothing you can’t do with it. I still love VBA, don’t get me wrong, but my relationship with the scripting language has changed considerably over the last several years. On a recent Excel.TV episode, Rick rightly pointed out the irony in my relationship with VBA solutions. “VBA appears in the title of you blog!” he said.
My issue isn’t with VBA itself, but rather with how we use it. This won’t be a cliché article complaining about how old VBA is or why I think another language is so much better. Blech. But I do want to discuss how I think it should be located in development and provide you some food for thought in this vein.
The “RUN” button perhaps best encapsulates my relationship with VBA. You know what the RUN button is because you’ve created it before. It’s the button that updates a worksheet’s values, creates a new report, inputs data into a model—and so on. It’s the button you press to begin automation. In a sense, it’s everything I used to think VBA was about.
But having done this for many years, I now hate RUN buttons. To some extent, they can be unavoidable given the underlying nature of the problem. But they are often more avoidable than we think. As followers of my blog know, I am keen to place as much functionality on the spreadsheet as possible. If I can do it with Excel and formulas, then I avoid needless VBA. This makes for tighter faster work, that’s easily scalable. (In my book, I call the concept “reusable components.”)
And RUN buttons are often filled with needless VBA. We can use formulas to automatically update data values so that what we’re looking at is always live data. Form controls can help us with this. RUN buttons are often used to create copies of new report tabs, where the layout of a template tab is copied, filled with the latest information, and then displayed. But wouldn’t one display tab that could use formulas or Power Pivot suffice? The RUN button symbolizes processes that are far more complex than they need be.
Look, I’m just complaining about a symbol of a true underlying problem. We make things more complex than they need to be. Our work should never be more complex than the underlying model.
But agree or disagree, we should think more about how we develop. Like I said, RUN buttons are sometimes unavoidable. But if you can limit the amount of code in your work, replacing iterative volatile actions with faster formulas, that’s something to shoot for. At least I think so.