Getting my VB custom functions to support usage within an Array Formula - How do I ge

Hi,

Does anyone know how to get VB custom functions (I'm using Excel 2003) to support usage within an Array Formula?

For example I have the following function:
……………….
Function Test(cell As Range)
Test = 1
End Function
……………….

If I use this from within excel within an array formula it does not work however. For example:

{=SUM(Test(J74:J78))} <== THIS DOES NOT WORK (i.e. it returns 1 instead of 5)

A standard microsoft function does work of course however, e.g.:

{=SUM(LEN(J74:J78))} This works.

Any ideas?

Thanks

Hi callagga!

Function Test(cell As Range)
Test = 1
End Function


{=SUM(Test(J74:J78))} <== THIS DOES NOT WORK (i.e. it returns 1 instead of 5)

Your Test function will always, definitely, return 1 as you assigned 1 to it. If you want the function to count the number of cells in the range passed. You can write it like:

Function Test(TheCells as Range) as Long
Test = TheCells.Count
end Function

... "VB custom functions (I'm using Excel 2003) to support usage within an Array Formula"... What exactly are you looking for???

Originally Posted by callagga

Hi,

Does anyone know how to get VB custom functions (I'm using Excel 2003) to support usage within an Array Formula?

For example I have the following function:
……………….
Function Test(cell As Range)
Test = 1
End Function
……………….

If I use this from within excel within an array formula it does not work however. For example:

{=SUM(Test(J74:J78))} <== THIS DOES NOT WORK (i.e. it returns 1 instead of 5)

A standard microsoft function does work of course however, e.g.:

{=SUM(LEN(J74:J78))} This works.

Any ideas?

Thanks

Option Explicit
Public Function Dataset(target As Range)
Dim ar() As Long
Dim index As Long
ReDim ar(1 To target.Count)
For index = 1 To target.Cells.Count
ar(index) = target.Cells(index).Value
Next
Dataset = ar
End Function

on a sheet, I put 1,2,3,4 in D3:D6
in another cell
{=SUM(dataset(D3:D6)) }

the key in the function is that it should return an array of data



"callagga" wrote:

>
> Hi,
>
> Does anyone know how to get VB custom functions (I'm using Excel 2003)
> to support usage within an Array Formula?
>
> For example I have the following function:
> ……………….
> Function Test(cell As Range)
> Test = 1
> End Function
> ……………….
>
> If I use this from within excel within an array formula it does not
> work however. For example:
>
> {=SUM(Test(J74:J78))} <== THIS DOES NOT WORK (i.e. it returns 1
> instead of 5)
>
> A standard microsoft function does work of course however, e.g.:
>
> {=SUM(LEN(J74:J78))} This works.
>
> Any ideas?
>
> Thanks
>
>
> --
> callagga
> ------------------------------------------------------------------------
> callagga's Profile: http://www.excelforum.com/member.php...o&userid=27049
> View this thread: http://www.excelforum.com/showthread...hreadid=465479
>
>

Thanks, but this still doesn't seem to fully support the array formular concept. I will give an example below which works find for an existing VB function (e.g. Len) but not for our custom function.

The example is where you have a formular like this:

=SUM(IF($A129=$A$120:$A$123,GregsTest(B$120:B$123),0))

Where GregsTest is:

Function GregsTest(target As Range)
Dim ar() As Long
Dim index As Long
ReDim ar(1 To target.Count)

For index = 1 To target.Cells.Count
ar(index) = target.Cells(index).Value * 1
Next

GregsTest = ar
End Function

In the spreadsheet you have (the data and value areas):

Andrew 1
Andrew 1
John 123232
John 222

Andrew 246912
John 246912

As you can see the figures for both Andrew and John are the same, where as if you were calculating something with a VB formular they would be different and correct.

Do you understand what I mean?

Thanks again