Michael Alexander - Excel 2019 Power Programming with VBA

The following procedure uses six local variables declared by using Dimstatements:

Sub MySub() Dim x As Integer Dim First As Long Dim InterestRate As Single Dim TodaysDate As Date Dim UserName As String Dim MyValue ' - [The procedure's code goes here] - End Sub

Notice that the last Dimstatement in the preceding example doesn't declare a data type; it simply names the variable. As a result, that variable becomes a variant.

You also can declare several variables with a single Dimstatement. Here's an example:

Dim x As Integer, y As Integer, z As Integer Dim First As Long, Last As Double

Unlike some languages, VBA doesn't let you declare a group of variables to be a particular data type by separating the variables with commas. For example, the following statement, although valid, does not declare all the variables as integers:

Dim i, j, k As Integer

In VBA, only kis declared to be an integer; the other variables are declared variants. To declare i, j, and kas integers, use this statement:

Dim i As Integer, j As Integer, k As Integer

If a variable is declared with a local scope, other procedures in the same module can use the same variable name, but each instance of the variable is unique to its own procedure.

In general, local variables are the most efficient because VBA frees up the memory that they use when the procedure ends.

Sometimes, you want a variable to be available to all procedures in a module. If so, just declare the variable before the module's first procedure (outside of any procedures or functions).

In the following example, the Dimstatement is the first instruction in the module. Both Procedure1and Procedure2have access to the CurrentValuevariable.

Dim CurrentValue as Long Sub Procedure1() ' - [Code goes here] - End SubSub Procedure2() ' - [Code goes here] - End Sub

The value of a module-wide variable retains its value when a procedure ends normally (that is, when it reaches the End Subor End Functionstatement). An exception is if the procedure is halted with an Endstatement. When VBA encounters an Endstatement, all variables in all modules lose their values.

To make a variable available to all the procedures in all the VBA modules in a project, declare the variable at the module level (before the first procedure declaration) by using the Publickeyword rather than Dim. Here's an example:

Public CurrentRate as Long

The Publickeyword makes the CurrentRatevariable available to any procedure in the VBA project, even those in other modules in the project. You must insert this statement before the first procedure in a module (any module). This type of declaration must appear in a standard VBA module, not in a code module for a sheet or a UserForm.

Static variables are a special case. They're declared at the procedure level, and they retain their value when the procedure ends normally. However, if the procedure is halted by an Endstatement, static variables do lose their values. Note that an Endstatement is not the same as an End Substatement.

You declare static variables by using the Statickeyword.

Sub MySub() Static Counter as Long '- [Code goes here] - End Sub

A variable's value may change while a procedure is executing (that's why it's called a variable ). Sometimes, you need to refer to a named value or string that never changes: a constant .

Using constants throughout your code 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 use the constant's name rather than its value in your expressions. Not only does this technique make your code more readable, it also makes it easier to change should the need arise—you have to change only one instruction rather than several.

You declare constants with the Conststatement. Here are some examples:

Const NumQuarters as Integer = 4 Const Rate = .0725, Period = 12 Const ModName as String = "Budget Macros" Public Const AppName as String = "Budget Application"

The second example doesn't declare a data type. Consequently, VBA determines the data type from the value. The Ratevariable is a Double, and the Periodvariable is an Integer. Because a constant never changes its value, you normally want to declare your constants as a specific data type.

Like variables, constants have a scope. If you want a constant to be available within a single procedure only, declare it after the Subor Functionstatement to make it a local constant. To make a constant available to all procedures in a module, declare it before the first procedure in the module. To make a constant available to all modules in the workbook, use the Publickeyword and declare the constant before the first procedure in a module. Here's an example:

Public Const InterestRate As Double = 0.0725

If your VBA code attempts to change the value of a constant, you get an error ( Assignment to constant not permitted). This message is what you would expect. A constant is a constant, not a variable.

Excel and VBA make available many predefined constants, which you can use without declaring. In fact, you don't even need to know the value of these constants to use them. The macro recorder generally uses constants rather than actual values. The following procedure uses a built-in constant ( xlLandscape) to set the page orientation to landscape for the active sheet:

Sub SetToLandscape() ActiveSheet.PageSetup.Orientation = xlLandscape End Sub

It's often useful to record a macro just to discover the various constants that can be used. And, if you have the AutoList Membersoption turned on, you can often get some assistance while you enter your code (see Figure 3.2). In many cases, VBA lists all the constants that you can assign to a property.

FIGURE 3.2 VBA displays a list of constants that you can assign to a property.

The actual value for xlLandscapeis 2(which you can discover by using the Immediate window). The other built-in constant for changing paper orientation is xlPortrait, which has a value of 1. Obviously, if you use the built-in constants, you don't really need to know their values.

The Object Browser can display a list of all Excel and VBA constants. In the VBE, press F2 to bring up the Object Browser.

Like Excel, VBA can manipulate both numbers and text (strings). There are two types of strings in VBA.

Fixed-length strings are declared with a specified number of characters. The maximum length is 65,535 characters.

Variable-length strings theoretically can hold up to 2 billion characters.