User defined function output

[Casey]

Hello,
I have a question about the output of a user defined function. I have generated a function which solves linear equations, but my result is 3 variables. how do I put these out into the excel spreadsheet? I think this is a simple problem but maybe not. my variables ar a, b, c and my function is linearEQ. Right now I can show one at a time, ie linearEQ = a at the end of the the function, but I want to displaythem all in a column or row starting where the function is.
thanks for any help you can give,
Casey

 

[Mark W.]

If your UDF returns 3 values then you'll need to enter it as an array function occupying 3 cells (either vertical or horizontal depending on your UDF's design). Select 3 adjacent cells, type your UDF [ e.g., =MyFunc() ], and press Control+Shift+Enter. For more on array formulas see the Excel help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-02-20 10:53

 

[Casey]

Thanks for your help.
Is this the only way to export the results or can I do something like show all three values in one cell with a space in between them?
thanks
Casey

 

[Casey]

Hey, heres my code if that helps at all.
at the end I can only use one variable "c"
Casey

Function LinearEQ(InRange As Range) As Variant

Dim x1, x2, x3, y1, y2, y3 As Long
Dim a, a1, a2, a3, b, b1, b2, b3, c, c1, c2, c3 As Long

x1 = InRange(1, 2)
x2 = InRange(2, 2)
x3 = InRange(3, 2)
y1 = InRange(1, 1)
y2 = InRange(2, 1)
y3 = InRange(3, 1)

a1 = (x1) ^ 2
a2 = (x2) ^ 2
a3 = (x3) ^ 2

b1 = x1
b2 = x2
b3 = x3

'first round of elimination
c1 = 1
c2 = 0
c3 = 0

b2 = b2 - b1
b3 = b3 - b1

a2 = a2 - a1
a3 = a3 - a1

y2 = y2 - y1
y3 = y3 - y1

'second round of elimination
a3 = a3 - (a2 * b3 / b2)

y3 = y3 - (y2 * b3 / b2)

b3 = 0

'backward subtitution
a = y3 / a3

b = (y2 - (a * a2)) / b2

c = (y1 - (a * a1) - (b * b1)) / c1



LinearEQ = c



End Function

 

[Juan Pablo González]

You can, I can think of two ways.

One, add another parameter (An integer) that takes one of three values, 1, 2 or 3, and depending on the value of this, return the corresponding value.

Two, return the three values in one BUT, they will return as a string.

Result = a & " " & b & " " & c

 

[Casey]

Thank you so very much it works like a charm