I've got an array that I load up using code. The array varies in size during resizing which takes place in an outer nested loop. My
problem is that I need to run through the array and find the largest integer that exists within it. This should be easy but I seem
to be having a brain fart. Lets say my array is called Prog(i), and I need to loop through it and find out which of the numbers is
the largest. For purposes of my question, lets just say that the array contains 5 elements, so I need to find out which of the 5
integers within the array is the largest.

Thanks in advance,
--
RMC,CPA

Hi R,

Try using the Excel Max function, e,g..

Sub Tester01()
Dim arr As Variant
arr = Array(100, 150, 75, 95, 22)
MsgBox Application.Max(arr)

End Sub


---
Regards,
Norman



"R. Choate" wrote in message
...
I've got an array that I load up using code. The array varies in size
during resizing which takes place in an outer nested loop. My
problem is that I need to run through the array and find the largest
integer that exists within it. This should be easy but I seem
to be having a brain fart. Lets say my array is called Prog(i), and I need
to loop through it and find out which of the numbers is
the largest. For purposes of my question, lets just say that the array
contains 5 elements, so I need to find out which of the 5
integers within the array is the largest.

Thanks in advance,
--
RMC,CPA

This array is of variable size and contains variable contents. There is no easy situation like "Array(100, 150, 75, 95, 22)" used in
your example. My situation would be more like "Array(i)", where if i = 3, the array(i) would equal whatever is in that position
within the array. So I need the Max among all of the elements, represented by the variable i, as noted in my original post. For
instance, it would be more like array(1 to 50), where array(1) might be 62, array(15) might be 170, etc., etc.. Of course, Array(1
to i) is not proper syntax.
--
RMC,CPA


"Norman Jones" wrote in message ...
Hi R,

Try using the Excel Max function, e,g..

Sub Tester01()
Dim arr As Variant
arr = Array(100, 150, 75, 95, 22)
MsgBox Application.Max(arr)

End Sub


---
Regards,
Norman



"R. Choate" wrote in message
...
I've got an array that I load up using code. The array varies in size
during resizing which takes place in an outer nested loop. My
problem is that I need to run through the array and find the largest
integer that exists within it. This should be easy but I seem
to be having a brain fart. Lets say my array is called Prog(i), and I need
to loop through it and find out which of the numbers is
the largest. For purposes of my question, lets just say that the array
contains 5 elements, so I need to find out which of the 5
integers within the array is the largest.

Thanks in advance,
--
RMC,CPA

R. Choate wrote:
This array is of variable size and contains variable contents. There is no easy situation like "Array(100, 150, 75, 95, 22)" used in
your example. My situation would be more like "Array(i)", where if i = 3, the array(i) would equal whatever is in that position
within the array. So I need the Max among all of the elements, represented by the variable i, as noted in my original post. For
instance, it would be more like array(1 to 50), where array(1) might be 62, array(15) might be 170, etc., etc.. Of course, Array(1
to i) is not proper syntax.
If you want to return the *value* of the largest number in a 1-D array
named myArray, Norman Jones gave you the code:

Application.Max(myArray)

If you want to return the *array index number* of the largest number in
the array, then, you can use

Application.Match(Application.Max(myArray), myArray, 0) - 1 + LBound(arr)

Alan Beban

No, Norman's code does not work and the index number code indicated incorrect syntax in the editor. If I have an array where Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2" associated with Prog(3). I need for the code to return the "15"
after looping through all of them and determining that 15 was the largest element in the array. Here is the solution I finally
worked out on my own (Using option base 1):

High = 0
For A = 2 To BoxNums

If Prog(A) - 0 High Then
High = Prog(A)
Else
GoTo goaroundagain
End If

goaroundagain:

Next
If High = 0 Then
Z = Prog(1) + High
ElseIf High < Prog(1) Then
Z = Prog(1)
Else
Z = High
End If
--
RMC,CPA


"Alan Beban" wrote in message ...
R. Choate wrote:
This array is of variable size and contains variable contents. There is no easy situation like "Array(100, 150, 75, 95, 22)" used
in
your example. My situation would be more like "Array(i)", where if i = 3, the array(i) would equal whatever is in that position
within the array. So I need the Max among all of the elements, represented by the variable i, as noted in my original post. For
instance, it would be more like array(1 to 50), where array(1) might be 62, array(15) might be 170, etc., etc.. Of course, Array(1
to i) is not proper syntax.
If you want to return the *value* of the largest number in a 1-D array
named myArray, Norman Jones gave you the code:

