Help troubleshoot simple code for Multi-arrays 2

[Oyster]

I have a problem Redim a multi-array - I get a subscript out of range error that I don't know how to fix.

In order to be clear about what my problem is, I've prepared a simple program. Data has been added to a 5-row, 2-column FlexGrid. I want to first calculate the average in the first 4 rows, then calculate the average for rows 2 to 5. As with my actual application, there are missing values.

I have no problem in calculating the average for the first 4 rows, but I get a subscript out of range error when trying to ReDim the arrays to calculate the average for rows 2 to 5. Is there anyone who could take a few moments to look at my VB code and see what my problem is?

'========================================

Private myArray As Variant

Private Sub Command1_Click()
Command1.Enabled = False
Command2.Enabled = True
ArrayAvg myArray

End Sub

Private Sub Command2_Click()
Command1.Enabled = False
Command2.Enabled = False
countArray = 1
ArrayAvg myArray
End Sub

Private Sub Form_Load()
Dim FGArray() As Integer

ReDim FGArray(1 To 5, 1 To 2)
For i = 1 To 5
For j = 1 To 2
FGArray(i, j) = i * j
MSFlexGrid1.TextMatrix(i - 1, j - 1) = FGArray(i, j)
Next j
Next i

'For i = 0 To 3
'MSFlexGrid1.TextMatrix(i, 0) = ""
'Next
MSFlexGrid1.TextMatrix(2, 0) = ""
MSFlexGrid1.TextMatrix(4, 0) = ""
MSFlexGrid1.TextMatrix(0, 1) = ""


End Sub
Function ArrayAvg(arr As Variant, Optional First As Variant, _
Optional Last As Variant, Optional IgnoreEmpty As Boolean = True) As Variant
Dim index As Long
Dim sumcol1 As Double
Dim countcol1 As Long
Dim sumcol2 As Double
Dim countcol2 As Long

MAvg = 4 'number of elements to use in average

'*******************************************************
'IF RUNNING THROUGH THE ARRAYS FOR THE FIRST TIME
'THEN BUILD 2-D ARRAY FOR ROWS 1 TO 4 IN FLEXGRID
'*******************************************************

If countArray = 0 Then
ReDim arr(0 To MAvg - 1, 1 To 2)


For j = 1 To 2
For i = 0 To MAvg - 1
arr(i, j) = MSFlexGrid1.TextMatrix(i, j - 1)
Next i
Next j

'*******************************************************
'IF NOT, THEN UPDATE EXISTING ARRAYS WITH NEW DATA
'BY DEFINING 2-D ARRAY FOR ROWS 2 TO 5 IN FLEXGRID
'*******************************************************

Else

' =========================================================
' Update First Dimension
' =========================================================

'Remove the LBound() data
For index = LBound(arr, 1) To UBound(arr, 1) - 1
arr(index, 1) = arr(index + 1, 1)
Next

'Remove UBound() data
ReDim Preserve arr(UBound(arr, 1) - 1)

'Add a data element to UBound(+1)

ReDim Preserve arr(UBound(arr, 1) + 1)

arr(UBound(arr)) = MSFlexGrid1.TextMatrix(4, 0)

' =========================================================
' Update Second Dimension
' =========================================================

'Remove the LBound() data
For index = LBound(arr, 2) To UBound(arr, 2) - 1
arr(index, 2) = arr(index + 1, 2)
Next

'Remove UBound() data
ReDim Preserve arr(UBound(arr, 2) - 1)

'Add a data element to UBound(+1)

ReDim Preserve arr(UBound(arr, 2) + 1)

arr(UBound(arr)) = MSFlexGrid1.TextMatrix(4, 1)

End If

'*******************************************************
'THE FINAL STEP IN THE FUNCTION IS TO CALCULATE AVERAGES
'*******************************************************

' =========================================================
' Average for first column
' =========================================================

If IsMissing(First) Then First = LBound(arr, 1)
If IsMissing(Last) Then Last = UBound(arr, 1)

' if arr isn't an array, the following statement raises an error
For index = First To Last
If IsNumeric(arr(index, 1)) Then
If IgnoreEmpty = False Or Not IsEmpty(arr(index, 1)) Then
countcol1 = countcol1 + 1
sumcol1 = sumcol1 + arr(index, 1)
End If
End If
Next

' if array empty(count = 0)then skip average; otherwise return the average
On Error Resume Next
ArrayAvgCol1 = sumcol1 / countcol1
If countArray = 0 Then
Text1.Text = ArrayAvgCol1
Else
Text3.Text = ArrayAvgCol1
End If

' =========================================================
' Mean for second column
' =========================================================

If IsMissing(First) Then First = LBound(arr, 2)
If IsMissing(Last) Then Last = UBound(arr, 2)

' if arr isn't an array, the following statement raises an error
For index = First To Last
If IsNumeric(arr(index, 2)) Then
If IgnoreEmpty = False Or Not IsEmpty(arr(index, 2)) Then
countcol2 = countcol2 + 1
sumcol2 = sumcol2 + arr(index, 2)
End If
End If
Next

' if array empty(count = 0)then skip average; otherwise return the average
On Error Resume Next
ArrayAvgCol2 = sumcol2 / countcol2

If countArray = 0 Then
Text2.Text = ArrayAvgCol2
Else
Text4.Text = ArrayAvgCol2
End If

End Function

 

[MichaelRed]

Well, I THINK your problem is with the dimensions of hte Flex Grrid. But some of what you are doing is somewhat foregin to my general practice that it was MUCH easier for me to collapse your ArrayAverage routine that mine doesn't quite look like what you wrote. This is all a bit much for posting, so if you want to see my version, send me an e-mail and I'll send the thinnnnggggyyyyy to you directly.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JJR]

Oyster,

When I understand correctly, the first time use create an array like this:

 ReDim arr(0 To MAvg - 1, 1 To 2)

The second time you try to redim your array using the preserve statement. If you do that you can only change the size of the last dimension in your array and you can't change the numer of dimensions.

From the MSDN library:

If you use the Preserve keyword, you can resize only the last array dimension, and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

Good luck!!

Jordi Reineman