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.

Advertisements

9 thoughts on “My Top 5 VBA Development Environment Tips

  1. Chris Macro

    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.

    Reply
  2. Jon Gladstone-Gelman

    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.

    Reply
    1. Jordan G Post author

      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!

      Reply
  3. Patrick Matthews

    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”

    Reply
    1. Jon Gladstone-Gelman

      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.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s