What is the "fast" way to write out a 2d-array of user defined types to a
worksheet.

I.E.
User type is a record of 10 fields. Which has 30 rows...

Type UT
field1 as string
field2 as string ....
field10 as string
end type
Dim myUDTarray () as UT
....
ReDim Preserve myUDTarray(ubound(myUDTarray) + 1) 'adds a row
myUDTarray(ubound(myUDTArray)).field1 = "v1"
....
myUDTarray(ubound(myUDTArray)).field10 = "v10"

Using the set rng = range("a1").cells
then copying myUDTarray(x).each field...to the rng.offset(x,y).value in a
loop is extremely slow.

what are some other options?

When I try to write code that uses variant arrays I get a comple error that
says my user type must be in a public module... but I do not want the type
"public" it is used ONLY in this one module.. however... what is the
statement that I would put at the top of the module to allow me to assign the
usertype'd array to a worksheet range.

If there is some way to build a valid variant array from my user type,
perhaps that would work.

Ideas?


--
Regards,
John

Putting multiple values in each element of your 2d-array as you do with your
UDT would mean that you would not be able to write it to a worksheet in any
other way than looping through and writing each value. You might write it
to another expanded 2D array first, then write that to the worksheet in one
step.

--
Regards,
Tom Ogilvy


"John Keith" wrote in message
...
What is the "fast" way to write out a 2d-array of user defined types to a
worksheet.

I.E.
User type is a record of 10 fields. Which has 30 rows...

Type UT
field1 as string
field2 as string ....
field10 as string
end type
Dim myUDTarray () as UT
...
ReDim Preserve myUDTarray(ubound(myUDTarray) + 1) 'adds a row
myUDTarray(ubound(myUDTArray)).field1 = "v1"
...
myUDTarray(ubound(myUDTArray)).field10 = "v10"

Using the set rng = range("a1").cells
then copying myUDTarray(x).each field...to the rng.offset(x,y).value in a
loop is extremely slow.

what are some other options?

When I try to write code that uses variant arrays I get a comple error
that
says my user type must be in a public module... but I do not want the type
"public" it is used ONLY in this one module.. however... what is the
statement that I would put at the top of the module to allow me to assign
the
usertype'd array to a worksheet range.

If there is some way to build a valid variant array from my user type,
perhaps that would work.

Ideas?


--
Regards,
John

Here is what I was trying from your suggestion ... "You might write it to
another expanded 2D array first, then write that to the worksheet in one
step."

The trouble is that I can't make "vaData(1, 1).Value = 1" properly assign
a cell of the variant a value of 1... How do you properly declare vaData,
then how do you assign it's elements values.

Below is some test code that i was using to try out some solutions: Neither
Test nor Test2 were successful in assigning values to a variant array.

Option Base 1
Option Explicit
Type UDT
Field1 As Integer
Field2 As String
Field3 As Long
Field4 As Long
End Type
Dim UDTArray() As UDT

Sub test()
Dim lRow As Long, lCol As Long
Dim addr As String
Dim BRCell As String
Dim TLCell As String
Dim varLong As Long, varString As String, varInteger As Integer
ReDim Preserve UDTArray(1)

For lRow = 1 To 6
If lRow 1 Then ReDim Preserve UDTArray(UBound(UDTArray) + 1)
UDTArray(lRow).Field1 = 1
UDTArray(lRow).Field2 = "Assigned Row" & lRow
UDTArray(lRow).Field3 = 1.56
UDTArray(lRow).Field4 = 2.34
Next lRow

' Dim vaData As Variant
' ReDim vaData(UBound(UDTArray), 4) ' still caused the 424 error

Dim vaData(1 To 6, 1 To 4) As Variant

Dim varVar As Variant
For lRow = 1 To UBound(vaData)
varInteger = UDTArray(lRow).Field1
varVar = UDTArray(lRow).Field1
vaData(lRow, 1).Value = 1 'ERROR - Object Required (run
time 424)
' vaData(lRow, 1).Value = varVar ' "
' vaData(lRow, 1).Value = varInteger ' "
varVar = UDTArray(lRow).Field2
vaData(lRow, 2).Value = varVar
varVar = UDTArray(lRow).Field3
vaData(lRow, 3).Value = varVar
varVar = UDTArray(lRow).Field4
vaData(lRow, 4).Value = varVar
Next lRow
Range("A1:D6").Value = vaData
End Sub
Sub test2()
Dim vaData(1 To 6, 1 To 4) As Variant
Dim varVar As Variant

' vaData(1, 1).Value = 1 'ERROR - object required (runtime 424)

varVar = 1
vaData(1, 1).varVar 'ERROR - object required (runtime 424)
End Sub
--
Regards,
John


"Tom Ogilvy" wrote:

Putting multiple values in each element of your 2d-array as you do with your
UDT would mean that you would not be able to write it to a worksheet in any
other way than looping through and writing each value. You might write it
to another expanded 2D array first, then write that to the worksheet in one
step.

