Anyone know how to - Range = typed array

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

Code:

---

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..

Code:

---

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:

Code:

---

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

---

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

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:

Code:

---

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.

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:

Code:

---

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

---

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

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

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.

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.

Quote:

---

Originally Posted by snb

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:

Code:

---

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.

My 1.5 cents ...

I usually do something like this for Collections or Dictionaries, but it works for array also

Code:

---

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:

Code:

---

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

---

Quote:

---

Originally Posted by gsmcconville

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?

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)

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?

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.

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:

Code:

---

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

---

How come only p.Description is multiplied by 10?

Because it's in the code:

p.Description = "kk" & j * 10

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

Code:

---

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

---