Line Continuation

Strategic line breaks also help make code easier to read and understand. Many VBA statements are quite long. Take the following lf...Then statement used to fill a variable with a value:

If (txtCustomerState =

"CA" And

txtCustomerZip = "95685") Or

(txtCustomerState =

"WA" And

txtCustomerZip = "89231") Then

txtCustomerRegion =

"Western

US"

End If

As you can see, this code is a bit long. When printed in this book, even the conditional portion of the statement takes up several lines. When you write this code in VBA, all of the code will go on one very long line. Obviously, the line won't all display on the screen, as shown in Figure 5-3. This can make procedures difficult to read, as you need to not only scroll up and down to view the entire procedure but scroll left and right, as well.

A line continuation character (an underscore preceded by a space) is used to break long lines of code into understandable, easy-to-read segments. Although you are inserting characters, they are for visual appearance and do not change the meaning of the code. The space/underscore at the end of a code line indicates that the next line is a continuation of the current line as in the following code snippet, as shown in the second example in Figure 5-3. The benefit of using line breaks is evident.

If (txtCustomerState : (txtCustomerState txtCustomerRegion End If

CA" And txtCustomerZip = "95685") Or _ "WA" And txtCustomerZip = "89231") Then "Western US"

■Chapter 5 Line Continuation Sample with en If THEN statement

■BEFORE - Code telow is written without Line Continuation ■Notice that the text only wraps after the word Ther..

If (txtCu3tom.erState = "CA" And txtCustomerZip = "35635"] Or (txtCu3torr.e: txtCustonerRegion = "Western US" End If

■AFTER - The same example with line treats created ty adding a space ■and underscore 'at the end of the line. The treak3 not only keep the ■code within the viewing pane, tut they also follow natural grouping. ■Again, the underscore is not required after the word Then.

If (txtCustomerState - "CArr And txtCustomerZip - "95635") Or _ (txtCustomerState = "WA" And txtCustomerZip = "39231"} Then txtCustomerRegion — "Western US" End If

Figure 5-3

Strategic line breaks not only keep the code within the viewing pane, but they also help developers recognize individual steps within a procedure. There's one limitation to the line continuation character, however; you can't use it "as is" within literal strings. If you're using the line continuation character in a string, you must use the & symbol on the continued line and add extra quotation marks—one before the space/underscore at the end of the line and one after &/space at the start of the next line. Figure 5-4 illustrates the following examples. When opening a recordset with an SQL statement, you could end up with a very long SQL statement to include all of the fields you need within your table. The statement might read something like the following:

strSQL = "SELECT [CustomerName], [CustomerCode], [CustomerAddressl], [CustomerCity], [CustomerState], [CustomerZip] FROM Customers WHERE [CustomerState] Is Not Null;"

You can use the line continuation character along with the & symbol to turn that code into the following:

strSQL = "SELECT [CustomerName], [CustomerCode], [CustomerAddressl]" & ", [CustomerCity], [CustomerState], [CustomerZip] FROM" _ & " Customers WHERE [CustomerState] Is Not Null;"

"Line Continuation Sample for a SQL statement

■BEFORE - Code below is written without Line Continuation strSQL = "SELECT [CustomerName], [CustomerCode], [CustomerAddressl], [CustomerCity]

"AFTER - Using Line Continuation makes code easier to read. ■ There is a " at the end and beginning of each line of the code. ■Only the part within the quotes is concatentated into the string of code. 'Notice the space after the comma at the beginning of the second line "and after the " at the beginning of the third line.

strSQL = "SELECT [CustomerName], [CustomerCode], [CustomerAddressl]" _ s [CustomerCity], [CustomerState], [CustomerZip] FROM" _ |& " Customers WHERE [CustomerState] Is Not Null;"

Figure 5-4

Use line continuation characters any time you have code lines longer than your screen width. Keep in mind that you need to include the space that is used to separate words. It can be placed after the last character at the end of the line but before the underscore, or between the quotation mark and the first character at the beginning of the next line. Without the appropriate spaces, words can run together. So the preceding example, might end up with " ... [CustomerZip] FROMCustomers WHERE..."

Was this article helpful?

0 0

Post a comment