How to fix ByRef Argument Type Mismatch error in Excel VBA

In VBA, “ByRef” is a keyword used to declare a parameter of a subroutine or function as a “By Reference” argument. When a parameter is declared as “ByRef”, any changes made to the parameter within the subroutine or function will be reflected in the original variable that was passed in as an argument. This means that the value of the original variable can be modified by the subroutine or function. 

A “ByRef argument type mismatch” error in VBA can occur when there is a mismatch between the data type of the variable passed as an argument and the data type expected by the subroutine or function. This error can occur when the parameter in the subroutine or function is declared with a specific data type, and the data type of the argument passed to that parameter does not match. In this tutorial, we’ll learn how to fix this error in Excel VBA. 

The possible reasons for this error to occur are as follows;

• Usage of different variable names
• Usage of different variable data type
• Variable data type not defined in one macro

So, let’s discuss these in the following sections and discuss the steps to fix these issues as well.

Reason 1 – Usage of different variable names

This is a very common type of mistake users make while working with many procedures. They forget the actual name of the variable that is to be used within the macro. So, they face this error.

Look at this simple VBA code;

Sub Test_byRef_Error()

Dim A As Long

    A = 50

    Test_Macro B

    MsgBox A

End Sub

Sub Test_Macro(ByRef A As Long)

     A = A * 10

End Sub

In this code we can clearly see that our Test_byRef_Error has a variable named A and defined as Long type. However, in Test_Macro we mistakenly used variable B. Therefore, we’ll get the error as shown above.

Step 1 – Change the variable name to fix error

• In the VBA Editor, go to the line in the first macro where the variable B is used.
• Change the variable name from B to A and run the code again by pressing the play button or F5. 
• This time the code will run successfully and we’ll not get any errors as shown above.

Reason 2 – Usage of different variable data type

The second common mistake is defining variables with different data types in both macros. 

Look at this simple VBA code;

Sub Test_byRef_Error()

Dim A As Integer

    A = 50

    Test_Macro A

    MsgBox A

End Sub

Sub Test_Macro(ByRef A As Long)

     A = A * 10

End Sub

In this code we can clearly see that our Test_byRef_Error has a variable named A is defined as Integer type. However, in Test_Macro we mistakenly used variable A with Long data type. Therefore, we’ll get the error as shown above.

Step 1 – Change the variable data type to fix error

• In the VBA Editor, go to the line in the second macro where the variable A is defined as Long instead of Integer.
• Change the variable data type from Long to Integer in Test_Macro and run the code again by pressing the play button or F5. 
• This time the code will run successfully and we’ll not get any errors as shown above.

Reason 3 – Variable data type not defined in a Macro

The second common mistake is defining variables with different data types in both macros. 

Look at this simple VBA code;

Sub Test_byRef_Error()

    A = 50

    Test_Macro A

    MsgBox A

End Sub

Sub Test_Macro(ByRef A As Integer)

     A = A * 10

End Sub

In this code we can see that our Test_byRef_Error has a variable named A and it is not defined as Integer type. However, in Test_Macro we have called variable A with Integer data type. Therefore, we’ll get the error as shown above.

Step 1 – Define the variable data type to fix error

• In the VBA Editor, go to the line in the second macro where the variable A is assigned a value.
• In a line just before this one, define the variable data type by using Dim A As Integer in Test_byRef_Error macro and run the code again by pressing the play button or F5. 
• This time the code will run successfully and we’ll not get any errors as shown above.