Operators have precedence, which will affect the value (result) if not carefully used. For example, the following two expressions will result to a different value

1 + 2 * 3 / 2 - 6 = -2

((1 + 2) * 3) / 2 - 6 = -1.5

A bracket changes the order in which the expression is calculated.

The following table will give an indication of how expressions are calculated:

Operator	Priority Level (in highest order)
( ) Brackets	1
^ Power sign	2
-ve Negative values	3
*, / Multiply and Divide	4
+, - Addition/Subtraction	5
<, <=, >, >=	6
Like	7

There are more operators that fit in between the above table. Refer to on-line Help or Users Guide.

Concatenate (&)

Concatenate is the term used to join two or strings (text values) together. You use the & (ampersand) as the operator (the glue) to connect one string to another.

This is commonly used in VBA to build string messages and narratives which will part static and dynamic (variables) enhancing the procedure to a more user friendlier environment.

For example, to join my first name with my surname with a space between the two using two variables and a static string (space) between:

Sub ConcatenateExample()
    Dim FName As String, SName As String
    FName = "Ben"
    SName = "Beitler"
    MsgBox "Welcome " & FName & " " & SName
End Sub

Constants

A Constant is a value in a macro that does not change. By using constants, is similar to variables where a value is assigned to the variable.

For example:

Variable known as TitleHeading can have a value assigned to it.

TitleHeading = “SALES CASH FLOW for JANUARY”

Every time the value is used, by using the variable it is easier to handle, update changes and therefore more efficient.

To assign a named constant, proceed with the command Const before the variable.

For example

Variable known as pi can have a value of 3.1415926 assigned to it. Since this is a fixed value for pi, assign the Const command to it.

Const PI = 3.1415926

Functions

Functions, like Excel functions are used to return a value.

For example:

MySName = "Anderson"
Surname = UCase(MySName)

Where Surname variable uses an UCase function to convert the variable, MySName data to upper case.

Example:

The following example will put a date and time formula in to the active cell and then resize the column.

Sub formula1()
ActiveCell.Formula = "=Now()"
ActiveCell.EntireColumn.AutoFit
End Sub

Arguments

Some functions and statements will contain required and optional arguments. An argument is an element of that function or statement which is required to apply that command. Arguments either use a comma separator or can use its syntax name.

For example:

Absolute Formula

Similar to the Absolute Macros we looked at earlier, an Absolute Formula will refer to specific cell references.

Sub formula()
ActiveCell.Formula = "=Sum(A1:A4)"
End Sub

Relative Formula

Similar to the Relative Macros we looked at earlier, a Relative Formula will refer to the active cell and the other cells around it.

Sub formula()
ActiveCell.FormulaR1C1 = "=Sum(r[-4]c:r[-1]c)"
End Sub

Instead of using cell references in the formula, i.e. A1:A4, the relative formula uses row and column references, i.e. r[-4]c:r[-1]c.

In the above example, r[-4]c refers to the cell 4 rows above the active cell in the same column and r[-1]c refers to the cell 1 row above the active cell in the same column.

Next Topic: Visual Basic Functions

Want to teach yourself Access? Free online guide at About Access Databases

Note: Your knowledge of Excel formulae and functions will help derive VBA calculations.