Enums(Enumeration): The Key to Cleaner, More Efficient VBA Code

Рет қаралды 29,256

Күн бұрын

👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: bit.ly/3JjuD0H
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Enums(Enumerations): The Key to Cleaner, More Efficient VBA Code
Enums(Enumerations) are a simple but incredibly useful feature of VBA. Despite their simplicity, most users are not sure how to use them and therefore avoid them. In this video, I will cover everything you need to know about enums such as:
* What are they
* Why You need them
* Examples of how to use them
* A Good naming convention for enums
* How to use them with columns
#ExcelVBA #ExcelVBAEnums #VBAEnums #VBAEnumeration #Enum #Enumeration #Enums #VBA
Table of Contents:
00:00 - Introduction
00:15 - Examples of Enums
00:59 - Built-in Enums
02:03 - How numbering works
04:06 - Simple Enum Example
05:26 - A typical use of enums
06:54 - Using None as a default
07:28 - Reading all the members
08:28 - Using enums for columns
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)

Пікірлер: 126

@robertbendkowski3385 Жыл бұрын

Every time I begin to think I'm getting pretty good in VBA you post another video and my inner voice whispers: you have much to learn, Robert.

@Excelmacromastery Жыл бұрын

Thanks Robert. Glad to provide new insights.

@RanjitKumar-br2ff Жыл бұрын

Same feeling for last 15 years..excel is ocean in itself

@LongLiveHumour 9 ай бұрын

As a new developer only just discovering the power of enums in MS Access, this was immensely clarifying. Thank you!

@grahamc5531 Жыл бұрын

Been using enums for a few years now and so simple,but never knew about the first and last flags....I shall attempt using them soon

@Excelmacromastery Жыл бұрын

They are a well kept secret 😀

@thwbn3993 Жыл бұрын

[_First] and [_Last] are a great new input! Thanks a ton!

@thanhluan4616 Жыл бұрын

So amazing guide to use enum to get data from worksheet based on column name

@peterlilley6819 Жыл бұрын

Enums are a goto when I develop (I also sometimes use the Dictionary object for similar results). Enums are particularly useful when working with dynamically assigned two dimensional arrays. E.g. typically a two dim array would look like Array(1,1) = “Smith”, Array(2,1) = “John” etc. etc. but remembering what the first dimension names are can be difficult, using enums enables the following Array(ecEmp.LastName,1) = “Smith”, Array(ecEmp.FirstName,1) = “John”…. Considerably more readable, oh! And one other thing, use a hidden field like [_Max] in the enum to store the number of the highest assigned enum, this means you will never have to change the redim of the array at initialisation if you initialise the array as: redim array(ecEmp.[_Max]). So if you had to add an extra element like “MiddleName” you would simply adjust the [_Max] value to reflect this and not have to worry about looking for the redim statement!

@Excelmacromastery Жыл бұрын

Thanks for the feedback Peter. I would not set [_Max] and let it be one more than the last member. It's a small thing but it means you don't need to remember to update it when you add a new member. Obviously you need your code to take this into account when using max.

@peterlilley6819 Жыл бұрын

@@Excelmacromastery Thanks Paul, yes I agree, I probably needed to be a little clearer in my comment. I do assign all my enum numbers manually and [_Max] is always the same number as the last unhidden element so my enum would look like ( dash - indicates line breaks) enum ecEmp - LastName = 0 - FirstName = 1 - [_Max] = 1 - end enum.

@edwar368 Жыл бұрын

I have used enums for years. In particular for the purpose of defining the column structures of sheets. We have large number of records in multiple sheets that have many complex validation and reporting requirements. Reading the data into arrays and then processing them and then writing out validation reports is so much easier using enums of the column names as the array index and means that we only ever need to change the enum and not multiple validations if we add or remove columns.

@Excelmacromastery Жыл бұрын

Thanks for the feedback Mark. I always use them for columns. Very convenient.

@robf5684 Жыл бұрын

I learned about Enums on your website a few years ago now, and they really helped my code a lot. I'd written business-critical tools peppered with constants to avoid magic numbers, and was able to convert most of them to structured Enums instead. So thank you!

