Subroutine will not compile

Question

I'm using Access VBA, and I keep getting

Compile error: Argument not optional

whenever I try to pass a collection into a function. What is going on?

Private Sub btnTest_Click()
    Dim GarbageLanguages As New Collection
    GarbageLanguages.Add "VBA"
    PrintCollectionCount (GarbageLanguages)  '<-- error happens here
End Sub

Public Sub PrintCollectionCount(c As Collection)
    Debug.Print c.Count
End Sub

Eggers

Answer 1

Short Answer

Remove the parentheses from the following line:

PrintCollectionCount (GarbageLanguages)

Long Answer

For better or worse (mostly worse), VBA has both functions and subroutines:

• Function - expression that must return a value
• Subroutine - statement that cannot return a value

Unfortunately, using each of them requires slightly different syntax. Suprisingly, this is not a valid subroutine call:

Subroutine(arguments)

Instead, you need to use one of these two options:

Call Subroutine(arguments)
Subroutine arguments

It's even more unfortunate that when you use the wrong syntax, all you get is extremely cryptic error messages. Finally, it's also hard to get used to not using parenthesis because single arguments that are primitive types instead of objects actually work fine:

Subroutine(SomeString)                ' works
Subroutine(SomeInteger)               ' works
Subroutine(SomeObject)                ' does not work
Subroutine(SomeString, SomeInteger)   ' does not work

Aside from memorizing the awful error messages, you can try to train yourself to look out for whenever a space gets automatically inserted after the subroutine's name. This:

Subroutine(argument)

gets changed to this:

Subroutine (argument)  '<-- RED FLAG

Eggers