VBA Visual Basic for Applications

1
VBA Visual Basic for Applications

• Week 20 - Tutorial

2
Objectives

• At the close of this tutorial you should
  understand
• Objects, Properties, Methods and Events
• Event driven programming
• Basic VBA programming syntax
• Functions and Procedures

3
What is VBA

• VBA is a programming language
• It can be used in all major Microsoft Office
  Applications
• VBA allows developers to create their own
  functions and procedures to enhance bespoke
  databases or spreadsheets
• It is more powerful and flexible than Access
  macros

4
Objects Properties Methods and Events

• Before we can create any VB code we must
  understand Objects Properties, Methods and Events
• Objects can be anything in your database such
  as a form, textbox, combo box the database is
  also an object. Other applications such as Excel
  are also objects

5
Properties

• Objects can have Properties that describe their
  characteristics
• A Form has a Caption property, a textbox has a
  Font property
• We can set the fonts attributes by setting some
  of the textboxes properties such as Bold or
  Italic

6
Methods

• Object also have Methods, which allow actions to
  be done to the objects
• A Recordset object has an AddNew method that
  allows the addition of a new record it also has
  MoveFirst, Delete, Insert etc.

7
Events

• Closely related to methods are Events
• VBA is an Event driven programming language
• A Event is a specific action that occurs on or
  with an Object
• VBA is totally dependent on events being
  initiated or occurring

8
Events cont.

• The Click event of a button is probably the most
  commonly initiated event
• When you click a command button on a form, the
  code within the click event is executed
• All controls that are placed on a form have
  events the form itself has its own events

9
Modules

• All the code for a VBA program is contained in
  Modules
• VBA has Class and Standard Modules
• Each Form and Report has its own Class module
  that contains code that is specific to that form
  or report
• A standard module contains generic code that is
  not specific to any particular object and can be
  deployed anywhere within a program

10
Viewing a Form Module

• To view a Form module open the form in Design
  View and click on the code icon on the toolbar

11
The Form Class Module Code Window

• The code module is displayed

12
Form Class Module

• If we click on the General dropdown list we can
  see a list of Objects (controls) that have been
  placed on the form. It also lists the Form
  itself

13
Form Class Module

• If we select Form from the list, the
  Declarations dropdown list displays Load
  this refers to the Form Load Event

14
Form Class Module

• If we click on the declaration drop down list we
  can view all the other Events available to the
  form
• When you select an Event, a code template is
  created in the code window this is where we add
  the code for that Event

Code goes in here
15
Coding an Event

• To access both the properties and the controls on
  the form we use the reserved word Me
• This refers to the form itself
• To access the properties and controls we use the
  dot (.) notation
• For example, to access the Forms Caption
  property we would use
• Me.Caption

16
Coding an Event

• To set the Caption property we can enter a name
  in speech marks and click save, and no matter
  what name exists in the form name property, the
  Load Event code will over-ride it to display the
  name defined in the procedure

17
Procedures and Functions

• Apart from Event procedures developers can write
  their own procedures and functions or more
  correctly Sub Procedures and Function Procedures
• Sub Procedures perform actions and can be run
  directly or in response to an event
• Function Procedures return a single value of any
  data type i.e. String, Integer, Date

18
Modifying Existing Event Procedures

• Access can satisfy most user requirements,
  however, by providing your own VBA code you can
  create individual bespoke Access database
  applications
• The code that Access provides for a controls
  event procedures can be rewritten to suit your
  own application and confirmation and error
  messages can be overridden and replaced by more
  suitable messages

19
Modifying Existing Event Procedures

• When you create an input form using the wizard,
  Access does not provide a Delete option, only
  record navigation and add new record options

Navigation buttons
Add New button
20
Adding Delete Button

• To add a delete option place a command button on
  the form and using the wizard set the button to
  Delete Record
• When the button is clicked an internally
  generated message is displayed if the user
  clicks No then another message is displayed
• Each message has the Microsoft Office Access
  caption

21
Customising Messages

• To disable Accesses error messages select
  Tools-gtOptions, select the edit/Find tab, uncheck
  Record changes and click Apply

Record Changes
22
Customising Messages

• We now need to edit the Access generated code of
  the Delete button
• In Design mode click on the delete button to
  display the properties window
• Go to the Click Event and click on the ellipses
  this will take you to the click event procedure
  code template see next slide

23
Customising Messages

• The code prefixed with DoCmd is the code that
  deletes the record all the rest is Access
  generated error trapping, which we want to keep

