How to use Custom Types To Write Ultra Fast VBA code

Рет қаралды 28,855

Күн бұрын

How to use Custom Types To Write Ultra Fast VBA code
One of the powerful features of VBA is the ability to create user-defined types. User-defined types, also known as custom types, are data structures that can hold multiple pieces of data of different data types. They allow developers to create their own data types that are tailored to the specific needs of their application.
By defining custom types, developers can organize related data into a single variable and manipulate it as a unit. This makes code easier to read, write and maintain, as it simplifies the code and reduces the risk of errors. User-defined types can be used to create complex data structures and objects that are not available in the built-in data types of VBA. This is especially useful when developing large-scale applications or add-ins that require custom data types to perform complex operations.
#ExcelVBA #ExcelVBAEnums #VBAEnums #VBAEnumerators
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE SOURCE CODE FOR THIS VIDEO: bit.ly/4127hDh
Related Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + L 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)
Table of Contents:
00:00 - Introduction
00:27 - What is a User Defined Type
01:20 - A simple Type example
03:18 - Where Types cannot be used
03:48 - How to use Types with arrays
06:04 - Real-World example intro
07:21 - Example - reading the data
09:51 - Example - writing data to the sheet
12:21 - Speed: Comparing Type and Class Modules

Пікірлер: 92

@Excelmacromastery Жыл бұрын

Let me know your experience with User Defined Types. Have you used them and if so, have you found them useful?

@EricHartwigConsulting Жыл бұрын

I have not used user defined types before but I am very much looking forward to trying them out after seeing this video! Great demonstration!

@greenblits7509 Жыл бұрын

I've learned about types before class modules. When I was introduced to clsMods I thought "why use those instead of types". Today I learned that types don't work with collections and dictionaries. Aside from that I wouldn't know why else I would use the clsMods when types are available and (in my opinion) more readable.

@chrisklimantiris2520 Жыл бұрын

First time I come across it, thanks to you Paul. But i will try to learn how to use them. Sounds powerful.

@yousajtube Жыл бұрын

User Defined Types are useful for I/O with those Windows APIs that read or write C struct types. I would say that they are mostly a feature of earlier VB(A) days. Nowadays Excel devs interact mostly with data stored in ranges, tables and data models where UDTs are less helpful. Still good to know.

@stefanogattoCH Жыл бұрын

Yes, I do use Types when I deal with records coming from a database (using DAO or ADO in Excel VBA). The only thing is that by default fields of a database table can be set to NULL, while VBA classic types (Long, String, Boolean,...) DO NOT accept NULL, except Variants. So these 2 creatures from these 2 worlds are opposite one to each other and this leads me to define all fields of my VBA Types to Variant instead of a classic type, if I want records from those Types to hold data from an Access table. So this is true for all db fields, except for mandatory ones (NOT NULL), like primary keys.

@FernandoMaltaFencas Жыл бұрын

I have just tried using User Defined Types for the first time and my god, man, how this thing is fast! I used to had a code to read and consolidate ~3k rows and were using For Next in a databodyrange; it was taking about 15s to finish all the code. Then I switched to arrays and it got way faster, taking about 6s to run through the code. Then last night I watched this video of yours and gave it a try to use User Defined Types (with arrays) instead... 0.2s!!! I mean, WOW! It is almost instantaneous! Thank you very much for this brillant video and keep sending more of that!!

@tomharrington1453 Жыл бұрын

That is 12 minutes and 50 seconds packed with useful information and data manipulation techniques. Awesome.

@Excelmacromastery Жыл бұрын

Thanks Tom

@mjohnstone2399 Жыл бұрын

UDT's with the LSet command have come in very handy for casting numeric types to larger numeric types without VBA conversion for negative values. I.e. Just it just copies the binary value. Now there's an advance topic for the use of UDT's as can only use LSet with UDT's. I'm developing a UInt64 and UInt32 and didn't want VBA attempting to automatically convert "negative" values for unsigned types. Could use Memory Copy API of bytes but you take a performance hit verses UDT and LSet.

@RichardJones73 Жыл бұрын

I've used types written by other people and I thought they were complicated but this video made it feel quite easy. Hopefully it will be when I try it out soon

@KM-co5mx Жыл бұрын

