Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Nov 26, 2023
Solved

Data Types in Excel

Dear Experts,                  Is there some VBA code, which can print all the "Data Types"  in Excel   Data Types **************** Size in bytes **************Range Bytes Integer Signed Intege...
excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Nov 27, 2023

    anupambit1797 

    Maybe you can use the VarType function to check.

    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 VarType(cellValue) = vbBoolean Then
        GetDataType = "Boolean"
    Else
        GetDataType = "Text"
    End If
End Function

     Vba code is untested.

SnowMan55's avatar
SnowMan55
Bronze Contributor
Nov 27, 2023

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

 

Resources