VBA - MsgBox a 2D Array (Matrix)

Question

I am trying to visualize a 2D Matrix (Array) using a MsgBox, but the code I have doesn't give me the correct representation.

Sub test()

Dim M(22, 7) As Double
TwoD_Array_Matrix_In_MSGBOX (M)

End Sub
'_________________________________________________________________________

Public Function TwoD_Array_Matrix_In_MSGBOX(arr As Variant)

h = UBound(arr, 1)
w = UBound(arr, 2)
'MsgBox ("h = " & CStr(h + 1) & vbCrLf & "w = " & CStr(w + 1)) ' to check if the width and hight of the Matrix are correct
Dim msg As String

For i = 0 To w
    For ii = 0 To h
        msg = msg & arr(ii, i) & vbTab
    Next ii
    msg = msg & vbCrLf
Next i

MsgBox msg

End Function

This is the result I get:

I think it's because you are limited by the length of the `msg` (so it's skipping or something) . You could try to save each row in a String, so you'll have an array of `String`s , and then loop and show them in a `MsgBox` — Shai Rado, Jan 25 '17 at 13:51
For what its worth i really like the idea of trying to see the output of an array visually. — Doug Coats, Jan 25 '17 at 13:53

score 3 · Accepted Answer · edited Jan 25 '17 at 14:14

3

You have w and h interchanged.

Dim msg As String

For i = 0 To h
    For ii = 0 To w
        msg = msg & arr(i, ii) & vbTab
    Next ii
    msg = msg & vbCrLf
Next i

MsgBox msg

edited Jan 25 '17 at 14:14

R3uK

14,417
7
43
77

answered Jan 25 '17 at 13:45

CMArg

1,525
3
13
28

Hi CMArg, I believe they are correct in my code, as otherwise I get "Subscript out of range" Error – Trenera Jan 25 '17 at 13:47
1

you're scanning the array properly, but the first line of the messagebox is showing the 8 first elements of your first dimension (that has 23 elements), the second row another 8 and so. And I think thats not what you want.. – CMArg Jan 25 '17 at 13:50
1

See Doug answer: you have `ii` and `i` interchanged (in `arr(ii, i)`)... – CMArg Jan 25 '17 at 13:51
Thats def the issue – Doug Coats Jan 25 '17 at 13:52
CMArg, you were right. I changed the code so: first loop is for h and the for w. I didn't paid attentined to that in the begginning, as I just saw i and ii interchanged in your answer. Thanks! – Trenera Jan 25 '17 at 13:55
Good. And I will keep you code for examine arrays. They use to be a headache to me... – CMArg Jan 25 '17 at 13:59

score 3 · Answer 2 · answered Jan 25 '17 at 13:50

this works perfectly for me

Private Sub this()
    Dim this(22, 7) As Integer
    Dim msg$
    For i = LBound(this, 1) To UBound(this, 1)
        For j = LBound(this, 2) To UBound(this, 2)
            msg = msg & this(i, j) & vbTab
        Next j
    Next i
    MsgBox msg
End Sub

score 0 · Answer 3 · answered Jan 25 '17 at 14:26

It might be more flexible to write a function which returns a string, a sort of 2-dimensional join, which allows you to choose both the item delimiter (defaulting to vbTab) and the row delimiter (defaulting to vbCrLf).

You can MsgBox this string -- or write it to the immediate window -- or (with a comma chosen as one of the delimiters) -- write it to a CSV file, etc.:

Function MatrixJoin(M As Variant, Optional delim1 As String = vbTab, Optional delim2 As String = vbCrLf) As String
    Dim i As Long, j As Long
    Dim row As Variant, rows As Variant

    ReDim rows(LBound(M, 1) To UBound(M, 1))
    ReDim row(LBound(M, 2) To UBound(M, 2))

    For i = LBound(M, 1) To UBound(M, 1)
        For j = LBound(M, 2) To UBound(M, 2)
            row(j) = M(i, j)
        Next j
        rows(i) = Join(row, delim1)
    Next i
    MatrixJoin = Join(rows, delim2)
End Function

Tested by:

Sub test()
    Dim A As Variant
    A = Range("A1:B3").Value
    MsgBox MatrixJoin(A)
    Debug.Print MatrixJoin(A, ",", ";")
End Sub

Screenshots of output:

VBA - MsgBox a 2D Array (Matrix)

3 Answers3