| Macros offer a powerful and flexible way to
extend the features of MS Excel. They allow the automation of
formatting, charting and other often-used spreadsheet tasks.
Perhaps more significantly, macros also enable the user to
seamlessly integrate an Excel workbook with another
application, such as WinWedge. This article provides an
overview of creating, editing, saving and invoking macros.
At its simplest, a macro is just a recording of the
keystrokes and mouse actions involved in performing a
particular task. At any time after the macro is created, the
task can be automatically performed by invoking the macro,
which essentially plays back the recording. More advanced
macros can display custom forms (with command buttons, text
boxes, drop-down lists, etc.) and interact with other
applications; these macros typically involve the writing and
editing of Visual Basic for Applications (VBA) program
code.
The examples in this article were created with Excel 2000,
but may be easily adapted to prior (or later) versions of
Excel.
A Simple Macro
If you frequently need to format individual cells in a
particular way (for example, currency style in red Arial bold
12-point font), a simple recorded macro will do the trick. Use
the following steps to create it:
- Select Tools, Macro, Record New Macro to display
the Record Macro dialog box.
- In the Record Macro dialog box, type a
descriptive Macro name (such as "RedCurrency"). By
default, the macro will be stored and available only in the
current workbook; if appropriate, open the Store macro
in drop-down list and select either New Workbook (to
store and use the macro only in a new empty workbook) or
Personal Macro Workbook (to make the macro available in
all workbooks). If desired, edit the text in
Description. When done, click the OK button to
begin recording.
- The word "Recording" will appear on the status bar at
the bottom of the Excel window to remind you that all
keystrokes and mouse actions are now being recorded.
Depending on how your system is configured, a Stop
Recording toolbar may also appear in the window. If you
make a mistake, simply correct it as you normally would and
continue; both the mistake and its correction will become
part of the macro, and may be edited out later if
desired.
- Perform the actions necessary to complete the task. For
this example, the actions are:
- Select Format, Cells to display the Format
Cells dialog box, click the Number tab, select
Currency in the Category list, and click the
OK button; as an alternative, you may simply click
the $ button on the Formatting toolbar.
- Select Format, Cells to display the Format
Cells dialog box, click the Font tab and
then:
- open the Color drop-down list and select the
desired color;
- select Arial from the Font list;
- select Bold from the Font style list;
- select 12 from the Size list; and
- click the OK button.
- If the Stop Recording toolbar is visible, click
its Stop Recording button; if the toolbar is not visible,
select Tools, Macro, Stop Recording.
A VBA Macro
Some macros, especially those designed to interact with
another application, must be created using the Visual Basic
Editor. For example, the GetSWData macro shown below
increments a row pointer, retrieves a single field of data
from the Software Wedge, and places it in column A of the
indicated row in Sheet1 of the current workbook. Text
following an apostrophe is a comment, and does not affect the
operation of the macro.
| |
Sub
GetSWData()
'preserve
current row pointer value between macro
calls
Static RowPointer As
Long
'increment row
pointer (initialized to 0 on first
call)
RowPointer = RowPointer +
1
'establish DDE link
to WinWedge on Com1
ChannelNum =
DDEInitiate("WinWedge", "Com1")
'retrieve Field(1) from
WinWedge into variant array F1
F1
= DDERequest(ChannelNum, "Field(1)")
'convert variant to
string
WedgeData$ = F1(1)
'write data to first column in
current
row
Sheets("Sheet1").Cells(RowPointer,
1).Value = WedgeData$
'close the DDE
link
DDETerminate
ChannelNum
End Sub |
To create this macro, do the following:
- Select Tools, Macro, Macros to display the
Macro dialog box.
- Type in the name of the macro, and click the
Create button.
- A code window will open in the Visual Basic Editor, with
the first and last lines of the macro already typed in for
you.
- Type in the balance of the macro and close the window
captioned Microsoft Visual Basic.
Regardless of whether a macro was created by recording or
through the Visual Basic Editor, it is stored as VBA code. For
example, the simple recorded macro described above might be
stored as follows:
| |
Sub RedCurrency() '
' Macro2
Macro
' Macro recorded 11/9/1999 by Tal
Technologies,
Inc.
'
Selection.Style
= "Currency"
With
Selection.Font
.Name =
"Arial"
.FontStyle =
"Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End
With
End Sub |
If you need to correct or modify a macro, you always use
the Visual Basic Editor. The procedure is:
- Select Tools, Macro, Macros to display the
Macro dialog box.
- Select the name of the macro you wish to edit, and click
the Edit button.
- A Visual Basic Editor window will open with macro's code
in it.
- Make the desired corrections or modifications and close
the Window captioned Microsoft Visual Basic.
Macros are saved as part of the workbook in which they were
created. If you attempt to exit from Excel without saving any
macros you created or modified, you will see a warning dialog
box giving you the option to save the affected workbook. You
can avoid seeing this warning by saving your work in either
Excel or the Visual Basic Editor.
Tools Menu
The standard way to invoke a macro is via the Tools menu,
as follows:
- Select Tool, Macro, Macros to display the
Macro dialog box.
- Select the name of the macro you wish to invoke, and
click the Run button.
- The macro will run immediately. For example, if you
invoked the RedCurrency macro above, the formatting of the
current cell would immediately change to red Arial bold
12-point font.
Worksheet Button
A more convenient way to invoke a macro is via a command
button on the worksheet itself. While you can install a
command button from either the Controls Toolbox toolbar or the
Forms toolbar, you should generally use only the Forms
toolbar. When a macro is invoked from a command button
installed from the Controls Toolbox toolbar, the button itself
retains the focus, and this may interfere with the correct
operation of the macro. When a macro is invoked from a command
button installed from the Forms toolbar, the button does not
retain the focus and the macro executes normally. To create a
command button on a worksheet, do the following:
- Click the Create Button button on the
Forms toolbar.
- Drag the mouse pointer on the worksheet to indicate the
location and shape of the command button you wish to create.
When you release the mouse button, the command button will
appear, along with an Assign Macro dialog box.
- In the Assign Macro dialog box, select the name
of the macro to be invoked by the button, and then click the
OK button.
- Type a descriptive name to appear on the button
face.
- Click in the worksheet, but not on the command
button, to de-select the command button.
- Click the "X" button at the upper right corner of the
Forms toolbar to remove it from the screen.
The macro will now execute immediately whenever the command
button is clicked.
Custom Toolbar Button
You can also use a custom button on any of the toolbars to
invoke a macro. To create a custom button on a toolbar, do the
following:
- If the toolbar you wish to add the custom button to is
not visible, select View, Toolbars and then click the
name of that toolbar to display it.
- Select View, Toolbars, Customize to display the
Customize dialog box.
- Click the Commands tab.
- In the Categories list select Macros.
- Drag the Custom Button icon from the Commands
list and drop it at the desired position on the toolbar.
During the drag, a small square box will appear just below
and to the right of the mouse pointer. The box will contain
a "+" when the mouse pointer is at a location where the
custom button may dropped; otherwise it will contain an
"x".
- Right-click the new custom button on the toolbar, and
select Assign Macro to display the Assign
Macro dialog box.
- In the Assign Macro dialog box, select the name
of the macro to be invoked when the custom button is
clicked, and click the OK button.
- If you want to change the button image, right-click the
new custom button on the toolbar, and select Change
Button Image and then click the image you want to
use.
- Click the Close button in the Customize
dialog box to close the dialog box.
The macro will now execute immediately whenever the custom
toolbar button is clicked.
Shortcut Key
You can also invoke a macro by simply pressing its assigned
shortcut key (sometimes known as a "hot key"). If you are
recording a new macro, you can assign a shortcut key to it
while the Record Macro dialog box is displayed. Simply
click the Shortcut key text box and either type a
letter key (to produce a Ctrl shortcut key) or type a letter
key while holding down the Shift key (to produce a Ctrl-Shift
shortcut key).
After a macro has been created, you can assign or change
its shortcut key by doing the following:
- Select Tools, Macro, Macros to display the
Macro dialog box
- Select the name of the macro and click the
Options button to display the Macro Options
dialog box.
- Enter the shortcut key as indicated above, and click the
OK button.
- Close the Macro dialog box by clicking either the
close ("X") button on the title bar or the Cancel
button.
The macro will now execute immediately whenever the
shortcut key is pressed. Remember to hold down the Ctrl key
(for Ctrl shortcut keys) or both the Ctrl and Shift keys (for
Ctrl-Shift shortcut keys) while you press the letter
key. |
