[ACCEPTED]-VBA editor auto-deletes spaces at the ends of lines-editor

Accepted answer
Score: 39

I had this exact problem and the following 2 worked for me.

  1. Click the Microsoft Office Button, and then click Excel Options
  2. Click the Add-Ins category
  3. In the Manage box, click COM Add-ins, and then click Go.
  4. Look for an add in called 'Load Test Report AddIn' then uncheck it
  5. restart excel

This addin is installed with 1 VS2010 Beta2

Score: 13

In Excel 2010, toggling Design Mode button on the Developer Ribbon 1 Tab solves the problem for me.

Score: 6

I've definitely had that issue before, where 8 the vba editor would format as I was typing 7 (not just when I went to another line). For 6 me, it seemed to be related to a Microsoft 5 Web Browser control that I had in an open 4 workbook. When I took out the web browser, the 3 VBA editor started acting normally again. I 2 have no idea why that worked, but it did. Now 1 I avoid using that control in my workbooks.

Score: 5

Something is causing your spreadsheet to 8 recalculate while you are in the VBA Editor 7 and this 'compiles' your code and thus strips 6 the spaces. You need to stop the cells recalculating 5 while you are editing. Turning the calculation 4 to manual in the spreadsheet. Tools > Options 3 > Calculation should do the trick.

I noticed 2 this when I had cells recalculating thanks 1 to a DDE connection.

Score: 4

There is sort of a way to turn off the auto-"correction" in 4 the VBA Editor.

Tools Menu -> Options -> Editor Tab -> Clear 3 the Auto Syntax Check box

Sadly, this won't solve all your 2 problems, as the VBA Editor tends to have 1 a mind of its own, for better or worse.

Score: 2

Taking it out of design mode fixes it every-time 5 on vba2003 - its was driving me crazy at 4 first ... i did notice it might have something 3 to do with the web control but when i take 2 it out of design mode it works fine ... just 1 toggle that to get it to behave :)

Score: 2

I have had this same problem several times 22 recently, and has driven me crazy.

After 21 reading this post all the different fix-ups 20 mentioned (none worked for me), I recalled 19 I have been playing lately with Internet 18 Explorer Control, opening Internet Explorer 17 from VBA.

This is mentioned in some of the 16 answers in this post.

This got me in the 15 right path and, in order to fix it, I had 14 to first delete all iexplore.exe instances 13 (alt+control+delete), closed Excel and open 12 excel again. (iexplore.exe had been opened 11 invisible from the VBA code, and I didn't 10 know they were running)

That fixed the bug.

I 9 have been programming quite heavily with 8 VBA for over 4 years, and never had this 7 problem.

Just a couple of weeks ago I started 6 using IE control, and I started getting 5 this problem...so in my case I can only 4 assume the bug is directly related to the 3 IE control.

I am running both excel 2003 2 and 2007 in same PC at the same time, and 1 the problem only happens with excel 2007.

Score: 1

Turn off Tools > Options > General > Background Compile. This solved it for me.


Score: 1

None of the above for me. I had a Application.OnTime timer 3 that fired every second and this triggered 2 a recalculation of something: commenting 1 it made my day.

Score: 1

This has started happening to me recently 26 after adding a Timer event to an Access 25 2007 form. The VB editor "finalizes" the 24 current line (as if you had moved the cursor 23 off of the statement; I have auto syntax 22 checking off) each time the event fires 21 (initially I had it set to 1 second, now 20 it is set to 5 seconds, and the behavior 19 scales accordingly). In order for the event 18 code to execute, the application has to 17 ensure that it has been compiled. Presumably, the 16 editor needs to be in a "sane" state to 15 do this, as it probably has to check for 14 code dependencies among modules. Note that 13 the behavior occurs regardless of whether 12 any dependent code is actually loaded into 11 the editor at the time. Note also that 10 resetting the execution state doesn't affect 9 the timer event firing. As a practical 8 matter, one could close the triggering form, switch 7 it to design view, set a breakpoint in the 6 triggered code, or increase the timer interval 5 while working with the editor.

In contrast 4 to abhishek's comment, changing the settings 3 of Tools/Options.../General/Compile On Demand 2 and/or Background Compile did not affect 1 the issue.

Score: 1

If it helps, none of the previous answers 7 solved mine. Only solution appeared to 6 be to close the xls file and reopen it. Frustrating 5 to have to do every 30 mins but at least 4 it works. Would love to know why it's recompiling 3 and cleaning the text... should really be 2 an option to disable the text cleanup but 1 couldn't find it.

Score: 1

This is a long standing problem that could 38 have various causes. I had this same issue 37 occur in the Access VBE (so naturally the 36 Excel answers weren't relevant). After 35 a LOT of digging I finally got it fixed 34 with the solution below. First a recap 33 though:

If you are here because of this issue 32 in Excel, try the solutions above first. To 31 summarize:

  • The most common issue in Excel is that the Design button is toggled. Toggle that and see if the behavior changes as submitted by Dmitry Frenkel above
  • If that doesn't work, check for the "Load Test Report" AddIn as mentioned by Ade.

Those are really the two main 30 causes in Excel, but if neither of those 29 solutions work, then scroll through the 28 rest of the solutions here. All are valid 27 possibilities for the cause. In Access the 26 cause is pretty straight forward. It's 25 a timer issue on a form somewhere. I found 24 this solution from here by User Kevin K. Sullivan.

  • Copy 23 the following line of code onto the clipboard. (You 22 might need to paste it into a text editor 21 first and coerce it onto one line, depending 20 on your newsreader. It must be one line.)

    For 19 i = 0 to Forms.Count -1: Debug.Print Forms(i).Name, Forms(i).TimerInterval: Next 18 i

  • Switch to Access.

  • Press Ctrl-G to go to 17 the Immediate Window.

  • Press Ctrl-V to paste 16 in the code.

  • Press enter to to run the code. All 15 open forms will be listed. Any non-zero 14 timer intervals are your culprits. Simply 13 close that form (It may be invisibly open 12 from another process than the one you thought 11 you were dealing with).

I think the solution 10 for Access here is what the user Dom was 9 trying to say above. I guess the down votes 8 were because it wasn't explained very well 7 and/or because he was speaking of Access 6 when the OG issue was in Excel. Regardless, he 5 is likely on point if you are here because 4 of the VBE in Access.

I hope this helps people. I 3 know I kept finding this page when researching 2 this issue, so that's why I thought I'd 1 update this solution here.

Score: 0

I hit this problem today on a fresh install 4 of Excel 2010 Beta 2. None of the above 3 made any difference, but going into the 2 trust center and disabling all application 1 add-ins fixed the problem for me.

Score: 0

I have Office 2010 Pro and I had the same 6 issue. As I type the space between each 5 word was being deleted. After trying each 4 of the options turning them on and off the 3 only way I found working was to disable 2 the Winzip Courie(excel) add-in. This is done thru the Options dialog 1 box Add-Ins section.

Score: 0

In office 365 I had the same issue - what 3 worked for me is I saved the file with a 2 different name and when I re-opened the 1 new file the problem went away.

Score: 0

I found this issue pops up when I had AutoSave 2 on. Turning that off let me code without 1 it compiling every second.

More Related questions