The way to declare variables

Question

I have a problem in vba.

Here is the Code:

Sub GetData()
If IsNumeric(UserForm1.TextBox1.Value) Then
flag = False
i = 0
id = UserForm1.TextBox1.Value
Do While Cells(i + 1, 1).Value <> ""

    If Cells(i + 1, 1).Value = id Then
        flag = True
        For j = 2 To 3
            UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
        Next j
    End If

    i = i + 1

Loop

If flag = False Then
    For j = 2 To 3
        UserForm1.Controls("TextBox" & j).Value = ""
    Next j
End If

Else
ClearForm
End If
End Sub

My problem is with first If statement. It only works when i declare variables like:

Dim id As Integer
Dim i As Integer

or

Dim i, id As Integer

But it doesn't work when i declare them like:

Dim id, i As Integer

It acts like when condition is false.

What is the difference?

Answer 1

When you Dim more than one variable in a line separated by a comma

e.g.

Dim i, id as Integer

..only the first variable (i) will be dimensioned correctly. The second variable (id) will be a variant.

Answer 2

See http://www.cpearson.com/excel/declaringvariables.aspx (especially the section "Pay Attention To Variables Declared With One Dim Statement") for a good discussion of how variables are declared. In your case, only id will have an explicit type (Integer). Variable i will be of the default type, Variant.

Note that this differs from VB.NET, where both variables would have Integer type.