If you’re an accountant, a sales analyst, or in any other office position where writing VBA code helps you do your job faster, you may have uttered the words “I’m not a programmer” before, and that wouldn’t have been wrong: once the code is written, you’d tweak it every once in a while to fix a bug here or there, and then move on to do your actual job as soon as things look like they work as they should. If you’re finding that as weeks and months pass, you’re spending more and more time debugging that code, it might pay off to learn a bit more about how “actual programmers” do things, but be warned: it’s a bit of a rabbit hole – in a good way, but still, it goes as deep as you’re willing to go.
If you’re a consultant delivering advanced Excel solutions to your business clients, you may have thought, said, or written the same words, too. But you like your worksheets efficient, flexible, reliable, easy to maintain; over the years you’ve become an expert at dissecting and modeling a business problem into a fine solution that will live on and grow with the client. You are a “power user”, a professional, and it shows.
Writing code involves exactly the same identical problem-solving thought process: dissecting a problem into small steps, and modeling it into …a sequence of executable statements. When you use a
SUM function in a worksheet, you assess whether the range of cells you’re adding up will need to grow over time, and you make sure it’s as simple as possible to add or insert a new row without breaking the integrity of the worksheet: you’re not just solving the problem at hand, you’re anticipating the extension points, facilitating them, making it harder to break things. You shade the background of cells intended for data entry, use borders around fields, conditional formatting, and data validation to ensure everything is obvious and remains consistent; you source data validation lists from named ranges pointing to a column in a table, so that adding new possible valid entries is easy as pie and requires no other step than… adding the new possible valid entries in the table. Next to another table that requires a particular sort order because it’s being used by dynamic named ranges that source data validation lists for co-dependent dropdowns, you might put up a very obvious formatted shape with an inner text that explains why that table needs to be sorted by this column then that column, and what happens to the associated validation dropdowns when the sort is broken.
See, you are a programmer. Worksheets are programs – even more intensely so with the amazing new features coming to Excel: dynamic arrays are changing the entire paradigm and turning the very thinking of worksheet functions into something that really isn’t very far from the mindset you’d have in functional programming.
So why is it that VBA code is so often seen as merely a sequence of executable statements then? Why is it that “it works, therefore it’s good enough” is so often where the bar is? You could have made that data validation list work off a standard range of cells in some (hidden?) column somewhere off-screen, and that would have worked too… but “well, it works” isn’t where the bar should be at, and you know it.
Depending on what the code needs to do, VBA code can become much more than just a macro once you start not just solving the problem at hand, but also anticipating the extension points, facilitating them, making it easier to maintain, and harder to break things. If writing code is part of what you do for a living, then you might as well write good code. Good code isn’t just code that works. It’s code that adheres to a number of language-agnostic principles and modern-day guidelines that even plain procedural code should follow. It’s code that Joe from accounting probably couldn’t have written by themselves, but that they could likely read and understand (at least at the higher abstraction levels), and if they know enough VBA to be dangerous, they could likely even maintain and extend it!
I’m not saying every piece of VBA code needs OOP and dependency injection and inversion of control and 20 class modules with 1 method each ought to get involved in sorting a
ListObject. Just that maybe, VBA code would be a little less dreadful to the eventual IT staff inheriting it, if instead of saying “I’m not a programmer”, we cared about the quality of our code in the exact same way we care about the quality of our worksheets and dashboards, or Access databases and reports, or whatever it is that we’re doing.
7 thoughts on ““I’m not a programmer””
A most excellent article, this is exactly where I am!
I will be looking into RubberDuck today! Thanks!
LikeLiked by 1 person
I’m fairly computer savvy, but haven’t done programming since pascal in high school some 20++ years ago. About six months ago I changed roles at my work to where I was working as a technical writer. The grunt of the work is taking bare text and formatting. With a bit of searching online, in the last three months I have coded multiple VBA macros that take three clicks to finish a job, rather than what could take a day or two. I’ve surprised myself quite a bit a some of the ones I’ve come up with.
Part of the problem might be fear that it is more complex than it really is. When trying to pass these macros off to co-workers, and showing them how to use them, it sometimes came back as “that’s too complicated” or “I have no idea what you’re doing” when I’m only showing where to FIND the macro, not how to code their own. When explaining to them that the buttons on the ribbons are technically macros, they look lost beyond all.
Yesterday in only a couple hours I learned how to add a new section on the Home tab in the ribbon with the macros front and center. We’ll see how that goes….
LikeLiked by 1 person
Hang in there and keep at it… The more advanced stuff will come as your experience grows. As for your coworkers, some people will always prefer to print things out and bust out their red pens, so don’t let them get to you.
LikeLiked by 1 person
The reason for the lack of quality is that it takes more time to build a good application but as you said, building the application is NOT their main duty.
Their main duty probably is to make money off it by interpreting the reports the application generates.
Been there, done that. I’ve worn the shoes of the VBA guy in the office, *and* the shoes of the IT guy picking up abandoned VBA code that’s such a mess it’s impossible to tell what’s going on, let alone fixing any problems with it.
If you are writing and maintaining any code, you ARE a programmer – whether you accept it or not, and whether that’s what you’re paid to do or not. Time spent doing things right, is time that won’t be inevitably wasted trying to make sense of the bloody mess 3 years down the line (whether that’s future-you or someone else)!
Note I’m not talking about simple macros here, this is more about business-critical VBA programs that do complex things that IT folks typically look down upon and say “huh why are you doing that in Excel/VBA?”
[…] you’re new to VBA and programming in general, or if you’re not a programmer and you’re only interested in making macros, then be warned that reading any further may make […]
[…] One of the very first inspection to be implemented in Rubberduck was the Option Explicit inspection. Okay, part of it was just because it was a trivial one to implement even before we had an actual parser… but the basic idea was (and still is) that nobody knows everything, and it’s with our combined knowledge that we make a mighty bunch, and that is why static code analysis in Rubberduck explains the reasoning behind each inspection result: there are quite many things Rubberduck warns of, that I had no idea about 10 or 15 years ago. That never stopped me (and won’t stop you either) from writing VBA code that worked perfectly fine (except when it didn’t), but whether we realize and accept it or not… a macro written in VBA code is a set of executable instructions, which makes it a program, which makes the act of writing it programming, which makes us programmers. […]