Language: Deutsch English















Last Update: 2024 - 01 - 25








How to write Better VBA code

by Philipp Stiefel, originally published December 19th, 2016

last updated 2021-03-21


Stars and shooting star in a mountain sky - Better VBA Title Image

Based on a photo by Štefan Štefancík, used here under CC0 licensing

This is the companion page to my Better-VBA-Series on YouTube. A series where I’m going to show and explore methods I consider useful to write better VBA code.

So, what exactly is Better VBA Code? – Well, if there is a concise definition, I do not know it yet. Maybe we can move closer to an answer, by looking at the thoughts and Ideas I have (so far).

Let’s get started with some more questions.

Are you a VBA-User or a VBA-Developer? What are the measures you use to assess the quality of your code? Is your only objective to write code that works or do you strive for more?

Do you want to write better code?

You are reading on, so I assume the answer is: “Yes!”

Can code be better than just working?

VBA is a language used intensively for recorded macros and small automation projects. It is used for serious, sizeable and sometimes mission critical applications as well. These are two absolute diametrical use cases. These contradicting fields of use are facilitating issues.

Many VBA users start their path to becoming VBA developers with those small and simple automation projects. Some of those small applications grow bigger and bigger over time to become sizeable and important to the business entities using them.

Many developers maintaining such applications have not grown their skills and programming techniques as would be adequate for the projects they work on. They started with some macros that someday were converted to VBA-Code. That code is usually of horrible quality, but it works.

Later they tweak the code to be somewhat more versatile and adapted to their requirements increased complexity. This is frequently done by only adjusting the parts of code that need to be changed and leaving all else as it was written (or generated) originally.

This evolution of the project results in code that is doing what is supposed to do. - But it is code that is fragile and is becoming ever increasingly hard to maintain.

Many developers maintaining such projects are lacking the experience to recognize that this does not have to be this way. They just assume that bigger projects are simply harder to maintain.

Others see the problems, but they haven’t got any clue on how to remedy these issues. This is understandable. Most VBA related resources on the internet just focus on getting the job done. They show how to write the code that works, but then they stop.

It is not easy to find any information beyond just getting the job done. Information on how to write readable, maintainable and maybe even beautiful code, using VBA. Code that is easy to understand as wells as robust and resilient. Code that can be enjoyed by the persons having to maintain it.

Improve your skills, improve your VBA Code

I do have more than 15 years of experience writing VBA code. I am familiar with several other languages such as Java, C#, and Transact-SQL. I worked on tiny projects and on some really huge projects (in VBA scales). I think, I know a fair share of best practices to write readable, maintainable and robust code.

That all being said, I am honest with you. I’m not feeling like I know it all. I’m well aware that I still write code that is not as error-free, as readable, as maintainable and as robust as it should be. - I too am still learning.

There is not just one single, magic switch you need to flick to make your code better. There are many aspects to better VBA code. If you’d ever learn them all, there might pop up some new aspects, nobody ever thought of before.

There is more than just knowing how to write good code, to actually do it. You need to apply your knowledge and skills to each and every line you write. That requires discipline. A lot of discipline. - More than I often have got.

So we need to tools to make it easier to write better code. We need tools to nudge us in the right direction should we ignore some of the practices we should know and follow.

I do slack off from time to time, I sometimes do ignore best practices. Despite all my mistakes, despite my occasional negligence, I strive to improve my code. Strive to write better code in the next procedure, the next module or the next application.

Join me on the way to write better VBA code!

The Videos

I going to post a short summary of each of the videos here. As the main content is in the videos, I’m not going to cover the topics as extensively as I usually would.

The first four videos recorded so far are about topics I considered “beginner level”. – Some people, who watched a peek preview of them begged to differ. – So I let you be the judge of that.

Episode 0 - Introduction

This video introduces the series and has essentially the same content as this text. I recorded this video actually as the fifth video, after writing this text. As Better VBA is mainly a video series, I felt this introduction should be available in video format as well.

Episode 1 - Essential settings for the VBA Editor

Before you even write the very first line of code in any VBA-Project, you should make sure you have got you VBA Editor configured correctly. While most of the settings can be set according to personal preference, there are two that are not debatable in my opinion.

  • Auto Syntax Check – This is the most annoying thing I’ve seen in any code editor, ever. Turn this off to get rid of those ultra-annoying Messages-Boxes popping up whenever there is a syntax error in the line of code you just moved the cursor away from.
  • Require Variable Declaration – Automatic (not required) variable declaration is the cause for countless numbers of errors I have seen newbies struggle with. I cannot comprehend why Microsoft decided to switch this off by default. Turn this on and make sure you manually add an Option Explicit-Statement to every code module you already created in your project.

There are a couple of other options, you should absolutely know about, like Notify Before State Loss, and the Error Trapping options, even though there is not strict rule on how to configure them.

Episode 2 - Naming of variables

Variables are a crucial part of (almost) any routine. Getting the variable naming right can make the difference between intuitively readable code and an impregnable heap of garbage.

I show a pretty simple VBA procedure with particularly bad variable names. Then I change just the variable names to meaningful ones. – See the difference!

Episode 3 - Constants in VBA

