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!
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.
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!
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
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. :)
@@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?
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.
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!
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?
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!
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?
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 ?
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!
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.
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?
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
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?
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’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,
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
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.
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.
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…
Would emulate from time , date , location so all we needed is address and time and emulate data from there to create from RGB i/o from time phase hopefully can't be confuse and no search needed because time variable can be like 3 × 3 × 3 of long that limited i
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
If data base as large as camera and few billion picture of airport passenger over years then the data base of limited by Long data types. Even if we divide into smaller data by day still the storage could swallowed all our operating money. But by emulate data is time phase color scheme then all database needed to store only few byte per picture reduce memory needed by billion time
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
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.
I madara... King of this world... Declare you the king of vba..
😄
5 times watching now. Clearer for noobs like me. Now I wanna use only array and collection. Thanks
@Excel Macro Mastery I now work more or less with VBA development thanks to your videos and very informative webpage
You're welcome.
The clarity with which you explain vba is outstanding. Well done!
Thanks Ori.
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.
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
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?
We are not worthy. Great tutorial, Paul. Thank you for the lesson.
My pleasure Tom.
One of your most interesting video so far. Thank you!
Awesome Paul! Thanks for sharing this. Much to study and learn :)) Thumbs up!!
Glad you liked it Wayne.
This content is awesome!
Thanks! Great video as always. Much appreciated.
Back again... Thanks for the effort
You're welcome.
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?
Thank you Paul!
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!
thanks a lot, helped to solve some design problems.
Glad it helped!
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 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?
Quality content, as always! Even knowing that, it was nice to watch your video :)
Glad you enjoyed it!
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.
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?
This was very helpful. Thank you!
You're very welcome!
I really liked your video! Thank you so much!!
Thanks David
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 ?
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.
Thank you for this wonderful video :)
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!
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.
I usually use a dictionary of dictionaries, but a dictionary of objects is clearer.
Is this better than using recordsets?
Thanks for the tutorial
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, could you please inform which Font do you use in the VBA editor?
Consolas
@@Excelmacromastery Thank you!
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
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?
At some point, I started to loose track. Will watch this over again.
Couldn't you make a new class that uses a Collection and then add Exists method to the class?
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.
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.
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’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.
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
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
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
Thanks
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.
Thank You
Welcome!
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.
hi dear paul, do make python series?
I have two Python videos so far. I may do more in the future.
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…
Would emulate from time , date , location so all we needed is address and time and emulate data from there to create from RGB i/o from time phase hopefully can't be confuse and no search needed because time variable can be like 3 × 3 × 3 of long that limited i
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
😍
If data base as large as camera and few billion picture of airport passenger over years then the data base of limited by Long data types. Even if we divide into smaller data by day still the storage could swallowed all our operating money. But by emulate data is time phase color scheme then all database needed to store only few byte per picture reduce memory needed by billion time
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