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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 work*** 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 work*** 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 work*** in one
> > step.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "John Keith" <JohnKeith@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:3A9D7BE3-CE50-4EB9-B511-C79B8BA73146@xxxxxxxxxxxxxxxx
> > > What is the "fast" way to write out a 2d-array of user defined types to a
> > > work***.
> > >
> > > 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 work*** range.
> > >
> > > If there is some way to build a valid variant array from my user type,
> > > perhaps that would work.
> > >
> > > Ideas?
> > >
> > >
> > > --
> > > Regards,
> > > John
> >
> >
> >
.

Quantcast