Working with Constants
Constants are values that don't change. They can be numbers, strings, or other values, but, unlike variables, they keep their values throughout your code. VBA recognizes two types of constants: built-in and user-defined.
Using Built-In Constants
Many properties and methods have their own predefined constants. For Excel objects, these constants begin with the letters xl. For Word objects, the constants begin with wd. For VBA objects, the constants begin with vb.
For example, Excel's Window object has a WindowState property that recognizes three built-in constants: xlNormal (to set a window in its normal state), xlMaximized (to maximize a window), and xlMinimized (to minimize a window). To maximize the active window, for example, you would use the following statement:
ActiveWindow.WindowState = xlMaximized
Creating User-Defined Constants
To create your own constants, use the Const statement:
Const CONSTANTNAME [As type] = expression
The name of the constant. Most programmers use all-uppercase names for constants.
Use this optional expression to assign a data type to the constant.
The value (or a formula that returns a value) that you want to use for the constant.
For example, the following statement creates a constant named DISCOUNT and assigns it the value 0.4:
Const DISCOUNT = 0. 4
This chapter gave you the nitty-gritty on VBA's variables. You learned what variables are and how to declare them, and you learned the various data types supported by VBA. I finished by showing you how to work with arrays and constants.
You also learned a few things that will help you avoid the most common variable-related errors made by beginning programmers:
Declare all your variables.
To ensure you declare all your variables, add the Option Explicit statement to the top of all your modules (or get VBA to do it for you automatically).
Assign a specific data type to each variable.
Put all your variable declarations at the top of each procedure (that is, immediately after the Sub or Function statement).
To avoid confusing a variable with a built-in VBA name, use a lowercase first letter in each of your variable names.
After a variable has been declared, enter all subsequent instances of the variable entirely in lowercase. After you move the cursor away from the variable name, the Visual Basic Editor should change the case to match what you have in the original declaration. If it doesn't do this, it likely means you spelled the variable name wrong.
Here's a list of chapters where you'll find related information:
You often use operators and expressions to assign values to variables. I discuss this in detail in Chapter 4, "Building VBA Expressions."
Objects have a separate variable type. I talk about it, as well as about assigning objects to variables, in Chapter 5, "Working with Objects."
See Chapter 12, "Interacting with the User," to learn more about the details of the MsgBox statement.