1 / 22

Access VBA Programming for Beginners - Class 4 -

Access VBA Programming for Beginners - Class 4 -. by Patrick Lasu p_lasu@lycos.com. Class 4 - Overview. Coding Concepts Reading Code VB Operators/Characters: Concatenation, Line Continuation, Comments Stepping through Code IF Statements IIF Statements Select Case Statements.

hannibal
Download Presentation

Access VBA Programming for Beginners - Class 4 -

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

More Related