VBA ByRef Argument Type Mismatch

ByRef Argument Type Mismatch in Excel VBA

In this article, we explain the error encountered while 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.”

What Does ByRef Mean?

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

To understand this, let’s take a look at the below two macros.

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 & Macro2, respectively. To understand this better, run the macro line by line by pressing the F8 key.

Press the F8 key to capture the value of variable “A” as 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.

Now we can see the value of the variable “A” is 50. This is because since we have used the word “ByRef” to declare the variable “A,” which is the same as in Macro1, it has captured the value we have 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 3 times to go back to the above macro (Macro1).

Now press one more time 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, by using the ByRef word, we actually triggered the Macro2 subprocedure by retaining the value of variable “A” from Macro1 and then executing the value of A by multiplying 10.

Top 3 Reasons for VBA Byref Argument Type Mismatch

Above, we have seen how “ByRef” works, but we bound to make some of the mistakes which invariably resulted in throwing a VBA error message as “ByRef Argument Type Mismatch.”

This is because of many reasons, and in this section, we will show you how to rectify this error and debug the code.

Error Reason #1 – Different Variable Names

One of the main reasons behind getting this error in Excel VBA is due to different variables 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. Now, if you try to run the code, we will get VBA Error as “ByRef Argument Type Mismatch.”

As you can see above, variable “B” has been highlighted because the type of variable name is a mismatch.

Solution: To overcome this issue, we need to make sure that variable names in both the procedure is exact.

Error Reason 2: Different Variable Data Types

Even though variable names are the same, still it causes an error, this is because of the data type we assign to them. 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, I have declared variable “A” as Integer data type in Macro1, and in Macro2 same variable was assigned the data type as “Long.”

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

This 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 data type assigned in one macro and not assigned in 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, I have not declared any variable rather simply assigned the value to the variable.

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

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

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 actually asks us to declare the variable first.

So, Option Explicit 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, data type should be the same in both the procedures.
• Each variable needs to be declared separately in the case of multiple variables.

Recommended Articles

This has been a guide to VBA ByRef Argument Type Mismatch. Here we discuss the top 3 reasons for 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