Application.Max(myArray)

If you want to return the *array index number* of the largest number in
the array, then, you can use

Application.Match(Application.Max(myArray), myArray, 0) - 1 + LBound(arr)

Alan Beban

Hi R,

No, Norman's code does not work and the index number code indicated
incorrect syntax in the editor. If I have an array where Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2"
associated with Prog(3). I need for the code to return the "15"


Using your precise example, the following works for me, returning 15:

Sub Tester02()
Dim Prog(1 To 3) As Variant
Prog(1) = 15
Prog(2) = 0
Prog(3) = 2
MsgBox Application.Max(Prog)
End Sub

In my original response I used:

arr = Array(100, 150, 75, 95, 22)

simply as a convenient way to populate a 5 element 1-D array (as per your
example) and the sub was intended solely to demonstrate the use of the Excel
Max function to return the largest array element. This usage is independent
of the method by which the array is loaded. So however you load the array,
be that by assignment or by looping, a similar method should work.

BTW, in versions prior to xl2002, I believe that this will fail on arrays
with more than 5461 elements.

---
Regards,
Norman



"R. Choate" wrote in message
...
No, Norman's code does not work and the index number code indicated
incorrect syntax in the editor. If I have an array where Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2"
associated with Prog(3). I need for the code to return the "15"
after looping through all of them and determining that 15 was the largest
element in the array. Here is the solution I finally
worked out on my own (Using option base 1):

High = 0
For A = 2 To BoxNums

If Prog(A) - 0
High Then
High =
Prog(A)
Else
GoTo
goaroundagain
End If

goaroundagain:

Next
If High = 0 Then
Z = Prog(1) +
High
ElseIf High <
Prog(1) Then
Z = Prog(1)
Else
Z = High
End If
--
RMC,CPA


"Alan Beban" wrote in message
...
R. Choate wrote:
This array is of variable size and contains variable contents. There is
no easy situation like "Array(100, 150, 75, 95, 22)" used
in
your example. My situation would be more like "Array(i)", where if i = 3,
the array(i) would equal whatever is in that position
within the array. So I need the Max among all of the elements,
represented by the variable i, as noted in my original post. For
instance, it would be more like array(1 to 50), where array(1) might be
62, array(15) might be 170, etc., etc.. Of course, Array(1
to i) is not proper syntax.
If you want to return the *value* of the largest number in a 1-D array
named myArray, Norman Jones gave you the code:

Application.Max(myArray)

If you want to return the *array index number* of the largest number in
the array, then, you can use

Application.Match(Application.Max(myArray), myArray, 0) - 1 + LBound(arr)

Alan Beban

Norman's suggestion worked ok for me.

But Alan had a typo in his code: That last Arr should have been MyArray.

Option Explicit
Sub testme()

Dim Prog(1 To 3) As Long
Prog(1) = 15
Prog(2) = 0
Prog(3) = 2

Debug.Print "Max Value-- " & Application.Max(Prog)
Debug.Print "Index of Max Value-- " _
& Application.Match(Application.Max(Prog), Prog, 0) - 1 + LBound(Prog)

End Sub

Returned this in the immediate window:
Max Value-- 15
Index of Max Value-- 1



"R. Choate" wrote:

No, Norman's code does not work and the index number code indicated incorrect syntax in the editor. If I have an array where Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2" associated with Prog(3). I need for the code to return the "15"
after looping through all of them and determining that 15 was the largest element in the array. Here is the solution I finally
worked out on my own (Using option base 1):

High = 0
For A = 2 To BoxNums

If Prog(A) - 0 High Then
High = Prog(A)
Else
GoTo goaroundagain
End If

goaroundagain:

Next
If High = 0 Then
Z = Prog(1) + High
ElseIf High < Prog(1) Then
Z = Prog(1)
Else
Z = High
End If
--
RMC,CPA

