Watch these 28 minutes if you want to become an Advanced VBA user...
HTML-код
- Опубликовано: 5 июл 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
Want to download the source code for this video? Go here: bit.ly/3PprNZ8
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Subscribe to the channel here: bit.ly/36hpTCY
Watch these 28 minutes if you want to become an Advanced VBA user...
In this video, I'm going to show you how to master using Arrays, Collections and Dictionaries in VBA. If you can grasp what is covered in the video then you are well on your way to becoming an advanced VBA user. Make sure to download the code from the link in the description so that you can try out the examples for yourself.
#VBACollection #VBADictionary #VBAArray
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
Table of Contents:
00:00 - Introduction
00:17 - How to use Arrays with Ranges
08:36 - Arrays vs Dictionaries/Collections
09:53 - Example 2: Unique values - Array
13:45 - Example 2B: Using Transpose
14:53 - Collection basics
16:49 - Example 2: Unique values - Collection
18:32 - Dictionary Basics
20:39 - Example 2: Unique - Dictionary
22:07 - Example 3: Sum Data Dictionary
25:02 - Example 4: Sum Multiple fields 
Наука
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.
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.
You deserve a knighthood sir, truly amazing service you provide..so many careers improved thanks to your teaching
Thanks.
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.
Excellent tutorial. Love the combination of the dictionary and array in the last example. Brilliant.
Thanks. Glad you like it.
It's great to see all the different structures side by side.
Great as always. I've learnt so much from you throughout the years!
Thanks Robert
I look forward to your uploads more than any other youtuber. Thank you!
I appreciate that!
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
Thanks for your work Paul! Your vids are always truly helpful!👍👍👍🤟😎
My pleasure John!
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.
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
Thank you very much Mr. Paul.
Please, we need more about VBA for Trading.
I definitely need to dive deep into these subjects. Thank you for sharing this valuable content.
You're welcome Jan
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
Great job. Thanks for this tutorial!
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.
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).
Thanks for the video, well presented and clearly explained!
You're welcome Nic
You are amazing. Thank you so much. Thanks to you, I am using VBA fast. Greetings from Turkey :)
Thanks
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!
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?
Excellent tutorial! Didn't know an array is x10 times slower compared to a dictionary.
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. :((
This type of Teaching especially VBA , Never before seen.............. Great Sir😲😲😲
Thanks
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.)
Would you have to keep nesting for loops to set criteria on multiple columns?
Thx a lot.
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?
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
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.
This is valuable information to optimize processes. Thank you very much. Excellent video.
Super ❤
Thank you for the video. It is difficult to understand the logic behind the existinarray function to extract the unique list using array.
Fantastic
Thanks Jose
Could you use the custom "Type" rather than the array?
Thanks s....
For example 4. Is it faster to use array than class?
Do you have community to answer excle vba beginners queries? Or do you gave online courses to learn vba for beginners
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 want to generate Standard meteorological week number from specific date
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.
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?
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!
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.
can the Simulator Trading using Excel VBA also work in Access app?
Not easily because it is written specifically for Excel.
cool
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.
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.
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
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
It is very useful video but getting the Unique values using Arrays was explained very fast.
How to solve active x command become picture
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(ruclips.net/video/cUB-MYTlDUU/видео.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.
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
I keep getting an error in the existsinarray function at the for i = 1 to current row
What is the error?
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
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.
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