This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

How to make the Visual Basic Editor less annoying

Make VBE less annoying

Make VBE less annoying

Writing VBA code is not always easy, just trying to get the right flow of logic can be quite taxing.  Sometimes, even just using the Visual Basic Editor can be taxing by itself.  I can’t even begin to count the number of times I have been annoyed by the following error message popping up:

VBA Editor Error

I know how to write IF statements in VBA. I’ve done them thousands of times, but this message still appears.  Often it occurs when I just want to quickly fix something else whilst half way through an IF statement.  So, I click elsewhere to fix the problem.  ARRRRGGGHHH!!!! Error message appears! The Visual Basic Editor has decided that I absolutely must be made aware of this error instantly!  I must now click OK, before I can go and fix the error, then go back to complete my IF statement.  Very, very, very annoying!  However, there are things which we can do to make the Visual Basic Editor just a little bit less annoying, and this is what we will be covering today.  Along with preventing this error message, we will also be looking at a few other tips.

 

Displaying the Edit Toolbar

The Edit Toolbar contains some of the most useful tools.  But, for some reason, it is not enabled automatically.  To enable this Toolbar click View -> Toolbars -> Edit.

VBA Editor Edit Toolbar Turn On

Once enabled you should see the additional tools.  Initially, it may be a floating window, but you can drag this window over the top of the Visual Basic Editor, it will be added to the menu at the top.  In the screen shot below I have placed this toolbar above the coding window.


Advertisement:

VBA Editor Edit Toolbar Display

There are lots of useful tools available in this toolbar.  For now, I am only going to mention 2 of them.

  1. If you wish to indent blocks of code, or to remove indentation then use the buttons marked with [1] above.  The relative indentation in the code will be maintained.  The whole code block will just indent or de-indent everything in the selection by the same amount.
  2. Commenting out big sections of code can be useful.  If we want to try something new without losing the existing code, we can put comment marks next to the old code, so that it is not executed.  But we don’t need to keep pressing apostrophe and down arrow over and over again.  We can now add or remove the apostrophes to any blocks of code we have selected.

 

Turn off Auto Syntax Check

The error message discussed in the introduction to this post is caused by Auto Syntax Check.  We can we can turn off this check.  Don’t worry, we will still be able to see the errors, the VB Editor will highlight the text in red.  But the annoying error message will not appear – YAY!

To turn off the Auto Syntax Check click Tools -> Options …

VBA Editor Options

From the Options Window de-select Auto Syntax Check, as shown by [1] in the window below.

VBA Editor Options Window


Advertisement:

Click OK to accept the changes.

 

Forcing variables to be declared

Debugging code can be a tricky thing.  In many programming languages variables are created on the fly.  If I wanted to create a new variable, I just type its name and it’s created.  However, my typing is not 100% accurate.  Therefore, I can easily create 2 variables, one spelt correctly and the other due to a mis-spelling of the first one.  The software doesn’t know it’s a typo, so it just creates 2 variables.

VBA can set set-up to only accept variables which have been declared.  This is a great feature, as any spelling differences are identified as errors, rather than as new variables.

If the words Option Explicit automatically appear at the top of the coding window this option is already turned on.  If Option Explicit does not appear automatically then click Tools -> Options …From the Options Window select Require Variable Declaration, as shown by [2] in the section above.  Click OK to accept the changes.

 

Using the Immediate Window

For years I did not know the Immediate Window existed, nobody had ever told me.  If I wanted to check how my code was being executed I would insert message boxes all over the place.  When the code was executed all the message boxes would pop-up to show me values of variables or the progress of the code execution.


Advertisement:

But it is possible to use the Debug.Print statement within your code, which will output the requested information to the Immediate Window (no more message boxes).

Example code

Sub ImmediateWindowTest()

Dim Message As String
Message = "Show me in the Immediate Window"

Debug.Print Message

End Sub

If we were to run the code above without the Immediate Window we would see nothing.  But once the Immediate Window is open we can see the output of “Show me in the Immediate Window” within the Immediate Window.

To turn on this feature click View -> Immediate Window (or Ctrl+G as the shortcut)

VBA Editor Immediate Window Turn On

This will open the Immediate Window below the coding window.

VBA Editor Immediate Window Example

 

The End

These may not seem like big changes, and you may not use these features straight away, but you have just made significant steps towards making your VBA coding life much less frustrating.