VBA User-Defined Types

User-defined types are composite data types containing one or more variables of other data types. User-defined types can be used to return multiple values from a function, pass a struct to a DLL procedure, improve efficiency when used in place of a class, and to make code more organized and readable.

User-Defined Types Vs Classes

Although user-defined types can sometimes take the place of using a class, they have several key differences from classes:

Classes can define methods, user-defined types cannot
Classes can be instantiated, user-defined types cannot
Classes require their own dedicated modules, user-defined types do not
Class instances can be added to a Collection object, user-defined types cannot
User-defined types are more efficient than classes

Although there are some limitations with user-defined types when compared to classes, user-defined types can be a more efficient and light-weight alternative when all that is needed is a structure containing data fields. User-defined types do not have the overhead of a dedicated module and can help keep the size of a VBA project small when defining a number of data structures. Using a user-defined type with an array is more efficient than using a user-defined class with a Collection object. If a data structure does not need methods or instances then one should consider using a user-defined type instead of a class.

Declaring User-Defined Types

User-defined types are declared using the Type statement. There are a number of rules when declaring user-defined types:

Must be declared above all procedures in a module.
Must have at least one member.
When Public or Private is not specified, user-defined types are Public by default.
Public types cannot be declared in class modules.
Private types in a class module cannot be used as parameters for public procedures.
Private types in a class cannot be return types for public procedures of the class.
Private types in class modules cannot be public member variables of the class.
Types cannot contain a member that is declared as its own type. No circular dependencies.
When a type contains another type as a member, the member's type must be declared first.

'Standard Module

Option Explicit

Public Type TExample1
    Message As String
    Number  As Double
End Type

Public Type TExample2
    Message As String
    T1      As TExample1
End Type

Public Sub Example()

    Dim T1 As TExample1
    T1.Message = "MT1 Message"
    T1.Number = 3.5

    Dim T2 As TExample2
    T2.Message = "T2 Message"
    T2.T1 = T1

    Debug.Print T2.Message, T2.T1.Message, T2.T1.Number
    
End Sub

In a standard code module, Private user-defined types can be accessed publicly as a member variable of a Public user-defined type.

'Standard Module: Module1

Option Explicit

Private Type TPrivate
    Message  As String
    Number   As Double
End Type

Public Type TPublic
    Message  As String
    Priv     As TPrivate
End Type

'Standard Module: Module2

Option Explicit

Public Sub Example()

    Dim T As TPublic

    T.Priv.Message = "Hello, World!"
    T.Priv.Number = 3.5

    Debug.Print T.Priv.Message
    Debug.Print T.Priv.Number

End Sub

Multiple Return Values

There are times when it may be necessary for a function to return more than one value. A user-defined type can be used to return multiple values from a function.

Option Explicit

Public Type TExample
    Message As String
    Number  As Double
End Type

Public Function GetData() As TExample
    GetData.Message = "Hello, World!"
    GetData.Number = 3.5
End Function

Public Sub Example()

    Dim E As TExample
    E = GetData()

    Debug.Print E.Message
    Debug.Print E.Number

End Sub

User-Defined Type Parameter

User-defined types can be used as parameters of procedures.

Option Explicit

Public Type TExample
    Message As String
    Number  As Double
    Bool    As Boolean
End Type

Public Sub PrintTExample(T As TExample)
    Debug.Print T.Message, T.Number, T.Bool
End Sub

Public Sub Example()

    Dim T As TExample

    T.Message = "Hello, World!"
    T.Number = 3.5
    T.Bool = True

    PrintTExample T

End Sub

Pass Struct To DLL Procedure

User-defined types can be used to pass or return structs to and from DLL procedures.

// C++ 64-bit DLL

#define DLLExport __declspec(dllexport)

struct RECTANGLE {
    int s1;
    int s2;
};

extern "C" {

    DLLExport int __stdcall CppRectangleArea(RECTANGLE* rect) {
        return rect->s1 * rect->s2;
    }

}

