1800-222-WTF

Excel & VBA

18 Feb 2018

VBA Procedures, Subroutines and Functions

/
Posted By
/
Comments1
/

Procedures

A VBA structure is said to be constructed from procedures, or sections of code within a larger program. Each procedure performs a set of specific tasks and is relatively independent of the remaining code. There are, however, two types of procedures:

  1. Subroutine: A procedure that performs a task and then terminates.
  2. Function: A procedure that performs a task (perhaps some calculations) and returns a value or result back to the parent procedure.

A program will always contain at least one procedure. More complex code can warrant having many procedures to achieve different tasks. It is advisable for larger projects to break down complex operations into smaller more manageable procedures. This has several benefits, which include:

  1. Promotes a decrease in code duplication
  2. Reduced complex problems into smaller simpler peices
  3. Improves program readability

Subroutines

A subroutine begins with the keyword Sub followed by the name of the subroutine and then a set of parenthesis. Optional parameters can be placed inside the parenthesis, but we will learn more about that in a later lesson. Each subroutine will be terminated with the keyword End Sub, which defines the code block. All of your code should sit in between the Sub and End Sub statements. Below is an example subroutine:

If you want to have a go and executing the subroutine above, copy the code and paste it in the Visual Basic Editor and hit ‘F5’ on your keyboard! This subroutine will launch a simple message box on the screen.

Functions

Similar to a subroutine, a function begins with the keyword Function followed by the name of the function and a set of parenthesis. Again, optional arguments would be placed inside the parenthesis but we’ll get to that later. This type of procedure is terminated by the keyword End Function. See an example below:

Functions must return a result. This is achieved by assigning (using the equals sign) a value to the name of the function. In the trivial example above, the function would return the number 42 to the main subroutine.

Naming Procedures

In general you can call your subroutines and functions whatever you like. However, there are some rules you need to abide by.

  1. The name must begin with an alphabetic character, the rest of the name may contain alphanumeric characters and the underscore symbol ( _ ).
  2. The name must be unique.
  3. The name must not be longer than 255 characters.
  4. The name must not be a protected keyword (like Sub or Function)

We will dive into naming convention in a bit more detail once we start looking into variables (sit tight for the next lesson).

Calling Procedures

When building larger coding projects with lots of procedures you will likely want to trigger or call a procedure from within another. This is achieved by using another VBA keyword Call followed by the name of the procedure. Consider the example below:

Assume the program starts with the subroutine named Parent, on entering the Parent subroutine, the first statement encountered is Call FavoriteChild. At this point, all control is given to the FavoriteChild subroutine, and it will start executing its own lines of code. At the end of the FavoriteChild subroutine, control is returned to the Parent. The next statement encountered is the another subroutine Call OtherChild, program control is passed over so that it can run its code before relinquishing control back to the Parent subroutine. As there are no more statements remaining, code execution would terminate.

Strive for progress... not perfection...