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

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

Next line of code says “Macro2 A” i.e. name of the second macro and “A” is the variable defined through “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 variable “A” is 50 this is because since we have used the word “ByRef” to declare the variable “A” which is 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 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 value we have assigned in this macro (Macro1) is 50, by using 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 reason behind getting this error in Excel VBA is due to different variables passed in two procedures. For an 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 “A” variable and in Macro2 we have used “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 same still it causes an error this is because of 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 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 “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: Alternative solution is to make the variable declaration mandatory by adding “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 same in both the procedures.
• Each variable needs to be declared separately in 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 –

• Examples of VBA Goal Seek
• Excel VBA String Array
• VBA Index Match Function
• OverFlow Error in Excel VBA
• 1004 Error in Excel VBA
• IFERROR in Excel VBA
• On Error in Excel VBA