Anyone know how to - Range = typed array

[gsmcconville]

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]

Do not overcomplicate things:

This suffices:

Dim Arr(1 To 10, 1 To 5) 
ActiveSheet.Range("A1:E10")= Arr

[westconn1]

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]

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]

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]

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]

Thanks everyone. Based on this information, I think it will be best to just use a basic non typed array.

[Bob Phillips]

What's wrong with snb's last suggestion. It used a type, it is simple, and it works - job done.

[gsmcconville]

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]

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]

@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]

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]

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]

@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]

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]

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]

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]

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]

Cool, thanks everyone.

Although I did not find a way to use this in my work, I learned from it.

[snb]

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