Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    Can someone please explain (in laymans language) why you would add ByVal / ByRef in the arguments list!
    Eg: Sub myVar(ByVal N As Integer)
    What is ByVal and ByRef for? The VBA help does not really explain it too nicely!

    Tx
    Regards,
    Rudi

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    Thax Charlotte. It is a bit more clear now!

    >>> This does not happen if you pass the arguments ByRef.
    Is this last statement correct? Should it not be "This does not happen if you pass the arguments ByVal."

    If you DO NOT specify this is the default ByVal? (IE, If you say Sub Test(N as Integer) is this seen as ByVal by default?
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    > Should it not be "This does not happen if you pass the arguments ByVal."

    You're right Rudi... just a typo.

    > is the default ByVal?

    This varies (annoyingly) from language to language, but for VB/ VBA the default is ByRef. My personal <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> is that this is the way things should be by default. There's no point in passing a second copy of something unless you actually need a second copy.

    Alan

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

    Re: ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    <P ID="edit" class=small>(Edited by charlotte on 30-Jun-05 08:22. Corrected error in last word)</P>Think of it like this: when you pass an argument ByVal, you are passing the value of the argument and you can safely change that value in the current routine without affecting the object or variable in its original location because all changes are local only. When you pass a ByRef argument, you are really passing a pointer to the value's original location, so that if you change the value in that argument in the current routine, that change finds its way back to the original source. It allows you to retrieve multiple values from a function, for example, if you pass all the arguments ByRef and return a Boolean value to indicate the success of the routine. Then in the code that called the function, the variables you passed into the call will now have the value assigned to them in the function. This does not happen if you pass the arguments ByVal
    Charlotte

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

    Re: ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    In .Net, the default is ByVal. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> But it works a bit differently in .Net anyhow. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    Interesting. I'm yet to sample the delights of .NET to any extent, but I presume there's sound reasoning behind using this default. On the surface, it looks like a step backwards in terms of efficiency, in my .NET-ignorant opinion.

    Alan

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

    Re: ByVal / ByRef in Sub Procedure Arguments (Excel VBA)

    ByRef is used very sparingly in .Net because the ByRef and ByVal aren't quite the same thing as the VB/VBA version.
    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
  •