Sub and function procedure

Sub function

When programming VBA you write sequences of VBA statements in procedures in modules. All procedures in VBA are named. In standard modules you will use Sub and Function procedures.

Sub procedure

When you write a VBA macro, the code is in a Sub procedure. In its simplest form:

Sub MyFirstProcedure()
    MsgBox "Hello World", vbOKOnly + vbInformation
End Sub

When run this code displays a message box, as you would expect.

It is good practice to give all items in your program meaningful descriptive names so you can easily find them back later. For readability use Camel Case
Splitting your code in several procedures.

You write a procedure to automate a certain process or to handle an event. When you write more code you will notice certain parts of the code were already written before on another occasion. If this occurs it may be a good idea to isolate this code in a separate procedure, for example to test if a file exists. Note however that there are several reasons this should not be overdone:

  • Visual Basic is a mature business oriented language. The majority of procedures you need are allready built-in. Your task is to know about them and use them when appropriate.
  • If you change the code in a procedure which is called from several places, this will affect all the procedures that use it. This is good when you are fixing a bug, but it may be also work to your disadvantage when the task done in the called procedure is not very clearly determined. In that case the change which works fine for one procdure may be disruptive to another.
  • It is extra work to create a well-defined procedure for re-use. For example the below function CreateWorksheet is questionable because it captures one common aspect of adding a worksheet, the naming, but ignores others. In such a case it is better to re-use fragments such as descibed in Excel Add Worksheet
  • When debugging your code switching view from the procedure to a called procedure and back can be confusing - although this can be avoided by using 'Step Over (Shift-F8)'.
An alternative and modern way to organise your code well is by defining custom classes.
VBA Procedures Scope - Public vs Private

A vba procedure can have either Public or Private Scope which is determined by preceding it with the Public or Private keyword. A Private procedure can only be called by all procedures in the same module and will not be visible or accessible to procedures of outside modules in the project. A Private procedure will also not appear in the Macros dialog box. A Public procedure can be called by all procedures in the same module and also by all procedures of outside modules in the project. A Public procedure's name will appear in the Macros dialog box and can be run from there. If no scope keyword is not specified a procedure is considered to be Public by default.

Public Function Square(dbl As DoubleAs Double

Function procedure

A Function procedure is similar to a Sub procedure, but a function can also return a value. In the example below you see the return value is assigned using the function name, as if it were a variable.

Function Square(dbl As DoubleAs Double
    Square = dbl * dbl
End Function

The next function returns a newly creates worksheet with a name given to it in the process. Note that the name of the function is important for the user (programmer) to understand what the procedure will do. The Set keyword as part of the return value is used because it is an Object assignment (from class Worksheet).

Function CreateWorksheet(strName As String, Optional wb As Workbook) As Worksheet
    If wb Is Nothing Then Set wb = ActiveWorkbook
    Set CreateWorksheet = wb.Worksheets.Add(Type:=xlWorksheet)
    CreateWorksheet.Name = strName
End Function

Procedure Arguments

With most procedures you will have to specify what the data is that needs to be processed and possibly variables to control the logic. In the above function CreateWorksheet, there were two arguments, strName to give the name to the new worksheet replacing the automatically assigned name Sheet1, and wb allowing to specify another workbook than the one that happens to be the default. The name of the argument helps the user understand how he can use the function. In this sense wb may not be the most clear argument name. By specifying the type of the argument you help the compiler check if a call to the function has been done with acceptable values for the arguments.

Optional Arguments

When an argument is preceded by the keyword Optional, the procedure may be called leaving the argument empty. In some cases the procedure may remain totally ignorant of the argument and work fine. In the above example filling in for the optional argument was necessary and the procedure body filled in the missing workbook as ActiveWorkbook. An alternative for non-object arguments is to explicitly assign a default as part of the argument:

Sub xmplOptionalDefault(Optional str As String = "The Default")

Here, when called while leaving the str argument empty, the str value gets "The Default" assigned.

All arguments after an Optional argument must also be Optional.

Call Procedure with optional argument empty or specified

Sub CallProcedureDemo()
    Dim wsCreateWorksheet As Worksheet
    'call with optional argument empty
    Set wsCreateWorksheet = CreateWorksheet(strName:="a")
    Dim wbThisWorkbook As Workbook
    'call with all arguments specified
    Set wbThisWorkbook = ThisWorkbook
    Set wsCreateWorksheet = CreateWorksheet(strName:="a", wb:=wbThisWorkbook)
End Sub