1 / 25

Access VBA Programming for Beginners - Class 2 -

Access VBA Programming for Beginners - Class 2 -. by Patrick Lasu p_lasu@lycos.com. Class 2 - Overview. Quick Review of Class 1 Constants Why not using Variants all the time Option Explicit Scope/Visibility and Lifetime of Variables. Quick Review of Class 1. VBA

loyal
Download Presentation

Access VBA Programming for Beginners - Class 2 -

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

More Related