Introduction

In the MS Excel’s Visual Basic Editor, Sub is a statement, like a function fun(), that performs a specific task.

A sub is a piece of code that performs a specific task and does not return a result.

The purpose of a subroutine is to make the code more readable and manageable by breaking down large pieces of code into smaller parts. Sub doesn’t return any value, but by contrast, functions always return some value. We can call a sub procedure in another procedure by simply typing the name of the Sub after the Call statement.

Why use Sub?

Break the code: An average computer program contains hundreds to thousands of source code lines. This creates complexity, ambiguity, and difficulty to run, read, and debug the code. Subroutines help us by dividing a large program into smaller sections. This also saves time and effort to debug program errors. Moreover, whenever a change is required in the program, it is always easier to amend the code when it is divided into specific sections.

Reusability: By writing, the name of the sub after the Call statement, sub procedures can be reused in the entire program repeatedly. This saves us from the hassle of repeating the same lines of code again and again.

Let’s say we want to perform arithmetic calculations in our Excel workbook multiple times. Instead of writing the same code again and again, we can simply create a Sub Calculate() procedure. We can write the code inside the Calculate subroutine and simply use the Call statement to reuse the Calculate Subroutine code in the main program.

Sub and fun are self-documenting: Let’s assume you have a function called Area and another that says DeleteRows. The programmer can easily tell what the program does by just looking at the name. Therefore, VBA programming by Sub and Fun becomes very manageable and easy to decode.

Syntax of Sub

To successfully compile a sub procedure, a specific syntax needs to be followed. To declare a sub, we first have to assign a unique name to the sub.  The sub should also contain a body where the code is written.

You must declare the sub “Sub” keyword before writing the name of the sub. It is always advisable to assign a unique name that specifies what kind of task the subroutine will perform. For example, if you want to copy data then the name of sub should be CopyData.

The following rules must be kept in mind while declaring the name of the sub:

• The name must begin with a letter and should not contain spaces.
• The name must be unique and should describe the routine’s purpose.
• The name cannot be a reserved (pre-defined for language) word in VBA.
• You can’t use any special characters while naming the Sub: #, $, %, &, @, ^, *, or! .All these are reserved words for pre-defined purposes.

 The body of the sub should be followed by an ending statement “End Sub” .

Please find below the syntax:

[ Private |Friend| Public] [ Static] Sub name [(arguments)]
‘do something
End Sub

Syntax with descriptions

Part	Description
Public	Optional. If a subroutine is Public, then this particular Sub is accessible to all other procedures in the entire program. You don’t necessarily have to write the word Public before the Sub procedure. By default, all subroutines are Public.
Private	Optional. Private Sub procedures are only accessible to those procedures that are stored within the same module. We have to write the word Private  before writing the name of the Sub. For examples, Private Sub CopyRows().
Friend	Optional. When a subroutine is declared as Friend, this allows the routine to be accessed from modules that are outside the class , but part of the project within which the class is defined.
Static	Optional. If a subroutine is declared as Static, all the local variables defined in the procedure are allocated storage space once only. Every time the program is running the value of variables is preserved.
Name	Required. This is the unique name of the Sub
Arglist	Optional. The list of variables that are passed to the subroutines is known argument list. The argument list can compromise of single or multiple variables.
Statements	Optional. Lines of code that specifies what task the subroutine has to perform.

Call Sub Statement Syntax

The screenshot below displays a subroutine Main in which multiple subroutines are being accessed using the Call statement.

Calling sub procedures with more than one argument

The screenshot below depicts two ways to Call a subroutine. The subroutine PriceCalc has two arguments. As seen in the screenshot below, the PriceCalc subroutine has been accessed in the Main subroutine two ways:

Use parentheses when calling function procedures

As mentioned earlier, function procedures return values. If we want to utilize the return value of a function procedure, we first have to assign the value of a function to a variable and use the parentheses to enclose the argument. In the example below, the MsgBox function return value is being stored in a variable called Answer1

However, If you don’t want to utilize the return value(s) of a function, you can call the function without enclosing the arguments in parentheses. Please see the example below:

Passing named arguments

By using the named argument functionality in VBA, we can pass values to called procedures in a sub or a function. The named arguments can be defined in any order.

In the example below, Title and Prompt are named arguments for the function MsgBox.

Conclusion

• Sub and Function are very similar, but Sub doesn’t return any type of value and fun has a return type.
• Subroutines follow the “divide & conquer” rule to make the lives of programmers easy.
• Sub procedures help to increase the reusability and reliability of the code.
• Sub procedures can be accessed in other procedures using two different statements.
• Function procedures’ return values can be stored in variables.

See also:

Sub or Function Not Defined Errors

How to use Exit Function