What do ByRef and ByVal mean to you?

I’ve been interviewing recently and have been subjecting the candidates to a rather nice VBA question. I thought the regulars here might get a kick out of thinking up the ‘perfect’ answer for one or more of the scenarios. It involves a Sub Main calling a Function which takes a single parameter:

Scenario a): the parameter is a Long integer and is passed using the ByRef keyword. The Function changes the parameter variable’s value. What is the purpose of the ByRef keyword in this context?

Scenario b): the parameter is a String and is passed using the ByRef keyword. The Function makes no change to the parameter variable’s value. What is the purpose of the ByRef keyword in this context?

Scenario c): the parameter is an ADO Recordset object and is passed using the ByVal keyword. The Function makes changes to the Recordset’s Filter and Sort properties. What is the purpose of the ByVal keyword in this context?

What do you think?

21 Comments

  1. Jon Peltier says:

    Are you sure about scenario b? Passing the string ByRef results in a changed string:

    ‘–
    Function ChangeStringByRef(ByRef sTest As String) As Boolean
    Dim sNew As String
    Dim iChar As Long
    sNew = “”
    For iChar = Len(sTest) To 1 Step -1
    sNew = sNew & Mid(sTest, iChar, 1)
    Next
    sTest = sNew
    ChangeStringByRef = True
    End Function
    ‘–
    Function ChangeStringByVal(ByVal sTest As String) As Boolean
    Dim sNew As String
    Dim iChar As Long
    sNew = “”
    For iChar = Len(sTest) To 1 Step -1
    sNew = sNew & Mid(sTest, iChar, 1)
    Next
    sTest = sNew
    ChangeStringByVal = True
    End Function
    ‘–
    Sub TestChangeString()
    Dim s1 As String, s2 As String, s3 As String
    Dim bTest As Boolean

    s1 = “abcde”
    s2 = s1
    s3 = s1

    bTest = ChangeStringByRef(s2)
    Debug.Print “ByRef: ” & s1 & ” – ” & s2

    bTest = ChangeStringByVal(s3)
    Debug.Print “ByVal: ” & s1 & ” – ” & s3

    End Sub
    ‘–

    The result in the Immediate window is:

    ByRef: abcde – edcba
    ByVal: abcde – abcde

    - Jon

  2. Jon Peltier says:

    pre tags don’t work, eh? Pity.

  3. doco says:

    “…parameter variable’s value…”

    Actually, if you want to be really technical: the function’s ARGUMENT takes a PARAMETER… It is the ARGUMENT that is either ByVal or ByRef. :-)

  4. Billkamm says:

    A) since it is a function I would assume that it changes an parameter in place and returns some status. Several win32 API calls work in this way as well.

    B) the purpose of passing as variable ByRef and not changing it value is most likely to increase speed.

    C) When you pass an object “ByVal” you aren’t passing the object By Value you are passing the pointer to the object ByVal. You can however make a copy of the object using the New keyword and the undocumented function ObjPtr()

  5. doco says:

    In reality, for me, I have really not seen or understood the purpose of ByRef. I have seen in books and online some cutesy things done with it that appeared to me as being the long way around the mountain; and made the code a bit confusing. After all, the purpose of a function is to return something to you, strongly suggesting the possiblility of change – why muddy the water with ByRef?

    Of course I am one of those remedial level math guys that could never FULLY grasp the subtle differences between Distributive, Associative and Commutative either :eek: :-(

  6. doco says:

    Besides…

    If you ever plan on going to .NET or converting your VB code to .NET – ByRef is a no no. Or so I have read…

  7. Helen says:

    I think the questions are rather unclear at the moment… “The purpose of the ByRef keyword” is always the same: to pass the variable by reference.

    I guess you mean “What is the purpose of using the ByRef keyword in this case” or even “What is the rationale for using the ByRef keyword in this case”?

  8. Jamie Collins says:

    Jon Peltier: “Are you sure about scenario b? Passing the string ByRef results in a changed string”

    Yes, I am sure I said that the Function makes no change to the variable’s value :) It only results in a changed string if you, er, change the string.

    Billkamm guessed where I was coming from here. Because String is a pointer type, ByVal results in a ‘deep’ copy being made i.e. the entire contents, which could be considerable, are copied to a new location in memory and a new pointer created. Improved memory management and performance was the rationale here.

  9. Jamie Collins says:

    Helen: “I guess you mean … ‘What is the rationale for using the ByRef keyword in this case’?”

    Yes, I much prefer your wording. I’ll let you in on a secret: I seem to have hit the ‘Publish’ when I merely intended ‘Save Draft’. Oh yeah, and me writing style is plenty bad, also.

  10. Jamie Collins says:

    Doco: “the function’s ARGUMENT takes a PARAMETER”

    I can never remember which way round those go, so I default to using ‘parameter’ and hope no one notices ;-)

  11. Jamie Collins says:

    Billkamm: “You can however make a copy of the object using the New keyword and the undocumented function ObjPtr()”

    I know how to use ObjPtr() to get a ‘dumb’ pointer to the object then use CopyMemory API to create a new ‘live’ pointer and increment the reference count. Is it as simple to create a copy of the COM object itself?

  12. Jon Peltier says:

    “I said that the Function makes no change to the variable’s value “

    Oh, you meant the function by design makes no change. I thought you implied the function changed it but somehow the change wasn’t reflected in the passed variable’s value.

    Duh.

  13. jkpieterse says:

    I guess when the function needs to alter the content of more than just one (object) variable, ByRef comes in handy.

    If e.g. the function is set up to be a boolean function that denotes whether any errors have occurred inside the function, one needs to use byref arguments to get at the actual results of the function.

  14. Billkamm says:

    In response to #11:

    This article can probably explain it better than I could:

    http://www.windowsdevcenter.com/pub/a/oreilly/windows/ron/objects.html?page=last

  15. Joseph says:

    I’d like to know what practical impact the answers to these questions have.

    Are we looking at issues that have real life meaning here, or are we navel gazing?

  16. Jamie Collins says:

    Joseph: “Are we looking at issues that have real life meaning here, or are we navel gazing?”

    Good question. Remember the context is a job interview.

    I’d expect everyone to know the answer to scenario a. If they gave me the answer I was looking after for scenario b then I’d know they would have an appreciating of performance issues. If they gave a sensible answer for scenario b then they’d have justified putting ‘COM’ or ‘ActiveX’ on their cv/resume.

    If they were confused by the latter two scenarios then the chances are they haven’t much experience of component based or object oriented programming in VBA. Mentioning the concept of ‘trusted code’ or the implications of marshalling (I didn’t say the Function was in-process) would earn bonus points.

    You may find it interesting that the best answer I got to scenario b went something like, ‘ I think the answer is [incorrect assumption here] but I’m really not sure. You’ve got me interested though. What is the correct answer?’ This candidate starts Monday. How real life is that?

    Jamie.

    –

  17. Jamie Collins says:

    In response to #14:

    I think you *do* need to explain because I can see nothing in the article which creates a copy of an object instance. It would be a very handy thing to create a copy instance (rather than a copy pointer to the same instance) but I’ve always considered it out of reach because of the fact a single COM object can be stored in multiple memory locations and hence have different references for each (think mulitple interfaces).
    Jamie.

    –

  18. greg says:

    I can tell there is more book smart than real life preactice in come of these responses. A Value passed BYREF will chage the vlaue in the calling method, as most of us know. What I have not seen anyone pick up on is the 1001 uses for passing values byref in a function. How about the good old introduction to VB.NET book. Most of realize that there are often times an integer of 0 may be returned back from a function without it being in error. By (for example) returning a true or false back from the method or function we can say that the argument we passed into our parameter was infact successfuly processed. Of course this is just one simplistic example.

  19. [...] 2009 VBA Code 0 Comments Tags: Software, VBA I recently came across a discussion about ByRef and ByVal, which I found a bit confusing. So I thought I’d summarise my (possibly mis-) understanding [...]

  20. karthick says:

    Thanks a lot for your short and excellent functions.

    Appreciate your publishment…thanks once again

    :D

    thanks
    Karthick

  21. Jim Thomlinson says:

    The fundamental difference between byref and byval is that by ref passes the variable into the function, where as byval makes a copy of the variable and passes that in. Knowing that question 1 is relatively obvious. Question 2 is a bit tougher. The issue is one of performance. Copying most variables is easy as the size is known, but a string is actually a null terminated array of characters so the copy requires more overhead. Question 3 is where it gets difficult because you need to understand that references to objects are handled by pointers and that the copy only copies the pointer and not the entire object. From that stand point I do not see any value to passing ByVal in #3. Let me know if I am off the mark here. Always good to learn.

    The only other question I would have asked is if you do not specify ByRef or ByVal then what is the default? Interesting how many people have no idea.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: