Introduction to VBA Programming Many Types of Statements

Introduction to VBA Programming

Many Types of Statements • VBA statements • Access object model’s methods and properties • Data Access Object’s methods and properties • Active. X Data Object

Example: To Open a Database • DAO command: – Set db = Open. Database("c: salesdb. mdb") • Access Object Model’s Application Object methods: – Current. DB method: • Set db = Current. DB() – Open. Current. Database method: • Set db = Open. Current. Database("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 my. Procedure(arg 1, arg 2, …) • If call is not used, arguments are not surrounded by parentheses. – My. Procedure arg 1, arg 2, …

Variable Declarations • Option Explicit • Dim variable. Name as Data. Type • 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: lower. Upper, employee. Name

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 emp. Name as String • Declare multiple variables with one Dim: – Dim emp. Name, dependent. Name, emp. SSN as String • Dim X As Integer, Y As Single • Initiatialization – Dim interest. Rate as Double

Object Reference: Set • Declare object variales: – Dim var. Name As Database – Set db = open. Current. Database("c: salesb. mdb") • Dereferencing objects: – Set var. Name = 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 National. Day 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 Date. Diff Demo: – Days to Christmas • • Dim my. Date 1, mydate 2 As Date my. Date 1 = Now mydate 2 = #12/25/2005# Msg. Box (Date. Diff("d", my. Date 1, mydate 2))

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 <40 Text 1. text=“cold” CASE < 60 Text 1. text=“cool” CASE 60 to 80 Text 1. text=“warm” CASE ELSE Text 1. 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 Until Private Sub Command 1_Click() Dim counter As Integer counter = 0 Do While counter <= 5 Debug. write(counter) counter = counter + 1 Loop Text 1. Text = counter End Sub Private Sub Command 2_Click() Dim counter As Integer counter = 0 Do Until counter > 5 Debug. write(counter) counter = counter + 1 Loop Text 1. Text = counter End Sub

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 Text 4 . Back. Color = vb. Yellow . Font. Size = 20 . Text = "test" End With

Procedures . Sub procedure: Sub. Name(Arguments) … End Sub – To call a sub procedure SUB 1 • CALL SUB 1(Argument 1, Argument 2, …) • Or • SUB 1 Argument 1, Argument 2, …

Function • Private Function tax(salary) As Double • tax = salary * 0. 1 • End Function

Call by Reference Call by Value • By. Ref – The address of the item is passed. Any changes made to the passing variable are made to the variable itself. • By. Val – Default – Only the variable’s value is passed.

By. Ref, By. Val example Private Sub Command 2_Click() Dim my. Str As String my. Str = Text 0 Call Change. Text. Ref(my. Str) Text 0 = my. Str End Sub Private Sub Change. Text. Ref(By. Ref str. Input As String) str. Input = "New Text" End Sub

Msg. Box • Msg. Box(prompt, other arguments) • Msg. Box can return a value representing the user’s choice of buttons displayed by the box. – Use Help to find constants used with the Msg. Box

Input. Box(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. city. Name = Input. Box("Please enter city name: “, , “SF”) If city. Name = vb. Null. String Then Msg. Box. Show ("customer click cancel") Else Text 1 = city. Name End If Note: vb. Null. String 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. – Do. Cmd. Open. Form st. Doc. Name, , , st. Link. Criteria, , ac. Dialog • Note: Macro/Open. Form/Window Mode

VBA Functions

Monthly Payment Form Text 6 = -Pmt(Text 2 / 12, Text 4 * 12, Text 0)

Conditional Required Field Private Sub Form_Before. Update(Cancel As Integer) If Year(Now) - Year(Birthdate) < 18 Then If Is. Null(Text 14) Then Msg. Box ("You must enter guardian name! ") Cancel = True Text 14. Set. Focus End If End Sub

Domain Aggregate Functions 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 • From Student form, lookup Fname: – =DLook. Up("[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 Number. Of. Students(FID) Number. Of. Students = DCount("sid", "student", "fid='" & Forms!faculty!FID & "'") End Function

Access. Object • An Access. Object object refers to a particular Microsoft Access object within the following collections. • All. Data. Access. Pages • All. Database. Diagrams • All. Forms • All. Functions • All. Macros • All. Modules All. Queries All. Reports All. Stored. Procedures All. Tables All. Views

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

For Each … Next • • • Dim form. Name As String Dim obj As Access. Object For Each obj In Application. Current. Project. All. Forms form. Name = form. Name + obj. Name + vb. Cr. Lf Next Msg. Box (form. Name) • Msg. Box ("Number of forms: " + CStr(Application. Current. Project. All. Forms. Count))

Access. Object Properties • Current. View Property Date. Created Property Date. Modified Property Full. Name Property Is. Loaded Property Name Property Properties Property Type Property

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

Dim int. View As Integer If Current. Project. All. Forms("faculty"). Is. Loaded Then int. View = Current. Project. All. Forms("faculty"). Current. View If int. View = 0 Then Msg. Box ("Design view") Else. If int. View = 1 Then Msg. Box ("Form view") Else Msg. Box ("Datasheet view") End If Else Msg. Box ("Not open") End If

Error Handling

Overview • Probably no matter how careful and meticulous you are, some time to time, there will be problems with your code or your application. Some problems will come from you. Some problems will be caused by users. And some problems will be caused by neither you nor your users. This means that there are things you can fix. Those you can avoid as much as possible. And there are situations beyond your control. Still, as much as you can, try anticipating any type of problem you imagine may occur when a user is using your application, and take action as much as possible to avoid bad situations.

Error Categories • Syntax: A syntax error comes from your mistyping a word or forming a bad expression in your code. • Run-Time: After all syntax errors have been fixed, the program may be ready for the user. Imagine that, in your code, you indicate that a picture would be loaded and displayed to the user but you forget to ship the picture or the directory of the picture indicated in your code becomes different when a user opens your application.

Error Categories • Logic: These are errors that don't fit in any of the above categories. They could be caused by the user misusing your application, a problem with the computer on which the application is running while the same application is working fine in another computer. Because logic errors can be vague, they can also be difficult to fix.

Handling Errors • type On Error Go. To followed by the name of the label that would deal with the error. Ex: Private Sub cmd. Calculate_Click() On Error Go. To cmd. Calculate_Click_Error . . . cmd. Calculate_Click_Error: Msg. Box "There was a problem when executing your instructions" End Sub

Handling Errors Ex. cmd. Calculate_Click_Exit: Exit Sub cmd. Calculate_Click. Error: Msg. Box "There was a problem when trying to perform the calculation. ", _ vb. OKCancel Or vb. Information, _ "Compound Interest" Resume cmd. Calculate_Click_Exit End Sub

Array

Array • You can declare an array to work with a set of values of the same data type. An array is a single variable with many compartments to store values, while a typical variable has only one storage compartment in which it can store only one value. Refer to the array as a whole when you want to refer to all the values it holds, or you can refer to its individual elements.

Declare Array • For example, to store daily expenses for each day of the year, you can declare one array variable with 365 elements, rather than declaring 365 variables. Each element in an array contains one value. The following statement declares the array variable with 365 elements. By default, an array is indexed beginning with zero, so the upper bound of the array is 364 rather than 365.

Declare Array • Re. Dim statement is used to change the number of dimensions, to define the number of elements, and to define the upper and lower bounds for each dimension Re. Dim Preserve var. Array(10)

Set value to element of array • To set the value of an individual element, you specify the element's index. The following example assigns an initial value of 20 to each element in the array. Sub Fill. Array() Dim cur. Expense(364) As Currency Dim int. I As Integer For int. I = 0 to 364 cur. Expense(int. I) = 20 Next

Changing the Lower Bound • You can use the Option Base statement at the top of a module to change the default index of the first element from 0 to 1. In the following example, the Option Base statement changes the index for the first element, and the Dim statement declares the array variable with 365 elements Option Base 1 Dim cur. Expense(365) As Currency

Storing Variant Values in Arrays • One way is to declare an array of Variant data type Dim var. Data(3) As Variant var. Data(0) = "Claudia Bendel" var. Data(1) = "4242 Maple Blvd" var. Data(2) = 38 var. Data(3) = Format("06 -09 -1952", "General Date") • The other way is to assign the array returned by the Array function to a Variant variable Dim var. Data As Variant var. Data = Array("Ron Bendel", "4242 Maple Blvd", 38, _ Format("06 -09 -1952", "General Date"))

Multidimensional Arrays • you can declare arrays with up to 60 dimensions Dim sng. Multi(1 To 5, 1 To 10) As Single • Use nested For. . . Next statements to process multidimensional arrays Sub Fill. Array. Multi() Dim int. I As Integer, int. J As Integer Dim sng. Multi(1 To 5, 1 To 10) As Single For int. I = 1 To 5 For int. J = 1 To 10 sng. Multi(int. I, int. J) = int. I * int. J Debug. Print sng. Multi(int. I, int. J) Next int. J Next int. I End Sub