current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.