Excel and the VBA Editor

Make the VBA editor screen your friend

When you first start out using VBA and writing VBA (macro) code it is useful that the VBA Editor helps you quickly identify when you have made a mistake. If a line of code has an error it will display a dialog box that explains the error and turns the line of code red.

Once you have been coding for a while and are comfortable with coding, the dialog boxes that you have to respond to, can slow you down and become annoying.

You can turn off the error dialog boxes that appear as you code. This will still leave the line of code in red if it has an error.

In the VBA window click the Tools menu and click Options.

VBA Tools Options

 

Untick the “Auto Syntax Check” option.

 

VBA Edit options
Click OK.

Note – I also have the “Require Variable Declaration” option ticked to make sure I create a Dim statement for all variables I use. With this option Excel warns you of all variables that don’t have a Dim statement. This reduces errors due to typing errors with variable names.

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.