• # Creating Your Own Constants For Functions

One of the items that is very useful in code is the use of constants. These items are simply text masks that translate to numbers, making it easier for us to remember how to interact with the code.

For example, if you put "? vbOkOnly" in the immediate window, you'll see that it resolves to 0. Likewise, "? vbYes" resolves to 6. It's much easier to remember the words than the numbers, which is why Enumerations (or Enums) exist.

The good news is that beginning with Excel 2000, we have been given the ability to program our own Enumerations to help in our coding!

To create your own constants, you basically need to list your enumerations and set them equal to something. This list is then encapsulated in an Enum clause. Personally, I try to make the title descriptive, and also preface each Enum value with a shortened form of the constant. It is also important that your Enum be placed near the top of your module, along with any public variables and API calls. It must be above any other subroutines or functions.

An example of an Enumeration listing meal courses is shown in the code below:
Code:
```Public Enum FoodCourses
'Author       : Ken Puls (www.excelguru.ca)
'Enum Purpose : Allow use of constants in DesiredFoodCost function
FoodCourse_Breakfast = 0
FoodCourse_Lunch = 1
FoodCourse_Dinner = 2
FoodCourse_Dessert = 3
End Enum```
Once you have your Enumerations built, you can use them in your procedures. To illustrate this, I have written up an example of a function that is used to return the target food cost percentage for a specific meal. (These costs bear no resemblance to reality, they are simply used for illustration.)
Code:
```Function DesiredFoodCost(ByVal MealType As FoodCourses) As Double
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Demonstrate the use of Enumerations
Select Case MealType
Case Is = FoodCourse_Breakfast
DesiredFoodCost = 0.32
Case Is = FoodCourse_Dessert
DesiredFoodCost = 0.43
Case Is = FoodCourse_Dinner
DesiredFoodCost = 0.52
Case Is = FoodCourse_Lunch
DesiredFoodCost = 0.35
Case Else
DesiredFoodCost = 0.5
End Select
End Function```
The other nice thing is that the Enumerations expose themselves to intellisense as we code. The picture below is a screen capture of the procedure as it was being written. I simply pressed Ctrl+Spacebar part way through the word to bring up the list of items that VBA is aware of:

So now what?
This is where the really good part starts. While it's great that you can use Enumerations like the above, the real power starts showing when you now start to call your functions. If you examine the function above closely, you'll see that I used the Enumeration in the parameter portion of the function:
Code:
`Function DesiredFoodCost(ByVal MealType As FoodCourses) As Double`
This is very important, as it forms the link to the Enumeration. The net effect is that you can now use the Enumerations as constants in your code when you call the function. To illustrate, I began typing a line to evaluate a DesiredFoodCost in the immediate window. As soon as I typed the open brace, the intellisense immediately exposes our list of custom constants!

Enumeration Versatility
If you recall, at the beginning of the article, I mentioned that Enumerations translate numerical values into textual words that we can remember. To illustrate this, try running the following in the immediate window:
Code:
`? DesiredFoodCost(FoodCourse_Dessert)`
The value that should be returned is 0.43, as we can see from the function used above.
Now, run the following code in the immediate window to learn the numerical value (of the Enumeration) for FoodCourse_Dessert:
Code:
`? FoodCourse_Dessert`
The value returned should be 3. You can check the Enumeration to see that this is true.

Finally, let's prove that the Enumerations are just converting our words to numbers. If what I've told you is correct, you should be able to just subsitute the number for the words in our call to the DesiredFoodCost function... so let's try it:
Code:
`? DesiredFoodCost(3)`
So what did you get? My results are below.

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

• ### Recent Forum Posts

#### Delete rows depending on values in other rows

Hello there

Trying to sort out some rows in a sheet...

there is also a column name equipmentType (I) and contains "Wire"...

bdgull Today, 05:36 AM

#### Automatic quarterly date change

If cell D7 contains the date of a past bill, then in any other cell:
=EDATE(D7,3*(QUOTIENT(DATEDIF(D7,TODAY(),"M"),3)+1))
will give...

p45cal Yesterday, 09:59 PM

#### Partial Lookup Text with variable lookup values

try:

Code:
`=IF(SUMPRODUCT(ISNUMBER(SEARCH('Quality List'!\$C\$2:\$C\$500,F2))*(A2='Quality List'!\$A\$2:\$A\$500)*(LEN('Quality List'!\$C\$2:\$C\$500)>0))>0,"Yes","No")`
...

p45cal Yesterday, 06:48 PM

#### Excel 2002 Check if all six numbers have won before

Closed thread and anon125 knows why....

p45cal Yesterday, 03:37 PM

#### Automatic quarterly date change

Well true that was a mistake it should say may 17 now but after that it should say August 17

Sent from my Pixel 5 using Tapatalk...

Luigi802 Yesterday, 02:43 PM