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