Excel VBA Collections: Collections vs Arrays (4/5)

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

In this Excel VBA video, we look at the differences between the VBA Array and the VBA Collection. Both have very powerful features and are quite different.

It's important to understand them so that you can complete your VBA task more efficiently.

#vbacollection

Table of Contents:
00:20 Comparison 1: Read from a worksheet to a collection
00:47 Reading from a worksheet to a collection
02:08 Reading from a worksheet to an array
04:19 Comparison 2: Reading specific rows from a worksheet
04:53 Reading specific rows to a collection
06:21 Reading selected rows to an array
08:19 Comparison 3: Inserting Items
08:34 Inserting items into a collection
09:17 Inserting items into an array
11:33 Comparison 4: Updating item values
11:48 Updating items in a collection
12:33 Updating items in an array
13:15 Summary of differences between an array and a collection

Related links:

Shortcut Keys:
Ctrl + R: View the Project Properties Window.
Ctrl + Shift + 8(or Ctrl + *): Get the current region on a worksheet.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.
Tab: To move lines of code to the right(Indent)
Shift + Tab: To move lines of code to the left(Outdent).

[Excelmacromastery]

Thank you for watching this video. If you have any questions, please add a comment below.

[oziflyer]

Great series of videos, Paul! One thing that really impresses me is that several times I've thought; "What if i want to...", and within seconds you say "Now you might be thinking... what if I want to...", and say exactly what I'm thinking :-) Oh, and I join the hundreds of other commenters here who say - I've been using VBA for years, and I'm learning new things with every video! Thank you!

[AS-ymbp]

This channel is like a gold mine for me. I used to think I knew a lot in VBA but I'm realizing that I'm only scratching the surface. I wonder what other time and memory saving tips are there going forward.

[steveroberts]

Thanks Paul another bite size video full of great information and explained very clearly.

[GurgMaster]

Thanks again - very good stuff again. I'm using it as a refresher but these are some of the best explanations I've seen and I think I'm actually understanding stuff I used to bluff my way through. Cheers

[annaokarmus]

Excel Macro Mystery, good to have you here :) You helped me a lot to understand Arrays & Collections!

[krn]

Thanks Paul. Great explanation between Collections and Arrays. Now I understand.

[wayneedmondson]

Hi Paul.. great details on using arrays vs. collections. It helps me better understand when to use one vs. the other. Looking forward to more on your channel. Thumbs up!

[RicardoCamisa]

Obrigado pela sua partilha. Gostei muito da forma como esclareceu esta questão. Muito bom...

[obsoquasi]

The last couple of months I've been working a lot with Tables (as opposed to just Ranges) which are ListObjects in VBA. They handle very similar to Collections and are very intuitive to code with using the "for each" command. Like Collections, it's not the fastest method, but I'd rather not have to deal with having to resize arrays all the time :). And using ListObjects means, all the operations are not just in the memory (as with Collections and arrays), but the result can be seen in the actual table on the Worksheet.

[buithitrami]

Hi Paul, thanks for your new video. I have been looking forward to it since your last one 😊

[rickswineberg]

Great video, suggest (ctrl + A) also gives current region.

[the_feature_selector]

Your videos are easily the most concise Excel VBA videos on the net.

Are there many jobs in Ireland that use VBA a great deal?

[cartoonistgautam]

explained in an easiest way i ever came across.

[conan]

Updating a single item in a collection is a combination of remove and insert!

[Vumevume]

Hi Paul, I see you have several vids on connections. Whenever I have looked at connections I've always preferred to use Dictionaries instead. Wondered what your thought on that might be.

[noviceprogrammer]

I never resize an array because it has its limitations and also it's very memory intensive.

Instead, since we know the dimensions of the data, any sub array is generally not going to be bigger than the original array, so why not create the sub array to have the same dimensions as the original array, thus removing the need to constantly resize.

The only drawback to my method is that the sub array tends to be only partially filled, so you will be creating a big array unnecessarily.

To overcome that, you could define a new variable that counts the number of items that matches your criteria, then redim your sub array.

[MrMallesh]

please share one video on Access Database Connection also..

[conan]

You should do the redim after next i with redim preserve arr(1 to row-1)
Max row is rg.rows.count

[richardgreves]

Thanks Paul, this is awesome! I particularly like the ArrayInsert function. I was wondering if it could be adapted for adding formulas rather than strings. I have a sheet that has facility information with a number of variables some of which are formulas) for a given year. When data for the new year becomes available I need to insert a row between the last year of the current facility and the first year of the next facility. Doing this the old way (without arrays) takes forever. With arrays I am not able to get the ArrayInsert function to work unless I store the array as a string which breaks the formula cells.