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!
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.
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
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!
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.
@@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.
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.
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.
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.
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?
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.
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)
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
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 :-)
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/
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.
4 роки тому
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?
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
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"?
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.
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?
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.
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.
Thank you for watching this video. If you have any questions, please add a comment below.
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!
Thanks for the feedback. Glad you like the practical advice.
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.
Thanks Aashay.
Thanks Paul another bite size video full of great information and explained very clearly.
glad you like it Steve.
Excel Macro Mystery, good to have you here :) You helped me a lot to understand Arrays & Collections!
Glad it was helpful!
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
Thanks Paul. Great explanation between Collections and Arrays. Now I understand.
that's great to hear😀
Great 💯👍
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!
No problem Wayne. Glad that the videos help you understand.
Great video, suggest (ctrl + A) also gives current region.
Ctrl + A means select All. It behaves a bit different to Current Region in Excel.
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.
I haven't run any speed tests on tables but I think that would be an interesting topic.
John, you could also put your table into an array and your code will be faster.
@@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.
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?
Obrigado pela sua partilha. Gostei muito da forma como esclareceu esta questão. Muito bom...
Very clear and useful, many thanks
Hi Paul, thanks for your new video. I have been looking forward to it since your last one 😊
Thanks Mi. If you liked it you will like the next one which is one using class modules with collections.
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.
Great lesson, Paul! Thank you!
You're welcome Celia
explained in an easiest way i ever came across.
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.
Dictionaries are better overall but Collections can be useful on some situations. My next video series is about the VBA Dictionary.
Updating a single item in a collection is a combination of remove and insert!
You should do the redim after next i with redim preserve arr(1 to row-1)
Max row is rg.rows.count
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.
Thank you so much
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?
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.
please share one video on Access Database Connection also..
Thanks Mallesh. This is an interesting and useful topic in VBA. I hope to cover it in the future.
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)
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
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 :-)
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/
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.
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?
Hi, and thank you! how is it possible to write the array inside a listobject?
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
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"?
Tem como ordenar os dados de uma collection?
You can use this:
excelmacromastery.com/excel-vba-collections/#Sorting_a_Collection
You missed that collection can be used for unique list creation while array will read duplicates also in a list
Cool 👍
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.
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?
will Collections or Arrays accept formula error values?
Great
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.
Thanks for sharing!
Hmm. how about read a list into an array, then pump that into a collection?
Hi, when using currentregion does it include cells hidden by a filter?
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].
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 ???
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.
SQL Query with Ado is also efficient.
The shortcut keys used in the video are mentioned in the description
nice video , plz share on udemy also. Thanks
why on Udemy?