Option Explicit

Public Type TRectangle
    S1 As Long
    S2 As Long
End Type

Public Declare PtrSafe Function CppRectangleArea _
    Lib "C:\ExampleDLL.dll" (ByRef Rect As TRectangle) As Long

Public Sub Example()

    Dim Rect As TRectangle
    Rect.S1 = 10
    Rect.S2 = 20

    Dim Area As Long
    Area = CppRectangleArea(Rect)

    Debug.Print Area

End Sub

Arrays and User-Defined Types

Using an array with a user-defined type is an excellent way of storing tabular data in a type-safe, meaningful, and memory efficient way. Using a user-defined type array instead of a multi-dimensional array of type Variant provides the correct explicit data type for each field, a clear meaning for what each field represents, and saves memory when compared to using Variants. Consider the example below using a multi-dimensional array of type Variant and then again using an array with a user-defined type.

Note: Arrays of user-defined types cannot be iterated over using a For Each loop.

Option Explicit

Public Sub Example()

    Dim PersonInfoArray(0 To 2, 0 To 2) As Variant

    PersonInfoArray(0, 0) = "Bill"
    PersonInfoArray(0, 1) = #01/01/1992#
    PersonInfoArray(0, 2) = "New York"

    PersonInfoArray(1, 0) = "Bob"
    PersonInfoArray(1, 1) = #01/01/1993#
    PersonInfoArray(1, 2) = "Chicago"

    PersonInfoArray(2, 0) = "Ben"
    PersonInfoArray(2, 1) = #01/01/1994#
    PersonInfoArray(2, 2) = "Los Angeles"

    Dim i As Long
    For i = LBound(PersonInfoArray, 1) To UBound(PersonInfoArray, 1)
        Debug.Print PersonInfoArray(i, 0), PersonInfoArray(i, 1), PersonInfoArray(i, 2)
    Next i

End Sub

Option Explicit

Public Type TPersonInfo
    FirstName As String
    BirthDate As Date
    HomeTown  As String
End Type

Public Sub Example()

    Dim PersonInfoArray(0 To 2) As TPersonInfo

    With PersonInfoArray(0)
        .FirstName = "Bill"
        .BirthDate = #01/01/1992#
        .HomeTown = "New York"
    End With

    With PersonInfoArray(1)
        .FirstName = "Bob"
        .BirthDate = #01/01/1993#
        .HomeTown = "Chicago"
    End With

    With PersonInfoArray(2)
        .FirstName = "Ben"
        .BirthDate = #01/01/1994#
        .HomeTown = "Los Angeles"
    End With

    Dim i As Long
    For i = LBound(PersonInfoArray) To UBound(PersonInfoArray)
        With PersonInfoArray(i)
            Debug.Print .FirstName, .BirthDate, .HomeTown
        End With
    Next i

End Sub

LSet

LSet can be used with User-Defined Types to assign the values in one type with the values of another type.

Note: It is not recommended to use LSet with user-defined types because there is no guarantee that the members of both types are the same.

Option Explicit

Public Type TExample1
    Message As String * 15
    Number  As Double
End Type

Public Type TExample2
    Message As String * 15
    Number  As Double
End Type

Public Sub Example()

    Dim T1 As TExample1
    T1.Message = "Hello, World!"
    T1.Number = 3.5

    Dim T2 As TExample2
    LSet T2 = T1

    Debug.Print T2.Message
    Debug.Print T2.Number

End Sub

Option Explicit

'''VBA does not ensure that user-defined types match

Public Type TExample1
    Message As String * 15
    Number  As Double
End Type

Public Type TExample2
    Number  As Long
    Message As String * 15
End Type

Public Sub Example()

    Dim T1 As TExample1
    T1.Message = "Hello, World!"
    T1.Number = 3.5
    
    Dim T2 As TExample2
    LSet T2 = T1
    
    Debug.Print T2.Number  'Prints: '6619208
    Debug.Print T2.Message 'Prints: 'llo, World!

End Sub