VBA Data Types

Data Types define what kinds of values can be stored in a variable or returned from a function. Each data type takes up a certain amount of memory, has a certain range of values, and has a default value representing the falsey value for the type. The Variant data type can be used to hold almost any type of data, however it is more efficient, more descriptive, and more secure to assign a specific data type for each variable and function.

*The LongLong and LongPtr data types were added in VBA7.

Data Type Functions

VBA provides functions to determine a variable's data type at runtime.

The TypeName function returns a string describing a variable's type.

The VarType function returns a member of the VbVarType enum.

Option Explicit

Public Sub Example()

    Dim i As Long

    Debug.Print TypeName(i)

    If VarType(i) = vbLong Then
        Debug.Print "i is a Long Integer"
    End If

End Sub

Type Conversion Functions

Type Conversion Functions are used to convert data of one type to another type.

*CLngLng and CLngPtr were added in VBA7.

Public Sub Example()

    'The max value of LongLong is 9,223,372,036,854,775,807
    Dim LL As LongLong
    LL = 2 ^ 64 / 2 - 1

    'The max value of Currency is 922,337,203,685,477.5807
    Dim C As Currency
    C = CCur(LL)

    Debug.Print LL 'Prints 9223372036854775807
    Debug.Print C  'Prints -1

End Sub

Public Sub Example()

    Dim LL As LongLong
    LL = 922337203685477^

    Dim VC As Variant
    VC = 922337203685477.5807@

    'Causes Overflow Error
    Dim B As Boolean
    B = LL < VC

End Sub

Type Identifier Characters

Type Identifier Characters can be used instead of "As Type" to give variables, constants, parameters, and functions their data types. Literal values can also have type identifier characters to determine their data types. There are no type identifier characters for Boolean, Byte, Date, Object, Variant, LongPtr, Arrays, User-Defined Classes, or User-Defined Types. Type identifier characters must be used to represent literals of type Currency, LongLong, and Single. If no type identifier character exists, type conversion functions can be used.

*The LongLong type identifier was added in VBA7. LongLong is only available on 64-bit platforms.

Public Sub Example()

    'Use type identifier to change a literal's type
    'Use conversion functions for types that have no identifier

    Debug.Print TypeName(255)
    Debug.Print TypeName(255&)
    Debug.Print TypeName(CByte(255))

End Sub

Public Function AddTwoNumbers&(Num1&, Num2&)
    
    'Use type identifier instead of "As Type"

    Dim TheSum&
    TheSum = Num1 + Num2

    AddTwoNumbers = TheSum

End Function

Public Sub Example()
  
    'Type identifier must be used for literals of type Currency, LongLong, and Single

    Dim C As Currency
    C = 922,337,203,685,477.5807@

    Dim LL As LongLong
    LL = 9,223,372,036,854,775,807^

    Debug.Print TypeName(1.0) 'Prints Double
    Debug.Print TypeName(1.0!) 'Prints Single

End Sub

Data Types of Literals

Literal values have a default data type. Type identifier characters change the type of a literal value.

Public Sub Example()

    Debug.Print TypeName(32767)
    Debug.Print TypeName(2147483647)
    Debug.Print TypeName(3.5)
    Debug.Print TypeName(2147483648)
    Debug.Print TypeName("Hello, World!")
    Debug.Print TypeName(#01/01/20 00:00:00#)
    Debug.Print TypeName(True)

End Sub

DefType

The DefType statement is used to declare the default data type for variables, arguments, and functions in a given module. The DefType statement is declared at the top of a module along with a letter range which specifies that members that start with those letters have that default data type. To specify all identifiers in the module use the letter range A-Z. When the letter range A-Z is specified, international characters are considered part of that range as well. If no DefType statement is declared, the default data type for all variables, arguments, functions, and Property Get procedures is Variant.

Syntax: DefType letterrange, [ letterrange ] . . .

Option Explicit

DefLng A-Z

Public Function Example()

    Dim i
    Dim j
    Dim k

    Debug.Print TypeName(i)
    Debug.Print TypeName(j)
    Debug.Print TypeName(k)

    Debug.Print TypeName(Example)

End Function