VBA ByVal and ByRef — Passing Variables
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
but this is not the whole story. There are some implicit defaults present that may affect your program. A better initial line might be
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:
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:
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.
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:
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.
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.
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:
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.
Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we’ll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.
Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.
This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog. Visit him on LinkedIn and his personal page.
VBA ByVal vs ByRef
When creating your function or subroutine, you will regularly need to pass arguments to it. You can choose to pass the arguments to the procedure either by value or by reference, with the keywords being ByVal and ByRef , respectively. What do these mean? What’s the difference between them? How do they work? And when should I use one over the other?
What is ByVal and ByRef?
When you create a procedure, you have to decide if the procedure will be accepting arguments or not. If your procedure does accept arguments, you can decide how it accepts those arguments.
In these procedures, we are using ByVal and ByRef explicitly. However, if you don’t specify which you want to use, like here:
Then you’re using ByRef by default.
So what do these mean, exactly?
A Note About Data Types
Before we dive into this, it’s important to understand that there are value types and reference types when creating your variables. The basic idea is that simple data types like numbers, strings, and booleans are considered value types, where complex objects like Ranges, Worksheets, Charts, etc. are called reference types. You can think of a complex object as something that can have value types and reference types inside of it. For example, the Range data type is an object that has information about cell values, font type, color, border lines, other Range objects, etc.
How do you know if you’re dealing with a value type or a reference type? One simple way to tell is how you assign the variable for that type.
For example, look how we assign the Integer variable and the Range variable:
When you use the Set keyword to assign a variable, you’re dealing with a reference type.
Passing Arguments ByVal and ByRef with Different Data Types
There are subtle differences between using value types and reference types when passing data to a procedure using ByVal and ByRef . The table below describes the basics of what happens in each scenario.
| Pass Using ByVal | Pass Using ByRef | |
|---|---|---|
| Value Type | Copies the underlying data into the called procedure. You cannot modify the original data in the calling procedure. | Gives the called procedure access to the original data. This means when you change a value in the called procedure, it will also be changed in the calling procedure. |
| Reference Type | Copies the reference to the called procedure. You can modify the contents of the object and those changes will be reflected in the called procedure. | Gives the procedure access to the original variable and all of its contents. This allows you to modify the the contents of the object and be able to reassign the variable which will be reflected in the calling procedure. |
Confusing? You bet it is! Don’t worry, with some examples, we’ll clear things up.
Quick note about “calling procedure” versus “called procedure” — when you call a procedure from another procedure, the procedure that you’re currently in is considered the called procedure and the procedure that invoked it is called the calling procedure.
Here’s an image to help explain. When we’re inside the DoStuff procedure (noted by the yellow highlight), the DoStuff procedure is considered the called procedure and the Main procedure is the calling procedure.
With all of this in mind, let’s dive into some examples.
Does this article help you? If so, please consider supporting me with a coffee ☕️
Passing Arguments Using ByVal
Using ByVal with Value Types
When passing a value type to a procedure using the ByVal keyword, you are essentially saying “hey, I would like to have a copy of the value.” Think of it like a copy machine. You can copy a piece of paper and hand it to someone. They can do whatever they want with that copy, but it doesn’t affect your original paper.
Let’s look at an example:
Here, we pass the string hello into the procedure TryToChangeThisValue . When we get into the TryToChangeThisValue procedure, we print the string hello to the Immediate Window. We then change the data in the variable s and print its value again, which now shows new message .
However, when we get back to the ByValueExampleWithValueType procedure, we print the str variable, which is still set to hello .
Passing a value type using ByVal lets us hand out a copy of the data, rather than give access to the original data itself. This is a good thing, since we may not want other procedures to be able to change variables in our main procedure.
Using ByVal with Reference Types
When you pass a reference type using ByVal , what you’re really saying is “please give me a copy of the reference.” What’s a reference? A reference is an address to the variable in memory on your computer. Think of it like a home address. You can give someone a home address and they can figure out how to get there. Once they get there, they can change things (like mowing your lawn or painting your house).
Let’s use another example to illustrate:
In this example, we set the cell variable to the A1 range on the current worksheet. We then change the value to hello . Then we pass the cell variable to the ChangeCell procedure using ByVal . Since cell is a reference type, what we really passed to ChangeCell is a copy of the reference to that same cell.
Once we’re in the ChangeCell procedure, we print the value of the range, which at that point is hello . Then we update the value of the cell to new message . After we get back to the ByValueExampleWithReferenceType procedure, we print the cell’s value again, which is now changed to new message .
Being able to modify the original data can be useful in certain scenarios. For example, say you needed to take a range of cells and format them in a certain way. The code for that might be hundreds of lines long. To me, it would be better to split that functionality out to keep your main procedure shorter and cleaner.
Why not just copy the whole object? Doing that can be an expensive operation on your computer and can make your code run slower.
Passing Arguments Using ByRef
Using ByRef with Value Types
When passing a value type to a procedure using ByRef , you are saying that you do want the reference to the original data. Going back to our copy machine analogy, this time instead of giving someone a copy of a paper, it’s like telling them which filing cabinet the paper is in. They can reach the original paper and make lasting changes to it.
Let’s change our string example above to use ByRef :
Now, when we call TryToChangeThisValue using ByRef , the change on the string to new message also changes the str variable back in the ByReferenceExampleWithValueType procedure.
Why would you want to use ByRef for value types? Well, when using ByVal , the entire data contents are copied into the called procedure. If you have a value type that has a lot of data (for example, a string that is 65,400 characters long), it may be better to pass by reference if you need to optimize for speed and memory consumption.
Using ByRef with Reference Types
When passing a reference type to a procedure using ByRef , you are saying that not only do you want the reference to the original data, but you also can change the reference itself if you want to. This means that you can reassign the variable from the calling procedure to point to another object completely.
Here’s an example to help bring the point home:
Let’s break this down step-by-step:
- We start off in the ByReferenceExampleWithReferenceType procedure.
- We set cell to the address A1 and print out that address in the Immediate Window.
We then call ChangeCell and pass in the cell variable using ByRef .
- Inside ChangeCell , we first print out the address to show that it is pointing to A1 before we change it.
- We then use the Set keyword to reassign the myCell variable to the new address A2 .
- We print out the new address of myCell which is now showing $A$2 .
Reassigning the variable inside ChangeCell also reassigned it in the ByReferenceExampleWithReferenceType procedure because we used ByRef on a reference type.
If we tried to reassign the myCell variable inside ChangeCell when using ByVal , the reassignment would not be reflected in the calling procedure, as shown here:
Passing a reference type to a procedure using ByRef gives you more control over the variable that came from the calling procedure.
What about Constants?
If the value type that you’re working with is a constant (by using the Const keyword), you cannot change the original value in the calling procedure, even if you were to use ByRef .
However, once inside your called procedure, you can modify the value of the constant that was passed in, even if you use ByRef . This means that the value you get inside the called procedure is not treated as a constant since you can change it.
Also, as far as I know, you cannot make reference types constants.
Choosing Between ByVal and ByRef
So how do you choose between ByVal and ByRef ?
Technically speaking, all you need to know is if you’re working with value types or reference types, and from there you can determine what level of access you want to have in your procedures. Beyond that, it’s just a matter of preference.
For me, I like to think of it this way:
If dealing with large data types (like long strings), consider using ByRef for a performance boost. Just be careful not to change the data in the called procedure (unless you intend to).
Personally, I default to ByVal most of the time for both value types and reference types. The main reason for this is because I prefer to have the main procedure own its data. I find it simpler to code and debug, but at the end of the day, it’s just a preference. I do believe there are good reasons to allow inner procedures to modify data it receives and sometimes you can’t avoid doing that anyway.
By the way, I do recommend to explicitly call out the keywords ByVal and ByRef in your procedure, so it’s clear which one you’re using. You can leave it out, but then you’ll need to remember that it’s ByRef by default.
Now that you have the details in your hands, you can figure out what works best for you.
Wow, you read the whole article! You know, people who make it this far are true learners. And clearly, you value learning. Would you like to learn more about Excel? Please consider supporting me by buying me a coffee (it takes a lot of coffee to write these articles!).
VBA ByVal
ByVal is a statement in VBA. ByVal stands for “By Value,” i.e., when the subprocedure calls in from the procedure, the value of the variables is reset to the new value from the new procedure called in.
In VBA, when we work with functions that call in different functions, there are certain circumstances that the value for the original argument is changed when calls the function. The ByVal statement prohibits the procedure or the code from changing the value for the argument.
By reading the explanation, we know it is not easy to understand. But with practical examples of function, we can understand it better.
Table of contents
You are free to use this image on your website, templates, etc, Please provide us with an attribution link How to Provide Attribution? Article Link to be Hyperlinked
For eg:
Source: VBA ByVal (wallstreetmojo.com)
How to use ByVal Argument in VBA?
Example #1
Code:
The above two macro procedures have a common variable, “k,” across procedures. Let me explain this in detail before we see the result.
In the first Macro, we have assigned the value of 50 to the variable “k.”
Next, we have called the second macro procedure from the first Macro.
In Macro2, we have reset the variable’s value to k = k + 5. In this macro, we have used the ByVal argument to assign the value to the variable “k.”
Now, to understand “ByVal,” let’s run the VBA code by pressing the F8 key.
#1 – Upon pressing the F8 key first, it will highlight the first line of the Macro1.
At this point, place a cursor on the variable “k.” It should show the value of the variable “k.”
At the moment, the value of “k” is zero.
#2 – Press the F8 key again, and it will jump to the third line.
Even now, the value of “k” is still zero.
#3 – Press the F8 key now. See the value of the k.
Since the “k” value sets to 50 and the code executes, the value shows as 50.
#4 – The highlighted line is “Macro2 k,” i.e., pressing the F8 key will jump to the second procedure, Macro2.
#5 – Even now, the value of variable “k” in this procedure also shows as 50. But inside this macro, we are resetting the value of the variable “k” as k = k + 5, i.e., 55. So, now press the F8 key two more times.
As you can see above, the “k” value is 55 now.
#6 – Press the F8 key. It will jump back to the Macro1 procedure.
When the macro jumps back to the original procedure Macro1, our variable “k” value is no longer 55 but rather the original value in this procedure, i.e., 50.
As we have told at the beginning of the article “ByVal,” the argument does not carry values from one procedure to another even though it carries the variable’s value from the first macro to the second by the moment. It encounters the line “ByVal” when it returns to the original macro. It resets the value to the original value in the procedure only.
Example #2
Now, take a look at the below two macros.
Code:
- It is similar to the first example. In the macro “P1,” we have assigned the value of 10 to the variable “k,” and in the same macro “P1,” we have called the second macro “P2” with variable “k.”
- In the second macro, “P2,” we have used the ByVal argument. This time, the value of variable “k” is 15.
This macro carries the value of variable “k” as ten from macro “P1” to macro “P2.” So, this macro will reset the value to 15, but the moment it comes back to finish the macro to the first macro, “P1,” the value of “k” back to 10, not 15.
Things to Remember
The ByVal argument does not affect the variable value even after running the macro. Still, we can carry the variable’s value from one macro to the other with the By Ref argument.
Recommended Articles
This article has been a guide to VBA Byval. Here, we discuss using the ByVal argument in VBA along with the examples and downloadable Excel template. You can learn more about VBA from the following articles: –
VBA-Урок 9. Процедуры и функции
На данный момент, все процедуры, мы создавали, имеют тип Public , что означает, что они доступны из любого модуля.
Чтобы сделать процедуру доступной только в определенном модуле, используется ключевое слово Private:
Запуск процедуры с середины другой процедуры
Чтобы выполнить процедуру с середины другой процедуры, просто введите ее название.
Здесь есть очень простой пример:
Аргументы
Аргументы делают возможным использование значений из процедуры в под-процедуры (запомните, что по умолчанию, переменные являются доступны только по той процедуры, в которой они были объявлены).
К процедуре «warning» был добавлен аргумент, в данном случае это переменная «var_text» с типом «String» (строка):
Эта процедура требует аргумент, поэтому мы должны поставить значение после «warning», чтобы выполнить ее:
Когда мы хотим прописать несколько аргументов, тогда они должны быть отделены запятыми.
Необязательные аргументы
По умолчанию, если процедура имеет аргументы, то они должны быть обязательно проставлены, и если они не проставлены, тогда процедура не выполнится.
Необязательный аргумент может быть добавлен после обязательного, с помощью ключевого слова Optional . Например:
Теперь эта процедура может быть выполнена с или без опционального аргумента, как здесь:
Аргументы должны быть введены в правильном порядке.
Чтобы протестировать, присутствует ли опциональный аргумент в процедуре, мы используем функцию IsMissing . Эта функция совместима только с некоторыми типами функций (типа Variant) и это является решающим, так как тип необязательно аргументов не был указан в объявлении (необъявленный тип = Variant).
Здесь есть пример, который использует два фрагмента кода, которые рассматривались выше:
См. рисунок ниже (пример 1):
ByRef — ByVal
По умолчанию, аргументы имеют тип ByRef , что означает: если переменная передается как аргумент, ссылка на нее будет также передаваться. Иными словами, если переменная была изменена другой под-процедурой, то она также будет изменена во внешней процедуре, которая вызывает эту под-процедуру.
Чтобы стало понятнее, ниже есть пример того, что произойдет, если макрос будет запущен на выполнение:
Второй метод заключается в использовании ByVal .
В отличие от ByRef , который передает ссылки (ярлык), ByVal передает значение, которое означает, что значение передано как аргумент не было изменено.
Ниже вы можете увидеть как предыдущий код и ByVal работают:
Что вам нужно запомнить: используйте ByVal когда переменная не должна быть изменена .
Функции
Основным отличием между процедурой и функцией является то, что функция возвращает значение.
Вот простой пример:
Функция может быть использована на рабочем листе, подобно любой другой функции в Excel.
Например, чтобы получить квадрат значения, которое введенное в ячейку A1: