View Full Version : [SOLVED:] Anyone know how to - Range = typed array
gsmcconville
08-14-2014, 01:50 AM
Hello
I have been trying to copy an array to a range, I can do it when the array is just a standard array such as
Dim Arr(1 to 10, 1 to 5) as Variant
Dim Rng as Range
Set Rng = ActiveSheet.Range("A1:E10")
Rng = Arr
However what I would like to do is..
Type Product
Code As String
Description As String
Cost As Currency
Qty As Integer
Retail As Currency
End Type
Sub Test()
Dim Arr(1 To 10) As Product
Dim Rng As Range
Set Rng = ActiveSheet.Range("A1:E10")
Rng = Arr
End Sub
However this produces an error of "Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions.
Can someone please shed some light to weather it is possible to do what I want?
I read some where that the Type needs to be a class, but could not get that to work either
Do not overcomplicate things:
This suffices:
Dim Arr(1 To 10, 1 To 5)
ActiveSheet.Range("A1:E10")= Arr
westconn1
08-14-2014, 03:28 AM
you can declare the type in any object module, class, worksheet or thisworkbook, must be private, but i still doubt you can assign an array of types to a range, without looping
lecxe
08-14-2014, 03:33 AM
Hi
You cannot write directly a typed array to a range.
If you want to work with a custom data type array and write its data to a range, you could either
. write each element, field by field, to each cell in the range, or
. you can use an auxiliary variant array, and coerce the data into variants.
The variant array can then be written directly to the range.
For the second option, this is an example that you can adapt:
Option Explicit
Type Product
Code As String
Description As String
Cost As Currency
Qty As Integer
Retail As Currency
End Type
Sub Test()
Dim Arr(1 To 10) As Product
Dim arrPrint(1 To 10, 1 To 5) As Variant ' auxiliary variant array
Dim j As Long
Dim Rng As Range
Set Rng = ActiveSheet.Range("A1:E10")
' initialise product array values
Arr(3).Code = "COD001"
Arr(6).Description = "Desc_001"
Arr(7).Qty = 200
'...
' to write the custom data type array to the range use an auxiliary variant array
For j = 1 To 10
arrPrint(j, 1) = Arr(j).Code
arrPrint(j, 2) = Arr(j).Description
arrPrint(j, 3) = Arr(j).Cost
arrPrint(j, 4) = Arr(j).Qty
arrPrint(j, 5) = Arr(j).Retail
Next j
Rng = arrPrint
End Sub
As you mentioned, you could also explore implementing a class.
Bob Phillips
08-14-2014, 04:37 AM
What you could also do is create a class called Product with your five fields, and another collection class called Products, and Products could have a property that returns the whole set as a simple array that you could dump onto the worksheet.
In a macro module:
Type Product
Code As String
Description As String
Cost As Currency
Qty As Integer
Retail As Currency
End Type
Sub M_snb()
Dim p As Product
With CreateObject("scripting.dictionary")
For j = 0 To 6
p.Code = "kk" & j
p.Description = "kk" & j * 10
p.Cost = 12 * j
p.Qty = 300 * j
p.Retail = 20 * j
.Item("snb_" & j) = Array(p.Code, p.Description, p.Cost, p.Qty, p.Retail)
Next
Cells(20, 1).Resize(.Count, 5) = Application.Index(.items, 0, 0)
End With
End Sub
gsmcconville
08-14-2014, 02:39 PM
Thanks everyone. Based on this information, I think it will be best to just use a basic non typed array.
Bob Phillips
08-14-2014, 03:12 PM
What's wrong with snb's last suggestion. It used a type, it is simple, and it works - job done.
gsmcconville
08-14-2014, 03:40 PM
It loads the data into a type, and then copies that data into a standard array.
So when you go to use the array for other things, it will have no reference to the type.
I appreciate snb's suggestion, but it just seems like extra steps that are not nescessary.
I may as well save time and put the data straight into the array.
Aussiebear
08-18-2014, 01:35 AM
Righto, I've read throughout this thread a couple of times and was wondering if someone could kindly explain what happened here? In snb's first post i thought that was the answer in so far as Range = Arr. How does one reference the Array to Type?
@Aussiebear.
In every Dictionary item I put a normal 1 dimensional array: Array("","",''")
In that array I can store separate properties of the variable p, that I declared to be a 'Procuct'Type.
So the Dictionary consists of several 1-dimensional arrays.
The funny thing in Excel is I can 'transform' 1-dimensional Dictionary items into a multidimensional array using Application(.items,0,0), although .items itself is a 1-dimensional array too.
There's no relation between array and type, other than that the array serves only as a 'container' for properties of a 'type' variable.
lecxe
08-18-2014, 07:40 AM
The funny thing in Excel is I can 'transform' 1-dimensional Dictionary items into a multidimensional array using Application(.items,0,0), although .items itself is a 1-dimensional array too.
Hi
Just a remark, if someone is wondering:
One could say .items is, in fact, a jagged array in which the first level is 1D.
In this case each element of .items is itself an array (of 5 simple, non-array elements), making .items a 2-level jagged array.
Application(.items,0,0) is converting a 2 level jagged array into a 2D array (with simple, non-array elements)
... which is very convenient.
I believe you could also use:
Cells(20, 1).Resize(.Count, 5) = Application.Index(.items, 0)
but using one parameter for the rows and one for the columns makes it easier to read.
Paul_Hossler
08-18-2014, 09:05 AM
My 1.5 cents ...
I usually do something like this for Collections or Dictionaries, but it works for array also
Option Explicit
Type Product
Code As String
Description As String
Cost As Currency
Qty As Integer
Retail As Currency
End Type
Sub Test()
Dim T As Product
Dim Arr(1 To 10) As Product
Dim Rng As Range
Dim i As Long
Set Rng = ActiveSheet.Range("A1:E10")
For i = 1 To 10
With T
.Code = Rng.Cells(i, 1).Value
.Description = Rng.Cells(i, 2).Value
.Cost = Rng.Cells(i, 3).Value
.Qty = Rng.Cells(i, 4).Value
.Retail = Rng.Cells(i, 5).Value
End With
Arr(i) = T
Next I
MsgBox Arr(2).Code
MsgBox Arr(4).Description
End Sub
@PH
Nice !
Since it's only about values I'd say:
Sub Test()
Dim T As Product
Dim Arr(1 To 10) As Product
Dim sn
Dim i As Long
sn = ActiveSheet.Range("A1:E10")
For i = 1 To 10
With T
.Code = sn(i, 1)
.Description = sn(i, 2)
.Cost = sn(i, 3)
.Qty = sn(i, 4)
.Retail = sn(i,5)
End With
Arr(i) = T
Next I
MsgBox Arr(2).Code
MsgBox Arr(4).Description
End Sub
Aussiebear
08-18-2014, 03:50 PM
So when you go to use the array for other things, it will have no reference to the type.
So in essence here, the array "arr" is linked to the type Product for the duration of the sub?
Paul_Hossler
08-18-2014, 04:20 PM
arr is an array of variants, so you can assign user defined types to the elements
Like the OP said, it's a little bit loopy since you need to populate an variable of the user defined type, and then assign that variable to an array element
The advantage IMHO is that you can address the elements using the user defined type:
MsgBox Arr(2).Code
MsgBox Arr(4).Description
as opposed to
MsgBox Arr(2,2)
MsgBox Arr(4,3)
gsmcconville
08-18-2014, 04:52 PM
But would I be correct in saying that with snb's and Paul Hossler's last post, is that you cannot quickly dump that data into a range?
Paul_Hossler
08-18-2014, 07:40 PM
You be correct
If you make arr a generic NxM array you can load it and dump it all at once.
BUT you have to use arr(n,1), arr(n,2), etc.
gsmcconville
08-18-2014, 10:43 PM
Cool, thanks everyone.
Although I did not find a way to use this in my work, I learned from it.
You can write every product, - one at a time - directly into a range:
Type Product
Code As String
Description As String
Cost As Currency
Qty As Integer
Retail As Currency
End Type
Sub M_snb()
Dim p As Product
For j = 1 To 6
p.Code = "kk" & j
p.Description = "kk" & j * 10
p.Cost = 12 * j
p.Qty = 300 * j
p.Retail = 20 * j
sheet1.cells(j,1).resize(,5) Array(p.Code, p.Description, p.Cost, p.Qty, p.Retail)
Next
End Sub
Aussiebear
08-19-2014, 01:42 AM
How come only p.Description is multiplied by 10?
Because it's in the code:
p.Description = "kk" & j * 10
Paul_Hossler
08-19-2014, 09:03 AM
Since the array is already typed as Product, I really didn't need the intermediate variable (T)
There's still no way to assign to the array in one swell foop, but I usually go for readability since there doesn't seem to be a performance issue
Option Explicit
Type Product
Code As String
Description As String
Cost As Currency
Qty As Integer
Retail As Currency
End Type
Sub Test()
Dim Arr(1 To 10) As Product
Dim Rng As Range
Dim i As Long
Set Rng = ActiveSheet.Range("A1:E10")
For i = 1 To 10
With Arr(i)
.Code = Rng.Cells(i, 1).Value
.Description = Rng.Cells(i, 2).Value
.Cost = Rng.Cells(i, 3).Value
.Qty = Rng.Cells(i, 4).Value
.Retail = Rng.Cells(i, 5).Value
End With
Next I
MsgBox Arr(2).Code
MsgBox Arr(4).Description
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.