1. Access VBA Programmingfor Beginners - Class 4 - by Patrick Lasu p_lasu@lycos.com

2. Class 4 - Overview • Coding Concepts • Reading Code • VB Operators/Characters: • Concatenation, Line Continuation, Comments • Stepping through Code • IF Statements • IIF Statements • Select Case Statements

3. Coding Concepts • Reading Code • Reading code can be tricky • Statements to the left and right of an equal sign will not be equal until the code is executed (and then it can be “not equal” again). • Tip 1: Read (evaluate) from the right of the equal sign to figure it out • Tip 2: Do not worry about “past” statements

4. VBA Code intValue = 5 + 2 Next Line of Code Behind the scenes 0 = 5 + 2 Next Line of Code Coding Concepts Reading Code – Example 1

5. Behind the scenes 7 = 5 + 2 Next Line of Code VBA Code intValue = 5 + 2 Next Line of Code Coding Concepts Reading Code – Example 1

6. Behind the scenes 0 = 5 0 = 0 + 2 Next Line of Code VBA Code intValue = 5 intValue = intValue + 2 Next Line of Code Coding Concepts Reading Code – Example 2

7. Behind the scenes 5 = 5 5 = 5 + 2 Next Line of Code VBA Code intValue = 5 intValue = intValue + 2 Next Line of Code Coding Concepts Reading Code – Example 2

8. Behind the scenes 7 = 5 7 = 7 + 2 Next Line of Code VBA Code intValue = 5 intValue = intValue + 2 Next Line of Code Coding Concepts Reading Code – Example 2

9. Coding Concepts • Concatenation character: & (ampersand) • Used for putting expressions together Example: strFirstName = “John” strLastName = “Doe” strFullName = strFirstName & “ “ & strLastName • Avoid using ‘+’ to concatenate, it can produce unexpected results

10. Coding Concepts • Line continuation character: _ (underscore) Example: Msgbox “This is important“, _ vbOKOnly, “My Message” String Example: strMsg= “This is a very important “ & _ “message from me!” • Limit is 25 lines, or 24 line continuations

11. Coding Concepts • Making comments • Use ‘ (apostrophe) to start a comment • Comments are not executed; used to document what the code is suppose to do • No need to write an essay Example: ‘Assign a value intValue=5 Rem Example: Rem Assign a value intValue=5

12. Coding Concepts • Stepping through code • Use [F5] in the code window to execute the code • Use [F8] in the code window to execute the code one step at a time • Works in a Standard Module, does not work in a Form Module.

13. IF Statement • An IF statement evaluates a condition to find out if it is True or False, then executes the appropriate statement(s) • Type the word ‘If’ in the code window and press [F1] for help on the topic

14. IF Statement • Syntax (Single Line): If condition Then [statements] [Else elsestatements] If Sales>100K Then Bonus=10% Else Bonus=1% • Syntax (Multi-Line) If condition Then[statements] [Else[elsestatements]] End If Pseudo-code: If Sales>100K Then Bonus=10% Else Bonus=1% End If

15. IF Statement • Syntax (If…Then…ElseIf…Then…Else) If condition Then[statements] [ElseIf condition-n Then[elseifstatements] ... [Else[elsestatements]] End If If Sales>100K Then Bonus=10% ElseIf Sales>50K Then Bonus=5% Else Bonus=1% End If

16. IF Statement Pseudo-Code: If Sales>100K Then If NewClients>5 Then Bonus=15% Else Bonus=10% End If Else Bonus=1% End If • Nested IF If condition Then If condition Then [statements] [Else [elsestatements]] End If [Else [elsestatements]] End If

17. IF Statement • Operators for IF Statement condition • Equal (=) • Not Equal (<>) • Less Than (<) • Less Than Or Equal To (<=) • Greater Than (>) • Greater Than Or Equal To (>=)

18. IF Statement • Logical Operators • And • Or • Not • Xor • Eqv • Imp If Sales>100K And NewClients > 5 Then If Sales>100K Or NewClients > 10 Then If Not(Sales>100K) Then

19. Immediate IF • Immediate If is similar to If statements • Syntax IIf(expr, truepart, falsepart) IIf(Sales>100K, Bonus=10%, 1%) • Can be nested IIf(expr, truepart, IIf(expr, truepart, falsepart)) IIf(Sales>100K, Bonus=10%, IIf(Sales>50K,Bonus=5%, Bonus=1%)) • Type the word ‘IIf’ in the code window and press [F1] for help on the topic

20. Immediate IF • The drawback is that it always evaluates both true and false part • It is slower than IF statements • Can give unexpected results

21. Select Case • Select Case is similar to If…Then…ElseIf statements. • The difference is that it evaluates an expression once and then compares it to different values • Makes it more efficient than multiple ElseIf statements • Type the word ‘Select’ in the code window and press [F1] for help on the topic

22. Select Case • Syntax: Select Case testexpression [Case expressionlist1] [statements] [Case expressionlist2] [statements] [Case Else] [statements] End Select • Pseudo-Code: Select Case Sales Case >100K Bonus=10% Case >50K Bonus=5% Case Else Bonus=1% End Select