Recipe2.5.Defining Constants

Recipe 2.5. Defining Constants

Problem

You'd like to declare some constants and need to know the correct VBA syntax.

Solution

VBA constants are declared using the syntax Const Name As DataType = Value , as illustrated in Example 2-6.

Example 2-6. Declaring constants

Const MyConstant As Integer = 14

    Public MyConstant2 As Double = 1.025

Discussion

Just as with variables, you can declare constants with local or global scope. Constants declared within procedures have local scope; that is, they are available only within the procedure. Constants declared outside of any procedures at the beginning of a code module have module-level scope and can be used by any procedure within the module. As with variables , you can use the Public keyword, as illustrated in Example 2-6, when declaring constants at the beginning of a code module to make the constant available to all procedures in the project.

A constant is just that, constant. Once a constant is declared with its value set, you cannot change its value. Constants are useful for defining descriptive names to commonly used values such as scientific or engineering coefficients. For example, you could define a constant to represent p and use the constant Pi throughout your calculations rather than write out the numerical value.

Excel includes a built-in worksheet function PI that returns the value of p . You can actually use that function in your VBA code by accessing the function through the WorksheetFunction object, as in WorksheetFunction.Pi . See Recipe 2.13 for more information on Excel Objects.

Recipe 2.6. Using Arrays

Problem

You don't know the VBA syntax for declaring arrays .

Solution

You declare arrays just as you do variables , except you specify the size of the array in the declaration, as shown in Example 2-7.

Example 2-7. Declaring arrays

Dim y(1 To 4) As Double
Dim x(4) As Double

Dim M(1 To 8, 1 To 8) As Double
Dim N(8, 8) As Double

Arrays are declared with their size specified in parentheses following the array name .

Discussion

When you use the To keyword in an array size specification, the first number is the lower bound and the second number (the one following To ) is the upper bound of the array. You can skip the To keyword and simply put the desired size (i.e., the number of array elements) in parentheses following the array name. In this latter approach, the first array index, the base, is 0 unless you use the Option Base statement.

Use the Option Base statement at the beginning of a code module (before your procedures) to specify the default base for arrays. For example, Option Base 1 sets the base to 1, while Option Base 0 sets the base to 0.

You can declare multidimensional arrays by separating each dimension's size, or upper and lower bounds, by commas in the array declaration, as shown in the last two lines of Example 2-7. VBA supports multidimensional arrays of up to 60 dimensions.

To actually access an array's elements in your code statements, write the array name followed by the index to the array element enclosed in parentheses. Example 2-8 shows how to access array elements.

Example 2-8. Accessing array elements

y(3) = 2.983

M(1, 2) = 4.321

Separate the array indices by commas for multidimensional arrays.

The array declarations discussed here are static declarations in that the size of the arrays is specified in the Dim statement. In some special cases, you may not know the required size of an array and may need to allocate memory for the array dynamically. In such cases you can declare the array as discussed earlier but leave the parentheses following the array name blank (include the parentheses, just don't put anything between them). Then in your code you'll have to dynamically allocate memory for the array of the size you desire . Use ReDim statements to set, or reset, the size of an array. For the most part, I'll stay away from dynamically allocated arrays except in special circumstances. You can learn more about dynamic arrays by reading the VBA help topic "Declaring Arrays." Press F1 in the VBA IDE to access the VBA help guide.