The continuation character

When writing VBA code, you can break a long line into two or more lines by using a continuation character, which is just an underscore (_). Many programmers use continuation characters to break lengthy VBA statements into two or more lines, especially in code you see printed in books and such because the code needs to fit within the margins of the book.

For example, this fairly long line of code barely fits within the margins in this book:

Public Sub MySum(anyName As String, anyNum as Number)

Here's the same line broken into two lines by using a continuation character:

Public Sub MySum(anyName As String, anyNum as Number)

When VBA sees the continuation character at the end of a statement, it knows that the line to follow is a continuation of the current statement, so it treats the two (or however many) lines as one long line.

If you want to use the continuation character when writing your own code, be aware that the continuation character never inserts blank spaces. If you need a blank space before the next word in a broken line, put a blank space in front of the continuation character. For example, the preceding example ends with a blank space and then the continuation character.

Also, be aware that you can't use a continuation character within a literal string in code. A literal string is any text that's enclosed in quotation marks. For example, the following line assigns a fairly long line of literal text to a control named MyCombo on the current form:

MelMyCombo.Value = "Literal text in quotation marks"

It's perfectly okay to break the preceding line as follows because the continuation character isn't inside the literal text:

Me.MyCombo.Value = _

"Literal text in quotation marks"

However, if you try to break the line within the literal text this way:

Me.MyCombo.Value = "Literal text _ in quotation marks"

the code fails when executed, and you get a syntax error.

We should mention, though, that you can break long strings of literal text in code in a couple of ways. One is to just keep adding chunks of text to a string variable by using variableName = variableName & "nextString"

You can see an example of that when building the mySql variable in Chapter 7.

The other way in which you can use an alternative to building a variable is to break the large literal into smaller literals, each surrounded by quotation marks. Concatenate (join) the strings by using the & sign, and break the line with a continuation character immediately after the & sign. For example, you could break the long literal, shown in the previous example, like this:

Me.MyCombo.Value = "Literal text" & _ " in quotation marks"

Don't forget to include any blank spaces between words inside your quotation marks. For example, in the preceding line, the space before in is the blank space between the words text and in.

When VBA "unbreaks" the line, like this:

Me.MyCombo.Value = "Literal text" & " in quotation marks"

the whole line still makes sense and executes perfectly, by placing the words

Literal text in quotation marks inside a control named MyCombo on the open form.

The first line in the following code example declares a string variable named SomeString. The next four lines are actually one long line that stores a lengthy chunk of text in the variable. Again, notice how each portion is contained within its own quotation marks. Each broken line ends with an & sign (to join strings) and an underscore (to continue the same line):

Dim SomeString As String

SomeString = "You can break VBA statements using" & _ " an underscore, but not inside a literal. If" & _ " you want to break a long literal, you have to" & _ " enclose each chunk in its own quotation marks."

Okay, that's enough talk about general VBA stuff. The title of this chapter is "Putting Recordsets to Work," and you do that in the next section. It shows you how to create a real solution to a real problem (for some people, anyway) by using VBA, some recordsets, and a little bit of everything else described in previous chapters.

Was this article helpful?

0 0

Post a comment