How to use Class Modules with the VBA Dictionary

[@Excelmacromastery]

Make sure to download the code from the link in the description and try out the code for yourself.

[@ce917]

The link for the source code is missing in the description.

[@MichaelDewTechTips]

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!

[@Excelmacromastery]

I've updated the link. It should be fine now.

[@krn14242]

Will have to rewatch this one a few times for it to sink in. Thanks Paul.

[@Excelmacromastery]

You're welcome.

[@iamjojo999]

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.

[@Excelmacromastery]

You're welcome

[@rrrprogram8667]

I madara... King of this world... Declare you the king of vba..

[@Excelmacromastery]

😄

[@norulzanamohamedsamuri1364]

5 times watching now. Clearer for noobs like me. Now I wanna use only array and collection. Thanks

[@PanzerRanger]

@Excel Macro Mastery I now work more or less with VBA development thanks to your videos and very informative webpage

[@Excelmacromastery]

You're welcome.

[@jeffprager4348]

As always, Paul does a magnificent job of turning complex coding into a simple procedure.

[@tomharrington1453]

We are not worthy. Great tutorial, Paul. Thank you for the lesson.

[@Excelmacromastery]

My pleasure Tom.

[@Orumaiti]

The clarity with which you explain vba is outstanding. Well done!

[@Excelmacromastery]

Thanks Ori.

[@jimfitch]

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!

[@Excelmacromastery]

Thanks Jim.

[@Foreman1329]

One of your most interesting video so far. Thank you!

[@Excelmacromastery]

Glad to hear that!

[@musefirefox2420]

Thanks! Great video as always. Much appreciated.

[@oldhatman6769]

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,

[@Excelmacromastery]

Glad you found it useful.

[@wayneedmondson1065]

Awesome Paul! Thanks for sharing this. Much to study and learn :)) Thumbs up!!

[@Excelmacromastery]

Glad you liked it Wayne.

[@philipmcdonnell7168]

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]

Thanks for sharing Philip! I'm sure you're smarter than you give yourself credit for.

[@jamiewellbourn3609]

@@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?

[@AnilKumar-vi8oe]

Back again... Thanks for the effort

[@Excelmacromastery]

You're welcome.

[@xenzomihamn4278]

This was very helpful. Thank you!

[@Excelmacromastery]

You're very welcome!

[@davidyenni3455]

I really liked your video! Thank you so much!!

[@Excelmacromastery]

Thanks David

[@SMEEST55]

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.

[@Excelmacromastery]

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.

[@davescott7680]

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.

[@Excelmacromastery]

Good point Dave.

[@sastianp]

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!

[@mariodinoia9586]

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

[@kishorekizz8446]

Thank you for this wonderful video :)

This content is awesome!

[@michaelpavlov9493]

thanks a lot, helped to solve some design problems.

[@Excelmacromastery]

Glad it helped!

[@budisantoso1526]

Thanks for the tutorial

[@graytonw5238]

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!

[@Excelmacromastery]

Once it clicks you will see it very clearly.

[@danteencinas7020]

Thank you Paul!

[@SimpleExcelVBA]

Quality content, as always! Even knowing that, it was nice to watch your video :)

[@Excelmacromastery]

Glad you enjoyed it!

[@bellmonica2036]

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?

[@jamesthomas8249]

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?

[@gonthiermichel7874]

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 ?

[@bondniko]

At some point, I started to loose track. Will watch this over again.

[@hermanbroeckx8329]

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?

[@kleinur5960]

Thank you for the great video. I created one class module with 9 columns. After that I copied it to an array with the following command: arr = Dict.Items The array was created but instead of column numbers it shows class names. Unfortunately, UBound(arr, 2) shows “Type mismatch”. Type of array in watch window is also strange. It shows: “Variant/Variant(0 to 5374)” only. I would expect “Variant/Variant(0 to 5374, 0 to 8)”. I can not work after that with the array. Could you tell me what the solution is, please.

[@victor_wang_1]

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?

[@A.P.G.]

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?

[@sastianp]

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.

[@Info-God]

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

[@TriPham-j3b]

Thanks

[@GoodVibes-qg4ko]

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?

[@bondniko]

Hey, what does the pile of old soviet cars signify? They were not particular, but anyway.

[@wattjock2405]

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!

[@marekchudy8893]

Thank You

[@Excelmacromastery]

Welcome!

[@bagnon]

Is there any advantage to using rng(i,1) instead of rng.Cells (i, 1)?

[@Excelmacromastery]

There only real difference is that using Cells allows you to use the With statement. I use Cells everywhere so it is consistent.

[@viveksingh9395]

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

[@joker_g7337]

I usually use a dictionary of dictionaries, but a dictionary of objects is clearer. Is this better than using recordsets?

[@Excelmacromastery]

It's similar to Recordsets but they work a bit differently.

[@dragos_c13]

Hey, could you please inform which Font do you use in the VBA editor?

[@Excelmacromastery]

Consolas

[@dragos_c13]

@@Excelmacromastery Thank you!

[@Monduras]

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?

[@Excelmacromastery]

See docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/compiler-constants

[@deechvogt1589]

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?

[@Excelmacromastery]

There is a Type structure but it has limits when using it with arrays.

[@andgnd3674]

hi, do you have a github account or can recommend some interesting sources to find out new commands

[@Excelmacromastery]

I don't have a github account at this time.

[@jmstampe]

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.

[@Excelmacromastery]

Thanks for the feedback Jonathan.

[@jmstampe]

@@Excelmacromastery absolutely! Big fan of your content. I think a lot of people are unaware of how powerful VBA really is.

[@rickmorenojr]

Couldn't you make a new class that uses a Collection and then add Exists method to the class?

[@edbanz2012]

What is the maxinum rows and columns of the dictionary n collection?

[@Excelmacromastery]

There is no specific limit but it would be subject to memory limits.

[@songokussj4cz]

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.

[@Excelmacromastery]

Good point Son

[@TriPham-j3b]

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

[@andrewscoins5013]

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

[@vuminhduc2011]

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

[@ousmanetall1286]

hi dear paul, do make python series?

[@Excelmacromastery]

I have two Python videos so far. I may do more in the future.

[@georgemathew860]

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…

[@dermotbalson]

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

[@TriPham-j3b]

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

[@tughanozsezer9332]

😍

[@lastdance903]

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