"Alan Beban" wrote in message ...
R. Choate wrote:
This array is of variable size and contains variable contents. There is no easy situation like "Array(100, 150, 75, 95, 22)" used
in
your example. My situation would be more like "Array(i)", where if i = 3, the array(i) would equal whatever is in that position
within the array. So I need the Max among all of the elements, represented by the variable i, as noted in my original post. For
instance, it would be more like array(1 to 50), where array(1) might be 62, array(15) might be 170, etc., etc.. Of course, Array(1
to i) is not proper syntax.
If you want to return the *value* of the largest number in a 1-D array
named myArray, Norman Jones gave you the code:

Application.Max(myArray)

If you want to return the *array index number* of the largest number in
the array, then, you can use

Application.Match(Application.Max(myArray), myArray, 0) - 1 + LBound(arr)

Alan Beban

--

Dave Peterson

I appreciate the attempts, but it did not work in my situation. Perhaps it had something to do with the dynamic resizing of the
arrays or something else that was in the more complicated actual code than what I gave in my example because I wanted to simplify
the specific problem and not sound confusing. In any case, I solved the issue so I won't bother you guys any further on my post. It
is academic at this point.

Thanks again,
--
RMC,CPA


"Dave Peterson" wrote in message ...
Norman's suggestion worked ok for me.

But Alan had a typo in his code: That last Arr should have been MyArray.

Option Explicit
Sub testme()

Dim Prog(1 To 3) As Long
Prog(1) = 15
Prog(2) = 0
Prog(3) = 2

Debug.Print "Max Value-- " & Application.Max(Prog)
Debug.Print "Index of Max Value-- " _
& Application.Match(Application.Max(Prog), Prog, 0) - 1 + LBound(Prog)

End Sub

Returned this in the immediate window:
Max Value-- 15
Index of Max Value-- 1



"R. Choate" wrote:

No, Norman's code does not work and the index number code indicated incorrect syntax in the editor. If I have an array where
Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2" associated with Prog(3). I need for the code to return the "15"
after looping through all of them and determining that 15 was the largest element in the array. Here is the solution I finally
worked out on my own (Using option base 1):

High = 0
For A = 2 To BoxNums

If Prog(A) - 0 High Then
High = Prog(A)
Else
GoTo goaroundagain
End If

goaroundagain:

Next
If High = 0 Then
Z = Prog(1) + High
ElseIf High < Prog(1) Then
Z = Prog(1)
Else
Z = High
End If
--
RMC,CPA

"Alan Beban" wrote in message ...
R. Choate wrote:
This array is of variable size and contains variable contents. There is no easy situation like "Array(100, 150, 75, 95, 22)"
used
in
your example. My situation would be more like "Array(i)", where if i = 3, the array(i) would equal whatever is in that position
within the array. So I need the Max among all of the elements, represented by the variable i, as noted in my original post. For
instance, it would be more like array(1 to 50), where array(1) might be 62, array(15) might be 170, etc., etc.. Of course,
Array(1
to i) is not proper syntax.
If you want to return the *value* of the largest number in a 1-D array
named myArray, Norman Jones gave you the code:

Application.Max(myArray)

If you want to return the *array index number* of the largest number in
the array, then, you can use

Application.Match(Application.Max(myArray), myArray, 0) - 1 + LBound(arr)

Alan Beban

--

Dave Peterson

Norman Jones wrote:
Hi R,

Using your precise example, the following works for me, returning 15:

Sub Tester02()
Dim Prog(1 To 3) As Variant
Prog(1) = 15
Prog(2) = 0
Prog(3) = 2
MsgBox Application.Max(Prog)
End Sub

In my original response I used:

arr = Array(100, 150, 75, 95, 22)

simply as a convenient way to populate a 5 element 1-D array (as per your
example) and the sub was intended solely to demonstrate the use of the Excel
Max function to return the largest array element. This usage is independent
of the method by which the array is loaded. So however you load the array,
be that by assignment or by looping, a similar method should work.

BTW, in versions prior to xl2002, I believe that this will fail on arrays
with more than 5461 elements.

There are workarounds for this limitation with the functions from the
freely downloadable file at http://home.pacbell.net/beban

Alan Beban

From an academic standpoint, there is nothing your code does that couldn't
be done in one line with the Max function by replaceing *All* your code with

