VBA ByRef Argument Type Mismatch – How to Fix?

You will encounter the ‘ByRef argument type mismatch’ error in Excel VBA if you call a procedure or function and feed it with an argument of a different data type than the one expected by the procedure or function. 

For instance, you will encounter the ‘ByRef argument type mismatch’ error if you call a procedure or function that expects an argument of string data type and pass an argument of integer data type to it.

ByRef Argument Type Mismatch VBA

What Does ByRef Mean in VBA?

‘ByRef’ is short for ‘By Reference.’

When you use ‘ByRef’ in a procedure, you pass the address of an argument instead of the value.

Passing the variable’s address means the procedure can access the actual variable and change its value.

By default, arguments are passed by reference unless specified otherwise.

‘ByRef’ is the opposite of ‘ByVal’ (By Value).

Passing a variable by value is when you pass a copy of the variable’s value to a procedure instead of passing the address.

Passing a variable’s value allows the procedure to access a copy of the variable without being able to change its actual value. 

In this tutorial, I will show you four ways to fix Excel VBA’s ‘ByRef argument type mismatch’ error.

Method #1: Match the Data Types of Arguments Passed With those Expected

Ensure that the data types of the variables you pass as arguments match those expected by the procedure or function you have called. 

Suppose you have the following code, which triggers the ‘ByRef argument type mismatch’ error. 

Sub mainProcedure()
    Dim myValue As Integer
    myValue = 350
    Call myProcedure(myValue)
End Sub

Sub myProcedure(ByRef inputValue As String)
    Debug.Print inputValue
End Sub

The error happens because the ‘mainProcedure’ subroutine passes an argument ‘myValue,’ which is of Integer data type, to the ‘myProcedure’ subroutine that expects an argument of String data type.

To fix the error, you can either change the data type of the ‘myValue’ variable to a String that matches the data type expected by ‘myProcedure,’ or change the data type of the argument expected by ‘myProcedure’ to an Integer that matches the data type of the ‘myValue’ argument passed by ‘mainProcedure.’

Also read: VBA Runtime Error 91 – How to Fix?

Method #2: Use Explicit Conversion When Passing Arguments

When passing arguments, you can use explicit conversion to avoid the ‘ByRef argument type mismatch’ error.

Explicit conversion refers to using conversion functions such as ‘CStr,’ ‘Cint,’ ‘CDbl,’ etc. 

Here’s an example:

Sub mainProcedure2()
    Dim myValue As Integer
    myValue = 350
    Call myProcedure2(CStr(myValue))
End Sub

Sub myProcedure2(ByRef inputValue As String)
    Debug.Print inputValue
End Sub

In this example, the ‘mainProcedure2’  subroutine declares an integer variable ‘myValue’ and then calls the ‘myProcedure2’ subroutine, passing ‘myValue’ after explicitly converting it to a string using the’CStr’ function.

By using ‘CStr(myValue),’ you ensure that even though ‘myValue’ is an integer, it gets converted to a string before being passed to ‘myProcedure2.’

This way, you avoid the ‘ByRef argument type mismatch’ error that might occur if you directly pass an integer to a procedure expecting a string.

Also read: VBA Type Mismatch Error (Error 13)

Method #3: Enter ‘Option Explicit’ at the Top of Your Modules

The ‘Option Explicit’ statement is a directive in VBA that forces you to explicitly declare all variables before using them in the code.

Including Option Explicit at the beginning of a VBA module requires you to explicitly declare every variable using the ‘Dim,’ ‘ReDim,’ ‘Static,’ ‘Private,’ ‘Public,’ or ‘Const’ keywords before using it. 

The ‘Option Explicit’ directive forces you to declare all variables, helping catch undeclared variables that might lead to type mismatches.

Suppose you have the following code in a module without the ‘Option Explicit’ statement at the top:

Sub mainProcedure3()
    Dim myValue, iValue As Integer 
    myValue = 350
    iValue = 450

    Call myProcedure3(myValue)

End Sub

Sub myProcedure3(ByRef inputValue As Integer)
    Debug.Print inputValue
End Sub

You will encounter the ‘ByRef argument type mismatch’ error when you run the code.

The error occurs because the ‘mainProcedure3’ subroutine passes a variant ‘myValue’ variable (no specific data type) to the ‘myProcedure3’ subroutine expecting an argument of Integer data type. 

Unlike some programming languages, VBA doesn’t let you declare a group of variables as a particular data type by separating the variables with commas.

For example, the following statement in the above code, although valid, does not declare all the variables as integers:

Dim myValue, iValue As Integer 

In VBA, only ‘iValue’ is declared an integer; the ‘myValue’ variable is declared a variant. 

To declare ‘myValue’ and ‘iValue’ as integers, use the following statement:

Dim myValue As Integer, iValue As Integer

To prevent the mistake of trying to declare a group of variables as a specific data type by separating them with commas, you can add the statement ‘Option Explicit’ at the top of the module.

This declaration will make VBA require you to explicitly declare each variable you plan to use in your code.

If you want VBA to add the statement for you in each module automatically, you can do the following:

  1. In VB Editor, open the ‘Tools’ menu and select ‘Options.’
Click on the tools option in the menu and then click on options
  1. Select the ‘Require Variable Declaration’ checkbox on the’ Options’ dialog box and click OK.
Check the required variable declaration option to avoid VBA ByRef Argument Type Mismatch

Note: VBA will automatically add ‘Option Explicit’ in new modules. For older modules, you must add the statement manually.

Method #4: Use the Variant Data Type

You can use the Variant data type if an argument’s data type is uncertain. However, because this data type can slow your code, only use it when necessary. 

The code example below uses the variant data type to handle a ‘Cancel’ click:

Sub mainProcedure4()
    Dim userAge As Variant
    userAge = InputBox("Enter your age:")
    Call myProcedure4(userAge)
End Sub

Sub myProcedure4(ByRef inputValue As Variant)
    Debug.Print inputValue
End Sub

Explanation of the Variant Data Type

The ‘Variant’ data type is versatile and can hold any data type, including numbers, strings, dates, and objects.

When you declare a variable as a ‘Variant,’ it can dynamically change its data type at runtime based on the value assigned to it. 

The ‘Variant’ type is handy when you need a variable to handle different data types dynamically.

Although the ‘Variant’ type has flexibility, it has some trade-offs. Unlike variables with specific data types, variables of the ‘Variant’ type consume more memory.

Additionally, runtime dynamic type checking can slow down the code.

In this article, I explained the ‘ByRef argument type mismatch’ error and showed four ways to fix it.

I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment