VBA ByRef Argument Type Mismatch

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

ByRef Argument Type Mismatch in Excel VBA

This article explains the error encountered using Excel VBA ByRef as “Argument Type Mismatch Error.” Before that, let me introduce you to “By Ref” first. Variables are key to any programming language, and VBA is not different either. We have seen many ways of declaring variables. One such way of declaring variables is by using the words “ByRef” and “ByValByValVBA ByVal or "By Value" is a statement that facilitates the user to reset the main value, i.e., by replacing it with another sub procedure in excel. It is a common practice when the user gets a down to 0 main procedure value.read more.”

What Does ByRef Mean?

“ByRef” means “By Reference” using this word, we can pass arguments to procedures (for both sub & function) by reference. It is unlike its brother, “By Val,” which is not flexible but fixed in nature.

To understand this, let us look at the two macros below.

Code:

Sub Macro1()

    Dim A As Long

    A = 50

   Macro2 A

   MsgBox A
End Sub

Sub Macro2(ByRef A As Long)

    A = A * 10

End Sub

We have two subprocedures here named Macro1 and Macro2, respectively. To understand this better, run the macro line by line by pressing the F8 key.

VBA ByRef Error Example 1.1

Press the F8 key to capture the variable “A” value is 50.

VBA ByRef Error Example 1.2

The next line of code says “Macro2 A,” i.e., the name of the second macro, and “A” is the variable defined through the “By Ref” word.

VBA ByRef Error Example 1.3

As you can see above, the moment we execute the line of code “Macro2 A,” it has jumped to the next VBA sub procedureVBA Sub ProcedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA.read more from the above procedure.

VBA ByRef Error Example 1.4

We can see the value of the variable “A” is 50 because we have used the word “ByRef” to declare the variable “A,” which is the same as in Macro1. Therefore, it has captured the value assigned to this variable “A” from the Macro1.

Now, in this macro (Macro2) equation says A = A * 10 i.e. A = 50 * 100. Press the F8 key three times to return to the macro above (Macro1).

VBA ByRef Error Example 1.5

Now, press the F8 key one more time the F8 key to see the value of variable “A” in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

VBA ByRef Error Example 1.6

The value says 500.

Even though the value we have assigned in this macro (Macro1) is 50 using the ByRef word, we triggered the Macro2 sub procedure retaining the value of variable “A” from Macro1. Then, we execute the value of A by multiplying 10.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Top 3 Reasons for VBA Byref Argument Type Mismatch

Above, we have seen how “ByRef” works, but we are bound to make mistakes that invariably result in throwing a VBA errorVBA ErrorVBA error handling refers to troubleshooting various kinds of errors encountered while working with VBA. read more message as “ByRef Argument Type Mismatch.”

It can be due to many reasons. This section will show you how to rectify this error and debug the code.

You can download this VBA ByRef Argument Type Mismatch Excel Template here – VBA ByRef Argument Type Mismatch Excel Template

Error Reason #1 – Different Variable Names

One of the main reasons for getting this error in Excel VBA is that different variables have passed in two procedures. For example, look at the below codes.

Code:

Sub Macro1()

   Dim A As Long
   A = 50

   Macro2 B

   MsgBox A

End Sub

Sub Macro2(ByRef A As Long)

    B = B * 10

End Sub
VBA ByRef Argument Error Reason 1.1

In Macro1, we have used the “A” variable, and in Macro2, we have used the “B” variable. So, if you try to run the code, we will get a VBA Error as “ByRef Argument Type Mismatch.”

ByRef Argument Type Mismatch Error 1

As you can see above, variable “B” gets highlighted because of the variable name type mismatch.

Solution: To overcome this issue, we must ensure that variable names in both procedures are exact.

Error Reason 2: Different Variable Data Types

Even though variable names are the same, it still causes an error. That is because of the data type we assign to them. For example, look at the below code.

Code:

Sub Macro1()

    Dim A As Integer
    A = 50

    Macro2 A
    MsgBox A

End Sub

Sub Macro2(ByRef A As Long)

    A = A * 10

End Sub
VBA ByRef Argument Error Reason 2.1

In the above codes, we have declared variable “A” as an Integer data type in Macro1. However, in Macro2, the same variable was assigned the data type “Long.”

When we run this code, it will cause a VBA error “ByRef Argument Type Mismatch.”

ByRef Argument Type Mismatch Error 2

That is because we have assigned two different data types for the same variable name.

Solution: Data type should be the same in both the procedures.

Error Reason 3: Variable Data Types Missing in One Macro

The Excel VBA error, “ByRef Argument Type Mismatch,” could happen due to the data type assigned in one macro, not another macro.

Code:

Sub Macro1()

    A = 50

    Macro2 A
    MsgBox A

End Sub

Sub Macro2(ByRef A As Long)

    A = A * 10

End Sub
VBA ByRef Argument Error Reason 3.1

In the above code of Macro1, we have not declared any variable but assigned the variable value.

On the other hand, for Macro2, we have declared the variable “A” as long. So if you try running this code, it will cause the “ByRef Argument Type Mismatch” VBA error.

ByRef Argument Type Mismatch Error 3

Solution1: The first solution is to declare the variable in both the procedures and assign the same data type to avoid these situations.

Solution2: An alternative solution is to make the variable declaration mandatory by adding the “Option Explicit” word at the top of the module.

VBA ByRef Argument Error Solution 3.1

What this will do is that before it shows VBA “ByRef Argument Type Mismatch,” Error, it asks us to declare the variable first.

VBA ByRef Argument Error Solution 3.2

So, Option ExplicitOption ExplicitVBA option explicitly makes a user mandatory to declare all the variables before using them; any undefined variable will throw an error while coding execution. We can enable it for all codes from options to require variable declaration.read more always comes in handy in VBA.

Things to Remember

  • ByRef is the opposite of By Val.
  • ByRef carries the reference from one procedure to another.
  • The variable name and data type should be the same in both procedures.
  • Each variable needs to be declared separately in the case of multiple variables.

Recommended Articles

This article has been a guide to VBA ByRef Argument Type Mismatch. Here, we discuss the top 3 reasons for the ByRef argument type mismatch error with their solution in Excel VBA. You can learn more about VBA from the following articles: –