III - Variables
Now that you know what Visual Basic for Applications (VBA) is and are familiar with the VBA environment, I want to start introducing you to the elements of the language. One of the basic elements in VBA is the variable. It serves as a placeholder and identifier for a specific value that allows the value to be changed when necessary and still retain it's meaning and context.
Variables in Real Life
We actually use words in real life that could be considered variables. A good example would be the word "salary". If you ask one person what their salary is, they are likely to give you a different answer than if you ask someone else. A person's salary will also vary (hopefully increasing) from year to year. In this way, the term "salary" refers to the same thing but a different value at different times or with different people. The value of the salary (i.e. $45,000) would have no meaning on its own but when the term "salary" is applied, it starts to make sense. In this way, the human expression "My salary is $45,000" could be expressed in VBA like this:
Dim Salary As Currency
Salary = 45000.00
If the person received a raise at the end of the year, it might be expressed like this:
Dim Salary as Currency
Dim Raise as Currency
Salary = $45,000
Raise = Salary * 0.05
Salary = Salary + Raise
In this case, another variable called Raise is declared and set to 5% of the salary (the asterisk is used in VBA to multiply numbers). The resulting value is then added to the original salary to get the new value.
Using Variables in Code
An example of how VBA uses variables can be seen in this sample.
Public Sub FormOpen(strFormName As
String)
'Declare variables
Dim dbsCurrent As CurrentProject
Dim frmCurrent As AccessObject
Dim blnExist As Boolean
blnExist = False
'Reference current project.
Set dbsCurrent = Application.CurrentProject
'Check if requested form exists.
For Each frmCurrent In dbsCurrent.AllForms
If frmCurrent.Name = strFormName Then
blnExist = True
End If
Next
...
This code checks to see if a specific form exists by looping through all the forms in the database and comparing the name of each one to the value contained in the strFormName variable on the first line.
As you can see in the examples, the Dim keyword is used to create or declare a variable. This declaration documents the use of the variable for the code and assigns a specific data type to the variable (i.e. "as Boolean"). A good practice is to declare all variables at the top of a procedure in order to keep track of what has been declared. Variables take up memory and when changing code, it is a good idea to remove variable declarations that are no longer needed.
At the top of this sample, you can see three variables being declared using the Dim keyword. The first two, dbsCurrent and frmCurrent, are referred to as object variables because they refer to objects that have properties and methods, just like forms and reports in Access. The third is a boolean variable that refers to a single true or false value.
When a Boolean variable is declared, it's initial value is False just as when an Integer is declared, it initializes to 0. In the above example, I explicitly set blnExist to False for safety and clarity. It's also a good habit to have as it's sometimes necessary when looping through the same operation more than once and reusing variables.
Variables are used in a couple of ways in this sample. The first line of the procedure, also called its declaration, accepts the strFormName parameter. This is a type of variable that holds information being passed to the procedure by another section of code. The parameter name gives the value context within the code, identifying it as "the form name that was passed in". This particular variable doesn't change within the course of the procedure.
An example of a variable that does change throughout the procedure is frmCurrent. When the code is iterating through the available forms, it needs a variable to reference the form that it's working with at any given time. That's where frmCurrent comes in handy. The For Each ... Next loop uses frmCurrent to hold each form for inspection until it's ready to move to the next one. Without a variable to hold the form reference, it would be more difficult to run through a list of objects like this.
You'll learn more about For ... Next and other looping structures in a later chapter.
While frmCurrent is used to hold different items as the code runs, the blnExist variable holds a single item that may change while the code is running. In this case, it's holding a value indicating if the specified form has been found. This value remains False unless the specified form is found and then it becomes True. This indicator will be referenced later in the code to take further action depending on it's value.
Declaring Variables
The Dim keyword (short for "dimension") is used to declare a variable in VBA as a specific type.
Dim CurrentForm as String
The above variable is declared as a string which holds alphanumeric data (i.e. names, zip codes). The variable could be declared without specifying the type which would result in a Variant type that can hold any type of value. This is to be avoided precisely because anything can be stored in it. Assigning a specific type to a variable guards against inappropriate values being stored in it which can and probably will cause errors later when the code references the variable and tries to use it in an operation.
For example, if you make a mistake as you're writing code and try to store the string value "Boston" to a variable that is declared as an integer, VBA will catch this and alert you. Not so with a Variant type because it can store either one. It also makes the code unclear as in the above example where there's no way to know if the CurrentForm variable is supposed to refer to a form name or the form object itself.
More than one variable can be declared on a single line in VBA by placing a comma between the declarations. The Dim keyword does not need to be repeated.
Dim FirstName as String, LastName as String
This example declares two string variables. Again, it's important to specify the type for each variable or you will end up declaring at least one of them as a Variant.
VBA does not absolutely require you to declare variables.
You could just start using a variable in the code somewhere and VBA would treat
it as a Variant. This is very bad programming practice as it does nothing
to track or document the variables being used or define their data types.
The fact that VBA allows it is one of the reasons the language is not regarded
very highly by many professional programmers. VBA can be set to require all
variables to be declared in the option settings or by placing the
following statement at the top of a module:
Option Explicit
For more information on the data types that you can use with variables in VBA, search the Access help files for "data type".
Setting Variables
As shown throughout these examples, assigning a value to a variable is as simple as using the equals operator in a statement.
FirstName = "Andrew"
The exception to this in VBA is when you're working with an object variable such as the dbsCurrent variable in the example above. In this case, you need to use the Set command like this:
Set dbsCurrent = Application.CurrentProject
If you don't use the Set keyword with an object variable or you do use it with a non-object, you'll find out quickly enough when you get errors like these as you try to run the code.
The first error states that an object variable has not been set which means that it does not equal anything. (An object variable that has not been assigned an object is referred to by VBA as being equal to 'Nothing'.) The second error results when the Set keyword is used on a value variable that cannot hold an object and simply states that an object is required.
Non-object variables such as integers and strings which simply hold a value are sometimes referred to as "primitive data types".
In order to avoid memory problems and potential
errors, it is important to explicitly destroy object variables when the code is
finished with them, usually at the end of the procedure where they are used.
This is done by setting the object variable to equal 'Nothing'.
Set CustomerForm = Nothing
Naming Variables
There are very few actual rules for naming variables. Variables in VBA must start with an alphanumeric character (a-z, A-Z or 0-9) and cannot be longer than 255 characters. They also cannot contain spaces or any of the characters #, !, &, %, $ or @. These are type-declaration characters that can be used to mark a value as being of a specific data type and are reserved for use by VBA.
You must also remember to make your variable names unique within their scope. In other words, you wouldn't give two variables the same name within the same procedure or name a procedure variable the same as a module-level variable in the same module as the two would conflict.
The main idea is to name it something that will be self-explanatory and not so long that you're likely to make a mistake when typing it. Variables in VBA are not case-sensitive which means that newForm, NewForm and NeWfOrM are all the same thing.
There are a couple of naming conventions that you can use as a guide in standardizing your variable names.
Hungarian notation is an older style of variable naming that attaches a two or three letter prefix onto the beginning of the variable to indicate the data type, i.e. strName for a string variable or intAge for an integer. In newer programming technologies, this notation is discouraged but in VBA, it's still useful.
Another type of notation is called CamelCase. In this style, variables with more than one word have the first letter of each word capitalized. Although VBA is case-insensitive when it comes to variables, it will remember the capitalization you use with a variable or procedure name. Examples of CamelCase would include:
AgeInYears
StreetAddress
LastName
Another practice is to separate multiple words in a variable name with an underscore although this might be a little tedious when typing a lot of code.
Age_In_Years
Street_Address
Last_Name
Constants
Sometimes when writing code, there is a specific value that you need to use more than once and you need to ensure that the correct value is used each time. This is when a constant should be declared. A constant is declared once in code using the Const keyword and then cannot be changed except by changing the declaration. Some examples might be:
Public Const VALUE_OF_PI As Double = 3.14
Public Const SALES_TAX = As Double = .06
Public Const DRINKING_AGE As Integer = 21
Notice that I've named the constants in all caps and used underscores to separate words. This is not required but it is one way to distinguish constants referenced in code from ordinary variables.
Also notice that the constants are all declared as Public. If I were coding these, I would probably place them in a standalone module named General or something similar and make them all public. This would mean that they would be available to the entire project. No matter where I referenced them in the code, VBA would automatically recognize them as the constants that had been declared and they would supply the correct value.
Using one of the above constants, you could have a function like this:
Private Function TotalWithTax(Total as Double) As
Double
Dim SalesTaxAmount as Double
SalesTaxAmount = Total * SALES_TAX
TotalWithTax = Total + SalesTaxAmount
End Function
In this example, the sale total is passed in and then multiplied by the SALES_TAX constant. The result is added to the original total to return the result.
In addition to defining standard constants as shown here, constants can be used to supply custom values such as company names or other values specific to your operation. Another advantage is that if those values ever do change, you only have to change them in one place rather than throughout the code.
Variable and Constant Scope
Variables and constants are private by default when declared with the Dim keyword. This means that the area in which they are recognized by the code is limited to the smallest area possible. Variables within procedures and functions cannot be public. If you declare a variable using the Dim keyword at the top of a form, it will not be seen outside that form. If you declare it at the top of a module, it's limited to the procedures within that module. You can also explicitly make a variable private by declaring it with the Private keyword:
Private TagNumber as String
At module level (at the top of the module, outside of any procedures), you can make a procedure or constant public by using the Public keyword:
Public MonthlySales as Double
This means that the variable and it's value will be available from outside the module and, in this case, you would need to be careful not to name another variable "MonthlySales" so as not to conflict with this one.
Declaring and working with variables is one of the basic aspects of working in VBA and, in a short time, it will become second nature, especially as you see some of the errors that can be caused by improperly declaring or setting one or how easy they can accumulate within your code.
While variables represent single values and objects within code, procedures represent single units of code within an event-driven language and in the next chapter, I'll talk about how they're written and used within VBA.
