Why is VBA function giving compile error for collection but not for string

Question

I am trying to pass a collection to a VBA function but I'm getting a compile error I cannot seem to fix.

This is a simplified example that gives the error.

Sub test()
    Dim fooString As String
    Dim fooCollection As collection
    Set fooCollection = New collection
    useString (fooString)
    useCollection (fooCollection)
End Sub

Public Function useString(foo As String)
    MsgBox ("here")
End Function

Public Function useCollection(foo As collection)
    MsgBox ("here")
End Function

I'm not seeing what I'm doing differently than what is shown in examples such as the one shown here: How do I sort a collection?

This is the error I'm getting (Compile Error: Argument not optional):

score 5 · Answer 1 · edited Jun 07 '19 at 22:39

You're getting this dialog because of the implicit "helpful" default member. In the Object Browser navigate to the Collection in the classes pane and in the members you'll see that Item is the default member, designated by the teal icon.

Here's another example. Ask yourself what it will print and say it out loud before running the code.

Public Sub DontWrapIt()
    Dim foo As Range
    Set foo = Sheet1.Range("A1")
    GiveMeARange foo
    GiveMeARange (foo)
End Sub

Private Sub GiveMeARange(ByVal param As Variant)
    Debug.Print TypeName(param)
End Sub

Wrapping the object with parentheses causes it to be evaluated which causes an implicit invocation of the "helpful" default member... Which requires an Index parameter that isn't supplied - hence, "parameter not optional" .

score 1 · Answer 2 · answered Jun 07 '19 at 21:49

1

I don't know why that particular error (as opposed to a more informative error) is given, but when you call a sub or a function as a sub (not using the return value) you shouldn't have parentheses around the arguments. The following works:

Sub test()
    Dim fooString As String
    Dim fooCollection As collection
    Set fooCollection = New collection
    useString fooString
    useCollection fooCollection
End Sub

Public Function useString(foo As String)
    MsgBox "here"
End Function

Public Function useCollection(foo As collection)
    MsgBox "here"
End Function

But -- if your functions don't really return anything, perhaps you could just define them as subs to begin with. In VBA, there really isn't any point in making something which in a language like C would be a void function.

answered Jun 07 '19 at 21:49

John Coleman

51,337
7
54
119

1

You can call the function this way without assignment (and without the compile error), generally (you could do this with a variant array, a range, a string, an integer, etc.). I think the error here might have something do with how parentheses when used this way force an *evaluation* of the statement wrapped within. A collection doesn't have a value or default value. – David Zemens Jun 07 '19 at 22:03
2

@DavidZemens You are almost correct. `(fooCollection)` is [trying](https://stackoverflow.com/a/10262247/11683) to call `fooCollection.Item()` because it's the [default method](https://stackoverflow.com/a/53481683/11683) of `Collection`, and the `Item()` method requires an index argument. It's *that* argument that is missing. – GSerg Jun 07 '19 at 22:04
Further, when you `New` the collection it's a `Nothing` initially. If you alternatively do `Dim c as Object: Set c = New Collection`, there won't be a compile error (but there will be a runtime 450 error) – David Zemens Jun 07 '19 at 22:06
@DavidZemens That makes sense. The parentheses are usually harmless (unless you have more than one argument), but in this case they are not. GSerg really nails the error down. – John Coleman Jun 07 '19 at 22:14
4

The parens around an argument *always* force evaluation as a value, which has the side-effect of forcing the argument to be passed `ByVal`, even if the procedure says it's a `ByRef` parameter - I disagree with this being "harmless": it's a silent implicit operation that *usually goes unnoticed*, until it inevitably causes problems... which can be extremely difficult to pin down when you don't know what's going on. – Mathieu Guindon Jun 07 '19 at 22:47

FAB · Answer 3 · 2019-06-08T07:12:34.293

0

Is just a matter of calling the other sub..

If it doesn't have any parameters, you can call it by name only:

useCollection or Call useCollection

If it does have parameters, then you either call it without parentheses, or you use Call:

useCollection parameter or Call useCollection(parameter)

Sub test()
    Dim fooString As String
    Dim fooCollection As Collection
    Set fooCollection = New Collection
    useString (fooString)

    fooCollection.Add "test message", "test"
    useCollection fooCollection
    Call useCollection(fooCollection)
End Sub

Public Function useString(foo As String)
    MsgBox ("here")
End Function

Public Function useCollection(foo As Collection)
    MsgBox foo(1)
End Function

See some more info about the Call statement:

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist. If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded

edited Jun 08 '19 at 07:12

answered Jun 07 '19 at 21:49

FAB

2,505
1
10
21

1

Do NOT use `Call` - it is deprecated. Which means no longer supported and could disappear from the language at any time. Why create new code with a feature that is dead? – AJD Jun 08 '19 at 06:44
1

@AJD I've seen people before mentioning that `Call` is deprecated, yet on the documentation site there is no mention about being deprecated. Can you kindly provide some resource to support your claim? I've updated my answer as well with some more details from the documentation page. – FAB Jun 08 '19 at 07:18
2

@FAB In QuickBasic/QBasic for Dos `Call` allows calling assembler functions. With an assembler function, for some technical reason, it cannot return a value. So Call discard return values. It use it that you pass a variable in as a parameter, and it fills in the variable. The passed in parameter must be large enough (or you crash) to hold the return value. It does nothing in Windows except be compatible with old Dos code, in case you are porting it. – Noodles Jun 08 '19 at 07:38
@Noodles Thanks for the insight into QuickBasic/QBasic, in all honesty is first time I hear about them, so this sounds like a history lesson to me. I didn't know specifically that `Call` discards values before reading the above documentation today, but if you are `Call`ing (and not assigning to a variable), you are not expecting a return value anyway, so is kind of implicit understanding that nothing will be returned. As for the `Call` statement, it does make the code more readable (for me), and as far as I can tell, is not deprecated... would like to know why people say it is. – FAB Jun 08 '19 at 07:52
1

@Fab Call was designed so say `func(Param1 as long, Param2 as string) as Anything`. So Param2 is where the function returns it value, but the function can't allocate memory so Param2 MUST be big enough to hold the return value before you pass it. Also a intrinsic feature of Basic is if you don't care about the return value you call a `Function` as if it's a `Sub`. That is what Basic programmers expect of you so they can understand your code. – Noodles Jun 08 '19 at 08:01

score 0 · Answer 4 · answered Jun 08 '19 at 15:14

This works:

Sub test()
    Dim fooString As String
    Dim fooCollection As VBA.collection
    Set fooCollection = New VBA.collection
    fooCollection.Add "bar", "bar"
    useString (fooString)
    useCollection fooCollection
End Sub

Public Function useString(foo As String)
    MsgBox ("useString")
End Function

Public Function useCollection(ByRef foo As VBA.collection)
    MsgBox ("useCollection")
End Function

Why is VBA function giving compile error for collection but not for string

4 Answers4