Breaking Up Long VBA Statements

Although one line of VBA code can contain as many as 1,024 characters, it is a good idea to break up a long statement into two or more lines to make your procedure more readable. Visual Basic has a special line continuation character that can be used at the end of a line to indicate that the next line is a continuation of the previous one, as in the following example:

Selection.PasteSpecial _ Paste:=xlValues, _ Operation:=xlMultiply, _ SkipBlanks: =False, _ Transpose:=False

The line continuation character is the underscore (_). You must precede the underscore with a space. You can use the line continuation character in the following locations in your code:

■ Before or after operators; for example: &, +, Like, NOT, AND

■ Before or after a comma

■ Before or after an equal sign

You cannot use the line continuation character between a colon and equal sign. For example, the following use of the continuation character is not recognized by Visual Basic:

Selection.PasteSpecial Paste: _ =xlValues, Operation: _ =xlMultiply, SkipBlanks: _ =False, Transpose: _ =False

Also, you may not use the line continuation character within the text enclosed in quotes. For example, the following usage of the underscore is invalid:

MsgBox "To continue the long instruction, use the _ line continuation character."

The above instruction should be broken up as follows:

MsgBox "To continue the long instruction, use the " & _ "line continuation character."

0 0

Post a comment