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

@Excelmacromastery

Жыл бұрын

Thanks.

Excellent tutorial. Love the combination of the dictionary and array in the last example. Brilliant.

@Excelmacromastery

Жыл бұрын

Thanks. Glad you like it.

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.

@Excelmacromastery

Жыл бұрын

Thanks Faan. 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!

@Excelmacromastery

Жыл бұрын

Thanks Robert

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

@Excelmacromastery

Жыл бұрын

You're welcome Alex

I look forward to your uploads more than any other youtuber. Thank you!

@Excelmacromastery

Жыл бұрын

I appreciate that!

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.

@Excelmacromastery

Жыл бұрын

You're welcome Joe

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.

@Excelmacromastery

Жыл бұрын

Glad you like it Tom.

Thanks for your work Paul! Your vids are always truly helpful!👍👍👍🤟😎

@Excelmacromastery

Жыл бұрын

My pleasure John!

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.

@Excelmacromastery

Жыл бұрын

Thanks Richard, glad you like it

Thank you very much Mr. Paul. Please, we need more about VBA for Trading.

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?

Great job. Thanks for this tutorial!

I definitely need to dive deep into these subjects. Thank you for sharing this valuable content.

@Excelmacromastery

Жыл бұрын

You're welcome Jan

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.

@Excelmacromastery

Жыл бұрын

You're welcome Karl.

Thanks for the video, well presented and clearly explained!

@Excelmacromastery

Жыл бұрын

You're welcome Nic

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).

You are amazing. Thank you so much. Thanks to you, I am using VBA fast. Greetings from Turkey :)

@Excelmacromastery

Жыл бұрын

Thanks

This type of Teaching especially VBA , Never before seen.............. Great Sir😲😲😲

@Excelmacromastery

6 ай бұрын

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!

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. :((

Being a dead language, VBA still gets the work done in so many work spaces. Thanks to you, kind sir!

@houstonvanhoy2198

4 ай бұрын

"The most productive corpse on the team." (Attributed to a MS Access user.)

Thx a lot.

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.

@Excelmacromastery

Жыл бұрын

True but lBound is always 1 for a range array.

Super ❤

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

This is valuable information to optimize processes. Thank you very much. Excellent video.

Fantastic

@Excelmacromastery

Жыл бұрын

Thanks Jose

Thank you for the video. It is difficult to understand the logic behind the existinarray function to extract the unique list using array.

Would you have to keep nesting for loops to set criteria on multiple columns?

Thanks s....

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

@luisflavioaquino

Жыл бұрын

Would be very nice he makes a video comparing the new array functions to pure array!

@Excelmacromastery

Жыл бұрын

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.

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?

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.

@Excelmacromastery

Жыл бұрын

Thanks for sharing!

cool

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.

@Excelmacromastery

Жыл бұрын

It won't work. You cannot update the array within the Dictionary.

I want to generate Standard meteorological week number from specific date

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

Do you have community to answer excle vba beginners queries? Or do you gave online courses to learn vba for beginners

Could you use the custom "Type" rather than the array?

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.

@Excelmacromastery

Жыл бұрын

Make sure to remove any popup blockers

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.

For example 4. Is it faster to use array than class?

It is very useful video but getting the Unique values using Arrays was explained very fast.

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.

@Excelmacromastery

Жыл бұрын

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.

@tomamore3

Жыл бұрын

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

can the Simulator Trading using Excel VBA also work in Access app?

@Excelmacromastery

Жыл бұрын

Not easily because it is written specifically for Excel.

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?

How to solve active x command become picture

I keep getting an error in the existsinarray function at the for i = 1 to current row

@Excelmacromastery

Жыл бұрын

What is the error?

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?

@Excelmacromastery

Жыл бұрын

You need to use Redim when resizing with variables.

@lucasf.v.n.4197

Жыл бұрын

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

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

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

@Excelmacromastery

Жыл бұрын

You can see how to filter an array in this video at 3 minutes(kzread.info/dash/bejne/lYl2j6-ShM-qhbg.html)

@hajajahirhussain6814

Жыл бұрын

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1) End Function

@IncrediPaulAZ

Жыл бұрын

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. 🍻

@robertbendkowski3385

Жыл бұрын

@@hajajahirhussain6814 I believe filter function only works with one-dimensional arrays so your function won't solve every case.

@Excelmacromastery

Жыл бұрын

The VBA filter function is very slow- otherwise that would work.

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

@Excelmacromastery

Жыл бұрын

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

@Excelmacromastery

Жыл бұрын

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