24
Message Boxes

• To display messages to the end user when the
  program is running VBA uses message boxes
  (MsgBox)
• They can be deployed in various ways depending on
  the requirements
• If they are to display only messages that inform
  users of a particular state or condition then the
  format can be either of the following see next
  slide

25
Message Boxes

• MsgBoxRecord Saved which will return only the
  text in an OK only message box with the Access
  caption

26
Message Boxes

• MsgBox "Record Saved", vbInformation which
  displays the same as previously shown but with an
  exclamation mark

27
Message Boxes

• MsgBox "Record Saved", vbInformation, "Bilbos
  Products As previous but with user defined
  caption

28
Message Boxes

• We can also get the message boxes to respond to
  user input i.e. Delete Record Yes/No/Cancel
• To do this we must use the MsgBox as a function
  i.e. return a single value
• When a message box uses the vbYesNoCancel
  function it returns an integer value dependant on
  whether the user has clicked Yes, No or Cancel

29
Message Boxes

• Yes 6
• No 7
• Cancel 2
• If we give this value to a variable we can then
  evaluate the value using an If statement in the
  code and display other messages or performing
  other tasks dependant on the users choice

30
Evaluating Message Boxes

• The format for such a message is

31
Evaluating Message Boxes

• We declare an integer variable to contain the
  response using the Dim VB keyword
• The message box parameters must be encapsulated
  in parentheses
• It is then passed to the variable

Dim response As Integer response
MsgBox("Delete Record?", vbYesNoCancel
vbQuestion, "Bilbos Products")
32
Evaluating Message Boxes

• The variable is then evaluated using the If
  statement notice the other message boxes used
  to inform the user whether the record has been
  deleted or not

If response 6 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70 DoCmd.DoMenuItem
acFormBar, acEditMenu, 6, , acMenuVer70
MsgBox "Record Deleted",
vbInformation vbOKOnly, "Bilbos Products"
Else MsgBox "Record
Not Deleted", vbInformation vbOKOnly, "Bilbos
Products" End If
33
Evaluating Message Boxes

• You can also use the vbYes function in place of
  the number i.e. 6, to make the code more readable
• For more information on message boxes read pages
  12 14 of the VBA trainer

34
Custom Event Procedures

• If a user accidentally changes data in a form and
  moves to the next record the data is saved
• We can prevent this by adding code to one of the
  many form events available
• The BeforeUpdate event is invoked if any changes
  have been made to the data

35
BeforeUpdate

• If we add the following code to the event and the
  user changes any data, clicks any of navigation
  buttons and then selects Yes, the data is saved
  otherwise the save is undone

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim response As Integer response
MsgBox("Save Details", vbYesNo, "Save") If
response vbNo Then Me.Undo
Cancel False End If End Sub
36
BeforeUpdate

• If we change Cancel to True, then the current
  record remains displayed when the user selects No
• If we add a Save button to the form then the
  BeforeUpdate event is called whenever data is
  changed and the Save button is clicked see next
  slide

37
BeforeUpdate
38
Inbuilt Functions

• VBA has many inbuilt functions i.e. IsNumeric,
  IsDate etc that can be used by the developer when
  validating user entry
• For example, if we had a textbox named
  txtPurchaseDate we could trap any erroneous input
  by the user by coding an If statement in an event
  i.e.
• If IsDate(txtPurchaseDate) then
• The IsDate function is a Boolean function that
  returns either True or False

39
User Defined Functions

• We can create our own functions and place them in
  a Standard Module i.e. a module that is not tied
  to any form or report. They contain generic code
  to be used anywhere in the program
• To add a standard module to the program click
  Insert-gtModule

40
User Defined Functions

• Assuming that the discount in the form below was
  calculated from the purchase date and date paid
  i.e. paid before 30 days then 20 discount
  30-60 days 10 discount gt 60 days no discount, we
  could code the calculation in the BeforeUpdate
  event

41
User Defined Functions

• However, we may need this calculation elsewhere
  in the program so a separate calculation function
  in the module would be better to do this, click
  on the code icon and in the project explorer,
  double click the Module icon

Project Explorer
Module Icon
42
User Defined Functions

• To add a new function template to the module
  select Insert-gtProcedure
• Give the function a name.
• Set the Type as Function
• Set the Scope as Public
• we need to use this in
• more than one place

43
User Defined Functions

