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.

 

[Wei-Dong Xu [MSFT]]

Hi ANE,

Thank you for posting in MSDN managed newsgroup!

The suggestion from Vasant will help you a lot in this scenario. You will need to use the array for the paramteter of Mode function.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[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))

 

[Wei-Dong Xu [MSFT]]

Hi Alesha,

Thank you for replying!

Furthermore, we can also work with the multiple arguments if you pass range objects instead of values. For example, each cell "A1, B1, ... J1"
cantains the number:1, 2, 1, 2, 3, 1, 2, 3, 4, 8(each cell contains one number). I write one sample codes for you:
'Code begin---------------------------
Sub Test_worksheetfunction_mode()

Dim result As Double
Dim objSht As Worksheet
Set objSht = Worksheets("Sheet1")

result = WorksheetFunction.Mode(objSht.Range("A1").Value, _
objSht.Range("B1").Value, _
objSht.Range("C1").Value, _
objSht.Range("D1").Value, _
objSht.Range("E1").Value, _
objSht.Range("F1").Value, _
objSht.Range("G1").Value, _
objSht.Range("H1").Value, _
objSht.Range("I1").Value, _
objSht.Range("J1").Value)
MsgBox result

End Sub
'Code end---------------------------

We will obtain the return value successfully without any mistake.

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Vasant Nanavati]

Or possibly:

Sub Test_worksheetfunction_mode()

Dim result As Double
Dim objSht As Worksheet
Set objSht = Worksheets("Sheet1")

result = WorksheetFunction.Mode(Range("A1:J1"))
MsgBox result

End Sub

to make it a bit shorter.

--

Vasant

Hi Alesha,

Thank you for replying!

Furthermore, we can also work with the multiple arguments if you pass

range objects instead of values. For example, each cell "A1, B1, ... J1"

cantains the number:1, 2, 1, 2, 3, 1, 2, 3, 4, 8(each cell contains one

number). I write one sample codes for you: