I want to transfer the contents from part of the a 'Type' array to an excel range (see example below), but get 'Invalid Qualifier' error. Any help would be appreciated?

Public Type typDetails
 firstName As String
 lastName As String
End Type

Dim userDetails(100) As typDetails

Public Sub test()

 userDetails(0).firstName = "Bob"
 userDetails(0).lastName = "Bobson"
 userDetails(1).firstName = "Bob"
 userDetails(1).lastName = "Bobson"
 userDetails(2).firstName = "Bob"
 userDetails(2).lastName = "Bobson"

 Worksheets(1).Range("A1:A3") = userDetails.firstName

End Sub

Thanks in advance,
Alex.

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

Think you need a loop:

Public Sub test()

Dim i As Long

userDetails(0).firstName = "Bob"
userDetails(0).lastName = "Bobson"
userDetails(1).firstName = "Bob"
userDetails(1).lastName = "Bobson"
userDetails(2).firstName = "Bob"
userDetails(2).lastName = "Bobson"

For i = 0 To 2
 Worksheets(1).Cells(1, i + 1).Value = userDetails(i).firstName
Next i

End Sub