Writing an array(2d) of user types to a worksheet

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >