Can You use Array formulas with user written functions?

[John McGraw]

I would like to know if it is possible to get user written functions to work with array formulas.

As an example, here is a Average function I wrote to illustrate the question:

----
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 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


How can I a user written function to work in an array formula? Or is it even possible?

Thanks for any help

John

 

[Mark W.]

Take a look at the data types that you're passing as arguments to MyAverage...

{1;4;FALSE;FALSE;10}

...One of the causes of the #VALUE! error are incompatible data types (see the last entry in the Help Topic for "What does the error #VALUE! mean?"). The AVERAGE worksheet function ignores boolean values -- that's the "trick" behind {=AVERAGE(IF(A1:A5=1,B1:B5))}. How does your UDF handle boolean values? Sorry, I can't answer this question for you. I'm not a VBA guru.

BTW, {=AVERAGE(IF(A1:A5=1,B1:B5))} is 5 instead of 3. Perhaps, you were using a different data set.
This message was edited by Mark W. on 2002-03-08 15:11

 

[Juan Pablo González]

Yes, it's possible. The "trick" is that you need to declare your function as a Variant.

Then, handle the info in a temporary array. But, if you need to do an AVERAGEIF function, I would go with Excel's built in way. Or you could just create an UDF that would "appear" as a SUMIF, but, really work as told.

 

[John McGraw]

I tried the variant approach, and it still doesnt work. I simplified the code greatly to JUST focus on getting a function to work with an array formula:
----
Function MyAverage(DRange as Range)As Variant
MyAverage = DRange.Range("A1").Value
End Function
----

In this attempt, I am only trying to assign the function with the value of the 1st cell in the range. It works if I dont enter the function in an array formula, but as an array formula, I still get "#VALUE!"

I'm totally stumped. (I am pressing CTRL-SHIFT-ENTER)

 

[Russell Hauf]

I was able to get it to work using the following code. Hope this helps!<pre><font color='#000000'><font color='#000080'>Option</font><font color='#000080'>Explicit</font><font color='#000080'>Function</font> MyAverage(DRange<font color='#000080'>As</font> Variant)<font color='#000080'>As</font><font color='#000080'>Variant</font><font color='#000080'>Dim</font> R<font color='#000080'>As</font><font color='#000080'>Variant</font><font color='#000080'>Dim</font> Sum<font color='#000080'>As</font> Double<font color='#000080'>Dim</font> Count<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>Dim</font> intI<font color='#000080'>As</font><font color='#000080'>Integer</font><font color='#000080'>Dim</font> intJ<font color='#000080'>As</font><font color='#000080'>Integer</font>

For intI = 1 To UBound(DRange, 1)
For intJ = 1 To UBound(DRange, 2)<font color='#000080'>If</font> TypeName(DRange(intI, intJ))<> "Boolean"<font color='#000080'>Then</font>
Sum = Sum + DRange(intI, intJ)
Count = Count + 1<font color='#000080'>End</font><font color='#000080'>If</font>

Next intJ
Next intI


MyAverage = Sum / Count<font color='#000080'>End</font><font color='#000080'>Function</font></font></pre>
This message was edited by Russell Hauf on 2002-03-09 15:23

 

[Juan Pablo González]

On 2002-03-09 14:18, John McGraw wrote:
I tried the variant approach, and it still doesnt work. I simplified the code greatly to JUST focus on getting a function to work with an array formula:
----
Function MyAverage(DRange as Range)As Variant
MyAverage = DRange.Range("A1").Value
End Function
----

In this attempt, I am only trying to assign the function with the value of the 1st cell in the range. It works if I dont enter the function in an array formula, but as an array formula, I still get "#VALUE!"

I'm totally stumped. (I am pressing CTRL-SHIFT-ENTER)

This worked for me (With and Without CSE)

<pre>
Function MyAverage(DRange As Range) As Variant
MyAverage = DRange.Range("A1")
End Function


</pre>