@Excelmacromastery Жыл бұрын

You're welcome Rob.

@philipmcdonnell7168 Жыл бұрын

I've not used eNums the same as I haven't used Collections, Dictionaries or Class Modules. ENums are the easiest to comprehend now that I've seen your video but I've barely done any VBA in recent months as I've had to just "use" my workbook rather than keep developing it. Funny that today, I started thinking about modding the workbook and then saw your video. :)

@Excelmacromastery Жыл бұрын

Thanks for sharing Philip!

@GodAdministrator Жыл бұрын

The most useful VBA tutorial on the web! Thank you so much!

@Excelmacromastery Жыл бұрын

Glad it was helpful!

@vincentlee7995 Жыл бұрын

Really useful. Thank you so much

@joaocustodio2094 Жыл бұрын

Paul, thank you very much for this video. It allowed me to "see" this feature in a more comprehensive way.

@Excelmacromastery Жыл бұрын

You're welcome

@Akens888 Жыл бұрын

Another good reason to prefix your enum members is the avoid any conflicts with existing functions, for example if you had a enum for orientation you can't use Left or Right as an enum or you will have problems.

@rewatiraman2956 Жыл бұрын

Thanks, I am new to VBA, I will try my best to learn this technique.

@huytruong2026 Жыл бұрын

I have used Enum for a while, but never known about these amazing tricks. Hats off! Thank you so much!

@Excelmacromastery Жыл бұрын

You're welcome.

@jonkirk2118 Жыл бұрын

Another fantastic tutorial! Many thanks.

@Excelmacromastery Жыл бұрын

You're welcome Jon.

@rolf7135 Жыл бұрын

Thank you - a very good video tutorial and a good description. Thanks!

@Excelmacromastery Жыл бұрын

You are welcome!

@tommyfilano3970 Жыл бұрын

Can‘t thank you enough! Your Videos helped me a lot.

@Excelmacromastery Жыл бұрын

You're welcome

@rickmorenojr Жыл бұрын

Excellent tips. Very clearly explained. Thank you.

@Excelmacromastery Жыл бұрын

Glad it was helpful!

@zorglub8949 Жыл бұрын

I just learned something really really useful today... Thanks😀

@Excelmacromastery Жыл бұрын

You're welcome

@houstonvanhoy7767 Жыл бұрын

1,701 views so far on posting day. Your fan club is growing. MVP! Update: 3,267 views.

@Excelmacromastery Жыл бұрын

This topic is a lot more popular than I thought.

@ThorstenStrauch Жыл бұрын

Thank you so much! Important stuff in under 10minutes well explained. Bravo!

@Excelmacromastery Жыл бұрын

You're very welcome!

@Info-God Жыл бұрын

This comes from passion, perseverence (aka: stubborness), desire for efficiency and why not from being in competition with yourself.

@gavdownes100 Жыл бұрын

Another wonderful tool. Thank you so much

@Excelmacromastery Жыл бұрын

You're welcome!

@guillaumepottier6091 Жыл бұрын

Very useful trick. I am pretty sure to use it soon, and to live this feature. Thanks for this very pedagogical video.

@Excelmacromastery Жыл бұрын

You are welcome!

@SimondeLisle-au Жыл бұрын

Thank you so much for this - was wondering how I could use it until the last 60 seconds of the video where you showed how it can be used to replace Column names - brilliant! This will be so useful when first writing code to work with columns as so often I need to insert or delete or move columns around after the code is first written.

@Excelmacromastery Жыл бұрын

You're welcome Simon

@josealvesferreira1683 Жыл бұрын

splendid!

@BenjaminHouot Жыл бұрын

I didn't know about hidden attribute with bracket [], it could be interesting in some code. i will give it a try

@RichardJones73 Жыл бұрын

Oh man, wish I knew this before! Thanks

@Excelmacromastery Жыл бұрын

Happy to help!

@Rice0987 Жыл бұрын

If i understood right Enum is an array of constants you can assign from start. Thanks for lesson!

@djkujo007 Жыл бұрын

Great video

@Pedritox0953 Жыл бұрын

Great video!

@Excelmacromastery Жыл бұрын

