Byval vba что это

VBA-Урок 9. Процедуры и функции

На данный момент, все процедуры, мы создавали, имеют тип Public , что означает, что они доступны из любого модуля.

Чтобы сделать процедуру доступной только в определенном модуле, используется ключевое слово Private:

Запуск процедуры с середины другой процедуры

Чтобы выполнить процедуру с середины другой процедуры, просто введите ее название.

Здесь есть очень простой пример:

Аргументы

Аргументы делают возможным использование значений из процедуры в под-процедуры (запомните, что по умолчанию, переменные являются доступны только по той процедуры, в которой они были объявлены).

К процедуре «warning» был добавлен аргумент, в данном случае это переменная «var_text» с типом «String» (строка):

Эта процедура требует аргумент, поэтому мы должны поставить значение после «warning», чтобы выполнить ее:

Когда мы хотим прописать несколько аргументов, тогда они должны быть отделены запятыми.

Необязательные аргументы

По умолчанию, если процедура имеет аргументы, то они должны быть обязательно проставлены, и если они не проставлены, тогда процедура не выполнится.

Необязательный аргумент может быть добавлен после обязательного, с помощью ключевого слова Optional . Например:

Теперь эта процедура может быть выполнена с или без опционального аргумента, как здесь:

Аргументы должны быть введены в правильном порядке.

Чтобы протестировать, присутствует ли опциональный аргумент в процедуре, мы используем функцию IsMissing . Эта функция совместима только с некоторыми типами функций (типа Variant) и это является решающим, так как тип необязательно аргументов не был указан в объявлении (необъявленный тип = Variant).

Здесь есть пример, который использует два фрагмента кода, которые рассматривались выше:

См. рисунок ниже (пример 1):

ByRef — ByVal

По умолчанию, аргументы имеют тип ByRef , что означает: если переменная передается как аргумент, ссылка на нее будет также передаваться. Иными словами, если переменная была изменена другой под-процедурой, то она также будет изменена во внешней процедуре, которая вызывает эту под-процедуру.

Чтобы стало понятнее, ниже есть пример того, что произойдет, если макрос будет запущен на выполнение:

Второй метод заключается в использовании ByVal .

В отличие от ByRef , который передает ссылки (ярлык), ByVal передает значение, которое означает, что значение передано как аргумент не было изменено.

Ниже вы можете увидеть как предыдущий код и ByVal работают:

Что вам нужно запомнить: используйте ByVal когда переменная не должна быть изменена .

Функции

Основным отличием между процедурой и функцией является то, что функция возвращает значение.

Вот простой пример:

Функция может быть использована на рабочем листе, подобно любой другой функции в Excel.

Например, чтобы получить квадрат значения, которое введенное в ячейку A1:

# Passing Arguments ByRef or ByVal

The ByRef and ByVal modifiers are part of a procedure’s signature and indicate how an argument is passed to a procedure. In VBA a parameter is passed ByRef unless specified otherwise (i.e. ByRef is implicit if absent).

Note In many other programming languages (including VB.NET), parameters are implicitly passed by value if no modifier is specified: consider specifying ByRef modifiers explicitly to avoid possible confusion.

# Passing Simple Variables ByRef And ByVal

Passing ByRef or ByVal indicates whether the actual value of an argument is passed to the CalledProcedure by the CallingProcedure , or whether a reference (called a pointer in some other languages) is passed to the CalledProcedure .

If an argument is passed ByRef , the memory address of the argument is passed to the CalledProcedure and any modification to that parameter by the CalledProcedure is made to the value in the CallingProcedure .

If an argument is passed ByVal , the actual value, not a reference to the variable, is passed to the CalledProcedure .

A simple example will illustrate this clearly:

# ByRef

# Default modifier

If no modifier is specified for a parameter, that parameter is implicitly passed by reference.

The foo parameter is passed ByRef in both DoSomething1 and DoSomething2 .

Watch out! If you’re coming to VBA with experience from other languages, this is very likely the exact opposite behavior to the one you’re used to. In many other programming languages (including VB.NET), the implicit/default modifier passes parameters by value.

# Passing by reference

Calling the above Test procedure outputs 84. DoSomething is given foo and receives a reference to the value, and therefore works with the same memory address as the caller.

The above code raises run-time error 91

(opens new window) , because the caller is calling the Count member of an object that no longer exists, because DoSomething was given a reference to the object pointer and assigned it to Nothing before returning.

# Forcing ByVal at call site

Using parentheses at the call site, you can override ByRef and force an argument to be passed ByVal :

The above code outputs 42, regardless of whether ByRef is specified implicitly or explicitly.

Watch out! Because of this, using extraneous parentheses in procedure calls can easily introduce bugs. Pay attention to the whitespace between the procedure name and the argument list:

# ByVal

# Passing by value

Calling the above Test procedure outputs 42. DoSomething is given foo and receives a copy of the value. The copy is multiplied by 2, and then discarded when the procedure exits; the caller’s copy was never altered.

Calling the above Test procedure outputs 1. DoSomething is given foo and receives a copy of the pointer to the Collection object. Because the foo object variable in the Test scope points to the same object, adding an item in DoSomething adds the item to the same object. Because it’s a copy of the pointer, setting its reference to Nothing does not affect the caller’s own copy.

# Remarks

# Passing arrays

Arrays must be passed by reference. This code compiles, but raises run-time error 424 "Object Required":

Name already in use

docs / docs / visual-basic / language-reference / modifiers / byval.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

ByVal (Visual Basic)

Specifies that an argument is passed by value, so that the called procedure or property cannot change the value of a variable underlying the argument in the calling code. If no modifier is specified, ByVal is the default.

[!NOTE] Because it is the default, you do not have to explicitly specify the ByVal keyword in method signatures. It tends to produce noisy code and often leads to the non-default ByRef keyword being overlooked.

The ByVal modifier can be used in these contexts:

The following example demonstrates the use of the ByVal parameter passing mechanism with a reference type argument. In the example, the argument is c1 , an instance of class Class1 . ByVal prevents the code in the procedures from changing the underlying value of the reference argument, c1 , but does not protect the accessible fields and properties of c1 .

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.

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