2D array automatically reverts to 1D array when #Rows = 1

[RawlinsCross]

I have a sub-routine that compares sheet A (mvNew) to sheet B, any row discrepancy increments a count and stores the row# in an array called vRows. That part is fine so I won't include that code. The trouble comes when the count is 1. Here's a code snippit where I'm having the problem. See code comments for line explanations.

If iCount > 0 Then
    ReDim Preserve vRows(1 To iCount) 'This is an array of row #s that are recorded during the comparison algorithm
    ReDim vTraining_(1 To UBound(vRows), 1 To 13) 'After this line executes my dimensions of vTraining_ are (1 to 1, 1 to 13) as UBound(vRows) = 1
    vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns) 'After this line executes my dimensions of vTraining_ are (1 to 13)
    'How can I get this vTraining_ array back to (1 to 1, 1 to 13)???
    'In order for the "FormDataArray" method to work, the vTraining_ array has to be a 2D array
    Call FormDataArray
    Call InsertNewRows
End If

So the Application.Index automatically converts vTraining_(1 to 1, 1 to 13) to vTraining_(1 to 13) and this mucks up further downstream methods. Any thoughts on what the best strategy is to get my 2D array back?

 

[6StringJazzer]

It would help if you showed all your code. Sometimes the root of a problem is not where you think it is.

How is vTraining_ declared?

If you assign a Range to it, it will be redimensioned, which is why it is not staying (1 to 1, 1 to 13)

INDEX generally returns a Range of a single cell. You seem to be expecting it to return something else. Also it is expecting the following calling sequence:

INDEX(Range, Row Number, Column Number)

but you are giving it:

mvNew--no idea what this is--how is it defined?
Application.Transpose(vRows)--an array, not a row number
vColumns--no idea what this is--how is it defined? If it is like vRows then it is an array, not a column number.

 

[RawlinsCross]

Hi 6StringJazzer,

I'll show the entire code for this method but the library is very large and I figured it would not help to post it all in this case. I load two csv files, dump their used ranges into arrays mvNew and mvPrev, then I run a comparison on those arrays by dumping mvPrev into a dictionary and comparing mvNew against that.

Dim mvNew as Variant
mvNew = mrRange.value
is how this is defined.
(same goes for the mvPrev)

vRows is a 1D array of row numbers

Private Sub PerformBinaryComparison()

Dim oDict As Object
Dim i As Long, iCount As Long
Dim sKey As String
Dim vColumns As Variant

ReDim vColumns(1 To 13)
For i = 1 To 13
    vColumns(i) = i
Next i

Set oDict = CreateObject("Scripting.Dictionary")

'Create a dictionary with the PREV array
For i = LBound(mvPrev, 1) To UBound(mvPrev, 1)
    sKey = mvPrev(i, 4) & "-" & mvPrev(i, 10)
    If Not oDict.Exists(sKey) Then
        oDict.Add sKey, vbNullString
    End If
Next i

ReDim vRows(1 To 5000)

'Perform comparison
For i = LBound(mvNew, 1) To UBound(mvNew, 1)
    sKey = mvNew(i, 4) & "-" & mvNew(i, 10)
    If Not oDict.Exists(sKey) Then
        iCount = iCount + 1
        If iCount >= UBound(vRows) Then
            ReDim Preserve vRows(1 To UBound(vRows) + 1000)
        End If
        vRows(iCount) = i
    End If
Next i

If iCount > 0 Then
    ReDim Preserve vRows(1 To iCount) 'This is an array of row #s that are recorded during the comparison algorithm
    ReDim vTraining_(1 To UBound(vRows), 1 To 13) 'After this line executes my dimensions of vTraining_ are (1 to 1, 1 to 13)
    vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns) 'After this line executes my dimensions of vTraining_ are (1 to 13)
    'How can I get this vTraining_ array back to (1 to 1, 1 to 13)???
    'In order for the "FormDataArray" method to work, the vTraining_ array has to be a 2D array
    Call FormDataArray
    Call InsertNewRows
End If
    
End Sub

 

[BSALV]

just a guess (didn't check it), so with vTraining with 1 row = when iCount=1
Can't you preform a transpose as next line after the "application.index" then vTraining

 vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns) 'After this line executes my dimensions of vTraining_ are (1 to 13)
