Data Types in Excel

anupambit1797 

The phrase "in Excel" in your post might be ambiguous.  In a worksheet, cells can contain a value that is:

• nothing (Variant of subtype Empty)
• a Boolean
• a Currency amount
• a Date (internally, a double-precision floating point number, even though only a subrange is valid)
• a double-precision floating point number (this covers integers, fractions, real numbers, time)
• a string (i.e., characters; text)
• an error (Variant of subtype Error)

(That list might be incomplete.)

But VBA can work with variables of type:

• Boolean [2-byte?]
• Byte [1-byte, unsigned]
• Currency [8-byte integer scaled by 10,000]
• Date [same as Double; always includes time-of-day]
• Decimal *
• Double-precision floating point [8-byte]
• Empty *
• Error *
• Integer [2-byte, signed]
• Long [4-byte, signed]
• LongLong [8-byte, signed]
• LongPtr [8-byte]
• Null * [This is neither a null character nor a database null value.]
• Object  [4-byte; generic, or a specific name for a specific object class]
• Single-precision floating point [4-byte]
• String [usually, 6 10 bytes + 2 bytes per character]
• User-defined type
• Variant, which can contain any of the above [loosely speaking], or an array of any of the above

(Except for LongPtr, that list is from the VbVarType enumeration, exposed in the Object Browser.)
VBA does not use unsigned numeric data types, except for Byte.  Those marked with an asterisk are available only through the Variant type, which requires at least 8 bytes of memory.  More information is available at VBA - Data Types and Limits.  The memory required for an Object variable might depend upon the operating system (32-bit or 64-bit).

I do not know of any technique for automatically producing a list of either.

But what is the purpose of this list?  Why do you want it to be generated by (VBA) code?

Edit: Microsoft has changed some VBA memory storage; see Data type summary

anupambit1797 

Excel does not directly expose a VBA object or property that provides information about the data types of cells in a worksheet, as far as I know. However, you can use VBA to loop through the cells in a range and infer their data types based on their values.

Below is a simple VBA code that you can use to print the data types of cells in a specified range:

Vba code (is untested):

Sub PrintDataTypes()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    ' Set the worksheet and range
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    Set rng = ws.UsedRange ' Change to your desired range
    
    ' Loop through each cell in the range
    For Each cell In rng
        Debug.Print "Cell (" & cell.Address & ") Data Type: " & GetDataType(cell.Value)
    Next cell
End Sub

Function GetDataType(cellValue As Variant) As String
    ' Function to determine the data type of a cell value
    If IsEmpty(cellValue) Then
        GetDataType = "Empty"
    ElseIf IsNumeric(cellValue) Then
        GetDataType = "Number"
    ElseIf IsDate(cellValue) Then
        GetDataType = "Date"
    ElseIf IsBoolean(cellValue) Then
        GetDataType = "Boolean"
    Else
        GetDataType = "Text"
    End If
End Function

This code defines a subroutine (PrintDataTypes) that loops through each cell in the specified range, prints the cell address, and calls a helper function (GetDataType) to determine the data type of the cell value.

Note: This approach provides a basic categorization of data types. It may not cover all edge cases, and it treats anything that is not numeric, date, boolean, or empty as text.

To run the code, press Alt + F8, select PrintDataTypes, and click Run.

Keep in mind that if your worksheet has a large number of cells, this operation might take some time to complete. Also, remember to replace "Sheet1" with the actual name of your worksheet and adjust the range accordingly. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.