Calling Excel worksheet functions (1 Viewer)

U

Uncle Phil

Guest
It appears that I should be able to use Excel worksheet functions from Access. Just at the moment, I simply want to calculate medians, percentiles and so forth for a recordset selected in a query.

BUT: however I try to call the worksheet function, Access returns "undefined function" or some such message.

I have set references to what I think are the relevant files, including
Microsoft Excel 9.0 object library
Microsft Office web components function library
Microsoft DAO 3.6 object library

I have tried calling the function from within an expression in a query, ie.
ExpressionName:Application.WorksheetFunction.Median(recordsource)

and also setting up as a user-defined function in VB:

Function Percentile(range, Percent)
Percentile = Application.WorksheetFunction.Percentile(range, Percent)

End Function



I've tinkered with both of these for some hours, but got nowhere.

Access help is less than helpful, and VB help not much more so.

Anyone got any clues on this? Don't be too technical please - I'm not a techie, I'm a biologist!
 

pdx_man

Just trying to help
Local time
Today, 16:52
Joined
Jan 23, 2001
Messages
1,347
Your References look right ... You must Dimension a variable as an Excel Object, then call the function from there.

Dim objXLS As New Excel.Application

Then call the function:

MyVariable= (objXLS.Days360(dteDate1, dteDate2))
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:52
Joined
Feb 19, 2002
Messages
42,981
I doubt that you can substitute a recordset object for cell references so I don't believe that you can use this type of excel function in Access.

You can make your own functions though. Here is code that I copied some time ago. I have never used it so I can't vouch for it. The poster says it was based on an article from the Microsoft knowledgebase if you care to look for it.

Code:
Posted by Kem E. on April 07, 2000 at 16:58:56:

I have modified the derive median from code from MS so that it computes a median for each group. Below is 

the code. 

tblName = table or query name
fldName = field/column in table you want to get median
fldGrpName = field/column name in table you want to group on
ctlName = NUMERIC field (should be same as fldGrpName)

ie. ComputeMedian("qryRent","rent","category",[category])

I actually put it in a query as an expression and it worked but it might take a long time. The main 

component is the strSQL. Modify it to suit your needs.

Function ComputeMedian(tblName, fldName, fldGrpName, ctlName) As Long
Dim MedianDB As Database
Dim ssMedian As Recordset, strSQL As String
Dim fldGrpNum As Variant
Dim RCount, i, X, y, OffSet, Median2

Set MedianDB = CurrentDb()

fldGrpNum = ctlName

' Create SQL string.
strSQL = "SELECT [" & fldName & "] FROM [" & tblName & "] WHERE [" & _
fldGrpName & "] = " & fldGrpNum & " AND [" & fldName & "] is not null ORDER BY [" & fldName & "] asc"

Set ssMedian = MedianDB.OpenRecordset(strSQL)
If ssMedian.RecordCount = 0 Then
    Median2 = -1
    ComputeMedian = Median2
Else
    ssMedian.MoveLast
    RCount = ssMedian.RecordCount
    X = RCount Mod 2
    If X <> 0 Then
        OffSet = ((RCount + 1) / 2) - 2
        For i = 0 To OffSet
        ssMedian.MovePrevious
        Next i
        ComputeMedian = ssMedian(fldName)
    Else
        OffSet = (RCount / 2) - 2
        For i = 0 To OffSet
        ssMedian.MovePrevious
        Next i
        X = ssMedian(fldName)
        ssMedian.MovePrevious
        y = ssMedian(fldName)
        ComputeMedian = (X + y) / 2
    End If
End If
ssMedian.CLOSE
MedianDB.CLOSE
End Function
 

Jon K

Registered User.
Local time
Today, 23:52
Joined
May 22, 2002
Messages
2,209
In Excel we can pass a range of values to the functions e.g.
=Median(A1:A4)
=Percentile(A1:A4, 0.3)

In Access we don't have ranges, but we can pass values to these WorksheetFunctions in an Array.


In the attached Access 2000 database, I have included two methods that you can use depending on whether the data in a table are stored vertically in a column or horizontally in a row.

