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

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

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 😀

[@thwbn2508]

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

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

[@vs_gaming3013]

The last example was brilliant! Thanks for that!

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

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

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

[@GodAdministrator]

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

[@Excelmacromastery]

Glad it was helpful!

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

[@zorglub8949]

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

[@Excelmacromastery]

You're welcome

[@LPAtkins]

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.

[@thanhluan4616]

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

[@Rice0987]

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

[@rewatiraman2956]

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

[@عليعلي-ن1خ3ص]

Probably one of the most interesting uses of Enums in VB(A) and which has not been mentioned in the video is checking\setting Bits [flag(s)] via bitwise operations: ( Used with lots of Win32 apis such as SeWindowLong\GetWindowLong for setting\getting window styles) EXAMPLE: ======== Enum eFlags A = 1 ' 2^0 B = 2 ' 2^1 C = 4 ' 2^2 D = 8 ' 2^3 E = 16 ' 2^4 End Enum Sub Test() Dim x As Long x = 7 Debug.Print CBool((x And eFlags.B)) '

[@stevescheidjr4632]

I use this technique with settings. Each setting is given a power of 2 enum. The settings selected are added to added together and stored in a single integer or long. To see if a setting was selected, use "if setting and total = setting then..."

[@عليعلي-ن1خ3ص]

@@stevescheidjr4632 That's exactly what the above snippet is doing ... You explained it vey well ... I use this bit ANDing\ORing\XORing operations all the time to manipulate flags\settings particularly with the win32 api - Thank you .

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

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

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

[@rickmorenojr]

Excellent tips. Very clearly explained. Thank you.

[@Excelmacromastery]

Glad it was helpful!

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

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

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

[@vincentlee7995]

Really useful. Thank you so much

[@rolf7135]

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

[@Excelmacromastery]

You are welcome!

[@RichardJones73]

Oh man, wish I knew this before! Thanks

[@Excelmacromastery]

Happy to help!

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

[@tommyfilano3970]

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

[@Excelmacromastery]

You're welcome

[@roelofvanheerden4157]

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?

[@jonkirk2118]

Another fantastic tutorial! Many thanks.

[@Excelmacromastery]

You're welcome Jon.

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

[@gavdownes100]

Another wonderful tool. Thank you so much

[@Excelmacromastery]

You're welcome!

[@BenjaminHouot]

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

[@sedataksakal414]

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

[@Excelmacromastery]

You're welcome

[@Pedritox0953]

Great video!

[@Excelmacromastery]

Glad you enjoyed it

[@djkujo007]

Great video

[@shadow_gaming_sk]

Excellent

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

[@AnilKumar-vi8oe]

Great again, love the content

[@Excelmacromastery]

Glad to hear it Anil

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

[@josealvesferreira1683]

splendid!

[@plummetplum]

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

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

[@Monkey.D.Dragaon]

Is there any way to store String in enum?

[@mateuszbajko4342]

Incredible... !

[@Excelmacromastery]

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.

[@RungeCarl]

I use Enums in Access VBA

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

[@ZAWARUDO-g4k]

is there ANY WAY to get VBA to preserve the casing of Enums ? Because Enums change their Uppercase / Lowercase / Mixedcase if u do it outside of your declaration. And that is really annoying...

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

[@rrrraaaacccc80]

💯👍

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

[@user-pu2zp2ke2l]

Great

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

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

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

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

[@tomharrington1453]

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

[@CollDott]

🎉🎉🎉

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

[@АлексейСоков-ь8и]

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.

[@ErniePantuso]

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

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

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