VBA-fnDataTypeDetectionV001

(this code is free to use, tested, works properly) - click here to get the code

Option Explicit
'------------------------------------ _
Software Name: fnDataTypeDetectionV001 _
Description:    - detects the data type _
                - returns the data type as a string or an integer _
License: Freeware _
Creation Date: 7. July 2024 _
Author: Gabriel Kovacs, linkedin.com/in/gabrielkovacsprogrammer _
------------------------------------
Public Function fnDataTypeDetection(varDataItem As Variant) As String 'Or: "As Integer"
    On Error GoTo ErrorHandler
    Dim strTypeName As String
    'Dim intTypeCode As Integer
    
    'determine the type of data in varDataItem using VarType function
    Select Case VarType(varDataItem)
        Case vbEmpty
            'vbEmpty (0): Represents an uninitialized state or an empty cell
            strTypeName = "Empty"
            'intTypeCode = 0
        Case vbNull
            'vbNull (1): Represents data with a Null value, _
                typically not used directly in cells without specific database interactions _
                or advanced VBA manipulations.
            strTypeName = "Null"
            'intTypeCode = 1
        Case vbInteger
            'vbInteger (2) and vbLong (3): While Excel itself treats all numbers _
            as double-precision floating-point numbers (Double), _
            these types might be used in VBA but are not native to cell content
            strTypeName = "Integer"
            'intTypeCode = 2
        Case vbLong
            'vbInteger (2) and vbLong (3): While Excel itself treats all numbers _
            as double-precision floating-point numbers (Double), _
            these types might be used in VBA but are not native to cell content
            strTypeName = "Long"
            'intTypeCode = 3
        Case vbSingle
            'vbSingle (4) and vbDouble (5): Excel stores numbers as Double, _
            so vbSingle wouldn't natively be a cell content type.
            strTypeName = "Single"
            'intTypeCode = 4
        Case vbDouble
            'vbDouble (5): Excel stores numbers as Double (native cell content type)
            strTypeName = "Double"
            'intTypeCode = 5
        Case vbCurrency
            'vbCurrency (6): Can be used in cells, especially formatted for monetary values _
            (excel stores vbCurrency value in cell as vbDouble but when reading from cell _
            excel converts vbDouble to vbCurrency).
            strTypeName = "Currency"
            'intTypeCode = 6
        Case vbDate
            'vbDate (7): Common in cells for date values.
            strTypeName = "Date"
            'intTypeCode = 7
        Case vbString
            'vbString (8): Standard text data in cells.
            strTypeName = "Text"
            'intTypeCode = 8
        Case vbObject
            'vbObject (9): Objects are not a data type that can be stored directly in a cell. _
            Objects like Range or Worksheet are part of Excel VBA programming.
            strTypeName = "Object"
            'intTypeCode = 9
        Case vbError
            'vbError (10): Common in cells that contain errors from formulas.
            strTypeName = "Error"
            'intTypeCode = 10
        Case vbBoolean
            'vbBoolean (11): Can appear in cells, usually as results of logical formulas. _
            In cell they appear as text but even they(true,false) are text, they are treated as logical values.
            strTypeName = "Boolean"
            'intTypeCode = 11
    '---------------------
    'vbVariant (12): Variants themselves aren't stored in cells; _
     rather, a cell's value can be accessed as a Variant in VBA.
    '---------------------
    'vbDataObject (13): Data objects pertain to data transfer operations _
     and cannot be stored directly in a cell.
    '---------------------
        Case vbDecimal
            'vbDecimal (14): can be used only in VBA for very high precision calculations. _
             If vbDecimal is stored in a cell it is converted to vbDouble with precision _
             reduced to precision of vbDouble.
            strTypeName = "Decimal"
            'intTypeCode = 14
        Case vbByte
            'vbByte (17): can be used only in VBA to reduce memory usage. _
            If vbByte is stored in a cell it is converted to vbDouble. _
            When retrieved from a cell function DByte() must by applied.
            strTypeName = "Byte"
            'intTypeCode = 17
        Case vbUserDefinedType
            'vbUserDefinedType (36): UDTs or structured types are only for use in VBA and _
            cannot be stored directly in a cell.
            strTypeName = "UserDefinedType"
            'intTypeCode = 36
        Case vbArray
            'vbArray (8192): Only used in VBA to hold items, values even cell values. _
            But an Excel cell itself cannot directly contain an array.
            strTypeName = "Array"
            'intTypeCode = 8192
        Case Else
            'Common Conventions: In many systems, negative numbers or specific flags _
            are used to indicate error states or undefined conditions. _
            For instance, -1 is a commonly used value for indicating _
            errors or undefined conditions in many programming environments.
            strTypeName = "Unknown"
            'intTypeCode = -1
    End Select
    
    fnDataTypeDetection = strTypeName
    'fnDataTypeDetection = intTypeCode
    Exit Function
ErrorHandler:
    fnDataTypeDetection = "Error in detection"
    MsgBox "Error occured:" & vbCrLf _
        & "No. " & Err.Number & ", " & Err.Description, vbCritical, "Data Type Detection Error"
    On Error GoTo 0
    Err.Clear
End Function

Sub test()
    Dim varVariable As Variant
'    varVariable = "hello"
'    varVariable = ""
'    varVariable = 1
    varVariable = 1.1
    Debug.Print "Variable type is: " & fnDataTypeDetection(varVariable)
End Sub