Sub and Function Procedures
VBA code that you write in the
Visual Basic Editor is known as a
The two most common types of procedures are
Subprocedure is a group of
VBA statementsthat performs an action (or actions).
Functionprocedure is a group of
VBA statementsthat performs a calculation and returns a single value.
Most of the macros you write in
You can think of a
Sub procedure as being like a command: “Execute the
Sub procedure and something happens”.
Exactly what happens depends on the
Function is also a procedure, but it’s quite different from a
For understanding a
Function let’s take an example from
Excel includes many worksheet functions that you use every day. Examples include
SUM function takes input in form of values and then it does calculation behind the scene, and then returns a single value.
The same goes for
Function procedure that you develop with
Sub procedure starts with the keyword Sub and ends with an End Sub statement. Here’s an example:
Sub Message() MsgBox "That’s all folks!" End Sub
This example shows a procedure named
Message. A set of parentheses follows the procedure’s name.
In most cases, these parentheses are empty. However, you may pass arguments to
Sub procedures from other procedures.
Sub uses arguments, list them between the parentheses.
Please remember when you record a macro with the macro recorder; the result is always a Sub procedure.
Function procedure starts with the keyword Function and ends with an End Function statement.
Here’s an example:
Function CubeRoot(number) CubeRoot = number ^ (1/3) End Function
This function, named
CubeRoot, takes one argument (named
number), which is enclosed in parentheses.
Functions can have any number of arguments or none at all.
When you execute the function, it returns a single value — the cube root of the argument passed to the function.
VBA allows you to specify what type of information (also known as
data type) is returned by a
You can execute a
Function procedure from another procedure (a
Sub or another
Please note that no matter how hard you try, you can’t use the macro recorder to record a Function procedure. You must manually enter every Function procedure that you create.
Naming Subs and Functions
Like humans & pets, every
Function procedure must have a name.
Although it is perfectly acceptable to name anything what you want, it’s usually not a good idea to use such a freewheeling attitude when
procedures, you must follow a few rules:
You can use letters, numbers, and some punctuation characters, but the first character must be a letter.
You can’t use any spaces or periods in the name.
VBAdoes not distinguish between uppercase and lowercase letters.
You can’t embed any of the following characters in a procedure name: #, $, %, &, @, ^, *, or !
Procedurenames can be no longer than 255 characters. (Of course, you would never make a procedure name this long.)
Ideally, a procedure’s name describes the routine’s purpose.
Some programmers prefer using
sentence-like names that provide a complete description of the procedure.
Some examples include
The use of such lengthy names has pros and cons.
On the one hand, such names are descriptive and usually unambiguous. On the other hand, they take longer to type.
Everyone develops a naming style, but the main objectives are to make the names descriptive and to avoid meaningless names such as Update, Fix, and Macro1.
Next post will be about
Executing Sub & Function procedures.