Array Formulas for Personal Functions

[John McGraw]

Can the entry of array formulas in cells work with your own self-written VBA functions?

I wrote a function that recieves a Range,
but when I try to utilize it with a conditional array,
example:
{=MYFUNCTION(IF(A1:A100=10,B1:B100))}

I get #VALUE. Am I doing something wrong, or is this just not allowed with homemade functions? I did press CTRL+Shift+Enter BTW.

 

[Russell Hauf]

Post your function and what you hope to accomplish with it and I'm sure that someone will be able to help you.

-rh

 

[John McGraw]

On 2002-03-07 16:00, Russell Hauf wrote:
Post your function and what you hope to accomplish with it and I'm sure that someone will be able to help you.

-rh

I'm just expirementing, so my code is just
an average function:

----
Function MyAverage(DRange As Range) As Double

Dim R As Object
Dim Sum As Double
Dim Count As Integer

Count = 0: Sum = 0

For Each R In DRange
Sum = Sum + R.Value
Count = Count + 1
Next R

MyAverage = Sum / Count

End Function
----

Now, this works fine with if I just use it in normal formulas, but it returns "#VALUE!" if I try to use it in an array formula.

So, if column A = {1;1;2;2;1} and
B = {1;4;2;9;10}

I get the following results:

As Array Formulas
{=myaverage(IF(A1:A5=1,B1:B5))} = #VALUE!
{=AVERAGE(IF(A1:A5=1,B1:B5))} = 3
As Normal Formulas
=myaverage(B1:B5) = 5.2
=AVERAGE(B1:B5) = 5.2