Creating a jagged array in VBA, writing seems to work but reading fails Type Mismatch

Question

I'm in SAP, trying to dump an array of GuiLabel s into a jagged array.

First I parse the child's ID to get the row and column while filtering out non-GuiLabel objects.

This part works.

I created a variant array that represents the lines, in each line element I put an array of strings representing each 'label columns'.

This part seems to work with no errors

The problem occurs when I try to read back from this first variant array.

I followed this great thread -> How do I set up a "jagged array" in VBA?

and by the end of it, it seems I should be able to read each of my elements with a simple Debug.Print myRows(0)(0) , however I get a type mismatch !

Any advice appreciated !

Function LabelToArray(LblCollection As Variant) As Variant()

    Dim myid As String
    Dim mycolumn As String
    Dim myrow As String
    Dim intLastRow As Integer
    Dim intLastCol As Integer
    Dim myRows As Variant
    Dim myColumns() As String

    ' Create first row
    ReDim myRows(0)

    ' For every child object in the collection
    For Each mychld In LblCollection

        ' Only execute for labels
        If mychld.Type = "GuiLabel" Then

            ' Get column and row of current label
            myid = Split(mychld.ID, "[")(4)
            myid = Mid(myid, 1, Len(myid) - 1)
            mycolumn = Split(myid, ",")(0)
            myrow = Split(myid, ",")(1)

            ' New row ? Reset column counter, set row counter and redim the array of rows
            ' This will fail spectacularly if SAP stop giving rows in numerical order
            If myrow > intLastRow Then intLastCol = 0: intLastRow = myrow: ReDim myRows(myrow)

            ' Reset or resize this line's columns array
            If intLastCol = 0 Then
                ' Set filled out 'myColumns' array into correct 'myRows' object
                If myrow <> 0 Then myRows(myrow - 1) = myColumns
                ' testing, print last column in this row
                If myrow <> 0 Then Debug.Print myColumns(UBound(myColumns))
                ' testing, print first column in this row
                If myrow <> 0 Then Debug.Print myColumns(0)
                ' testing, print number of columns
                If myrow <> 0 Then Debug.Print UBound(myColumns)
                ' Reset myColumns array because we're on a new line
                ReDim myColumns(0)
            Else
                ReDim Preserve myColumns(intLastCol)
            End If

            myColumns(intLastCol) = mychld.Text

            intLastCol = intLastCol + 1

            'Debug.Print mycolumn & "," & myrow

        End If

    Next

    ' Copy last row into array
    myRows(myrow) = myColumns

    ' SEEMS TO WORK FINE UP TO THIS POINT !

    Debug.Print UBound(myRows)
    Debug.Print myRows(0)(0)  'THIS LINE FAILS, TYPE MISMATCH (run-time error 13) ! I also tried cstr(myRows(0)(0))

    LabelToArray = myRows

End Function

Can you add an example with an input and an expected output? — Florent B., May 25 '16 at 05:31
@TimWilliams You are correct this wipes out the array ! Good catch ! I will try this tomorrow first thing in the afternoon ! (evening shift eh!) Thanks so much ! — Shodan, May 25 '16 at 07:03
@FlorentB. My error very likely is that missing 'preserve' statement that is wiping out the array each time a line is added. The LblCollection contains a bunch of object on a SAP GUI user area. Some of these objects are GuiLabels (just SAP labels). The labels are arranged by line and columns but each line has a different amount of 'columns'. Really it's just phrases built out of labels. Each label has a text caption. The ID property looks like this "wnd[0]/usr/lblsomething[24,3]". This would be a label at row #3 starting at column #24. Similar to a 80x25 console output. — Shodan, May 25 '16 at 07:07

Ambie · Accepted Answer · 2016-05-25T05:47:51.813

That error is thrown if the second dimension does not exist. My money would go on that as your problem.

Here's an example of an array of jagged arrays, the last being empty. The last line will throw a Type Mismatch error because there is no second dimension.

Dim mainArray(0 To 3) As Variant
Dim subArray As Variant

subArray = Array("A", "B", "C")
mainArray(0) = subArray
Debug.Print mainArray(0)(0)
'prints A

subArray = Array(1, 2, 3, 4)
mainArray(1) = subArray
Debug.Print mainArray(1)(0)
'prints 1

subArray = Split("HELLO|WORLD", "|")
mainArray(2) = subArray
Debug.Print mainArray(2)(0)
'prints HELLO

mainArray(3) = Empty
Debug.Print mainArray(3)(0)
'Type mismatch

Have a look in your Locals Window and test if your arrays are correct.

As noted in the comments, that ReDim without Preserve looks suspicious - I think you may be clearing your old arrays.

Thanks, looks like you guys have figured out my mistake, I'll post update tomorrow ! — Shodan, May 25 '16 at 07:04

Creating a jagged array in VBA, writing seems to work but reading fails Type Mismatch

1 Answers1