Hi ,
There are two types of procedures in standard code modules ( I am excluding class modules here ) - subroutines and functions.
Subroutines basically perform a set of actions and exit ; they may or may not have parameters passed to them.
Functions perform a set of actions using parameters which have been passed to them , and return a result.
Functions can be further subdivided into standard functions , which can be called from subroutines or other functions , and UDFs ( User Defined Functions ) which are like native Excel functions , in that they need to be used in worksheet cells ; thus a UDF which concatenates the text from a range of cells ( something for which there is no native Excel function ) can be called by the following formula :
=ConCat(A1:A5)
The above formula can be entered in any worksheet cell , and the UDF will return the result in that worksheet cell.
To illustrate with a few examples , suppose we have a subroutine called ,
JustaMessage , and we code it as :
Code:
Public Sub JustaMessage()
MsgBox "Returning from ConCat procedure in " & Application.VBE.ActiveCodePane.CodeModule.Name
End Sub
This same procedure ( subroutine ) can be located in any Sheet section , in the ThisWorkbook section or in a standard code module.
If you click on Macros , you will see the following 3 macros :
JustaMessage -------------------- located in a standard code module
Sheet1.JustaMessage ----------- located in the Sheet1 section
ThisWorkbook.JustaMessage --- located in the ThisWorkbook section
You can run any of them , and you will see the message box with the appropriate message.
When we come to functions , functions will not be displayed in the Macros box , since functions are not meant to be run as standalone procedures ; they are either to be called from subroutines or other functions , or to be used in formulae in Excel worksheet cells.
Narayan