Z = Application.Max(prog)

Unless your array exceeds 5461 elements and you are using xl2000 or earlier.


Using your approach you have made it unnecessarily complex. If the max will
always be greater than zero then

Z = 0
for i = lbound(prog) to ubound(prog)
if prog(i) Z then
Z = prog(i)
end if
Next

Would be the same.

--
Regards,
Tom Ogilvy


"R. Choate" wrote in message
...
I appreciate the attempts, but it did not work in my situation. Perhaps it
had something to do with the dynamic resizing of the
arrays or something else that was in the more complicated actual code than
what I gave in my example because I wanted to simplify
the specific problem and not sound confusing. In any case, I solved the
issue so I won't bother you guys any further on my post. It
is academic at this point.

Thanks again,
--
RMC,CPA

"R. Choate" wrote:

No, Norman's code does not work and the index number code indicated
incorrect syntax in the editor. If I have an array where
Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2"
associated with Prog(3). I need for the code to return the "15"
after looping through all of them and determining that 15 was the
largest element in the array. Here is the solution I finally
worked out on my own (Using option base 1):

High = 0
For A = 2 To BoxNums

If Prog(A) - 0
High Then
High =
Prog(A)
Else
GoTo
goaroundagain
End If

goaroundagain:

Next
If High = 0 Then
Z = Prog(1)
+ High
ElseIf High <
Prog(1) Then
Z = Prog(1)
Else
Z = High
End If
--
RMC,CPA

Perhaps something in the outer nesting of my code has prevented the simple solution from working. Believe me, I wanted it to work
and I tried it and I got the wrong answer. I know of you and I have great respect for your Excel knowledge. All I can say is that
there is something about my situation that is preventing the one line of code from working. Now that 2nd bit of code you gave me
below does work and is an improvement over what I wrote. However, remember that when I wrote it, I had been trying everything to get
the right answer. I went to the NG as a last resort, mostly because I do have experience with this and I should be able to write the
appropriate code for something so basic without going to the NG for an answer. Sometimes that approach leads to overly cumbersome
code as you have pointed out.

I do not have a large number of elements, either. Nowhere close to 5461. Again, I like that 2nd 6 lines of code you gave me and it
works, but the one line of code

Z = Application.Max(prog)

does not give me the right answer.

Hopefully I can save your patience and willingness to help me for another question later.

Thanks, Tom

Richard
--
RMC,CPA


"Tom Ogilvy" wrote in message ...
From an academic standpoint, there is nothing your code does that couldn't
be done in one line with the Max function by replaceing *All* your code with

Z = Application.Max(prog)

Unless your array exceeds 5461 elements and you are using xl2000 or earlier.


Using your approach you have made it unnecessarily complex. If the max will
always be greater than zero then

Z = 0
for i = lbound(prog) to ubound(prog)
if prog(i) Z then
Z = prog(i)
end if
Next

Would be the same.

--
Regards,
Tom Ogilvy


"R. Choate" wrote in message
...
I appreciate the attempts, but it did not work in my situation. Perhaps it
had something to do with the dynamic resizing of the
arrays or something else that was in the more complicated actual code than
what I gave in my example because I wanted to simplify
the specific problem and not sound confusing. In any case, I solved the
issue so I won't bother you guys any further on my post. It
is academic at this point.

Thanks again,
--
RMC,CPA

"R. Choate" wrote:

No, Norman's code does not work and the index number code indicated
incorrect syntax in the editor. If I have an array where
Prog(1)
= 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2"
associated with Prog(3). I need for the code to return the "15"
after looping through all of them and determining that 15 was the
largest element in the array. Here is the solution I finally
worked out on my own (Using option base 1):

High = 0
For A = 2 To BoxNums

If Prog(A) - 0
High Then
High =
Prog(A)
Else
GoTo
goaroundagain
End If

goaroundagain:

Next
If High = 0 Then
Z = Prog(1)
+ High
ElseIf High <
Prog(1) Then
Z = Prog(1)
Else
Z = High
End If
--
RMC,CPA

