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,

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,

Try using UBOUND and LBOUND to give you the upper and lower bounds of the
array.

Regards

Trevor


"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,

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,

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,

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,

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,

Thanks millions,

It works,

Souris,


"Tom Ogilvy" wrote in message
...
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,