The importance of naming variables is a pretty obvious affair. But what about values that are not meant to be variable at all? Are we doomed to retype the same hard coded and frequently meaningless literals over and over again in our code? – Thanks to Constants, we are not!

Episode 4 - Enums (Enumerations) in VBA

While constants are fairly well known, my experience suggests Enums are not. You should know how this subtype of Constants works and how it can massively improve the usability (from a developer’s point of view) of your code.

Episode 5 - VBA code formatting

A fundamental factor for the readability of source code is its formatting. I thought this to be too trivial to justify making a video about it. My daily experiences with code written by other developers, beginners and intermediates alike, indicate otherwise. So, here I show the difference in readability between poorly formatted code and well formatted code.

Episode 6 - Variable declaration

Variables are an essential part of programming. So, you should know very well how to properly declare your variables. In this video I show a common pitfall, some general recommendations and discuss why I do NOT use Hungarian Notation anymore.

Regardless whether you use Hungarian Notation or not, you absolutely should know what it is and the rationale behind it.  The de-facto standard for Hungarian Notation in VBA, is the Leszynski/Reddick naming convention, which is widely used in VBA.

To fully understand my take on variables, you should watch my video about variable naming (Episode 2) as well.

I strongly discourage you to use variable typing by using DefType-Statements. For the curious; here is a link to the documentation of the DefType statements. - Please do not use DefType Statements!

Enjoy watching…

Episode 7 - Code formatting tools

Proper formatting of the VBA source code is a crucial factor for the readability of the code. While you are writing code, you should format it correctly right away because it will help you to write solid, error free code.

But what about maintaining legacy applications with badly formatted code? Of course, fixing the formatting of the code has massive benefits through improved readability, but it reformatting the code manually takes a lot of effort in a big application.

In this video I show two tools that reduce the effort to reformat VBA source code to virtually zero.

Smart Indenter

Smart Indenter is a completely free add-in with the single purpose of indenting VB/VBA source code.

Further info and download: http://oaltd.co.uk/Indenter/

MZ-Tools

MZ-Tools, an add-in containing a whole suite of useful extensions to Visual Studio and the VBA development environment, has a feature to properly indent source code.

Further info and free trial version: https://mztools.com/

Episode 8 - Use Procedures to reuse your code

DRY - Don’t repeat yourself! - This is an important principle of maintainable and effective programming. So we need to reuse the code we already wrote. The most basic form of code reuse is to write procedures instead copy/pasting code around.

Episode 9 - Improve Code Readability with Procedures

Another compelling argument for using procedures: Readability!

Writing procedures for code reuse is a fairly common concept. Recently, while reading Clean Code by Robert C. Martin, I became aware of a different reason to use procedure in you VBA code.

By structuring code into procedures, you can significantly improve the intuitive readability of your code. Watch the video to see how some complex, technical if-blocks read almost like plain English after I encapsulated them into their own functions.

I’m still amazed how effective this very simple approach is. All the more, as I have more than ten years of experience in programming and never had that idea myself.

Book recommendation: Clean Code: A Handbook of Agile Software Craftsmanship* - Robert C. Martin
(*=Amazon Affiliate Link)

Episode 10 – To Comment or not to Comment?

Comments are often viewed as the silver bullet to enhance code for better readability and maintainability. – But is this really the case?

Once upon a time, „commented source code“ was considered the premium edition of source code. These times are long gone, but the idea that adding more comments to VBA code makes it better still lingers on. - I question this narrative and urge you to carefully evaluate the value of every comment you write.

There are numerous potential problems with using comments in programming. They may distract from the essence of the code by providing useless, outdated, or even incorrect contradicting information. Even worse, some developers are encouraged to not bother to write readable code because they can “explain it in comments”.

Comments should always be a last resort to provide information or clarification that cannot be expressed in code directly. They should never be an excuse to not make your code as readable as you possibly can.

Episode 11 – How to use On Error Resume Next?

The On Error Resume Next statement makes the VBA runtime environment to suppress any error and to just execute the next line of code. – How convenient. – Not!

This is not a joke. Of course, it is technically simple to put an On Error Resume Next statement anywhere into your VBA code and it will do what you instructed it to do. – But did you really consider all the (side) effects that this statement will have? And will developers that are going to touch the code in the future also consider all consequences?

When I see On Error Resume Next in an application, even my own, there is often at least one bug connected to incorrect use of that error handling statement. So, how do you use On Error Resume Next correctly?

It is essential to narrow down the scope of On Error Resume Next as much as possible. Also pay really good attention to any check conditions you implement to evaluate the successful execution of any code affected by On Error Resume Next.

Conclusion

These are the videos recorded so far. - I welcome any feedback you might have. Send me an email or write a comment on YouTube.

Overall, I enjoyed recording and editing these videos much. I am pretty sure there are more to come. I’ve got some ideas shortlisted already.

Subscribe to my newsletter not to miss the next episode.

Share this article: Share on Facebook Tweet Share on LinkedIn Share on XING

Subscribe to my newsletter

*

I will never share your email with anyone. You can unsubscribe any time.
This email list is hosted at Mailchimp in the United States. See our privacy policy for further details.

Benefits of the newsletter subscription



© 1999 - 2024 by Philipp Stiefel - Privacy Policiy