Excel VBA Collections: How to use Collections (1/5)

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

This video shows how to use Collections in Excel VBA. Collections are used to store a group of similar variables.

In this video you will see:

* How to declare and create a collection
* How to add, insert and remove items from a collection
* How to get the number of items in a collection
* How to use the Watch Windows to check the contents of the Collection

Table of Contents:
00:48 Why do you need a Collection?
02:15 How to declare a Collection
04:00 Adding items
06:00 Inserting items
07:24 Get the number of items in a Collection
09:07 Removing items

Shortcut Keys:
Ctrl + G: View the Immediate Window.
F5: Run the code from the current sub.
F9(or click left margin): Add a breakpoint to pause the code.

[wayneedmondson]

Hi Paul.. thanks for the intro video on How to Use Collections.. great explanation and tips/tricks for getting started. Looking forward to more on your channel. Thanks for sharing your knowledge. Thumbs up!!

[Metroid-rgpn]

Paul, you are, by far, the best VBA channel I've come across. Thanks for your advanced yet easy-to-understand examples!

[dangelorrrr]

I love how you manipulated the screens to show everything so clearly. Excellent video!

[jeffprager]

As always, Paul is exceptional when it comes to excel.  Been following him for years now.

[ouzytheoriginal]

very clear and simply explained thanks dude

[Zomp]

Keep up the good work. I stumbled across your website the other day too while searching for Excel stuff. There are plenty of Excel videos on YouTube, but you present the ideas much more clearly than the others.

[emrealtinoglu]

Thanks Paul, Looking forward to having your new videos

[yandhi]

So helpful. Supplementing my vba knowledge from the wiseowl tutorials. I need to watch more of your videos!

[stevedavies]

Great simple introduction in to collections.

[virriki]

A lot of useful information (including F5 !!!). Thanks!

[buithitrami]

Great and clear explanation Paul! Look forward to your next videos 😊

[rrrprogram]

watching again and again... Awesome work

[mizukikaioh]

Wow awesome! thanks for sharing :) you make it look easy! You won my subscription ;)

[GurgMaster]

Excellent video. Simple - and very well explained. Thank you. Can we have a hundy items in a collection as easily a tousand? 🙂

[lukkash]

remember to use WITH statement to not to repeat the same name of an object while e.g. adding new items into a collection or sth similar 🙂

[we_heart_ice_land]

great explanations and great accent! (slighly reminds me of roy... it crowd🤓)

[yandhi]

Whats the diff between an object and a variable?

Appreciate the vid !

[hariprasad]

Thank you for providing very useful videos. Pls help me in the line of code to get the value of coll2. Example: i need 2nd coll value in range A1

[adampolcz]

hi Paul, I am writing an Othello program in VBA and found that swapping elements of a collection is only 2 rows: Sub swapElmnts(coll As Collection)
coll.Add coll(goodMove)), before:=1 coll.Remove positionOfBadMove + 1
End Sub What do you think?

[anmac]

Hello, I am wondering if I can use filesystemobject properties such as .datecreated, .datelastmodified, .size, .path for files/subfolders with collections? I also want to include a range of cells with folder paths that I want to exclude and finally after the excluded folder paths i want to search for all files and folders at the end to display in the worksheet. How can I do this?