Introduction to Excel (and VBA) macros

​

Creating a simple (non-vba) macro

1. Select Tools, Macro, Record New Macro to display the Record Macro dialog box.
2. 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.
3. 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.
4. Perform the actions necessary to complete the task. For this example, the actions are:
5. 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.
6. Select Format, Cells to display the Format Cells dialog box, click the Font tab and then:
   1. open the Color drop-down list and select the desired color;
   2. select Arial from the Font list;
   3. select Bold from the Font style list;
   4. select 12 from the Size list; and
   5. click the OK button.
7. If the Stop Recording toolbar is visible, click its Stop Recording button; if the toolbar is not visible, select Tools, Macro, Stop Recording.

​

Creating a VBA 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

1. Select Tools, Macro, Macros to display the Macro dialog box.
2. Type in the name of the macro, and click the Create button.
3. A code window will open in the Visual Basic Editor, with the first and last lines of the macro already typed in for you.
4. Type in the balance of the macro and close the window captioned Microsoft Visual Basic.

​

Editing macros

 Sub RedCurrency()
   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

1. Select Tools, Macro, Macros to display the Macro dialog box.
2. Select the name of the macro you wish to edit, and click the Edit button.
3. A Visual Basic Editor window will open with macro’s code in it.
4. Make the desired corrections or modifications and close the Window captioned Microsoft Visual Basic.

​

Saving macros

Invoking a macro in the tools menu

1. Select Tool, Macro, Macros to display the Macro dialog box.
2. Select the name of the macro you wish to invoke, and click the Run button.
3. 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.

​

Invoking a macro using a worksheet button

1. Select View, Toolbars, Forms to display the Forms toolbar.
2. On the Forms toolbar, click Create Button.

3. 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.
4. In the Assign Macro dialog box, select the name of the macro to be invoked by the button, and then click the OK button.
5. Type a descriptive name to appear on the button face.
6. Click in the worksheet, but not on the command button, to de-select the command button.
7. Click the “X” button at the upper right corner of the Forms toolbar to remove it from the screen.

Invoking a macro using a custom toolbar button

1. 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.
2. Select View, Toolbars, Customize to display the Customize dialog box.
3. Click the Commands tab.
4. In the Categories list select Macros.
5. 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”.
6. Right-click the new custom button on the toolbar, and select Assign Macro to display the Assign Macro dialog box.
7. 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.
8. 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.
9. Click the Close button in the Customize dialog box to close the dialog box.

​

Invoking a macro using a shortcut key

1. Select Tools, Macro, Macros to display the Macro dialog box
2. Select the name of the macro and click the Options button to display the Macro Options dialog box.
3. Enter the shortcut key as indicated above, and click the OK button.
4. Close the Macro dialog box by clicking either the close (“X”) button on the title bar or the Cancel button.