Max of VBA Array

[ExcelMonkey]

I have a two dimensional array. I have tried to sum the second column of
it. When I test I get a sum of 0 but when I test the individual values
I clearly get numbers that are greater than 0 and should then Max to
the highest of those numbers. What I am I doing wrong?

Public UnitOfferArray() As Variant
Dim ArrayMax As Variant

ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 9))

?ArrayMax
0

?UnitOfferArray(1, 9)
49.2126
?UnitOfferArray(2, 9)
39.7223
?UnitOfferArray(3, 9)
28.0002
?UnitOfferArray(4, 9)
27.943
?UnitOfferArray(5, 9)
11.2936
?UnitOfferArray(6, 9)
7.6502
?UnitOfferArray(7, 9)
4.6001
?UnitOfferArray(8, 9)
1.7726
?UnitOfferArray(9, 9)
1.2268
?UnitOfferArray(10, 9)
1.1163
?UnitOfferArray(11, 9)
0
?UnitOfferArray(12, 9)
0
?UnitOfferArray(13, 9)
0

 

[acw]

Hi

Try
ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 10))

The data is in the 10th column of tjhe array UnitOfferArray. Remember that the array starts from 0.

Tony

----- ExcelMonkey > wrote: -----

I have a two dimensional array. I have tried to sum the second column of
it. When I test I get a sum of 0 but when I test the individual values
I clearly get numbers that are greater than 0 and should then Max to
the highest of those numbers. What I am I doing wrong?

Public UnitOfferArray() As Variant
Dim ArrayMax As Variant

ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 9))

?ArrayMax
0

?UnitOfferArray(1, 9)
49.2126
?UnitOfferArray(2, 9)
39.7223
?UnitOfferArray(3, 9)
28.0002
?UnitOfferArray(4, 9)
27.943
?UnitOfferArray(5, 9)
11.2936
?UnitOfferArray(6, 9)
7.6502
?UnitOfferArray(7, 9)
4.6001
?UnitOfferArray(8, 9)
1.7726
?UnitOfferArray(9, 9)
1.2268
?UnitOfferArray(10, 9)
1.1163
?UnitOfferArray(11, 9)
0
?UnitOfferArray(12, 9)
0
?UnitOfferArray(13, 9)
0

 

[ExcelMonkey]

But I used Option Base 1 before my variable declarations.

 

[acw]

Ok

When I use option base 1, then it works for me.

I tested with the following

Sub eee()

Dim arr2(4, 9)
arr = Array(39.7223, 49.2126, 28.002, 27.943)
For i = 1 To 4
arr2(i, 9) = arr(i)
Next i

MsgBox WorksheetFunction.Max(WorksheetFunction.Index(arr2, 0, 9))

End Sub

----- ExcelMonkey > wrote: -----

But I used Option Base 1 before my variable declarations.

 

[Tom Ogilvy]

Sub Tester1()
Dim UnitOfferArray(1 To 13, 1 To 9) As Double
Dim var As Double
UnitOfferArray(1, 9) = 49.2126
UnitOfferArray(2, 9) = 39.7223
UnitOfferArray(3, 9) = 28.0002
UnitOfferArray(4, 9) = 27.943
UnitOfferArray(5, 9) = 11.2936
UnitOfferArray(6, 9) = 7.6502
UnitOfferArray(7, 9) = 4.6001
UnitOfferArray(8, 9) = 1.7726
UnitOfferArray(9, 9) = 1.2268
UnitOfferArray(10, 9) = 1.1163
UnitOfferArray(11, 9) = 0
UnitOfferArray(12, 9) = 0
UnitOfferArray(13, 9) = 0
var = Application.Sum(Application.Index(UnitOfferArray, 0, 9))
Debug.Print var

End Sub

produced:
172.5377

if you declare the lower bound, then there is no doubt what it is.

 

[ExcelMonkey]

So to expand on this further, lets now way that my array has 5
dimensions. I fill the array with values within 5 imbedded For Next
Loops. Assume I use the rnd() function to fill them for simplicity.
Once I have filled all the rows and columns in the first two dimensions
(i.e. rows and columns) I want to sum all the data in column 9. The
following is not working. It is giving me a Type Mismatch Error for
ArrayMax.

Private Sub Other()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim Array1() As Variant
Dim ArrayMax As Variant

ReDim Array1(1 To 9, 1 To 13, 1 To 5, 1 To 5, 1 To 5)
For A = 1 To 5
For B = 1 To 5
For C = 1 To 5
For D = 1 To 13
For E = 1 To 9
Array1(E, D, C, B, A) = Rnd()
Next E
Next D
ArrayMax = Application.Max(Application.Index(Array1, 0, 9, 0, 0, 0))
Next C
Next B
Next A

End Sub