You're ready to write some code and would like to declare some variables but are not sure of the proper VBA syntax for doing so.
Use the Dim statement to declare a variable, as illustrated in Example 2-4.
Example 2-4. Declaring variables using the Dim statement
Dim a As Double Dim n As Integer
Dim statements are of the general form Dim VariableName As DataType, where Dim and As are VBA keywords, DataType is one of the VBA data types as discussed in Recipe 2.3, and VariableName is a name you supply that conforms to the naming rules discussed in Recipe 2.2.
You can use Dim statements to declare local variables within procedures by including the Dim statements in the body of the procedure. Such local variables can be used only by the procedures within which they are declared. To declare global variables that have module-level scope, place the Dim statements at the beginning of the code module before any procedure declarations. Such global variables can be used by any procedure in the module within which the variables are declared.
To declare global variables that can be used by any code module within a project, use a Public statement like this Public VariableName As DataType.
You can declare multiple variables on a single line by separating the variable declarations with commas, as shown in Example 2-5.
Example 2-5. Multiple declarations
Dim a As Double, Dim b As Double, Dim c As Double Dim d, e, f As Double
The first line in Example 2-5 declares three Double type variables, a, b, and c. It's tempting to think that the second line in Example 2-5 declares three more Double type variables, d, e, and f, but this is not the case. The fact is, only variable f in the second line is a Double; d and e are of Variant type. So what gives?
VBA requires an explicit data type to follow each variable name; otherwise, it assumes a Variant type. So to make d and e Doubles, you'd have to put As Double after each one. Given this requirement, in my opinion there isn't much use in putting multiple declarations on a single line in VBA. You're better off putting each Dim statement on a separate line to make your code more readable.
By now you're probably wondering what a Variant data type is. A Variant is a generic data type that can represent any of the other explicit data types. VBA determines the appropriate type to use given the context within which a Variant variable is used. While this may seem like a flexible way to use variables, it's less efficient in terms of code execution performance and makes your code less readable. I recommend using explicit data types as much as possible and using Variants only when absolutely necessary (for example, when a built-in VBA procedure or function requires Variant parameters).
Getting Acquainted with Visual Basic for Applications
Collecting and Cleaning Up Data
Time Series Analysis
Curve Fitting and Regression
Numerical Integration and Differentiation
Solving Ordinary Differential Equations
Solving Partial Differential Equations
Performing Optimization Analyses in Excel
Introduction to Financial Calculations