PDA

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

snb
08-14-2014, 02:14 AM
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.

snb
08-14-2014, 04:38 AM
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?

snb
08-18-2014, 05:32 AM
@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

snb
08-18-2014, 12:50 PM
@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.

snb
08-19-2014, 01:33 AM
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?

snb
08-19-2014, 02:34 AM
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