Defining and using constants in VBA

Many people 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 10 than to type "TAX_RATE". They're right, but a little extra effort will make your programs easier to read, more reliable and easier to maintain.

Creating a constant

Use the Const statement to create a constant.

Public Const BABY_AGE As Integer = 5
Public Const BABY_RATE As Double = 0.00
Public Const CHILD_AGE As Integer = 16
Public Const CHILD_RATE As Double = 0.75

These constants are public and will be available to every part of the database. This example shows the constants used in the calculation of ticket prices. It's traditional to type constant names in upper case.

Easier to read

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

Select Case Age
  Case Is < 5
    Ticket = 0
  Case Is < 16
    Ticket = 0.75 * BasePrice
  Case Else
    Ticket = BasePrice
End Select

If we use constants then we can immediately see what the code is doing:

Select Case Age
  Case Is < BABY_AGE
    Ticket = BABY_RATE * BasePrice
  Case Is < CHILD_AGE
    Ticket = CHILD_RATE * BasePrice
  Case Else
    Ticket = BasePrice
End Select

More reliable

Reliability comes because Access will check that you've spelt the names of the constants correctly, you are using Option Explicit aren't you.

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

  Case Is < 16
    Ticket = 0.57 * BasePrice

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 child ticket is incorrect in this example 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.

Access VBA showing that a constant has not been declared If you're using constants then any typo will get flagged up as soon as you compile or run the program. If for example you were to type 'chlidrate' by mistake :

  Case Is < 16
    ticket = chlidrate * baseprice

then you'll get an error message as soon as you run the program and 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 been declared and so it will correct the case to 'Ticket'. Similarly it will recognise 'baseprice' and change it to 'BasePrice' as soon as you press Enter. It won't recognise 'chlidprice' though and the fact that this 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.

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 individually. Don't think you'll be able to do it with a global search and replace. Access 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 change it by hand.

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

'-- Baby age limit was 5 until Oct 2010.
Public Const BABY_AGE As Integer = 3

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