• The code below uses the DateDiff inbuilt function
  that has 3 parameters and returns, in this
  instance, an integer value based on the
  difference in days between the 2nd two parameters
  given as parameters to the function
• The getDiscount function returns a Single value

Given Parameters
Return Value
44
User Defined Functions

• The function is useless unless it is called from
  within an event in this case the BeforeUpdate
  event

Private Sub Form_BeforeUpdate(Cancel As
Integer) Dim response As Integer response
MsgBox("Save Details", vbYesNo, "Save") If
response vbNo Then Me.Undo
Cancel True Else Me!txtDiscount Null
If Not Me!datePaid Then
Me!txtDiscount getDiscount(datePaid)
End If End If End Sub
45
User Defined Functions

• We could also place the code in the AfterUpate
  event of the datePaid text box so that the
  txtDiscount is calculated when the datePaid
  textbox loses the focus

Private Sub datePaid_AfterUpdate()
Me!txtDiscount Null If Not Me!datePaied
Then Me!txtDiscount getDiscount(datePaid
) End If End Sub
46
User Defined Sub Procedures

• However, we are repeating code, which we want to
  avoid
• To do this we select the Form Module an create a
  procedure by selecting Insert-gtProcedure
• Make the procedure private
• as it is only going to be
• used on this form

Select Private
47
User Defined Sub Procedures

• Add the following code

Private Sub calculateDiscount() Me!txtDiscount
Null If Not Me!datePaid Then
Me!txtDiscount getDiscount(PurchaseDate,
datePaid) End If End Sub
48
User Defined Sub Procedures

• Call the code from within each event

Private Sub Form_BeforeUpdate(Cancel As
Integer) Dim response As Integer response
MsgBox("Save Details", vbYesNo, "Save") If
response vbNo Then Me.Undo
Cancel True Else Call calculateDiscount End
If End Sub
Private Sub datePaid_AfterUpdate() Call
calculateDiscount End Sub
49
User Defined Sub Procedures

• See how much neater the code is this is called
  code re-use
• However, there is another calculation to be done
  on the form the total price based on the number
  of products, the product price and the discount
  i.e.
• totalproductPricenoOfProductsdiscount

50
Finishing it off

• We need another function in our Standard Module
  getTotalPrice that returns a currency value

Public Function getTotalPrice(discount As Single,
price As Currency, noProducts) As Currency Dim
total As Currency If Not discount And price ltgt 0
And noProducts ltgt 0 Then total (discount
(price noProducts)) price noProducts ElseIf
price ltgt 0 And noProducts ltgt 0 Then total
price noProducts Else total 0 End
If getTotalPrice total End Function
51
Finishing it off

• If we add the new function to our
  calculateDiscount procedure and change the name
  of the procedure to a more suitable name i.e.
  calculatePrice, we can call this procedure from
  other events
• Task try adding a similar procedure to the
  AfterUpdate event of some of the input texboxes
  in a program of your own

Private Sub calculatePrice() Me!txtDiscount
Null Me!txtTotal Null If Not Me!datePaid
Then Me!txtDiscount
getDiscount(PurchaseDate, datePaid)
Me!txtTotal Format((getTotalPrice(Me!txtDiscount
, Me!ProductPrice, Me!noOfProducts)),
"Currency") End If End Sub
52
Finishing it off

• This is my final code for the form on the right

Private Sub datePayed_AfterUpdate() Call
calculatePrice End Sub Private Sub
Form_AfterUpdate() Call calculatePrice End
Sub Private Sub Form_BeforeUpdate(Cancel As
Integer) Dim response As Integer response
MsgBox("Save Details", vbYesNo, "Save") If
response vbNo Then Me.Undo
Cancel True Else Call calculatePrice End
If End Sub Private Sub Form_Current() Call
calculatePrice End Sub Private Sub
noOfProducts_AfterUpdate() Call
calculatePrice End Sub
53
Summary

• In this tutorial we have covered
• Objects, Methods, Properties and Events
• Modules Class and Standard
• Modifying Event Procedures
• Adding user defined messages
• Adding User Defined Function
• Adding User Defined Sub Procedures

54
Summary cont.

• To cover VBA in any depth would require a whole
  module at least
• This tutorial has hopefully given you some ideas
  in how to customise Access programs
• For a more detailed coverage see references

55
References

• Getting Started with MS VB for Applications
  (VBA) - Available from your lecturer or from
  one of its creators, Mary Spence
• Further Uses of MS VBA same authors as above
• Any Good book especially Microsoft
  publications