Macro Error

Question

Hi There,

I took an online VBA course back in June so I'm not sure if I turned something on/off but I can't create simple macros anymore for some strange reason.

If I record a very simple format text (hit record, select the text, choose yellow background, red text, select stop macro) macro, when I go to run it, it takes me to the VBE and it pulls up with a compile error with vbe code I've never seen when I have done this for the past 20 years at least 100000 times.

Please help. Here is a video of the steps I took and what happens and below here is the compile error.

I just tried it on another computer and this is what the VBE should look like:

Answer 1

I just hit record macro, named it, then stop and this is what I got when I ran the macro.

1. It opened VBE immediatly
2. Message box said "Compile Error"
3. And this is what appeared in the VBE module

Sub Macro1()

'

' Macro1 Macro

'

'

    Application. "Book1!Macro1"

End Sub

======================

So, therefore still not working.

Thanks for reaching out though.

Something is up with this Excel because I have another version of Excel on the same computer with no problems doing macros. So something was either turned on or off somewhere down the line. HELP

Answer 2

Hi There ...

Try recording your macro before you perform any operation on your sheet.

Like selecting a range or so . That would help :):):)

Answer 3

Thanks so much for taking the time out to try to answer.

To Answer Your Questions:

1)  You wrote - "quite possible that during your vba course you created code that is causing this?". 

Answer - not sure where to find all the macros on my Excel. I looked under Review - Macros - no macros.

I changed the options for Book 1, All Workbooks  - no macros. So if there is another place to find macros, I am unsure where it is.

2. You asked "Does this happen if you start Excel in safe mode?

Answer  Yes it does.

3. You said "Workaround: You can save the working macro as a text file and copy it into a module in the vbe."

I teach Excel for a living and it's important that the macro feature works.

But again, thanks for reaching out. I am answering the questions, in case another person want to help with this mystery.

Answer 4

I hope someone else can tell you the why. Offhand it looks like you are in a different area of the spreadsheet, perhaps a header. It is quite possible that during your vba course you created code that is causing this.

Does this happen if you start Excel in safe mode?

Open Office apps in safe mode on a Windows PC - Microsoft Office

Workaround: You can save the working macro as a text file and copy it into a module in the vbe.