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

--
Message posted from http://www.ExcelForum.com

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


--
Message posted from http://www.ExcelForum.com

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



ksnapp wrote in message ...
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 the
average property of the worksheet function.

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

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


---
Message posted from http://www.ExcelForum.com/

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

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"ksnapp " skrev i en meddelelse
...
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 the
average property of the worksheet function.

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

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


---
Message posted from http://www.ExcelForum.com/