access vba programming for beginners - class 2 - by patrick lasu [email protected]
TRANSCRIPT
Access VBA ProgrammingAccess VBA Programmingfor Beginners for Beginners
- Class 2 -- Class 2 -byby
Patrick LasuPatrick Lasu
Class 2 - OverviewClass 2 - Overview
Quick Review of Class 1Quick Review of Class 1 ConstantsConstants Why not using Variants all the timeWhy not using Variants all the time Option ExplicitOption Explicit Scope/Visibility and Lifetime of VariablesScope/Visibility and Lifetime of Variables
Quick Review of Class 1Quick Review of Class 1
VBAVBA– Visual Basic for ApplicationsVisual Basic for Applications
» Application = Access, Word, Excel, etc.Application = Access, Word, Excel, etc.
Event DrivenEvent Driven– User Event: Example - Clicking a ButtonUser Event: Example - Clicking a Button– Programming Event: Example - TimerProgramming Event: Example - Timer
At least 3 different ways to code the same At least 3 different ways to code the same tasktask
Quick Review of Class 1Quick Review of Class 1
Good Code – It worksGood Code – It works Bad Code – It does not workBad Code – It does not work Better Code – Make it “Short and Sweet”Better Code – Make it “Short and Sweet” Save often, use [CTRL]+[S]Save often, use [CTRL]+[S] Use the Help Files [F1]Use the Help Files [F1]
Quick Review of Class 1Quick Review of Class 1
A variable is a storage for a value that can A variable is a storage for a value that can change during code executionchange during code execution– Answering Yes or NoAnswering Yes or No
Quick Review of Class 1Quick Review of Class 1
There are several Data Types for Variables There are several Data Types for Variables and Constants for efficiencyand Constants for efficiency– String = Stores Text – “Patrick”, “123 Main St”String = Stores Text – “Patrick”, “123 Main St”– Number = Stores Numbers - “1, 2, 3,...”, “3.14”Number = Stores Numbers - “1, 2, 3,...”, “3.14”– Boolean = Stores True/FalseBoolean = Stores True/False– Date = Stores DateDate = Stores Date– Currency = Currency format – “Dollar, Yen”Currency = Currency format – “Dollar, Yen”– Variant = Stores AnythingVariant = Stores Anything
And there are many more!!!!And there are many more!!!!
Quick Review of Class 1Quick Review of Class 1
Naming convention for Variables:Naming convention for Variables:– Strings – Starts with “str”Strings – Starts with “str”
» strFirstNamestrFirstName
– Integers – Starts with “int”Integers – Starts with “int”» intCountintCount
– Boolean – Starts with “bln”, “bol”, “bool”Boolean – Starts with “bln”, “bol”, “bool”» boolExitboolExit
– Variant – Starts with “var”Variant – Starts with “var”» varAnyValuevarAnyValue
Quick Review of Class 1Quick Review of Class 1
A variable needs to be declaredA variable needs to be declared
Syntax: Dim Syntax: Dim variablenamevariablename [As type] [As type]
– Dim = Dimension (make space for it)Dim = Dimension (make space for it)– variablename = Ex: strFirstNamevariablename = Ex: strFirstName– [As type] = Optional, Ex: As String[As type] = Optional, Ex: As String
Dim strFirstName As StringDim strFirstName As String
ConstantsConstants
A constant is a storage for a value that does A constant is a storage for a value that does not change during code executionnot change during code execution– 3.1415, vbYes, vbRed3.1415, vbYes, vbRed– Can be changed manually Can be changed manually
» Going from 365 days to 360 days when calculating Going from 365 days to 360 days when calculating interestinterest
ConstantsConstants
Naming Conventions for ConstantsNaming Conventions for Constants– Use UPPERCASEUse UPPERCASE
» PIPI
– Start with “con” Start with “con” » conInterestconInterest
ConstantsConstants
Constants must to be declaredConstants must to be declared– Syntax: Syntax:
Const Const constnameconstname [As type] = Expression [As type] = Expression
– Const = Declares it as a ConstantConst = Declares it as a Constant
– constnameconstname = Ex: PI = Ex: PI
– [As type] = Optional, Ex: As Double[As type] = Optional, Ex: As Double
– Expression = Value assigned to it, Ex: 3.1415Expression = Value assigned to it, Ex: 3.1415
Const PI As Double = 3.1415Const PI As Double = 3.1415
Changing ConstantsChanging Constants
A Constant Value cannot ChangeA Constant Value cannot Change– You can change Constants manually by You can change Constants manually by
declaring several Constantsdeclaring several Constants» Const LONGYEAR as Integer = 365Const LONGYEAR as Integer = 365
» Const SHORTYEAR as Integer = 360Const SHORTYEAR as Integer = 360
– Let the user make the choice on a form which Let the user make the choice on a form which Constant to useConstant to use
» Example: Option buttonsExample: Option buttons
ConstantsConstants
Constant values are hard-codedConstant values are hard-coded– Need to change it directly in the codeNeed to change it directly in the code
VariantsVariants
Why not use Variants for all Variables and Why not use Variants for all Variables and Constants?Constants?– It is slowerIt is slower
» Uses up more memoryUses up more memory
» Has to be convertedHas to be converted
– Sacrifices readability of code (Programmer)Sacrifices readability of code (Programmer)» varCount – Does it count by whole numbers, or by varCount – Does it count by whole numbers, or by
fractional numbers?fractional numbers?
Option ExplicitOption Explicit
Using “Option Explicit” forces all Variables to be Using “Option Explicit” forces all Variables to be declared before the code can rundeclared before the code can run
Good for catching errorsGood for catching errors
Option ExplicitOption ExplicitDim curBonus as CurrencyDim curBonus as CurrencyDim curSalary as CurrencyDim curSalary as Currency……curBonus = curSalry * 0.1curBonus = curSalry * 0.1……
Error Message (“Variable not defined”)Error Message (“Variable not defined”)
curSalry
Option ExplicitOption Explicit
There is no set rule that says all Variables There is no set rule that says all Variables need to be declared – it is just good practiceneed to be declared – it is just good practice– With Option Explicit you are forced to declareWith Option Explicit you are forced to declare
With Option Explicit, you cannot use With Option Explicit, you cannot use “temporary” variables without declaring “temporary” variables without declaring themthem
Lifetime/Visibility of VariablesLifetime/Visibility of Variables
Public
Form
Local
Visible to the procedure where it is declared
Visible to all procedures within the form
Visible to all procedures within the project (database)
Lifetime/Visibility of VariablesLifetime/Visibility of Variables
Local LevelLocal Level– Visibility and Lifetime of variables are within Visibility and Lifetime of variables are within
the procedure that called itthe procedure that called itExample: Declaring the variable within a Example: Declaring the variable within a button’s On Click Event.button’s On Click Event.
The variable is destroyed after the code ends The variable is destroyed after the code ends (except Static variable – Next class!).(except Static variable – Next class!).No other procedure (button) can access the No other procedure (button) can access the variable.variable.
Lifetime/Visibility of VariablesLifetime/Visibility of Variables
Form1Form1
Private Sub MyButton_Click()Private Sub MyButton_Click()Dim strName As StringDim strName As StringstrName = “John”strName = “John”MsgBox strNameMsgBox strName
End SubEnd Sub
MyButton1 MyButton2
??????
Cannot see strName
Lifetime/Visibility of VariablesLifetime/Visibility of Variables
Form LevelForm Level– Visibility and Lifetime of variables are within Visibility and Lifetime of variables are within
the form for all procedures.the form for all procedures.Declaring the variable in General Declarations Declaring the variable in General Declarations section (Top) of the form.section (Top) of the form.
The variable is destroyed after the form is The variable is destroyed after the form is closed.closed.
Lifetime/Visibility of VariablesLifetime/Visibility of VariablesForm1Form1
Dim strName As StringDim strName As String
Private Sub MyButton1_Click()Private Sub MyButton1_Click()strName = “John” strName = “John” MsgBox strNameMsgBox strName
End SubEnd SubPrivate Sub MyButton2_Click()Private Sub MyButton2_Click()
MsgBox strNameMsgBox strNameEnd SubEnd Sub
MyButton1
MyButton2
Lifetime/Visibility of VariablesLifetime/Visibility of Variables
PublicPublic– Visibility and Lifetime of variables are within Visibility and Lifetime of variables are within
the project (database).the project (database).Declaring the variable as Public in General Declaring the variable as Public in General Declarations section in a standard module. Declarations section in a standard module.
The variable is destroyed after you close the The variable is destroyed after you close the project. project. All procedures (buttons, etc.) have access to the All procedures (buttons, etc.) have access to the variable as long as the project is runningvariable as long as the project is running
Lifetime/Visibility of VariablesLifetime/Visibility of Variables
Form1Form1
Private Sub MyButton1_Click()Private Sub MyButton1_Click()
strName = “John” strName = “John” MsgBox strNameMsgBox strName
End SubEnd Sub
Form2Form2
Private Sub MyButton2_Click()Private Sub MyButton2_Click()
MsgBox strNameMsgBox strName
End SubEnd Sub
MyButton1 MyButton2
Module1
Public strName as String
Review – Class 2Review – Class 2
Constants can change, but not its valueConstants can change, but not its value Avoid declaring all variables as VariantsAvoid declaring all variables as Variants Option Explicit is good for catching errorsOption Explicit is good for catching errors Lifetime and Visibility of variables are Lifetime and Visibility of variables are
(simplified):(simplified):– Local: Within the procedureLocal: Within the procedure– Form : All procedures within the formForm : All procedures within the form– Public: All procedures within the projectPublic: All procedures within the project
Next Class…Next Class…
Static VariablesStatic Variables MsgBox and InputBoxMsgBox and InputBox