Note: These examples have been tested on Excel 8. They should work also on Excel 5. In Excel 8 objects such as buttons and text boxes can be accessed by View/Toolbars/Forms. In Excel 5 they are under Insert/Macro/Dialog.
In this section we show how to record macros. The next section is Looping Through a Range of Cells.
The macro languages for application packages such as spreadsheets and word processors were originally introduced to save (record) and repeat frequently used sets of keystrokes. Now programmers are likely to write complex routines in macro languages such as Visual Basic for Applications. Recording a macro is a useful starting point for coding, and may save us from having to remember some of the more obscure properties of Excel objects.
For example, the next section makes cells with values greater or equal to 1000 bold. But how to set a cell bold?
Start with a new spreadsheet. Select Tools/Options/Editor and make sure all boxes are checked, in particular Require Variable Declaration.
Select Tools/Macro/Record New Macro. Change the name to mbold, type b into the Ctrl box, and click OK. A Stop button appears.
Select the range A1:C4 with the mouse, then choose Format/Cells/Font and click Bold. Press OK, then press the Stop button.
Type some numbers into the selected cells to check that their format has changed. Now select A1:C4 again and set the font back to regular (you are not recording now). Then select some other cell, and press Ctrl-b. Cell A1;C4 should become bold again.
Now look in Tools/Macro/Macros. mbold (the only macro) will be selected. Click Edit and inspect the code. It should read as follows:
Option Explicit
Sub mbold()
'
' mbold Macro
' Macro recorded 27/05/97 by ismu
'
' Keyboard Shortcut: Ctrl+b
'
Range("A1:C4").Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
Note that what appear to be VBA comments are instructions to the editor.
You can see the hierarchy of objects and properties which have been used:
Range.Font.Fontstyle = "Bold"
(note also that the rest of the Font properties have also been set, although they weren't changed)
We will now adapt this code to our original purpose.
We need to clarify our specification. Do we want the macro to operate on a fixed range of cells, or on the range of cells selected by the user. First let us assume that we want a fixed range, (A1:C4).
We wish to make only those cells bold which have a value >= 1000. This means that we will have to test individual cells and hence will need a loop. (We don't have one yet, because the Font property can refer to a complete Range.) The most common loop when working with spreadsheets loops through the objects in a collection - the For ... Each loop. This gives us the following code (without the extra Font properties):
Option Explicit
Sub mbold()
'
' mbold Macro
' Macro recorded 27/05/97 by ismu
'
' Keyboard Shortcut: Ctrl+b
'
Dim cell As Object
For Each cell In Range("A1:C4")
If cell.Value >= 1000 Then
cell.Font.FontStyle = "Bold"
Else
cell.Font.FontStyle = "Regular"
End If
Next cell
End Sub
If we wish our macro to work on a range of cells selected by the user, we just replace the For ... Each line with
For Each cell in Selection
(note that here cell is a variable, not a key word - though Cells is)
Note also that this macro fails if one of the cells has a non-numeric value
This page is maintained by Jim
Underwood who can be reached at
jim@socs.uts.edu.au.
This page was last updated on July 23rd 1999.
http://www-staff.socs.uts.edu.au/~jim/avb/excel.html 