Visual Basic Syntax

J. C. Daly

VBA syntax for used for the following is described.

Subroutines

The syntax for a subroutine is;


Public Sub Name(parameter1 as integer, Parameter2 as double, ...etc)
Your code goes here
End Sub

Functions

Functions return a value. The syntax for functions is,


Public Function Name(parameter 1 as double, paremeter2 as integer, ...) as double
Name = parameter1^4
End Function

Since the function returns a value it must be declared, Here we declared the function, Name, as double. Excel has many built-in functions. VBA allows you to define your own functions. These functions can be called in Excel.

Declaring Variables

The syntax for declaring variables is,


Dim m as integer
Dim x as double

Here m is declared to have a data type, integer and x is declared to be a double precision number. Variables can have a local or global scope. When variables are declared inside a procedure they are local variables. Local variables can be used only in the procedured they are defined. Variables have global scope, where they can be used by any procedure in a module, when they are declared at the beginning of the module before any procedure declarations.

If an explicit data type such as, double or integer, is not included in the Dim statement, the data type variant is assumed. The variant data type represents any of the other data types. VBA assigns a data type to the variable based on the syntax in which the variable appears. The use of the variant data type makes the code less efficient.

Constants

The syntax for declaring constants is,


Constant name1 as double = 1.234

or

Public name2 as integer = 22


Just as with variables, constants can be declared as local or global by declaring them inside a procedure, so they are local to that procedure, or declaring them at the beginning of a module so they are available to all procedures in the module.

Arrays

The syntax for declaring arrays is,


dim x(5) as double

Here x is the array x(0), x(1), x(2), x(3), x(4)

Multi-dimensional arrays can also be declared.

dim y(1 to 8, 1 to 4) as integer

Here first index goes from 1 to 8, and the second index goes from 1 to 4. If the statement read Dim y(8,4) as integer the indexes would go from 0 to 7 and 0 to 3.


Comments

It is good practice to comment your code. Comments ensure you will understand what you were trying to do when you return to the code after a period of time. Comments also allow another person to pick up your work and complete or modify the code. This is important in industry where teams work on coding projects, or where good code modules are reused in different situations.

Professional programmers and A students are generous with comments.

The syntax for comments is,


REM   This is a comment. It is for humans to read and is not seen by VBA

X = 0   '   This is a comment


Text following the rem or following an apostrophy, ' , is a comment. It is not seen by VBA.

If

The syntax for an if statement is,


if(x =y) then
' statements
end if
Or
If(a>b) then
' statements
else
' statements
End If
Or
If(a>=3) then
' statements
elseif(a > 4) then
' statements
elseif(a> 5) then
' statements
else
' statements
End If

For

The syntax for the For loop is,


for n = 1 to 10
' statements
next n

While

The syntax for a while loop is,


Do while( a <= c)
' statements
Loop
Or
Do
' statements
Loop   while( a <= c)

In the first while loop if the condition is true, the statements are executed. The code loops and the execution of the statements is repeated as long as the condition is true. In the second while loop the statements are first executed. Execution repeats as long as the condition is true.