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

I just copied and pasted your code into my VBA and it worked.... It might be somewhere else aside form this code — John Bustos
– John Bustos, Commented Apr 24, 2014 at 16:50
Are you using a non-English version? Also, unless there was a copy-paste error, "FilmName" should be made consistent throughout the module. — Sam
– Sam, Commented Apr 24, 2014 at 17:17

Artur Rutkowski · Accepted Answer · 2015-03-10 17:22:16Z

6

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

answered Mar 10, 2015 at 17:22

Artur Rutkowski

5374 gold badges9 silver badges18 bronze badges

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

Comments

Gary's Student · Accepted Answer · 2014-04-24 17:20:59Z

2

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

Place a very large value in D10
Place a text value in D10

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

answered Apr 24, 2014 at 17:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Nippey · Accepted Answer · 2014-10-23 11:53:58Z

2

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.

edited Oct 23, 2014 at 11:53

Nippey

4,73138 silver badges45 bronze badges

answered Oct 23, 2014 at 11:47

Peter Rush

211 bronze badge

Comments

cssyphus · Accepted Answer · 2018-08-07 16:22:41Z

-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.

answered Aug 7, 2018 at 16:22

cssyphus

40.4k21 gold badges104 silver badges125 bronze badges

2 Comments

Sandra Rossi Over a year ago

I don't understand this answer and I can't reproduce the symptom.

cssyphus Over a year ago

The error message is nebulous (see Peter Rush's answer) and there is little hint in the error message that an invalid variable declaration caused the error, to wit: a !== "a" -- that is, myVarName = "a" would have worked. Had this answer existed when I searched, it would have jogged my mind and I would have caught my typo earlier than I did, hence this answer for the next bleary-eyed jockey. I am surprised you could not reproduce or understand the obvious error. Evidently I did not explain in the answer very well so I will revise it.

Collectives™ on Stack Overflow

Compile Error Expected Function Or Variable

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related