calling Excel median function in a macro

[johncollier1]

Hi Guys
I'm trying to use this code snippet to use the excell median function to work out the Median value from a number of feilds in a record

Sub xlMedian() 
Dim obj As Excel.Application 
Set obj = CreateObject("Excel.Application")
 MsgBox obj.Application.Median(1, 2, 5, 8, 12, 13) 
obj.Quit Set obj = Nothing End Sub
[CODE]
 
do I have to create an variable or array of all the fields or can I just substitute the highlighted numbers for field names?
 
O' and what will happen if some of the fields in the record are null
will they be ignored, count as zero or cause an error?
 
Cheers for looking guys
John

 

[jdraw]

I know you were looking to use Excel's median, but here's a median function in vba

'---------------------------------------------------------------------------------------
' Procedure : MedianF
' Author    : Jack
' Date      : 12-09-2012
' Purpose   : from SROss81
' http://www.access-programmers.co.uk/forums/showthread.php?p=1190336#post1190336
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs:
'         pQuery  -- name of the query
'         pField ---- name of the field
' Dependency: N/A
'--------------------------------------------------------------------------
'
Function MedianF(pQuery As String, pfield As String) As Single

Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single
   On Error GoTo MedianF_Error

strSQL = "SELECT " & pfield & " from " & pQuery & " WHERE " & pfield & ">0 Order by " & pfield & ";"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements
MedianF = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2
End If
rs.Close

   On Error GoTo 0
   Exit Function

MedianF_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure MedianF of Module AWF_Related"
End Function

Hope it's useful to you

 

[johncollier1]

Thanks jdraw
I'll give it a go

Best regards
John

 

[johncollier1]

O' hang on there
does the variable pfield refer to a single field from multiple records?

what I need to do is modify this so that it takes multiple fields in a single record

forgive me my programming skills arent good enough to work it out

regards
John