Adding a multi-dimensional item into an array, then retrieving all of the items at once

Question

So I'm working on learning arrays in VBA.

VBA does not treat arrays very nicely, and I found a .net implementation in VBA that seems to work better/cleaner. Great, let's use that!

So I start by making my sub, dimming the array, and setting it:

Sub ArrayTest()
Dim StorageArray as Variant

Set StorageArray = CreateObject("System.Collection.Arraylist")

Hurray, working array! Let's add items to it.

StorageArray.Add "Peter"

For i = 1 to 4
    StorageArray.Add i
Next i

Works great!

Let's get items out of it.

For each Name in StorageArray
    debug.print Name
Next
end sub

Everything's rosy up until here. Now I want to go a bit more advanced: I want to add multi-dimensional objects to the array, and I want to dumb the ENTIRE array all at once into Excel. This is where it breaks down.

StorageArray.Add "Peter", "Paul" yells at me for a non-specified syntax error. StorageArray.Add ("Peter", "Paul") continues to yell at me.

ShTest.Range("A1").value2 = StorageArray also yells at me. "Missing paramater does not have a default value. Paramater name: Parameters" is the error given.

I know at one point I could dump an entire array out into Excel like that, but I was building off of someone elses code that I no longer have access to, so I can't figure out what the difference is.

I'm willing to look into other array methods, but the reason I'm looking into this in the first place is I have a large (~70k lines) file that needs a table unpivoted. PowerQuery is THE textbook tool for doing this, however, I don't have access to it. Apparently SQL could also help, but my SQL knowledge isn't strong enough. A double loop works, but it takes literal hours to process, and I know it can be done in minutes. I suspected that most of the time is taken up by writing the data one line at a time to the results sheet, so I'm looking into arrays to speed things up massively.

tachytelic.net/2019/09/vba-add-item-array is what I'm using as a reference - short version, apparently it re-calculates each time you add an item — Selkie
– Selkie, Commented Jan 26, 2020 at 16:06
What is the size in megabytes of the 70k line file. Is it plain text or some other format that you want to import into a Results sheet in a Excel workbook ? — CDP1802
– CDP1802, Commented Jan 26, 2020 at 16:11
I guess a typical unpivot task with 70k rows isn't a problem to be implemented with conventional VBA arrays. Could you please add some more info on your file that needs a table unpivoted? — omegastripes
– omegastripes, Commented Jan 26, 2020 at 16:12
You'd be better off finding out how arrays work in VBA (it's not that complex!) - if you want to implement an unpivot then that's likely the best way to go (once you figure out your unpivot logic). Trying to find out how ArrayList can help via trial and error isn't going to be a very productive path to take, and if you want to put the final result onto a worksheet in a single operation then likely you'd need to do that via a regular VBA array anyway. — Tim Williams
– Tim Williams, Commented Jan 26, 2020 at 17:19
Remember you don't need to build it up by adding a new "line" in a loop - just start off by making it as large as you think it will need to be (or larger) then you can cut it down in one step when you're done. Just need to keep track of the last-populated "row" number as you're filling it. Eg: stackoverflow.com/questions/53218565/… — Tim Williams
– Tim Williams, Commented Jan 26, 2020 at 17:41

FaneDuru · Accepted Answer · 2020-01-26 17:35:37Z

Excel VBA treats the arrays in a peculiar way, but it has something suitable only to Excel ranges, which I think is good to be known and used. Also, a collection is not suitable for your purpose, I think.

So, if you declare the arrays in the way you did, so created array are 0 based type and it is not complicated to use multi-dimensional ones, but let us say more complicated...

Besides this way of declaring, it is an Excel specific one working like that:

Private Sub testArrays()
  Dim arrTest As Variant, rng As Range, sh As Worksheet
   Set sh = ActiveSheet 'use here your sheet
   Set rng = sh.Range("A2:K1000")
     arrTest = rng.Value
     Debug.Print UBound(arrTest, 1), UBound(arrTest, 2)
     'UBound(arrTest, 1) = array rows and UBound(arrTest, 2) = array columns
End Sub

The resulted array is a multi-dimensional 1 based type. You can speed up your application working only in memory using such a type. You can also mix things loading any arrays type in a loop iterating between this one elements... For pasting at once use the next code:

Dim shNew As Worksheet
    Set shNew = ActiveWorkbook.Sheets("2")
    shNew.Range("A2:K1000").Value = arrTest

You only must take care of pasting the array in a range having the same rows and colums. Of course, if you need all data from the array...

freeflow · Accepted Answer · 2020-01-26 16:20:47Z

1

You need a combination of Collections and Arrays.

Sub ArrayDemo()
    ' We could equally use Dim myArray(1 to 5) as variant
    Dim myArrays As Collection
    Set myArrays = New Collection
    myArrays.Add Array(1, 2, 3, 4, 13, 6, 7, 8)

    Dim myVar As Variant
    myVar = Worksheets("Sheet1").Range("A1:Z20").Value
    myArrays.Add myVar
    myArrays.Add Split("This is a sentence that is split into an array", " ")

    myArrays.Add Array(1, "Hello World", myArrays.Item(1), 3.142, New Collection)
    Debug.Print myArrays.Item(1)(4)
    Debug.Print myArrays.Item(2)(4, 6)
    Debug.Print myArrays.Item(3)(3)

End Sub

In the example above I've used a Collection to hold my arrays but you could also use an array of variants.

answered Jan 26, 2020 at 16:20

freeflow

4,3753 gold badges13 silver badges19 bronze badges

5 Comments

Selkie Over a year ago

That looks pretty workable! The only question I have, how do I dump all of the data out at once? Do I need to cycle through every single item?

freeflow Over a year ago

You can put an array back into excel by assigning the array to a worksheet range. Lots of example on Stackoverflow.

Selkie Over a year ago

Right, but your example has a collection, and I'm trying to work out how to dump the data out of that collection into Excel in one shot, without looping through and putting in numbers one at a time

FaneDuru Over a year ago

@Selkie: Look to my answer, please, and see how to do what you request...

freeflow Over a year ago

VBA doesn't support putting a collection into excel. You have to use an array.. The array can only contain items that excel understands so you can't have an array item that is an object.

Collectives™ on Stack Overflow

Adding a multi-dimensional item into an array, then retrieving all of the items at once

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related