VBA Subs

A sub, or subroutine, is similar to a function in that it consists of a block of code that performs a specific task, however, unlike functions, a sub cannot return a value. Like functions, the statements within a sub are only executed when it is called and a sub can be used multiple times within a program. In VBA, a sub can be defined and called as follows.

' Sub definition.
Sub sub_name(parameter_name As parameter_type)

   ' Statement(s) to execute.
    
End Sub

' Sub call.
sub_name(parameter_value)

The sub name is not case sensitive, so ‘sub_name’ and ‘Sub_Name’ are both valid calls to the above sub. Although numbers can be used within a sub name, it must always start with a letter. It is also possible to include more than one parameter in a sub call and definition by separating them with a comma.

The example sub below takes an Excel spreadsheet cell reference, in the form of a string parameter and displays the message ‘Hello world’ in the desired cell, in this case, cell ‘A1’.

' Sub definition.
Sub Message(messageCell As String)

   Range(messageCell).Value = "Hello world"
    
End Sub

' Sub call.
Message("A1")

It is possible to make a parameter for a sub optional by using the ‘Optional’ key word and specifying a default value. Here, ‘A1’ is given as the default value for the ‘messageCell’ parameter, meaning that the sub can be called with no parameter value specified.

' Sub definition.
Sub Message(Optional messageCell As String = "A1")

   Range(messageCell).Value = "Hello world"
    
End Sub

' Sub call.
Message

Another example of where a sub is used is with a button, as shown in the ‘Getting Started‘ section. Here, the sub is executed each time the button is clicked.