Page 202 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

Detecting Empty Cells
An infamous saying among computer programmers is “garbage in, garbage out.” What this
means is that if bad data is fed into a procedure, it will generate a bad answer. To guard
against this, the integrity of the data needs to be verified.
One element you should check before relying on the totals within the Y2001ByMonth.xls
workbook is that all values have been entered. You can do so by searching for empty cells and
prompting the user for a value to be entered into the cell. Searching for empty cells is accom­
plished by using the Range object’s SpecialCells method. The syntax for the SpecialCells
method is

expression.SpecialCells(Type, Value)
Chapter 8
expression is a required range object.
Type is a required value that should match one of the xlCellType constants, listed in Table 8-1.
Value is an optional variant. If Type is xlCellTypeConstants or xlCellTypeFormulas, Value is
used to determine which type of cells to include in the result. The numeric values that corre­
spond to the xlSpecialCellsValues constants, listed in Table 8-2, can be added together to
return more than one type of special cell.
Table 8-2. xlSpecialCellsValue Constants

Constant Description Numeric Value
xlErrors Returns cells that contain errors 16
xlLogical Returns cells that contain a logical (that is, Boolean) 4
value
xlNumbers Returns cells that contain numerical values 1
xlTextValues Returns cells that contain text 2

This FindEmptyCells procedure searches for empty cells in the data areas of Y2001ByMonth.xls
and prompts the user for a value to enter into each empty cell found.

Sub FindEmptyCells()
Dim rgeSearch As Range, rgeEmpties As Range, rgeCurrent As Range
Dim strPrompt As String, strTitle As String, strReturn As String
Dim strRow As String, strAddress As String, strColumn As String
strTitle = "Microsoft Office Excel 2003 Programming Inside Out"

'Select proper range based on days in month
Set rgeSearch = Range("D6", Range("D6").End(xlDown))
Set rgeSearch = Range(rgeSearch, rgeSearch.End(xlToRight))
'Find Empties - End if none found
'Set Error trap for no cells found
On Error Resume Next

176