際際滷

際際滷Share a Scribd company logo

Vba class 4

Download as PPT, PDF
M
Mangesh Gadre

This document provides an overview of coding concepts, IF statements, IIF statements, and Select Case statements in VBA for beginners. It explains how to read code, use operators and characters like concatenation and line continuation, and step through code. It provides examples and syntax for IF statements, IIF statements, and Select Case statements.

1 of 22
Downloaded 21 times
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
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
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
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
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
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
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
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
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
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
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.
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
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
IF Statement Syntax (IfThenElseIfThenElse) 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
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
IF Statement Operators for IF Statement condition Equal (=) Not Equal (<>) Less Than (<) Less Than Or Equal To (<=) Greater Than (>) Greater Than Or Equal To (>=)
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
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
Immediate IF The drawback is that it always evaluates both true and false part It is slower than IF statements Can give unexpected results
Select Case Select Case is similar to IfThenElseIf 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
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

More Related Content

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 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) 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 (IfThenElseIfThenElse) 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 Select Case is similar to IfThenElseIf 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