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

[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

 

[Guest]

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 D36
in another cell
{=SUM(dataset(D36)) }

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