How to use Custom Types To Write Ultra Fast VBA code

👉 Ready to master VBA?
(Note: If the download page doesn't work then make sure to turn off any popup blockers)

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

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

[Excelmacromastery]

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

[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!!

[RichardJones]

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

[tomharrington]

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

[mjohnstone]

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.

[SaberTooth]

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.

[mching]

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.

[mjohnstone]

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.

[free]

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.

[chrisklimantiris]

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

[jom]

Awesome, good technique, thanks Paul.

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

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

[KM-comx]

Thank you for sharing and making us smarter ! 😊

[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

[raymondsusing]

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?

[matthiasriehn]

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

[maksim]

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

[gerdamft]

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.

[bennwalton]

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