--
Regards,
Tom Ogilvy


"John Keith" wrote in message
...
What is the "fast" way to write out a 2d-array of user defined types to a
worksheet.

I.E.
User type is a record of 10 fields. Which has 30 rows...

Type UT
field1 as string
field2 as string ....
field10 as string
end type
Dim myUDTarray () as UT
...
ReDim Preserve myUDTarray(ubound(myUDTarray) + 1) 'adds a row
myUDTarray(ubound(myUDTArray)).field1 = "v1"
...
myUDTarray(ubound(myUDTArray)).field10 = "v10"

Using the set rng = range("a1").cells
then copying myUDTarray(x).each field...to the rng.offset(x,y).value in a
loop is extremely slow.

what are some other options?

When I try to write code that uses variant arrays I get a comple error
that
says my user type must be in a public module... but I do not want the type
"public" it is used ONLY in this one module.. however... what is the
statement that I would put at the top of the module to allow me to assign
the
usertype'd array to a worksheet range.

If there is some way to build a valid variant array from my user type,
perhaps that would work.

Ideas?


--
Regards,
John

Never mind...
Just realized my mistake:

vaData(1,1) = "1"

--
Regards,
John


"John Keith" wrote:

Here is what I was trying from your suggestion ... "You might write it to
another expanded 2D array first, then write that to the worksheet in one
step."

The trouble is that I can't make "vaData(1, 1).Value = 1" properly assign
a cell of the variant a value of 1... How do you properly declare vaData,
then how do you assign it's elements values.

Below is some test code that i was using to try out some solutions: Neither
Test nor Test2 were successful in assigning values to a variant array.

Option Base 1
Option Explicit
Type UDT
Field1 As Integer
Field2 As String
Field3 As Long
Field4 As Long
End Type
Dim UDTArray() As UDT

Sub test()
Dim lRow As Long, lCol As Long
Dim addr As String
Dim BRCell As String
Dim TLCell As String
Dim varLong As Long, varString As String, varInteger As Integer
ReDim Preserve UDTArray(1)

For lRow = 1 To 6
If lRow 1 Then ReDim Preserve UDTArray(UBound(UDTArray) + 1)
UDTArray(lRow).Field1 = 1
UDTArray(lRow).Field2 = "Assigned Row" & lRow
UDTArray(lRow).Field3 = 1.56
UDTArray(lRow).Field4 = 2.34
Next lRow

' Dim vaData As Variant
' ReDim vaData(UBound(UDTArray), 4) ' still caused the 424 error

Dim vaData(1 To 6, 1 To 4) As Variant

Dim varVar As Variant
For lRow = 1 To UBound(vaData)
varInteger = UDTArray(lRow).Field1
varVar = UDTArray(lRow).Field1
vaData(lRow, 1).Value = 1 'ERROR - Object Required (run
time 424)
' vaData(lRow, 1).Value = varVar ' "
' vaData(lRow, 1).Value = varInteger ' "
varVar = UDTArray(lRow).Field2
vaData(lRow, 2).Value = varVar
varVar = UDTArray(lRow).Field3
vaData(lRow, 3).Value = varVar
varVar = UDTArray(lRow).Field4
vaData(lRow, 4).Value = varVar
Next lRow
Range("A1:D6").Value = vaData
End Sub
Sub test2()
Dim vaData(1 To 6, 1 To 4) As Variant
Dim varVar As Variant

' vaData(1, 1).Value = 1 'ERROR - object required (runtime 424)

varVar = 1
vaData(1, 1).varVar 'ERROR - object required (runtime 424)
End Sub
--
Regards,
John


"Tom Ogilvy" wrote:

Putting multiple values in each element of your 2d-array as you do with your
UDT would mean that you would not be able to write it to a worksheet in any
other way than looping through and writing each value. You might write it
to another expanded 2D array first, then write that to the worksheet in one
step.

--
Regards,
Tom Ogilvy


"John Keith" wrote in message
...
What is the "fast" way to write out a 2d-array of user defined types to a
worksheet.

I.E.
User type is a record of 10 fields. Which has 30 rows...

Type UT
field1 as string
field2 as string ....
field10 as string
end type
Dim myUDTarray () as UT
...
ReDim Preserve myUDTarray(ubound(myUDTarray) + 1) 'adds a row
myUDTarray(ubound(myUDTArray)).field1 = "v1"
...
myUDTarray(ubound(myUDTArray)).field10 = "v10"

Using the set rng = range("a1").cells
then copying myUDTarray(x).each field...to the rng.offset(x,y).value in a
loop is extremely slow.

what are some other options?

When I try to write code that uses variant arrays I get a comple error
that
says my user type must be in a public module... but I do not want the type
"public" it is used ONLY in this one module.. however... what is the
statement that I would put at the top of the module to allow me to assign
the
usertype'd array to a worksheet range.

If there is some way to build a valid variant array from my user type,
perhaps that would work.

Ideas?


--
Regards,
John