Access VBA Programming for Beginners - Class 2 -

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

2. Class 2 - Overview • Quick Review of Class 1 • Constants • Why not using Variants all the time • Option Explicit • Scope/Visibility and Lifetime of Variables

3. Quick Review of Class 1 • VBA • Visual Basic for Applications • Application = Access, Word, Excel, etc. • Event Driven • User Event: Example - Clicking a Button • Programming Event: Example - Timer • At least 3 different ways to code the same task

4. Quick Review of Class 1 • Good Code – It works • Bad Code – It does not work • Better Code – Make it “Short and Sweet” • Save often, use [CTRL]+[S] • Use the Help Files [F1]

5. Quick Review of Class 1 • A variable is a storage for a value that can change during code execution • Answering Yes or No

6. Quick Review of Class 1 • There are several Data Types for Variables and Constants for efficiency • String = Stores Text – “Patrick”, “123 Main St” • Number = Stores Numbers - “1, 2, 3,...”, “3.14” • Boolean = Stores True/False • Date = Stores Date • Currency = Currency format – “Dollar, Yen” • Variant = Stores Anything • And there are many more!!!!

7. Quick Review of Class 1 • Naming convention for Variables: • Strings – Starts with “str” • strFirstName • Integers – Starts with “int” • intCount • Boolean – Starts with “bln”, “bol”, “bool” • boolExit • Variant – Starts with “var” • varAnyValue

8. Quick Review of Class 1 • A variable needs to be declared Syntax: Dim variablename [As type] • Dim = Dimension (make space for it) • variablename = Ex: strFirstName • [As type] = Optional, Ex: As String Dim strFirstName As String

9. Constants • A constant is a storage for a value that does not change during code execution • 3.1415, vbYes, vbRed • Can be changed manually • Going from 365 days to 360 days when calculating interest

10. Constants • Naming Conventions for Constants • Use UPPERCASE • PI • Start with “con” • conInterest

11. Constants • Constants must to be declared • Syntax: Const constname [As type] = Expression • Const = Declares it as a Constant • constname = Ex: PI • [As type] = Optional, Ex: As Double • Expression = Value assigned to it, Ex: 3.1415 Const PI As Double = 3.1415

12. Changing Constants • A Constant Value cannot Change • You can change Constants manually by declaring several Constants • Const LONGYEAR as Integer = 365 • Const SHORTYEAR as Integer = 360 • Let the user make the choice on a form which Constant to use • Example: Option buttons

13. Constants • Constant values are hard-coded • Need to change it directly in the code

14. Variants • Why not use Variants for all Variables and Constants? • It is slower • Uses up more memory • Has to be converted • Sacrifices readability of code (Programmer) • varCount – Does it count by whole numbers, or by fractional numbers?

15. Option Explicit • Using “Option Explicit” forces all Variables to be declared before the code can run • Good for catching errorsOption ExplicitDim curBonus as CurrencyDim curSalary as Currency…curBonus = curSalry * 0.1… • Error Message (“Variable not defined”) curSalry

16. Option Explicit • There is no set rule that says all Variables need to be declared – it is just good practice • With Option Explicit you are forced to declare • With Option Explicit, you cannot use “temporary” variables without declaring them

17. Lifetime/Visibility of Variables Visible to all procedures within the project (database) Public Form Visible to all procedures within the form Local Visible to the procedure where it is declared

18. Lifetime/Visibility of Variables • Local Level • Visibility and Lifetime of variables are within the procedure that called itExample: Declaring the variable within a button’s On Click Event.The variable is destroyed after the code ends (except Static variable – Next class!).No other procedure (button) can access the variable.

19. Lifetime/Visibility of Variables Form1 Private Sub MyButton_Click() Dim strName As StringstrName = “John” MsgBox strName End Sub MyButton1 MyButton2 ?????? Cannot see strName

20. Lifetime/Visibility of Variables • Form Level • Visibility and Lifetime of variables are within the form for all procedures.Declaring the variable in General Declarations section (Top) of the form.The variable is destroyed after the form is closed.

21. Lifetime/Visibility of Variables Form1 Dim strName As String Private Sub MyButton1_Click() strName = “John” MsgBox strName End Sub Private Sub MyButton2_Click() MsgBox strName End Sub MyButton1 MyButton2

22. Lifetime/Visibility of Variables • Public • Visibility and Lifetime of variables are within the project (database).Declaring the variable as Public in General Declarations section in a standard module. The variable is destroyed after you close the project. All procedures (buttons, etc.) have access to the variable as long as the project is running

23. Form1 Private Sub MyButton1_Click() strName = “John” MsgBox strName End Sub Form2 Private Sub MyButton2_Click() MsgBox strName End Sub Lifetime/Visibility of Variables Module1 Public strName as String MyButton1 MyButton2

24. Review – Class 2 • Constants can change, but not its value • Avoid declaring all variables as Variants • Option Explicit is good for catching errors • Lifetime and Visibility of variables are (simplified): • Local: Within the procedure • Form : All procedures within the form • Public: All procedures within the project

25. Next Class… • Static Variables • MsgBox and InputBox