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!
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.
@@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.
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.
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. :)
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!
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.
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.
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.
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.
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.
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?
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.
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?
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...
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.
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 !!!
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?
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.
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.
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 :) )
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.
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!
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 ...
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.
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
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.
Thanks Robert. Glad to provide new insights.
Same feeling for last 15 years..excel is ocean in itself
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
They are a well kept secret 😀
As a new developer only just discovering the power of enums in MS Access, this was immensely clarifying. Thank you!
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!
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.
@@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.
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.
Thanks for the feedback Mark. I always use them for columns. Very convenient.
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. :)
Thanks for sharing Philip!
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!
You're welcome Rob.
The last example was brilliant! Thanks for that!
[_First] and [_Last] are a great new input! Thanks a ton!
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.
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.
The square brackets allow using "foreign identifiers". We can then use the underscore to make the members invisible.
1,701 views so far on posting day. Your fan club is growing. MVP! Update: 3,267 views.
This topic is a lot more popular than I thought.
I have used Enum for a while, but never known about these amazing tricks. Hats off! Thank you so much!
You're welcome.
I just learned something really really useful today... Thanks😀
You're welcome
If i understood right Enum is an array of constants you can assign from start.
Thanks for lesson!
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.
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.
Thanks Ryan. Great tip!
A great one tip!
If you use base2, it is limited to 32 items for type Long (32 Bit) and LongLong 64 items (64Bit)
Paul, thank you very much for this video. It allowed me to "see" this feature in a more comprehensive way.
You're welcome
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.
You're welcome Simon
The most useful VBA tutorial on the web! Thank you so much!
Glad it was helpful!
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?
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.
Thanks Hadi
So amazing guide to use enum to get data from worksheet based on column name
Thanks, I am new to VBA, I will try my best to learn this technique.
Excellent tips. Very clearly explained. Thank you.
Glad it was helpful!
Oh man, wish I knew this before! Thanks
Happy to help!
Very useful trick.
I am pretty sure to use it soon, and to live this feature.
Thanks for this very pedagogical video.
You are welcome!
I didn't know about hidden attribute with bracket [], it could be interesting in some code. i will give it a try
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!🤟😎
You're welcome John.
Really useful. Thank you so much
Thank you so much! Important stuff in under 10minutes well explained. Bravo!
You're very welcome!
This comes from passion, perseverence (aka: stubborness), desire for efficiency and why not from being in competition with yourself.
Teşekkür ederim. Sade ve güzel bir anlatım.
You're welcome
I have used enums in MS access but not as brilliantly as you do. Thank you for all your awesome videos
You're welcome
Thank you - a very good video tutorial and a good description. Thanks!
You are welcome!
I love you sr! you are the best! A question...: How behave in a large amount of data??? it´s faster than Dict, etc?
It is very fast as it's just a number. Cannot be compared to the dictionary as they are quite different.
@@Excelmacromastery Ok, so: it's like a list with variable index 😋
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
That is useful. There is a XlRgbColor Enum in VBA but only for Excel. It has colors like rgbAliceBlue, rgbBrown etc.
Could i write code to search for columns with particular names and assign them to eNums in the same eNum statement?
Great video. If VBA was kung fu, you would be Jackie Chan.
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?
Depends how you use them. You can start the enum at one or at the column the table starts at.
Can‘t thank you enough! Your Videos helped me a lot.
You're welcome
Another fantastic tutorial! Many thanks.
You're welcome Jon.
Paul, can I ask you for a video about "type"? Can you link type whit array or dictionary? thanks in advance.
I'll keep it in mind. Thanks for the suggestion.
Excellent
please, we need a video about a searchable database of high quality VBA code snippets.
I'm working on this at the moment.
Great video!
Glad you enjoyed it
Great video
Is there any way to store String in enum?
Another wonderful tool. Thank you so much
You're welcome!
I use Enums in Access VBA
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...
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.
Thanks for the info Keith.
splendid!
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 !!!
Incredible... !
Thanks 😊
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?
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.
It's possible but a sizable project
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.
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 :) )
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.
@@edwar368 cool, thanks for the info!
@@Excelmacromastery very cool, thanks
How would you compare Enum to class module? 😃
I wouldn't. They are very different. Check out my videos on class modules if you would like to see an intro to them.
Great again, love the content
Glad to hear it Anil
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!
Thanks for the suggestion.
Great
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 ...
ToString would be useful. Other types would be against the nature of enums.
🎉🎉🎉
2023
This sounds kind of like classes, kinda.
Not really. Watch again and it might become clearer.
GePeTo is mute.
I don't understand?
Chat GPT...GePeTo...Pinocchio's father.
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.
Thanks Ernie, It's actually a very common issue in Excel VBA. Column positions change all the time.
@@Excelmacromastery excel columns and garage columns always move when you are not watching them.
Thanks a lot, Paul
It's called iterative development. "I see that you did what I asked. Can you please add a couple of things?" It happens.
It's called iterative development. "I see that you did what I asked. Can you please add a couple of things?" It happens.
#IterativeDevelopment
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
Thanks Marian. I'll put Like on my list of topics.