Thank you for sharing and making us smarter ! 😊

@Excelmacromastery Жыл бұрын

You're welcome.

@chrisklimantiris2520 Жыл бұрын

Hi Paul. Thank you for sharing valuable knowledge with us. You are by far the best guide in this vba journey. Compact and comprehensive. Appreciate it Paul Never he

@Excelmacromastery Жыл бұрын

Thanks Chris

@SaberTooth2251 Жыл бұрын

They've been wildly useful for me, and I learned the basics from this Channel No Doubt. Combined with arrays, the flexibility in changing the format of how information is displayed in a printout is taking the standard table filter functionality to the customizability that VBA provides. Whatever information I need to keep track of, I just throw the word my in front of that category of information, and that effectively functions as a new column in such a filterable table.

@Excelmacromastery Жыл бұрын

Glad the videos helped. Thanks for sharing your experience.

@free3690 Жыл бұрын

I've played with UDT a few times but never had a chance to use it like how you did in the video. The benchmark at the end was nice. Thanks for the insights.

@Excelmacromastery Жыл бұрын

You're welcome

@Husky_Passion Жыл бұрын

if you still want to use "types" in dictionnaries, the way to do it is by creating a class , and delare those variables there. If you're not familiar with dictionaries and classes, it may seem confusing at start, but in the end it's even faster. PS : arrays are still more stable , as excel is not perfect (for really big codes it gets confusing sometimes for the programmer, and multilayered dictionaries(with classes) often times bug, but arrays+types won't, strangely enough).

@Excelmacromastery Жыл бұрын

You can also use a dictionary to store the array positions. So you could use a type in the array.

@Mukdeni Жыл бұрын

@@Excelmacromastery Thank you Paul, could you please make a video on that? I am interested in learning how to add UDT to a dictionary. Currently I am using class modules, but feel it's slow. Would be great to know how the same can be achieved with UDT

@mching4473 Жыл бұрын

I don't write VBA anymore but I wish I knew this! I was so focused on class modules for implementing real world object representation.

@Excelmacromastery Жыл бұрын

Class modules have their place but UDTs are powerful for specific tasks.

@mjohnstone2399 Жыл бұрын

I always use a private UDT for each class and a this variable to access class variable members. It makes for cleaner and clear code. I think has additional advantages for serialization of a class thou haven't got around to playing around with serialization of classes.

@jom3211 Жыл бұрын

Awesome, good technique, thanks Paul.

@marcellino53 2 ай бұрын

Excelente. Você é um fenômeno!

@alexcrown497 Жыл бұрын

Thx alot! Very useful thing!

@BenjaminHouot Жыл бұрын

Also, if maybe you can show us how to use or to create a colorpicker dialog. i spend more than 1 to 2 hours to find something. but the result is not as good as i can imagine. chatgpt provide examples that doesn't work. or i have to use other COM components that not every one have on his computer. i finally got something with google but the dialogs is not perfect.

@TheJaebeomPark 5 ай бұрын

new concept^^ thanks

@sedataksakal414 Жыл бұрын

Harikasınız. Çok Teşekkür ederim.

@jerkorulez Жыл бұрын

Hi Paul, I've been following your videos for quiet some time now. Great work🎉. Wondering if you are thinking to do class + arrays. In general, I'm almost always prompt to think about arrays just based on their speed to printing back to excel. That's why I would like to combine them with class modules in vba. Thanks

@aNDy-qh1em Жыл бұрын

Thank you Paul, a great tutorial! IMHO ising UDF in arrays will definitively be efficient to fill listboxes as contrasted to filling the latter with collections/dictionaries of class instances. Also CallByName function should work with UDF properties.

@Excelmacromastery Жыл бұрын

Thanks. Glad you liked it.

@bennwalton Жыл бұрын

Just finding your backlog of content and man u r a wizard thank you for sharing your secrets

@Excelmacromastery Жыл бұрын

Glad you like them!

@raymondsusing5090 Жыл бұрын

Hi Master! I would like to commend you for being an amazing tutor for VBA!! Had watched your Data Entry Tutorial and it helped me a lot to create my own tracker, but I would like to ask for your help. You might want to show us how to create a separate search form that can be linked to your data entry form. Search form with combo boxes criterion based on the header column and it can display the result into listbox and textbox. Gracias?

@DM-py7pj Жыл бұрын

Interesting thanks. I really don't make enough use of Type. I can't recall the last time I used them in fact. I'd still prefer oversizing an array initially with a single redim later over the expense of repeated redim preserves within a loop. Though I might have missed a reason for this choice. Prolly worth mentioning you can only redim the rightmost dimension as well.

@Excelmacromastery Жыл бұрын

If you oversize the array you need to copy everything to the new array when using types. Redim Preserve is actually quite fast so not much difference in the speed

@maksim0933 Жыл бұрын

Lesson very useful, but topic is advanced, I am on my beginning path

@gerdamft9099 Жыл бұрын

Great video. I wonder if you'd recommend using user defined types to store time series data i.e. open, high, low, close, volume in a 2 dimensional array for a large number of assets. I'm experimenting with doing this with UDFs and wonder if you think it's a good way to store data to backtest trading strategies.

@mgodek7092 Жыл бұрын

Thanks a lot for sharing. I've learnt so much from Yours videos. I have one question,.Is type faster than class module with dictionary ?

@Excelmacromastery Жыл бұрын

It's hard to compare directly because types cannot be used with a dictionary.

Жыл бұрын

Awesome material, thank you so much! Quick question. Can we have this method on minute 00:06:15 when the data comes in different col index each time is read. Meaning I often extract thousands of data from a system as a report but same column often is in position 10 and changes to position 12. it is hard to create some VBA code when the columns are not ordered in the same position each time. I appreciate the help.

@gexcel Жыл бұрын

Incredible!

@Excelmacromastery Жыл бұрын

Thanks

@praveenkumawat2662 Жыл бұрын

Could you please make video on realtime update data with best practices?

@matthiasriehn2902 Жыл бұрын

perfect, 11 tousend datasets in less than blink of an eye

@BenjaminHouot Жыл бұрын

I used it 10 years ago, now i only use class module. But i never check about the speed. So i might reconsider my position. :)

