AccessVBA Programming for Beginners Class 2

1
Access VBA Programmingfor Beginners - Class 2 -

• by
• Patrick Lasu
• p_lasu_at_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 CTRLS
• 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
  CurrencycurBonus 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 buttons 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
Lifetime/Visibility of Variables
Module1 Public strName as String

• Form1
• Private Sub MyButton1_Click()
• strName John MsgBox strName
• End Sub

• Form2
• Private Sub MyButton2_Click()
• MsgBox strName
• End Sub

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