Defining and using constants in VBA

Many developers don't make enough use of constants when they're programming. Some of them don't know that Access supports constants, others know that constants exist but think that it's faster to type the value 10 than it is to type the names"TAX_RATE". They're right in the short term, but a little extra effort at design time will make your programs easier to read, more reliable and easier to maintain.

Creating a constant

Use the Const statement to create a constant. These four lines create two integer and two double constants and sets their values. It's traditional to type constant names in upper case so that they can be easily identified in the program code.

These constants have public scope and will be available to every piece of VBA code in the database. This example shows some constants that might be used in the calculation of ticket prices.

Make the program easier to read

An expression using the name of a constant is easier to read than one which includes the literal numeric or text values. The following calculation works out the ticket price to be charged correctly but it's not immediately obvious what's happening:

If we use named constants instead of the numbers then we can immediately see what the code is doing:

Make the program more reliable

The second advantage is that the programs become more reliable. This reliability comes because Access will check that you've spelt the names of the constants correctly as you're typing the code.

It would be very easy in the first example to make a minor typo in one of the numeric values:

The line is still a valid VBA expression and the code will compile and run without error. Of course your tests ought to show that the price of the child ticket is incorrect here but what if it were a more subtle error, something that won't show up until you have hundreds of users. That's when bugs get expensive.

If you're using constants then any typo will get flagged up as soon as you compile or run the program. Both 0.75 and 0.57 are valid numbers but if you were to type 'chlidrate' as the name of the constant by mistake :

then you'll get an error message as soon as you run the program. The code edit window will appear with your typo highlighted.

The Access code editor will give you another clue before you get that far. As you are typing the names of the variables and constants the editor will recognise 'ticket' as being a variable that has already been declared and so it will correct the appearance to match the case used in the declaration - 'Ticket'. Similarly it will recognise 'baseprice' and change it to 'BasePrice'. It won't recognise 'chlidprice' though and the fact that this word has stayed in lower case should wake you up to your error. Either way, the database is not going to get as far as the users with this typo in place.

All this assumes that you are using Option Explicit at the head of the VBA modules. If you deliberately turn this off then you'll have no protection against spelling mistakes in the names of variables or constants.

Make the program easier to maintain

The final advantage is that code is easier to maintain when constants are used. The business situation is likely to change many times over the lifetime of your database and what are you going to do if somebody decides that the age limit for free tickets is going to change from 5 to 3?

If you have been using literal values then you will have to look for every age calculation in the system and edit them one by one. Don't be tempted to do it with a global search and replace. The VBA editor will certainly correct the age calculation by finding every 5 and changing it into a 3 but it'll also change the VAT rate from 17.5 to 17.3 and perhaps change the company's address or phone number as well. You can use the global search to find every 5 but you'll have to look at every occurrence and decide whether or not to change it by hand. It's very easy to miss a change.

If on the other hand you've used a constant then you just make one simple change to the source code:

And because it's just the one change it's easy to add a comment to tell people what you've done.