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 |
Writing an array(2d) of user types to a worksheet
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 |
Writing an array(2d) of user types to a worksheet
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 |
Writing an array(2d) of user types to a worksheet
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 |
All times are GMT +1. The time now is 06:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com