none
VBA code *text* disappears, but code is still there RRS feed

  • Question

  • Hey folks,

    Had a spooky problem with a bunch of Excel 2007 (SP3) VBA modules where the text of most of the code disappeared in the editor window (except for the option explicit statement!), but I could still see the modules in the project browser, I could export the modules and see the code, the routines were still visible in the macro selector, and I could still run individual subs in the modules.  I opened and closed the VBA editor several times, and opened and closed the workbook and Excel several times.  What I finally discovered was that if I hit a breakpoint that was already in one of the modules, the code for that module would start to reappear.  Finally, the problem just went away for some reason.

    Please note that this is not a case of macros going into the Personal xlsm file.  In fact, the code text in both the workbook I was working in *and* my Personal workbook modules also disappeared in the editor.  Per the few other references I found to problems like these, the files are not on a lan, and the module I was working on did not resave the workbook.

    The problem started with an distinct editor glitch.  I started to type an "If" statement and typed in "If" and then a space.  The "If" turned red as if there was a syntax error, and the space vanished.  The next character I typed changed the "If" back to dark text and appeared immediate next to the "f", i.e., I could not put a space in the statement.  I can't recall if I closed and reopened the editor immediately after trying that a few times or not, but it was after this glitch that the text in the subs and functions vanished -- except, oddly, for the "option explicit" instruction at the top of the page.

    I tried turning off a number of the editor "tools:options" assist options, and as I mentioned, I closed and opened both the editor, the workbook and EXcel several times, but it wasn't until I hit that breakpoint that the code started to become visible again.

    I could swear I had run into this editor glitch with the red text and vanishing spaces maybe once or twice before in Office 2003 VBA over the past ten years or so (there was an "Oh, not that again" reaction), but I don't recall which Office app I saw it in (most likely it would have been in Excel or Word), and I'm not sure I remember if I had the text go hidden like it did this time.

    Any similar experiences or suggestions would be appreciated.

    Thanks

    Friday, August 10, 2012 3:41 PM

All replies

  • In the Visual Basic Editor, select Tools | Options...

    In the Editor tab, in the Window Settings section, make sure that the check box "Default to Full Module View" has been ticked, then click OK.


    Regards, Hans Vogelaar

    Friday, August 10, 2012 3:58 PM
  • Hi Hans,

    Thanks for the suggestion, but I was already in Full Module view when the editor glitch happened and when the code text started vanishing.  I don't know if it was a factor, but it was actually one of the things I had tried turning off before the text started to reappear.   Once the text started coming back I went back to Full Module view without any problems.

    Thanks

    Friday, August 10, 2012 4:23 PM
  • Try this, with the normal precautions about editing the Registry

    Close all Office/VBA apps

    Rename
    HKEY_CURRENT_USER\Software\Microsoft\VBA\6.0\Common
    Rename with _bak or say by adding say today's date, eg
    \Common_120810

    You could also "Export" the tree before renaming (Right click on Common)

    Restart your Excel and open the VBE

    Back in the Registry press F5 to refresh and you should see a new \Common tree with the default settings. Reset any settings as preferred in the VBE

    I vaguely recall what you describe a very long time ago, not sure if this reset will help but worth a try.

    Peter Thornton

    Friday, August 10, 2012 4:42 PM
    Moderator