For vertical data, I used a form. When the command button on the form is clicked, the Median and the 30th Percentile will be calculated from table "Vertical Data" and displayed in two text boxes on the form. The code used is in the On Click event of the command button:-
Code:
Private Sub cmdCalculate_Click()
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim [b]arrayNum[/b]
    
   Set db = CurrentDb
   Set rs = db.OpenRecordset("SELECT [Num] FROM [Vertical Data]" & _
             " WHERE [Num] IS NOT NULL")
   
   rs.MoveLast
   rs.MoveFirst
   [b]arrayNum[/b] = rs.GetRows(rs.RecordCount)
    
   Me.txtMedian = WorksheetFunction.Median([b]arrayNum[/b])
   Me.txtPercentile = WorksheetFunction.Percentile([b]arrayNum[/b], 0.3)
    
   Set db = Nothing
   Set rs = Nothing
    
End Sub


For horizontal data, I used a query and two public functions:-

Query Horizontal Data:
SELECT [ID], val(getMedian([Fld1],[Fld2],[Fld3],[Fld4])) AS Median,
val(getPercentile(0.3,[Fld1],[Fld2],[Fld3],[Fld4])) AS [30th Percentile]
FROM [Horizontal Data];

.
Code:
Public Function getMedian(ParamArray aNum())
   getMedian = WorksheetFunction.Median(aNum)
End Function

Public Function getPercentile(Pcnt, ParamArray aNum())
   getPercentile = WorksheetFunction.Percentile(aNum, Pcnt)
End Function


Note
In Excel, the range can contain null values. In Access, null values will result in a type mismatch error in the query. (You can use code in the public functions to remove null values from the array if necessary.)

In the database, references have been made to the Microsoft 3.6 Object Library and the Microsoft Excel 9.0 Object Library.
.
 

Attachments

  • Using Excel WorksheetFunctions in Access 2000.zip
    25 KB · Views: 399
Last edited:

Rose412

Registered User.
Local time
Today, 23:52
Joined
Aug 26, 2003
Messages
46
Removing null values from an array.

Nice example Jon!

I have data that are stored horizontally in rows and some fields may contain null values. I'm no good at using arrays. Can you show me how to remove the nulls from an array. Thanks in advance.

Rose
 

Jon K

Registered User.
Local time
Today, 23:52
Joined
May 22, 2002
Messages
2,209
Manipulating values in an array is a complicated task. Instead we can use a new array to hold the non-null values.

To use the getMedian() function as an example, we could use code to copy the non-null values from array aNum to a new array aNum2, and pass this new array to the WorksheetFunction:-
Code:
Public Function getMedian(ParamArray aNum())
   Dim aNum2()
   Dim vItem
   Dim i As Integer
   
   For Each vItem In aNum
      If Not IsNull(vItem) Then
         [b]ReDim Preserve aNum2(i)[/b]
         aNum2(i) = vItem
         i = i + 1
      End If
   Next vItem
   
   getMedian = WorksheetFunction.Median(aNum2)
End Function

When we resize aNum2 with ReDim, we must use the Preserve keyword to retain the values that have already been copied to aNum2.
.
 

Rose412

Registered User.
Local time
Today, 23:52
Joined
Aug 26, 2003
Messages
46
That did it. Thank you very much Jon.

Rose
 
U

Uncle Phil

Guest
Thanks Jon for your advice. Yes, I had to move onto something else, but now I've come back to my (still unsolved) problem.

You've shown how to calculate the percentile for horizontal data in a query and for vertical data in a form.

What I want is to calculate the percentile for vertical data in a query, perhaps by means of a user-defined function, applied to a recordset selected earlier in the query.

I perhaps ought to be able to work out how to do this by re-arranging your two examples - but I'm a bear of very little knowledge of VBA.

Any thoughts?
 
U

Uncle Phil

Guest
Thanks Jon for your advice. Yes, I had to move onto something else, but now I've come back to my (still unsolved) problem.

You've shown how to calculate the percentile for horizontal data in a query and for vertical data in a form.

What I want is to calculate the percentile for vertical data in a query, perhaps by means of a user-defined function, applied to a recordset selected earlier in the query.

I perhaps ought to be able to work out how to do this by re-arranging your two examples - but I'm a bear of very little knowledge of VBA.

Any thoughts?
 

Jon K

Registered User.
Local time
Today, 23:52
Joined
May 22, 2002
Messages
2,209
I have added a function, a one-row table and a query in the database.
.
 

Attachments

  • Pencentile Access 2000.zip
    29.2 KB · Views: 315

Users who are viewing this thread

Top Bottom