Variables

In this blog post we cover declaring variables, naming conventions, scope and constants.

When writing code in V.B.A. we often need to do calculations based on values that can change. An example would be working out the area of a circle. Take a look at the code below to see how we have used variables to for values that we do not know when the code starts running.

Public Sub AreaOfCircle()

Dim d As Double
Dim radius As Double
Dim measure As String
'This is where we declare our variables

'They are variables because when the sub-procedure runs,
'we do not know their values

Const PI = 3.14159265359
'We have declared pi as a constant and not a variable
'because its value never changes...

radius = InputBox("Please enter a radius")
'We obtain the value of radius using an input box

measure = InputBox("Please enter cms or inches")
'We obtain the value of measure using an input box

d = PI * (radius * radius)
'We work out the area of the circle and assign it
'to d

MsgBox "The area of your circle is " & Round(d, 4) & " " & measure


End Sub

Declaring variables

Variable declaration is the act of telling VBA the name of your variables before you actually use them.   You should always declare the variables you will use as soon as logically possible, which usually means at the top of your function or sub procedure. You should also state the data type of your variables.  In the above code we are telling V.B.A. that we would like to declare a variable called Radius which has a data type double.

It is a good idea and standard practice to declare variables and data types

Dim

To declare a variable in VBA we use the keyword Dim (which stands for dimension).

Dim Age As Integer   	' VBA makes space for Age, of type Integer
Dim Name as string  	' VBA makes space for Name, of type String

The name of the variable must follow Dim and the type of the variable should follow with the keywords “As” and then the type.

Note: If, at the top of the module, you include the “Option Explicit”  statement, you must let V.B.A. know the data type that you will be assigning the variable (e.g. as Integer, as Double, as String). If, however, you omit “Option Explicit” at the top of the module, you don’t have to let V.B.A. know what type of data you are going to use. V.B.A. will assume that you are using the data type “Variant” and proceed accordingly. Always use “Option Explicit”!!!

Restrictions on naming variables

The names we can use for variables must conform to a small set of rules:

1. They must begin with a letter or an underscore (_).
2. They must end with a number or letter.
3. They may contain any sequence of numbers or letters or underscores (_).
4. They may contain upper or lower case letters.
5. They must not be one of VBA’s keywords.

The compiler will automatically tell you if a variable is illegally named and will not execute unless variables are valid.

Dim a As String             ' is a valid variable name
Dim b_ As String            ' is a valid variable name
Dim _b as String            ' variable names must start with a letter
Dim 2b as String            ' variable names must start with a letter
Dim c1 As String            ' is a valid variable name
Dim d12 As String           ' is a valid variable name
Dim e_e1 As String          ' is a valid variable name
Dim f! as String            ' punctuation not allowed in variable names
Dim g As String             ' is a valid variable name
Dim dim as String           ' is not valid – “Dim” is a keyword
Dim string as String        ' is not valid – “String” is a keyword
Dim number As String        ' number is not a keyword so this is valid

 

Naming Conventions

A naming convention is a way of naming variables which enables us to easily understand both the data type of the variable and the reason for its existence.  There are a couple of rules to follow when naming variables.

• Use meaningful variable names – make your variables mean something. Zzxd isn’t meaningful, but fileNotFound means something to a human, even though it doesn’t affect the computer or VBA in any way.
• Use camelCase for variables – that is, for every word in your variable name make the first letter of the first word lower-case, and the remaining letters upper-case. thisIsCamelCase.
• Use UPPER_CASE for constants (see below)– when you declare a constant, the name of that constant is usually capitalised.  This means nothing to the compiler but means everything to you (we look at constants later on in this unit).

Another convention is to use up to 3 small letters before the variable name to indicate the data type.

• iMyNumber would be of type Integer
• dblMyOtherNumber would be of type Double
• strText would be of type String

Constants

Constants differ from variables in that their value does not change after they have been declared.  This is how we code with constants:

Dim a as String		   ' is a regular variable declaration
Const B = 1	           ' declare the constant B with a value of 1
Const DATABASE_NAME   =     “accdb_firsttime” ' new constant called DATABASE_NAME

You may have noticed that constants are not given a data type; this is because VBA makes some intuitive assumptions about the data. For example, any text surrounded by double quotation marks is a String; any number without a decimal point will fit into a Long; any decimal number will fit into a Double, and any True or False values fit into a Boolean value (True or False). This is the same logic VBA will take if you were not to define your data type on a variable using Dim.

Variable Scope

When you declare a variable in your program you also implicitly determine which parts of your code can access it. In VBA there are three types of declaration that affect the scope of a variable; Procedure, Module and Public.

'Global Declaration
Public SalesTax As Double

'Module Level Declaration
Private ItemPrice As Double

Sub getPriceIncVAT()
    Dim PriceIncVAT As Double
    Call getSalesTax
    Call getItemPrice
    PriceIncVAT = ItemPrice + (ItemPrice * SalesTax)
    MsgBox ("The price of the item including VAT is: $" & PriceIncVAT)
End Sub

Sub getSalesTax()
    SalesTax = InputBox("What is the tax? (20%=0.2)")
End Sub

Sub getItemPrice()
    ItemPrice = InputBox("What is the price of the item?")
End Sub

Procedure Level Scope

Procedure level scope means that a variable is recognised only within that procedure. In the above code the variable PriceIncVAT has a procedure level scope and is only recognised within the sub-procedure getPriceIncVAT. To achieve this we use the dim or static keywords and declare the variable inside the sub-procedure we wish to recognise it.

Module Level Scope

Module level scope means that a variable can be recognised by any sub procedures within the module. ItemPrice has a module level scope and this is reflected in the fact that the variable is recognised in getItemPrice and getPriceIncVAT. To give a variable a module scope we declare it at the top of the module and use the private keyword (private means it is only available to sub-procedures within the module it is declared in).

Public Level Scope (also known as Global Scope)

Public level scope means that a variable is recognised by every sub-procedure and function within the active application. (In the above code SalesTax has a public level scope.) This can be useful for variables that should be consistent throughout the application (e.g. SalesTax shouldn’t be 20% in one sub procedure and 15% in another). It is convention to create a module with a name like “Globals” where it is possible to keep all of the public variables in one place where they can easily be maintained and modified as required.

Variables are a key part of VBA and mastering them will enable you to write better, more efficient code.

 

Related Posts

Data Types In VBA Variables