Averageing a collection

[ksnapp]

hello

i need the average of a collection

val is the name of a collection, here is the line of code:

a = application.worksheetfunction.average(val)


I tried this and it get an run time error 1004 unable to get th
average property of the worksheet function.

Is there something wrong with my VBA or is it that I can't use
collection like this?

Is there a way to average a collection? I have tried to averag
numbers in the argument list and it works just fine

 

[Guest]

if your talking about a collection as used by VBA, like Workbooks collection, then a collection is an Object not a numeric value, and cannot be used in that function

----- ksnapp > wrote: ----

hell

i need the average of a collectio

val is the name of a collection, here is the line of code

a = application.worksheetfunction.average(val


I tried this and it get an run time error 1004 unable to get th
average property of the worksheet function

Is there something wrong with my VBA or is it that I can't use
collection like this

Is there a way to average a collection? I have tried to averag
numbers in the argument list and it works just fine

 

[Wouter]

Hi ksnapp,

The worksheet function axpects a RANGE in stead of a COLLECTION.

A range can be several non connected cells, or a names range.


good luck,

Wouter

 

[Leo Heuser]

Hello ksnapp

Here's some example code:

Sub AverageOfCollection()
'Leo Heuser, 16 Apr. 2004
Dim CollSum As Double
Dim Counter As Long
Dim TestArray As Variant
Dim TestColl As New Collection

TestArray = Array(1, 3, 4, 6, 7)

On Error Resume Next

For Counter = LBound(TestArray) To UBound(TestArray)
TestColl.Add Item:=TestArray(Counter)
Next Counter

For Counter = 1 To TestColl.Count
CollSum = CollSum + TestColl(Counter)
Next Counter

MsgBox "Average is: " & CollSum / TestColl.Count

End Sub