A subroutine is a series of statements that performs a specific task.
Subroutines are also known as Procedures or Macros.
A subroutine can be any length but it is good programming practice to keep them under 30 lines.
You can exit a subroutine at any time using the 'Exit Sub' statement.

Public Sub Procedure_One() 
'do something
   Exit Sub
'exit the procedure before doing this
End Sub


There are a couple of ways to call a procedure from within another procedure, although using the Call keyword and enclosing your arguments in parentheses is the preferred method.
You can create your new subroutines by using the (Insert > Procedure) dialog box.
If you type it in directly the appropriate ending (either End Sub or End Function) will be inserted automatically.


Name - The name of the procedure you want to create.
Type - The type of procedure, either subroutine, function or property.
Scope - The scope of the procedure, either public or private. Public can be accessed from other modules. Private can only be accessed from this module.
All Local variabls as Statics -


By default all subroutines and functions are public which means they can be called by code in ANY other code modules.
For clarity it is often good to prefix your subroutines and function with the word "Public"

Public Sub Procedure() 
End Sub

Sub Procedure()
End Sub

Private procedures can only be called from within the SAME module.

Private Sub Procedure() 

End Sub

Any subroutine that has a Private scrope will not appear in the (Tools > Macro > Macros) dialog box.

Call Keyword

Any parameters must be enclosed in parenthese when using the Call keyword.
The Call statement can be used to call a procedure, function or dynamic link library procedure.

Public Sub Procedure_Name 
   Call DisplayTwoNumbers(20, 30)
End Sub

When calling a procedure you can omit the Call keyword.
If you do you must also omit the parenthese around your arguments.
Doing this makes you code harder to read because you can not quickly identify the procedure and function calls.

Public Sub Procedure_Name 
   DisplayTwoNumbers 20, 30
End Sub

Using the Call keyword though is encouraged as it makes it very clear that another procedure is being called.

Public Sub DisplayTwoNumbers(ByVal iNumber1 As Integer, _
                             ByVal iNumber2 As Integer)
Dim itotal As Integer

   itotal = iNumber1 + iNumber2
   Call Msgbox(itotal)
End Function

Public procedures can be called from anywhere
Private procedures can only be called from within the current module

Sub Procedure1() 
   Call MsgBox("You have entered procedure 1")
   Call Procedure2()
   Call MsgBox("You have returned to procedure 1")
End Sub

Sub Procedure2()
   Call MsgBox("You have entered procedure 2")
End Sub

When you call a public procedure that lies in another module there is a potential for ambiguity as there may be a procedure with the same name in another module
You should always fully qualify your procedure names with the name of the module.

Call ModuleName.ProcedureName() 

If necessary specify the project name as well

Call ProjectName.ModuleName.ProcedureName() 

Use Descriptive Names

This name should try and describe what the process that is performed by the procedure
Use meaningful names (ideally two words) and avoid using any keywords. Never use the names Run, Load Show etc.
Be careful using short names as certain names do not allow you to run them from the (Tools > Macro > Macros list) (eg t1, t2 etc).
Even though procedure and function names are not case sensitive
You can use a capital letter at the start of ever word to help presentation.
Here are some examples:

Sub CollectNumbers 
Sub SortFiles

Try to avoid using names that are meaningless like "PrintIt" or "Modify".

Exit Sub

This can be used when you want to exit from a procedure before the procedure terminates naturally.

Module Name Prefix

If you have more than one macro with the same name the one in the active template, documents or workbook takes precedence, then it's the global Add-Ins in alphabetical order, then the global template personal.xls or and then the Built-Ins.
When we call a public procedure that lies in another module there is a potential problem with ambiguity, for there may be more than public procedure with the same name in another module.
You will have to precede the variable, constant, procedure or function with the module name if there is a conflict.
To precisely identify a procedure or function in a project or file, prefix the procedure name with the module name.
VBA will execute the first one it finds.
Always qualify your procedures with the name of the module


When necessary you can even specify the project name as well


where the ProjectName has to be the code name of the project, not the filename. The default code name is always VBProject.


Any parameters must be enclosed in parentheses when you use the Call keyword.
To prevent your procedure macros from showing in the macro list prefix them with the word "Private" or you can add a dummy optional argument.
It is possible (but not by default) to have public procedures with the same name in different modules. If so you must always fully qualify your procedure names with the module name.

© 2018 Better Solutions Limited. All Rights Reserved. © 2018 Better Solutions Limited TopPrevNext