Compiler Error in Excel Vba

Question

I'm getting compiler error while running below code:

Sub AddNameInList()

  FilmName = InputBox("Type in a new film name")

   Worksheets("sheet2").Activate

   Range("b1").End(xlDown).Offset(1, 0).Select

   ActiveCell.Value = FilmName

   MsgBox FilmName & " was added to the list"

End Sub

Error Message

Compiler Error: Variable not defined

I guess these issues would be clearer if the error message was changed to say "Variable not dimensioned" — SierraOscar
– SierraOscar, Commented Apr 19, 2016 at 20:41

Scott Craner · Accepted Answer · 2016-04-19 19:42:05Z

2

You need to declare FilmName:

Sub AddNameInList()
Dim FilmName As String

FilmName = InputBox("Type in a new film name")

Worksheets("sheet2").Range("B1").End(xlDown).Offset(1, 0).Value = FilmName

MsgBox FilmName & " was added to the list"

End Sub

If you have the Option Explicit set either at the top of the module or in the settings then it requires that all variables be declared.

Also avoid using .Select and .Activate. In general they are not needed and only slow down the code.

answered Apr 19, 2016 at 19:42

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

Compiler Error in Excel Vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related