Working with constants

A variable's value may (and usually does) change while your procedure is executing. That's why they call it a variable. Sometimes, you need to refer to a value or string that never changes — a constant. A constant is a named element whose value doesn't change.

As shown in the following examples, you declare constants by using the Const statement:

Const NumQuarters As Integer = 4

Const ModName As String = "Budget Macros"

Public Const AppName As String = "Budget Application"

Using constants in place of hard-coded values or strings is an excellent programming practice. For example, if your procedure needs to refer to a specific value (such as an interest rate) several times, it's better to declare the value as a constant and refer to its name rather than the value. This makes your code more readable and easier to change; should the need for changes arise, you have to change only one statement rather than several.

Like variables, constants have a scope. Keep these points in mind:

I To make a constant available within only a single procedure, declare the constant after the procedure's Sub or Function statement.

i To make a constant available to all procedures in a module, declare the constant in the Declarations section for the module.

i To make a constant available to all modules in the workbook, use the Public keyword and declare the constant in the Declarations section of any module.

If you attempt to change the value of a constant in a VBA routine, you get an error. This isn't surprising because a constant is constant. Unlike a variable, the value of a constant does not vary. If you need to change the value of a constant, what you really need is a variable.

Excel and VBA contain many predefined constants, which you can use without the need to declare them yourself. In general, you don't need to know the value of these constants to use them. The macro recorder usually uses constants rather than actual values.

The following simple procedure uses a built-in constant (xlCalculationManual) to change the Calculation property of the Application object. (In other words, this changes the Excel recalculation mode to manual.)

Sub CalcManual()

Application.Calculation = xlCalculationManual End Sub

I discovered the xlCalculationManual constant by recording a macro that changed the calculation mode. I also could have looked in the Help system under "Microsoft Excel Constants." As shown in Figure 7-3, the Help system lists all the built-in constants.

Figure 7-3:

The Help system lists Excel constants.

Figure 7-3:

The Help system lists Excel constants.

The actual value of the built-in xlCalculationManual constant is -4135. Obviously, it's easier to use the constant's name than to look up the value (even if you knew where to look). By the way, the constant for changing to automatic calculation mode is xlCalculationAutomatic; its value is -4105. As you can see, many of the built-in constants are just arbitrary numbers that have special meaning to VBA.

To find the actual value of a built-in constant, execute a VBA statement such as the following:

MsgBox xlCalculationAutomatic

Working with strings

Excel can work with both numbers and text, so it should come as no surprise that VBA has this same power. Text is often referred to as a string. You can work with two types of strings in VBA:

1 Fixed-length strings are declared with a specified number of characters. The maximum length is 65,526 characters. As a point of reference, this chapter contains about half that many characters.

1 Variable-length strings theoretically can hold as many as two billion characters.

Each character in a string takes one byte of storage. In addition, a variable-length string consumes an additional 16 bytes. Therefore, if you're striving for efficiency, it's better to use fixed-length strings if possible.

When declaring a string variable with a Dim statement, you can specify the maximum length if you know it (it's a fixed-length string) or let VBA handle it dynamically (it's a variable-length string). The following example declares the MyString variable as a string with a maximum length of 50 characters. (Use an asterisk to specify the number of characters, up to the 65,526 character limit.) YourString is also declared as a string but its length is unspecified:

Dim MyString As String * 50 Dim YourString As String

When declaring a fixed-length string, do not use a comma in the number that specifies the string size. In fact, never use commas when entering a numeric value in VBA.

Working with dates

Another data type you may find useful is Date. You can use a string variable to store dates, but then you won't be able to perform date calculations. Using the date data type gives your routines greater flexibility. For example, you might need to calculate the number of days between two dates. This would be impossible if you used strings to hold your dates.

A variable defined as a date uses eight bytes of storage and can hold dates ranging from January 1, 0100 to December 31, 9999. That's a span of nearly 10,000 years and more than enough for even the most aggressive financial forecast. You can also use the date data type to work with time data (seeing as VBA lacks a time data type).

These examples declare variables and constants as a date data type:

Dim Today As Date Dim StartTime As Date Const FirstDay As Date = #1/1/2007# Const Noon = #12:00:00#

In VBA, place dates and times between two hash marks, as shown in the preceding examples.

Date variables display dates according to your system's short date format, and display times according to your system's time format (either 12- or 24hour). The Windows Registry stores these settings and you can modify them via the Regional and Language Options dialog box in the Windows Control Panel. Therefore, the VBA-displayed date or time format may vary, depending on the settings for the system on which the application is running.

0 0

Post a comment