Macros

1
Macros

• Tutorial Week 20

2
Objectives

• By the end of this tutorial you should understand
  how to
• Create macros
• Assign macros to events
• Associate macros with VBA
• Assign a macro to an application Switchboard at
  start up
• Add error messages through macros associated with
  events

3
What are macros?

• Macros are a means by which we can automate a
  process or group of processes.
• For example, in many Microsoft applications we
  can record a macro to automate a task that the
  end user performs on a regular basis

4
Recording a macro

• A useful macro for a student to record would be
  to set up a Word document that can be used as a
  generic template for all the reports that need to
  be written for assignments
• Most academic reports require a contents list,
  page numbers, headings and a footer showing the
  authors name

5
Recording a macro

• To do this, create a new Word document
• Then select Tools, Macros, Record New Macro from
  the main menu

6
Recording a macro

• Give the macro a name
• You could assign the template to a keyboard
  shortcut, a toolbar or run it directly from the
  macro menu
• Click OK

7
Recording a macro

• Add the section headings and assign them to
  Heading 1 style
• Set each heading on a new page by pressing
  ctrl_Enter
• Add the footer with page numbers

8
Recording a macro

• Add appropriate table of contents from main menu
• Select Table of Contents tab and click OK

9
Recording a macro

• Click Stop Recording
• Close the document without saving
• The macro is now available to use

10
Playing a recorded macro

• Open a new document and select Tools, Macro,
  Macros
• Find the recorded macro from the list and click
  Run
• The document will open with your pre-formatted
  conditions

11
VBA Visual Basic for Applications

• The actions performed whilst recording the macro
  have been converted into lines of Visual Basic
  code
• To view the code select Tools, Macro, Macros and
  then select the macro in the LH window and click
  Step into more on VBA later

12
VBA Code
13
Macros in MS Access

• To use macros in MS Access we have to use a
  different approach as we cannot record the
  macro
• Before we create a macro we must consider what we
  want to automate
• As macros are based on VB code a macro can only
  respond to an event

14
Macros in MS Access

• There are many pre-written actions that a macro
  can respond to
• To view these actions select the Macro option in
  the database design interface and click New

15
Macros in MS Access

• Click on the Action dropdown list to view the
  actions

16
Macros in MS Access

• Now let us consider where we could use macros
• As computing students you will have to develop a
  software project, possibly using MS Access
• An Access project must have a menu structure to
  allow the end user to navigate though the system
• Access provides this functionality through a
  Switchboard

17
Switchboards

• An example of a switchboard is shown below

18
Switchboards

• When the user clicks on the database icon we
  would not normally expect them to have access to
  the development environment, only the menu
  (switchboard)
• To load the switchboard at the program start we
  can create a new macro named AutoExec

19
AutoExec Macro

• Assume that we have already created our
  Switchboard (a tutorial for creating switchboards
  is available)
• Click on Macros in the development window and
  select New
• Select OpenForm from the action list and set
  the Form name to Switchboard see next slide

20
AutoExec Macro

• Set the Data Mode to Edit
• Close and name the macro AutoExec
• There can only be one file of this name in any
  application

21
Setting Startup

• The next time you open the program it will look
  for the AutoExec macro and perform the actions
  assigned to it in this case, open the
  Switchboard
• However, the end user still has access to the
  development environment
• To prevent this select Tools, Startup from the
  main menu

22
Setting Startup

• Uncheck all the options in the dialog box to
  remove all development toolbars
• This is best performed after all development has
  been carried out
• However, after un-checking all the options you
  can access the development environment by
  pressing and holding the Shift key before
  clicking the icon to open the application

23
Multiple Actions

• It is possible to assign multiple actions to a
  macro, for example, if we wanted to open a
  Clients table showing the records in descending
  order and set the window to maximised we would
  write the following macro

24
Multiple Actions

• The action arguments are assigned in the bottom
  LH pane of the design window

25
Activating the macro

• To activate the macro we can either double click
  the macro name in the Macros window or assign it
  to an event
• If end-users only have access to the Switchboard
  then we will have to assign the macro to the
  click event of one of the switchboard menu buttons

26
Assigning a macro to an event

• Assuming that a Switchboard has been added to the
  application, click on Tools, Database Utilities,
  Switchboard manager see below

27
Assigning a macro to an event

• If we have a menu structure similar to the one
  shown, click on Open Tables and select Edit
• Then click New see next slide

28
Assigning a macro to an event

• Add a name in the Text textbox
• Select Run Macro from the Command list
• Select the OpenClientTable from the Macro List
  and click OK and close switchboard manager

29
Running the Switchboard Macro

• Open the Switchboard
• Click on Open Tables in the Main Switchboard
• Click on Open Client Table and the table will be
  displayed maximised and the data listed in
  descending order

30
Macro Conditions

• You may want to specify a condition i.e. do not
  display the table if there are no records to
  display
• To add conditions click on the Conditions icon
  on the main menu bar and the conditions column is
  displayed

31
Building the Condition expression

• Right click on the row where you want to apply
  the condition and select Build
• From the Built in Functions list select DCount
• Add the parameters to the Function

32
Building the Condition expression

• The DCount function will return the count of all
  the records in the Client table.
• The expression checks if count gt 0
• If the expression is true then all the actions
  displaying ellipses ( (denoting that the above
  condition also applies to these actions)) in the
  condition column are invoked see next slide

33
Building the Condition expression

• If the condition is met then a message is
  displayed informing the user that the macro has
  been run

34
Adding Message Boxes

• If the expression returns false then all the
  actions with ellipses are ignored
• Below shows another condition which checks if
  there are no records if true then display an
  appropriate message i.e. No Data to Display

35
Adding Macros to Property Events

• Another approach to assigning macros is to use
  the Events of a control, form or report at
  design time
• For example, if we only want to display a report
  if there is data to display we could use the On
  No Data event of the report

36
Adding Macros to Property Events

• The following report asks the user to enter two
  date parameters and displays all orders between
  those dates

37
Adding Macros to Property Events

• The problem is that if there are no orders
  between those dates an empty report is displayed
• To remedy this we add the following macro to the
  On No Data event of the report
• If there is no data to display the message is
  displayed and the event is cancelled

38
Adding Macros to Property Events

• To access the On No Data event of the report,
  open the report in design view and click the
  Properties icon on the main menu
• Select the Event tab and add the macro to the
  On No Data event
• The macro will only run if there is no data

39
Summary

• In this tutorial we have looked at
• How to record a macro
• How to create an Access macro
• How to associate a macro with an event
• How to create an AutoExec macro
• How to associate the AutoExec macro with startup
  and the Switchboard
• How to add conditions to the macro
• How to create error/information messages in macros