Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/ Want to download the source code for this video? Go here: shorturl.at/nMnc6 (Note: If the download page doesn't work then make sure to turn off any popup blockers)
Hugely useful video! Thanks a lot! I have been programming paralelly in Python and Vba and it never stops amazing me how creative one must get to solve specific problems in vba. When removing duplivates in python you simply change the object type to sets, which intrinsicly does not allow duplicates. Why not then create a class modul in vba that does exactly the same?
Great video . Nice to see dictionary storing the array row, I haven’t seen that shown anywhere before. I’m always surprised by that as I use it quite frequently when wanting to merge two sets of data, so much faster than looping one array against another. Also liked seeing the transpose of the keys and items to drop them into the worksheet which is new one on me that I’ll definitely be using, so much easier than what I’ve been doing.
I know this channel is Excel orianted, but we always come across projects that use Word and Excel together. Word has its own Dictionary object for some language specific properties. I feel it would be better to fully qualify "as Scripting.Dictionary", much like Word.Range and Excel.Range. However I don't use Word so not exactly sure. Any input regarding this would be appreciated. I will also ask in the Facebook group.
I like dictionaries. But their major drawback is weak debugging support. You can only see keys and no values in the locals window. :(. They remind me of JSON objects
Hello sir , I have a array and I want to filter this array, what it is possible to check that the value is available in array or not , I try it with loop but with this it's become too complex. Please help
I believe that looping is the only way to check for existing values in an array. I usually use a dictionary of classes to make it faster and more robust (almost all learned from this channel). He has lots of great videos on using dictionaries and my macros have gotten SO much faster because of this channel. 🍻
Um... I typed in the code exactly as shown at 2:50 Mark. Nothing prints out in the Range starting at F1. Running the code, I placed a watch on 'arr' and I can see everything got read into the array... but nothing gets written out. I'm confused?
If you have a new version of excel, you can use the new function . Unique, Sort, SortBy, Filter, is so easy. But unfortunately I have learned so much with dictionaries and collections that it is difficult for me to use the new ones
Definitely the Unique function is a great option in most situations if you have a newer version of Excel. Getting unique values with the 3 data structures is really useful for comparison.
At the 12 minute mark an example two, the value of currentrow is zero, and it is passed to the function existsinarray at the start, if so, how is it possible to iterate from one to zero and not get an error?
hi paul, i have found out a simplest way to test if value exits in an array; notice that i have based on this tutorial before going further and found it! in this example i use arrayList: by going to → Tools → Referecences and checking this library → mscorlib.dll after this, an automation error can occur but fixing it is simple for an experienced vba developer that's it try the following codes bellow↓ Sub UseArrayList() Dim arrMain As Variant Dim arrList As New ArrayList
arrMain = Sheet1.Range("A1").CurrentRegion.Value2
Dim i As Long For i = LBound(arrMain,1) To UBound(arrMain,1) If arrList.Contains(arrMain(i, 1)) = False Then arrList.Add arrMain(i, 1) End If Next i Range("C1").Resize(arrList.Count, 1) = WorksheetFunction.Transpose(arrList.toarray)
UBound is an array lenght only when the LBound is 1. But there are zero-based arrays in many cases. The proper way to get an array length is use LBound(arr)-UBound(arr)+1 and it is convinient to make that as a function.
Thanks! Quick question..... Is there a benefit from creating a separate array in the final example vs using an array itself as the item in the dictionary? If dict.Exists(first_name) Then dict(first_name) = Array(dict(first_name)(0) + quantity, dict(first_name)(1) + sales_price) Else dict.Add first_name, Array(quantity, sales_price) End If This has been the way I have handled this situation in the past.
When you use one main array you can write it out it once which is faster and more efficient. Also in your code you are creating a new array for each record which is not which is much slower.
@@Excelmacromastery Right-O! Thanks, that's why I asked. I dug around a little and did a little testing. I made the dataset 5000 rows and then just to keep it entertaining I made it do it repeat the rows 1000 times. The nested array in the dictionary came in at a little over 10 seconds. Your one array method came in at 2.5 seconds. For the more fun I went for a two dictionary method (one for price and one for quantity) and it still was a little slower at around 3.2 seconds. In a small dataset it didn't make much of a difference at all but boy at scale those arrays cost factors more! Also, for future people watching, you can not directly update an array inside of a dictionary. A copy is returned and so you can not simply update an element. A new array must be constructed and replace the old one as I was doing in my previous question. That, as he has shown, comes at a significant time cost in VBA.
I've watched your other video's on dictionaries and collections and was always unsure as to the how and why of using them in real life... Today it all came together thanks.
A few months into practicing, made some nice macro buttons for projects, one project having 20ish buttons. I used for and for each loop, open and close workbooks from a workbook. Just getting to understanding ref and val, and passing variables. I want to do array, coll/dictionary, learn more about class modules, and enum. Finally ado and sql for query ws like a database. I watch this nonstop. Very confusing to a newbie like me. packed with so much info that everytime i watch (after gaining experience practicing) i understand more. Hands down most entertaining, and most practical real world examples. Paul is very lucky to have that speaking voice and knowledge. It is all the work on editing the clips and having a speaking voice that holds the audience attention. My subs are very long like most beginners, but once I understand completely enum, class modules, ref/val, functions, and how to tie my shoes I think I can say I am intermediate at vba.
Thanks for your Valuable content.. Is this possible to use this for Vlookup in 7 Columns from Large Data set having multiple columns Around 200 and Having 800000 Rows? The common Vlookup is very slower it takes more than 10 minitues. Is any way for Lookup faster?
Thanks a lot Paul. This is really helpful. I just cannot download the code. I think the link may be corrupted. Would you mind to check. Thanks a lot again.
Hi Kelly, this is so good and knowledgable, i just started watching your video and learn great stuff ...thanks for sharing your VBA Knowledge.....i have one question that can we copy array to clipboard.... I always have to copy cells that has duplicate values, usually i remove duplicate but not able to directly copy Only unique values....pls help
Glad to see you again! Good explanation 👏 I have one question that I can't solve in any way. Unable to print duplex via VBA. Are there any options? Google says this issue has been around since MS Office 2010, but hasn't been fixed yet. :((
If you have more than 100k (breakeven) IDs to store, Collection is the fastest. Dictionary checks internal a mount of internal hash values. Use For Each and not For Next to loop through a Collection, it is much faster.
Should I continue with excel + VBA or use Python? In finance, I get data from Bloomberg into excel, then use VB on the excel ranges containing that data. Python seems to not be as easy to do this but I am not sure! Also I get the impression that “programming” in python is just a lot of grabbing existing routines.. unlike VB where I end up writing everything from scratch. So far I am sticking with VB & excel, it gets the job done.. but I definitely am a dinosaur amongst all the young python kids lol
I just want to verify that the dictionary cannot have more than 2 “columns”? I ended up using a multiple column collection because I couldn’t use the dictionary
The dictionary can only store one item per key. However that item could be another data structure or class module. The collection is the similar except the key is optional.
Thanks to you I started using arrays dict and collections for my project and the macros are just far better with them! Thank you. I guess that IT wilk be also useful od one day i decide to automate Excel with python (if i learn IT one day).
Great video, but I feel like all those things have been already covered in your previous videos. I'd love to see another video on some professional application using userforms and classes. Keep up the good work!
8:34 Why declare variable outputarray as variant and in the next line redimensioning? Cant u just declare with the sizes? Or is it because the sizes are variables and not constants?
@@Excelmacromastery got it; it is very clear when u recall the C programming language; even though the newest standard allows VLA (variable length array), the oldest standard required the size of arrays allocated in the stack to be known at compile time (constants); otherwise, if the size is variable, then it was required to dynamic allocate memory for the array in the heap, with a calloc call
First time commenting since watching one of your videos for the first time a couple of years ago. Your videos helped me discover the wonders of programming (VBA initially, which led to my curiosity about other programs) and made me far more efficient in what I do. Thank you for sharing your knowledge in such a clear and organized way. Best, Alec
Amazing video. I use Arrays because I am just not used to using Collections or Dictionaries. I see that they are useful when dealing with a large amount of data. Thanks for sharing your techniques.
Great explanation! One thing: i would rather create a small array for each item in the dictionary and just store the array in the dictionary als the item.
please make an excel file with monthly activation if the excel file has expired activate it by entering the activation code again the excel file can be used again for one month of use
Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
Want to download the source code for this video? Go here: shorturl.at/nMnc6
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
I like how you start with pseudo-code comments, and then fill them in with the actual code as you go.
Hugely useful video! Thanks a lot! I have been programming paralelly in Python and Vba and it never stops amazing me how creative one must get to solve specific problems in vba. When removing duplivates in python you simply change the object type to sets, which intrinsicly does not allow duplicates. Why not then create a class modul in vba that does exactly the same?
You deserve a knighthood sir, truly amazing service you provide..so many careers improved thanks to your teaching
Thanks.
Excellent tutorial. Love the combination of the dictionary and array in the last example. Brilliant.
Thanks. Glad you like it.
Pure Kelly magic! As an octogenarian I don't program anymore but still love to watch this type of presentation from an incredibly talented maestro.
Thanks Faan. Glad you like it.
Thanks for the transpose limitation info.
Something else that is nice about the Dictionary is that arrays and objects can be stored as the values.
Great video . Nice to see dictionary storing the array row, I haven’t seen that shown anywhere before. I’m always surprised by that as I use it quite frequently when wanting to merge two sets of data, so much faster than looping one array against another. Also liked seeing the transpose of the keys and items to drop them into the worksheet which is new one on me that I’ll definitely be using, so much easier than what I’ve been doing.
Thanks Richard, glad you like it
I know this channel is Excel orianted, but we always come across projects that use Word and Excel together. Word has its own Dictionary object for some language specific properties. I feel it would be better to fully qualify "as Scripting.Dictionary", much like Word.Range and Excel.Range. However I don't use Word so not exactly sure. Any input regarding this would be appreciated. I will also ask in the Facebook group.
Thanks for sharing!
Great as always. I've learnt so much from you throughout the years!
Thanks Robert
I like dictionaries. But their major drawback is weak debugging support. You can only see keys and no values in the locals window. :(. They remind me of JSON objects
Hello sir , I have a array and I want to filter this array, what it is possible to check that the value is available in array or not , I try it with loop but with this it's become too complex. Please help
You can see how to filter an array in this video at 3 minutes(ua-cam.com/video/cUB-MYTlDUU/v-deo.html)
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
I believe that looping is the only way to check for existing values in an array. I usually use a dictionary of classes to make it faster and more robust (almost all learned from this channel). He has lots of great videos on using dictionaries and my macros have gotten SO much faster because of this channel. 🍻
@@hajajahirhussain6814 I believe filter function only works with one-dimensional arrays so your function won't solve every case.
The VBA filter function is very slow- otherwise that would work.
Hi Paul, love your tutorials, question about data types; When would you use Integer as opposed to Long?
Um... I typed in the code exactly as shown at 2:50 Mark. Nothing prints out in the Range starting at F1. Running the code, I placed a watch on 'arr' and I can see everything got read into the array... but nothing gets written out. I'm confused?
Great video, Paul. I have to watch it in 5 to 10 minute blocks. There is so much in it I need time to absorb it or my head will start spinning.
Glad you like it Tom.
If you have a new version of excel, you can use the new function . Unique, Sort, SortBy, Filter, is so easy. But unfortunately I have learned so much with dictionaries and collections that it is difficult for me to use the new ones
Would be very nice he makes a video comparing the new array functions to pure array!
Definitely the Unique function is a great option in most situations if you have a newer version of Excel.
Getting unique values with the 3 data structures is really useful for comparison.
My comment was deleted. I just wanted to clarify something. Hope you dont delete this comment.
Thank you for the video. It is difficult to understand the logic behind the existinarray function to extract the unique list using array.
At the 12 minute mark an example two, the value of currentrow is zero, and it is passed to the function existsinarray at the start, if so, how is it possible to iterate from one to zero and not get an error?
It is very useful video but getting the Unique values using Arrays was explained very fast.
hi paul, i have found out a simplest way to test if value exits in an array; notice that i have based on this tutorial before going further and found it!
in this example i use arrayList: by going to → Tools → Referecences and checking this library → mscorlib.dll
after this, an automation error can occur but fixing it is simple for an experienced vba developer
that's it try the following codes bellow↓
Sub UseArrayList()
Dim arrMain As Variant
Dim arrList As New ArrayList
arrMain = Sheet1.Range("A1").CurrentRegion.Value2
Dim i As Long
For i = LBound(arrMain,1) To UBound(arrMain,1)
If arrList.Contains(arrMain(i, 1)) = False Then
arrList.Add arrMain(i, 1)
End If
Next i
Range("C1").Resize(arrList.Count, 1) = WorksheetFunction.Transpose(arrList.toarray)
End Sub
UBound is an array lenght only when the LBound is 1. But there are zero-based arrays in many cases. The proper way to get an array length is use LBound(arr)-UBound(arr)+1 and it is convinient to make that as a function.
True but lBound is always 1 for a range array.
I want to generate Standard meteorological week number from specific date
It's great to see all the different structures side by side.
Would you have to keep nesting for loops to set criteria on multiple columns?
Thanks! Quick question.....
Is there a benefit from creating a separate array in the final example vs using an array itself as the item in the dictionary?
If dict.Exists(first_name) Then
dict(first_name) = Array(dict(first_name)(0) + quantity, dict(first_name)(1) + sales_price)
Else
dict.Add first_name, Array(quantity, sales_price)
End If
This has been the way I have handled this situation in the past.
When you use one main array you can write it out it once which is faster and more efficient. Also in your code you are creating a new array for each record which is not which is much slower.
@@Excelmacromastery Right-O! Thanks, that's why I asked. I dug around a little and did a little testing. I made the dataset 5000 rows and then just to keep it entertaining I made it do it repeat the rows 1000 times. The nested array in the dictionary came in at a little over 10 seconds. Your one array method came in at 2.5 seconds. For the more fun I went for a two dictionary method (one for price and one for quantity) and it still was a little slower at around 3.2 seconds. In a small dataset it didn't make much of a difference at all but boy at scale those arrays cost factors more!
Also, for future people watching, you can not directly update an array inside of a dictionary. A copy is returned and so you can not simply update an element. A new array must be constructed and replace the old one as I was doing in my previous question. That, as he has shown, comes at a significant time cost in VBA.
I've watched your other video's on dictionaries and collections and was always unsure as to the how and why of using them in real life... Today it all came together thanks.
You're welcome Joe
A few months into practicing, made some nice macro buttons for projects, one project having 20ish buttons. I used for and for each loop, open and close workbooks from a workbook.
Just getting to understanding ref and val, and passing variables.
I want to do array, coll/dictionary, learn more about class modules, and enum. Finally ado and sql for query ws like a database.
I watch this nonstop.
Very confusing to a newbie like me. packed with so much info that everytime i watch (after gaining experience practicing) i understand more.
Hands down most entertaining, and most practical real world examples. Paul is very lucky to have that speaking voice and knowledge. It is all the work on editing the clips and having a speaking voice that holds the audience attention.
My subs are very long like most beginners, but once I understand completely enum, class modules, ref/val, functions, and how to tie my shoes I think I can say I am intermediate at vba.
Thanks for your Valuable content..
Is this possible to use this for Vlookup in 7 Columns from Large Data set having multiple columns Around 200 and Having 800000 Rows?
The common Vlookup is very slower it takes more than 10 minitues.
Is any way for Lookup faster?
Thanks a lot Paul. This is really helpful.
I just cannot download the code. I think the link may be corrupted. Would you mind to check.
Thanks a lot again.
Make sure to remove any popup blockers
Hi Kelly, this is so good and knowledgable, i just started watching your video and learn great stuff ...thanks for sharing your VBA Knowledge.....i have one question that can we copy array to clipboard.... I always have to copy cells that has duplicate values, usually i remove duplicate but not able to directly copy Only unique values....pls help
Glad to see you again! Good explanation 👏
I have one question that I can't solve in any way. Unable to print duplex via VBA. Are there any options? Google says this issue has been around since MS Office 2010, but hasn't been fixed yet. :((
If you have more than 100k (breakeven) IDs to store, Collection is the fastest. Dictionary checks internal a mount of internal hash values. Use For Each and not For Next to loop through a Collection, it is much faster.
Should I continue with excel + VBA or use Python? In finance, I get data from Bloomberg into excel, then use VB on the excel ranges containing that data. Python seems to not be as easy to do this but I am not sure! Also I get the impression that “programming” in python is just a lot of grabbing existing routines.. unlike VB where I end up writing everything from scratch. So far I am sticking with VB & excel, it gets the job done.. but I definitely am a dinosaur amongst all the young python kids lol
Could you use the custom "Type" rather than the array?
I just want to verify that the dictionary cannot have more than 2 “columns”? I ended up using a multiple column collection because I couldn’t use the dictionary
The dictionary can only store one item per key. However that item could be another data structure or class module.
The collection is the similar except the key is optional.
For example 4. Is it faster to use array than class?
How to solve active x command become picture
Thanks to you I started using arrays dict and collections for my project and the macros are just far better with them! Thank you. I guess that IT wilk be also useful od one day i decide to automate Excel with python (if i learn IT one day).
Thank you very much Mr. Paul.
Please, we need more about VBA for Trading.
Excellent tutorial! Didn't know an array is x10 times slower compared to a dictionary.
Great video, but I feel like all those things have been already covered in your previous videos. I'd love to see another video on some professional application using userforms and classes. Keep up the good work!
Do you have community to answer excle vba beginners queries? Or do you gave online courses to learn vba for beginners
Thanks s....
I keep getting an error in the existsinarray function at the for i = 1 to current row
What is the error?
8:34
Why declare variable outputarray as variant and in the next line redimensioning? Cant u just declare with the sizes? Or is it because the sizes are variables and not constants?
You need to use Redim when resizing with variables.
@@Excelmacromastery got it; it is very clear when u recall the C programming language; even though the newest standard allows VLA (variable length array), the oldest standard required the size of arrays allocated in the stack to be known at compile time (constants); otherwise, if the size is variable, then it was required to dynamic allocate memory for the array in the heap, with a calloc call
This type of Teaching especially VBA , Never before seen.............. Great Sir😲😲😲
Thanks
cool
Super ❤
Thx a lot.
You are amazing. Thank you so much. Thanks to you, I am using VBA fast. Greetings from Turkey :)
Thanks
can the Simulator Trading using Excel VBA also work in Access app?
Not easily because it is written specifically for Excel.
First time commenting since watching one of your videos for the first time a couple of years ago. Your videos helped me discover the wonders of programming (VBA initially, which led to my curiosity about other programs) and made me far more efficient in what I do. Thank you for sharing your knowledge in such a clear and organized way.
Best,
Alec
You're welcome Alex
Amazing video. I use Arrays because I am just not used to using Collections or Dictionaries. I see that they are useful when dealing with a large amount of data. Thanks for sharing your techniques.
You're welcome Karl.
Great job. Thanks for this tutorial!
I definitely need to dive deep into these subjects. Thank you for sharing this valuable content.
You're welcome Jan
Great explanation! One thing: i would rather create a small array for each item in the dictionary and just store the array in the dictionary als the item.
It won't work. You cannot update the array within the Dictionary.
please make an excel file with monthly activation if the excel file has expired activate it by entering the activation code again the excel file can be used again for one month of use
Thanks for the suggestion
Thanks for your work Paul! Your vids are always truly helpful!👍👍👍🤟😎
My pleasure John!
Thanks for the video, well presented and clearly explained!
You're welcome Nic
Fantastic
Thanks Jose
I look forward to your uploads more than any other youtuber. Thank you!
I appreciate that!
Being a dead language, VBA still gets the work done in so many work spaces. Thanks to you, kind sir!
"The most productive corpse on the team." (Attributed to a MS Access user.)
This is valuable information to optimize processes. Thank you very much. Excellent video.