Glad you enjoyed it

@johnwayne8059 Жыл бұрын

Hi Paul!✌️ I hope you're doing fine! Thanks for your content!👍👍👍 I use enums very often and I think it can make everything easier!🤟😎

@Excelmacromastery Жыл бұрын

You're welcome John.

@MrChinna143143 5 ай бұрын

Excellent

@AnilKumar-vi8oe Жыл бұрын

Great again, love the content

@Excelmacromastery Жыл бұрын

Glad to hear it Anil

@hadibq Жыл бұрын

That's a proper coding technique using enums 👍 that classifies the values set of a property and makes software design at a higher standard when documented.

@Excelmacromastery Жыл бұрын

Thanks Hadi

@sedataksakal414 Жыл бұрын

Teşekkür ederim. Sade ve güzel bir anlatım.

@Excelmacromastery Жыл бұрын

You're welcome

@mateuszbajko4342 Жыл бұрын

Incredible... !

@Excelmacromastery Жыл бұрын

Thanks 😊

@ryanstraiton2021 Жыл бұрын

I have found enums to be very useful and have also tried numbering them with base2 numbering so that they can be checked against bitwise operations. Always great videos.

@Excelmacromastery Жыл бұрын

Thanks Ryan. Great tip!

@drancerd Жыл бұрын

A great one tip!

@pezad4187 Жыл бұрын

If you use base2, it is limited to 32 items for type Long (32 Bit) and LongLong 64 items (64Bit)

@sinus2220 Жыл бұрын

Very informative video as always. And, as always, there is something new to learn from you even for advanced VBA users (square brackets). Can you provide a bit more info why do these initial and last items have to be specified in that way? Evaluate shorthand comes to mind but doesn't really make sense in this scenario.

@Excelmacromastery Жыл бұрын

The square brackets allow using "foreign identifiers". We can then use the underscore to make the members invisible.

@roelofvanheerden4157 9 ай бұрын

Thank you for the lessons you provide. Its taught me a lot (and continue doing so daily). Just a question about good practice. Is it best to write Enums in subs or, class modules? If it doesn't really matter, then what would be a good approach to decide when to put it where?

@hichamhadj9640 Жыл бұрын

I have used enums in MS access but not as brilliantly as you do. Thank you for all your awesome videos

@Excelmacromastery Жыл бұрын

You're welcome

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

Great

@CollDott 9 ай бұрын

🎉🎉🎉

@plummetplum 3 ай бұрын

Could i write code to search for columns with particular names and assign them to eNums in the same eNum statement?

@RungeCarl Жыл бұрын

I use Enums in Access VBA

@Monkey.D.Dragaon 3 ай бұрын

Is there any way to store String in enum?

@tomharrington1453 Жыл бұрын

Great video. If VBA was kung fu, you would be Jackie Chan.

@drancerd Жыл бұрын

I love you sr! you are the best! A question...: How behave in a large amount of data??? it´s faster than Dict, etc?

@Excelmacromastery Жыл бұрын

It is very fast as it's just a number. Cannot be compared to the dictionary as they are quite different.

@drancerd Жыл бұрын

@@Excelmacromastery Ok, so: it's like a list with variable index 😋

@andrebouve3513 Жыл бұрын

Hi Paul, once a again a great tutorial. Small question. Does it make sense to use enum with tables (the real Excel tables)? And if so, how to refer than to a tablecolumn?

@Excelmacromastery Жыл бұрын

Depends how you use them. You can start the enum at one or at the column the table starts at.

@m_marcamo Жыл бұрын

Paul, can I ask you for a video about "type"? Can you link type whit array or dictionary? thanks in advance.

@Excelmacromastery Жыл бұрын

I'll keep it in mind. Thanks for the suggestion.

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

please, we need a video about a searchable database of high quality VBA code snippets.

@Excelmacromastery Жыл бұрын

I'm working on this at the moment.

@stevetatterton Жыл бұрын

