+ Reply to Thread
Results 1 to 4 of 4

return 2 or more variables from a function

  1. #1
    David Adamson
    Guest

    return 2 or more variables from a function

    I just can remember how to do the following.

    I need to return 2 variables from a function so that I can use them within a
    sub.
    In this case 2 column numbers so that I can use them to find something.

    Any help would be appreciated

    regards
    David

    -----------
    Sub Find_Columns
    Dim Col1, col2 as integer

    Col1 = YColumn(check)

    End sub


    Function YColumn(check As String)

    Select Case check

    Case "Vegetables"
    Col1 = 2
    Col2 =4

    Case "Mellons"
    Col1 =5
    Col2 = 6

    End Select

    End function



  2. #2
    Registered User
    Join Date
    07-03-2004
    Posts
    49

    Re :return 2 or more variables from a function

    David

    You cannot return more than one variable from a function.

    What you can do though is return a variant containing an array. This array can contain any number of values that you might want to retun.

    Remember, a variant can contain any data type and that includes an array of variants as well.

    Hope that helps!


    Best regards


    Deepak Agarwal

  3. #3
    David Adamson
    Guest

    Re: return 2 or more variables from a function

    thanks

    "agarwaldvk" <[email protected]> wrote
    in message news:[email protected]...
    >
    > David
    >
    > You cannot return more than one variable from a function.
    >
    > What you can do though is return a variant containing an array. This
    > array can contain any number of values that you might want to retun.
    >
    > Remember, a variant can contain any data type and that includes an
    > array of variants as well.
    >
    > Hope that helps!
    >
    >
    > Best regards
    >
    >
    > Deepak Agarwal
    >
    >
    > --
    > agarwaldvk
    > ------------------------------------------------------------------------
    > agarwaldvk's Profile:
    > http://www.excelforum.com/member.php...o&userid=11345
    > View this thread: http://www.excelforum.com/showthread...hreadid=486118
    >




  4. #4
    Jon Peltier
    Guest

    Re: return 2 or more variables from a function

    Another approach is to dimension your function as a Boolean, but pass a
    bunch of arguments by ref. These might be empty placeholders passed in, but
    the function gives them values. If the function does its calculations
    correctly, without error, it returns a value of True, so the calling sub
    knows it can rely on the arguments passed back. These can only be used by
    other VBA procedures, not in UDFs.

    An overly simple example might go like this:

    Sub DumbSub()
    Dim i1 As Integer, i2 As Integer
    Dim iMin As Integer, iMax As Integer
    Dim bTest As Boolean

    Set rng = ActiveSheet.Range("A1:A10")
    i1 = 1
    i2 = 5

    bTest = GetMinMax(i1, i2, iMin, iMax)

    If bTest Then
    MsgBox iMax & " > " & iMin
    Else
    MsgBox i1 & " = " & i2
    End If

    End Sub

    Function GetMinMax(iOne As Integer, iTwo As Integer, _
    iLow As Integer, iHigh As Integer) As Boolean

    If iOne = iTwo Then
    GetMinMax = False
    Exit Function
    End If

    If iOne > iTwo then
    iHigh = iOne
    iLow = iTwo
    Else
    iHigh = iTwo
    iLow = iOne
    End If

    GetMinMax = True

    End Function

    The calling sub passes in two values to find out which is greater. It checks
    for an error (i.e., the two values are equal). If there's an error, it
    displays one message, but if there's no error, it displays a different
    message.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    "David Adamson" <[email protected]> wrote in message
    news:[email protected]...
    > thanks
    >
    > "agarwaldvk" <[email protected]>
    > wrote in message
    > news:[email protected]...
    >>
    >> David
    >>
    >> You cannot return more than one variable from a function.
    >>
    >> What you can do though is return a variant containing an array. This
    >> array can contain any number of values that you might want to retun.
    >>
    >> Remember, a variant can contain any data type and that includes an
    >> array of variants as well.
    >>
    >> Hope that helps!
    >>
    >>
    >> Best regards
    >>
    >>
    >> Deepak Agarwal
    >>
    >>
    >> --
    >> agarwaldvk
    >> ------------------------------------------------------------------------
    >> agarwaldvk's Profile:
    >> http://www.excelforum.com/member.php...o&userid=11345
    >> View this thread:
    >> http://www.excelforum.com/showthread...hreadid=486118
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1