Passing argurments to excel functions or addins in access

[Neuder]

Being a rookie at VB, I have found references to using Excel functions or
add-ins in access. I still am not clear on how to use them for data that
exists in a table or query. As an example the Median function in excel. I
know there are ways to program for the median in VBA but I'm using this as an
example of my problem. If I have a table with the following items:

Week SCC Milk
1/1/04 234 72.5
1/8/04 225 68.7
1/15/04 228 74.6
1/22/04 210 77.9

And I want to use an excel function such as Median or an addin like
statistical tools, how do I get the field data into the argument?

Public Function xlMedian( FName as Single) As Double
xlMedian = Excel.Application.Median(FName)
End Function

The knowledge base articles use keyboard input but I'm not sure how to use
existing data? Do I need to create some type of array first?

Again I'm a newbie but appreciate the help found in this forum!!

Dr. Lou Neuder
Green Meadow Farms
Elsie, Michigan
(e-mail address removed)

 

[MikeC]

Neuder,

The Median function itself, requires a cell range so, at a
minimum, a range must be passed to the function. In
addition, Excel needs to know the location of the workbook
file and the name of the sheet. Each of these items will
be passed as a parameter.

The original name of your function has been preserved.
The "xlMedian" function is executed as follows:

xlMedian WorkBookPath, SheetName, UprLeft, LwrRight

The function will return Excel's Median value as a Double.

Here's the function:

Public Function xlMedian(WorkBookPath As String, SheetName
As String, _
UprLeft As String, LwrRight As
String) As Double
On Error GoTo Err_xlMedian

Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim xlRange As Excel.Range

Set xl = CreateObject("Excel.Application")

xl.Visible = True
Set wkb = xl.Workbooks.Open(WorkBookPath)
Set wks = wkb.Worksheets(SheetName)
Set xlRange = wks.Range(UprLeft & ":" & LwrRight)

xlMedian = xl.WorksheetFunction.Median(xlRange)

Exit_xlMedian:
On Error Resume Next
wkb.Close False
Set wkb = Nothing
Set wks = Nothing
xl.Quit
Set xl = Nothing
Set xlRange = Nothing
Exit Function

Err_xlMedian:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number &
vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_xlMedian

End Function

 

[Jamie Collins]

The Median function itself, requires a cell range so, at a
minimum, a range must be passed to the function.

Are you sure? Looking in the Excel class in the Object Browser, the
definition is:

Function Median(Arg1, [Arg2], [Arg3], [Arg4], [Arg5], [Arg6], [Arg7],
[Arg8], [Arg9], [Arg10], [Arg11], [Arg12], [Arg13], [Arg14], [Arg15],
[Arg16], [Arg17], [Arg18], [Arg19], [Arg20], [Arg21], [Arg22],
[Arg23], [Arg24], [Arg25], [Arg26], [Arg27], [Arg28], [Arg29],
[Arg30]) As Double

Jamie.

--

 

[MikeC]

Jamie,

This custom function is designed to pass one Excel range
at a time. The function can be called a second, third,
etc. time to provide median value's for other ranges. The
ranges described in Neuder's original post were contiguous
ranges, so this function has been designed for that
purpose.

I suppose it could have been designed to pass multiple
ranges to Excel's built-in function, but I didn't believe
that would be necessary based on the original post.

The function worked fine in testing. Give it a try if you
are interested.

-----Original Message-----

The Median function itself, requires a cell range so, at a
minimum, a range must be passed to the function.

Are you sure? Looking in the Excel class in the Object Browser, the
definition is:

Function Median(Arg1, [Arg2], [Arg3], [Arg4], [Arg5], [Arg6], [Arg7],
[Arg8], [Arg9], [Arg10], [Arg11], [Arg12], [Arg13], [Arg14], [Arg15],
[Arg16], [Arg17], [Arg18], [Arg19], [Arg20], [Arg21], [Arg22],
[Arg23], [Arg24], [Arg25], [Arg26], [Arg27], [Arg28], [Arg29],
[Arg30]) As Double

Jamie.

 

[Jamie Collins]

This custom function is designed to pass one Excel range
at a time.

The function worked fine in testing. Give it a try if you
are interested.

Here's my test:
Excel.Application.WorksheetFunction.Median(1, 2, "3", Array(4,5,6),
Now, CDate("22 SEP 2004"))

Here's the answer:
4.5

Here are my conclusions: The Median function itself does not 'require
a cell range'. When you do pass it a Range, Value is its default
property in scalar context so VBA coerces the Range object to an array
of values. Saying the function 'requires a cell range' is a red
herring.

Jamie.

--