Dynamic Array and Message Box (VBA/Excel/Y2k)

Topic

New Reply

Is there a way in which the output from a dynamic array can be output to a message box? Thanks.

Reply | Quote

Replies

Yes, for example like this:

Sub Test()
Dim arr() As String
Dim itm As Variant
Dim strRes As String
ReDim arr(1 To 3)
arr(1) = “This”
arr(2) = “That”
arr(3) = “Other”
strRes = “Items:”
For Each itm In arr
strRes = strRes & vbCrLf & itm
Next itm
MsgBox strRes, vbInformation
End Sub

or like this:

Sub Test()
Dim arr() As String
Dim i As Integer
Dim strRes As String
ReDim arr(1 To 3)
arr(1) = “This”
arr(2) = “That”
arr(3) = “Other”
strRes = “Items:”
For i = LBound(arr) To UBound(arr)
strRes = strRes & vbCrLf & arr(i)
Next i
MsgBox strRes, vbInformation
End Sub

I assumed that your dynamic array has one dimension.

Reply | Quote

Or the old favourite Join function:

Sub Test()
   Dim arr() As String
   Dim itm As Variant
   Dim strRes As String
   ReDim arr(1 To 3)
   arr(1) = "This"
   arr(2) = "That"
   arr(3) = "Other"
   strRes = "Items:"
    strRes = strRes & vbCrLf & Join(arr, vbCrLf)
   MsgBox strRes, vbInformation
End Sub

Reply | Quote

An embarrassment of riches…

Reply | Quote

Hello Hans

I have been searching (unsuccessfully) for an example of this in a multi-dimensional array. Can you help me out?

Many thanks in advance.

Reply | Quote

Not much different really:

Sub Test()
    Dim arr() As String
    Dim i As Integer
    Dim strRes As String
    ReDim arr(1 To 3, 1 To 2)
    arr(1, 1) = "This"
    arr(1, 2) = "or"
    arr(2, 1) = "That"
    arr(2, 2) = "or a bit of the"
    arr(3, 1) = "Other"
    arr(3, 2) = "!"
    strRes = "Items:"
    For i = LBound(arr, 1) To UBound(arr, 1)
      For j = LBound(arr, 2) To UBound(arr, 2)
        strRes = strRes & vbCrLf & arr(i, j)
      Next j
    Next i
    MsgBox strRes, vbInformation
End Sub

Reply | Quote

Thanks for the response Rory

But, Ohhhh! I should have been more specific. I am looking for a “For Each” solution.

Why? you ask. Outside the academic interest: it just seems that it wopuld be a tad more elegant.

Reply | Quote

You can use

Sub Test()
Dim arr() As String
Dim v As Variant
Dim strRes As String
ReDim arr(1 To 3, 1 To 2)
arr(1, 1) = “This”
arr(1, 2) = “or”
arr(2, 1) = “That”
arr(2, 2) = “or a bit of the”
arr(3, 1) = “Other”
arr(3, 2) = “!”
strRes = “Items:”
For Each v In arr
strRes = strRes & vbCrLf & v
Next v
MsgBox strRes, vbInformation
End Sub

Reply | Quote

Thanks Hans
I think that I will need to ponder this for a while.

Reply | Quote