Introduction to Excel Macros

Introduction to Excel Macros

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.   Creating Macros 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.   Editing Macros 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.   Saving Macros 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.   Invoking Macros 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: Select View, Toolbars, Forms to display the Forms toolbar. 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 selectChange 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.