Can not access VBA editor

[GazMathias]

Hi,

I've come accross a really frustrating problem in which I can not access the VBA editor for any application in the Office suite using my profile. All code runs fine.

I am using Office 2016 Professional Plus and I am encountering the following:

In Excel if I invoke the editor with Alt + F11 I get the 'Out of Memory' exception, on a new Book after a fresh boot with nothing else running and with all add-ins disabled.

In Excel if I click Edit on any macro in the list I get the 'Unexpected Error; quitting' exception.

If I attempt to edit any procedure in Access I get the 'Unexpected Error; quitting' exception either clicking on a module or the buttons next to events.

In Outlook if I click the VBA editor button, nothing happens.

If I attempt to create a new macro in Outlook or Word I get the 'Unexpected Error; quitting' and 'Out of Memory' errors one after the other.

Things I have tried:

1) Disabled all add-ins.
2) Rename personal.xlsb.
3) Repair Office.
4) Completely remove Office, reboot, reinstall office.
5) Remove HKCU VBA 6.0 common keys (backed up then restored after testing).

There is no code modules common to all of the Office suite that I run.

If I log on to the machine with another user I can use the VBA editor as normal but on my profile I can not use it at all which makes me think there is some common VBA IDE user critical preference file / reg entries somewhere that I am not finding a clue to with my research efforts.

I made no changes to any of my code when this started occuring, which happened whilst I was working in Excel.

Have any of you guys experienced this before or have any ideas about the startup sequence of the IDE to know where it gets its settings, other than the HKCU common keys?

Any help would be much appreciated.

Thanks,

Gaz

[GazMathias]

Hi,

Managed to solve this myself so I'm posting the solution here for anyone else pulling their hair out for half a day like I was.

Since the editor appeared for another user on the same computer I exported that user's entire registry key and all subkeys / values from HKEY_CURRENT_US ER/Software/Microsoft/VBA and imported them into my profile.

It now works, crisis averted.

For anyone encountering the same problem the steps to complete this are:

1) Log in to a profile that works.
2) Hit Win+R, type regedit and hit Enter.
3) Navigate to HKEY_CURRENT_US ER/Software/Microsoft/.
4) Right-click the VBA key and click Export.
5) Give the file a name and save it to somewhere you can access later from the faulty profile.
6) Log in to the faulty profile.
7) Repeat steps 2 to 4 above to export the faulty keys.
8) On the registry editor's File menu, click import.
9) Import the file you exported in step 4.

If you can not access the registry due to a GPO rule or you can not log in as an administrator / another user then feel free to show your IT support this post.

Hope that helps someone!

Gaz

[NeoPa]

Hi Gaz.
While I appreciate what you've done here, a simpler approach, and one more generally used as it's safer, is to reinstall the software. MS Office has a Repair feature in its installation so that can be done relatively easily.

Personally, as I'm happy enough to play around in the Registry - of both the User Profile (HKCU) & the Machine (HKLM), I'd tend to use your approach. However, with the tendency of most people to read one thing and do something either slightly, or completely, different it is generally not wise to include any sort of Registry manipulation without very strong warnings associated about it all being very dangerous and that they risk making their computer unusable if they get it wrong. I expect you've seen a number of such warnings in your internet travels.

So, while I'm very happy that you found a clever solution, I've reset it as the Best Answer because it's risky and not the answer most professionals would recommend in those circumstances.

[Rabbit]

It sounds like they did try all that before resorting to the registry.

[NeoPa]

Hi Rabbit.

That's a fair point. However, with the greatest of respect to Gaz who's only posting to be helpful I'm sure, this comes with no warning and, possibly more seriously, it comes without identifying either where the important change is found or what other items within that whole area might be adversely affected by such a blanket change.

The VBA Key, for such it is, not a Value that can be seen by navigating to HKEY_CURRENT_US ER\Software\Mic rosoft as instructed, contains (typically) one or more version numbers and at least a Common Key under each. There can be more but needn't be. I believe version 6.0 goes up to Office 2003 and 7.0 for beyond that but I don't have all versions so cannot be sure.

If I were about to follow these instructions I would want to try simply renaming HKEY_CURRENT_US ER\Software\Mic rosoft\VBA\7.0 (Depending on the version I'm interested in of course.) before doing the Office Repair. I suspect, in such a circumstance, that the repair would recreate the default version of that Key for me. I would then have the added bonus of being able to compare them visually (There are not more than twenty or so Values in each ...\Common Key, so not too complex for someone who's comfortable with the Registry.) and seeing what was actually wrong.

I should add that for someone who isn't comfortable in the Registry my advice would be to leave it well alone and possibly draw the attention of an IT helper to this thread.

As always whenever working within the Registry, make sure you have a backup of any area within where you expect to make any changes. That will not protect you from all problems but at least it will as long as your changes don't stop your computer from booting up. Renaming an existing Key is all well and good but it should still only be attempted after making a safety backup.

