Conquering Compile Errors

The error messages that you'll face most often are the compile errors that happen in the VBA editor Code window. Every time you type a complete VBA statement and then move the cursor to some other line in the procedure, VBA quickly compiles that line of code. It doesn't actually run the code — it just compiles the one line of code to make sure that it will run when you run the procedure.

When you're first learning to program, compile errors might seem incessant and unstoppable. That's only because you're not yet familiar enough with the VBA language to write valid statements. And perhaps you haven't yet accepted the fact that when it comes to writing code, guessing never works. Either you know how to use a particular statement or you don't. You really have to know how to use all the help that's available to you — and use it well.

For more information on the various types of Help available to you in the VBA editor, read about understanding syntax in Chapter 3 and objects and collections in Chapter 5.

Compile error messages are rarely specific about what the problem is. For example, Figure 12-4 shows an Expected: = message, triggered by the MsgBox statement shown in the code. The error message tells you that the compiler was expecting to find an equal sign (=) in that statement, but it doesn't tell you where the equal sign should go. (If it knew where the equal sign belongs, it wouldn't have to show the message. It could just put in the equal sign for you.)

Clicking the Help button in the error message box rarely helps much. In this case, you'd just get a brief description of the problem and a few examples. However, the examples aren't necessarily relevant to the code that you're writing at the moment: They're just general examples.

Figure 12-4:

A sample compile error.

Figure 12-4:

A sample compile error.

The only real solution is to find out the correct syntax for the MsgBox keyword. As it turns out, there are two syntactical forms of MsgBox. The first form, which you can use to just show a simple message with an OK button, is

MsgBox prompt where prompt is the message to display in the box (either as literal text enclosed in quotation marks or the name of a variable that contains text).

The second form allows you to specify multiple arguments, such as the title, buttons, and icon to show. Using MsgBox in that manner requires the syntax variable = MsgBox(prompt[,buttons][,title][,helpfile,context])

Here's where you get a clue as to the whereabouts of the missing equal sign. Because the MsgBox statement in the code uses multiple arguments, I have to use variable = at the left side of MsgBox(), with parentheses around its arguments. The value returned by MsgBox() will be a number indicating which button the user clicked, so the variable accepting that value should be declared as an integer. Thus the correction to the problem code in Figure 12-5 is the corrected code shown here:

Public Sub Sample()

Dim Answer As Integer

Answer = MsgBox("Hello World", vblnformation, "Test") End Sub

The main point here is that the error message Expected: = really didn't tell you how to solve the problem. The only real solution to the problem was to find out how to use the MsgBox() statement and to see some examples of its use in Help. That's typical of compile error messages: They maybe give you a vague hint as to what the problem might be, but they never solve the problem for you nor even tell you how to solve the problem.

Take a look at some more common (and usually unhelpful) compile error messages and the solutions to the problems they've found.

0 0

Post a comment