To Documents

Excel Macros

Changing Security Settings

• Before you can open an Excel file containing a macro, you must change Excel's macro security setting:
  1. Invoke Excel: Start >> Programs >> Microsoft Office >> Microsort Office Excel 2003.

  2. Select Main Menu Tools >> Macro >> Security. Make sure the Security Level Tab is selected >> Check the Medium radio button >> Click OK.

  3. Change to the Trusted Publishers Tab. Check both of the checkboxes "Trust all installed add-ins and templates" and "Trust access to Visual Basic project." Click OK.

• When you open an Excel file containing a macro, click the Enable Macros button.

Recording an Excel Macro

• Start with a blank Excel Spreadsheet.
  1. Select Tools >> Macro >> Record New Macro.

  2. Enter the Macro Name and the Shortcut Key in the Record Macro Dialog.

  3. Make any modifications that you wish to be recorded. Suggestions are:
     Enter numbers or characters in cells.

     Select a rectangular range of cells.

     Set the font name of a selected area.

     Set the font size of a selected area.

     Set the font color of a selected area.

     Set the font of a selected area to bold.

     Set the font of a selected area to italic.

     Set the fill color of a selected area.

     Draw borders, including line style and line color.

     Left justify, right justify or center cell contents.

  4. When you are finished, stop recording by selecting Tools >> Macros >> Stop Recording.

Running an Excel Macro

• Here are two ways to run a recorded macro:
  1. Select Tools >> Macros >> Macros. Select the desired macro in the Macro Dialog and click the Run button.

  2. Hold down the control key and press the short cut key for the macro.

Editing an Excel Macro

• To edit an Excel Macro: Select Tools >> Macros >> Macros. Select the desired macro in the Macro Dialog and click the Edit button.

• View the macro or make changes.

Excel Macro Objects

• Here are some Excel macro objects:
  Range("cells in range")   A specified range of cells. Example:
  Range("B3:F11")

  Selection   The current selection of cells.

  Font   A font object containing information like font name, size, color, etc.

  ActiveCell   The cell that is currently active.

  Interior   The interior region of currently selected cells, containing information like border type and fill color.

• The dot operator sets up a belonging relationship for objects. For example
  Selection.Font.Bold = True

  states that
  1. the Font object belongs to the Selection Object

  2. The Bold property belongs to the Font object

• The item at right can also be an action as in this example:
  Selection.ClearContents

• Here are two possible sequences involving dot operators:
  1. Object1.Object2.Object3...Objectn.Property = Value
     Example:   Selection.Font.Bold = True

  2. Object1.Object2.Object3...Objectn.Action
     Example:   Selection.ClearContents