Functions and Procedures

In VB and VBA, there are several options for storing and accessing blocks of code, and for structuring different parts of a project. For larger projects it is a good idea to separate code into different Modules which represent either a particular part of the application, or groups of Functions and Procedures that relate to each other, in a form for example.

In VB, the various forms of an application usually have their own Code Module, but another Module could be used to store procedures relating to it, rather than putting them all together. The same idea could be applied to Spreadsheet command buttons or User Forms in Excel, and Forms in Access. This way, larger or more complex solutions are broken down into smaller parts, which are easier to manage and update.

When a command button is performing a number of tasks sequentially, or even when it is necessary to perform certain tasks dependant on decisions or the results of other functions being successful, it is much easier to manage and debug, when the individual tasks have their own function or procedure, then the command button can call these as necessary. Although it is worth considering which functions or procedures should be declared Public and those which need to be Private?

If a Sub procedure or function is only going to be used in that Module, then it should be declared as Private; all Spreadsheet and User Form procedures are declared Private by default, this means that they cannot be access outside that particular Module. If you will need to call it from another Module then you’ll have to Declare them as Public, but it is usually a good idea to put the ‘Option Private Module’ statement right at the top of the Module code, so it cannot be accessed from outside the project, which in Excel also means they don’t appear in the Run Macro list.

The key difference in VB between a function and a procedure is that a function usually returns a value, whereas a sub procedure doesn’t. This can be especially useful for when you need to base other parts of code on the outcome of a function, whereas a Sub just needs to do something. One example of this is the Outlook Email example, where a Sub procedure creates the actual Email, but Functions are used to get the To, Cc, and Message text strings.

Functions can be written to return any Data Type, so for example, you could have a Function to return a Boolean (True or False) value to determine if the function was successful or not, and that can be used to define what other actions or functions are run. A Boolean function can also be used to control user access to parts of a file or application. This is a subject I go into more in the User Access Controls article.