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
    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.

Your first VBA module

When using VBA extensivly it is a good idea to know how to use VBA. VBA means Visual Basic for Application and can be used to automate functions that are hard to achieve using simple functions. VBA uses the same syntax as Visual Basic and is quite simple to use.
To write a VBA application you need to open the Visual Basic Editor (VBE) which you do by pressing ALT + F11. Choose Insert => Module in the menu to create a new module.
Enter the following code:

Sub Test()
    'Select cell C1
    'Write "Foo" in the selected cell
    Selection.Value = "Foo"
End Sub

If you press run (either F5 or press the green triangle icon in the icon tray) you’ll see how the module has written Foo in cell C1. So, you ask, do you have to run modules from inside the VBE? Of course not, that would be dull. If you get back to your excel sheet there are differences to running VBA script depending on which Office pack you are using. I’m using Office 2007 so I will show you how to do it in Office 2007, however, if you are running 2003 or perhaps an even newer version then it shouldn’t be too different.

In Office 2007 press the big office button in the top left corner (where you go if you want to save or open a file) and choose Excel Options in the bottom right of the menu. In the options Popular tab check the “Show Developer tab in the Ribbon“. You might have to change your security settings in the Trust Center to get it to work.

This has now opened the Developer tab and if you go to it you should be able to select your macro by pressing the Macro-icon:

The Macro menu
The Macro menu

If you press Run the script will execute and you’ll see the effect in cell C3. You have now written your first VBA script that does something!