VBA editor auto-deletes spaces at the ends of lines

E

18

19

Is there a way to convince the VBA editor in Excel to stop auto-formatting lines to remove the space at the end when I pause in my typing for a quarter second?

Enjambement answered 22/7, 2009 at 9:38 Comment(0)

D

4

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

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

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

Dulce answered 22/7, 2009 at 12:59 Comment(3)

And while you're there, you should check the Require Variable Declaration box. It will auto-enter an Option Explicit in every new file, saving you potential frustration when debugging a variable that just won't change since there's a typo in its name. – Dulce 22/7, 2009 at 13:0

Be warned that when you uncheck Auto Syntax Check you will no longer receive incessant message boxes telling you about a "Compile Error"; which I believe every VB developer has grown to love. – Barberabarberry 2/6, 2011 at 17:45

SCROLL DOWN! If you landed here via google, the answer you are looking for is ade's answer (with many more up votes). – Richard 15/11, 2012 at 23:35

A

39

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

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

This addin is installed with VS2010 Beta2

Alcestis answered 31/3, 2010 at 10:4 Comment(7)

Disabling "Load Test Report AddIn" as suggested by user 'ade' worked perfectly for me. Since I use Visual Studio 2010 I guess I got this add-in as part of installation. – Trahan 8/11, 2010 at 13:44

For me, it was the "Team Foundation Server Excel add-in" causing this issue. – Cuda 17/1, 2012 at 14:56

Disabling "Load Test Report AddIn" worked for me. One wonders what the hell it's doing behind the scenes, to affect the code editor window like that! – Boling 26/1, 2012 at 10:46

Also for me, the 'Team Foundation Add-in' is guilty - even although it was disabled. I had to start Excel as administrator in order to actually remove the add-in. Thank You ade – Gadfly 22/7, 2015 at 7:33

Where is "Microsoft Office Button"? @Alcestis – Vagrant 10/11, 2016 at 9:41

COM Add-Ins in on the Dev ribbon within Excel. Disabling all of them worked for me. – Vagrant 10/11, 2016 at 9:42

Mindjet MindManager Add-in was the offender for me – Tarragona 9/11, 2018 at 17:5

B

13

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

Bencion answered 20/12, 2010 at 21:50 Comment(3)

That helped. Thanks. Though, I'm not sure if this is actually the root cause or if it stops the root cause from occurring when in design mode. – Explicative 3/3, 2011 at 6:12

Actually, I take it back. It didn't help, because it goes out of design mode on its own. – Explicative 3/3, 2011 at 8:5

Each time you run from the code editor it goes out of designer mode. The same toggle button is on the editor toolbar, so you can switch back right there. – Auberbach 27/12, 2013 at 16:22

N

6

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

Nally answered 22/7, 2009 at 18:44 Comment(0)

J

5

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

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

Juta answered 27/7, 2009 at 21:45 Comment(3)

This answer sounds right to me, based on similar experience in Access. I can't remember what was causing code to twitch, but I was learning about recursion at the time ... shudder ... – Vedette 28/7, 2009 at 4:53

Me too, a DDE connection was the cause – Reverberation 10/1, 2014 at 12:40

yeah for me it was an OFFSET formula in one of the spreadsheets I had open – Cohberg 18/8, 2016 at 1:28

D

4

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

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

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

Dulce answered 22/7, 2009 at 12:59 Comment(3)

And while you're there, you should check the Require Variable Declaration box. It will auto-enter an Option Explicit in every new file, saving you potential frustration when debugging a variable that just won't change since there's a typo in its name. – Dulce 22/7, 2009 at 13:0

Be warned that when you uncheck Auto Syntax Check you will no longer receive incessant message boxes telling you about a "Compile Error"; which I believe every VB developer has grown to love. – Barberabarberry 2/6, 2011 at 17:45

SCROLL DOWN! If you landed here via google, the answer you are looking for is ade's answer (with many more up votes). – Richard 15/11, 2012 at 23:35

S

2

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

Spirketing answered 4/12, 2011 at 9:5 Comment(0)

O

2

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

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

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

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

That fixed the bug.

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

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

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

Olwen answered 30/12, 2011 at 18:22 Comment(0)

O

1

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

Orrery answered 8/1, 2010 at 13:9 Comment(0)

L

1

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

Lapin answered 5/11, 2010 at 17:2 Comment(0)

C

1

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

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

Cristycriswell answered 2/6, 2011 at 16:38 Comment(0)

P

1

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

Piperidine answered 21/6, 2011 at 17:49 Comment(0)

M

1

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

If you are here because of this issue in Excel, try the solutions above first. To 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 causes in Excel, but if neither of those solutions work, then scroll through the rest of the solutions here. All are valid possibilities for the cause. In Access the cause is pretty straight forward. It's a timer issue on a form somewhere. I found this solution from here by User Kevin K. Sullivan.

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

For i = 0 to Forms.Count -1: Debug.Print Forms(i).Name, Forms(i).TimerInterval: Next i
Switch to Access.
Press Ctrl-G to go to the Immediate Window.
Press Ctrl-V to paste in the code.
Press enter to to run the code. All open forms will be listed. Any non-zero timer intervals are your culprits. Simply close that form (It may be invisibly open from another process than the one you thought you were dealing with).

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

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

Mattiematting answered 10/1, 2021 at 21:54 Comment(0)

A

0

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

Anaclinal answered 11/2, 2010 at 21:31 Comment(0)

E

0

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

Espagnole answered 29/2, 2012 at 18:56 Comment(0)

H

0

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

Hazan answered 22/9, 2018 at 3:0 Comment(0)

B

0

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

Bobstay answered 15/5, 2020 at 17:9 Comment(0)

G

-2

I don't think I've ever seen the VBE remove a space when I've stopped typing. It will remove trailling spaces from lines if you move to another line, but that's something different and not behaviour that I think can be altered.

Gwen answered 22/7, 2009 at 12:48 Comment(3)

Oh, it does. I can't recall how I got that behavior un-stuck. – Vedette 24/7, 2009 at 4:8

So you're saying it can't happen, huh? That's not a very helpful answer. It IS happening. To me. And I know what I'm doing. – Explicative 3/3, 2011 at 6:7

Not saying it can't happen, simply that I've never seen it happen. – Gwen 30/3, 2011 at 9:10

T

-2

The removal of spaces in the VBA editor for Access occurs when a form is open in Form("Execute") mode. This is probably due to background executions based on "On Timer" methods. Close the form in Access solves the problem in VBA.

Tevet answered 8/7, 2010 at 9:27 Comment(1)

Please see my solution below. Dom is not wrong in this post with respect to the Access VBE, but this answer could use a little expanding. The down votes aren't warranted IMO. In the Access VBE an "On Timer" issue is very likely the problem. Closing that form will very likely fix this. Granted that the OG issue was for Excel, anyone with this problem in any version of the VBE (Access, Excel, etc) will find themselves on this page because of Google. For that reason I updated the solutions below. I hope this helps. – Mattiematting 10/1, 2021 at 21:58

Recommended topics

Hot tags