User Defined Functions in Calculated Fields

[doco]

I have been looking for information on how to use User Defined Functions in Calculated Fields on a Report (Using MS Access 2003 Pro).

I need to use more than the six or eight Stat functions Access comes with. So I created some user defined functions that incorporate Excel.WorksheetFunction.Function. Which should work fine. The problem is the UDF's won't even fire from the report - they just return #ERROR without firing.

I have also used the wizard to 'point' to those functions in the Functions|User Defined Functions list still producing the error.

Interestingly, searches on Google, this site and others return almost no results. The 16 hits from this site typically pose the question with no responses. The two Access books I have do not even mention User Defined Functions.

Any source would be appreciated

TIA

 

[boblarson]

Where and how did you create the functions?

 

[doco]

Added a standard module in Access then
EG.

Public Function Median(ByVal aField As DAO.Field) As Double

    Median = Excel.WorksheetFunction.Median(aField)
    
End Function

Added a standard TextBox control on a report footer with ControlSource =FORMAT(Median([Ratio]), "Standard")

[Ratio] being a field on the Report Detail

NOTE:
Whether the syntax of the Function is correct or not is not the issue at this point. What is the issue is the Function does not get called; placing a break point revealed this...

 

[doco]

It seems the problem is not with calling the function as Access is not letting me use Excel worksheet functions wrapped in a UDF. I do have a reference set to Excel Objects x.0. But still produces an error. Go figure!

I was forced to hand build Median, AverageDeviation, GeometricMean, etc. ARGH! Really sucks!

Any ideas on how to re-create the FREQUENCY() function