I am trying to write some VBA code in the built-in VBA editor in Excel 2003. A strange thing keeps happening. As I am typing out code, the editor autmatically removes the last space that I type. It is almost as if the auto-syntax feature has gone mad. I have disabled the auto-syntac option and it still makes no difference. For example, I want to type out the line "Dim i As Integer". As I start typing this line out I get to "Dim i" and a second later, it strips out the space automatically and the line becomes "Dimi" which of course means that I have to go back and reinsert the space. This behaviour is making it impossible to write code inside the editor and one has to write the code in Notepad and copy and paste it in. Does anybody have any ideas about what's going on here?
Many thanks,
John
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
The most common locations of Add-Ins folders are:
C:\Program Files\Microsoft Office\Office11\Xlstart
C:\Program Files\Microsoft Office\Office\Xlstart
C:\Documents and Settings\User Name\Application Data\Microsoft\Excel\XLSTA
C:\Winnt\Profiles\User name\Application Data\Microsoft\Excel\Xlsta
The folder that is specified in the "At startup, open all files in" box on the General tab in the Options dialog box.
Look for any file that might be the add-in. After quiting Excel, move the targeted file to another location (do not delete it until sure it is the desired add-in.) Start Excel and confirm that the add-in is removed. If not, return the moved add-in to the original XLSTART folder and try again with another.
Some add-ins are not stored in XLSTART folders and are loaded into Excel using Registry entries. If the correct Registry entry can be found then it can be used to locate and delete the add-in. The Registry entry should also be removed but only after making a backup of the Registry and confirming that the add-in identified is the correct add-in. The most common Registry locations where add-ins are located are:
HKEY_CURRENT_USER\Software
HKEY_CURRENT_USER\Software
HKEY_CURRENT_USER\Software
Kevin
Hi Rory,
I tried opening up Excel in safe mode and it still does it. I have took a screeshot of my add-ins list in the main Excel window and I have attached the screenshot to this posting.
Hi Kevin,
I looked in all those paths and they either do not exist on my computer or else there are no files in the XLSTART folder. I have attached a screeshot of my current add-ins below, most of which are not checked, just the normal ones at the top. In fact, I tried unchecking all of them and the problem still persists.
Thanks,
John
Screenshot of Add-Ins box in the main Excel screen
Hi Kevin,
The only thing I see in the project window is "VBAProject(Book1)". This is after having opened up Excel in safe mode and unchecking all add-ins in the main Excel window. There is nothing that I can see that is loaded up in either the add-in section or by Excel when it starts up. In Tools\Options\General, the field "At startup, open all files in:" field is blank.
Thanks,
John
Hi Kevin,
I don't have Google Desktop or the full Adobe Acrobat. I have the acrobat reader but I can't think that this would cause this to happen. I tried uninstalling the Bloomberg add-ins from the Add/Remove Programs List but it doesn't make any difference either. This is very strange, its almost as if there's an invisible hand on my backspace button that just deletes any spaces that I type in. This doesn't happen on any other program on my computer, just the Excel VBE. It doesn't even happen in the main Excel screen when I am typing a formula into a cell.
Thanks,
John
What is happening is the VBE editor is deciding to do a syntax check on the line of code and, as part of that process, it trims any trailing spaces. You can see the same result by pressing the ENTER key. I have only seen the VBE editor do this when I have have timers pop while editing the code. Certain states do not allow timers to pop such as when editing a cell or design mode. So the question is what is causing the VBE editor to do a syntax check. At this point I am stumped.
Can you detect any sort of frequency? In other words, about how long does it take to happen once you have typed that trailing space?
Kevin
Hi Kevin,
I detect a slight lag from the moment I press the space bar to when the space is deleted. It is about 0.5-1.0 sec on average so it doesn't do it straight away. The timer theory sounds very plausible but for the life of me I can't see what else is running in Excel at the same time. Like I said, if I open up Excel in safe mode and start with a brand new spreadsheet, there should be nothing loaded up into memory beforehand. I can't see anything else in the project window other than the new spreadsheet project. Is there a way I can recreate a profile in Excel in the same way that you can recreate a mail profile in Outlook?
Thanks,
John
>Is there a way I can recreate a profile in Excel in the same way that you can recreate a mail profile in Outlook?
No. The Excel profiles are based on Windows user profiles. If you can create a new Windows profile that's a great test.
I'm tempted to give you my list of steps I use to repair Excel installations but my gut tells me it's something else running on your machine that is causing the Excel VBE to compile. Have you tried editing VBA code in Word, Outlook, or Access? Have you run some virus and spyware software like Lavasoft's free Ad-Aware tool?
If you feel so inclined you can run through some of these steps for repairing Excel installations. But I believe there is a low probability any of them will work.
---
On rare occasions Excel can become unstable and start exhibiting unwanted behavior, crashing, or not even starting. Below are several steps to take to try and get Excel back to a stable state. Try to reproduce the problem after each step and, if resolved, there is no need to continue to the next step.
Step 1 - Install Updates
Go to http://office.microsoft.co
Step 2 - Repair the Excel Installation
This option is only available in Excel 2000 and later. To repair the Excel installation, start Excel and choose the menu command Help->Detect and Repair. The product CD may be required.
Step 3 - Remove/Reset the Tool Bar Configuration File
Reset the toolbar settings file by deleting the Tool Bar Configuration File while Excel is not running. This file is usually located in the folder
C:\Documents and Settings\Your Login Name\Application Data\Microsoft\Excel
and is named "Excel*.xlb".
Step 4 - Reset the Registry Entries
Instruct Excel to reset the Excel registry entries. Do this by opening the Start menu and selecting Run. Enter "Excel.exe /UnRegServer" and click OK, wait for the process to finish, then enter "Excel.exe /RegServer" and click OK. The full path to Excel may have to be entered for the command to work.
To get the command to work the full path to the Excel application may be required. If so, the path usually looks like:
C:\Program Files\Microsoft Office\OFFICE\Excel.exe
or
C:\Program Files\Microsoft Office\OFFICExx\Excel.exe
where xx is the Excel version: 10 for 2002, 11 for 2003, and 12 for 2007. So the commands for Excel 2003 are:
"C:\Program Files\Microsoft Office\OFFICE11\Excel.exe"
"C:\Program Files\Microsoft Office\OFFICE11\Excel.exe"
Step 5 - Clear the Option Settings
Run RegEdit (Start->Run, enter "regedit", and press ENTER) and navigate to HKEY_CURRENT_USER\Software
Step 6 - Remove Custom Add-Ins
Remove any custom add-ins from the XLStart folder and the folder that is specified in the "At startup, open all files in" box on the General tab in the Options dialog box. A custom add-in is any Excel workbook created by the user and saved as an Add-In in an Excel startup folder.
Step 7 - Reinstall/Remove Third Party Add-Ins
This step involves looking for and removing third party add-ins until all add-ins have been dealt with and either removed or determined to not be the cause of the problem. Adobe Acrobat and Norton Anti-Virus are known to have conflicts with Excel. Upgrading to the latest version of these programs usually solves most problems.
The easiest way to remove an add-in is to, from within Excel, choose the menu command Tools->Add-Ins. In the Add-Ins available box, clear the check box next to the add-in to unload and click OK. To remove the add-in from the menu, restart Excel.
To completely remove an add-in use the add-in manufacturer's uninstaller. Uninstallers are found in the Add or Remove Programs control panel.
Without an uninstaller, completely removing an add-in from a computer is possible but not easy. First look in all known XLSTART folders. The most common locations of these folders are:
C:\Program Files\Microsoft Office\Office11\Xlstart
C:\Program Files\Microsoft Office\Office10\Xlstart
C:\Program Files\Microsoft Office\Office\Xlstart
C:\Documents and Settings\User Name\Application Data\Microsoft\Excel\XLSTA
C:\Winnt\Profiles\User name\Application Data\Microsoft\Excel\Xlsta
The folder that is specified in the "At startup, open all files in" box on the General tab in the Options dialog box.
Look for any file that might be the add-in. After quitting Excel, move the targeted file to another location (do not delete it until sure it is the desired add-in.) Start Excel and confirm that the add-in is removed. If not, return the moved add-in to the original XLSTART folder and try again with another.
Some add-ins are not stored in XLSTART folders and are loaded into Excel using Registry entries. If the correct Registry entry can be found then it can be used to locate and delete the add-in. The Registry entry should also be removed but only after making a backup of the Registry and confirming that the add-in identified is the correct add-in. The most common Registry locations where add-ins are located are:
HKEY_CURRENT_USER\Software
HKEY_CURRENT_USER\Software
HKEY_CURRENT_USER\Software
HKEY_CURRENT_USER\Software
Step 8 - Completely Remove and Reinstall Excel/Office
This is the most drastic measure and, if done correctly, is the last possible step short of tossing the box out the window. The idea behind completely removing an Office/Excel installation is to purge any and all files and registry entries that might be causing the problem. The normal Office/Excel uninstaller function is not designed to do this. Each version of Office/Excel has different procedures for complete removal. These are listed by version below.
Office 2003
There is no official documented technique for completely removing Office 2003. Open the Add or Remove Programs control panel, locate Microsoft Office, and click Remove. Additional steps follow below to manually purge Office remnants.
Office 2002/Office XP
There is no official documented technique for completely removing Office 2002/Office XP. Open the Add or Remove Programs control panel, locate Microsoft Office, and click Remove. Additional steps follow below to manually purge Office remnants.
Office 2000
Run this cleanup utility: http://support.microsoft.c
Run this cleanup utility: http://support.microsoft.c
Office 97
Follow these instructions: http://support.microsoft.c
Run this cleanup utility: http://support.microsoft.c
After completing the above instructions for the specific version of Office, do the following:
Run this cleanup utility to clean up Windows Installer remains for Office: http://support.microsoft.c
Open the Registry (click Start and select Run, enter RegEdit and click OK) and navigate to HKEY_LOCAL_MACHINE->Softwa
Open Windows File Explorer, navigate to C:\Program Files and delete the "Microsoft Office" folder.
Download, install, and run CCCleaner (http://www.ccleaner.com).
Reinstall Office.
Kevin
I have two things we can check.
AutoRecover Settings
Choose the menu command Tools->Options, navigate to the Save tab, uncheck the "Save AutoRecover info every..." check box, click OK. See if the problem still occurs. If not, reset the setting to it's original value. If it does resolve the problem, re-enable AutoRecover and try setting the period to 10 or 15 minutes.
COM Add-Ins
This is a little trickier because we have to modify the menus first. Right click in the toolbar area and choose Customize, navigate to the Commands tab, select Tools from the left list, scroll the right list until "COM Add-Ins" is found (a little past half way), drag the "COM Add-Ins" command to the main menu bar and the Tools menu and then down to just past the Add-Ins menu, release the mouse button to drop the command there, and click Close on the dialog. Choose the menu command Tools->COM Add-Ins and see if any entries are listed in the dialog. If they are, uncheck them, quit and restart Excel, and see if the problem still occurs. Return to the COM Add-Ins dialog and re-install the COM Add-Ins removed earlier.
Kevin
Another similar problem:
http://www.experts-exchang
Kevin
Hi Kevin,
What happened is that due to other unrelated problems, I had to reinstall Microsoft Office and now the problem has gone away. Even though I am not sure what the actual problem was, I can code again properly in Excel VBA. Thank you very much for your sterling efforts to help. Much appreciated.
Best Regards,
John
Had the same issue in Office 2007 Enterprise > Excel.
tried disabling auto-syntax check & auto indentation.
tried setting the workbook to manual calculation
this is a fresh Vista install, updated to SP2. fresh office install all updates installed. i tried rebooting. I tried going in to control panel & Changing the installation to uncheck Excel to effectively uninstall excel, then later re-checking it to re-install it.
I ran Excel options > Resources. Diagnostics reported it repaired 1 issue, but the constant compiling of VBA remained. there is no such problem in Word/Access.
I'm now using the Control Panel > Change Installation > Repair.
I have an ugly temporary solution: put a single quote after your cursor, this will prevent the compiler from removing the spaces.
couldn't find anything on Office support. The article that helped me most was:
http://office.microsoft.co
Hello Folks,
I think I may have stumbled across a resolution for this issue. The same thing just happened to me while I was in the middle of coding a project (literally). For a minute I thought maybe I hit a hot key or something, because the VBE started auto-deleting trailing spaces and auto-validating comments. However, the issue seemed to go away when Design Mode was turned ON.
Now, I don't fully understand how, but I think this had something to do with the "state loss" warning that vba gives when you are running/debugging and editing code. That is the only thing that happened during my coding session that was out of the ordinary (this is the first time I go the "state loss" warning). Honestly, I don't even remember the sequence of actions that lead to this warning or how I closed out the warning. I hadn't given the message much thought until I ran into this auto-delete issue and started searching for a resolution.
As far as the fix, closing excel and reopening DID NOT resolve this issue. However, once I remember that I had rec'd the "state loss" warning mentioned above, all I did was Run the procedure that I was working on (with Design Mode turned OFF). After the Sub ran through to completion, I went back into the editor and it started to behave normally again.
I realize this is a vague resolution, but since I seemed to stumble across something, I thought it would be prudent to post my findings here. If anyone can give more clarity around the "state loss" warning that I have mentioned here, that would help. As of right now I don't even know how to re-create the warning to verify if this is the root cause.
Regards,
Shaun
Business Accounts
Answer for Membership
by: zorvekPosted on 2008-05-29 at 01:10:00ID: 21667180
You have an event timer going off somewhere in your code. Do you execute an Application.OnTime statement anywhere in the project?
Kevin