VBA ByRef Argument Type Mismatch

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 “ByVal.”

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

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

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.

As you can see above, the moment we execute the line of code “Macro2 A,” it has jumped to the next VBA sub procedure from the above procedure.

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

Now, press the F8 key one more time the F8 key to see the value of variable “A” in the message box in VBA.

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.

Above, we have seen how “ByRef” works, but we are bound to make mistakes that invariably result in throwing a VBA error 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.

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

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

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

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

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

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.

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.

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

So, Option Explicit always comes in handy in VBA.

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

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: -

• Goal Seek in VBA
• VBA Index Match Function
• Overflow Error in Excel VBA
• 1004 Error in Excel VBA