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
- Visual Basic is case insensitive.
- Subroutines start with the scope qualifier of Public or Private.
- If Public the subroutine has global scope and can be called from any module.
- If Private the subroutine has local scope and can only be called from the module in which it is defined.
- The key word sub lets VBA know that this is a subroutine.
- The subroutine must have a name.
- The name must start with a letter.
- Names can not include the characters @, &, $, #, or !. Spaces and periods are also not allowed in names.
- Avoid names that are the same as built-in functions or Excel key words.
- These naming rules apply to all names in VBA, including functions, variables, constants, and parameters.
-
Parameters are enclosed in parentheses following the Name.
The Parameter name is followed by a specifier, such as "as double"
Specifiers include,
- double (double precision, 8 bytes)
- integer (-32,768 to n+ 32,768 2bytes)
- byte (0 to 255, 1 byte)
- String (ASCII and special characters)
- boolean (True or False, 2 bytes)
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.