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

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 myString to the sub_sub subroutine. We didn’t specify how we passed the variable, so it defaults to ByRef. If you run this, you should see goodbye in your Immediate window (press Ctrl + g to display the immediate window). The variable myString went to sub_sub, was modified, and came back with a different value. This is what VBA ByRef does. It’s relatively intuitive, but not always useful. Sometimes it can even be dangerous to the integrity of your application.

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.

I'll take a free VBA Developer Kit

At the end of this subroutine, myString should output hello. This happens because the instance of myString that is looked up by the Debug.Print line is the original variable. The string goodbye is actually assigned to a second, short-lived instance of myString, which is erased after sub_sub2 ends. In a nutshell, that’s what ByVal does.

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, your machine accesses memory address 0x0001234. If you change the value by writing x = 10 the value stored at 0x0001234 would change to 10.

However, just because a variable has the name x doesn’t mean it is always referring to the same memory location, even in the same program. You can have copies of variables, or you can have identically-named variables in different subs or functions.

At the hardware level, that means our example has an x variable stored at 0x0001234 and a variable with the same name, x, at another location, say 0x0006789. The two different x variables point to different memory locations.

In our 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 ByVal.

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 ByVal

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

Variable Scope

Using 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

The variable x is only visible inside show_local_scope. No other function or sub can see x, unless we pass it.

However, if we declare x at the top of the module, we will expand its scope to the entire module.

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 global_x because it is already available everywhere in the module.

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).

Local Precedent

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 global_x, which will have a value of 1 during its short lifetime. The local global_x takes precedent over the global variable of the same name within the module_scope12 subroutine.

Persistence

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.

Conclusion

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.