When you first started learning to program in VBA, your macros were probably entirely written in a single subroutine. As a matter of fact, you probably didn’t make use of functions at all. If you’re new to VBA, you might even ask yourself there’s a difference between subroutines and functions?. As your VBA applications grow in complexity, though, you’ll realize you are likely duplicating code. This is code bloat, and code aesthetics (and your sanity) require the reduction of this bloat.
You can break your code into blocks, termed subroutines or functions. These blocks can be called from other blocks. The process of restructuring code is called refactoring. Even mature code bases are refactored sometimes.
Generally subs or functions are connected to the main part of the program by variables that are passed to them.
Your sub might look like
sub mySub(email, msg) 'email and msg are passed in from the main subroutine 'code for parsing and sending messages end sub
but this is not the whole story. There are some implicit defaults present that may affect your program. A better initial line might be
Sub mySub(ByVal email, ByRef msg)
This tutorial will explore the differences in VBA ByVal and ByRef arguments, so you’ll know when to use them properly.
- Passing Variables
- What’s the point of using copies?
- The Scope of Variables
- Caveats Regarding Wide Scoped Variables
When you start to use multiple subs and functions, you’ll need to send variables to them. For example, let’s say you have a variable
x in your original function. You want to ship it off to another subroutine, play around with it, and, when you finish, you want
x to come back modified.
To see if this is possible, place this little VBA code block in your VBA editor:
Sub pass_variables_with_defaults() Dim myString As String myString = "hello" sub_sub myString Debug.Print myString End Sub Sub sub_sub(myString) myString = "goodbye" End Sub
In this example we passed the variable
ByRef. If you run this, you should see goodbye in your Immediate window (press Ctrl + g to display the immediate window). The variable
Passing Copies of Variables
Often you don’t want to modify your original data, but you want to work with that data in another function or sub. You can shield your original variable by making a copy of it, then passing this copy to the new function/sub instead of passing the original variable.
To do this, simply add
ByVal in the declaration line of the called sub, like this:
Sub pass_variables_byval() Dim myString As String myString = "hello" sub_sub2 myString Debug.Print myString End Sub Sub sub_sub2(ByVal myString) myString = "goodbye" End Sub
Make powerful macros with our free VBA Developer Kit Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.
At the end of this subroutine,
Debug.Print line is the original variable. The string goodbye is actually assigned to a second, short-lived instance of
Storing Variables in Memory
All computer programs must store their variables somewhere in working memory (RAM). There is a physical place on a RAM chip that stores the value of that variable, which is referenced by a memory location ID (memory address). When you call that variable for use in calculations, the machine looks up the memory location and accesses that physical location to retrieve the value. If you write a new value to a variable, say by writing
x = 2, the value at that memory location is changed.
When you initialize a variable using
Dim x as Integer a memory location is set aside for a variable of datatype Integer, and its address is stored in a lookup table. Let’s say the address is 0x0001234. Every time you reference
x = 10 the value stored at 0x0001234 would change to 10.
However, just because a variable has the name
At the hardware level, that means our example has an
ByVal example from the previous section, VBA now has two variables with the name
myString, but they refer to completely different memory locations. When the program returns to the original subroutine, the VBA program looks to the original memory address (0x0001234), not the copy’s address.
When Copies Aren't Copies
You may be wondering what’s the point of talking about memory addresses. It can be a little complex, after all. I ran through this thought exercise since it can be helpful in showing why VBA doesn’t always make copies of “variables” when you pass
Objects - structures like a Dictionary, Collection, Object, Workbook, and so on - take up a lot more space than simple variables, like an Integer, String, or Boolean. Partially for this reason, VBA won’t copy Objects. Even if you pass an Object
ByVal, your original Object isn’t shielded like a regular variable.
For objects, the
ByVal qualifier simply copies the pointer, or the path to the memory location. It does not copy the whole object in memory. That means whenever you access an object, even if you’ve passed it
ByVal, you are really accessing the original, underlying structure in RAM. If you change it, the changes will persist.
This can be intuitively understood with the
Worksheet object. If you pass a worksheet, say
Sheet("Users") ByVal, you won’t duplicate the whole sheet! Any changes you make to the worksheet object will change the actual worksheet. Pass a worksheet object ByVal, then take a look at the worksheet to see for your self.
Don’t fall into the trap of not realizing you are passing a pointer instead of a variable. This can be extremely frustrating for inexperienced users. It’s a subtle point but has wide-reaching consequences - especially if you thought your original data was protected.
What's the Point of Using
If you’re passing data, you often want to modify it. Other times you need to pass and work with your data, but retain the original form, too.
ByVal perfectly serves this situation.
Let’s say you are working with an API, and you have a long response string. You want to extract the ticker symbol using string manipulations, but you will need the full response later - perhaps for other string manipulations. You could send a copy of the string to the parsing subroutine, which would preserve all the original data in the original variable:
Sub show_user_ticker() Dim jResponse As String 'do stuff to get the JSON string from the API 'the string is hardcoded here for illustration jResponse = "market: nasdaq, order_type: gtc, sym: lmno, price: 40.93, volume(000s): 1039" get_ticker jResponse 'do other things with the original, long jResponse string End Sub Sub get_ticker(ByVal jString) If InStr(jString, "sym: ") > 0 Then jString = Right(jString, Len(jString) - InStr(jString, "sym: ") + 1) jString = Left(jString, 9) MsgBox (jString) End If End Sub
ByVal makes copies of the data, and the copy will only be accessible from within the called sub, while the original data can only be accessed and modified from within the calling sub. Conversely,
ByRef allows you to access and modify the original data from both the calling and the called sub.
Sometimes you might need to modify your original data in multiple subroutines. You could pass your variable
ByRef to every one, but there is another solution that utilizes variable scope.
Most of the time we use local variables, which can only be seen from inside the function or subroutine in which they are declared. We declare them by writing
Dim in front of them inside the sub. Other subs can only see them if we pass them ByRef.
Sub show_local_scope() Dim x As Integer 'do stuff with x End Sub
However, if we declare
Dim global_x As Integer Sub module_scope0() global_x = 5 Call module_scope1 Debug.Print global_x End Sub Sub module_scope1() global_x = global_x + 1 End Sub
If you run this, you should see an output of
6. You don’t have to pass
If you need the variable to be visible in all modules, you can use
Public in place of
Dim when declaring your variables at the top. If you want to know more, check out our full VBA Variable Scope and Lifetime tutorial.
Caveats Regarding Wide-Scope Variables
At first glance, module-level or public scope might seem awesome. You can just declare all your variables at the top and never have to pass them again. However, this is a dangerous practice for two main reasons: precedent and persistence (lifetime).
Locally-declared variables take precedent over global variables if they have identical names. If we modify our code above like so:
Dim global_x As Integer Sub module_scope02() global_x = 5 Call module_scope12 Debug.Print global_x End Sub Sub module_scope12() Dim global_x as Integer 'this line initializes global_x at another memory location, and within this sub, global_x starts out at zero and ends at 1 global_x = global_x + 1 End Sub
we will end up with 5 at the end plus an intermediate variable named
Module- and publicly-scoped variables live for as long as the program is running and are only erased once the program ceases or the programmer explicitly garbage collects or reinitializes them. If you habitually use these wide-scope variables, you may forget that the variable continues to live. When you try an operation in a new sub on a variable you thought was blank but still contained a value from a previous macro execution, it can mess up your entire application. In short code, this may not be problematic, but as your code base expands, it can become very difficult to track where and when global variables are changing.
Usually passing parameters with the default, implied
ByRef is good enough. But if you want to add a layer of protection to the original data,
ByVal is the easiest way to protect transferred data. You won’t have to worry about accidentally overwriting something or modifying your original data and accidentally breaking your code later.
However, remember that objects are not copied like variables.
Finally, global variables are useful, too, but one should be vigilant about identical names, conflicting scope, and persistence during the course of the program.
Before you go, I want to remind you that if you’re serious about writing macros, you should subscribe for more VBA tips. Simply fill out the form below and we’ll share our best time-saving VBA tips.