Develop a macro to specification
Introduction
Tutorials to download
Develop a macro to specification
Uses of macros
Macro languages — VBA
Other macro languages
Reviewing basic macro operations
Recording macros
Storing macros
Assigning word processing macros
Visual Basic for Applications
Understanding programming concepts
What is the Visual Basic Editor?
Basic structure of the code
Interpreting VBA code
VBA Help
Manipulation of macros
Editing a word processing macro
Copying and renaming a word processing macro
The development process — testing
Obvious errors
Hidden errors
What techniques can you use to test your macros?
Testing tip
Tracing macro actions
Demonstrating to the client
Requesting user information in a macro
InputBox() statement
Providing information to users
The MsgBox statement
Forms and form fields
Creating an online form in Word
Ways to activate Word macros
Assigning a macro to fields on forms
Macro buttons on forms — a document automation field
Create automatically run macros
Macros for spreadsheets
Review basic spreadsheet macro operations
MS Word versus Excel macros
Spreadsheet macro code
Editing spreadsheet macros
Absolute and relative recording in a spreadsheet macro
Switching between modes
Function macros
Advantages of function macros
Characteristics of a function macro
Using a function macro
Tips for writing function macros
Spreadsheet forms
User forms
Customising the activation of a macro
Spreadsheet references
Macros in a database
A database front end menu system
Automating the startup of a database
Database macro code
Interpreting the code
Summary
Check your progress
Introduction
This topic assumes that you already know how to create simple macros. Nevertheless, it is a good idea to start the topic by revising what you know, and we will do some revision here. So even if you haven’t created macros, the activities should help to quickly bring you up to speed with them.
This is a complex topic that requires you to view and edit code in a programming language. You may have carried out many advanced operations with applications without having to do this. You can’t avoid it here! This topic doesn’t aim to turn you into a professional programmer, but you’ll need to understand basic programming language concepts, and this can be difficult to begin with.
Tutorials to download
In the Research section of this Learning Pack there are a number of tutorials you can download, in order to practise applying the features discussed in this reading. You will be referred to tutorials where relevant.
If you are using applications other than Microsoft, there are also websites listed in the Research section that can provide you with tutorials, help or other assistance to learn how to use the features in your software to create and edit macros.
Develop a macro to specification
Clients will differ in their specific macro needs. There will be repetitive tasks that have to be carried out many times in many documents. There will be a variety of users doing different types of jobs. This doesn’t mean you have to be an expert in a macro programming language, but you’ll need to have some understanding of what a macro language is, how it operates, how you can make simple changes, and use the tools available for fixing problems.
Let’s start by revising what you already know about macros.
Uses of macros
A macro can group together a series of steps or actions into a single command to make everyday tasks easier. You can get a macro to run in a number of different ways: assigning it to a toolbar button, a menu item or shortcut keys.
Below we’ve provided a list of some of the useful things you can do with macros. Most of them apply to all the applications in common use, though they are more relevant to some than others, and it obviously depends on your particular client’s needs.
Also, features are implemented slightly differently in different applications, as well as in different versions of an application. The bottom line is that you should get as much practice as you can creating and editing simple macros in a variety of applications — the more the better. We’ll be showing you some of the different macro tools that you can use. In some, but not all cases, what you learn about macros in one application should work in another. So, here are some of the things you should be able to do at the end of the topic:
- automate complex and repetitive tasks
- speed uproutine editing and formatting
- speed up data entry and reduce errors through forms, custom dialog boxes, input boxes, message boxes and controls such as text boxes, drop-down lists, check boxes, and so on.
- quickly carry out customised functions through buttons, customised menus and toolbars
- provide information and assistance to users
- create startup routines which protect your application and data from user errors
- carry out certain tasks automatically (without the need for user action)
- create (spreadsheet) functions for particular calculations that may be required
- help users carry out tasks that would otherwise require them to have an advanced skill level.
Macro languages — VBA
The programming language that underlies the macros created in Microsoft Office applications is called Visual Basic for Applications (VBA). Don’t confuse this with Visual Basic. VBA programs are designed to run within applications, not on their own outside an application. You can create and run macros for Word, Excel, Access, PowerPoint, Publisher, and even Outlook. The latest flavour of Visual Basic is called Visual Basic.Net.
Some (but not all) applications including Word, Excel and PowerPoint, allow you to easily create macros by recording a sequence of operations. Access allows you to create macros by selecting from a built-in list of actions.
Macros can be opened in a program called the Visual Basic Editor where they can be modified and enhanced. Of course, macros can also be created from scratch with the Editor. This is required in any case, for actions that cannot simply be recorded. In this topic you will be creating and modifying simple VBA macros in different applications. We’ll get back to VBA in a little while.
Other macro languages
This topic will largely deal with Visual Basic for Applications because the most widely used office applications are those produced by Microsoft. However, they are not the only ones. There are several alternatives, with an array of scripting languages for creating macros. Some of the alternative applications are compatible with the MS Office ones — they can successfully open and save various Office files. However, this is generally not the case with macros because of the different scripting languages involved. Following is an outline of some other suites and macro languages.
Lotus Smartsuite
Lotus Smartsuite provides office productivity applications, including Word Pro, 1-2-3, Organizer, Freelance Graphics, and Approach. The common programming language used by Lotus applications is called LotusScript
Corel Wordperfect Office
This suite comprises WordPerfect, Quattro Pro and Presentations. Macros are written in command based macro language called PerfectScript.
Ability Office
This is a low cost but good quality suite of applications. It uses VBScript to automate tasks.
Open Office
Open Office.org also provides an application suite, which was designed to be compatible with Microsoft Office applications. It is free (although you should check the licence), and is available for Microsoft Windows, Sun Solaris, other Unix-like systems, and Mac OS X.
In OpenOffice.org (or Ooo — the ‘.org’ inclusion is due to a trademark dispute), macros and dialogs are stored in documents and libraries. The included Integrated Development Environment (IDE) is used to create and debug macros. If you wish to create macros for OpenOffice, you’ll need to first learn about working in the IDE. You’ll find useful information on this in the Research section.
Reviewing basic macro operations
Before proceeding, you should check that you can carry out basic macro operations. It’s good to start with what you know, and in any case, you’ll need to create a few simple macros for later exercises. What simple operations should you be able to carry out?
You need to be able to:
- create macros using a ‘macro recorder’ function (if it is available)
- pause and resume the recording of a macro
- save a macro, storing it in an appropriate location
- run a macro
- assign a macro to a keyboard shortcut or toolbar
- delete a macro.
Recording macros
To record a macro, simply
- turn on the recording feature
- carry out the desired actions
- stop the recorder.
Recording tips and techniques
Keep in mind the following tips and techniques when recording macros. Some of them are specific to MS Office.
- It’s often a good idea to create a reverse macro to restore default settings.
- The macro recorder cannot record mouse actions in document text — the keyboard must be used to move the insertion point and select text.
- The mouse can be used to select commands and options.
- You can temporarily suspend recording and then resume recording where you stopped — for example, you might want to prepare for the next action.
- Before you record the macro, practise the sequence of operations first, because the macro will also record any mistakes and their corrections, and this adds extra unnecessary code.
If the macro you are recording is meant to run in different documents, make sure that the macro does not depend on the contents of the particular document in which you are recording.
- Try to anticipate and avoid messages that might be displayed. For example, if your macro includes a command to close a document, you’ll be prompted to save changes. A good way to avoid this message is to record a command to save the document first.
- If the macro includes the Find command or the Replace command, it is wise to click All in the Search box in either the Find dialog box or the Replace dialog box. This also avoids displaying a message (such as ‘Do you want to continue searching?’).
A special tip for the unwary
The most common mistake for novices when recording macros is forgetting to press the stop button. This tends to happen if you make a mistake while recording the macro. You get so busy concentrating on how to do something, and clicking here, there and everywhere, that you forget that the recorder is still running. One of the worst things that you can do is restart the recorder, or try to run a macro while the recorder is still running.
You can end up with disastrous results. The first time you do this you’ll think that the computer has gone mad, because you’ve ended up in what’s called an infinite loop. If you’ve included printing within such a macro, you could end up printing hundreds of pages!
You should largely be able to avoid this by practising the steps in the macro and knowing exactly what you need to do — even writing them down is a good idea, so you don’t have to trust your memory for a procedure that has many steps.
Storing macros
You should be clear on where you want to store your macros. Do you want your macro to:
- be stored and only run in the current document?
- be stored in a different document?
- be stored in a specific template, and be available for use in any document based on that template?
- be stored ‘globally’, ie be available to all documents that will be created?
These choices depend on the particular macro and its purpose. You may be creating a macro that has a specific purpose in a particular workplace template, such as a fax form, so it is obvious that you will store it within that template. You may be creating a macro that will be used in a broad range of documents for an organisation. Such a macro could be built into a global or default template.
Apart from choosing which document or template to store your macros in, applications have specific requirements for the location, name, and use of templates in order to make them available either for global or custom use. For example, templates in Excel are stored either in a ‘Templates’ folder, or an ‘XLStart’ folder. You need to investigate where and how to store your macros to make them available for the various applications you use.
Tutorial 1
Download Tutorial 1 from the Research section of this Learning Pack. In Tutorial 1 you get a chance to revise recording a simple macro in Microsoft Word. You should also try the practise activity: Creating a simple word processing macro.
Assigning word processing macros
Depending on the application, when you’re recording a macro, you are provided with a simple choice for assigning the macro — commonly a keyboard shortcut. However, you need to be able to implement a range of methods for running a macro, depending on the client’s needs. For example, you should be able to assign a macro to the following:
- an existing menu
- a new menu
- a shortcut key
- an existing toolbar
- a new toolbar.
Tutorial 2
Download Tutorial 2 from the Research section of this Learning Pack. In Tutorial 2, you can learn about different ways of assigning word processing macros in MS Word.
Visual Basic for Applications
Understanding programming concepts
In order to effectively study this topic, you need to develop an understanding of simple programming concepts and constructs. Ideally you have already used a programming language at an elementary level. If you haven’t, this topic will be a bit harder for you. We will provide you with an introduction, here. However, you may need to spend a fair bit of time working through the tutorials and other references in the Research section, in order to further develop your understanding.
What is the Visual Basic Editor?
As mentioned earlier, the Visual Basic Editor is a program used for creating and editing Visual Basic for Applications code, and provides the environment for creating the macros used in MS Office applications. There are scripting languages for other applications. We will use VBA as our example here.
In MS Office applications, the Visual Basic Editor is found under Tools… Macro. After starting the Editor, you’ll be presented with what might be a fairly unfamiliar and daunting screen.
Refer to the graphic below. There are two main sections: the Project Explorer on the left, and the Visual Basic Editor on the right. If the Project Explorer isn’t visible, you can display it by pressing the combination Control + R.
The Project Explorer groups together the high level objects in a Visual Basic Project. From here you can access Visual Basic code in any module of an open template or document. The Visual Basic Editor on the right displays the code for the selected object in the left window.
The VBA Editor window and Project Explorer
Figure 1: Visual Basic Editor
The Visual Basic Editor
In the Project Explorer section, look at the number of documents present. However, notice what is selected — the NewMacros module, and the code displayed in the right window is for two macros stored in the Normal template.
Basic structure of the code
We’ll start by defining some basic VBA terminology.
Subroutine / VBA code normally consists of a series of subroutines or parts. Each of these starts with Sub() followed by the name of the routine. The end of the subroutine, is shown by the words, (surprise, surprise) End Sub. Each subroutine corresponds to a separate macro.Statement / These are the instructions that make up subroutines. Contained in the instructions are three basic elements: objects, methods (actions) and properties.
Object / An object is something which can be clicked on, such as a worksheet, cell, and so on.
Method / A method is some action which can be applied to an object.
Property / A property is just as it sounds — an attribute of an object that can be set or changed. It’s easy to think of simple examples of this such as the bold property that can be applied to text.
Examining different examples of code is the best way to understand these elements.
Interpreting VBA code
Here is an example of the code generated after recording a simple word processing macro. This is how it appears in the Visual Basic Editor:
Figure 2: VBA code for a simple macro
We’ll look at some of the features of this code.
Colour coding
Parts of the code are displayed in colour. If you can’t see the colour coding, you can configure it. If your VBA Editor is not showing colours you can configure this through Tools/Options/Editor Format.
In this exampleblue is for words that are part of the VBA language (Keyword Text). These words should not be deleted or altered.
Green is for comments that are ignored by the computer when the code is run (Comment Text). Comments are an important inclusion when you create and edit code. They help you to explain the purpose of the code and include information to anyone who may have to read or edit the code later on.
Identifying a subroutine
A macro is contained between two statements: Sub followed bythe macro name, and at the end of the macro an End Sub statement. In this case, the name of the macro is MyName. Each macro is a different subroutine. If you have several macros, horizontal lines will separate them.
With… End With statement
The majority of the macro example consists of a With… End With statement.
Each word in the statement that begins with a period corresponds to an option in dialog box, eg SpaceBefore. These are called arguments or parameters.