Using Functions in Sheet Modules in Excel

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 

End Function

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))

 Next

End Function

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s