CODEWITHSUNDEEP
excelvba
Ben
Ben

Reputation: 47

Getting "Argument not optional" error on segment of code

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)
...

Upvotes: 1

Views: 41

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

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.

Upvotes: 3

Related Questions