Financial Analysis and Modeling Using Excel and VBA

Chapter 23. Sub and Function Procedures

Earlier I defined a VBA procedure as a recipe or an organized set of statements written in the VBA language to carry out certain tasks. There are only two types of VBA procedures : Sub procedures and Function procedures. A Sub procedure performs a number of actions, but does not return a value. A Function procedure is similar to a Sub procedure in that it can also perform actions, but in addition it can return a value, which can be a single value or an array.

A model may contain any number of procedures. You may view procedures as the way to organize in logical, manageable groups all the instructions VBA will need to do what the model is supposed to do.

You need to learn to develop and use both types of procedures, and this is what I will cover in this chapter.

Sub Procedures

Sub Procedures

A Sub procedure can perform a number of actions but cannot return a value.

THE STRUCTURE OF SUB PROCEDURES

A somewhat simplified structure of a Sub procedure is :

Sub Procedure_Name([argumentlist])
    [Statements]
    [Exit Sub]
    [Statements]
End Sub

The first line is called the declaration line of the procedure; it and the last line are required. As a matter of fact, after you type in the first line in a module to start a new Sub procedure and press Enter, VBA will automatically enter the last line. Then you can insert statements in between. Every Sub procedure must have a name. As with variables, short but descriptive names are best. I prefer to use names that use a ...