User defined function output
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: User defined function output

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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





  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you so very much it works like a charm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com