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

[@Excelmacromastery]

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

[@AS-ym2bp]

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.

[@Excelmacromastery]

Thanks Aashay.

[@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!

[@Excelmacromastery]

Thanks for the feedback. Glad you like the practical advice.

[@steveroberts8775]

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

[@Excelmacromastery]

glad you like it Steve.

[@krn14242]

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

[@Excelmacromastery]

that's great to hear😀

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

[@annaokarmus1334]

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

[@Excelmacromastery]

Glad it was helpful!

[@rickswineberg]

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

[@Excelmacromastery]

Ctrl + A means select All. It behaves a bit different to Current Region in Excel.

[@RicardoCamisa]

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

[@wayneedmondson1065]

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!

[@Excelmacromastery]

No problem Wayne. Glad that the videos help you understand.

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

[@Excelmacromastery]

I haven't run any speed tests on tables but I think that would be an interesting topic.

[@grzegorz2852]

John, you could also put your table into an array and your code will be faster.

[@obsoquasi]

@@grzegorz2852 For tables where I expect a lot of growth during operation, it's saver for me to access the actual table instead of using an array. It's slower yes, but it's safer and the code is more intuitive to write.

[@the_feature_selector859]

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?

[@jgojiz]

Very clear and useful, many thanks

[@rrrraaaacccc80]

Great 💯👍

[@CeliaAlvesSolveExcel]

Great lesson, Paul! Thank you!

[@Excelmacromastery]

You're welcome Celia

[@buithitrami175]

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

[@Excelmacromastery]

Thanks Mi. If you liked it you will like the next one which is one using class modules with collections.

[@turkaykoc8587]

Thank you so much

[@conan7422]

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

[@cartoonistgautam1965]

explained in an easiest way i ever came across.

[@noviceprogrammer2011]

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.

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

[@Excelmacromastery]

Dictionaries are better overall but Collections can be useful on some situations. My next video series is about the VBA Dictionary.

[@conan7422]

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

[@1fredq1]

Instead of stepping through the rows to decide which ones to add to the array (united states forexample) wouldn't it be faster to do an advanced filter to get what you are after then just add then to the array en masse as shown in the first example?

[@Excelmacromastery]

That's true Nigel. AdvancedFilter would be quicker and is better for copying and filtering data in many Excel VBA cases. However, in this video I wanted to compare arrays and collections in very simple terms to really show their differences.

[@MrMallesh1]

please share one video on Access Database Connection also..

[@Excelmacromastery]

Thanks Mallesh. This is an interesting and useful topic in VBA. I hope to cover it in the future.

[@user-pu2zp2ke2l]

Great

[@richardgreves6161]

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.

[@bilallechvien4558]

Cool 👍

[@gauravchuarasia5091]

You missed that collection can be used for unique list creation while array will read duplicates also in a list

[@furzkram]

I would dim the array to the ubound of the region first and then redim the array ONLY ONCE - namely, AFTER all matching rows have been retrieved and only to (row-1) in size. Redim is slowing down the processing and there is no need to adjust the array after each match is found.

[@RicardoCamisa]

Tem como ordenar os dados de uma collection?

[@Excelmacromastery]

You can use this: excelmacromastery.com/excel-vba-collections/#Sorting_a_Collection

[@1tempfile1]

Hello Mr.Paul Thank You for this great video! And overall great course =) But I have a question about updating values in collection. For example I need to update "orange" to "peach" "orange" have index = 4. I can create function to find index of "orange", store this index in tmpVar, remove this item, add new value before tmpVal. And wrap this code in a function for convenience

[@lucasvtiradentes]

great video, youre amazing!

[@Excelmacromastery]

Thanks Lucas.

Amazing, thx so much! Yours videos improve my skill and my macros:) Please, i have one question, for my macro i need all files names files in directory. It's possible make collection for files names in directory?

[@ghalebevaldo4416]

I ever use a second array to manage such tasks. Thats so fast. Need Not a collection. Not need preserve and redim only once. I write with resize to the sehen. Absolotely fast. But many thankx to explain the difference.

[@Excelmacromastery]

Thanks for sharing!

[@niteshprabhu6791]

The shortcut keys used in the video are mentioned in the description

[@aNDy-qh1em]

Hi Paul, do you have somewhere speed comparison in sorting arrays vs collection vs dictionary? Perhaps is there in VBA an open library or module for an analogue of list (incorporating conversion to/from other data structures and methods like sorting)

[@johnbill739]

Hi Paul. Great video. One question. @ 3.50 "Sheet1.Range (F1:I101").value = arr " prints back all the data in the array. I tried doing this in the second array(@ 6:15) when using the "rows" counter but for some reason it gets stuck on the first variable entered into the array. Do I have to loop through to get it to print. Also if you didn't know the range until after the program has completed is there some way of combing "(F1:I101)" to "Rows" to get an exact output range. Thanks again. Video was great

[@Excelmacromastery]

The second array is adding one item at a time. This is an array of strings. you can assign it to a range What do you mean my "it gets stuck on the first variable"?

[@lucagervini7514]

Hi, and thank you! how is it possible to write the array inside a listobject?

[@engrvarsi3774]

Loved it. I have two questions, and am unable to find answer anywhere... 1: Equivalent of javascript eval() which should work like... eval("msgbox 'show a message'") Here the code is in form of string msgbox "show a message" There is a way in which we take the code window as object, add module and sub programmatically, then execute it but it makes the variables in the current scope simply out of scope and lots of other problems. 2: How may I increase or decrease the number of dimensions of an Array... is that possible?

[@naumansarwar8216]

Thanks for the tutorial. I have a question. I tried the collection method which was very helpful and I thought to make a function with it as currently, I am using a long array normal formula without VBA which is lengthy and makes the sheet heavy. but when I tried to make a function which is exactly from the same example above it working from a second macro and give the correct answer but when I apply it on a sheet it gives #value error. can you please guide.

[@tonyfreelance4519]

Hello, Just found your site and really like the ideas, very different to my current approach and can see how I could use many of these techniques. I was wondering if there is a way to download a copy of the example sheets you are showing us on each of your videos. If I missed something please help me find it and if I have to buy or signup please let me know what to do. Thank You Tony :-)

[@Excelmacromastery]

Hi Tony. The worksheets for the videos aren't available. However I cover many of these topics in my live webinars which are free. The replays and code are available to website members here excelmacromastery.com/live-webinars/

[@akashtribhuvan9227]

will Collections or Arrays accept formula error values?

[@rrrprogram4704]

One question... If our need is to only filter the rows and paste it ... then, isn't POWER QUERY is the most efficient and easiest way ???

[@Excelmacromastery]

Power Query is certainly a great tool. But whether it is best depends on the requirements. My examples are very simple and certainly Power Query can do the job for these but with a more complex scenario it may be different.

[@binlee3866]

SQL Query with Ado is also efficient.

[@H2Dwoat]

Hi, when using currentregion does it include cells hidden by a filter?

[@mrashid229]

Yes it does! Life would have been easier if that wasn't the case, when you would try to copy (to array) rows based on some criteria[s].

[@TravisFX]

Hmm. how about read a list into an array, then pump that into a collection?

[@MrMallesh1]

nice video , plz share on udemy also. Thanks

[@Excelmacromastery]

why on Udemy?