Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Function arguements (Acc2000 on xp)

    About function arguements, I understand how you can pass variables to a function by putting them in brackets, but is there a way to get a value back that has two bits of information in it?

    Call function with no arguements, recieve answer with 2 values in it
    "Heading for the deep end"

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Function arguements (Acc2000 on xp)

    Depends on where you are using this function. In a query, the answer is no; you can only use the value returned by the function itself.

    If this function is being used in code, then you can modify the function to return the values in the parameters. For example, call the function like this:

    myfunction A, B

    If A & B are passed to the function ByRef, then however they are changed by the function changes them in the calling procedure.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Function arguements (Acc2000 on xp)

    Added later:
    See the replies by Mark and Charlotte below. Apparently, the coffee hadn't kicked in yet when I wrote this reply.


    One option is to define public variables, and set these. You actually don't need a function for this, a procedure will do.

    Public ValueOne As Variant, ValueTwo As Variant

    Sub TestProc()
    ValueOne = "Alan"
    ValueTwo = 37
    End Sub

    Another option is to make the function return a custom data type consisting of two values:

    Public Type TwoValues
    ValueOne As Variant
    ValueTwo As Variant
    End Type

    Function TestFunc() As TwoValues
    TestFunc.ValueOne = "Alan"
    TestFunc.ValueTwo = 37
    End Function

    Sub TestIt()
    Dim TempVar As TwoValues
    TempVar = TestFunc
    Debug.Print TempVar.ValueOne, TempVar.ValueTwo
    End Sub

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Function arguements (Acc2000 on xp)

    As noted in other replies, passing variable arguments by reference (ByRef) is the simplest way to "return" multiple values from a single sub or function. Simple example:

    Public Sub TestPassByRef()

    Dim A As Integer, B As Integer
    Dim C As Integer, D As Integer

    ChangeValueByRef A, B, C, D

    Debug.Print "A", A
    Debug.Print "B", B
    Debug.Print "C", C
    Debug.Print "D", D

    End Sub

    Public Sub ChangeValueByRef(ByRef A As Integer, ByRef B As Integer, _
    ByRef C As Integer, ByRef D As Integer)
    A = 2
    B = 4
    C = 10
    D = 16
    End Sub

    When you run TestPassByRef sub it'll be seen the variables have been "populated" by the second sub. You can also declare a function that returns an array. Example:

    Public Function GetArray() As Long()

    Dim tmp() As Long
    Dim n As Integer

    ReDim tmp(4)
    For n = 0 To 4
    tmp(n) = 2 * n
    Next n

    GetArray = tmp
    Erase tmp
    End Function

    Note the function declaration syntax - open & closed parentheses following data type. I don't use this syntax much, I find it more useful to pass an array by reference. Example:

    Public Sub TestGetArrayByRef()

    Dim tmp() As Long
    Dim n As Integer

    GetArrayByRef tmp

    For n = 0 To UBound(tmp)
    Debug.Print n, tmp(n)
    Next n
    Erase tmp

    End Sub

    Public Sub GetArrayByRef(ByRef tmp() As Long)
    Dim n As Integer
    ReDim tmp(4)

    For n = 0 To 4
    tmp(n) = 2 * n
    Next n
    End Sub

    If you run TestGetArrayByRef you will see that the tmp array variable was populated by GetArrayByRef sub. This can be useful, for example, if you need to sort an array. You pass a populated (but unsorted) array by reference to a sub that performs a sorting routine; if successful, the array in calling procedure will then be sorted. With array variables in particular the data types must match; in some cases the array variable may need to be declared as Variant. Note that in VB/VBA, by default, parameter variables are passed by reference, as opposed to by value (ByVal), so it's not necessary to specify ByRef in function or sub declaration. I prefer to include the "ByRef" keyword anyway; for example, in VB.NET, passing arguments by value is the default.

    HTH

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function arguements (Acc2000 on xp)

    You can use the arguments themselves to pass information back. This is essentially the way API calls work. When the arguments are passed ByRef instead of ByVal, the argument is actually a pointer back to the declared variable. That means that when the called routine modifies the value in the argument or populates an argument passed in empty, the calling routine can then proceed to use the (now populated) variable rather than just using the routine's returned value.
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Function arguements (Acc2000 on xp)

    Thanks to all for this input
    "Heading for the deep end"

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Function arguements (Acc2000 on xp)

    I have done this by creating User-defined data types, then getting the function to return one of them.

    This is from help

    User-Defined Data Type
    Any data type you define using the Type statement. User-defined data types can contain one or more elements of a data type, an array, or a previously defined user-defined type. For example:

    Type MyType
    MyName As String ' String variable stores a name.
    MyBirthDate As Date ' Date variable stores a birthdate.
    MySex As string
    End Type
    ************
    If a function returns a MyType value, it has the three bits of info in it, that you can then pull out.

    Public function fnMyfunction(clientID as long) as Mytype

    etc
    end function

    ClientSex = fnMyfunction(5).MySex
    Regards
    John



  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function arguements (Acc2000 on xp)

    That is just a means of making the arguments less confusing. You're still passing ByRef pointers to the elements of the custom type instanced in the object you pass in. I've used that approach on very complex stuff like dealing with latitude and longitude information mapped to points on the display and with FileOpen calls to the API.
    Charlotte

Posting Permissions

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