VBA array - find largest element

[R. Choate]

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,

 

[Norman Jones]

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

 

[R. Choate]

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


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

 

[Alan Beban]

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

 

[R. Choate]

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

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 Jones]

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.

 

[Dave Peterson]

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]

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


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

 

[Alan Beban]

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

 

[Tom Ogilvy]

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

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,

incorrect syntax in the editor. If I have an array where

 

[R. Choate]

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


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

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,

incorrect syntax in the editor. If I have an array where

 

[Postman]

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


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

 

[R. Choate]

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


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


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

 

[Cool Sport]

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

 

[Tom Ogilvy]

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

 

[Alan Beban]

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

 

[R. Choate]

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


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

 

[Cool Sport]

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 size>0 then
maxVal = tArray(1)
If size>2 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.

 

[R. Choate]

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


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 size>0 then
maxVal = tArray(1)
If size>2 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.

 

[Cool Sport]

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,