Variables and Naming

Variables

In the simplest terms, programming consists of variables, operators, and functions. Let us first discuss, variables.

Terms

Variable - remembers a value assigned to it and provides that value when requested. Variables are sometimes represented by letters or words. In this example, A=1, A is a variable being assigned the value of 1. A remembers 1 until it is assigned a new value. Thus, A=2, changes A to remember 2. B=A retrieves the 2 from A and assigns it to B.

Declare - creates a variable of a set type, and in some cases, with an initial value.

Variable Types - variables can remember text, numbers and objects. If we want a variable to contain only text we can declare it as variable type string. And if we want it to contain only small whole numbers we can declare it as variable type Integer. For a complete list of variable types click https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary

Example: Dim A as String

In the above example we declared (Dimensioned) a variable called A and restricted it to contain only characters (text).


Option Explicit

VBA allows us to use variables in our code without declaring them. When we do this VBA creates the variable as a variant variable type. Variants can contain anything and that seems attractive to beginners because it eliminates the step of declaring them, and the potential we might declare them with the wrong variable type. Advanced developers know this is risky behavior. Advanced developers know:

  • Undeclared variables can make reading, and thus, maintaining code more difficult as we must examine how a variable is used, throughout the routine it is used in, to know what it can, and cannot contain.

  • Undeclared variables can make misspelled variables hard to spot. How quickly did you spot the misspelling here: Variable = Varable + 50. When variables are declared, Varable would generate a compile error which would catch the error before we could use the routine and generate erroneous results.

  • Undeclared variables can open us up to using a variable in appropriately. For example, we might have a variable: Day. In one place in our routine it might contain the day's name and in another place it might contain the weekday number, or the day of the month. When variables are declared, if we declared Day as Integer, and tried to say Day = "Tuesday", VBA would generate a Type Mismatch error during testing.