How do I call Excel functions with an array parameter?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Microsoft KB article 198571, "HOW TO: Call Excel Functions from Within
Microsoft Access 2000" gives examples of calling the Median, ChiInv, and
Least Common Multiple functions from Access. I had a need recently to call
the Excel Rank function, but I have been unsuccessful so far.

In Excel, the Rank function will rank ties with the same number. For
example, if you have four test scores as shown below, the rank would be as
indicated:

Score Rank
100 1
97.85 2
97.85 2
95.2 4

The format for using the Rank function in Excel is:
RANK(number,ref,order)

Number is the number whose rank you want to find.
Ref is an array of, or a reference to, a list of numbers. Nonnumeric
values in ref are ignored.
Order is a number specifying how to rank number.

The three examples in the KB article do not include an array or list of
numbers. That's where I was struggling. Does anyone have an example of
calling an Excel function from Access that involves passing an array of
numbers?

Thanks
 
Hi, LaTanya.
I had a need recently to call
the Excel Rank function, but I have been unsuccessful so far. .. . .
Does anyone have an example of
calling an Excel function from Access that involves passing an array of
numbers?
Click to expand...

That's one of the hard ways to generate rankings between records, because
Access can generate ranks for you. Please see the following Web page for
some examples:

http://support.microsoft.com/default.aspx?id=208946

How Access treats ties may be a little different than you expect, so
experiment and see what the results look like. And if you really must use
Excel, remember that an Excel array is not the same as a VBA array of some
data type. The Excel array is a range of cells in the worksheet.

That said, here's an example of how to pass a VBA data type array to any
Excel function that absolutely must have a "Range" data type:

' * * * * CODE START * * * *

' Note: This subroutine requires the Microsoft Excel 9.0 Object Library
reference for Access 2K.
' "numToRank" is the value to rank and "numArray( )" is the array of values
to be used in the calculation.

Public Function callExcelRank(numToRank As Double, numArray( ) As Double) As
Long

On Error GoTo ErrHandler

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim rng As Excel.Range
Dim savefile As String
Dim idx As Long
Dim fOpenedApp As Boolean
Dim fOpenedBk As Boolean

savefile = "C:\Test\TestExcel.xls" ' Use any path and file name you want.

Set xlApp = CreateObject("Excel.Application") ' Use Office Automation
to open Excel.
fOpenedApp = True
Set xlBook = xlApp.Workbooks.Add
fOpenedBk = True
Set xlSheet = xlBook.Sheets(1)

'------------------------------------------------------------------
' Insert array values into individual cells in spreadsheet.
'------------------------------------------------------------------

For idx = 1 To UBound(numArray)
xlSheet.Cells(idx, 1).Value = numArray(idx)
Next idx

'------------------------------------------------------------------
' Create the "Range" for the "Rank" calculation.
'------------------------------------------------------------------

Set rng = xlApp.Application.Range(xlSheet.Cells(1, 1),
xlSheet.Cells(UBound(numArray), 1))
callExcelRank = xlApp.Application.Rank(numToRank, rng)

'------------------------------------------------------------------
' Save spreadsheet to avoid having to interact with Excel.
'------------------------------------------------------------------

xlBook.SaveAs savefile

CleanUp:

Set xlSheet = Nothing

If (fOpenedBk) Then
xlBook.Close
fOpenedBk = False
End If

Set xlBook = Nothing

If (fOpenedApp) Then
xlApp.Quit
fOpenedApp = False
End If

Set xlApp = Nothing

DoEvents ' Give O/S enough time to complete saving file.
Kill savefile ' Delete file 'cuz we don't need it.

Exit Function

ErrHandler:

MsgBox "Error in callExcelRank( ) in Module1." & vbCrLf & vbCrLf &
"Error #" & _
Err.Number & vbCrLf & Err.Description
GoTo CleanUp

End Function ' callExcelRank( )

' * * * * CODE END * * * *

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts, so that all
may benefit by filtering on "Answered questions" and quickly finding the
right answers to similar questions. Remember that the best answers are often
given to those who have a history of rewarding the contributors who have
taken the time to answer questions correctly.
 
Hi Gunny,

Sub TestXLWorksheetFunctions()
Dim oXL As Object
Dim L(4) As Long
Dim S As String
Dim lMax As Long, lMin As Long
Dim j As Long

Randomize
For j = 0 To UBound(L)
L(j) = 1000 * Rnd
S = S & CStr(L(j)) & vbCrLf
Next

Set oXL = CreateObject("Excel.Application")

lMax = oXL.WorksheetFunction.Max(L)
lMin = oXL.WorksheetFunction.Min(L(0), L(1), L(2), L(3), L(4))
MsgBox S & vbCrLf & _
"Max = " & CStr(lMax) & vbCrLf & "Min = " & CStr(lMin)

oXL.Quit
Set oXL = Nothing
End Sub

One of us is missing something; if it's me, please tell me what.
 
I was forgetting the difference between worksheet functions that accept
array arguments, and those (including RANK()) that require Excel ranges.
 
Back
Top