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

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!