SOLVED

Macro gives compile error

%3CLINGO-SUB%20id%3D%22lingo-sub-2247531%22%20slang%3D%22en-US%22%3EMacro%20gives%20compile%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247531%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20friends%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20Excel%20Macro.%20I%20recorded%20a%20very%20simple%20macro%20(typed%20'a'%20in%20cell%20A1)%20and%20saved%20it%20for%20testing.%20When%20I%20run%20the%20macro%2C%20it%20gives%20me%20a%20compile%20error.%20I%20suppose%20there%20is%20something%20wrong%20with%20my%20settings.%20The%20image%20is%20attached%20below.%3C%2FP%3E%3CP%3EPlease%20help%20me%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22patilosophy_0-1617214170826.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F268693i31A747392DD8E277%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22patilosophy_0-1617214170826.png%22%20alt%3D%22patilosophy_0-1617214170826.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2247531%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2247616%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20gives%20compile%20error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2247616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782347%22%20target%3D%22_blank%22%3E%40patilosophy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWeird!%20Try%20changing%20the%20macro%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Macro1()%0A%20%20%20%20Range(%22A1%22).Value%20%3D%20%22a%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Dear friends,

 

I am new to Excel Macro. I recorded a very simple macro (typed 'a' in cell A1) and saved it for testing. When I run the macro, it gives me a compile error. I suppose there is something wrong with my settings. The image is attached below.

Please help me out. 

patilosophy_0-1617214170826.png

 

9 Replies

@patilosophy 

Weird! Try changing the macro to

 

Sub Macro1()
    Range("A1").Value = "a"
End Sub
Dear Hans,

Your code is working well. But why is my macro not being recorded correctly? I searched for a solution everywhere but this problem seems unique to me.

Please check this short video where I recorded my macro.
https://youtu.be/clYd5nc2fN0

Thanks,
Pratik

@patilosophy 

As I wrote in my first reply, it's weird. The macro recorder is far from perfect, but I haven't seen it mess up completely like this. It's clearly not your fault!

@Hans Vogelaar How do I correct this? Is there a reset option in Excel? 

I got a research paper pending due to this. Any solutions helps!

 

Regards

@patilosophy 

You might try recording another macro to see if the same problem (or a similar one) occurs. If so, you'll have to write your macros from scratch...

Not a single macro that I recorded is working. Guess I'll have to take the hard way of writing the code which I am clueless about. Is there a customer complaint mail id where I would get a response from MS?

@patilosophy 

You can click File > Feedback > Send a Frown, but you won't get a reply...

best response confirmed by patilosophy (Occasional Contributor)
Solution
It looks like someone else had this issue and was able to fix it by uninstalling/reinstalling office. That would be the only thing I could suggest as you're certainly not doing anything wrong.

https://techcommunity.microsoft.com/t5/excel/excel-macro-recording-generate-wrong-vba/m-p/190857
This was the last option but it worked. Thanks!