Styjun

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.

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.

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