@Excelmacromastery Жыл бұрын

Worth trying 😀

@TSSC Жыл бұрын

Thanks for an interesting video. Two pieces of feedback from me: 1. What’s the time unit in the performance comparison table (12:20)? I guess ms. 2. Type surely is quicker at 200k/20k, but it would be interesting to see how the different methods perform with less or more data: linear, exponential or other pattern. E.g. a test with 2k/200, 20k/2k and 200k/20k

@Excelmacromastery Жыл бұрын

1. milliseconds. 2. I think Type will be quicker than Class Modules for this type of application because of the extra overhead to create a Class Module instance.

@TSSC Жыл бұрын

@@Excelmacromastery Thanks. I was thinking that if the overhead is a one-off then the percentage difference will be greater the less data that is processed. Hence, an advantage for the shown method.

@ragnarok7976 Жыл бұрын

Neat this feels kinda like a struct in other languages.

@Excelmacromastery Жыл бұрын

It's very similar

@ea6051 Жыл бұрын

oh my! im using vba for autocad and this might just do incredible things for coordinates!

@greenblits7509 Жыл бұрын

It seems interesting to use vba for autocad. Can I know how you use it, I'm intrigued.

@Excelmacromastery Жыл бұрын

It would very useful for this type of application. It should really clean up the code.

@houstonvanhoy7767 Жыл бұрын

@@greenblits7509 Same question. Following.

@MyaAr-nb8tf 8 ай бұрын

Thank you very much for all your videos. The download site does not work. And I hope you give much more different examples to your explanation.

@theexcelproject6973 9 ай бұрын

Is it better to use custom types or class modules?

@BeniZeBest2011 Жыл бұрын

I use them a lot!! Sometimes too much, especially encapsulated user defined type....It's the trap!!! But you can manage by not abuse encapsulated UDT

@Excelmacromastery Жыл бұрын

Thanks for the feedback John.

@BeniZeBest2011 Жыл бұрын

@@Excelmacromastery thank you for your vids. As aVBA programmer, in my very everyday job tasks I use user defined types and rarely class I think the only class I have is a kind of dynamic array-like classto kind of "automatically" get the bounds of a variant array or stufflike that... Keep on the good work, we always need to refresh our knowledges...

