Why sometimes the same variables declaration gives a compilation error?

Question

When there is a variable type mismatch compilation error in VBA sometimes the solution is to change how I declare the variables, but not the type. e.g.: If I have:

Dim numCables, numCaColumn, firstCableRow, numCable, numDiColumn, posInArray, j, numMaterialWDifSEColumn, numSEMaterials  As Integer

And I replace it for:

Dim numCables, numCaColumn, firstCableRow, numCable, numDiColumn, posInArray, j, numMaterialWDifSEColumn  As Integer
Dim numSEMaterials As Integer

Now with this change I don't have a compilation error and all is working fine.

I would like to know why this is happening.

Thanks.

This question pops up all the time, but in such a way that it is seldom an outright duplicate. Arguably VBA made a poor design decision here, but it is what it is. — John Coleman, Mar 27 '19 at 16:15

BigBen · Accepted Answer · 2019-03-27T16:20:19.150

In variable declaration, you must specify the type for each variable, otherwise it's a Variant.

In other words, in

Dim numCables, numCaColumn, firstCableRow, numCable, numDiColumn, posInArray, j, numMaterialWDifSEColumn, numSEMaterials  As Integer

only numSEMaterials is an Integer and the rest are Variants.

You need

 Dim numCables as Integer, numCaColumn as Integer, firstCableRow as Integer...

Note (thanks for the reminder @John Coleman) that it's almost always better to use a Long variable type instead of Integer. For a detailed explanation, see Why use integer instead of Long.

simple-solution · Answer 2 · 2019-03-27T16:25:46.567

Vba code like Dim a, b, c as integer does NOT declare a, b and c as integer type! It does only declare c as integer. The variables a and b are declared as variant!

To declare all variables as integer you have to decare each one individually:

Dim a as integer
Dim b as integer
Dim c as integer

.

PROOF

Option Explicit

Sub Debug_VarType()

Dim a, b, c As Integer

Debug.Print "Definition of a, b and c:"
Debug.Print "a: "; VarType(a); MyVarType(VarType(a))
Debug.Print "b: "; VarType(b); MyVarType(VarType(b))
Debug.Print "c: "; VarType(c); MyVarType(VarType(c))
Debug.Print ""

Dim d As Integer
Dim e As Integer
Dim f As Integer

Debug.Print "Definition of d, e and f:"
Debug.Print "d: "; VarType(d); MyVarType(VarType(d))
Debug.Print "e: "; VarType(e); MyVarType(VarType(e))
Debug.Print "f: "; VarType(f); MyVarType(VarType(f))
Debug.Print ""

End Sub


Function MyVarType(Varint As Integer) As String
Select Case Varint
    Case 0: MyVarType = "vbEmpty"
    Case 1: MyVarType = "vbNull"
    Case 2: MyVarType = "vbInteger"
    Case 3: MyVarType = "vbLong"
    Case 4: MyVarType = "vbSingle"
    Case 5: MyVarType = "vbDouble"
    Case 6: MyVarType = "vbCurrency"
    Case 7: MyVarType = "vbDate"
    Case 8: MyVarType = "vbString"
    Case 9: MyVarType = "vbObject"
    Case 10: MyVarType = "vbError"
    Case 11: MyVarType = "vbBoolean"
    Case 12: MyVarType = "vbVariant"
    Case 13: MyVarType = "vbDataObject"
    Case 14: MyVarType = "vbDecimal"
    Case 17: MyVarType = "vbByte"
    Case 20: MyVarType = "vbLongLong"
    Case 36: MyVarType = "vbUserDefinedType"
    Case 8192: MyVarType = "vbArray"
End Select
End Function

Why sometimes the same variables declaration gives a compilation error?

2 Answers2