strange behavior : How elements are inserted in vba arrays?

Question

Can any one explain me the following code behavior ?? -->

Public Type User
    firstName          As String
    lastName           As String
End Type

Sub test()
    Dim userList(1) As User
    Dim user1       As User
    user1.firstName = "Tom"
    user1.lastName = "Hanks"
    
    userList(0) = user1
    Debug.Print "0 userList(0).firstName=" & userList(0).firstName & " userList(0).lastName=" & userList(0).lastName
    user1.lastName = "Cruise"
    Debug.Print "1 userList(0).firstName=" & userList(0).firstName & " userList(0).lastName=" & userList(0).lastName

End Sub

And the console displays :

0 userList(0).firstName=Tom userList(0).lastName=Hanks

1 userList(0).firstName=Tom userList(0).lastName=Hanks

So my question is : why the console does not display

1 userList(0).firstName=Tom userList(0).lastName=Cruise

?

It looks like if vba insert a copy of the object I want to insert but not the original object.

Thanks for your answers.

This problem just happend, I did not succeed to solve it, but if I can't probably I will change my code so that all the initialization of the object will be done before the insertion in the array, but it is not very clean IMHO.

Answer 1

Simple Usage of a Class

• This is just a simple example of what, IMO, you wanted to do. It barely 'scratches the surface'.
• The objects (cUser) are most often held in a collection (users) but you might as well use an array (rCount is the number of (data) rows (objects, cUsers)).

Class Module e.g. Class1: Rename to cUser in the Properties window (F4)!!!

Option Explicit

Public ID As Long
Public FirstName As String
Public LastName As String

Standard Module e.g. Module1

Option Explicit

Sub Test()
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    ' Reference the data range (no headers).
    
    Dim rg As Range, rCount As Long
    
    With ws.Range("A1").CurrentRegion
        rCount = .Rows.Count - 1
        If rCount = 0 Then Exit Sub ' no data
        Set rg = .Resize(rCount).Offset(1) ' exclude headers
    End With
    
    ' Create a new collection that will hold all 'cUser' objects.
    Dim users As Collection: Set users = New Collection
    
    ' Loop through the rows of the range...
    
    Dim user As cUser, r As Long
    
    For r = 1 To rCount
        ' Create a new 'user' object.
        Set user = New cUser
        ' Populate the 'user'.
        user.ID = rg.Cells(r, 1).Value
        user.FirstName = rg.Cells(r, 2).Value
        user.LastName = rg.Cells(r, 3).Value
        ' Add to the 'users' collection.
        users.Add user
    Next r
    
    ' Loop through all the elements ('cUser') in the 'users' collection
    ' and return each user's properties in the Immediate window (Ctrl+G).
    For Each user In users
        Debug.Print user.ID & ". " & user.FirstName & " " & user.LastName
    Next user

End Sub

Result in the Immediate window

1. Tom Hanks
2. Jack Nicholson
3. Michael J. Fox
4. Robert De Niro

Standard Module e.g. Module1

• The same using an array instead of a collection.

Sub TestArray()
    
    ' Reference the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    ' Reference the data range (no headers).
    
    Dim rg As Range, rCount As Long
    
    With ws.Range("A1").CurrentRegion
        rCount = .Rows.Count - 1
        Set rg = .Resize(rCount).Offset(1) ' exclude headers
    End With
    
    ' Define an array that will hold all 'cUser' objects.
    Dim ArrUsers() As cUser: ReDim ArrUsers(1 To rCount)
    
    ' Loop through the rows of the range...
    
    Dim user As cUser, r  As Long
    
    For r = 1 To rCount
        ' Create a new 'user' object.
        Set user = New cUser
        ' Populate the 'user'.
        user.ID = rg.Cells(r, 1).Value
        user.FirstName = rg.Cells(r, 2).Value
        user.LastName = rg.Cells(r, 3).Value
        ' Add to the 'ArrUsers' array.
        Set ArrUsers(r) = user
    Next r
    
    ' Loop through all the elements ('cUsers') in the 'ArrUsers' array
    ' and return each user's properties in the Immediate window (Ctrl+G).
    
    For r = 1 To rCount
        Debug.Print ArrUsers(r).ID & ". " & ArrUsers(r).FirstName _
            & " " & ArrUsers(r).LastName
    Next r

End Sub