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
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
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:
- They must begin with a letter or an underscore (_).
- They must end with a number or letter.
- They may contain any sequence of numbers or letters or underscores (_).
- They may contain upper or lower case letters.
- 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
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 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.
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.