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:
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.
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.
There are lots of useful tools available in this toolbar. For now, I am only going to mention 2 of them.
- If you wish to indent blocks of code, or to remove indentation then use the buttons marked with  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.
- 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 …
From the Options Window de-select Auto Syntax Check, as shown by  in the window below.
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  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.
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).
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)
This will open the Immediate Window below the coding window.
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.