1 / 26

Controlling Execution

Controlling Execution. 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. IF and only if. One line IF (without END IF) IF (condition) Then Exit Sub One seeming line IF IF (condition) Then _

werner
Download Presentation

Controlling Execution

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 > 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(blnMale=True,”Male”,”Female”) • Useful in queries, properties, division • IIF(intN > 0, 300/intN, 0) ' to avoid an error msg • IIF(PercentCorrect>=90,"A", _ • IIf(PercentCorrect>=80,"B", _ • IIf(PercentCorrect>=70,"C", _ • IIf(PercentCorrect>=60,"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(intInput+1) & “?”

  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))

More Related