In fact thinking further, even if we don't have churn and we don't use an external table, what does eNum give us that we can't do by coding a function which converts from a readable code (eg "Australia") to a simpler code (eg 4)? So if we called our function ec we would refer to ec("Australia") rather than with eNums we would refer to ecAustralia. How much benefit is that really? I'm thinking if you do use eNums in many cases you might want to explicitly define a different integer value for each entry - i.e. convert the name to the explicit number in every case. This is like a lookup pair for each and doesn't use the implicit calculations of each entry based on the previous entry. Referring to the example in the video, if you specifically want to define Australia as 100 for any specific reason why would you want to assume China would be 101 apart from the fact that's the way enums work? Either these one to one connections are in some way fixed over time and in some way "official" - in which case they should be kept in a permanent file, and can be referred to by multiple callers - or they are just temporary for the purposes of current code - in which case a simple conversion function would apply equally as well. So is there any benefit?

@Manche-De-Pelle Жыл бұрын

Dear friends, I need to make a code to make all the possibilities of many criteria who are undefined. The user will fill all the criteria and possibilities and I want to be able to generate all the possibilities. For exemple we could have criteria1 (jeans, t-shirt, shoe) criteria2 (x-small, small, medium, large, x-large, 2x-large, 3x-large) criteria3 (blue, green, yellow, brown) ... So I would have as first result "jeans x-small blue" than as second result jeans small blue .. and the last one would be "shoe 3x-large brown" I don't know if I should use enums and if enums can be a range from a spreadsheets ??? Probably, I think of an array and how I would be able to get all the data from all the probabilities ??? thanks for your help and your videos !!!

@AbdullaKhizamworld Жыл бұрын

Dear sir, Possible to give a way to do Debtors Preparation by using the VBA? or power BI. Like Invoice where Collection, Refund, Other Adjustment 03 excel sheet to be match to get the Due. 03 criteria when match.

@Excelmacromastery Жыл бұрын

It's possible but a sizable project

@keithturner2889 Жыл бұрын

I use enums extensively but on the very odd occasion VBA reports an error eg enum parameter not found. The solution found was to equate each parameter in sequence starting at zero.

@Excelmacromastery Жыл бұрын

Thanks for the info Keith.

@BloominOnion1 Жыл бұрын

Interesting concept. I see that they are more descriptive representations for numbers. I'm wondering why you wouldn't write your function to accept strings instead of numbers. That way you'd not need to use a eNum input, just input the name directly. Clearly I'm missing something. Very interesting though, thanks for the content.

@edwar368 Жыл бұрын

Multiple nice things about enums: 1. You only need to set the first number element of the enum to a number and the rest follow on in sequence, so changing the order of enums can be done simply and easily in once place. Also having enums as meaningful names that are also numeric, means you can make your loops or accessing elements of arrays or sheets more readable by using the names , for example, looping using "for i = firstcol to lastcol" (but something more meaningful :) )

@Excelmacromastery Жыл бұрын

They are good answers Mark. Another thing is that the intellisence shows you the available member and will give and will give an error if you use an invalid one - similar to how data validation works on a spreadsheet.

@BloominOnion1 Жыл бұрын

@@edwar368 cool, thanks for the info!

@BloominOnion1 Жыл бұрын

@@Excelmacromastery very cool, thanks

@stevetatterton Жыл бұрын

Seems eNums are good for static data, they're effectively a lookup for a code, using for example an integer code to represent a meaningful text as a description. This would I expect be good for something like converting a currency code to a description - it will be relatively static over time. But if you wanted something which would have churn over time - like staff number to employee's name, it would involve changing of your enum definition, every time there is a change - i.e. constant changing of your VBA code. In this case you would presumably need a table in a file which could be maintained externally. Then your code would have to read the table to access the description from the code, this would get the latest data. This is where the use of objects can come in, where a single object for the whole lookup table can have a property with a key value as input argument which looks up the table using the key and returns the description. In fact if your lookup table was for instance currency code to latest exchange rate, this object method would return the latest exchange rate which could be changed on the lookup table constantly over the day. So what I'm suggesting is eNums good for static or relatively static lookups, files for any kind of churn over time.

@gummynut7121 Жыл бұрын

How would you compare Enum to class module? 😃

@Excelmacromastery Жыл бұрын

I wouldn't. They are very different. Check out my videos on class modules if you would like to see an intro to them.

