Using Excel WorksheetFunction.Mode function in Access VBA
 

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

Using Excel WorksheetFunction.Mode function in Access VBA
 

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


"ANE" wrote in message
om...
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

Using Excel WorksheetFunction.Mode function in Access VBA
 

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.

Using Excel WorksheetFunction.Mode function in Access VBA
 

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

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

--

Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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


"ANE" wrote in message
om...
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

Using Excel WorksheetFunction.Mode function in Access VBA
 

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.

Using Excel WorksheetFunction.Mode function in Access VBA
 

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

"Wei-Dong Xu [MSFT]" wrote in message
...
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.