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.