October 28

My Top 5 VBA Development Environment Tips


So you’ve got a project that requires VBA – you’re ready and excited – and you jump right in! But here come the headaches! With every syntax error the visual basic environment interrupts your programming mojo with an annoying popup. You remembered to place comments in your code – but where are the ones you’re looking for? When did Excel programming become so frustrating?
Below, I’ll describe how I’ve customized my editor to minimize annoyance and maximize efficiency. It might not help you become a better programmer…but then again, it really might! Your best work probably comes from a space you can call your own. So season your VBA developer environment to taste.
Tip #1: Change the Font
The problem is that the default font, Courier New, does a poor job differentiating between colors – especially when your eyes quickly scan the screen. Don’t believe me? See the difference for yourself with my personal font choice: Consolas, 9pt.
Go to Tools > Options, then click on the “Editor Format” tab to try out a new font. Whatever you choose, you’ll need to keep these things in mind:

  • You want a font that makes a significant contrast between colors.
  • You want a fixed-width font. If you choose a font that is not fixed-width, you’ll have trouble using Tab to align your code. Some well known fixed-width fonts are: Consolas and Lucida Console.
  • Pick something readable. Stay away from Mistrel.
Tip #2: Change your Comment Formatting
Make a statement with bold comments! Seriously though, the default comment formatting blends right in with the rest of the code. And that’s a pain – especially, when you’re on the hunt for some code you had commented to save for later.  I like my comments with a light-blue highlight in the background and a dark-blue foreground. They really stand out.
Go to Tools > Options, then click on the “Editor Format.” Select “Comment Text” from the list to get started.
Tip #3: Use the Immediate window, immediately
The Immediate window should be your best friend, but some folks didn’t even know it exists. Go to View > Immediate window if it’s not already open.
Why is it so great? Well, the immediate window allows you to print essential information to the screen while your program is running. Let’s say you need to iterate through tons of data and would like to know your intermediate progress but only temporarily. You can print to the immediate window by using Debug.Print(). Go ahead, try Debug.Print “foo!” in a Sub.
But wait, there’s more: you can also gain information even when you’re not running anything. Go place a shape onto an empty spread sheet and make sure you’ve selected it. Now go to your Immediate window and type Msgbox Selection.Width. Hit Enter.
Is your UserForm stuck in an endless loop? (it happens sometimes) Do a CTRL+Break and type “Unload Me” into the Immediate window to return everything to normal.
Tip #4: No more syntax error pop-ups
Sometimes you’re typing an IF/THEN but see some code above it that needs fixing. You click-off to go fix the code but you’re stopped by an annoying popup message. I mean, it’s nice of VBA to let you know there’s an error, but it didn’t need to ruin your flow.
So get rid of those nasty pop-ups by going to Tools > Options and unchecking Auto Syntax Check. The environment will still tell you that you have an error by highlighting the offending script in red – but the pesky popup box will bother you no more.
Tip #5: Opt for Option ExplicitBy putting Option Explicit at the top of your code, you are helping yourself so much. Seriously, I’m such an advocate of Option Explicit that I named my blog after it.
What does it do? It requires that you declare your variables. If you don’t write “Dim i as integer,” you will not be allowed to use i for anything. Without explicit declaration, VBA assumes that any new variable introduced is a variant type.
Explicit declaration might sound like more work, but trust me you’ll save yourself some headache. You’ll find that you might not always spell your variables correctly, for example, “RecordCount” might accidently be spelled as “RecrodCount.” When your program doesn’t give the correct output (because it assumes “RecrodCount” is actually a new variable), you’ll be forced to scour through your code looking for the problem, which you might not even realize is a typo. And if you missed the typo when you first typed it out, it’s likely that your quick scanning will miss it again. By requiring explicit declaration, undeclared variables will result in a compile-time error, so you can fix them immediately.
Don’t make Option Explicit optional. Go to Tools > Options and proudly check “Require Variable Declaration.”
Your developing environment is just as important and personal as any other. Make sure you use the features and options within the Visual Basic Environment to customize to the fullest.



You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
  • My recent change in the VBE settings was to change the tab width from 4 to 2 spaces. It gets rid of extra white space yet still allows enough of a tab-structure to still comprehend the VBA code easily.

  • I am quite late to this site. Some excellent advice but a minor nitpick. I strongly agree with the importance of Option Explicit. However, when you say, “By requiring explicit declaration, undeclared variables will result in a runtime error, so you can fix your variables before they become hidden within your code,” the answer is even better: an undeclared variable will result in a compile-time error. That means, if you compile your code before letting others touch it (a good practice that takes about one second), you will catch your errors before users ever see it.

    • You’re correct about that. It’s definitely a compile-time error. Since I reuse most of my content in other places, I have a vague memory that I may have fixed this at some point. Either way, good catch!

  • 1) I never did understand why Option Explicit wasn’t the default setting for the VBIDE. To not use it is to beg for trouble, really.
    2) Option Explicit has the side benefit of making your code sound…more exciting 🙂
    3) I’d add an item to select “Break in class modules”

    • 1). That’s because you’re a developer and it’s the only choice that makes sense for serious coding. VBA was designed to be friendly to those who aren’t developers and will never learn to code. You can record a macro and make small-but-useful tweaks without having to understand very much: try that with C#!. I aim higher than that, but I wouldn’t want to exclude these users. If it’s a tradeoff that leads to us being taken less seriously, I can live with that.
      2) Yes! Scripting is adventurous work that doesn’t appear to be from the outside so impactful language is beneficial on that level.
      3) This one really should be a default. It is profoundly unhelpful to get a runtime error and not be able to see beyond the call to the class object.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.