Adding Data to a Collection

[kingofaces]

Using collections is something I picked upon just recently, so I'm having a little trouble understanding why the current function returns a #Name error. Basically what I'm trying to do is add the y1 and y2 variables into a collection called ygroup. I'm then using a For Each loop to do a calculation for each of the y variables within y group. This is an example I have been tinkering with to try and understand how I could use collections in some of my larger projects, so any insight would go a long way. I am a bit of a beginner when it comes to collections and arrays, so maybe there is a much simpler solution if this isn't the most feasible method?

Function test(x, y1, y2) As Long
Dim ygroup As New Collection
Dim y As Long
test1 As Long

With ygroup
.Add Item:=y1
.Add Item:=y2
End With


For Each y In ygroup
Do
test1 = (x + y)
test = test + test1
Next y


End Function

I get the feeling I've just misunderstood how to properly enter the data into a collection after Googling various examples which didn't seem to explain the process very well for me.

 

[Bill Rockenbach]

take a look at this site

http://www.functionx.com/vbaexcel/objects/Lesson6.htm

 

[Ruddles]

For Each y In ygroup
Do
test1 = (x + y)
test = [COLOR=red][B]test[/B][/COLOR] + test1
Next y

What is the value of test in the code above?

 

[AlphaFrog]

Sub Mytest()
    MsgBox fxTest(1, 2, 3)
End Sub

Function fxTest(x As Long, y1 As Long, y2 As Long) As Long

    Dim ygroup As New Collection
    Dim y As Variant
    
    With ygroup
        .Add Item:=y1
        .Add Item:=y2
    End With
    
    For Each y In ygroup
        fxTest = fxTest + (x + y)
    Next y

End Function

 

[kingofaces]

Thanks Alphafrog, that did the trick. At first it still returned an error until I realized I had code open for just the sheet that I had been working with, but when I put it into a module the function worked fine. At least that clarifies I was on the right track and just missed a little housekeeping issue. The site Bill provided seemed like a good one when I first found it, but even though it seemed straightforward I kept searching when I couldn't find what was causing the issue. Thanks for showing me I'm going the right direction with this at least folks.

Ruddles, the test variable is initially 0 or nothing (pretty sure such as a statement isn't required aside from letting others know what is). All that value was doing was keeping a running total of each time the For Each loop did a calculation.