Line Continuation

Many VBA statements are quite long. Take the following If...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 fit on one very long line; however, the line won't all display on the screen, as shown in Figure 4.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.

i Microsoft Visual Basic - VBA Samples - [Samples (Code)]

ê^ File Edit View hiseit Debug Run Tools All(1-1 lis Window Help Type a question for lielp

; m « - y i * ^ a « i *> p-1 ► «a a fegj $ of * i m \ LU 132, coi 20 I

Pinjeci VQlA

É-fi Microsoft Office: B-Q Modules

A Module 1 ■ili Samples Class Modules iL

PlMpfMili? S H'lpEJ

I Samples Module Alphabetic | Categorised

] Samples

I (General)

▼ j j OpeiiDatabaseConnection st.rState = InputSox (rrPlease enteu a st ate7, "EnterState") ~

strConn = "Provider=Microsof t. Jet. OLEDB . 4 , □; Data Source=c:\Databases*AddresiSI

st.rSQL = "SELECT [Cust.omerWaine] , [Cust.omerCode] , [Cust.omerAdclres31] " _ £ "., [CustomerCity] , [CustomerState] , [CustomerSip] FROM" _ £ "Customers WHERE [CustoinerState] is Wot Hull;"

strSQL = "Select * from tblCustomers WHERE CustomerState = 1 " £ strState £ 1

objConn. Open (strConn) objConn.Mode = adModeRead objRST, Open st.rSQL, objConn, adOpenForwardOnly, adLockOptimist.ic objRST. MoveFirst.

While Wot ObjRST,EOF

txtCustomerState = obj RST.F ields("CustomerState") txtCustomerZip = objRST. Fields ("Cust.omerZip")

If (txtCustomerState = "CA" And txtCustomerZip = "55685") Or (tx'tCustomf txtCustomerRegion = "WesternUS" End If objRST. HoveWext|

Figure 4-3

Instead of writing very long lines of code in VBA, use the line continuation character to break long lines of code. Ending a line of code with a space followed by an underscore signifies that the next line is a continuation of the current line as in the following code snippet, shown here and in Figure 4.4.

If (txtCustomerState = "CA" and txtCustomerZip = "95685") or (txtCustomerState = "WA" and txtCustomerZip = "89231") then txtCustomerRegion = "Western US" End If

Microsoft Visual Basic - VBA Samples - [Samples (Code)]

File Edit View hiseit Debujj Run Tools Add-lns Window Help

! HI Ä - IS I A ^ IS Ä I *) V { A m a I % of I # I Ln 134, Col 5

Pmjetl VEIA S.nnE

VBA Samples (VE

5-

m Microsoft Offici

S-

& Modules

= Module 1

= Samples

IB

P7] Class Modules

<1

1

Pli>|KMliSS S0lll|>CJ

Samples Module Alphabetic Categorized

Pli>|KMliSS S0lll|>CJ

Samples Module Alphabetic Categorized

I Samples

J (General I

t| |0|)enDîitiibiiseC4nnection st.rState = Inputïïox("Please enter a state", "EnterState" ) ~

strConn = "Provider^Hicrosof t.. Jet. OLELB . 4 , 0 ; Data SDurciE=cj : \ Datatia3es\ Addresl ID

st.rSQL = "SELECT [Cust.omerWame] , [Cust.omerCode] , [ Cust. ornée Address 1] " _ £ ", [CustomerCity] , [Cust.omerSt.ate] , [Cust.omerZip] FRÜH" _ £ '"Customers WHERE [ Cust.omerSt.ate) i3 Wot Null;"

st.rSQL

"Select * "from t.fcilCu3t.oiiier3 TiTHE P. E Cust.overstate =

ofcijConn. Open (st.rConn) objConn.Mode = adllodeRead otojRST, Open st.rSOL, objConn, adOpenForuardOiily, adLockOpt.imist.ic oti jRST. JIoveFi rst-

Bhile Wot objRST,EOF

txtCustomerState = olojRST. Fields ("CustomerState"! txtCustomerZip = otojRST.FieIds("Customer Zip")

If (txtCustomerState = "CA" And txtCustomerZip = "95685") Or (txtCustomerState = "UA" And txtCustomerZip = "89231") Then txtCust.omerRegion = "Uest.ernUS " End If otojRST.HoveWext j Stan

Figure 4-4

IJÛC..I g|M..| :_JA...| [>JM..||^M.. | « ^^^©-iä^Ki^lS 11:33 AH

Isn't that easier to read? There's one limitation to the line continuation character though; you can't use it within literal strings. If you're using the line continuation character within a string, there's one way around this limitation. You can use the & symbol on the continued line and add extra quotation marks to work around this limitation. For example, if you need to open a recordset with an SQL statement, you could end up with a very long SQL statement in order to include all of the fields you need within your table. The statement might read something like the following:

strSQL = "SELECT [CustomerName], [CustomerCode], [CustomerAddress1], [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], [CustomerAddress1]" _ & ", [CustomerCity], [CustomerState], [CustomerZip] FROM" _ & "Customers WHERE [CustomerState] is Not Null;"

You should use the line continuation character any time you have a line of code longer than your screen width.

Was this article helpful?

0 0

Post a comment