CATCHING SYNTAX ERRORS


If you enter a worksheet formula incorrectly in Excel, Excel alerts you to the error and refuses to accept the entry. The VBA compiler (the system component that converts your English-like VBA code into machine language) normally performs the same service for you if you enter a VBA expression incorrectly. If you omit a required parenthesis, for example, the compiler beeps as soon as you press Enter. It also presents an error message and displays the offending line of code in a contrasting color (red, by default).

Certain kinds of syntax errors don’t become apparent to the compiler until you attempt to run your code. For example, if you write the following:

    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
and attempt to run this code without including an End With statement, you will see an error message: "Compiler error: Expected End With". Your procedure will halt, and you will be in break mode. (You can tell you’re in break mode by the appearance of the word “break” in brackets in the VBE title bar. The line that the compiler was attempting to execute will be highlighted—yellow, by default.) Break mode lets you fix your code and then continue running it. For example, if you omit an End With statement, you can add that statement while in break mode, and then press F5 (or choose Run, Continue) to go on with the show. If you want to exit from break mode rather than continue with the execution of your procedure, choose Run, Reset.

If you don’t like having the compiler complain about obvious syntax errors the moment you commit them, you can turn that functionality off. Choose Tools, Options, click the Editor tab, and clear the Auto Syntax Check check box. With automatic syntax checking turned off, your syntax errors will still be flagged when you try to run your code.

Auto Syntax Check is on by default. So are three other “auto” options: Auto List Members, Auto Quick Info, and Auto Data Tips. These are all useful, and you should leave them on, especially if you’re a relative newcomer to VBA. Auto List Members and Auto Quick Info help you complete a line of VBA code by displaying options available at the current insertion point or the names of arguments required by the function you’re currently entering. Auto Data Tips is relevant only in break mode. If you hover your mouse cursor over a variable name in break mode, the Auto Data Tips feature displays the current value of that variable as a ScreenTip.