But, i feel you need this:

<pre>
Function AverageIf(Range As Range, Criteria As String, Optional Average_Range As Range) As Variant
Dim T As String
If Not Evaluate("OR(" & Left(Criteria, 1) & "={""<"","">"",""=""})") Then _
Criteria = "=" & Criteria
If Average_Range Is Nothing Then
AverageIf = Evaluate("AVERAGE(IF(" & Range.Address & Criteria & "," & Range.Address & "))")
Else
AverageIf = Evaluate("AVERAGE(IF(" & Range.Address & Criteria & "," & Average_Range.Address & "))")
End If
End Function

</pre>

Which works EXACTLY like SUMIF, no need to array enter.

 

[John McGraw]

Thanks Russell!

I guess I am clueless as to how excel passes the array to the function. You are manipulating it as a 2 dimensional array, but to me it seems like it is 1 dimensional.

In fact, with experimenting with your code I noticed that I could omit the "intj" inner loop and get the same result. (I replaced "DRange(intI,intJ)" with "DRange(IntI,1)") This tells me that the second dimension of the array only includes 1 field. But thats virtually a 1-dimensional array, right?

As Mark W. mentioned, what is being passed to my function is {1;4;FALSE;FALSE;10}, or a 1d array. Why must it be manipulated as a 2d array?

Anyways, your help was great. Thanks again!

 

[John McGraw]

This worked for me (With and Without CSE)

<pre>
Function MyAverage(DRange As Range) As Variant
MyAverage = DRange.Range("A1")
End Function


</pre>

It did? I wonder why it didnt work for me?
I changed ".Range("A1").Value" to simply
".Range("A1")" as you have above, and I still get the "#VALUE!" error when entered with CSE.

Thanks for your help anyways!

John

 

[Dave Hawley]

Hi

You are asking for calculation slow down by using arrays (try Excels Database functions instead) but combining these with UDF's is not advised at all. You see an array formula must loop through all cells referenced and see if the criteria matches (its a bit like uisng a loop in VBA to find a value when the Find Method would get there 100+ times quicker). When you also combine a loop with an array formula it could be doing the same thing twice.

I would strongly advise using the DAVERAGE function for your needs. These functions are at least 100 times faster then array formulas, agian because they are not restricted to having to loop. If however you do use a UDF just add a condition to check, like:

Function MyAverage(DRangeComp As Range, vExclude, dAveRange As Range) As Double
Dim R As Range
Dim vVal
Dim iAveCount As Integer


For Each R In DRangeComp
If R<> vExclude Then
vVal = WorksheetFunction.Sum(dAveRange.Cells(R.Row, 1), vVal)
iAveCount = iAveCount + 1
End If
Next R

MyAverage = vVal / iAveCount
End Function

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
If it's Excel, then it's us!
This message was edited by Dave Hawley on 2002-03-09 20:26

 

[Russell Hauf]

On 2002-03-09 15:51, John McGraw wrote:
Thanks Russell!

I guess I am clueless as to how excel passes the array to the function. You are manipulating it as a 2 dimensional array, but to me it seems like it is 1 dimensional.

In fact, with experimenting with your code I noticed that I could omit the "intj" inner loop and get the same result. (I replaced "DRange(intI,intJ)" with "DRange(IntI,1)") This tells me that the second dimension of the array only includes 1 field. But thats virtually a 1-dimensional array, right?

As Mark W. mentioned, what is being passed to my function is {1;4;FALSE;FALSE;10}, or a 1d array. Why must it be manipulated as a 2d array?

Anyways, your help was great. Thanks again!

You can use it as a one-dimensional array for the range you selected, but in general, ranges you select in Excel are 2-dimensional. So if you select more than one column, you need the second dimension. Also, I suggest modifying the function to include only numeric types in the average (integer, long, single, double) - if you are going to continue along these lines, which you should not (this was just for you to try a UDF array function, correct?).

Regards,

Russell