Excel vba: Class sub: Wrong number of arguments or invalid property assignment on vba

Question

I have a class State and some sub inside it that takes a Scripting.Dictionary as an argument. However when I try to pass a dictionary there, I get a wrong number of arguments or invalid property assignment error. I can't figure out what's wrong.

'Sub insite State class
Sub addSecondItems(itemsDict As Object)
    MsgBox ("start addSecondItems")
End Sub

Sub test()
Dim stateCopy As State
Set stateCopy = New State
...
Dim dict1 As Object
Set dict1 = CreateObject("Scripting.Dictionary")
stateCopy.addSecondItems (dict1)  'error here
...
End Sub

At the same time

Sub testPetDict()

    Dim petDict As Object
    Set petDict = CreateObject("Scripting.Dictionary")
    Call readPetDict(petDict)

End Sub

Sub readPetDict(petDict As Object)
        Dim year As Integer
        For year = 2014 To 2017
            MsgBox (year & ". " & petDict(year))
        Next
End Sub

works fine.

What may be wrong here and why the second case works, while the first fails?

Remove the brackets: stateCopy.addSecondItems dict1 or use Call — Rory
– Rory, Commented Sep 19, 2017 at 13:29
By using parentheses, you force the object to be passed by value, hence the error. — Kostas K.
– Kostas K., Commented Sep 19, 2017 at 13:36
@Rory Thank you kind person! You saved me from going irreversible desperate! You might post an answer and I'll approve it. — Ans
– Ans, Commented Sep 19, 2017 at 13:56
@Kostas What's wrong with a dictionary being passed by value though? It's not like I need to edit it or something. — Ans
– Ans, Commented Sep 19, 2017 at 13:57
You can't pass an object by value, only by reference. Passing by value means creating and passing a copy where by reference, only a reference to the object is passed. — Kostas K.
– Kostas K., Commented Sep 19, 2017 at 13:58

Rory · Accepted Answer · 2017-09-19 15:25:21Z

3

You should remove the brackets:

stateCopy.addSecondItems dict1

or use Call

Call stateCopy.addSecondItems(dict1)

Otherwise the brackets try to coerce the dictionary to a value by calling its default property, Item, which requires an argument, hence the error message.

answered Sep 19, 2017 at 15:25

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

Excel vba: Class sub: Wrong number of arguments or invalid property assignment on vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related