Recording macros in VBA – A great way of learning

Sometimes when you are working with some VBA module of yours you wonder how to, for example, bolden the text in a cell or how to add borders to a selection. Well, finding out how to do that is quite simple as Office lets you record macros in Excel (and other programs). When you start a macro recording, everything you do will be recorded in code for you to review later.

This is handy as you can just record a macro with whatever you want to learn and then view the code afterwards. In this example we’ll record a macro where we format a cell to see what text formatting looks like in VBA.

First off, if you don’t have the Developer tab open in your excel instance, go to the Office button in the top-left corner and select Excel Options. In the options window, under the Popular-tab, check the Show Developer tab in ribbon-checkbox and press ok. You should now have an extra tab visible called Developer. If this isn’t working out you may have to check out your security settings.

In the developer tab you will see a small button called Record macro and that’s what we’re gonna use:

The record macro button
The record macro button

When you press that you are shown a prompt where you’ll add the name of your recording and a potential short cut to it. We won’t bother with the short cut this time but give it a pleasing name:

Name your macro
Name your macro

Now select cell A1 and get back to the Home tab. Add the text “Foo” to the cell and make it bold. Add whatever format you want to it actually, it will all be recorded. I made it bold and italic. I also changed the font size. Afterwards, get back to the Developer-tab and press Stop recording which shows in the same spot as where Start recording was shown before.

To view your recorded macro, enter the Editor (by pressing ALT + F11), my recording looked something like this:

Sub MyFirstMacro()
'
' MyFirstMacro Macro
'
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Foo"
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    With Selection.Font
        .name = "Calibri"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End Sub

This is quite handy as I can now see how to select a cell, how to make it bold etc. Use this technique every time you wonder how something is done in VBA. Remember to just record exactly what you need to know though, as these scripts tend to grow into enormous size quite quickly.

Leave a Reply