Page 213 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Ranges and Cells

Next intLoop
sglEnd = Timer
Debug.Print sglEnd
strPrompt = "Processing time range method: " & _
Format(sglEnd - sglStart, "#0.00000") & _
" Total Cells:" & Str$(intCount) & vbCrLf

sglStart = Timer
intCount = 0
For intLoop = 1 To 50
varCells = Range("JanuaryAllValues")
For intRows = 1 To UBound(varCells)
For intColumns = 1 To UBound(varCells, 2) Chapter 8
intCount = intCount + 1
Next intColumns
Next intRows
Next intLoop
sglEnd = Timer
strPrompt = strPrompt & "Processing time array method: " & _
Format(sglEnd - sglStart, "#0.00000") & _
" Total Cells:" & Str$(intCount)
MsgBox strPrompt, vbOKOnly + vbInformation, strTitle

End Sub

Getting Data Entry Right the First Time

Typing data into a worksheet or form is one of the most tedious activities known to human-
ity. If you’ve ever typed ISBNs into a database eight hours a day and five days a week for two
months, you can begin to get an appreciation for what data entry clerks go through. Columns
of numbers transpose in front of your eyes, books you’ve already entered find their way onto
your “to do” pile, and you change hands to avoid the worst effects of repetitive stress disor-
ders. Add in the potential for typographical errors, and you can understand why electronic
data collections are so notoriously inaccurate.
You can help catch data entry errors at the source by setting validation rules for cells in your
worksheets. As the name implies, a validation rule is a criterion that cell data must meet in
order to ensure that only meaningful information is added to your data collection. You get to
choose whether the invalid data is accepted as input or whether the user has to re-type the
s
l
l
l
l
data before being alowed to go on to the next cel. You can aso specify whether to alow uers
entering data to leave cells blank.
The key to creating validation rules for your cells is, as you probably guessed, the Range
object’s Validation property. The Validation property, which returns a Validation object, gives
you the tools to set the validation rules and notification styles for your cell ranges. Table 8-4
lists the Validation object’s useful properties and methods.

187