Using Excel WorksheetFunction.Mode function in Access VBA

[ANE]

Hi-

I would like to use the Excel Application Worksheet Mode function
in Access. Is it possible? I know that the following code works:

set the reference to microsoft excel library

Sub xlMedian()
Dim obj As Excel.Application
Set obj = CreateObject("Excel.Application")
MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13, 1, 1)
obj.Quit
Set obj = Nothing
End Sub

However, if I replace Median with Mode I get an error: "Type Mismatch"


If I try WorksheetFunction.Mode(1, 2, 5, 8, 12, 13, 1, 1)
With Sheets("Sheet1")
MsgBox WorksheetFunction.Mode(.Range(.Range("F2"), .Range("G2")))
End With

I receive the message
"unable to get the Mode property of the WorksheetFunction class"


I am very new to writing Excel VBA code. The answer maybe obvious,
however, searching for it seems to be a time sink!

Is it possible to do this without setting the ActiveCell formula.?

-Thanks so much.

ANE

 

[Vasant Nanavati]

I'm not quite clear on your goal. Are you trying to find the mode for the
array (1, 2, 5, 8, 12, 13, 1, 1) or for the values in the range F2:G2?

If the former, try:

WorksheetFunction.Mode(Array((1, 2, 5, 8, 12, 13, 1, 1))

For some reason that seems to work only with an array-type argument.

It the latter, you will get an error if your range does not contain
duplicate data points. Since F2:G2 only has two data points, they would need
to be identical for Mode to return a value.

I hope I have understood your question correctly.

 

[Vasant Nanavati]

Sorry, I messed up the parentheses (I'm always doing that!). Should be:

WorksheetFunction.Mode(Array(1, 2, 5, 8, 12, 13, 1, 1))