VBA in Excel

Introduction

Since the release of Office 2000, Microsoft has had Visual Basic integrated throughout its Office software suite. This allows you to create Macros in VB, that automate or customize your office applications. The VB used to create macros retains the common VB 5 functions, and has extended keywords, objects etc for interfacing with the application, be it Excel, Word, Outlook or another application. This special version of VB is known as Visual Basic for Applications (VBA).

In this tutorial, I hope to explain how to use VBA in Excel 2000. If you have an earlier version of Excel, this tutorial will still apply, but you will find there are a few discrepancies, mainly the location and interface in which you edit the macros.

Recording Macros

The simplest, and easiest way to write a macro, is to let Excel do it for you! To record a macro, click Tools|Macro|Record New Macro. Enter a name for the macro and description for the macro, and click OK. Excel will now record everything you do in Excel, and convert it into VB code, until you press the Stop button (which will be floating in the top right hand corner of Excel). For this example, once you have started recording, edit the worksheet so it looks like this:

When you have done that, click the stop button, and we'll take a look at what Excel has recorded.

To view and edit VB code in Excel 2000, click Tools|Macro|Visual Basic Editor. You will then see a window very similar to VB, except that they have put the project explorer and properties window on the left instead of the right!

Double click on Module1 in the Project window to see your recorded Macro. It should look something like this:

Sub ExampleMacro()
'
' ExampleMacro Macro
' Macro recorded 18/08/2000 by James Crowley
'
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "Cust ID"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Order ID"
    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"
    Range("B2:G2").Select
    Selection.Font.Bold = True
    With Selection.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
    End With
    Columns("B:G").Select
    Selection.ColumnWidth = 8.9
End Sub

We'll go through this code, and what it does in the next section

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"

Running Macros

After having a quick to see what code Excel recorded, I'll show you how to run the code, and then move on to the basic commands for formatting and adding formulae.

In Excel, you have a number of different ways to run some code. Either, you can press the Run Macro (Play) button in the Visual Basic editor, just like you would in VB. Alternatively, you can go to Tools|Macro|Macros... You can then select the Macro you want to run, and click Run!

To see what our recorded Macro does, add a new sheet to the Excel workbook by clicking Insert | Workbook. Then, run the macro using one of the methods above. You will now see the header we created when recording the Macro mysteriously appear, without you having to type anything! Hopefully now you can get some idea as to how much work Macros can actually save you, especially if you are performing similar operations regularly.

Now that you know how to record a Macro, and how to run it, we can take a look at the different objects and properties that let you apply formatting in Excel.

You might also like...

Comments

About the author

James Crowley

James Crowley United Kingdom

James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audien...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“There are only 3 numbers of interest to a computer scientist: 1, 0 and infinity”