Arrays: 0-based vs 1-based, 1D vs 2D

• Workbook Download (Dropbox)
• To copy the data in one go you have to put it in an adequate array, unfortunately using a loop, but which should be much faster than looping through a range.
• The following are various options how you may achieve this.
• I have expanded it to include the last name, too.
• You should probably introduce a long variable for UBound(userDetails).
• I used Private instead of Public to keep it on module-level.

Module1

Option Explicit

Private Type typDetails
    firstName As String
    lastName As String
End Type

Private userDetails(2) As typDetails

' Introduce two 0-based arrays (needs Transpose and 'size adjustment').
Sub test1()

    Dim i As Long
    Dim vntFirst As Variant
    Dim vntLast As Variant

    userDetails(0).firstName = "Bob1"
    userDetails(0).lastName = "Bobson1"
    userDetails(1).firstName = "John1"
    userDetails(1).lastName = "Johnson1"
    userDetails(2).firstName = "Peter1"
    userDetails(2).lastName = "Peterson1"

    ReDim vntFirst(UBound(userDetails))
    ReDim vntLast(UBound(userDetails))

    For i = 0 To UBound(userDetails)
        vntFirst(i) = userDetails(i).firstName
        vntLast(i) = userDetails(i).lastName
    Next

    With Worksheets(1)
        .Range("A1").Resize(UBound(vntFirst) + 1) = _
                Application.Transpose(vntFirst)
        .Range("B1").Resize(UBound(vntLast) + 1) = _
                Application.Transpose(vntLast)
    End With

End Sub

' Introduce two 1D 1-based arrays (needs Transpose).
Sub test2()

    Dim i As Long
    Dim vntFirst As Variant
    Dim vntLast As Variant

    userDetails(0).firstName = "Bob2"
    userDetails(0).lastName = "Bobson2"
    userDetails(1).firstName = "John2"
    userDetails(1).lastName = "Johnson2"
    userDetails(2).firstName = "Peter2"
    userDetails(2).lastName = "Peterson2"

    ReDim vntFirst(1 To UBound(userDetails) + 1)
    ReDim vntLast(1 To UBound(userDetails) + 1)

    For i = 0 To UBound(userDetails)
        vntFirst(i + 1) = userDetails(i).firstName
        vntLast(i + 1) = userDetails(i).lastName
    Next

    With Worksheets(1)
        .Range("A1").Resize(UBound(vntFirst)) = Application.Transpose(vntFirst)
        .Range("B1").Resize(UBound(vntLast)) = Application.Transpose(vntLast)
    End With

End Sub

' Introduce two 2D 1-based arrays.
Sub test3()

    Dim i As Long
    Dim vntFirst As Variant
    Dim vntLast As Variant

    userDetails(0).firstName = "Bob3"
    userDetails(0).lastName = "Bobson3"
    userDetails(1).firstName = "John3"
    userDetails(1).lastName = "Johnson3"
    userDetails(2).firstName = "Peter3"
    userDetails(2).lastName = "Peterson3"

    ReDim vntFirst(1 To UBound(userDetails) + 1, 1 To 1)
    ReDim vntLast(1 To UBound(userDetails) + 1, 1 To 1)

    For i = 0 To UBound(userDetails)
        vntFirst(i + 1, 1) = userDetails(i).firstName
        vntLast(i + 1, 1) = userDetails(i).lastName
    Next

    With Worksheets(1)
        .Range("A1").Resize(UBound(vntFirst)) = vntFirst
        .Range("B1").Resize(UBound(vntLast)) = vntLast
    End With

End Sub

Module2

Option Explicit

Private Type typDetails
    firstName As String
    lastName As String
End Type

' Declare as 1D 1-based array.
Private userDetails(1 To 3) As typDetails

' Introduce two 1D 1-based arrays (needs Transpose).
Sub test3()

    Dim i As Long
    Dim vntFirst As Variant
    Dim vntLast As Variant

    Erase userDetails
    userDetails(1).firstName = "Bob4"
    userDetails(1).lastName = "Bobson4"
    userDetails(2).firstName = "John4"
    userDetails(2).lastName = "Johnson4"
    userDetails(3).firstName = "Peter4"
    userDetails(3).lastName = "Peterson4"

    ReDim vntFirst(1 To UBound(userDetails))
    ReDim vntLast(1 To UBound(userDetails))

    For i = 1 To UBound(userDetails)
        vntFirst(i) = userDetails(i).firstName
        vntLast(i) = userDetails(i).lastName
    Next

    With Worksheets(1)
        .Range("A1").Resize(UBound(vntFirst)) = Application.Transpose(vntFirst)
        .Range("B1").Resize(UBound(vntLast)) = Application.Transpose(vntLast)
    End With

End Sub

Module3

Option Explicit

Private Type typDetails
    firstName As String
    lastName As String
End Type

' Declare as 2D 1-based array.
Private userDetails(1 To 3, 1 To 1) As typDetails

' Introduce two 2D 1-based arrays.
Sub test5()

    Dim i As Long
    Dim vntFirst As Variant
    Dim vntLast As Variant

    Erase userDetails
    userDetails(1, 1).firstName = "Bob5"
    userDetails(1, 1).lastName = "Bobson5"
    userDetails(2, 1).firstName = "John5"
    userDetails(2, 1).lastName = "Johnson5"
    userDetails(3, 1).firstName = "Peter5"
    userDetails(3, 1).lastName = "Peterson5"

    ReDim vntFirst(1 To UBound(userDetails), 1 To 1)
    ReDim vntLast(1 To UBound(userDetails), 1 To 1)

    For i = 1 To UBound(userDetails)
        vntFirst(i, 1) = userDetails(i, 1).firstName
        vntLast(i, 1) = userDetails(i, 1).lastName
    Next

    With Worksheets(1)
        .Range("A1").Resize(UBound(vntFirst)) = vntFirst
        .Range("B1").Resize(UBound(vntLast)) = vntLast
    End With

End Sub