I certainly wouldn't want any unsuspecting visitor to find only Gaz's post without adequate warning or a clear answer to exactly how to go about making such a change safely.

[NeoPa]

Not that Gaz should feel in any way that this isn't valuable information and well worthy of sharing. I learned a bunch just checking up on it. The fact that VBA comes under HKEY_CURRENT_US ER\Software\Mic rosoft rather than any version of HKEY_CURRENT_US ER\Software\Mic rosoft\Office is a very interesting surprise to me. That could prove very helpful to many I'm sure, so well done Gaz.

[GazMathias]

Actually repairing Office is not necessary.

Using a restored backup into another standalone VM I used process monitor to analyse registry ops from Excel.exe

It first does a RegQuery to see if the key 7.1/common exists (for my version) and loads the values if found.

On a subsequent monitor test I completely removed 7.1/Common and it did not recreate the keys on initial load. Only when I changed a font setting did I start seeing activity and the key with all 27 values suddenly appeared in the registry.

Clearly the editor is using the values therein to override internally defined defaults and from then on uses an all in approach - if you have made any amendments to the layout it will from that point on load all of those settings from the common key.

Odd that it doesn't try-catch any of that if those keys become corrupt. It's probably something absurdly simple, too, like a failed cast.

I have seen posts telling users to rebuild their profiles for problems like this one and that is just completely overkill.

For a user not willing or able to modify the registry then performing a system restore should revert the entries to their working state. However, I do not know how the problem initially occured so who's to say the restored entries wouldn't corrupt again?

I shall put the blame for this one on quantum machanical wierdness.

What I can tell you is that when you have stakeholders who have wants and needs on your back badgering you and your tools just aren't working then much stress and panic ensues. What I did got me working again and I just hope this thread will save someone from all of that and get them working, too.

In hindsight, I could have just restored my VM from backup to begin with but rebooting, repairing and reinstalling Office, having a quick hunt round the net and doing a search in the registry for VBA all seemed quicker at the time.

Now, we can put the obligitary "Be careful in the registry" warnings all over this post if needs be but can I suggest that we condense this thread into something more coreherent and useful and not the waffly thread that it has become? I don't think that is useful to anyone.

Maybe it can become an article instead and we can collectively contribute over time any and all odd behaviours with the IDE, its settings and thrown exceptions and detail any and all fixes required for whatever flavour if Office they are using.

Most posts on this kind of subject refer to problems with specific procedures the users have created.

In direct response to your posts. I use the words Key and Value correctly in my instructions. I specifically say to delete the entire VBA Key and all of its subkeys and replace them all with those equivalents from another profile on the same computer and thusly, the same installation and version of Office.

I also detail instructions to back up those keys by exporting them before importing the other user's keys in step 7.

None of that is necessary, simply removing the Common key associated with your version is enough to reset the editor back to default (and working!).

You can then refer to the existing values therein to reset fonts and layouts if you want.

TBH, it still bugs me that after all these years that IDE has not evolved to modern IDE editing standards and I can't replace it with something else! I mean really, how difficult would it be to hook into Visual Studio?

Gaz

[rollerbladegirl]

You guys are great. All of you. I really enjoy reading your discussions. Hours ago I had intended to try to find old service packs 2 and 3 for SQL 7.0 and ended up on this site (again!) and here I still am. You guys are great reading.

This one was intense. Thanks.

[NeoPa]

GazMathias:
I have seen posts telling users to rebuild their profiles for problems like this one and that is just completely overkill.

So speaks one with little grasp of how so many can struggle so deeply with anything technical - as well as the responsibility of giving advice that can never lead to problems.

It may well be hard to imagine how people can ever get something so relatively straightforward , so wrong, yet in a world of so many millions (Billions even) - many of whom were born before any sort of computers were even common in households and have struggled ever since - there are still a very large number who can manage to cause serious damage following what, to you and me, may seem very straightforward instructions.

In this situation, for example, there are no instructions on how to identify which numbered branch Key under the HKEY_CURRENT_US ER\Software\Mic rosoft\VBA\ Key is the relevant one. Bizarrely enough, there is a type of person I've come across on too many occasions to be unaware of, who, when confronted with something they don't understand, instead of deciding it's not safe to continue, will take a wild guess and simply hope that it will work in spite of that. For many that means simply selecting a Key (or even a Value as most technical people often don't know what either means) and deleting it.

Until you've seen this happen time and time again it's very easy to assume this is simple scare-mongering. Unfortunately I've seen such behaviour on many many occasions. That doesn't stop me being surprised and bemused that it ever could happen, but it continues to happen whether I understand or not.

Don't get me wrong. I see the value of what you're saying. It's actually very helpful and I can't help but be impressed that you were competent enough to trace this down to the nitty-gritty and thus give a very accurate explanation of what's going on. Not something many could have managed - even among the technically gifted.

