Printing a two dimensional array to a message box - Excel VBA

Question

I am trying to write the second two 'columns' of a dynamic array to a message box in a readable form. Is this possible?

I then want for if the user clicks 'OK' for the sub to continue, and if the user clicks cancel then I would like the sub to stop.

The code loading the array is as follows:

ReDim table(tableSize, 1 To 3)   ' set array length

i = 1
c = 1
Do While Worksheets("Items").Cells(i, 1).Value <> ""

    If Worksheets("Items").Cells(i, 2).Value = item Then    'load array
        table(c, 1) = i
        table(c, 2) = Worksheets("Items").Cells(i, 4)
        table(c, 3) = Worksheets("Items").Cells(i, 10)
        c = c + 1
    End If

    i = i + 1
Loop

I was trying to use a foreach loop but I don't know if that is the best way or not, and I couldn't work it out. To make it slightly more complicated I want to include a string before and after the printed out array.

@findwindow I cannot work out how to populate the message box at all so there is nothing in it. — GiANTOnFire
– GiANTOnFire, Commented May 24, 2016 at 15:54
Well, you need to show what you've tried. You can't expect SO to just write the code for you. — findwindow
– findwindow, Commented May 24, 2016 at 15:55
If that's how you load it, why not just build the string up as you load the array? — Rory
– Rory, Commented May 24, 2016 at 16:00
Inside the loop, before incrementing c, myStr = myStr & " " & table(c, 2). After the loop MsgBox myStr — OldUgly
– OldUgly, Commented May 24, 2016 at 16:13

Gary's Student · Accepted Answer · 2016-05-24 16:25:08Z

1

Here is one way to get a "table-style" message:

Sub MessyMessage()
    msg = ""

    For Each r In Range("A:A")
        If r.Value = "" Then Exit For
        msg = msg & vbCrLf & r.Value & vbTab & r.Offset(0, 1).Value
    Next r

    MsgBox msg
End Sub

answered May 24, 2016 at 16:25

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

Collectives™ on Stack Overflow

Printing a two dimensional array to a message box - Excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related