Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    Or the old favourite Join function:
    <pre>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
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    An embarrassment of riches... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Regards
    Don

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

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

    Not much different really:
    <pre>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
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Regards
    Don

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •