Vba class 4

Vba class 4

• 1.
  Access VBA Programming for 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.
  Coding Concepts Reading Code– Example 1 VBA Code Behind the scenes intValue = 5 + 2 0=5+2 Next Line of Code Next Line of Code
• 5.
  Coding Concepts Reading Code– Example 1 VBA Code Behind the scenes intValue = 5 + 2 7=5+2 Next Line of Code Next Line of Code
• 6.
  Coding Concepts Reading Code– Example 2 VBA Code Behind the scenes intValue = 5 0=5 intValue = intValue + 2 0=0+2 Next Line of Code Next Line of Code
• 7.
  Coding Concepts Reading Code– Example 2 VBA Code Behind the scenes intValue = 5 5=5 intValue = intValue + 2 5=5+2 Next Line of Code Next Line of Code
• 8.
  Coding Concepts Reading Code– Example 2 VBA Code Behind the scenes intValue = 5 7=5 intValue = intValue + 2 7=7+2 Next Line of Code Next Line of Code
• 9.
  Coding Concepts  Concatenationcharacter: & (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  AnIF 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) Pseudo-code: If condition Then If Sales>100K Then [statements] Bonus=10% [Else Else [elsestatements]] Bonus=1% End If End If
• 15.
  IF Statement  Syntax (If…Then…ElseIf…Then…Else) If condition Then If Sales>100K Then [statements] Bonus=10% [ElseIf condition-n Then ElseIf Sales>50K Then [elseifstatements] ... Bonus=5% [Else Else [elsestatements]] Bonus=1% End If End If
• 16.
  IF Statement  Nested IF Pseudo-Code: If condition Then If Sales>100K Then If condition Then If NewClients>5 Then [statements] Bonus=15% [Else Else [elsestatements]] Bonus=10% End If End If [Else Else [elsestatements]] Bonus=1% End If 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 If Sales>100K And NewClients > 5 Then – Or If Sales>100K Or NewClients > 10 Then – Not If Not(Sales>100K) Then – Xor – Eqv – Imp
• 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  SelectCase 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: Pseudo-Code: Select Case testexpression Select Case Sales [Case expressionlist1] Case >100K [statements] Bonus=10% [Case expressionlist2] Case >50K [statements] Bonus=5% [Case Else] Case Else [statements] Bonus=1% End Select End Select