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 😀
@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.
@vs_gaming3013
2 ай бұрын
The last example was brilliant! Thanks for that!
@thwbn3993
Жыл бұрын
[_First] and [_Last] are a great new input! Thanks a ton!
@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!
@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.
@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.
@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
@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?
@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.
@rickmorenojr
Жыл бұрын
Excellent tips. Very clearly explained. Thank you.
@Excelmacromastery
Жыл бұрын
Glad it was helpful!
@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.
@zorglub8949
Жыл бұрын
I just learned something really really useful today... Thanks😀
@Excelmacromastery
Жыл бұрын
You're welcome
@thanhluan4616
Жыл бұрын
So amazing guide to use enum to get data from worksheet based on column name
@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)
@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!
@عليعلي-ن1خ3ص
4 күн бұрын
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)) '
@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.
@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
@vincentlee7995
Жыл бұрын
Really useful. Thank you so much
@rolf7135
Жыл бұрын
Thank you - a very good video tutorial and a good description. Thanks!
@Excelmacromastery
Жыл бұрын
You are 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.
@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.
@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 😋
@RichardJones73
Жыл бұрын
Oh man, wish I knew this before! Thanks
@Excelmacromastery
Жыл бұрын
Happy to help!
@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
@BenjaminHouot
Жыл бұрын
I didn't know about hidden attribute with bracket [], it could be interesting in some code. i will give it a try
@tommyfilano3970
Жыл бұрын
Can‘t thank you enough! Your Videos helped me a lot.
@Excelmacromastery
Жыл бұрын
You're welcome
@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.
@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.
@jonkirk2118
Жыл бұрын
Another fantastic tutorial! Many thanks.
@Excelmacromastery
Жыл бұрын
You're welcome Jon.
@gavdownes100
Жыл бұрын
Another wonderful tool. Thank you so much
@Excelmacromastery
Жыл бұрын
You're welcome!
@sedataksakal414
Жыл бұрын
Teşekkür ederim. Sade ve güzel bir anlatım.
@Excelmacromastery
Жыл бұрын
You're welcome
@tomharrington1453
Жыл бұрын
Great video. If VBA was kung fu, you would be Jackie Chan.
@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.
@plummetplum
6 ай бұрын
Could i write code to search for columns with particular names and assign them to eNums in the same eNum statement?
@djkujo007
Жыл бұрын
Great video
@Pedritox0953
Жыл бұрын
Great video!
@Excelmacromastery
Жыл бұрын
Glad you enjoyed it
@shadow_gaming_sk
9 ай бұрын
Excellent
@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 !!!
@Monkey.D.Dragaon
7 ай бұрын
Is there any way to store String in enum?
@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...
@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
@josealvesferreira1683
Жыл бұрын
splendid!
@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?
@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.
@AnilKumar-vi8oe
Жыл бұрын
Great again, love the content
@Excelmacromastery
Жыл бұрын
Glad to hear it Anil
@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.
@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
@mateuszbajko4342
Жыл бұрын
Incredible... !
@Excelmacromastery
Жыл бұрын
Thanks 😊
@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.
@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.
@RungeCarl
Жыл бұрын
I use Enums in Access VBA
@user-pu2zp2ke2l
Жыл бұрын
Great
@CollDott
Жыл бұрын
🎉🎉🎉
@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.
@rrrraaaacccc80
22 сағат бұрын
💯👍
@АлексейСоков-ь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.
@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.
@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.
Пікірлер: 130