Excel statistis functions in access

[claudebo]

Hi

I would like to use the statistic functions from Excel in Access.
I have added “Microsoft Excel 5.0 Object Library” to the references.

I have tried:
________________________________________________________________
Dim obj As Object
Set obj = CreateObject("Excel.Application&quot
MsgBox obj.Application.median(1, 2, 3, 4, 5, 6, 7, 8, 12)
obj.Quit
Set obj = Nothing
________________________________________________________________

This works fine.

The problem arises with defining array in a way that Excel recognizes them.

I’ve tried
________________________________________________________________

Dim obj As Object
Set obj = CreateObject("Excel.Application&quot
MsgBox obj.Application.quartile([1, 2, 3, 4, 5, 6, 7, 8], 120)
obj.Quit
Set obj = Nothing
________________________________________________________________
Which Results in “Type Mismatch”
Excel help says to use {} in VBA to define array.
But that results in an error also.

Since there are a number of statistical functions I would like to use I prefer this method to writing VBA code for each function.



Thanks
Claude

 

[MichaelRed]

While there should be a variety of 'expression' formats which will work, the following illustrates one relatively easily adapted approach.

The BASIC issue is ye olde 'failure to communicate' error between yourself and the 'literature', as hte quart argument is restricted in its range. Please visit the ubiquitous {F1 (aka help) facillity re this specific issue.

Public Function basQuartile(Quart As Single, ParamArray MyAry() As Variant) As Single

    'Michael Red    10/13/2003  Tek-Tips thread705-678730 for claudebo
    '? basQuartile(1, 1, 2, 3, 4, 5, 6, 7, 8)
    ' 2.75

    '? basQuartile(3, 1, 2, 3, 4, 5, 6, 7, 8)
    ' 6.25

    Dim obj As Object
    Dim Idx As Integer
    Dim QuartVals() As Integer
    On Error GoTo ErrExit

    ReDim QuartVals(UBound(MyAry))
    While Idx <= UBound(MyAry)
        QuartVals(Idx) = MyAry(Idx)
        Idx = Idx + 1
    Wend

    Set obj = CreateObject(&quot;Excel.Application&quot;)

    basQuartile = obj.Application.quartile(QuartVals(), Quart)

    obj.Quit
    Set obj = Nothing

NormExit:
    Exit Function

ErrExit:
    Debug.Print Error(Err)
    Stop

End Function
[code]

MichaelRed
m.red@att.net

Searching for employment in all the wrong places