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!
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!
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
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, 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?
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?
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,
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 ?
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?
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
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.
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?
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?
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 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.
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!
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
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…
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
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
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.
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
As always, Paul does a magnificent job of turning complex coding into a simple procedure.
@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.
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.
We are not worthy. Great tutorial, Paul. Thank you for the lesson.
My pleasure Tom.
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
At some point, I started to loose track. Will watch this over again.
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!
Thank you Paul!
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?
This content is awesome!
One of your most interesting video so far. Thank you!
Glad to hear that!
Back again... Thanks for the effort
You're welcome.
Awesome Paul! Thanks for sharing this. Much to study and learn :)) Thumbs up!!
Glad you liked it Wayne.
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?
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?
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.
Thanks! Great video as always. Much appreciated.
This was very helpful. Thank you!
You're very welcome!
thanks a lot, helped to solve some design problems.
Glad it helped!
Thanks for the tutorial
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 ?
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?
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
Thank you for this wonderful video :)
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 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.
Thank You
Welcome!
Quality content, as always! Even knowing that, it was nice to watch your video :)
Glad you enjoyed it!
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.
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?
Couldn't you make a new class that uses a Collection and then add Exists method to the class?
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 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.
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!
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.
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
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…
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
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.
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.
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.
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
Hey, could you please inform which Font do you use in the VBA editor?
Consolas
@@Excelmacromastery Thank you!
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
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
😍