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.
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.
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.
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!!
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
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.
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
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.
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 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
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.
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.
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
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.
@@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.
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.
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
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.
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
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?
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.
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.
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.
Hey sir... at minute 8:11 a variable in your code shows "shSales" and for the life of me I can't figure out where it's coming from or if it's some typo... can you enlighten me?
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!!
@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.
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
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.
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
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.
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.
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.
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
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.
@@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...
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
Let me know your experience with User Defined Types. Have you used them and if so, have you found them useful?
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!
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.
First time I come across it, thanks to you Paul.
But i will try to learn how to use them. Sounds powerful.
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.
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.
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!!
That is 12 minutes and 50 seconds packed with useful information and data manipulation techniques. Awesome.
Thanks Tom
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
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.
You're welcome
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
Thanks Chris
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.
Glad the videos helped. Thanks for sharing your experience.
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).
You can also use a dictionary to store the array positions. So you could use a type in the array.
@@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
Just finding your backlog of content and man u r a wizard thank you for sharing your secrets
Glad you like them!
Thank you for sharing and making us smarter ! 😊
You're welcome.
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.
Class modules have their place but UDTs are powerful for specific tasks.
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.
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.
Awesome, good technique, thanks Paul.
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
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.
@@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.
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.
Thanks. Glad you liked it.
Excelente. Você é um fenômeno!
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
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.
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
Lesson very useful, but topic is advanced, I am on my beginning path
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?
Thx alot! Very useful thing!
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 ?
It's hard to compare directly because types cannot be used with a dictionary.
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.
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. :)
Worth trying 😀
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.
Is it better to use custom types or class modules?
Harikasınız. Çok Teşekkür ederim.
new concept^^ thanks
perfect, 11 tousend datasets in less than blink of an eye
oh my! im using vba for autocad and this might just do incredible things for coordinates!
It seems interesting to use vba for autocad. Can I know how you use it, I'm intrigued.
It would very useful for this type of application. It should really clean up the code.
@@greenblits7509 Same question. Following.
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.
Neat this feels kinda like a struct in other languages.
It's very similar
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.
Hey sir... at minute 8:11 a variable in your code shows "shSales" and for the life of me I can't figure out where it's coming from or if it's some typo... can you enlighten me?
sh means sheet, so it's referring to the sales sheet
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!!
I figure out, i have some N/A in my data set. I removed them and now filters fine in any col
@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
@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.
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
@@Excelmacromastery Paul - Thank you for sharing your response. Makes sense!
Incredible!
Thanks
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.
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
Use Static variables
If I have a long text inside the cell....is it possible to control it through a scroll inside the cell?
Could you please make video on realtime update data with best practices?
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.
Can you help me, i need beam optimization program in excel VBA.
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.
That's a very good point. Doing that provides great flexibility.
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.
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
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.
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
Thanks for the feedback John.
@@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...
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
I used years ago with c++ and I remember it being messy.
I wish types could be nested.
Type this01
Type this02
End Type
End Type
That is not allowed in VBA, but would be convenient.
They can be nested but you have to declare them separately
Type Type1
Var1 As String
End Type
Type Type2
Var1 As String
nestedType As Type1
End Type
@@Excelmacromastery Yes. I have done that many times. I did not like having to do it that way.
Great video but I'm having trouble downloading the source code. Just a bunch of exploding rings.
These same the same as Enum
2023