Aug 22 2021 03:41 AM
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!
Aug 23 2021 02:17 AM - edited Aug 23 2021 02:17 AM
SolutionUnlike 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.
Aug 23 2021 02:24 AM
Aug 23 2021 02:17 AM - edited Aug 23 2021 02:17 AM
SolutionUnlike 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.