@mariotejas Жыл бұрын

I recreated your code and works fine when i am reading from data(i,1) ="apple", but if i change the 1 for any other number i get type mismatch for example data(i,3) = "something" fails. Thank you very much for your videos!!

@mariotejas Жыл бұрын

I figure out, i have some N/A in my data set. I removed them and now filters fine in any col

@AttackOnTyler Жыл бұрын

@mariotejas you can wrap your Excel functions in iferror(existing_function,"") or 0 if you may need a value in the cell for another column

@SaberTooth2251 Жыл бұрын

Say, if you're looking for topics to cover, I would be mighty interested an exploration of msxml, the Microsoft XML core services reference\ Library. The Dom document model seems wildly powerful, but trying to structure a system to decompose, edit, reconstruct is proving to be a jungle of a mess

@Excelmacromastery Жыл бұрын

I used years ago with c++ and I remember it being messy.

@AttackOnTyler Жыл бұрын

You shouldn't need to create a new class if you're setting an array value to the class. You just need to instanciate the class above your for loop. This should speed things up a bit. Seems storing a udt in a collection or dictionary sets the value to the type reference and not the value at the time of adding, so a new class is the only route there.

@technologyfuture1914 Жыл бұрын

@Paul - This is good but why don't you mention that Types are very much limited, especially when compared with Class Modules. What UDT does is just a tiny piece of Class Modules' potential! Class Modules can deal with Procedures, Functions, and Properties, which Types (UDT) simply cannot. The reason UDT is speed is that the memory is allocated when they are declared, but in the case of Class Modules we need to use the word Set and that is when the memory is allocated.

@Excelmacromastery Жыл бұрын

I did cover what they are. I have covered Class modules in depth in other videos. There's not much to be gained by comparing them. Custom Types are simply a way of grouping variables. Class modules are used to provide better design for an application

@technologyfuture1914 Жыл бұрын

@@Excelmacromastery Paul - Thank you for sharing your response. Makes sense!

@yeahnick4260 8 ай бұрын

hmm. Here my experience right after this video: with my "old" Code (just Using two Variant Arrays, wich compare to each other) my code just runs in around 125ms with 3700 rows record with the Type-Array it run for 164ms, so it was actually slower. I wrote that whole code like you teached us in this video, but my Type has 5 properties instead, which were all used. maybe thats why it is slower? I dont know.

@yeahnick4260 8 ай бұрын

ok now i figured it out. Its because it needs extra time to transfer the Data from the Input-Array into Type-Array and back again into the Output-Array while both methods do the same amount of work

@barry9007 Жыл бұрын

hi, i used "types" for a while, and i change to class module now. "types" is good, and seem a little bit faster than class module. But when no any "types" code in file, i realize a matter that i dont know why is cause by "types". The matter is even i already saved the file, when i close file, it always prompt to save. and the code editer keep showing the file i opend . can you tell me why ? thank you.

@dermotbalson Жыл бұрын

Nice video, but I think you missed a fantastic feature. Suppose you have a product type, and each product will have a list of locations, varying in number. You can define location as an item in the product type, as an empty array (ie empty brackets), then, when you fill the product data, you can redim each product's location to the correct size, giving you the ability to have a different size location array for each product - something normally achievable only with classes.

@Excelmacromastery Жыл бұрын

That's a very good point. Doing that provides great flexibility.

@SatishKumar-mr1ng Жыл бұрын

Hi, I need your help on variable. I have one main excel file which open another excel file and run a code in it. The second(there will be 5 such file) will open a file, path of which is saved in 1st file. I tried to use public variable also but it is not getting passed to another file as scope of variable is at workbook level Anyway to do this in vba? Thanks in advance

@alexcrown497 Жыл бұрын

Use Static variables

@npam1559 6 ай бұрын

Can you help me, i need beam optimization program in excel VBA.

@hammeedabdo.82 Жыл бұрын

If I have a long text inside the cell....is it possible to control it through a scroll inside the cell?

@user-tq7yz9xb8g Жыл бұрын

Hello, Paul. Do you think it is possible to replace vba and excel open sourse software as libra/open office. In our country (mordor or imperial or smthg else) our goverment would like to do it.