Which is faster, ByVal or ByRef?

This article is not intended to be an exhaustive evaluation of ByVal and ByRef, but merely a simple comparison of the relative speed of both keywords in identical procedures with various data types.

ByVal and ByRef

Both of these keywords indicate how parameters are passed to functions and other procedures. These statements are optional, however ByRef is the default.

What is ByVal?

ByVal means "by value", which means a copy of a variable will be passed to a given procedure.

What is ByRef?

ByRef means "by reference", which means a pointer to the variable's memory address is passed to a given procedure.

So, what's the difference?

In general, when a parameter is passed ByRef, it can be permanently changed by the procedure. That means if you change the value of a parameter passed ByRef, it will change in the calling function. There are examples all over the Internet of this behavior so I won't post any here.

If you only need the value of a variable for a procedure, use ByVal. If you need to change the value of a variable inside a procedure and have that updated value reflected in the calling procedure, use ByRef.

It is important to note that certain variable types cannot be modified, even if they are passed ByRef. For a list visit Argument Passing ByVal and ByRef.

MSDN also has a matrix for determining behavior of ByVal and ByRef. It shows that even reference variables can be changed (sort of) if passed ByVal. I don't plan to verify that here.

Here's what MSDN has to say about performance:

Although the passing mechanism can affect the performance of your code, the difference is usually insignificant. One exception to this is a value type passed ByVal. In this case, Visual Basic copies the entire data contents of the argument. Therefore, for a large value type such as a structure, it can be more efficient to pass it ByRef.

For reference types, only the pointer to the data is copied (four bytes on 32-bit platforms, eight bytes on 64-bit platforms). Therefore, you can pass arguments of type String or Object by value without harming performance.

This is for Visual Studio but I assume it applies to Visual Basic as well (where appropriate).

Conventional Wisdom

I'll admit I haven't been fully around the Visual Basic block, but I'm not aware of any existing conventions with regard to ByVal and ByRef. I know I should be using them explicitly, but as usual I get lazy and let VB handle it for me. My assumption based on the descriptions of ByVal and ByRef is that ByRef will be faster than ByVal, because ByVal needs to make a copy of the variable before passing it.

ByVal vs. ByRef

We are going to test out various data types to see how they perform.

I wrote three simple functions we are going to use to test out the relative speed of both keywords. We are going to reuse these as needed.

Function GetByVal(ByVal str As String) As String
  GetByVal = str
End Function

Function GetByRef(ByRef str As String) As String
  GetByRef = str
End Function

The following procedure calls each function N times and determines how long the entire operation takes. The results of my very non-scientific testing follows.

Sub TestByValByRefString()

  Dim i As Long
  Dim str1 As String
  Dim starttimeByVal As Single
  Dim endtimeByVal As Single
  Dim starttimeByRef As Single
  Dim endtimeByRef As Single
  Dim msg As String

  Const numberOfLoops As Long = 100000

  str1 = "The quick brown fox jumps over the lazy dog."

  ' test ByVal
  starttimeByVal = Timer
  For i = 1 To numberOfLoops
    str1 = GetByVal(str1)
  Next i
  endtimeByVal = Timer

  ' test ByRef
  starttimeByRef = Timer
  For i = 1 To numberOfLoops
    str1 = GetByRef(str1)
  Next i
  endtimeByRef = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using ByVal: " & Format(endtimeByVal - starttimeByVal, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using ByRef: " & Format(endtimeByRef - starttimeByRef, "#.###") & " seconds" & vbCrLf

  MsgBox msg

End Sub

String Variables

We'll start at 100,000 iterations. The results are very similar:

ByVal vs ByRef String at 100,000 iterations

At 1,000,000 iterations there is a slight but not noticeable difference:

ByVal vs ByRef String at 1,000,000 iterations

At 10,000,000 iterations we're starting to see separation:

ByVal vs ByRef String at 10,000,000 iterations

It is clear to me that ByVal is slower than ByRef for small string operations.

Long Variables

I replace "String" with "Long" in the procedures above and choose an appropriate Long value.

At 100,000 iterations, ByRef is marginally slower than ByVal:

ByVal vs ByRef Long at 100,000 iterations

1,000,000 iterations and we still see similar separation:

ByVal vs ByRef Long at 1,000,000 iterations

At 10,000,000 iterations ByRef is still slower, but not by much.

ByVal vs ByRef Long at 100,000,000 iterations

For Long variables it seems ByRef is slower, but it doesn't look statistically significant.

Object Variables

Replace "String" or "Long" with "Object" in the above procedures. For my object I chose RegExp.

First we start with 100,000 iterations. VBA barely breaks a sweat, although ByRef was clearly faster.

ByVal vs ByRef Object at 100,000 iterations

At 1,000,000 iterations, ByRef is still twice as fast as ByVal:

ByVal vs ByRef Object at 1,000,000 iterations

Finally at 10,000,000 iterations, it's clear that ByRef is still faster than ByVal:

ByVal vs ByRef Object at 100,000,000 iterations


ByRef was faster for Strings and Objects. ByVal was slightly faster for Longs. I really don't know if that is because of the data type or something else. I understand that ByVal was generally slower because VBA has to make a copy of the variable. However I'm hesitant to start using ByRef for Strings because of the danger of changing a String's value during a called function and then the calling procedure uses the updated value instead of the (expected) existing value. So I'll probably continue to use ByVal on variables I don't want to change the value of, and ByRef for variables where I want the calling procedure to use the (potentially) updated value.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »

Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 2 Comment(s) on Which is faster, ByVal or ByRef?:

  1. I've only used the byref for strings when I needed every little bit of speed that I could get. I had a bunch of data in 3 or 4 layered class using dictionaries that needed to be fetched all at once with about 10,000 to 100,000 pieces of data. So, let's say 100,000*(2+4)=600,000. So it seems that I could have left it with byval and been OK.

    Interesting comparison. Thanks for doing it.

  2. I never specify ByRef or ByVal, except when I'm passing a variable back from a function. This doesn't cause problems because I don't seem to do operations directly on passed string or numeric variables. Based on this post, I can continue this practice without worrying about efficiency, at least until I start processing 10,000+ worksheets at a time.

This article is closed to any future comments.
learn excel dashboards