Page 203 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Set rgeEmpties = rgeSearch.SpecialCells(xlCellTypeBlanks)
If Err.Number = 1004 Then
strPrompt = "No empty cells found!"
MsgBox strPrompt, vbOKOnly + vbInformation, strTitle
Exit Sub
Else
strPrompt = "Unexpected error - " & Str$(Err.Number) & _
vbCrLf & Err.Description
MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle
End If
Chapter 8
'Reset error handler
On Error GoTo 0
'Loop through empties prompting for new values
For Each rgeCurrent In rgeEmpties
'Calculate time period
strAddress = "R5C" & Format(rgeCurrent.Column, "#0")
strAddress = Application.ConvertFormula(strAddress, xlR1C1, xlA1)
strPrompt = "Value missing for " & Format(Range(strAddress), _
"h:mm AM/PM")

'Calculate day
strAddress = "R" & Format(rgeCurrent.Row, "#0") & "C2"
strAddress = Application.ConvertFormula(strAddress, xlR1C1, xlA1)
strPrompt = strPrompt & " on " & ActiveSheet.Name & " " & _
Range(strAddress)
strReturn = InputBox(strPrompt, strTitle)
rgeCurrent = CSng(strReturn)
Next rgeCurrent
End Sub

Using Named Ranges

You might already know that you can select a range of cells on a worksheet and give it a name
by typing it in the Name Box. This lets you quickly select the range by choosing its name from
the drop-down list provided in the Name Box. These same names are available to you within
a macro. Instead of providing the cell coordinates for a range, you can use the name of the
range instead.

Defining a Named Range

Excel stores the names of defined ranges within the Names collection, which is a property of
the Workbook object. Using the Add method, you can create a Named range within the work-
book by specifying the name you want to use and the range it should point to.
If you wanted to do some analysis of the sales data for each hourly time period within the
Y2001ByQuarter.xls workbook, you could make your formulas easier to read by defining
each time period as a range. Rather than manually selecting the range and typing a name, you

177