Determining a Cell s Data Type

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 accepts a range argument and returns a string (Blank, Text, Logical, Error, Date, Time, or Value) that describes the data type of the upper-left cell in the range:

 Function CELLTYPE(cell As Range) As String '   Returns the cell type of the upper-left '   cell in a range     Dim UpperLeft As Range     Application.Volatile True     Set UpperLeft = cell.Range("A1")     Select Case True         Case UpperLeft.NumberFormat = "@"             CELLTYPE = "Text"         Case IsEmpty(UpperLeft.Value)             CELLTYPE = "Blank"         Case WorksheetFunction.IsText(UpperLeft.Value)             CELLTYPE = "Text"         Case WorksheetFunction.IsLogical(UpperLeft.Value)             CELLTYPE = "Logical"         Case WorksheetFunction.IsErr(UpperLeft.Value)             CELLTYPE = "Error"         Case IsDate(UpperLeft.Value)             CELLTYPE = "Date"         Case InStr(1, UpperLeft.Text, ":") <> 0             CELLTYPE = "Time"         Case IsNumeric(UpperLeft.Value)             CELLTYPE = "Value"     End Select End Function 

Figure 25-1 shows the CELLTYPE function in use. Column B contains formulas that use the CELLTYPE function with an argument from column A. For example, cell B1 contains the following formula:

 =CELLTYPE(A1) 

Figure 25-1: The CELLTYPE function returns a string that describes the contents of a cell.

On the CD 

The workbook  celltype function.xlsm that demonstrates the CELLTYPE function is available on the companion CD-ROM.