Introduction to VBA Programming

Many Types of Statements

• VBA statements

• Access object model’s methods and properties

• Data Access Object’s methods and properties

• ActiveX Data Object

Example: To Open a Database

• DAO command:– Set db = OpenDatabase("c:\salesdb.mdb")

• Access Object Model’s Application Object methods:– CurrentDB method:

• Set db = CurrentDB()

– OpenCurrentDatabase method:• Set db = OpenCurrentDatabase("c:\salesb.mdb")

VB Modules

• Standard modules:– Standard modules are separated database objects

containing one or many procedures. They most often contains utilities functions that are useful in many different circumstances.

– Create a standard module:• In the database window, click Modules and New.

• Form/Report modules: Containing procedures belong to a form/report– Create a form module:

• In the Form Design view, click the Code button

Procedures

• Sub procedures– Private procedures: Can be called only by procedures

in the same module.– Public: Can be called by procedures in any module.– Public is the default declaration

• Functions– returns a value– Used in an expression– Public/Private

To Invoke a Sub Procedure

• Use Call statement:– Arguments must be surrounded by

parentheses.– Call myProcedure(arg1, arg2, …)

• If call is not used, arguments are not surrounded by parentheses.– MyProcedure arg1, arg2, …

Variable Declarations

• Option Explicit• Dim variableName as DataType• Variable naming rules:

– The first character must be a letter.– Use only letters, digits, and underscore.– Cannot contain spaces or periods.– No VB keywords

• Naming conventions:– Descriptive– Consistent lower and upper case characters.

• Ex. Camel casing: lowerUpper, employeeName

VB Data Types

• Boolean (True/False):• Byte: Holds a whole number from 0 to 255.• Date: date and time, 8 bytes.• Double: real, 8 bytes• Single: real, 4 bytes• Integer: 2 bytes• Long: 4 bytes integer• Currency• String• Object: Holds a reference of an object• Variant

Variable Declaration Examples

• Dim empName as String

• Declare multiple variables with one Dim:– Dim empName, dependentName, empSSN as String

• Dim X As Integer, Y As Single

• Initiatialization– Dim interestRate as Double

Object Reference:Set

• Declare object variales:– Dim varName As Database– Set db = openCurrentDatabase("c:\salesb.mdb")

• Dereferencing objects:– Set varName = Nothing

Variable Scope• Procedural-level scope: declared in a

procedure with the Dim statement• Module-level: declared in a module’s

declaration section (outside any procedure) with either Dim or Private keyword.

• Public level scope: a module variable declared with the Public statement.

Constants

• User-defined constants:– Const NationalDay as date = #7/4/2005#

• Built-In constants:– VBA, Access, DAO, ADO

Data Conversion

• Implicit conversion: When you assign a value of one data type to a variable of another data type, VB attempts to convert the value being assigned to the data type of the variable.

• Explicit conversion:– VB.Net Functions: CStr, Ccur, CDbl, Cint,

CLng, CSng, Cdate,Val, etc.

Date Data Type

• Date literals: A date literal may contain the date, the time, or both, and must be enclosed in # symbols:– #1/30/2003#, #1/31/2003 2:10:00 PM#– #6:30 PM#, #18:30:00#

Some Date Functions

• Now: Current date and time• Time• DateDiff• Demo:

– Days to Christmas• Dim myDate1, mydate2 As Date• myDate1 = Now• mydate2 = #12/25/2005#• MsgBox (DateDiff("d", myDate1, mydate2))

Testing VBA Code with Immediate Window

• View/Immediate Window

Arithmetic and String Operators

• +, -, *, /. \, ^

• String Concatenation: &, +

• No compound operator:• K=k+1,

– not k+=1

IF Statement

• IF condition THEN

statements

[ELSEIF condition-n THEN

[elseifstatements]

[ELSE

[elsestatements]]]

End If

Select Case Structure

• SELECT CASE testexpression

[CASE expressionlist-n

[Statements]

[CASE ELSE

[elsestatements]

END SELECT

Select Case Example• SELECT CASE temperature

CASE <40Text1.text=“cold”

CASE < 60Text1.text=“cool”

CASE 60 to 80Text1.text=“warm”

CASE ELSEText1.text=“Hot”

End Select

Loop

• FOR index – start TO end [STEP step]

[statements]

[EXIT FOR]

NEXT index

DO [{WHILE| UNTIL} condition]

[statements]

[EXIT DO]

LOOP

Do While/Do UntilPrivate Sub Command1_Click()Dim counter As Integercounter = 0Do While counter <= 5 Debug.write(counter) counter = counter + 1LoopText1.Text = counterEnd Sub

Private Sub Command2_Click()Dim counter As Integercounter = 0Do Until counter > 5 Debug.write(counter) counter = counter + 1LoopText1.Text = counterEnd Sub

With … End With

With Text4 .BackColor = vbYellow .FontSize = 20 .Text = "testtest"End With

Convenient shorthand to execute a series of statements on a single object. Within the block, the reference to the object is implicit and need not be written.

Procedures

. Sub procedure:

Sub SubName(Arguments)

…

End Sub– To call a sub procedure SUB1

• CALL SUB1(Argument1, Argument2, …)• Or• SUB1 Argument1, Argument2, …

Function

• Private Function tax(salary) As Double• tax = salary * 0.1• End Function

Call by Reference Call by Value

• ByRef– The address of the item is passed. Any

changes made to the passing variable are made to the variable itself.

• ByVal– Default– Only the variable’s value is passed.

ByRef, ByVal examplePrivate Sub Command2_Click()Dim myStr As StringmyStr = Text0Call ChangeTextRef(myStr)Text0 = myStrEnd Sub

Private Sub ChangeTextRef(ByRef strInput As String) strInput = "New Text"End Sub

MsgBox

• MsgBox(prompt, other arguments)• MsgBox can return a value representing the

user’s choice of buttons displayed by the box.

– Use Help to find constants used with the MsgBox

InputBoxInputBox(Prompt [,Title] [, Default] [, Xpos] [, Ypos])

Xpos is the distance from the left edge of the screen, and Ypos is the distance from the top of the screen. Both are measured in twips (1/1440 th of an inch).

Note: The arguments are positional and optional. Enter a comma to skip an argument.

cityName = InputBox("Please enter city name:“, , “SF”)

If cityName = vbNullString Then

MsgBox.Show ("customer click cancel")

Else

Text1 = cityName

End If

Note: vbNullString is a VB keyword representing null value.

• Modeless form: Other forms can receive input focus while this form remains active.

• Modal form: No other form can receive focus while this form remains active.– DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

• Note: Macro/OpenForm/Window Mode

VBA Functions

Monthly Payment Form

Text6 = -Pmt(Text2 / 12, Text4 * 12, Text0)

Conditional Required Field

Private Sub Form_BeforeUpdate(Cancel As Integer)If Year(Now) - Year(Birthdate) < 18 Then If IsNull(Text14) Then MsgBox ("You must enter guardian name! ") Cancel = True Text14.SetFocus End IfEnd IfEnd Sub

Domain Aggregate FunctionsAggregate functions provide statistical information about sets of records (a domain). For example, you can use an aggregate function to count the number of records in a particular set of records or to determine the average of values in a particular field.The two types of aggregate functions, domain aggregate functions and SQL aggregate functions, provide similar functionality but are used in different situations. The SQL aggregate functions can be included in the syntax of an SQL statement but can't be called directly from Visual Basic. Conversely, the domain aggregate functions can be called directly from Visual Basic code. They can also be included in an SQL statement, but an SQL aggregate function is generally more efficient.

Examples

• From Student form, lookup Fname:– =DLookUp("[fname]","faculty","fid='" & [Forms]![student]![fid] & "'")

• From Faculty form, count number of students advised by the faculty:– =DCount("[FID]","Student","FID='" & [Forms]![Faculty]![Fid] & "'")

Function Example

Function NumberOfStudents(FID) NumberOfStudents = DCount("sid", "student", "fid='" & Forms!faculty!FID & "'")End Function

AccessObject Object

• An AccessObject object refers to a particular Microsoft Access object within the following collections.

• AllDataAccessPages • AllDatabaseDiagrams • AllForms• AllFunctions • AllMacros • AllModules AllQueries AllReports

AllStoredProcedures AllTables AllViews

Collection Structure

• Methods:– Count– Item(index), 0-based index– Add– Remove

For Each … Next

• Dim formName As String• Dim obj As AccessObject• For Each obj In Application.CurrentProject.AllForms

• formName = formName + obj.Name + vbCrLf• Next• MsgBox (formName)• MsgBox ("Number of forms: " + CStr(Application.CurrentProject.AllForms.Count))

AccessObject Properties

• CurrentView Property DateCreated Property DateModified Property FullName Property IsLoaded Property Name Property Properties Property Type Property

Is the Faculty form open? If so, in which view?

Dim intView As IntegerIf CurrentProject.AllForms("faculty").IsLoaded Then intView = CurrentProject.AllForms("faculty").CurrentView If intView = 0 Then MsgBox ("Design view") ElseIf intView = 1 Then MsgBox ("Form view") Else MsgBox ("Datasheet view") End IfElse MsgBox ("Not open")End If