1

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.

Community
  • 1
  • 1
Student of the Digital World
  • 2,971
  • 16
  • 62
  • 116
  • You have to specify which element of array you want in `& arr &`. As you want to list them all you need to have a loop that builds it. – Victor K Sep 20 '17 at 19:21
  • 3
    `On Error Resume Next` is a nice way to shoot yourself in the foot when attempting to debug code. Actually, it's a nice way to shoot yourself in the foot in debugged production code except when used in a _very_ controlled manner. – FreeMan Sep 20 '17 at 19:55

2 Answers2

6

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,",")
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
4

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 ,.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • This will work if arr is an array. But in my case, it is a collection. – Student of the Digital World Sep 20 '17 at 19:32
  • 3
    @Sid29 for the love of everything that's good in this world, **please** rename that variable to something meaningful, e.g. `reportColumns`. And [**drop *Systems Hungarian* notation**](https://www.joelonsoftware.com/2005/05/11/making-wrong-code-look-wrong/). – Mathieu Guindon Sep 20 '17 at 19:39
  • @Mat'sMug - I suppose that you mean me and not Sid? – Vityata Sep 20 '17 at 19:40
  • @Mat'sMug Yes sir, will do :) It is so confusing. I started building it as an array and then just changed it to collection, that's why. – Student of the Digital World Sep 20 '17 at 19:41
  • @Mat'sMug - ok, I will edit it here. But in general, once I started like this it is tough to change. My code will become inconsistent. – Vityata Sep 20 '17 at 19:41
  • @Mat'sMug - now you only need to know a certain boss, who does not allow additional software used in the company and everything will come to its place. – Vityata Sep 20 '17 at 19:43
  • 1
    If your IT folks / programmers have tooling such as ReSharper or they're using Visual Studio, that certain boss is unfairly blocking your productivity. – Mathieu Guindon Sep 20 '17 at 19:44
  • @Mat'sMug - true, but "the programmers are programmers and I am writing on that funny scripting language that does not even compile" (these are real words, that I have heard about 4 years ago in a German company. I always laugh when I remember it! :) – Vityata Sep 20 '17 at 19:50
  • @Vityata oh FFS... he's mistaking VBA for VBScript. WTF does he think 'Debug' -> 'Compile' do?! – Mathieu Guindon Sep 20 '17 at 19:52
  • @Mat'sMug will definitely look into that. Thanks:-) – Student of the Digital World Sep 20 '17 at 19:55
  • @Mat'sMug - he meant VBA and he meant compiling the way C# does. – Vityata Sep 20 '17 at 19:55
  • 1
    Wow, what a hopeless ignorant jerk. [VBA is a perfectly fine OOP-capable COM-based language](https://stackoverflow.com/a/31861832/1188513) and FWIW it's a hosted version of VB6, running with the very same VM. And VB6 was the "funny scripting language" everyone was using to compile executables and type libraries 15 years ago. The VM runs p-code, VBA compiles to p-code, and p-code is the pre-.NET equivalent of MSIL or Java's bytecode. This loser has no clue what he's talking about. – Mathieu Guindon Sep 20 '17 at 20:02
  • @Mat'sMug - true but he had his own way of understanding computers... That's why I have separated VBA from the other languages here - http://vitoshacademy.com/about – Vityata Sep 20 '17 at 20:09
  • 1
    @Mat'sMug - cheer up, it will appear in the near future :) – Vityata Sep 20 '17 at 20:57