Editing Macros

As we saw in the last section, you don't actually need to know any VB to create a Macro in Excel! However, even if you know VB, it can be very useful to record macros just so you can get to grips with the commands needed to perform an operation (such as setting the background colour of a cell). This means that if you don't know how to do something, simply record the action, and see what code Excel generates for you.

As VBA is so similar to VB, I am not going to cover standard VB syntax, procedures etc. In fact, all you really need to know is:

1) You can put code in a module. Any public procedures can be called from any sheet in the workbook (ie ExampleMacro)
2) You can put code in a worksheet. Any public procedures can be access from another sheet, so long as you reference it (ie Sheet1.ExampleMacro). Any private procedures can only be accessed from that worksheet
3) Any procedure (Sub) or function is a Macro.

Now you know that, we can take a look at the macro that Excel recorded for us, and see what it does. Then, in the next few sections, I will show you the main commands for selecting, formatting, inserting formulae etc.

So that you can see exactly what I mean, I have simply added comments explaining what each line of code does above it:

'Standard Procedure/Macro called ExampleMacro
Sub ExampleMacro()
    'Select B2
    Range("B2").Select
    'Set the active cells' value to 'Cust ID'
    ActiveCell.FormulaR1C1 = "Cust ID"
    'Select C2
    Range("C2").Select
    'Set the active cells' value to 'Order ID'
    ActiveCell.FormulaR1C1 = "Order ID"
    'you get the idea....
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "ItemID"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Quantity"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Unit Price"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "Total"
    'Select cells B2 across to G2
    Range("B2:G2").Select
    'Make the selected cells bold
    Selection.Font.Bold = True
    'Set the background colour of the selected cells
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    'Select columns B to G
    Columns("B:G").Select
    'Set the selected columns' column width to 8.9
    Selection.ColumnWidth = 8.9
End Sub

As you can see, you can select a group of cells using the Range object. Excel then calls the ActiveCell object, and sets its Formula/Value to something (ie ActiveCell.FormulaR1C1 = "ItemID"). You could also use ActiveCell.Value = "ItemID".

When there is more than one cell selected, and you want to apply formatting or text to the whole lot, you use the Selection object instead. It is essentially the same as the ActiveCell object, and has the same methods/properties etc, except that it refers to more than one cell!

In this code Excel recorded, it seems to be doing a lot of selecting, and then applying formatting to the current cell. This is how Excel works when recording a Macro, but you can simply refer to it directly, without changing the selection. For example, instead of using

Range("D2").Select
ActiveCell.FormulaR1C1 = "ItemID"

you could simply use

Range("D2").FormulaR1C1 = "ItemID"