CODEWITHSUNDEEP
arraysvba
clever_trevor
clever_trevor

Reputation: 1674

Setting an array value results in an Argument not optional error in Visual Basic

I am trying to set the values of an array of Collections inside a For loop. However, when I go to run the program, it throws a compile error that states "Argument not optional" and highlights the part where I set the array value. When I go to debug the subroutine, I cannot get past the first line of ConvertbucketCollectionTobucketArray(). At that point, bucketArray elements 0 through 12 have a value of Nothing and bucketCollection contains 13 elements(1-13), where only several contain items.

Dim bucketCollection As New Collection 'the Collection of buckets
Dim bucketArray(12) As New Collection 'bucketCollection as an array

...

Private Sub ConvertbucketCollectionTobucketArray() 'debugger stops here

    Dim newCol As Collection
    Dim i As Integer

    For i = 1 To bucketCollection.count

        Set newCol = bucketCollection.Item(i)
        bucketArray(i - 1) = newCol 'highlighted line here

    Next

End Sub

Upvotes: 0

Views: 992

Answers (1)

Alter
Alter

Reputation: 3464

Since collection is an object, you have to use set when copying it to an element (this is your error).

Set bucketArray(i - 1) = newCol

This will not cause an error, but an array doesn't need to be set as new.

Dim bucketArray(12) As Collection

And if bucketCollection is a classwide variable, you should separate creating a new instance and declaring it. Create a new instance of it in one of the functions. Otherwise, if you run the same code twice, it might use the same instance.

Sub test()
    Set bucketCollection = New Collection
    populate
    ConvertbucketCollectionTobucketArray
End Sub

Upvotes: 2

Related Questions