Subroutines

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

Scope

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.


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".


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

ModuleName.ProcedureName 

When necessary you can even specify the project name as well

ProjectName.ModuleName.ProcedureName 

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


Important

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.


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