VBA & Macro Problems

Excel VBA: Identify Cell Data Types in a Worksheet

Algirdas JasaitisAlgirdas Jasaitis Nov 12, 2026 868 views

Question details

The user needs to inspect worksheet cells with VBA and determine the underlying type of each cell value.

Excel VBA: Identify Cell Data Types in a Worksheet
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A macro should loop through a selected range or worksheet area, classify each cell value, and report the cell address and detected type.
Observed behavior
Excel does not provide a single object or property that returns a complete worksheet cell-type list, so cell values must be inspected and classified individually.
Before you start

Open the workbook in a macro-enabled format such as XLSM and confirm that macros are enabled. Decide whether the macro should inspect a selected range or the sheet's UsedRange before running it.

Solution 1Recommended

Use VBA VarType and value checks to classify worksheet cells

Use this approach when you need a detailed report of the underlying value type for every cell in a target range.

Worksheet cell types and VBA variable types are related but not identical. A cell can contain Empty, Boolean, Date, numeric, text, or error data, while its displayed number format does not necessarily reveal the underlying value type.

The macro below prints each cell address and a type label to the Immediate window. It checks the Variant subtype first so numeric text, dates, errors, and Boolean values are not incorrectly grouped together.

1
Open the VBA editor

In Excel, press Alt+F11. In the Visual Basic Editor, choose Insert > Module to create a standard module.

2
Add the cell-type inspection macro

Paste this code into the module: Sub PrintCellDataTypes() Dim ws As Worksheet Dim rng As Range Dim cell As Range Set ws = ThisWorkbook.Worksheets("Sheet1") Set rng = ws.UsedRange For Each cell In rng.Cells Debug.Print cell.Address(False, False) & " - " & GetCellDataType(cell.Value) Next cell End Sub Function GetCellDataType(ByVal v As Variant) As String Select Case VarType(v) Case vbEmpty GetCellDataType = "Empty" Case vbBoolean GetCellDataType = "Boolean" Case vbByte GetCellDataType = "Byte" Case vbInteger GetCellDataType = "Integer" Case vbLong GetCellDataType = "Long" Case vbLongLong GetCellDataType = "LongLong" Case vbSingle GetCellDataType = "Single" Case vbDouble GetCellDataType = "Double" Case vbCurrency GetCellDataType = "Currency" Case vbDate GetCellDataType = "Date" Case vbString GetCellDataType = "String" Case vbError GetCellDataType = "Error" Case vbNull GetCellDataType = "Null" Case Else GetCellDataType = "Other, VarType=" & VarType(v) End Select End Function

3
Set the range to inspect

Replace Worksheets("Sheet1") with the actual worksheet name. To inspect a specific range instead of the used area, replace Set rng = ws.UsedRange with a range such as Set rng = ws.Range("A1:D20").

4
Run the macro and view the results

Place the cursor inside PrintCellDataTypes and press F5, or return to Excel and choose Developer > Macros > PrintCellDataTypes > Run. In the VBA editor, select View > Immediate Window or press Ctrl+G to see the cell addresses and type labels.

Displayed formatting is different from the value type: A date or currency format can make a numeric value look different without changing its underlying VBA subtype. Use NumberFormat separately when you need to inspect how a value is displayed.

Inspect worksheet cell types with WPS Spreadsheets VBA

WPS Spreadsheets can help you review macro-enabled worksheets and use VBA-compatible automation where VBA support is available in your WPS installation. Open the workbook, inspect the target range, and use the macro editor to classify values such as blanks, dates, numbers, text, Boolean values, and errors.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the XLSX or XLSM workbook containing the cells you want to inspect.
  2. 2. Open the macro editor: Use the Tools or Developer menu to open the VBA or macro editor, depending on your WPS version and available macro support.
  3. 3. Create a standard module: In the editor, choose Insert > Module and paste the cell-inspection procedure and type-classification function.
  4. 4. Choose the worksheet range: Update the worksheet name and range in the code, such as UsedRange or Range("A1:D20"), to target the cells that need classification.
  5. 5. Run the macro and review the output: Run the procedure from the macro dialog or editor, then open the Immediate window or output panel to review each cell address and detected type.
Classify worksheet values by practical VBA data type categoriesReview and work with macro-enabled XLSM workbooksUse a familiar spreadsheet interface for range and cell inspectionMaintain compatibility with common Microsoft Excel XLSX and XLSM formats
microsoft office alternative - wps office

Frequently Asked Questions

Can VBA return every worksheet cell type with one Excel property?

No. Excel does not expose one property that lists all worksheet cell types. Loop through the target range and use VarType, IsEmpty, IsError, IsDate, and related checks to classify each value.

Why does a date sometimes appear as a number in VBA?

Excel stores dates as serial numbers. Use cell.Value rather than only Value2 when date detection matters, and check VarType or IsDate before labeling the value. The cell's NumberFormat controls how that number is displayed.

How can I inspect only a selected range instead of the entire worksheet?

Replace Set rng = ws.UsedRange with a specific range such as Set rng = ws.Range("B2:F50"), or assign the current selection with Set rng = Selection after confirming that a worksheet range is selected.

Where can I see the output from Debug.Print?

In the VBA editor, choose View > Immediate Window or press Ctrl+G. The macro writes each cell address and its detected type there.

How should VBA classify cells containing formulas?

VarType(cell.Value) classifies the result returned by the formula, not the formula expression itself. Use cell.HasFormula to separately determine whether the cell contains a formula, then inspect cell.Value for the resulting data type.