SOLVED

byref Type Mismatch Error when declaring multiple variables in single row

%3CLINGO-SUB%20id%3D%22lingo-sub-2672906%22%20slang%3D%22en-US%22%3Ebyref%20Type%20Mismatch%20Error%20when%20declaring%20multiple%20variables%20in%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2672906%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20I%20would%20need%20a%20bit%20of%20explanation%20what%20actually%20happens%20when%20I%20declare%20variables%20in%20a%20single%20row.%20If%20I%20do%20so%2C%20passing%20the%20variable%20to%20another%20Sub%20in%20the%20default%20ByRef%20method%20won't%20work.%20I%20know%20two%20possible%20solutions%20(workarounds%3F)%20for%20my%20issue%2C%20but%20I%20want%20to%20understand%20why%20it%20works%2C%20and%20why%20the%20original%20approach%20does%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20short%20code%20that%20gives%20byref%20type%20mismatch%20error%20when%20trying%20to%20compile%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Macro1()%0A%0A%20%20%20Dim%20A%2C%20B%20As%20Integer%0A%20%20%20A%20%3D%2050%0A%20%20%20B%20%3D%2010%0A%20%20%20Macro2%20A%0A%20%20%20Debug.Print%20A%0A%0AEnd%20Sub%0A%0ASub%20Macro2(ByRef%20A%20As%20Intger)%0A%20%20%20%20A%20%3D%20A%20*%2010%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BOne%20solution%20is%20to%20declare%20A%20and%20B%20variables%20in%20different%20rows%2C%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%20%20%20Dim%20A%20As%20Integer%0A%20%20%20Dim%20B%20As%20Integer%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20solution%20is%20if%20I%20pass%20the%20value%20of%20the%20variable%20instead%20of%20the%20reference%20in%20Macro2%2C%20but%20in%20that%20case%20A%20won't%20get%20the%20new%20value%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Macro2(ByVal%20A%20As%20Integer)%0A%20%20%20%20A%20%3D%20A%20*%2010%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20understand%20why%3F%20What%20happens%20when%20I%20declare%20the%20variables%20at%20once%20that%20prevents%20the%20project%20to%20get%20compiled%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20explanation!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2672906%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2674646%22%20slang%3D%22en-US%22%3ERe%3A%20byref%20Type%20Mismatch%20Error%20when%20declaring%20multiple%20variables%20in%20single%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2674646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120411%22%20target%3D%22_blank%22%3E%40szilvia_vf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnlike%20most%20other%20programming%20languages%2C%20VBA%20requires%20that%20you%20specify%20each%20variables%20data%20type%20separately%20when%20you%20declare%20them%20on%20a%20single%20line.%20If%20you%20don't%2C%20they%20are%20declared%20as%20the%20default%20type%20Variant.%3C%2FP%3E%0A%3CP%3ESo%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20Dim%20A%2C%20B%20As%20Integer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eis%20equivalent%20to%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20Dim%20A%20As%20Variant%2C%20B%20As%20Integer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EWhen%20you%20call%20Macro2%2C%20the%20argument%20that%20you%20pass%20to%20the%20ByRef%20argument%20A%20must%20be%20Integer%20(I%20assume%20that%20Intger%20was%20a%20typo).%20Since%20A%20is%20effectively%20declared%20as%20Variant%20in%20the%20line%20above%2C%20you%20get%20a%20Type%20Mismatch%20error.%3C%2FP%3E%0A%3CP%3ESolution%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20Dim%20A%20As%20Integer%2C%20B%20As%20Integer%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIn%20this%20version%2C%20both%20A%20and%20B%20are%20declared%20explicitly%20as%20Integer.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi!

 

I guess I would need a bit of explanation what actually happens when I declare variables in a single row. If I do so, passing the variable to another Sub in the default ByRef method won't work. I know two possible solutions (workarounds?) for my issue, but I want to understand why it works, and why the original approach does not.

 

Here is a short code that gives byref type mismatch error when trying to compile:

Sub Macro1()

   Dim A, B As Integer
   A = 50
   B = 10
   Macro2 A
   Debug.Print A

End Sub

Sub Macro2(ByRef A As Intger)
    A = A * 10
End Sub

 One solution is to declare A and B variables in different rows, like this:

   Dim A As Integer
   Dim B As Integer

 

 

Another solution is if I pass the value of the variable instead of the reference in Macro2, but in that case A won't get the new value:

Sub Macro2(ByVal A As Integer)
    A = A * 10
End Sub

 

I would like to understand why? What happens when I declare the variables at once that prevents the project to get compiled?

 

Thanks for the explanation!

2 Replies
best response confirmed by szilvia_vf (Occasional Contributor)
Solution

@szilvia_vf 

Unlike most other programming languages, VBA requires that you specify each variable's data type separately when you declare them on a single line. If you don't, they are declared as the default type Variant.

So

 

    Dim A, B As Integer

 

is equivalent to

 

    Dim A As Variant, B As Integer

 

When you call Macro2, the argument that you pass to the ByRef argument A must be Integer (I assume that Intger was a typo). Since A is effectively declared as Variant in the line above, you get a Type Mismatch error.

Solution:

 

    Dim A As Integer, B As Integer

 

In this version, both A and B are declared explicitly as Integer.

I learn something new every day. In this case I had an inappropriate declaration habit so far. Thank you! (indeed, Intger was just a typo)