if iCount=1 then vtraining=application.transpose(vTraining)

 

[RawlinsCross]

just a guess (didn't check it), so with vTraining with 1 row = when iCount=1
Can't you preform a transpose as next line after the "application.index" then vTraining

 vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns) 'After this line executes my dimensions of vTraining_ are (1 to 13)
if iCount=1 then vtraining=application.transpose(vTraining)

Hi BSALV,
That gives me a vTraining_ array dimension of (1 to 13, 1 to 1) instead of (1 to 1, 1 to 13). If I do another transpose I get back to vTraining_(1 to 13)

 

[Alex Blakenburg]

I had a bit of a play and did some research and I can't see anything that would be faster than simply using a for loop to transpose the data.
I am sure you were trying to avoid having to do that but unless someone else comes up with something else .......

eg. replace

vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns)

with

    If iCount > 1 Then
        vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns)
    Else
        Dim tmpArr As Variant
        Dim j As Long
        tmpArr = Application.Index(mvNew, Application.Transpose(vRows), vColumns)
        For j = 1 To 13
            vTraining_(1, j) = tmpArr(j)
        Next j
    End If

 

[RawlinsCross]

Hey Alex, couldn't agree with you more. I kind of thought that the loop option was there but consider it a bit 'brute-ish' maybe? Wondered if there was a more elegant solution. So I basically did what you have suggested. All-in-all a good exercise to get this discussion out as I see this a bit with 2D arrays of 1 row.

Guys, really appreciate your input. I ended up making a function "Convert1Dto2D" in case I need it in other situations

Main Code

If iCount > 0 Then
    ReDim Preserve vRows(1 To iCount) 'This is an array of row #s that are recorded during the comparison algorithm
    ReDim vTraining_(1 To UBound(vRows), 1 To 13) 'After this line executes my dimensions of vTraining_ are (1 to 1, 1 to 13)
    vTraining_ = Application.Index(mvNew, Application.Transpose(vRows), vColumns) 'After this line executes my dimensions of vTraining_ are (1 to 13)
    If iCount = 1 Then
        vTraining_ = Convert1Dto2D(vTraining_)
    End If
    'In order for the "FormDataArray" method to work, the vTraining_ array has to be a 2D array
    Call FormDataArray
    Call InsertNewRows
End If

Function Code

Public Function Convert1Dto2D(v1D As Variant) As Variant

Dim v2D As Variant
Dim i As Long

ReDim v2D(1 To 1, 1 To UBound(v1D))

For i = LBound(v1D) To UBound(v1D)
    v2D(1, i) = v1D(i)
Next i

Convert1Dto2D = v2D

End Function

 

[Alex Blakenburg]

Thanks for sharing the function you wrote, I am sure others will find that helpful.
Another similar one is when you assign a range to a variant expecting an array but the range only consists of a single cell and it gives you a single variant value and not an array.
Covered off with something like this.

    ' Copy range to an array
    If rg.Cells.Count <> 1 Then
        arr = rg.Value
    Else
        ReDim arr(1 To 1, 1 To 1)
        arr(1, 1) = rg.Value
    End If

 

[BSALV]

Is there a price for the laziest code ?

Sub Lazy_Translate_1D_to_2D()
     Dim Arr_1D, Arr_2D
     Arr_1D = Split("a b c d 1234 123.458 1/1/22 12€")  'your 1D-array

     With Range("A1").Resize(, UBound(Arr_1D) + 1) 'copy it to a row
          .Value = Arr_1D
          Arr_2D = .Value 'read it back
     End With
 
End Sub

PS. Why is the font and the layout in #7 much better then average.
What option did RawlinsCross use ?

 

[BSALV]

it keeps the vartype

Sub Lazy_Translate_1D_to_2D()
     Dim Arr_1D(5), Arr_2D
     'Arr_1D = Split("a b c d 1234 123.458 1/1/22 12€")  'your 1D-array

     Arr_1D(0) = "abcdefg"
     Arr_1D(2) = 12345.567
     Arr_1D(3) = DateSerial(22, 3, 10)


     With Range("A1").Resize(, UBound(Arr_1D) + 1)              'copy it to a row
          .Value = Arr_1D
          Arr_2D = .Value                                       'read it back
     End With
End Sub