ReDim Multidimensional array in VBA

Question

I have comments set up to run in and out of IBM Cognos formulas as needed.

To do so, I am putting the ranges into an array (LCogRng).

I am getting "Subscript out of range." once I get to ReDim Preserve LCogRng(1 To N, 1 To 2) As Range.

It works without Preserve, but that defeats the point.

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range, N As Integer
Dim CogArr() As String
Dim LCogRng() As Range

Sub AddTM1()
Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
ReDim CogArr(1 To 1) As String
ReDim LCogRng(1 To 1, 1 To 2) As Range
    ws.Activate

    For Each rng In ws.UsedRange
            N = Mid(rng.Comment.Text, 3, InStr(rng.Comment.Text, ":") - 3)
            cFormula = Mid(rng.Comment.Text, 5 + N, Len(rng.Comment.Text))
            If CogArr(1) = "" Then
                CogArr(1) = cFormula
                Set LCogRng(1, 1) = rng
            ElseIf UBound(CogArr) < N Then
                ReDim Preserve CogArr(1 To N) As String
                ReDim Preserve LCogRng(1 To N, 1 To 2) As Range 'Error row
                CogArr(N) = cFormula
                Set LCogRng(N, 1) = rng
            End If
        ElseIf InStr(rng.Comment.Text, "TM") > 0 And Len(rng.Comment.Text) <= 6 Then
            N = Mid(rng.Comment.Text, 5, 2)
            Set LCogRng(N, 2) = rng
        End If
        End If
    Next rng

Any help is much appreciated.

From a previous question: "I know that with ReDim Preserve you can only resize the last array dimension and you can't change the number of dimensions at all." stackoverflow.com/questions/25095182/… — user1274820
– user1274820, Commented Mar 26, 2015 at 19:59
I thought the resize limit refered to the LBound. I'm only changing the UBound. Haven't tried collection, haven't looked into it. — user2719198
– user2719198, Commented Mar 26, 2015 at 20:04
It is only the last dimension that can be resized. Annoying solution, but it works! — user2719198
– user2719198, Commented Mar 26, 2015 at 20:12

brettdj · Accepted Answer · 2015-03-28 09:43:15Z

0

For a 2D array as you have you can use TRANSPOSE to reorder the first dimesion, i.e.:

Sub UpdateArray()
Dim X
'çreate 4*2 array
X = [{"Apple","2";"Bananna","3";"Don","Bradman";"#Fail","PUA"}]
MsgBox UBound(X, 1) & " " & UBound(X, 2)
ReDim Preserve X(1 To UBound(X, 1), 1 To UBound(X, 2) + 1)
X = Application.Transpose(X)
ReDim Preserve X(1 To UBound(X, 1), 1 To UBound(X, 2) + 1)
X = Application.Transpose(X)
'you now have a 5*3 array
MsgBox UBound(X, 1) & " " & UBound(X, 2)
End Sub

answered Mar 28, 2015 at 9:43

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ReDim Multidimensional array in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related