WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA ByRef Argument Type Mismatch

VBA ByRef Argument Type Mismatch

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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.

VBA ByRef Error Example 1.1

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

VBA ByRef Error Example 1.2

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.

VBA ByRef Error Example 1.3

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.

VBA ByRef Error Example 1.4

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA ByRef Error Example 1.5

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

VBA ByRef Error Example 1.6

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.

You can download this VBA ByRef Argument Type Mismatch Excel Template here – VBA ByRef Argument Type Mismatch Excel Template

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

VBA ByRef Argument Error Reason 1.1

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

ByRef Argument Type Mismatch Error 1

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

VBA ByRef Argument Error Reason 2.1

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

ByRef Argument Type Mismatch Error 2

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

VBA ByRef Argument Error Reason 3.1

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.

ByRef Argument Type Mismatch Error 3

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.

VBA ByRef Argument Error Solution 3.1

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

VBA ByRef Argument Error Solution 3.2

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
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA ByRef Argument Type Mismatch Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More