Get the Cell Format in VBA

Sometimes we may want to check for the format of a cell in Excel VBA. This tutorial shows you how to get the format of a cell in Excel VBA.

How to Get the Format of a Cell in Excel VBA

We use the following dataset in our illustration:

Table

Description automatically generated

We use the following steps:

Press Alt + F11 in the active worksheet that contains the dataset to open the Visual Basic Editor.
Click Insert >> Module to insert a new module.
Type the following procedure in the module.

Sub getCellFormat()
    Dim Msg As String
    Select Case IsEmpty(ActiveCell)
        Case True
            Msg = "General."
        Case Else
            Select Case IsNumeric(ActiveCell)
                Case True
                    Msg = "Number."
                Case Else
                    Select Case IsDate(ActiveCell)
                        Case True
                            Msg = "Date."
                        Case Else
                            Msg = "Text."
                    End Select
            End Select
    End Select

Sub getCellFormat()

Dim Msg As String

Select Case IsEmpty(ActiveCell)

Case True

Msg = "General."

Case Else

Select Case IsNumeric(ActiveCell)

Case True

Msg = "Number."

Case Else

Select Case IsDate(ActiveCell)

Case True

Msg = "Date."

Case Else

Msg = "Text."

End Select

Save the procedure and save the workbook as a macro-enabled workbook.
Press Alt + F11 to switch back to the active worksheet and select cell A2.
Press Alt + F11 again to switch back to the Visual Basic Editor.
Place the cursor anywhere in the procedure and press F5 to run the code.

A message box pops up displaying the message that cell A2 has a Date format.

Click the OK button on the message box. This takes you back to the Visual Basic Editor.
Repeat steps 5,6 and 7 for cell A3, cell A4, and cell A5. Message boxes pop up displaying appropriate messages for cell A3 and cell A4:

Cell A3 has a Number format:

Graphical user interface, application, table

Description automatically generated

Cell A4 has a Text format:

Cell A4 has a General format:

Graphical user interface, application, table, Excel

Description automatically generated

Explanation of the procedure

The Select Case statement displays a message box depending on the format of the active cell.
The IsEmpty function checks whether the active cell is empty. If it is empty the message box shows that it has the General format. The General format is the default format of empty cells. It means no specified format.
The IsNumeric function checks whether the active cell contains a number value. If it has a numeric value, the message box shows that it has a Number format.
The IsDate function checks whether the active cell contains a Date value. If it has a date value, the message box shows that it has a Date format.
If the active cell is not formatted as Date, Number, or General, the Text format is assumed.

Conclusion

Sometimes we may want to check for the format of a cell in Excel VBA. This tutorial explained how you can get the format of a cell in Excel VBA.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 741