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?

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.


"LaTanya" wrote:

> 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 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.


On Thu, 3 Mar 2005 10:23:03 -0800, "'69 Camaro"
<(E-Mail Removed)_SPAM> wrote:

>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?
>
>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.
>
>
>"LaTanya" wrote:
>
>> 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
>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

I was forgetting the difference between worksheet functions that accept
array arguments, and those (including RANK()) that require Excel ranges.

On Thu, 03 Mar 2005 21:39:53 +0000, John Nurick
<(E-Mail Removed)> wrote:

>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.
>
>
>On Thu, 3 Mar 2005 10:23:03 -0800, "'69 Camaro"
><(E-Mail Removed)_SPAM> wrote:
>
>>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?
>>
>>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.
>>
>>
>>"LaTanya" wrote:
>>
>>> 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
>>>

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.