Declaring a Procedure

The term procedure can have two completely different meanings: it can refer to either a sub or a function. A sub is simply a block of code that carries out a set of instructions. A function returns a value to whatever called it (usually another procedure).

A procedure does its job when it is called by name and given any arguments needed. Procedures usually are not isolated entities; they usually operate in symphony with each other. Imagine this scenario: Procedure A is called. In order for it to complete its job, it needs more specialized information from procedure B. So, in order to get it, it calls B. B, in turn, needs information from procedure C. Procedure C then sends information back to procedure B, which in turn sends information back to procedure A.

This scenario may sound a bit far-fetched to the beginning programmer, but it is actually common in VBA projects. Each procedure should have a specialized purpose. While you may think that would make the project unmanageable, it actually adds to the ease of manageability. If something is not working right, you can go directly to the procedure in charge of that particular job.

There is another benefit: specialization adds to the reusability of procedures. If you have a procedure, for example, that formats a number to the currency format, you can reuse that procedure in any project that needs that particular job done.

Finally, this method cuts down on code redundancy. If you have a procedure whose only job is to format a number to the currency format, it can be called from anywhere within the project. Thus, you won’t need to write the same code over and over.

Note

When speaking about concepts that encompass both subs and functions, I will sometimes just use the generic word “procedure.”

In its simplest form, a sub is declared as follows:

Sub Name_of_Sub_Procedure(Arguments) 

And a function is declared as follows:

Function Name_of_Function(Arguments) As Type

In both cases, the name must meet the following rules:

• It must start with a letter (rather than a number or special character like _). However, after that, it can contain any combination of letters and numbers.

• It cannot be more than 255 characters long.

• It cannot contain spaces. In general, try to follow naming conventions discussed in the first three chapters. You can sometimes use an underscore ( _ ) to simulate a space.

• It cannot use a VBA keyword.

• It must use parentheses to accept arguments, even if there are none.

As with variables, it is a good idea to use descriptive names so that the procedure is almost self-documenting.

Declaring a function is a bit more involved. Remember, a function returns a value. However, as with a variable, you have to declare what type of value you are going to be returning. The types that a function can return are the same types that variables use. So, if your function is going to return a string, you would declare it as:

Function myFunction() As String

You can also declare who has access to the procedure with the keywords Private and Public. See “Design and Scope,” later in the chapter, for more on this.

The sub and function constructs also include a statement to indicate where the procedure’s code ends. In the case of a sub, it is:

End Sub

For a function:

End Function

VBA gives us a handy tool to assist in building procedures and functions. It is called the Add Procedure dialog box and can be found in the Insert menu:

Here you can name the procedure, specify its type, decide whether it will be Public or Private, and even decide how the variables will be treated. If some of these options are unclear to you now, don’t worry! We will be treating them as we progress through this chapter.

At any given point, you can either use this dialog box or type the procedure name.