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

Inserting

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 -


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.


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