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”
 
Search JabSto ::




Custom Search