Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    returning values from functions (XL97 sr2 on Win 2000 sp2)

    Is it possible to get a user defined VBA function to return 2 different values?

    For example if I entered =myFunction(arg_list) as an array formula over two cells could the code return two different numbers, one in each cell?

    stuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: returning values from functions (XL97 sr2 on Win 2000 sp2)

    Yes, by making the return value an array.

    For example:

    Function myFunction(ParamArray arg_list())
    myFunction = Array(WorksheetFunction.Sum(arg_list), WorksheetFunction.Average(arg_list))
    End Function

    Of course, you must use this in an array formula (confirm with Ctrl+Shift+Enter).

    For instance:
    =myFunction(1, 2, 3, 4, 5) entered in two cells next to each other as an array formula will return 15 in the first cell and 3 in the second cell.
    You can also use
    =myFunction(A1, B3, C5, D99)

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning values from functions (XL97 sr2 on Win 2000 sp2)

    I think see what you mean Hans, so what is wrong with my implementation?

    The worksheet contains =myFunction(a range of cells, a count) entered as an array formula over two adjacent cells, say B1 and B2.

    Then the code is:

    myFunction(myRange,myCell)
    Dim aryReturnValues(2) ' NB I've got Option Base = 1 set

    ...code to derive two values from the data in myRange and the count in myCell...
    ...the values end up in two variables, say A and B...

    myFunction = aryReturnValues(A , [img]/forums/images/smilies/cool.gif[/img]
    End Function

    I know the code in the middle works correctly, I can get the two values out one at a time by using the code twice in two separate functions that only differ in their names and the last line where the varible is assigned to the function name.

    stuck

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: returning values from functions (XL97 sr2 on Win 2000 sp2)

    Try

    Function myFunction(myRange,myCell)
    Dim aryReturnValues(2) ' NB I've got Option Base = 1 set

    ...code to derive two values from the data in myRange and the count in myCell...
    ...the values end up in two variables, say A and B...

    aryReturnValues(1) = A
    aryReturnValues(2) = B
    myFunction = aryReturnValues
    End Function

    or

    Function myFunction(myRange,myCell)

    ...code to derive two values from the data in myRange and the count in myCell...
    ...the values end up in two variables, say A and B...

    myFunction = Array(A , [img]/forums/images/smilies/cool.gif[/img]
    End Function

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning values from functions (XL97 sr2 on Win 2000 sp2)

    The function produces a horizontal array, so if you select B1 and C1 you should be OK.

    Otherwise select B1 and B2 and use

    =TRANSPOSE(myFUNCTION(arg1,arg2))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: returning values from functions (XL97 sr2 on Win 2000 sp2)

    YESSSS!

    Both of Hans' suggestions work now that Jan has got the horizontals and veritcals sorted out. What a team!

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    (un) stuck

Posting Permissions

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