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()
'exit the procedure before doing this
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()
Private procedures can only be called from within the SAME module.
Private Sub Procedure()
Any subroutine that has a Private scrope will not appear in the (Tools > Macro > Macros) dialog box.
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)
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
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
Public procedures can be called from anywhere
Private procedures can only be called from within the current module
Call MsgBox("You have entered procedure 1")
Call MsgBox("You have returned to procedure 1")
Call MsgBox("You have entered procedure 2")
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.
If necessary specify the project name as well
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:
Try to avoid using names that are meaningless like "PrintIt" or "Modify".
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 normal.dot 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.