VBA: Argument not optional

Question

I'm trying to mess around with some collections in VBA for the first time. I'm planning on using this collection to open multiple reports and run the same bit of code, which is why I wanted to get them into a collection. (If there's a better way to do that, please let me know.)

My collection making Function (to return the collection?):

Function CollectReports() As Collection

    Dim reports As New Collection

    reports.Add Item:="plant1", Key:="0"
    reports.Add Item:="plant2", Key:="1"
    reports.Add Item:="plant3", Key:="2"
    reports.Add Item:="plant4", Key:="3"

    TestCollection (reports)

End Function

My collection testing Sub:

Sub TestCollection(reports As Collection)

    Dim x As Variant

    For Each x In reports
        MsgBox (x)
    Next

End Sub

I originally had the sub as Sub TestCollection(ByRef reports) which is what I've been using for other methods which require a Dim from another method.

My issue is that I get an Argument not optional error when I attempt to debug my CollectReports() function

If you're feeling really generous, here's the code block I'm planning on using this collection for - is a collection the best way to do this?

Sub VlookupMGCCode(ByRef reports)

Dim lastrow As Integer
Dim wRange As Range
Dim blankRange As Range
Dim x As Variant

lastrow = Cells(Rows.count, "A").End(xlUp).Row
Set wRange = Range("$T$7:$T$" & lastrow) 'a single column to vlookup

CollectReports

For Each x in CollectReports 'deffinately an error here
    Set blankRange = wRange.SpecialCells(xlCellTypeBlanks)
    blankRange.Formula = "=VLOOKUP(RC[-18],'[" & x & "]Sheet1'!C1:C31,31,FALSE)"

    With blankRange
        .FillDown
        .Copy
        .PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False
    End With
Next

End Sub

I haven't tried running the VlookupMGCCode() Sub yet as the collection is needed, so I don't know what errors might come up, but I'm pretty confident that the way I'm trying to use the collection which CollectReports returns is wrong.

Thank you very much for your help and time!

score 1 · Answer 1 · answered Jul 09 '15 at 15:30

1

I believe the error reporting is misleading, you are actually getting the error at TestCollection(Reports) you dont need brakcets when calling a sub. Please try removing this and feedback if it works

Function CollectReports() As Collection

    Dim reports As New Collection

    reports.Add Item:="plant1", Key:="0"
    reports.Add Item:="plant2", Key:="1"
    reports.Add Item:="plant3", Key:="2"
    reports.Add Item:="plant4", Key:="3"

    TestCollection reports

End Function

answered Jul 09 '15 at 15:30

Krishna

2,451
1
26
31

Yes, drop the parenthesis. Excel is really weird with how it treats parameters that are inside of parenthesis. Objects need to be passed outside of parenthesis or excel treats them differently. It's really odd. As said in another comment, using the Call keyword changes the way they are handled as well. – user1274820 Jul 09 '15 at 15:34
TestCollection (reports) expects that to be set to a value. something like tmp_var = TestCollection (reports) If I remember correctly – 99moorem Jul 09 '15 at 15:38
but TestCollection is a sub not function - no return value. why the assignment? – Krishna Jul 09 '15 at 15:41

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

See this answer for an explanation of when to use brackets.

You have a couple of issues:

This line needs to be changed:

TestCollection (reports)

To either

Call TestCollection (reports)

or

TestCollection reports
Your CollectReports function is missing the line of code that assigns the collection. You need to add this line just before the end of the function:

Set CollectReports = reports

edited May 23 '17 at 12:29

Community

1
1

answered Jul 09 '15 at 15:33

ChipsLetten

2,923
1
11
28

Hey thank you for the answer. On the train of thought of `Set x = value`, could you explain why something like `Dim x As Integer` can be set with `x=1` while a range such as `Dim w As Range` needs `Set w as Range`? – Tawm Jul 09 '15 at 15:42
2

Because one is a simple value and the other is a reference to an object. See [this answer](http://stackoverflow.com/a/9924325/4243498) and [this one](http://stackoverflow.com/q/349613/4243498) – ChipsLetten Jul 09 '15 at 15:45

score 0 · Answer 3 · answered Jul 09 '15 at 15:28

Doing things slightly the wrong way round

Function CollectReports(reports As Collection) As Collection

    reports.Add Item:="plant1", Key:="0"
    reports.Add Item:="plant2", Key:="1"
    reports.Add Item:="plant3", Key:="2"
    reports.Add Item:="plant4", Key:="3"

End Function

Sub TestCollection()
    Dim reports As New Collection

    CollectReports reports

    Dim x As Variant

    For Each x In reports
        MsgBox (x)
    Next

End Sub

As for your other code I have not tested it all but

CollectReports

For Each x in CollectReports 'deffinately an error here

Change to

CollectReports reports

For Each x In reports 'deffinately an error here

Thanks for the answer! However, it works better in my mind if I run the Function and then call a Test instead of the other way around. @Krishna gave a solution which follows my train of thought more, but still, thank you :) -- this applies to the first post, not the edit :p — Tawm, Jul 09 '15 at 15:32

VBA: Argument not optional

3 Answers3