@ragnarok7976 Жыл бұрын

I feel like switches should be part of this. Even in cases (no pun intended) where you don't need the enum like say a numerical error code you'll still run into silly things like verbose if statements. You also lose the conciseness and readability of enums by not switching on them. Perhaps a second part but I don't think it would be two much for one video and the two concepts are tightly linked. Just my 2 cents thought!

@Excelmacromastery Жыл бұрын

Thanks for the suggestion.

@albertoghiglia9566 Жыл бұрын

It's a pity that Enums don't allow a sort of ".ToString" method (in order to print Enum "name "instead of its "value"). In addition, ENums accept only "Long" data types and not "String", "Object", etc ...

@Excelmacromastery Жыл бұрын

ToString would be useful. Other types would be against the nature of enums.

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

2023

@TP-om8of Жыл бұрын

This sounds kind of like classes, kinda.

@Excelmacromastery Жыл бұрын

Not really. Watch again and it might become clearer.

@Info-God Жыл бұрын

GePeTo is mute.

@Excelmacromastery Жыл бұрын

I don't understand?

@Info-God Жыл бұрын

Chat GPT...GePeTo...Pinocchio's father.

@szita2000 Жыл бұрын

I love this video as I am at the stage where I can build stuff in VBA, but my boss ALWAYS want to make change such as add a few columns here and there. Then Ibloody have to rewrite my column references.

@Excelmacromastery Жыл бұрын

Thanks Ernie, It's actually a very common issue in Excel VBA. Column positions change all the time.

@m_marcamo Жыл бұрын

@@Excelmacromastery excel columns and garage columns always move when you are not watching them. Thanks a lot, Paul

@houstonvanhoy2198 Жыл бұрын

It's called iterative development. "I see that you did what I asked. Can you please add a couple of things?" It happens.

@houstonvanhoy7767 Жыл бұрын

It's called iterative development. "I see that you did what I asked. Can you please add a couple of things?" It happens. #IterativeDevelopment

@marianraab2384 Жыл бұрын

Thanks for the video. I didn't know that I could DIM a variable to my own Enum and I don't need to type "eCount.ecXYZ" everywhere. I also didn't know the hidden "[_First]" and "[_Last]" feature. Could you also make a video about the "Like" operator? I use it quiet often in the last time because of the wildcard compare feature. I like to use it in "select case", but I do not like to type my variable which I want compare to every time, maybe you have any idea to avoid it. e.g. Select Case True case sVariable Like "*test*" case sVariable Like "*hello world*" End Select

@Excelmacromastery Жыл бұрын

Thanks Marian. I'll put Like on my list of topics.

@krn14242 Жыл бұрын

Thanks for the video. I have used Enums to create easy way to refer to colors. 'Public Enum Color ' ' Black = 0 'RGB(0, 0, 0) ' Blue = 14390640 'RGB(112, 149, 219) ' DarkBlue = 14423060 'RGB(20,20,220) ' LightBlue = 16764057 'RGB(153,204,255) ' Gray = 12632256 'RGB(192, 192, 192) ' Red = 6118894 'RGB(238, 93, 93) ' White = 16777215 'RGB(255, 255, 255) ' Green = 65280 'RGB(0,255,0) ' Yellow = 65535 'RGB(255,255,0) ' PaleYellow = 13434879 'RGB(255,255,204) ' Pink = 16764159 ' PaleOrange = 11389944 ' PaleGreen = 11854022 ' PaleBlue = 16247773 ' LighterGRAY = 14277081 ' LightGray = 15592941 'RGB(217,217,217) ' LightRed = 6118911 ' ' NHSBlue = 12082688 'RGB(0,94,184) ' RAHeadBlue = 10045468 'RGB(28,72,153) ' RALightBlue = 15849925 'RGB(197,217,241) ' RAGreen = 10807360 'RGB(64,232,164) ' RAYellow = 10092543 'RGB(255,255,153) 'End Enum

@Excelmacromastery Жыл бұрын

That is useful. There is a XlRgbColor Enum in VBA but only for Excel. It has colors like rgbAliceBlue, rgbBrown etc.