Excel VBA error: "Compile error: Expected: end of statement"

Question

I'm trying to do some very basic stuff with VBA in Excel (2010) for a user form.

I have the following as my 1st and only line of code so far (I'm trying to define an integer for a loop):

dim i as integer = 1

when I press enter at the end of the line I get the following error:

Compile error: Expected: end of statement

Bathsheba · Accepted Answer · 2016-06-02 13:41:59Z

4

Dim i As Integer = 1 is not syntactically valid in VBA and that's confusing the interpreter.

You need to write

Dim i As Integer
i = 1

instead.

Note also that the range of an Integer in VBA is -32768 to +32767. Most folk use a Long instead.

answered Jun 2, 2016 at 13:41

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

That with the long might be true, but theres no reason to use it over an integer for normal reasons

Also, extra note, Dim i as Integer = 1 is valid VB.NET, but not vba, just as you mentioned

Ron Rosenfeld · Accepted Answer · 2016-06-02 17:17:39Z

0

If you want i to always equal 1, then you could write:

Const i as Integer = 1

But since you write you are defining it for a loop, Bathsheba's syntax is better, or you could do

Dim i as integer
For i = 1 to n
   do something
next i

answered Jun 2, 2016 at 17:17

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges