Getting "Argument not optional" error on segment of code

Question

I keep getting "Argument not optional" on this Sub. The .Add word always gets highlighted as well.

I've tried using Set and declaring the collection in the Sub and in the calling function. Setting the argument as ByRef nor Optional works either.

Sub getDescriptions(ByRef descriptions As Collection)
    Dim i As Integer
    i = 0
    Set descriptions = New Collection
    Do While Cells(i + 3, 1).Value <> "" And Cells(i + 3, 2).Value <> ""
        descriptions.Add = Cells(i + 3, 2).Value & " - Test Period " & Cells(i + 3, 4).Value & " - " & Cells(i + 3, 5).Value
        i = i + 1
    Loop
End Sub

Public descriptions as Collection

Private Sub UserForm_Initialize()    'calling Sub
    With Application.ActiveWindow
        Me.Left = .Left + (.Width - Me.Width) / 2
        Me.Top = .Top + (.Height - Me.Height) / 2
    End With
    Set descriptions = New Collection
    getDescriptions (descriptions)
...

I think it would help to know how you are calling the sub as well... and given that you are supposed to pass the description as an argument, why set it as new? — FAB
– FAB, Commented Jun 18, 2019 at 18:57

Mathieu Guindon · Accepted Answer · 2019-06-18 19:20:23Z

3

There's the problem:

descriptions.Add = {expression}

You're invoking the Add method without any parameters, and then attempting to assign to its return value (and I don't think it has one).

Syntactically, that's as follows:

Take the descriptions collection
Invoke its Add method without any arguments (compile error: argument not optional)
Take the return value (there isn't one, but that's syntactically irrelevant) and, assuming it's an object with a default property, assign that to the expresion on the right-hand side of the = operator.

If the Add method didn't require any arguments and returned an object reference, your code could be valid. Since it does have a non-optional parameter, and doesn't return anything, it's a compile error.

Remove the = operator, you'll get this:

descriptions.Add {expression}

Which is as follows:

Take the description collection
Invoke its Add method, pass {expression} as an argument

This is also a problem:

getDescriptions (descriptions)

Remove the parentheses; they are forcing the object reference to be evaluated as a value expression - and the Collection class' default property (invoked when let-coercing the object during expression evaluation) being its parameterized Item(Index) member, you can't legally do that. Note that if the default property wasn't parameterized, you would be passing its value ByVal, regardless of getDescriptions specifying ByRef.

That said the descriptions parameter only needs to be passed ByRef because you are reassigning the object reference itself with that Set statement - a rather bug-prone thing to do with a global variable.

edited Jun 18, 2019 at 19:20

answered Jun 18, 2019 at 19:10

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

K.Dᴀᴠɪs Over a year ago

One other issue - if this code is all located in the same module, Public descriptions as Collection should be in the declarations section. Would be caught when using Option Explicit. But I am confused why OP is passing public vars as an argument anyway.

Collectives™ on Stack Overflow

Getting "Argument not optional" error on segment of code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related