An compile error in VBA after requiring variable declaration

Question

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

Frank Ball · Accepted Answer · 2018-08-27 14:03:38Z

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")

edited Aug 27, 2018 at 14:03

answered Aug 27, 2018 at 14:00

Frank Ball

1,1778 silver badges21 bronze badges

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

5 Comments

xzhu Over a year ago

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"'

BigBen Over a year ago

@XianhaoZHU do you by chance have another Sub named selection?

Frank Ball Over a year ago

Are you using Excel's built-in VBA editor or a 3rd party tool?

Frank Ball Over a year ago

@BigBen - Excellent question! Another related one would be: Do you have variables named "selection" and/or "activesheet"?

xzhu Over a year ago

@BigBen yes i do and i figure it out now. - -! Thanks

Collectives™ on Stack Overflow

An compile error in VBA after requiring variable declaration

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related