Syntax Errors Are Your Friend

Syntax errors are errors that occur when your code doesn't adhere to the syntax requirements of VBA. Perhaps no error is your friend, but if you had to pick an error as your friend, this would be a good choice. Syntax errors are relatively benign because they usually occur at development time and, as long as you have the Auto Syntax Check feature turned on, some can be automatically detected by the VBE as you enter your code.

NOTE Programmers frequently use the terms development time and run-time when discussing various programming topics. The term development time refers to the period of time when you are actively developing code. The term run-time refers to the period of time when your code is being executed.

But wait a minute, why doesn't the Auto Syntax Check feature detect all syntax errors? The Auto Syntax Check feature can detect only those syntax errors that it has enough context to detect. Specifically, it can only detect syntax errors that occur within a single statement or line of code. For example, the line

Debug.Prinf "This syntax error will be detected"

is a syntax error that the Auto Syntax Check feature can detect because it doesn't need any other statements to give this line meaning. This statement is an error because Debug.Print was mistakenly entered as Debug.Pnnf. Listing 4.1, by contrast, contains a syntax error that can't be found by Auto Syntax Check.

Listing 4.1: A Syntax Error Spanning Multiple Lines

Sub MissingNextStatement() Dim n as Integer

Sub Ca11BadProcedure()

MissingNextStatement End Sub

The problem with Listing 4.1 is that it is missing a Next statement. I classify this as a syntax error because it violates the syntax requirements of VBA. Every For statement should be followed at some point in the same procedure by a Next statement. Auto Syntax Check can never alert you to this error because it checks individual statements only as they are entered. Therefore, it can't check for syntax errors that require other statements to ensure proper procedure formation.

Technically, I would still classify this more difficult form of a syntax error as a development time error. Unfortunately, developers often release applications containing syntax errors and their users stumble into them at run-time. This would lead one to believe that these are run-time errors; however, this is not technically correct. End users should never see these kinds of errors because they are easily detected and corrected at development time. I must admit to being guilty of committing the following sin myself. Just so nobody misses the following important debugging rule, we should put this in lights:

Debugging Rule #1: Always compile your project before distributing your "completed" application. You may need to compile multiple times as the compile process stops when it finds its first error. Keep compiling until you aren't notified of any problems. Compile by selecting Debug ^ Compile YourProjectName.

Debugging Rule #2: Use the auto syntax check option. Occasionally it helps to turn this option off for short periods when you are copying/pasting code into a module that requires some editing. This option is found on the Editor tab of the Options dialog box (Tools ^ Options).

The reason the error in Listing 4.1 can appear at run-time is that VBA lets you save and distribute applications without compiling them as you would in most other programming languages. When your users run your application and cause something to happen that executes the procedure containing the syntax error, an error occurs. By compiling your application before you distribute it, you force it to be subjected to the VBA compiler; this ensures that all of the code in your project is syntactically correct. You can use Listing 4.1 to experiment with this—just follow these steps:

1. Enter all of the code into an empty module and then save the workbook. Notice that you aren't warned of any errors.

2. Switch to Excel, press Alt+F8, and run the macro CallBadProcedure, which just executes the MissingNextStatement procedure. Bingo. You experience the error that your users would see if you sent the workbook to them without compiling it first. The fun-loving users will love to rib you about the "special functionality" you built into your application.

3. In the VBE select Run ^ Reset to exit from Break mode.

4. Now you need to fool the compiler into thinking it needs to compile again because when you performed step 2, the compiler compiled the MissingNextStatement procedure on demand. Place the cursor at the end of any line and hit the Space bar. The VBE interprets this as a change to your code and allows you to recompile the project in the following step.

5. Compile the application by selecting Debug ^ Compile VBAProject.

When you perform step 5, the compiler issues the warning shown next. Remember, the last thing you should do before distributing your work is compile your project to ensure that you don't have any syntax errors in your application.

\M Microsoft Visual Hasit - ( hapier 4 Eiampln . - [Modulel |Code)J


£dit iitew Insert Format


&un Iools fidd-Ins Window

Help - B



1 J


jj j % s



I |(General

| MiiisnvjIlextSt.item-eiTt


Option Explicit


HissingNextStatement(i Dim n As Integer

For n = 1 To 5


Microsoft Visual Basic | X |


Sub End

Ceil IBadPro cedure () Hiss i ngNe x tStAt eraent


Compile error: For without Next

OK | Help


PracticeFit hBr eakHodi

Dim n As Integer


+1 0

Post a comment