Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.access.queries,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.access.queries,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.access.queries,microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mode function in excel using Text | Excel Discussion (Misc queries) | |||
Mode Function or MODE Function -- Can't get it to work!! | Excel Discussion (Misc queries) | |||
how to get write access from read-onlly mode | Excel Discussion (Misc queries) | |||
3D References Mode() Function Excel | Excel Worksheet Functions | |||
how do I end function mode in excel | Excel Discussion (Misc queries) |