Take Up A Collection
 

I have some code that is passed a Collection of positive values. What is the
easiest way to average the values ?
--
Gary''s Student - gsnu201001

Take Up A Collection
 

Is your collection a collection of range values? That's what I assumed. Not
sure if this is the easiest way, but it worked for me.
Sub test()

Dim coll As Collection

Set coll = New Collection
With coll
.Add Range("A1").Value
.Add Range("A2").Value
.Add Range("A3").Value
.Add Range("A4").Value
End With

MsgBox AvgCollection(coll)

End Sub

Function AvgCollection(col As Variant) As Double

Dim c As Variant
Dim dblSum As Double
Dim counter As Long

For Each c In col
dblSum = dblSum + c
counter = counter + 1
Next c

AvgCollection = dblSum / counter

End Function

--
Cheers,
Ryan


"Gary''s Student" wrote:

I have some code that is passed a Collection of positive values. What is the
easiest way to average the values ?
--
Gary''s Student - gsnu201001

Take Up A Collection
 

Two things... first, I would probably declare the argument to your
AvgCollection function as Collection instead of Variant and, second, you
don't need to maintain a separate Counter to count the elements in the
Collection as Collections have a Count property. This is how I would modify
your function...

Function AvgCollection(col As Collection) As Double
Dim c As Variant
Dim dblSum As Double
For Each c In col
dblSum = dblSum + c
Next
AvgCollection = dblSum / col.Count
End Function

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
Is your collection a collection of range values? That's what I assumed.
Not
sure if this is the easiest way, but it worked for me.
Sub test()

Dim coll As Collection

Set coll = New Collection
With coll
.Add Range("A1").Value
.Add Range("A2").Value
.Add Range("A3").Value
.Add Range("A4").Value
End With

MsgBox AvgCollection(coll)

End Sub

Function AvgCollection(col As Variant) As Double

Dim c As Variant
Dim dblSum As Double
Dim counter As Long

For Each c In col
dblSum = dblSum + c
counter = counter + 1
Next c

AvgCollection = dblSum / counter

End Function

--
Cheers,
Ryan


"Gary''s Student" wrote:

I have some code that is passed a Collection of positive values. What is
the
easiest way to average the values ?
--
Gary''s Student - gsnu201001

Take Up A Collection
 

The reason I used Variant was because I wasn't totally sure what he meant by
"a collection of positive values". I assumed he had a collection of cell
values, but that it could be possible to have a collection of values in an
Array. But you are right, in my example I should have used the syntax in
your post.

Thanks for the tips! They are always welcome.
--
Cheers,
Ryan


"Rick Rothstein" wrote:

Two things... first, I would probably declare the argument to your
AvgCollection function as Collection instead of Variant and, second, you
don't need to maintain a separate Counter to count the elements in the
Collection as Collections have a Count property. This is how I would modify
your function...

Function AvgCollection(col As Collection) As Double
Dim c As Variant
Dim dblSum As Double
For Each c In col
dblSum = dblSum + c
Next
AvgCollection = dblSum / col.Count
End Function

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
Is your collection a collection of range values? That's what I assumed.
Not
sure if this is the easiest way, but it worked for me.
Sub test()

Dim coll As Collection

Set coll = New Collection
With coll
.Add Range("A1").Value
.Add Range("A2").Value
.Add Range("A3").Value
.Add Range("A4").Value
End With

MsgBox AvgCollection(coll)

End Sub

Function AvgCollection(col As Variant) As Double

Dim c As Variant
Dim dblSum As Double
Dim counter As Long

For Each c In col
dblSum = dblSum + c
counter = counter + 1
Next c

AvgCollection = dblSum / counter

End Function

--
Cheers,
Ryan


"Gary''s Student" wrote:

I have some code that is passed a Collection of positive values. What is
the
easiest way to average the values ?
--
Gary''s Student - gsnu201001

.

Take Up A Collection
 

Thanks Rick!
--
Gary''s Student - gsnu201001


"Rick Rothstein" wrote:

Two things... first, I would probably declare the argument to your
AvgCollection function as Collection instead of Variant and, second, you
don't need to maintain a separate Counter to count the elements in the
Collection as Collections have a Count property. This is how I would modify
your function...

Function AvgCollection(col As Collection) As Double
Dim c As Variant
Dim dblSum As Double
For Each c In col
dblSum = dblSum + c
Next
AvgCollection = dblSum / col.Count
End Function

--
Rick (MVP - Excel)


"Ryan H" wrote in message
...
Is your collection a collection of range values? That's what I assumed.
Not
sure if this is the easiest way, but it worked for me.
Sub test()

Dim coll As Collection

Set coll = New Collection
With coll
.Add Range("A1").Value
.Add Range("A2").Value
.Add Range("A3").Value
.Add Range("A4").Value
End With

MsgBox AvgCollection(coll)

End Sub

Function AvgCollection(col As Variant) As Double

Dim c As Variant
Dim dblSum As Double
Dim counter As Long

For Each c In col
dblSum = dblSum + c
counter = counter + 1
Next c

AvgCollection = dblSum / counter

End Function

--
Cheers,
Ryan


"Gary''s Student" wrote:

I have some code that is passed a Collection of positive values. What is
the
easiest way to average the values ?
--
Gary''s Student - gsnu201001

.

Take Up A Collection
 

Thanks!
--
Gary''s Student - gsnu201001


"Ryan H" wrote:

Is your collection a collection of range values? That's what I assumed. Not
sure if this is the easiest way, but it worked for me.
Sub test()

Dim coll As Collection

Set coll = New Collection
With coll
.Add Range("A1").Value
.Add Range("A2").Value
.Add Range("A3").Value
.Add Range("A4").Value
End With

MsgBox AvgCollection(coll)

End Sub

Function AvgCollection(col As Variant) As Double

Dim c As Variant
Dim dblSum As Double
Dim counter As Long

For Each c In col
dblSum = dblSum + c
counter = counter + 1
Next c

AvgCollection = dblSum / counter

End Function

--
Cheers,
Ryan


"Gary''s Student" wrote:

I have some code that is passed a Collection of positive values. What is the
easiest way to average the values ?
--
Gary''s Student - gsnu201001