How to pass arguments between procedures ByRef,ByVal Excel(Excel) Macro(VBA)

Write how to pass arguments when procedures are separated.

We mentioned in the article below that when writing code, it is easier to read and process the code if the procedures are separated.

What is an Excel (Excel) Macro (VBA) Module and Procedure?

But now comes the problem of separating procedures.

That is how arguments are passed.

How to pass arguments

First, we have the code with no arguments passed on.

Sub Morning()
    Dim aisatsu As String
    aisatsu = "Good morning".
    Call Display
End Sub

Sub Display()
    MsgBox aisatsu
End Sub

This code stores the words "good morning" in "aisatsu" in a procedure called "morning" and displays the words stored in "aisatsu" in a message box in a procedure called "display.

I'll run the "morning."

The message box appeared, but nothing was displayed inside.

This is because the procedure "morning" stored the words "good morning" in the variable "aisatsu", but the same "aisatsu" variable did not display anything in the procedure "display" because nothing was stored in "aisatsu".

Next, I made this modification.

Sub Morning()
    Dim aisatsu As String
    aisatsu = "Good morning".
    Call Display(aisatsu)
End Sub

Sub Display(ByVal aisatsu As String)
    MsgBox aisatsu
End Sub

I will run "morning" again.

The argument was properly handed over.

Use of ByVal and ByRef

• ByVal (pass by value)
• ByRef (pass-by-reference)

ByVal is used in situations where you do not want the value to change, since it cannot be changed within the procedure.

ByRef is used in situations where the value needs to be changed rapidly, since the value can be changed within the procedure that is passed to it.

It is difficult to put into words, so let's look at the differences specifically.

ByVal(value passed)

I made a slight change in the procedure "display" and called it twice in a row in the "morning".

Sub Morning()
    Dim aisatsu As String
    aisatsu = "Good morning"
    Call display(aisatsu)
    Call display (aisatsu)
End Sub

Sub Display (ByVal aisatsu As String)
    MsgBox aisatsu
    aisatsu = "Hello"
End Sub

I'll run the "morning."

Press "OK."

The same words were used again.

No matter how much you change the arguments brought from the "morning" procedure in the "display", you will find that the changes are not carried over when you return to the "morning" procedure.

ByRef (pass-by-reference)

Next, change "ByVal" to "ByRef".

Sub Morning()
    Dim aisatsu As String
    aisatsu = "Good morning"
    Call display(aisatsu)
    Call Display(aisatsu)
End Sub

Sub Display(ByRef aisatsu As String)
    MsgBox aisatsu
    aisatsu = "Hello".
End Sub

Now you can try to run "morning" with it.

and

We could change the language.

Omission of ByVal and ByRef

By the way, "ByVal" and "ByRef" can be omitted.

What happens if it is omitted?

Sub Morning()
    Dim aisatsu As String
    aisatsu = "Good morning"
    Call display(aisatsu)
    Call Display(aisatsu)
End Sub

Sub Display( aisatsu As String)
    MsgBox aisatsu
    aisatsu = "Hello".
End Sub

Execution.

and

This was the first time that the company had been in the market for a new product.

In other words, we found that it is the same as "ByRef" when abbreviated.