Perhaps:

Public Function GET_LARGEST_NUMBER_SUM(ARR As Range, k As Variant) As
Variant
Dim v As Variant
Dim i As Integer
v = Application.Transpose(Application.Transpose(ARR))
For i = 1 To UBound(v)
If IsEmpty(v(i)) Then v(i) = 0
Next i


For i = 1 To k
GET_LARGEST_NUMBER_SUM = GET_LARGEST_NUMBER_SUM + _
Application.WorksheetFunction.Large(v, i)
Next i

End Function

--
Regards,
Tom Ogilvy

"Souris" wrote in message
...
My original problem was to get unknown max numbers from my range for
example 3.
Because worksheet functions do not support this.
I need to write a function to do this.
VBA does not let me pass array type to the function.
I have to pass varaint type to the function.

My formula shows "#VALUE!" on the spreadsheet, if the range has some
spaces
and the number passed less than 3 numbers.

I tried to get rid of the "#VALUE!" on the cells.

Here is my function code.

Public Function GET_LARGEST_NUMBER_SUM(ARR As Variant, k As Long) As Long

Dim i As Integer
ReDim ARR(1 To 10)

For i = 1 To UBound(ARR)
If IsEmpty(ARR(i)) Then ARR(i) = 0
Next i


For i = 1 To k
GET_LARGEST_NUMBER_SUM = GET_LARGEST_NUMBER_SUM + _
Application.WorksheetFunction.Large(ARR, i)
Next i

End Function

=IF(VLOOKUP("HOME WORK",NPARAMETER,4,0)=-1,(SUM(G12:P12)/VLOOKUP( "HOME
WORK",NPARAMETER,3,FALSE)),IF(VLOOKUP("HOME
WORK",NPARAMETER,4,0)=0,IF(COUNT(G12:P12) 0,
AVERAGE(G12:P12),0),GET_LARGEST_NUMBER_SUM(G12:P12 ,VLOOKUP("HOME
WORK",NPARAMETER,4,0))/VLOOKUP("HOME WORK",NPARAMETER,4,0)))

VLOOKUP("HOME WORK",NPARAMETER,4,0) = 3

Thanks millions,

"Tom Ogilvy" wrote:

Dim v as Variant
redim v(1 to 10)

or if you are picking up a range from the worksheet

Dim v as Variant
v = Range("A1:F10").Value
for i = lbound(v,1) to ubound(v,1)
for j = lbound(v,2) to ubound(v,2)
debug.print "v(" & i & "," & j & ")=" & v(i,j)
next j ' column
next i ' row

Array isn't a type like variant, long or double. It is a storage
structure.

Other than that, for me, you would need to ask a clearer question.
--
Regards,
Tom Ogilvy




"Souris" wrote in message
...
The type of parameter is Variant.
How can I redim the type to Array before scan it without knowing the
size?
Thanks again,

"Chip Pearson" wrote:

You can use a For Each loop on an array. Or, if you want to go by
index, use code like

Dim Ndx As Long
For Ndx = LBound(Arr) To UBound(Arr)
' do something with Arr(Ndx)
Next Ndx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Souris" wrote in message
...
I would like to scan an array which my function does not know
the size.
Are there any statement to return size of array like Sizeof or
Length then I
can scan the whole array.

any infoomation is great appreciated,