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
To correct this errorMake sure that the procedure name is spelled correctly. Find the name of the project containing the procedure you want to call in the References dialog box. If it does not appear, click the Browse button to search for it. Select the check box to the left of the project name, and then click OK.
I have resolved same error by following these 4 steps : Open Excel file which is having issue, press Alt + F11 go into its Visual Basic Editor. From the Tools menu select References ( Note, if references option is disabled in tools menu try closing and reopening file and enable Macro before proceeding next steps)
A compilation error can also occur when VBA doesn't find anything missing while typing the code, but it does when the code is compiled or executed. VBA checks each line as you're typing the code and highlights the syntax error as soon as the line is incorrect and you hit enter.
The Compile Error “End If without Block If: This is a simple compile time error that's thrown when the code containing any If blocks do not comply with the syntax (or) such a statement does not exist.
Remove the parentheses from the following line:
PrintCollectionCount (GarbageLanguages)
For better or worse (mostly worse), VBA has both functions and subroutines:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With