Microsoft Office Tutorials and References
In Depth Information
Working with Ranges
The
InRange
function may appear a bit more complex than it needs to be because the code
needs to ensure that the two ranges are in the same worksheet and workbook. Notice that the
procedure uses the
Parent
property, which returns an object’s container object. For example,
the following expression returns the name of the worksheet for the
rng1
object reference:
rng1.Parent.Name
The following expression returns the name of the workbook for
rng1
:
rng1.Parent.Parent.Name
VBA’s
Union
function returns a
Range
object that represents the union of two
Range
objects.
The union consists of all the cells from both ranges. If the address of the union of the two ranges
is the same as the address of the second range, the first range is contained within the second
range.
A workbook that contains this function is available on the companion CD-ROM in a file
named
inrange function.xlsm
.
Determining a cell’s data type
Excel provides a number of built-in functions that can help determine the type of data contained
in a cell. These include ISTEXT, ISLOGICAL, and ISERROR. In addition, VBA includes functions
such as
IsEmpty
,
IsDate
, and
IsNumeric
.
The following function, named
CellType
, accepts a range argument and returns a string
(
Blank
,
Text
,
Logical
,
Error
,
Date
,
Time
, or
Number
) that describes the data type of the
upper-left cell in the range. You can use this function in a worksheet formula or from another
VBA procedure.
Function CellType(Rng) As String
‘ Returns the cell type of the upper left
‘ cell in a range
Dim TheCell As Range
Set TheCell = Rng.Range(“A1”)
Select Case True
Case IsEmpty(TheCell)
CellType = “Blank”
Case Application.IsText(TheCell)
CellType = “Text”
Case Application.IsLogical(TheCell)
CellType = “Logical”
Case Application.IsErr(TheCell)
CellType = “Error”
