Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
Case IsDate(TheCell)
CellType = “Date”
Case InStr(1, TheCell.Text, “:”) <> 0
CellType = “Time”
Case IsNumeric(TheCell)
CellType = “Number”
End Select
End Function
Notice the use of the Set TheCell statement. The CellType function accepts a range
argument of any size, but this statement causes it to operate on only the upper-left cell in the range
(which is represented by the TheCell variable).
A workbook that contains this function is available on the companion CD-ROM. The file
is named celltype function.xlsm .
Reading and writing ranges
Many VBA tasks involve transferring values either from an array to a range or from a range to an
array. Excel reads from ranges much faster than it writes to ranges because the latter operation
involves the calculation engine. The WriteReadRange procedure that follows demonstrates the
relative speeds of writing and reading a range.
This procedure creates an array and then uses For-Next loops to write the array to a range and
then read the range back into the array. It calculates the time required for each operation by
using the Excel Timer function.
Sub WriteReadRange()
Dim MyArray()
Dim Time1 As Double
Dim NumElements As Long, i As Long
Dim WriteTime As String, ReadTime As String
Dim Msg As String
NumElements = 60000
ReDim MyArray(1 To NumElements)
‘ Fill the array
For i = 1 To NumElements
MyArray(i) = i
Next i
‘ Write the array to a range
Time1 = Timer
For i = 1 To NumElements
Cells(i, 1) = MyArray(i)
Next i
Search JabSto ::

Custom Search