An Introduction to Microsoft Excel Macros
How to Create and Use a Macro in MS Excel VBA
Oct 26, 2009
According to the Oxford Dictionary, the definition of a macro is:
"a single instruction that expands automatically into a set of instructions to perform a particular task."
In Microsoft Excel, a macro fits this definition perfectly as it can be used to carry out numerous tasks with the press of a key. The tasks performed by the macro can range from the simple formatting of cells to producing multiple complex charts, running formulas and copying/saving files.
Why use Macros?
Macros are ideal for avoiding having to repeat the same task over and over again.
A user can manually change the font size, colour, style, row height and column width in an Excel workbook. However, if the user required the same formatting on multiple documents, a macro would not only reduce the time involved, it would also eliminate the risk of human error.
With the help of a macro this formatting only needs to be done once. The macro can then be run on every document, instantly ensuring each file is formatted identically.
Making a Macro
There are two basic ways to make a macro in Excel:
- The user can use Excel's macro recording feature to set the macro recording, perform the actions required and then stop the recorder.
- The user can script their macro using VBA (Visual Basic for Applications) programming language.
For the purpose of this article, the method of recording macros will be used.
How to Record a Macro
To record a macro in Office 2003 follow these steps:
- Select Tools from the menu bar
- Hover over 'Macro' and the menu should expand to the right
- Select 'Record New Macro...'
- A pop up box will appear asking where the macro is to be stored and what name is to be given. Use an appropriate name (without spaces) and store the macro in 'This Workbook'. Click OK.
- The macro is now recording. All actions made in the excel workbook will be recorded into the macro.
- A small popup box should have appeared with a stop button. Once finished, press the stop button to save the macro.
How to Run a Macro
Once recorded, the macro is played by following these steps:
- Select Tools from the menu bar
- Hover over 'Macro' and the menu should expand to the right
- Select 'Macros...'
- Select the macro required and click 'Run'
Personal Macro Workbook (Personal.xls)
When recording a macro the user is given a choice of where it should be stored. If the macro is only to be used in the file in which it is created, it is advisable to store it in 'This workbook'.
However, for a generic macro that is to be re-used in other files it can be stored in the personal macro workbook. This is essentially an excel document called personal.xls, on a PC with Windows XP as the operating system this file is located in:
C:\Documents and Settings\~Username~\Application Data\Microsoft\Excel\XLSTART
Once created, personal.xls will open each time Microsoft Excel is started. The user is usually unaware of the file as it is opened in the background and doesn't appear on the taskbar.
Shortcuts for Running Macros
For a macro used frequently, the process can be sped up even further by adding the macro to a toolbar or assigning a shortcut key.
To assign a shortcut key, access the macro as if about to run it. Instead of clicking 'Run', select 'Options'. A key can be assigned that, when pressed with the Control key, will run the macro automatically. The shortcut key can also be assigned when the macro is first created.
To add a macro to a toolbar, right click on any toolbar and select 'Customize'. Under the 'Commands' tab, drag the 'Custom button' onto the toolbar, this will normally appear as a smiley face. Right click on the new button and select 'Assign Macro'. Once a macro has been assigned, every time the button is pressed the macro will automatically start running.
What is VBA?
The programming code of a macro is stored in VBA (Visual Basic for Applications). By using Excel's macro recording feature as described in this article, Excel automatically converts the actions taken by the user into VBA code. This means macro creation is accessible to users with no prior programming knowledge.
For more advanced users, the code generated can be viewed and modified in the Visual Basic Editor. To access this, select Tools -> Macro -> Visual Basic Editor. Editing the VBA code can make the macro more flexible and efficient.
Recording Macros vs. VBA Scripting
Advance users of Excel will usually script macros in the VB Editor rather than using Excel's recording feature. There are many more features available in Visual Basic making the macro far more versatile. It is also possible to call functions that are to be used repeatedly, avoiding the need for duplicating the code.
However, for the basic user there is no doubt that Excel's recording feature makes it possible to create and run basic macros very easily without any prior knowledge of programming or VBA.
 |
