In VBA how do you compare 2 instances of a User Defined Type (UDT)? I'm getting a Type mismatch

Question

I have a module that defines a UDT as follows:

Private Type UserData
    uName As String         'user name
    uDate As Date           'date/time of last interaction
End Type

I have a simple test function that I'm trying to use to compare two different instances of the UDT as follows:

Sub TestCheck()
    Dim testRec(1) As UserData
    testRec(0).uName = "a"
    testRec(0).uDate = Date
    testRec(1) = testRec(0)
    
    If testRec(1) = testRec(0) Then
        Debug.Print "Records match"
    Else
        Debug.Print "Records don't match"
    End If
End Sub

I get Compile error: Type mismatch on testRec(1) = testRec(0)

I really would rather not have to loop through each member of each instance in order to check for equivalency. Aren't UDTs supposed to act as variables? If I have to loop through each member of each instance to compare them, then it really doesn't save anything for me to use the UDTs. Is there a way to do the comparison without looping through the members?

Answer 1

For anyone who has the same question, based on Brian M Stafford's comments, the simple answer is no.
However, here's a simple function to get the job done:

Private Function UserDataEqual(ByRef varA As UserData, ByRef varB As UserData) As Boolean
    If varA.uName = varB.uName _
    And varA.uDate = varB.uDate Then
        UserDataEqual = True
    Else
        UserDataEqual = False
    End If
End Function

It would be used as follows:

Sub TestCheck()
    Dim testRec(1) As UserData
    testRec(0).uName = "a"
    testRec(0).uDate = Date

    testRec(1) = testRec(0)

    If UserDataEqual(testRec(1), testRec(0)) Then
        Debug.Print "Records match"
    Else
        Debug.Print "Records don't match"
    End If
End Sub

Thanks for answering my questions Brian.

Answer 2

This type of activity is what Classes are for. Instead of a user defined type create a class with appropriate methods. Here we have defined a Class called UserData which has a predeclared Id so that we can use the class as a UserData Factory. In this example we have

UserData Class

' User Rubberduck annotations to set the predecalredId to True
'@PredeclaredId
Option Explicit

Public Enum UserDataType

    udDate
    udName

End Enum

Private Type Properties
    UserData As Object
End Type

Private p As Properties


Public Function Make(ByVal ipName As String, ByVal ipDateAndTime As Date) As UserData
    
    With New UserData
    
        Set Make = .Self(ipName, ipDateAndTime)
    
    End With
    
End Function

Public Function Self(ByVal ipName As String, ByVal ipDateAndTime As Date) As UserData

    ' Use late bound crreation of a scripting dictionary to avoid reference issues
    Set p.UserData = CreateObject("Scripting.Dictionary")
    
    With p.UserData
    
        .Add udName, ipName
        .Add udDate, ipDateAndTime
    
    End With
    
    Set Self = Me
    
End Function



Public Property Get Item(ByVal ipEnum As Long) As Variant
    Item = p.UserData.Item(ipEnum)
End Property

Public Property Let Item(ByVal ipEnum As Long, ByVal ipValue As Variant)

    p.UserData.Item(ipEnum) = ipValue
End Property

Public Function SameAs(ByVal ipUserData As UserData) As Boolean

    SameAs = False
    
    Dim myIndex As Long
    For myIndex = 0 To p.UserData.Count - 1
    
        If Me.Item(myIndex) <> ipUserData.Item(myIndex) Then Exit Function
        
    Next
    
    SameAs = True

End Function

This class makes the creation of user data types a bit easier as we can now just say UserData,Make( ,

So the text sub can become

Option Explicit

Public Sub TestCheck()
    Dim testRec(1) As UserData
    Set testRec(0) = UserData.Make("a", Date)
    Set testRec(1) = UserData.Make("b", Date)
    

    If testRec(1).SameAs(testRec(0)) Then
        Debug.Print "Records match"
    Else
        Debug.Print "Records don't match"
    End If
    
End Sub

As you can see. You can see. To change the UserData class for a different set of members you only have to change the enumeration (provided you keep to simple variables).

Answer 3

Simple answer probably what you were looking for (although won't work in your situation I'm afraid, I'll explain):

'@Description("Returns the count of bytes which match over length")
Public Declare PtrSafe Function RtlCompareMemory Lib "ntdll" ( _
                                ByRef a As Any, _
                                ByRef b As Any, _
                                ByVal Length As LongPtr _
                                ) As LongPtr

Called like:

Debug.Assert RtlCompareMemory(a, b, LenB(a)) = LenB(a) 'checks every byte matches

'Or as a function ?UserDataMatch(a,b)
Public Function UserDataMatch(ByRef a As UserData, ByRef b As UserData) As Boolean
    UserDataMatch = RtlCompareMemory(a, b, LenB(a)) = LenB(a)
End Function

---

The catch is this won't work for you because Strings are stored in the UDT as pointers to some variable length block of memory containing their actual values (and these pointers will never match in VBA). So my approach only works for fixed sized UDTs, e.g.:

Public Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(0 To 7) As Byte
End Type

Public Type UDT
    IID As GUID 'nested is fine
    ClsID As GUID
    RefCount As Long
    MyClass As Object 'even classes and objects work - if the UDTs point to the same instance
End Type

But not for a UDT which contains a string or some other immutable reference type where the pointers can never be shared between two variables without low level hackery.