0

I have turned on the "require variable declare" option to force to declare the variable in the VBA setting. After recording a Macro, the following recorded Macro fails to run with an error message:

compile error: expected function or variable.

I have tried to turned off the "require variable declare" in tools->options, then the code runs successfully. How to run it when the option is on?

Option Explicit

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1:E4").Select
    selection.Copy
    Range("A16").Select
    activesheet.Paste
End Sub
xzhu
  • 49
  • 5

1 Answers1

1

VBA is case sensitive. selection.Copy should be Selection.Copy and activesheet.Paste should be ActiveSheet.Paste. Also, unless there is a need to select the cells, you can simplify your code to:

Range("A1:E4").Copy Range("A16")
Frank Ball
  • 1,039
  • 8
  • 15
  • Thanks for answering. The code is copied from the recorded Macro. I tried to fix it to CAPITAL letter. After click F5, it returns the same error and fix it to lowercase ''selection"' – xzhu Aug 27 '18 at 14:08
  • 2
    @XianhaoZHU do you by chance have another `Sub` named `selection`? – BigBen Aug 27 '18 at 14:08
  • Are you using Excel's built-in VBA editor or a 3rd party tool? – Frank Ball Aug 27 '18 at 14:10
  • @BigBen - Excellent question! Another related one would be: Do you have variables named "selection" and/or "activesheet"? – Frank Ball Aug 27 '18 at 14:10
  • 1
    @BigBen yes i do and i figure it out now. - -! Thanks – xzhu Aug 27 '18 at 14:13