1 / 55

VBA – Visual Basic for Applications

VBA – Visual Basic for Applications. Week 20 - Tutorial. 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. What is VBA. VBA is a programming language

yori
Download Presentation

VBA – Visual Basic for Applications

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. 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 Form’s 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 control’s 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->Options, 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 • MsgBox”Record 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->Module

  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 > 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->Procedure • 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->Procedure • 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. total=productPrice*noOfProducts*discount

  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 <> 0 And noProducts <> 0 Then total = (discount * (price * noProducts)) + price * noProducts ElseIf price <> 0 And noProducts <> 0 Then total = price * noProducts Else total = 0 End If getTotalPrice = total End Function

More Related