How to record an Excel macro

Now you’re probably eager to record your first macro in Excel 2007 or Excel 2010…

We’ll now cover how to use Excel’s macro recorder to create a simple macro in Excel.

 

*Please note, we do not cover writing or editing a macro using the VBA editor here.  Also, recorded macros can be greatly improved by using the VBA editor to fine tune how they work and make them more flexible.

In Excel 2007 and 2010, all macro options are to be found on the Developer tab of the Excel ribbon. You won’t have this tab as default, so here’s how to add it:

 

Add Developer Tab

Add Developer Tab

Click on the Office button in the top left hand corner of the screen then choose the Excel Options button found at the bottom.  This opens the Excel Options menu.

Next select the Popular option at the top of the left hand window of the open dialog box and tick the box as in the above image.

The macro we are going to make will simply change the font of the active sheet.  So to start recording select the developer tab from the menu and then choose record macro.

Record Excel Macro

Record Excel Macro

As soon as you select this the macro starts recording your actions.  These actions might be what sheets you select, what cells you format, what columns you delete and so on…

 

You will be asked to give your macro a name,  a description and also to assign a keyboard shortcut (you can leave this blank if you like).  The keyboard shortcut will allow you to run the macro quickly via a shortcut (here it’s holding down Control and then pressing “r”).

 

 

Name Excel Macro

Name Excel Macro

 

Now we will record a macro that will change the font of the active sheet and enlarge the contents of A1.

When finished push the “stop” icon.  The code below is actually what gets recorded.  The bits in bold are the main actions that we are interested in.

 

 

 

 

 

Sub Example()
‘ Example Macro
‘ This macro will change the font of the active sheet and make the font in cell A1 larger and bold
‘ Keyboard Shortcut: Ctrl+r
Cells.Select
With Selection.Font
.Name = “Courier New”
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range(“A1″).Select
With Selection.Font
.Name = “Courier New”
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Bold = True
End Sub

so our macro has recorded us selecting all the cells (on the active sheet), changing the font of all cells to “courier new”, changing the size of the font in cell A1 to 14 and then making that cell bold.

 

Note: If we now select a different sheet and run our macro (Control and “r”) then the above will be applied to the active sheet, so we’d have to edit our macro if we only ever wanted these actions to occur on a certain sheet.

Now to save this macro we save the workbook as a macro-enabled workbook (extension xlsm)

So now you know enough to start recording a macro and experimenting with how they work…

1 comment to How to record an Excel macro

Leave a Reply

  

  

  


− 5 = four

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>