Compile Error Expected Function Or Variable

Question

I'm new and trying to learn VBA. When I'm typing in the code I get Compile Error Expected Function Or Variable.

Is something regarding the activecell, but can't figure it out.

Sub Testare()
    Dim FilmName As String
    Dim FilmLenght As Integer
    Dim FilmDescription As String

    Range("b10").Select
    FilmName = ActiveCell.Value
    FilmLenght = ActiveCell.Offset(0, 2).Value

    If FilmLenght < 100 Then
       FilmDescription = "Interesant"
    Else
       FilmDescription = "Suficient"
    End If

    MsgBox FilmName & " is " & FilmDescription
End Sub

Answer 1

Here is what caused this error in my case:

Inside a new Public Function that I was starting to write, I began to type a statement, but needed to copy a long varname from another location, so I just inserted the letter a to prevent Excel from popping-up the uber-annoying Compile Error: Expected Expression dialog. So I had this:

myVarName = a

I then attempted to step-through my code to get to that point, and when Excel entered that function I received the message "Compile Error: Expected Function Or Variable". To resolve, I just changed the placeholder to a number:

myVarName = 1

And all continued just fine.

Answer 2

This error happens also when a Sub is called the same as variable (i.e. in one Sub you have for loop with iterator "a", whilst another Sub is called "a").

This gives an error that fits to your description.

Regards

Answer 3

It is possible to make your code fail in two different ways:

1. Place a very large value in D10
2. Place a text value in D10

This will result in either an overflow error or type mismatch error.

Answer 4

I know this was asked a while ago, but I ended up with the same error when I created a Sub within a worksheet with the Sub Name the same as the Worksheet name.

Unfortunately when this happens, no error is highlighted by a compile; the error only appears at run time. Also, the offending line is not highlighted, which makes it a bit difficult to find.

Changing the Sub name solves it though.