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


--
callagga
------------------------------------------------------------------------
callagga's Profile: http://www.excelforum.com/member.php...o&userid=27049
View this thread: http://www.excelforum.com/showthread...hreadid=465479

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

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

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


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=465479

Getting my VB custom functions to support usage within an Array Fo
 

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

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

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


--
callagga
------------------------------------------------------------------------
callagga's Profile: http://www.excelforum.com/member.php...o&userid=27049
View this thread: http://www.excelforum.com/showthread...hreadid=465479