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

 

[T-®ex]

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

 

[Patrick Molloy]

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

 

[callagga]

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