Excel VBA Introduction Part 26 - Constants and Enumerations (Const, Enum)

welcome to this wise hour tutorial in this video we're going to cover constants and in lumps or enumerations in Excel VBA so the video is all about how you work with fixed values in VBA we'll start with a quick explanation of what a constant is and how it compares to a basic variable then we'll mention some of the built-in constants that you can use before we move on and show you how you can declare and use your own custom constants once you've covered that we'll look at how to use enumerations and again we'll show you some of the built-in enumerations first before showing you how to declare your own custom enumerations the final part of the video will look at how you can using the immolations in variables and parameters of other procedures and we'll finish off by showing you how you can calculate the members or the constants stored in your enumeration so the fair bit to do let's get started in VBA a constant is simply a convenient label for the value which doesn't change there are lots of examples of built-in constants in VBA and you may well have already use them in your own code without realizing so for example if you've ever tried to change the color of a cell you may have used a color constant to do that if I say active cells or interior color equals and I wanted to make it blue I could use the constant VB blue to make that work there it is there's the constant VB blue I can tell that it's a constant because of a little gray box symbol listed next to its name in the intellisense list you can press ctrl + space on the keyboard to display this by the way that also gives you an idea of how many other constants are available every time you see this symbol that represents a constant so is another color constant VB black I'm gonna stick with VB blue for now and then I'm going to run this ability and just to prove that it works not then this is anything particularly exciting but just show that it works and it changes the background color of that cell to the value VB blue although technically it doesn't change it to the value VB blue it changes it to the value but the label VB blue represents if you want to know what values your constants actually refer to there are a couple of different ways you can see that if I use the f8 key to step through this subroutine you can let the mouse cursor linger over the name of a constant and it will tell you in a little what value that constant represents so in this case it's a number a long integer as it turns out and in most cases constants in VBA do represent numbers although technically a constant can represent any data type so for instance string and date and so on another way to see the values of your constant is to use the object browser so if I just reset my subroutine and increase the height of my screen again we can use the object browser from the View menu and we can use that to display a list of constants so I've already scrolled to the one that I'm interested in here VB blue there's an enormous long list of various constants available not all the Living you unless is VB of course the best way to use this is looking at the Global's category on the left hand side and then scrolling little list to find all the constants on anything with this little gray symbol if I go back to VB blue by scrolling down list I've typed in the letter V there just scroll quickly to this section if you select the name of the content you're interested in you can see what its value is by looking at the bottom part of this window to prompt VB blue equals that number so that's an idea of how basic constants work in VBA what we'd like to move onto next is show you how you can design and declare your own constants and then use those in your own code for the first example will declare a couple of constants which holds some custom colors so we've got an example of the old wise L logo with our lovely shade of blue red we're going to do is declare constants which hold references to those colors so what I've done so far just to demonstrate what these colors are is to color in cells using some specific RGB settings so if I just show you quickly what these colors are or at least for the blue one I've used the red green blue color model to specify the exact shade of blue and also for the red use RGB to specify the exact shade of red as well what I'd like to do is create two constants which hold those colors so I don't ever have to specify these exact numbers ever again so back into the VBA editor and I've created a quick little subroutine called declaring constants and let's start by declaring the constant for our wise owl blue now declaring a constant is a lot like declaring a except that instead of the word dim you use the word Const instead so I say Const and I'd like to call my first constant W Oh blue so wo short for wise owl and then I have to say what datatype it will be and in this case it's going to be a long data type so a long integer and a whole number and if that was a variable that would be all that you would do you'd hit enter there and you'd have declared your variable but as you can see with the constant that's not acceptable when you declare a constant you also have to assign a value to it at the same time so after you've declared the datatype you have to say equals and then say what value that constant will reference now the value that I want my constant to reference is the color blue or the Y's L blue and a convenient way to find out what that is is to use the RGB function in VBA I'm going to view the immediate window I'm going to ask the immediate winner to tell me what the value for my Y is our blue color is using the RGB function so I say question mark RGB and the values for Y is our blue will be 0 comma 150 - comment - 0 - if I close the parentheses and then hit enter that tells you that to generate that color I need this specific number so I can copy that from the immediate window pasted him and that's a declaration for white I'll blue I can do exactly the same thing then for Y's I'll read I can say Const wo red as long Eckles and I'll use the immediate window again to ask me to tell me what the red color is and that's going to be 2 0 5 comma 50 1 comma 1 if I hit enter at the end of that line that's the numerical value for y I'll read so I can copy that paste it into the code and there's my two constants declared once you've declared some constants you can immediately use them in code so let's have a quick title in relation to c1 on this worksheet where as c1 that value equals wise owl and then we're going to change the background color of range c1 to wise owl blue so I'll say it up bring c1 that interior color equals and if I press ctrl in space and look for W oh I'll find WI blue six in the list and it has exactly the same symbol there as the built-in constants D so do the open for the background color let's change the font color to Y cell rota cuz that would look lovely I'm sure so dot font color equals wise I'll read and then by again just bringing the window down so we can see what's going to happen in the background let me clear the clothes immediate window just for the moment so I can see this a bit more clearly then I use the FA key to step through you'll see the again if you hover the mouse over your the name of your custom constants they will tell you what value those constants represent just in the same way as built-in constants D so as the background color there's the font color and there's a very very simple use for custom constants now although it's possible to declare constants within a single subroutine it's much more likely than in the real world you'll want to reference your constants either across all the subroutines in a single module or even across all the subs in all the modules in your entire project so if you want to extend the scope of your constants you can declare them outside the subroutines so let's just quickly move our constants from within this subroutine up to the top of the module to begin with so that extends the scope to the module level which means that any subroutine in this entire module can refer to the values of these constants if you wanted to extend the scope even further you can change these constants from module level to project level and you can do that by typing a word public in front of the word Const and that send them into public constants and surprisingly you may well have seen this from India in the variables video you knew exactly the same thing using variables I guess to represent a real-world situation rather than declaring public constants at the top of a single module you will ordinarily have an entirely new module in which all the or public constants and public variables sit so if I paste them in here and then call this module public constants I suppose in the real world I call it public constants and variables because that's porn airily what you put in there but now any subroutine in any module in the entire project can reference our two constants one very important thing to realize about constants and the main thing which distinguishes a constant from a variable is that once you've declared them you can't reassign a value to a constant it's kind of the whole point they have the idea of a constant is that it is constant it's fixed so if I've declared wise our bloons said that it's this specific value I can't change that in other code if I head back to my constants in VBA module and I try to say wo blue equals let's put in the number 1 2 3 4 then although I can write the code that does that as soon as I attempt to run it I'll get a compile error saying that you're not allowed to assign a value to a constant and that's absolutely completely sensible that's exactly what constants are meant or the way constants are meant to work they're a fixed value that does not change the only way I can modify what that constant references is by changing it at its original declaration as well as using constants to set the values of simple properties you can also use them in calculations so to demonstrate that we're going to create a couple of simple functions for calculating the properties of a circle so it's a bit of a flashback to high school mathematics we're going to have a function called circle circumference and this is going to calculate the circumference of a circle from its diameter so we can ask you to pass in the circle diameter as a double and the function is also going to return a double the calculation that will perform and return a value to the circle circumference function is say circle circumference equals circle diameter multiplied by the value of pi so I can remember that to about three decimal places 3.14 one and let's make it a bit more accurate let's cheat and use Google to get a much more accurate value of pi and the Ewing base item then we'll also have a function called circle area and this is going to calculate the area of the circle and surprisingly we need to pass in the circle radius this time and that's going to be a double and if the function is also going to turn a double we can say then that the circle area is equal to the value of pi multiplied by circle radius squared so circle radius x circle radius is one simple way to do that okay so there's the two functions both referencing the same value same constant value that doesn't change between the digits of ratings and it's unlikely to change in any other calculation in which we use the value of pi so rather than using the hard-coded number which is awkward to type in in the first place a constant will be a much better way to do that now this time the constant definitely needs to be referenced in more than one procedure such declare our constant at the top of the module I'm going to call this Const pi as the dates have this sum will be double so not just a long integer as in many other cases and I can pacing that same value again to assign a value to the constant in its declaration all I need to do then is replace any reference to that number with a reference to the value or the constant pi there it sits in the list again with that same little gray box symbol I can do that here again move the number and replace it with reference to PI I suppose we should give our functions a quick test shouldn't we so let's have a quick subroutine called circle properties and we'll ask the user what the circles radius is install that new variables I'm going to say them circ rad as a double I'll have another variable there called dim circ diam also as a double and then we'll ask these what the circle radius is by saying circ rad equals input box or what's the radius we can then calculate the circle Dian by saying Cirque des hommes equals circ rad times 2 nice and simple and then we'll have a message box which displays the circumference of the circle and another one which displays the area so I say message box and we'll say the circumference is and then we'll concatenate that together with the result of the circle circumference function so circle circumference open parentheses and there we'll pass in the value of the Cirque and I am variable another message box M message box the area is and here we'll concatenate the results of our circle area function so circle area and we'll pass in the Cirque rad parameter or value of the circle Vlad variable okay let's step through this one using the f8 key so we can see that we'll ask user what the radius is I'm going to put in know I know 7 for example it's okay and then that will be multiplied by 2 to get the diameter and then we'll call on our circle circumference function and this is going to reference the pi constant and again if I hover the mouse over the value PI it tells me what value is stored in it and this will calculate a value for the second running symbol turn that's to a message box and there's components of the circle and then if we show the next message box that will call in our circle area function again referencing the value pi or the constant pi that calculates the area and returns that to another message box so simple example of using constants in calculated values rather than just setting the values of simple properties earlier in the video we declared a couple of simple constants to hold the values of colors where Y is our bloom wise I'll read but what if we had a larger family of colors that we wanted to be able to reference and we wanted to group them together in some kind of sensible logical way well that's where enumerations come in now just like with constants there are a bunch of built-in enumerations in VBA if I head into my new module here enumerations i'm going to try to change the color of a cell again as a active cell dot interior dots color equals now earlier I looked at the idea of the VB colors VB blue VB black etc there's only eight of those since Excel 2007 there's been a whole other family of named color constants called the RGB colors so I press ctrl + space and said RGB we saw that function used earlier on but you can also see if there's a list of constants here with some wonderful flowery named colors that we could use now all of these RGB colors ratty part of a family an enumeration called excel RGB color you can see it in the intellisense list here whenever you see this little symbol comprising of two small yellow boxes that represents an enumeration and all that enumeration is is a collection of constants so if I say excel RGB color dot that then subsequently shows me a list of all the constants which belong to that enumeration so another way to display blue would be to say excel RGB color dot RGB blue the excel RGB color part is optional I don't need to provide that RGB blue being a constant is acceptable to put that in without the qualification of the enumeration you can see the enumerations in the object browser just as you can with the values of the constants so if I head to the View menu again and choose object browser if I looked for RGB blue in the intellisense list you'll see that that's just another constant and it actually has exactly the same value as VB blue if I wanted to see what the full list of RGB constants was then I can click on these over here where it says member of excel RGB color if I click on Excel RGB color there that takes me to that definition the definition like enumeration in the list if I selected it says enum Excel RGB color and then it shows me on the right-hand side all of the constants which belong to that enumeration and again here you can see you can get an idea of how many different enumerations there are available in Excel VBA so what we'd like to do is declare our own enumeration which holds a custom set of color constants now although you can declare single constants within the scope of a subroutine if you want to declare an enumeration that must be done at the module level so what I'm going to do is head back to my public constants module and I'm going to declare a new enumeration in here I want to make it public so that it can be referenced in any module in this project I'm going to say public enum and then think of a name for my enumeration I'm gonna call mine w/o color in flat I can spell it the proper way can't say as well I call it W color spell properly sorry Americans and then give yourself a couple of blank lines and say end enum and then within the top of the bottom between the top on the bottom there we can declare all the constants which we've part of that enumeration so I'm going to start actually by declaring W blue and then wo red and then W oh I say orange as well and W Brown and we could carry on and on and on we could have as many different constants within this enumeration as we like now I'm going to comment out the to public constants at this point because we're going to redefine wise I'll below and wise I'll read within the enumeration and at this point these constants do have a default value enumerations are based from 0 so if I wanted to have a quick look at what these constants currently reference I can view the object browser again easiest thing to do here is limit the list so you're only looking at things defined in the vba project and that will show you any numeration you've declared with the same little - yellow box symbol and if you select that it shows you a list of all the constants declared within them so I put blue first you see that blue has a value of 0 red has a value of 1 orange has a value of 2 and therefore Brown must have a value of 3 that's not particularly useful because we know that our custom wise all colors have very specific values so within the enumeration itself all you need to do is say what value those colors all those constants should represent so it's quite simple just declaring a whole family of constants in one single place now I haven't calculated what the orange and the brown colors are yet I'm going to display the immediate window and then use my good old RGB function to find out what these are all these are so the Y's L orange will be 240 common 132 comma 30 oops I forgot to put my close parenthesis in there and then I can copy that number out and say wise out orange equals that number and then for Brown the numbers for Brown will be 99 comma 42 comma thirty nine and then that gives me this number which I can copy us again and then assign that number to that constant so if I have a quick look in the object browser again now I can view the rip browser and it should show me now that the values for these colors have now been reset to the ones that I've specified before we use this enumeration it's worth are quickly mentioning one small restriction that you have earlier on we saw that with constants you can use essentially any data types or used double for pi we could also have used string and dates and so on with an enumeration however you're restricted to using the long data type so eventually effectively you're restricted to a number between 0 and about 2.1 for 7 billion but you know you use whole numbers but the constants in enumeration so now that we've declared the enumeration we can use that in normal code so let's head back to the enumerations module I'm gonna have a quick extra new server team called use enum and let's use range d1 this time sometimes they range d1 and dr. value equals wise owl again and then I'm going to use the enumeration to set its foreground and background colors and as a range d1 and darts interior dot color Eagles and if I want to do I can reference any of the constants in the enumeration directly so I could say for instance wise owl orange or W orange now reference that that's constant directly but sometimes it's nicer to use the name of the enumeration first if I say wo color dot and then I can pick from a limited list of the constants that belong just to that enumeration so I can say wo orange sorry wo color dots wo orange and again for the font color I can say range D 1 dot font color equals WL color dots wo Brown so now that I've done that let's have a quick look at stepping through the routine again if I use the fa key to begin stepping through again I can offer the mouse over any constant name in enumeration it tells me what the underlying value is and unsurprisingly all those things will be changed in the same way it looks a bit horrible actually but anyway nevermind that's the idea of using an enumeration is simply a group of constants now one of the really cool things about enumerations is that once you've declared them you can also use them as effectively a data type for other variables so let's say I want to change the way this ability works Ally rather than explicitly stating which color I'll use for the interior and fonts what I'm going to do is store those values in other variables so I'm gonna have dim primary color as Y is our color you'll see that the numeration appears in the intellisense list I also have gonna have dim secondary color as another wise I'll color what I can then do is store which color I want to use in those variables so I can say primary color equals and the brilliant thing is as soon as I type in the equal sign I get the intellisense list showing me only the acceptable values from that enumeration so essentially a validated data type almost so this I'm going to set the primary color to wise are blue I'm going to set the secondary color to be equal to wise L orange let's say what I can then do is instead of referencing explicitly these values I'm going to say that the interior color of the cell is equal to whatever the primary colors and the font color cell will be equal to whatever the secondary color is let's look for secondary color in that list so there we are if I run that subroutine again now we should see if I step through using the fa key they will see the primary color goes to yl blue and the second color my cell orange so we go the beauty of using this sort of system is that let's imagine you were you were doing lots and lots of color changes to lots and lots of individual cells and you wanted to use a particular theme for your colors you might have a whole range of primary secondary tertiary colors if you decided that you wanted to change that theme and you'd referenced each color constant independently to every single time you change the color you'd have to modify lots and lots of different lines of code if you have wanted to change it when you store those values in variables of course there's only one single place you need to change a primary secondary and tertiary colors and then that will affect the rest of the subletting the intellisense feature also works well with enumerations when you're passing them as argument two different procedures so what we'll do here is we'll create a couple of calculations to work out how much people get paid and we'll do it using an enumeration which works out how many hours there are in each period of time how many working hours there are it's going to start with the public enumeration called wo work hours and in there we're going to have a few values we're going to start with the wo day so the working day for wise L is going to be eight thousand eight hours then we'll have W a week which is the same as five times 8 so five day working week which is equal to 40 and then I have a wo month which is a little bit more rough and ready but let's assume it's a twenty day working month so I'll say that the number of hours is going to be hundred and sixty okay so that's Union relation declared now our creative function which uses that enumeration is one of its parameters so let's have a function called wise I'll pay and in there we'll pass in two different parameters will have the hourly rate so I'll just let the user put that in as they see fit hourly rate as currency and then the second parameter will be unit of time and for that we're going to use our enumeration wo work hours we're going to say that the function returns as currency and then in there we can perform a very simple calculation let me just drag the screen across we can see that bit more clearly what we'll do is all return wise I'll pay is equal to hourly rate multiplied by unit of time so whichever value gets passed in via that parameter now what we need to do is call that function and pass in a couple of values to make it calculate the result to do that will create a new subletting called calculate my pay it's fairly obvious what else is going to do I mean there will have a variable called dim pay per hour as currency and then we're going to ask use of what their pay per hour is using number box will say pay per hour equals in the box and then say what's your hourly rate what we can then do is work out three different values we look at the pay per day pay per week and pay per month by calling our wise I'll pay function three separate times so that's of a message box which displays your daily pay is and call our wise I'll pay function source a white I'll pay and the first parameter we're going to pass in on first argument going to pass into the hourly rate parameter it's going to be pay per hour whichever the user types in then when we type a comma here's a great bit you can once again see the intellisense list so we're going to use the wo day value for the for the y's are they constant and then we can do the same thing again I'm going to copy and paste outlining of those zones and then say weekly pay rather than Ali Bey and then monthly pay should be a bit more often ready and we can simply modify these units so rather than using my cell day for that one we'll type in a comma and use wise our week instead and then rather than using wise either eyes all day there retype the comma and select wise our month okay so now that we've done that let's run this one okay let's step through it using the faq actually so it starts typing to using f8 how will the rate of 108 pounds fifty so eight point five click OK and we'll call our function using the unit to time which will be eight because we're passing in a wise all day so there's unit time is 868 pounds per day week they'll be forty because that's what the wise our week constant references and finally wise our month 160 and that gives us another answer for that so there's the beauty of using enumerations for variables and as parameters for other procedures now on small problem with our enumeration as it stands is that if we ever need to change the number of hours in a working day then we've also got to update a week and the month now that should be fairly unlikely to happen again the idea of using constant over an enumeration in the first place is you're storing values that don't traditionally change but there is the potential for in this particular example at least for these values to be to be modified so let's say for example I want to change the day to a seven-hour working day that means the week's got to go to 35 and the month it's got to go 240 which is a little bit tedious so instead of actually typing in the exact numbers once you've declared one constant in an enumeration you can then refer to that constant again for later values so I can say W a week equals rather than number 35 I can make it equal to W ok x 5 and likewise for month I can say wo day x 20 or indeed W a week x 4 it would be the same thing so once I've done now let's step through this calculate my pace subroutine again using the fa key I'm going to put in the same hourly rate which is 82.5 and then I'm going to call my wo day from my wife I'll pay function is I'm passing in the wild a constant which you can see already is the number seven so that calculates the unit of time using the value seven and then for the second one Y is our week you can see it's already calculated Y is our week is 35 so 7 multiplied by 5 so we'll get the result for that and likewise for month that's days 7 multiplied by 20 which is 140 and so unsurprisingly you get the correct answer once again if you're going to use this technique to calculate the values of constants in an enumeration it's important to realize that the values you reference him must be other constants so for example wo day doesn't actually have to be part of this enumeration for it to work what I could do is say public const wo day equals 7 and then comment this out from the enumeration so it's only declared it's to collect separately and then this ability will work in exactly the same way if I run it just using the f5 key and type in a value exactly the same answers again what I can't do however is make this a variable so it must be in contact can't say public w/o day as long and then in my subroutine itself I couldn't say for example wo day equals seven if I try to run this one now it will fail immediately with a compile error saying that I must use a constant if I'm creating a basic expression in my enumeration declarations so just to be aware of the fact that it must be a constant that you're declaring there so I can remove this one comments W a day and read that line as well and everything we're working in if you've enjoyed this training video you can find many more online training resources at ww-why Zeljko UK

Channel: WiseOwlTutorials

Views: 53,853

Rating: 4.9727893 out of 5

Keywords: Visual Basic For Applications, Microsoft Excel (Software), constant, enum, enumeration, wise owl

Id: d8cfrdUjcuw

Length: 32min 1sec (1921 seconds)

Published: Mon Mar 17 2014