• # 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

#### Specification Comparison Table from Different Worksheets

Aaarrrggghhhh!!!!

I have just noticed another bloody merged cell - roll. Going to have to re-write again. NOW I'm getting annoyed. ...

AliGW Today, 08:31 AM

#### Specification Comparison Table from Different Worksheets

OK - the thing is, when you ask for help, you really need to try to make sure that the data you provide as a sample truly reflects the data you are working...

AliGW Today, 08:20 AM

#### Specification Comparison Table from Different Worksheets

Here's where i got to. When i'm creating the Pivot and Composite Query, reference to Column 7 shows error and I couldn't continue futher......

Polignac Today, 08:14 AM

#### Specification Comparison Table from Different Worksheets

Well, yes and no! This way, I just do it all again for you and you learn nothing.

Far better to share with me the version that shows how...

AliGW Today, 07:47 AM

#### Specification Comparison Table from Different Worksheets

Hi Ali, probably easier if I share the file with dummy data for better communication. As you can see, the layout for my real data will be identical to...

Polignac Today, 07:05 AM