How to use Class Modules with the VBA Dictionary
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/3CHIuYn
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
How to use Class Modules with the VBA Dictionary
In this video I'm going to show you how and why to use Class Modules with the VBA Dictionary.
We're going to looks under the hood and see exactly how Class Modules work in VBA. We're then going to do a full
VBA example. Finally I'm going to show you how you can avoid the issues with external libaries by getting the Collection to work exactly like a Dictionary
#VBADictionary #ClassModule #VBACollection
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + C 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) 
Наука
Make sure to download the code from the link in the description and try out the code for yourself.
The link for the source code is missing in the description.
The link to the sample code is missing from the Video description, and I could not find that content on your website either. If you could add the link to the description it would be much appreciated. Great video, hoping having the code will help me fully understand it!
I've updated the link. It should be fine now.
For me, the most amzing parts of this video is "how to update specific item of a collection when its correspoding key exists already."
And the method is to update the object stored in the item of the collection, rather than update variable directly. (Compare 9:05-9:20 with 12:49)
And the underlying principle is 12:50-13:02 and 5:05-5:24. This video covers not only the way of how vba store data, but point out how to use class module to update collection item. Thanks Paul, I've learned a lot from you.
You're welcome
Will have to rewatch this one a few times for it to sink in. Thanks Paul.
You're welcome.
As always, Paul does a magnificent job of turning complex coding into a simple procedure.
5 times watching now. Clearer for noobs like me. Now I wanna use only array and collection. Thanks
I madara... King of this world... Declare you the king of vba..
😄
@Excel Macro Mastery I now work more or less with VBA development thanks to your videos and very informative webpage
You're welcome.
Paul, Excellent tutorial. This really brings dictionary & class modules into sharp focus for me. I look forward to rewatching the tutorials you linked at the end again with the expectation that they’ll sink in this time. Thank you!
Thanks Jim.
The clarity with which you explain vba is outstanding. Well done!
Thanks Ori.
Hi Paul
I was wondering if you could use custom type instead of a class module. It seem a waste to use a class module when it just has a couple of public variables. I though that was th epurpose of a custom type.
Can you please explain the benefits
Thanks for your amazing videos
One of your most interesting video so far. Thank you!
Glad to hear that!
Thanks! Great video as always. Much appreciated.
Thank you Paul!
This content is awesome!
Awesome Paul! Thanks for sharing this. Much to study and learn :)) Thumbs up!!
Glad you liked it Wayne.
Back again... Thanks for the effort
You're welcome.
We are not worthy. Great tutorial, Paul. Thank you for the lesson.
My pleasure Tom.
Thank you for this wonderful video :)
This was very helpful. Thank you!
You're very welcome!
Me at 3:27: "Yep, yep, uh-huh, got it." Me at 4:05: "Wait, what? Huh??" I swear, once I get the concept of objects under my belt, I'll probably wonder why I couldn't grasp it before. Time to go back to the class modules tutorial!
Once it clicks you will see it very clearly.
Hello Paul,
Thank you so much for your great video!
May I know if there is any way to create a dictionary with multiple keys per item?
I really liked your video! Thank you so much!!
Thanks David
Quality content, as always! Even knowing that, it was nice to watch your video :)
Glad you enjoyed it!
thanks a lot, helped to solve some design problems.
Glad it helped!
Thanks for sharing. This was a big help with my golf league spreadsheet. Using a course dictionary and a player dictionary then a call sub, I can easily calculate matches and handicaps. Instead running a call how would I set both dictionaries and classes as a Global Variable?
At some point, I started to loose track. Will watch this over again.
Thanks for the tutorial
A lot of collection types are used in Excel, Access, etc. Understanding them is of key importance. They are also a topic in many program languages.
Very true. We call collections types data structures. Although I don't use the phrase much as most non-programmers wouldn't know what I meant.
Thanks, Paul. I use a Mac so being able to use a collection in place of the dictionary will help a lot...once I've got the rest of the process into my noddle...which is a slow process. :)
Thanks for sharing Philip! I'm sure you're smarter than you give yourself credit for.
@@Excelmacromastery I had no idea until this video that Dictionaries don't work on Mac! Is there any benefit then to a dictionary, when a collection can do the same things with objects?
Still very confused, Paul.
I need to understand "dictionary" and "collection" and "class".
What are they? Why should I use them?
Why should I use Excel over Access when I want to keep track of members and their yearly contributions?
Thank you Paul! Great and good explained as always!
Is there any possibility to add/remove variables from the class modules model dynamically? To get the variables from a excel table for example. Or would you replace the cls by an array or something else in this case?
I think I would use a dictionary for this treating variable as key and storing whatever value it holds as item. The advantage of this is that - as shown in the video - you can store objects in the variable.
Another big perk for using collection over dictionary, you can see the content of a collection when debugging into be Watch or Local window. So no more guessing what the item is, can just see all the items properties listed out.
Also because of the add item at position, within collection. It makes it pretty fast and easy to create a collection in order of its contents.
Good point Dave.
This can be by far the most ground-breaking advantage of collections over dictionaries. Thanks to your comment I almost completely switched to collections which I didn't use so much before. Thanks!
Hello, can you make a video on creating a VBA maybe cell for example A1 is already linked to RTD for stock info for the last price in my data, and as that price changes in real time then cell B1 would store the previous value and so on like B2,B3,B4.... can they be time stamped also?
Thanks for the interesting video. QQ: I go a ways back to QuickBasic. Is there a record structure or can that only be done with classes?
There is a Type structure but it has limits when using it with arrays.
Paul, appreciate the video on Dictionaries!
@9:24 ~ I hope I can ask this questions correctly... can I do this with String Variables in the Class Module?
I have two columns with multiple entries in each... e.g.: Category & Subcategory.
Category will have multiple entries e.g.: Conduit
Subcategory will have unique entries for the Category e.g.: "EMT, GRC, IMC"
When I create the dictionary, and then read through the ranges, it will create unique Categories, can I add then add the unique Subcategories to each unique Category?
for example:
with Category
.subcategory = .subcategory + rg.cells(i,2).value
end with
I have already worked out how to do this using Collections but, thought I would ask.
Thanks!
I’m trying to concentrate on using excel for prototyping and c# for product and this is a revolution for me. I can do prototype much closer to final product. Great stuff,
Glad you found it useful.
Thank You
Welcome!
Thanks for your valuable explanation. However, i think the reference for the vba code does not corresponds to this video. Can you update the link for the code corresponding to the video ?
Hi
Need your expert guidance on one topic. I am explaining the below scenario
I need to connect to SharePoint list through excel and insert or update the data in the existing SharePoint list. I am able to connect and insert data to a SharePoint list as single entry. What I need yo achieve is that inserting multiple entries in SharePoint list.columns from excel. Can you please help.me.how I can use range there
I wonder, is there a way to do this without having to declare the Public variables in the class module?
Like, a class that could work for any table you point it to and it would dynamically create the .Name, .Amount, etc. depending on the column names and number of columns?
Hey, what does the pile of old soviet cars signify? They were not particular, but anyway.
Quick one. How do I do a search based on multiple non adjacent dynamic numbering of columns? Let's say:
Columns 4,7,15,21,30,45
Or another case for
Columns 12, 15, 27
Is there any advantage to using rng(i,1) instead of rng.Cells (i, 1)?
There only real difference is that using Cells allows you to use the With statement. I use Cells everywhere so it is consistent.
Be sure to check for existing key by dict.exists("keyname") and NOT by if dict("keyname") is nothing. Because in the second example, it will create the key... And always returns false. Very often mistake.
Good point Son
i'm not familiar with using # in front of the if else statements as you do on 13:22 . is that something special for this sub? what does adding #s do?
See docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/compiler-constants
I usually use a dictionary of dictionaries, but a dictionary of objects is clearer.
Is this better than using recordsets?
It's similar to Recordsets but they work a bit differently.
Hey, could you please inform which Font do you use in the VBA editor?
Consolas
@@Excelmacromastery Thank you!
What is the maxinum rows and columns of the dictionary n collection?
There is no specific limit but it would be subject to memory limits.
Couldn't you make a new class that uses a Collection and then add Exists method to the class?
😍
hi, do you have a github account or can recommend some interesting sources to find out new commands
I don't have a github account at this time.
Good evening sir,
I have a question. Is it possible to perform multiple calculations in one userform? As an explanation I have written the calculation explanation in the comment.
But I want you to know that I am not logged in to Facebook and I don't plan to do so.
In textbox 1 I want to calculate
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "Bank
Criteria range4 column C
Criteria4 = "Start value
In textbox2 should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "Bank
Criteria range4 column C
Criteria4 = "+".
In text box 3 the following should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "Bank
Criteria range4 column C
Criteria4 = "-"
In text box 4
value from textbox1+value from textbox2- value from textbox3
In textbox 5 should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "wallet
Criteria range4 column C
Criteria4 = "Start value
In textbox6 should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "wallet
Criteria range4 column C
Criteria4 = "+".
In text box 7 the following should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "wallet
Criteria range4 column C
Criteria4 = "-"
In textbox 8
value from textbox5+value from textbox6- value from textbox7
In textbox 9 should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "Checkout
Criteria range4 column C
Criteria4 = "Start value
In textbox10 should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "Checkout
Criteria range4 column C
Criteria4 = "+".
In text box 11 the following should be calculated
Sum ifs
Sum range column M
Criteria range1 Column A
Criteria1 is not empty
criteria range2 column A
Criteria2 is in the past or today's date
Criteria range3 Column B
Criteria3 = "Checkout
Criteria range4 column C
Criteria4 = "-"
In textbox 12
value from textbox9+value from textbox10- value from textbox11
It would be really great if you could help me as I need to make my work easier as I have physical problems.
Financially at the moment is also not much possible the
Kind regards Andrew Scoins
Hi Paul
How can I use "multiply" key in one Dictionary
Example:
Material Batch Receipt
A X 2
A Y 1
B M 1
B N 1
B M 2
B P 1
Result
==> dict(Material = A, Batch = X) = 2
==> dict(Material = A, Batch = Y) = 1
==> dict(Material = B, Batch = M) = 3
==> dict(Material = B, Batch = N) = 1
==> dict(Material = B, Batch = P) = 1
I have to CONCATENATE "Material" and "Batch" as a string like ("A" & "^" & "X") then SPLIT them to show result, and so it's really stupid solution
I need your help urgently
Thanks
I should have used this on a dev project I was on last year that involved a global dictionary to maintain user access settings throughout the tool. Instead I just used one module devoted to data retention of a global dictionary. I imagine going this route provides better optimization than my approach.
Thanks for the feedback Jonathan.
@@Excelmacromastery absolutely! Big fan of your content. I think a lot of people are unaware of how powerful VBA really is.
In real time of routine work what is the best and useful technique weather to use dictionary or collection …..it’s very confusing..pls give a solution for this as an expert like u who have worked in the developing industry…
hi dear paul, do make python series?
I have two Python videos so far. I may do more in the future.
I find it much simpler most of the time to use an array to store all the data, and use the dictionary to store the array index applicable to each key
So adding a new item is something like
If Not dic.Exists(newKey) then dic.Add newKey, dic.Count+1
This following code generates a run time error 451, however if it was other data type as the class property instead of an array, it works fine. Anyone can explain why?
Sub CollectionObject()
Dim fruit As Collection
Set fruit = New Collection
Dim ofruit As clsFruit
Set ofruit = New clsFruit
ofruit.arr = Array(1, 2, 3)
fruit.Add Key:="Apple", Item:=ofruit
Set ofruit = fruit("Apple")
ofruit.arr = Array(4, 5, 6)
Dim i As Long
For i = LBound(fruit("Apple").arr) To UBound(fruit("Apple").arr)
Debug.Print fruit("Apple").arr(i) '---> error line
Next i
End Sub