Excel VBA: Argument Not Optional Error When Adding To An Array

Question

I am having an issue with the below code. Whenever it gets to the last line, it throws the error "Argument Not Optional", and will not compile. I have no clue what argument it is talking about.

Option Base 1
Dim temp As New ProductionItem 'Production Item is a class that I made
Dim Arr() As Collection
ReDim Arr(5)
Dim coll As New Collection
coll.Add temp
Arr(1) = coll

A Collection is an object. If you want to set Arr(1) to be the collection coll, you will need to Set Arr(1) = coll. (But are you really intending Arr to be an array of Collections of ProductionItem?) — YowE3K
– YowE3K, Commented Apr 11, 2017 at 22:45
And the missing argument it is talking about is the key, because without the Set keyword, VBA was trying to invoke the default property of the Collection object, that is, col1.Item(key) where the parameter key is mandatory... — A.S.H
– A.S.H, Commented Apr 11, 2017 at 23:24

YowE3K · Accepted Answer · 2017-04-11 23:25:12Z

1

Only need Set

Option Base 1

Sub fun()
    Dim Arr() As Collection
    ReDim Arr(5)
    Dim coll As New Collection
    coll.Add 1
    Set Arr(1) = coll
End Sub

edited Apr 11, 2017 at 23:25

YowE3K

24k7 gold badges28 silver badges45 bronze badges

answered Apr 11, 2017 at 23:01

ruirodrigues1971__

3521 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA: Argument Not Optional Error When Adding To An Array

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