I'm sure that the function is working fine. I've obviously got something in my outer nesting layers which led to the failure of the
function in my application. I have a pretty good sized procedure and I know that if I post it, then I would need to explain some
things and then I would be subject to all kinds of suggestions and/or criticisms regarding my code. Since I'm still working
feverishly on this project and need to get it done, I don't think I'll post the code this time. I do appreciate everybody for taking
an interest in this little snippet of my project. I hope I will be so lucky again when I have a better challenge. As a matter of
fact, I think I've got a good one now, which I'll post seperately in just a few minutes.

Thanks again!
Richard
--
RMC,CPA


"Postman" wrote in message ...
Could you stick an example sheet somewhere so we can see it not working?

If Z = Application.Max(prog) isn't working right it's a worry.

P


"R. Choate" wrote in message
...
| Perhaps something in the outer nesting of my code has prevented the simple
solution from working. Believe me, I wanted it to work
| and I tried it and I got the wrong answer. I know of you and I have great
respect for your Excel knowledge. All I can say is that
| there is something about my situation that is preventing the one line of
code from working. Now that 2nd bit of code you gave me
| below does work and is an improvement over what I wrote. However, remember
that when I wrote it, I had been trying everything to get
| the right answer. I went to the NG as a last resort, mostly because I do
have experience with this and I should be able to write the
| appropriate code for something so basic without going to the NG for an
answer. Sometimes that approach leads to overly cumbersome
| code as you have pointed out.
|
| I do not have a large number of elements, either. Nowhere close to 5461.
Again, I like that 2nd 6 lines of code you gave me and it
| works, but the one line of code
|
| Z = Application.Max(prog)
|
| does not give me the right answer.
|
| Hopefully I can save your patience and willingness to help me for another
question later.
|
| Thanks, Tom
|
| Richard
| --
| RMC,CPA
|
|
| "Tom Ogilvy" wrote in message
...
| From an academic standpoint, there is nothing your code does that couldn't
| be done in one line with the Max function by replaceing *All* your code
with
|
| Z = Application.Max(prog)
|
| Unless your array exceeds 5461 elements and you are using xl2000 or
earlier.
|
|
| Using your approach you have made it unnecessarily complex. If the max
will
| always be greater than zero then
|
| Z = 0
| for i = lbound(prog) to ubound(prog)
| if prog(i) Z then
| Z = prog(i)
| end if
| Next
|
| Would be the same.
|
| --
| Regards,
| Tom Ogilvy
|
|
| "R. Choate" wrote in message
| ...
| I appreciate the attempts, but it did not work in my situation. Perhaps
it
| had something to do with the dynamic resizing of the
| arrays or something else that was in the more complicated actual code
than
| what I gave in my example because I wanted to simplify
| the specific problem and not sound confusing. In any case, I solved the
| issue so I won't bother you guys any further on my post. It
| is academic at this point.
|
| Thanks again,
| --
| RMC,CPA
|
| "R. Choate" wrote:
|
| No, Norman's code does not work and the index number code indicated
| incorrect syntax in the editor. If I have an array where
| Prog(1)
| = 15, Prog(2) = 0, and Prog(3) = 2, Norman's code returns the "2"
| associated with Prog(3). I need for the code to return the "15"
| after looping through all of them and determining that 15 was the
| largest element in the array. Here is the solution I finally
| worked out on my own (Using option base 1):
|
| High = 0
| For A = 2 To BoxNums
|
| If Prog(A) - 0

| High Then
| High =
| Prog(A)
| Else
| GoTo
| goaroundagain
| End If
|
| goaroundagain:
|
| Next
| If High = 0
Then
| Z =
Prog(1)
| + High
| ElseIf High <
| Prog(1) Then
| Z =
Prog(1)
| Else
| Z = High
| End If
| --
| RMC,CPA
|
|
|
|

Is there any good way to deal with a very big array (like 1000 rows and
150 columns).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dim varr as Variant
varr = Range("A1").Resize(1000,150).Value

for i = lbound(varr,1) to ubound(varr,1)
for j = lbound(varr,2) to ubound(varr,2)
' process array element varr( i , j )
next j
Next i

Range("A1002").Resize(1000,150).Value = varr

--
Regards,
Tom Ogilvy




"Cool Sport" wrote in message
...
Is there any good way to deal with a very big array (like 1000 rows and
150 columns).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I don't know what you mean by "a good way", but if the functions in the
freely downloadable file at http://home.pacbell.net/beban are available
to your workbook, and given that myArray is a 1000x150 array:

