VBA Mystery

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    The laptop that I have was given to someone who had a VBA course. Something unknown to the user/myself was done to this laptop during the course and now it can't do a simple recorded macro like it used to. It comes up with a "compile error". I've done this macro several times, along with other macros and it just doesn't work.


    Please see this YoutubeVideo so you can see for yourself.

    External Content youtu.be
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.


    I've tried to see if there any autoexec macros, hidden macros listed in personal macros (unhide workbooks), under macros there are non listed. I have another version of Excel on the same computer and macros work just fine.

    I'm totally stumped. Please help. I have to teach recording macros next week and I can't.

    Roy & Dave,


    Thanks so much for replying.


    As you can see from the video:

    1) Simple formatting macro has been recorded.

    2) A compile error occurs when it should not.
    3) No VBA has been manually typed.


    Disregard last question - Question revised: Why is there a compile error?

    The compile error is because there is code missing.


    Which row is highlighted when the error occurs?


    Can you uninstall Excel and re-install it?

    There is something that has been adjusted, turned on, or perhaps a macro created and ran during that VBA course that is causing this but I don’t know what.


    When learning VBA, is there something that is taught in beg or interm level that would adjust the settings so that a simple macro can’t be created/recorded properly??


    This is horrific for me —I’ve got to teach a class this week.

    As far as I know there is nothing that can be done to the Macro Recorder with VBA.


    The compile error is because there is a missing With Statement. I'm not sure what steps you followed to record the macro though. I've tried to duplicate them but don't get code like yours.


    Why can't you re-install Office?

    To see the steps:


    External Content youtu.be
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

    Does the same thing happen in Safe Mode? (start Excel with the Ctrl key held down)


    If so, I'd start with a Repair of Office through Control Panel before resorting to a full reinstall.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    I suggested a full re-install based on reading this. I also saw suggestions that recording a macro in a different instance of Excel than the one that the macro recorder was opened in might cause the problem.

    Hello:

    When I watch the video I do notice one odd thing. The project window shows that you've created a module BUT the "Sheet1" is what is highlighted. So the code you're looking at is in Sheet1 and not the module. Check to see what has been produced in the code window in both places. Clear both the code from sheet1 and module1 and try recording again. I wondering if the ".Header. = 12" is a line in the Sheet1 code window that's causing the problem.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login