Controlling Execution

1
Controlling Execution
2
IF and only if..

• One line IF
• IF with and without ENDIF
• IF with and without ELSE
• One line ELSEIF
• Multiple ELSEIFs and Select Case

3
IF and only if..

• One line IF (without END IF)
• IF (condition) Then Exit Sub
• One seeming line IF
• IF (condition) Then _
• Exit Sub
• IF with ENDIF
• IF (condition) Then
• Exit Sub
• END IF

4
IF and only if..

• One line IF with ELSE
• IF (whatever) Then Yada ELSE YadaYada
• Five line IF with ELSE
• IF (whatever) Then
• Yada
• ELSE
• YadaYada
• END IF

5
IF and only if..

• ELSE required alone on one line
• IF (whatever) Then
• Yada
• ELSE
• YadaYada
• END IF
• THEN at end of line required
• END IF required alone on one line

6
ElseIF and only if..

• ElseIF is subordinate to the previous IF
• IF (whatever) Then
• Yada
• ElseIF (whatever) Then
• YadaYada
• ELSE
• YadaYadaYada
• END IF

7
Select Case vs ElseIF

• Select Case neat and maintainable
• Case formats very flexible
• Case 1,3,7
• Case 2 to 8
• Case CA to NM
• Case Is gt 21

8
Select Case Example

• Select Case Security
• Case "V"
• ' set properties for volunteers
• Case "S"
• ' set properties for supervisors
• Case ""
• ' exit sub
• End Select

9
Immediate If!

• IIF is a function
• IIF(condition, true return, false return)
• IIF(blnMaleTrue,Male,Female)
• Useful in queries, properties, division
• IIF(intN gt 0, 300/intN, 0) ' to avoid an error
  msg
• IIF(PercentCorrectgt90,"A", _
• IIf(PercentCorrectgt80,"B", _
• IIf(PercentCorrectgt70,"C", _
• IIf(PercentCorrectgt60,"D","F"))))

10
For Loops

• For intX 1 to 10 Step 2
• For Each varX in varDataSet
• Next required for these two For formats
• Exit For
• Restrictions on For Each
• 1-dimension only
• Each control variable must be Variant

11
For Loops Examples

• Dim varDataSet(0 to 49) as Variant
• Dim intX as Integer, intOdd as Integer
• Dim intSum as Integer
• Dim varItem as Variant, varSum as Variant
• ' sum the odd numbered elements (1,3,5,)
• For intX 1 to 49 Step 2
• intOdd intOdd varData(intX)
• Next
• ' sum all elements (1,2,3,)
• For Each varItem in varDataSet
• intSum intSum varItem
• Next

12
Do Loops

• Conditional logic after 1 pass through loop
• Do Loop While
• Do Loop Until
• Or, conditional logic before 1st pass through
  loop
• Do While Loop
• Do Until Loop
• Exit Do

13
Arrays

• Lists (states, income levels, schedules, etc)
• Private, Public, fixed or dynamic size
• Dim ccyIncomeLevel(0 to 5) As Currency

14
Which Array to Go?

• Option Base 1 or Option Base 0?
• Public strState(0 to 49) As String
• Public strState(1 to 50) As String
• Public strState(50) As String
• Above will be (0 to 49 or 1 to 50)depending on
  Option Base

15
strMonth(1) "January" strMonth(2)
"February" strMonth(3) "March" strMonth(4)
"April" strMonth(5) "May"
intInput InputBox("Enter Month
Number") MsgBox You mean strMonth(intInput)
? Or MsgBox You mean strMonth(intInput1)
?
16
Flexible Array

• Dynamic
• Dim ccyLevel() As Currency
• Expands and contracts
• Good for uncertain run-time conditions
• Can loose contents unless
• Redim Preserve ccyLevel(7)
• Can erase dynamic arrays Erase ccyLevel

17
Strings
18
Notes on strings

• String variables (Dim strName as String)
• String literals (txtName "Jones")
• Mixing variables and literals
• Select from tblRoster Where Name "Smith"
• How to embed quotes around strName?
• "Select from tblRoster Where Name " ???

19
How to embed single quotes
' If the name is a hard-coded literal Debug.Print
"Name 'Smith'" ' If the name is in a variable
(very likely) Dim strName As String strName
"Smith" Debug.Print "Name '" strName "'"
20
How to embed double quoteswith literal strings
' When the name is a hard-coded
literal Debug.Print "LastName ""Smith"""
'Coding hints for the triple quotes above
'step 1 LastName "Smith" 'step 2
LastName ""Smith"" 'step 3 "LastName
""Smith"" "
21
How to embed double quotesusing variables
Dim strName As String strName
"Smith" Debug.Print "LastName """ strName
"""" 'Coding hints for triple/quadruple
quotes 'step 1 "LastName strName" 'step
2 "LastName """ strName " 'step 3
"LastName """ strName """ "
22
How to embed double quotesas a variable
Dim strName As String strName "Nguyen" Dim
strQuote As String strQuote Chr(34) ' ACSII for
double quote strName strQuote strName
strQuote Debug.Print "LastName " strName
23
String Functions

• mid(strHayStack, intStart, intLength)
• Returns string or part of a string
• instr(intStart, strHayStack, strNeedle)
• Returns position where needle is in haystack

24
Mid()

• MyString "Mid Function Demo"
• ' Create text string.
• FirstWord Mid(MyString, 1, 3)
• ' Returns "Mid".
• LastWord Mid(MyString, 14, 4)
• ' Returns "Demo".
• MidWords Mid(MyString, 5)
• ' Returns "Function Demo".

25
InStr()

• strHaystack "XXpXXpXXPXXP
• strNeedle "p"
• intWhere Instr(1, strHaystack , "W")
• Returns 0
• intWhere Instr(4, strHaystack , strNeedle )
• Returns 6

26
Other String Functions

• Left() and Right() ' args string, no. of chars
• Ucase() and Lcase() ' one arg only
• Trim(), Rtrim() and Ltrim() ' one arg only
• Val(strA) ' change a string to a number
• Cstr(intX) ' change a number to a string
• Because Cstr(57) returns " 57" not "57" use
  Trim(Cstr(intX))