customizing worksheet functions

[pgianessi]

Hello everyone.

Does anyone know where the code of a worksheet function is?

I mean, for example, where in the file system is the file -and what is
its extension- that contains the definition of the SUM.IF() function?

Are worksheet function grouped in modules? Where are them? Can a user
add additional modules?

And, most of all, can a user in some way add other customized modules,
so that I can create a module MYFUNCTS.<EXT> with my own worksheet
functions and have them available by simply loading my module?

I know this question is a little bit weird.. thanks to anyone who will
answer me.

Bye

 

[dgp]

I'm not sure about where the built-in Excel functions are located, but
you can add custom functions to Excel by a few methods:
1) Use 'Tools>Macro>Visual Basic Editor' to insert a 'Module' in the
workbook you are using and write the custom functions there.
2) Use 'Tools>Macro>Visual Basic Editor' to insert a 'Module' in a
blank workbook then save the workbook as an add-in. Install the add-in
through 'Tools>Add-ins' and your custom functions will be available in
any workbook opened on your computer.
3) Beyond my experience, but you can also write custom functions in VB
or C? and compile them as add-ins.

If you use option 1 or 2, the functions will be written in Visual Basic
for Applications (VBA). The Visual Basic Editor Help is a good
resource, also you can find many examples in this newsgroup. Here is a
relatively simple example:

Function Air_visc(T)
' Viscosity of Air
' Air_visc - lbm/(in*s)
' T - Rankine
Air_visc = -1.3131831E-27 * T ^ 6 + 2.300535295E-23 * T ^ 5 _
- 1.644369666E-19 * T ^ 4 + 6.248831382E-16 * T ^ 3
_
- 1.393369936E-12 * T ^ 2 + 2.534826859E-09 * T _
- 1.414066691E-08
End Function

Slightly more complex, with explicit type definition (Double/Variant)
and a simple check on the input argument:
Function Air_visc(T As Double) As Variant
' Viscosity of Air
' Air_visc - lbm/(in*s)
' T - Rankine
If (T < 180) Or (T > 4500) Then
Air_visc = "Outside Temperature Range of Correlation"
Else
Air_visc = -1.3131831E-27 * T ^ 6 + 2.300535295E-23 * T ^ 5 _
- 1.644369666E-19 * T ^ 4 + 6.248831382E-16 * T ^
3 _
- 1.393369936E-12 * T ^ 2 + 2.534826859E-09 * T _
- 1.414066691E-08
End If
End Function

Functions can only return a value. Subroutines can modify cells,
sheets, toolbars, menus, call external programs, etc. For example, the
following sub does a find/replace on all selected cells. Again, you can
find many more examples in this newsgroup, and you can use
'Tools>Macros>Record New Macro' to help develop subroutines (but not
functions).

Sub FreezeRefs()
Selection.Replace What:="=", Replacement:="""=", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End Sub

I hope this helps get you started,
Dave Parker
West Palm Beach, FL