In Excel, worksheet modules can contain sub procedures that will run just like any other VBA procedure. The same is not true for function procedures. If you add the following function to the sheet module of Sheet1 and attempt to use it as a function in Sheet1, it will not be recognized (#NAME error).
Function Mult(a, b)
Mult = a * b
Indeed, it will not even show up in Excel’s intellisense when you try to type the function name in a cell.
However, the sheet level function can be recognized by a sub or function procedure that resides in a general module. So, for example, if the function shown above is placed in the sheet modules for Sheet1, Sheet2 and Sheet4 and cells A1 & A2 on those sheets contained values of (1,2), (3,4) and (5,6) respectively, the following function in a general module
Function MultResult(m, n)
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
MultResult = MultResult + wks.Mult(wks.Range(m), wks.Range(n))
will return the value 44 when entered in a cell as =MultResult(“A1”,”A2”).
A potential advantage can be gained by using this methodology since the sheets containing the functions in their sheet modules do not have to be in any particular position in the workbook for this technique to work. This is just a basic example of what can be done to take advantage of this Excel “quirk” in a productive way.