VBA functions iin array formulas

[Cord77]

Hi,

I have a question:

I can use for example the SIN-function for array formulas as in {=SIN(A1:A5)}. The original SIN-function takes one argument and returns a number.
Now, if I have a user defined function TEST, which takes one argument and returns a number, I cannot use this function in the same manner.
Is there a possibility to write the function, such that it can be used like (for example) the SIN-function?

Cord

[Dennis7849]

If you are asking, can I make a custom worksheet function? The answer is YES!

To do this
1. Enter the VB editor.
2. Add a module to the project.
3. See the example below.

Please Login or Register  to view this content.

Inside a cell, you would type

Please Login or Register  to view this content.

In this example, the function takes the value(single data type) that is in cell A1 and multiplies it by 3.14. The result(single data type) is returned to the cell that contains the custom worksheet function.

[shg]

Dennis,

While your function will certainly work as a UDF, all numbers on a worksheet are Doubles, so forcing a conversion to Single, computing the result as a Single, and then converting back to a Double on the worksheet is unnecessary overhead.

[Cord77]

Thank You, but Your answer does not answer my question. I want such a function to use as an array formula. In the manner as I could do it for example with SIN (or COS, or SQRT ...).

[shg]

Please Login or Register  to view this content.

[Cord77]

Thank You! So I have to manage the subtleties myself. I initially conjectured that Excel does this for me: calling my function for every element
in the range (or array) and manufacturing an result array of the same dimensions from all the return values. It would have been an elegant solution, wouldn't it?

[shg]

Yes, though you could write a generalized function to convert the input to a 2D array to simplify the code for any particular array-compatible UDF.

There's no need to quote my posts back to me -- I know what I wrote.

[Cord77]

Sorry, shg

it was not my intention to get on your nerves!

[shg]

My nerves are fine. It's just unnecessary clutter.