Determining a cells 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)

' 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" 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). CD-ROM

A workbook that contains this function is available on the companion CD-ROM. The file is named %

celltype function.xlsm .

0 0

Post a comment