Conquering Compile Errors

Common error messages that you face 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 run the code — it just compiles the one line to make sure that it runs 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 belongs. (If it knew, 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 would 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 now: They're just general examples.

Figure 12-4:

Sample compile error.

Figure 12-4:

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 retrieve a value while using multiple arguments, such as the title, buttons, and icon to show. Using MsgBox in that manner requires the following syntax (note the parentheses):

variable = MsgBox(prompt[,buttons][,title][,helpfile,context])

Here's where you get a clue to the whereabouts of the missing equal sign. Because the MsgBox statement in the code uses parentheses, we have to use variable = at the left side of MsgBox(), with parentheses around its arguments. The value returned by MsgBox() is 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-4 is this bit of corrected code:

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 might give you a vague hint of what the problem might be, but they neither solve the problem for you nor even tell you how to solve it.

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

Was this article helpful?

0 0

Post a comment