1 / 44

Introduction to VBA Programming

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

lam
Download Presentation

Introduction to VBA Programming

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction to VBA Programming

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

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

  4. 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

  5. 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

  6. 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, …

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

  8. 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

  9. 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

  10. Object Reference:Set • Declare object variales: • Dim varName As Database • Set db = openCurrentDatabase("c:\salesb.mdb") • Dereferencing objects: • Set varName = Nothing

  11. 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.

  12. Constants • User-defined constants: • Const NationalDay as date = #7/4/2005# • Built-In constants: • VBA, Access, DAO, ADO

  13. 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.

  14. 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#

  15. 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))

  16. Testing VBA Code with Immediate Window • View/Immediate Window

  17. Arithmetic and String Operators • +, -, *, /. \, ^ • String Concatenation: &, + • No compound operator: • K=k+1, • not k+=1

  18. IF Statement • IF condition THEN statements [ELSEIF condition-n THEN [elseifstatements] [ELSE [elsestatements]]] End If

  19. Select Case Structure • SELECT CASE testexpression [CASE expressionlist-n [Statements] [CASE ELSE [elsestatements] END SELECT

  20. Select Case Example • SELECT CASE temperature CASE <40 Text1.text=“cold” CASE < 60 Text1.text=“cool” CASE 60 to 80 Text1.text=“warm” CASE ELSE Text1.text=“Hot” End Select

  21. Loop • FOR index – start TO end [STEP step] [statements] [EXIT FOR] NEXT index DO [{WHILE| UNTIL} condition] [statements] [EXIT DO] LOOP

  22. Do While/Do Until Private Sub Command1_Click() Dim counter As Integer counter = 0 Do While counter <= 5 Debug.write(counter) counter = counter + 1 Loop Text1.Text = counter End Sub Private Sub Command2_Click() Dim counter As Integer counter = 0 Do Until counter > 5 Debug.write(counter) counter = counter + 1 Loop Text1.Text = counter End Sub

  23. 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

  24. Procedures . Sub procedure: Sub SubName(Arguments) … End Sub • To call a sub procedure SUB1 • CALL SUB1(Argument1, Argument2, …) • Or • SUB1 Argument1, Argument2, …

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

  26. 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.

  27. 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

  28. 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

  29. InputBox 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") Else Text1 = cityName End If Note: vbNullString is a VB keyword representing null value.

  30. 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

  31. VBA Functions

  32. Monthly Payment Form Text6 = -Pmt(Text2 / 12, Text4 * 12, Text0)

  33. 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 If End If End Sub

  34. 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.

  35. 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] & "'")

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

  37. AccessObject Object • An AccessObject object refers to a particular Microsoft Access object within the following collections. • AllDataAccessPages • AllDatabaseDiagrams • AllForms • AllFunctions • AllMacros • AllModulesAllQueriesAllReportsAllStoredProceduresAllTablesAllViews

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

  39. 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))

  40. AccessObject Properties • CurrentView PropertyDateCreated PropertyDateModified PropertyFullName PropertyIsLoaded PropertyName PropertyProperties PropertyType Property

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

  42. Dim intView As Integer 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") Else MsgBox ("Datasheet view") End If Else MsgBox ("Not open") End If

More Related