maxVal = ArrayMax1D(MakeArray(myArray, 1))

Alan Beban

Cool Sport wrote:
Is there any good way to deal with a very big array (like 1000 rows and
150 columns).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I don't know if I should feel bad for posting a problem that created a monster, or feel good for bringing up an issue that generated
lots of interest. I only wish I could get so much response to my "Intersection" post. Nobody wants to touch that one at all.

--
RMC,CPA


"Cool Sport" wrote in message ...
Is there any good way to deal with a very big array (like 1000 rows and
150 columns).



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry for interrupting the thread.

By the way, I have some suggestions. As I know that Excel Max function
is not the fastest way to utilize in vba code.

Let say you have an array named Prog(size)

The following sub will do the job you want. I assume that you have an
array of integers.

Option Base 1

Sub getMax(tArray as Variant) As Integer
Dim size As Integer, i As Integer
Dim maxVal As Integer

maxVal = 0 ' defalt return value
size = UBound(tArray)
If size0 then
maxVal = tArray(1)
If size2 then
For i = 2 to size
If tArray(i)maxVal then maxVal = tArray(i)
Next i
End If
End If

getMax = maxVal
End Sub

There is another way is that you can sort the array acsendingly then get
the last element which is the largest one. I have a sub to do this task
that can be posted if you like.

Hope you find this useful.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I'm a little bit rusty on my declaration rules, but I can see that you had a typo mixing the declaration of the array with the sub
name. However, when I tried the declaration as I thought you meant it, I got the red syntax error text. Perhaps you would take
another look at the code you posted. I'm hoping to keep it for later reference. I know I will need to do this again sometime.

Thanks,

Richard
--
RMC,CPA


"Cool Sport" wrote in message ...
Sorry for interrupting the thread.

By the way, I have some suggestions. As I know that Excel Max function
is not the fastest way to utilize in vba code.

Let say you have an array named Prog(size)

The following sub will do the job you want. I assume that you have an
array of integers.

Option Base 1

Sub getMax(tArray as Variant) As Integer
Dim size As Integer, i As Integer
Dim maxVal As Integer

maxVal = 0 ' defalt return value
size = UBound(tArray)
If size0 then
maxVal = tArray(1)
If size2 then
For i = 2 to size
If tArray(i)maxVal then maxVal = tArray(i)
Next i
End If
End If

getMax = maxVal
End Sub

There is another way is that you can sort the array acsendingly then get
the last element which is the largest one. I have a sub to do this task
that can be posted if you like.

Hope you find this useful.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

lol, sorry for my silly mistake. I wrote the code straight away from my
mind without checking it. It is supposed to be a FUNCTION that returns
value instead of procedure.

Please replace Sub with Function at the first and last statement

Function ....
.....
End Function


If your array declaration is like this:
Dim prog(5) As Integer

Then the statement below should work:
Msgbox getMax(prog)


It should work (I have tested it).

Sorry again,


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

That was much better. Thanks for the tip and the code.

Are you saying though, that using a UDF like the one you gave me will run faster than calling a worksheet function in VBA like the
max function? I was always under the understanding that UDFs are the 2nd slowest thing you can use, right behind array formulas on a
spreadsheet (Boy, those are slow. I love the way they work but I hate the performance and the sensitivity of them; always having to
use the ctrl+shift+enter when you write them or edit them. The only thing you can do normally is copy them.).

--
RMC,CPA


"Cool Sport" wrote in message ...
lol, sorry for my silly mistake. I wrote the code straight away from my
mind without checking it. It is supposed to be a FUNCTION that returns
value instead of procedure.

Please replace Sub with Function at the first and last statement

Function ....
.....
End Function


If your array declaration is like this:
Dim prog(5) As Integer

Then the statement below should work:
Msgbox getMax(prog)


It should work (I have tested it).

Sorry again,


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It is good when you are happy with the code. I have read it somewhere
says that you would be better not to use Application.Max function. Ok, I
have found it when replying this. Please read the tip no. 3 in this
article (http://www.avdf.com/apr98/art_ot003.html).

you :) -- me :))



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!