CODEWITHSUNDEEP
vba
Mandragor
Mandragor

Reputation: 25

Why sometimes the same variables declaration gives a compilation error?

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.

Upvotes: 0

Views: 71

Answers (2)

simple-solution
simple-solution

Reputation: 1139

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

enter image description here

Upvotes: 1

BigBen
BigBen

Reputation: 50008

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.

Upvotes: 3

Related Questions