I simply believe that you fail to appreciate the enormity of the gulf of understanding between those who deal with computer technology naturally and easily, and those who have never been comfortable with it in any form at all. Of which there are still a very large number in the world.

GazMathias:
Maybe it can become an article instead and we can collectively contribute over time any and all odd behaviours with the IDE, its settings and thrown exceptions and detail any and all fixes required for whatever flavour if Office they are using.

Maybe indeed, but we would need it to be properly formatted and written in such a way as to be very clear and easy to follow - with, as you say, the mandatory warning in bold letters to the effect that playing with the registry comes with risks that the reader has to bear responsibility for themselves.

[GazMathias]

Hi NeoPa,

I still don't feel that a proper solution exists for this problem (aside from a recent restore point) and as a developer I find the fact that these steps are even necessary quite alarming.

NeoPa:
So speaks one with little grasp of how so many can struggle so deeply with anything technical - as well as the responsibility of giving advice that can never lead to problems.

Far from it. I am the end of line support contact for around 70 on site users and thousands of corporate client employees so I know how to talk to them.

Your assumption is that no techincal users visit this forum and that is simply not true. This scenario is technical and so are its 'fixes'.

In this situation, for example, there are no instructions on how to identify which numbered branch Key under the HKEY_CURRENT_US ER\Software\Mic rosoft\VBA\ Key is the relevant one.

You are quite right. That is why I replaced the whole lot with the equivalent keys from the same computer. Same computer, same install and therefore, same keys. I only documented what I did to solve a problem that was affecting me.

I have offered to correct the post and add the obligitary warnings and I have detailed subsequent findings of my own investigations and offered a simple solution for the non technical (system restore). In my own solution I suggest that anyone encountering this problem direct IT support to my post for some scenarios and that can easily be amended to direct anyone not comfortable in the registry there too. I simply don't understand why I am receiving so much hostility from a professional such as you. Instead of personally attacking me why didnt you just PM me and ask me to flush out my solution for the non technical?


Gaz

[NeoPa]

Hi Gaz.

I feel there's some misunderstandin g here. While I may see things a little differently from you, I have great respect for your technical knowledge and all you post. Yes - I see adjustments that may be required for perfection. I still haven't seen a post that I believe would be appropriate for a Best Answer on its own, but I find the value of this whole thread to be enormous. Not just the original answer but also your follow-ups providing even more technical understanding. I've certainly learnt from this and been impressed by your getting even more helpful lowdown on the subject when it was still a little unclear why it behaved as it did.

You spoke (wrote) earlier about considering a new (or even updated) post that would be a perfect answer. In light of that I thought it was important to highlight areas that would need to be understood in order to save you wasting your time. I was aiming for constructive guidance. Belittling you publicly is no part of my agenda. Believe me - it would hardly reflect the respect that's building up for your posts.

[GazMathias]

Hi NeoPa,

I am humbled by your kind words. I will have a think about how best to proceed. The original intent with this post was to direct anyone with the same query here and I was careful to choose a title one might search for and, I hope, I was thorough in detailing the problem symptoms using verbatim error messages and the steps I initially tried to attempt to solve it.

I am kicking myself that I looked at 6.0/Common initially but not 7.1!

[NeoPa]

Frankly this is an unusual thread. Rarity gives it extra value in a way. Where else would someone find help for such a predicament?

And yes. If you need it confirming, though it seems obvious to me, you did a good job in a lot of respects. Care was obviously taken to itemise everything clearly and the effort put in is a good example of how to go about explaining the situation. I always try to make any threads I create into good examples of how best to post in order that others can have good examples to go from and yours is in a similar vein.

[SmithAnchorWork]

If I had difficulty accessing a VBA editor, and if I needed to edit VBA via an editor, I expect that I would install VB6 (with sp5 not sp6) and attempt to edit the VBA with that. Since I might have directly experienced, years ago, that VBA was a stripped down version of VB6, I think that I would try that.

I do not remember, but maybe VB6 sp5 has the ability to save in VBA form. If that is so then that might be part of a solution. If not, then maybe I could edit the VB6 sp5 code to strip it down to VBA usability.

I seem to remember that VBA might be able to call a VB6 sp5 dll. That might work.

Maybe VB6 sp5 could import the VBA and it could be edited that way and exported back in VBA form.

Maybe subclass Excel (2016?) with C++11 (I did not say VC++) and then control Excel from the outside bypassing a few limitations that way.

If the previous did not work, then I might uninstall all offending software (after backing up the directories) and then reinstalling them.

If that did not work, then I might format the hard drive (after reasonable backup of data) and install the operating system and software from scratch again.

The last fix has many times fixed problems. I would suggest backing up your entire hard drive with "EASUS ToDo Backup" maybe once per month. It can be intensely helpful.

You guys are still great to read.