CODEWITHSUNDEEP
vbaexcel
Student of the Digital World
Student of the Digital World

Reputation: 3041

VBA Display Collection in a message box - error

I am trying to Pop-up a message something like, 

"There are 3 Reports available . They are Today, Yesterday, Day before"

This is the code, 

  On Error Resume Next
  For Each a In MonthYear
     arr.Add a, a
  Next

  For i = 1 To arr.Count
     Cells(i, 1) = arr(i)
  Next


MsgBox ("There are " & arr.Count & " Reports available. They are  " & arr &

But it is not working. It says sub or argument not found.

Upvotes: 1

Views: 4701

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

For an array, as it appeared that the OP was using one. Now that the OP has editted the post to show a collection this method will not work.

I will leave it for future readers for an option with arrays.

MsgBox "There are " & UBound(arr) + 1 & " Reports available. They are  " & Join(arr,",")

Upvotes: 6

Vityata
Vityata

Reputation: 43575

This is what I came up with:

Option Explicit

Sub TestMe()

    Dim arr     As Variant
    arr = Array("Today", "Yesterday", "DayBefore")

    MsgBox ("There are " & UBound(arr) + 1 & _
            " reports available. They are " & Join(arr, ", ") & ".")


End Sub

Concerning the collection this is one way to transfer the collection to a string:

Option Explicit

Public Sub TestMe2()

    Dim reportsCol  As New Collection
    Dim i           As Long
    Dim textReport  As String

    reportsCol.Add "Today"
    reportsCol.Add "Yesterday"
    reportsCol.Add "The day before yesterday"

    For i = 1 To reportsCol.Count
        textReport = TextReport & " " & reportsCol(i) & _
            IIf(i = reportsCol.Count, ".", ",")
    Next i

    MsgBox textReport

End Sub

The IIf at the bottom decides whether to put a full stop . or a comma ,.

Upvotes: 4

Related Questions