Introduction to vba programming
1 / 44

Introduction to VBA Programming - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Introduction to VBA Programming' - kasimir-steele

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Many types of statements
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
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
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


  • 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
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
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
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
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
Object Reference:Set

  • Declare object variales:

    • Dim varName As Database

    • Set db = openCurrentDatabase("c:\salesb.mdb")

  • Dereferencing objects:

    • Set varName = Nothing

Variable scope
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.


  • User-defined constants:

    • Const NationalDay as date = #7/4/2005#

  • Built-In constants:

    • VBA, Access, DAO, ADO

Data conversion
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 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
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))

Arithmetic and string operators
Arithmetic and String Operators

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

  • String Concatenation: &, +

  • No compound operator:

    • K=k+1,

      • not k+=1

If statement
IF Statement

  • IF condition THEN


    [ELSEIF condition-n THEN




    End If

Select case structure
Select Case Structure

  • SELECT CASE testexpression

    [CASE expressionlist-n





Select case example
Select Case Example

  • SELECT CASE temperature

    CASE <40


    CASE < 60


    CASE 60 to 80




    End Select

Introduction to vba programming

  • FOR index – start TO end [STEP step]


    [EXIT FOR]

    NEXT index

    DO [{WHILE| UNTIL} condition]


    [EXIT DO]


Do while do until
Do While/Do Until

Private Sub Command1_Click()

Dim counter As Integer

counter = 0

Do While counter <= 5


counter = counter + 1


Text1.Text = counter

End Sub

Private Sub Command2_Click()

Dim counter As Integer

counter = 0

Do Until counter > 5


counter = counter + 1


Text1.Text = counter

End Sub

With end with
With … 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.

With Text4

.BackColor = vbYellow

.FontSize = 20

.Text = "testtest"

End With


. Sub procedure:

Sub SubName(Arguments)

End Sub

  • To call a sub procedure SUB1

    • CALL SUB1(Argument1, Argument2, …)

    • Or

    • SUB1 Argument1, Argument2, …


  • Private Function tax(salary) As Double

  • tax = salary * 0.1

  • End Function

Call by reference call by value
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 example
ByRef, ByVal example

Private Sub Command2_Click()

Dim myStr As String

myStr = Text0

Call ChangeTextRef(myStr)

Text0 = myStr

End Sub

Private Sub ChangeTextRef(ByRef strInput As String)

strInput = "New Text"

End Sub


  • 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


InputBox(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/1440th 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")


Text1 = cityName

End If

Note: vbNullString is a VB keyword representing null value.

Introduction to vba programming

Vba functions
VBA Functions this form remains active.

Monthly payment form
Monthly Payment Form this form remains active.

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

Conditional required field
Conditional Required Field this form remains active.

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


End If

End If

End Sub

Domain aggregate functions
Domain Aggregate Functions this form remains active.

Aggregate 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 this form remains active.

  • 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 Example this form remains active.

Function NumberOfStudents(FID)

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

End Function

Accessobject object
AccessObject Object this form remains active.

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

  • AllDataAccessPages

  • AllDatabaseDiagrams

  • AllForms

  • AllFunctions

  • AllMacros

  • AllModulesAllQueriesAllReportsAllStoredProceduresAllTablesAllViews

Collection structure
Collection Structure this form remains active.

  • Methods:

    • Count

    • Item(index), 0-based index

    • Add

    • Remove

For each next
For Each … Next this form remains active.

  • 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
AccessObject Properties this form remains active.

  • CurrentView PropertyDateCreated PropertyDateModified PropertyFullName PropertyIsLoaded PropertyName PropertyProperties PropertyType Property

Introduction to vba programming

Dim intView As Integer this form remains active.

If CurrentProject.AllForms("faculty").IsLoaded Then

intView = CurrentProject.AllForms("faculty").CurrentView

If intView = 0 Then

MsgBox ("Design view")

ElseIf intView = 1 Then

MsgBox ("Form view")


MsgBox ("Datasheet view")

End If


MsgBox ("Not open")

End If