VBA 编辑器自动删除行尾的空格

VBA editor auto-deletes spaces at the ends of lines

提问by David

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?

当我暂停打字四分之一秒时，有没有办法说服 Excel 中的 VBA 编辑器停止自动格式化行以删除末尾的空格？

采纳答案by Andrew Scagnelli

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

有一种方法可以关闭 VBA 编辑器中的自动“更正”。

ToolsMenu -> Options-> EditorTab -> Clear the Auto Syntax Checkbox

Tools菜单 -> Options-> EditorTab -> 清除Auto Syntax Check框

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

遗憾的是，这并不能解决您的所有问题，因为 VBA 编辑器往往有自己的想法，无论好坏。

回答by ade

I had this exact problem and the following 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

1. 单击 Microsoft Office 按钮，然后单击 Excel 选项
2. 单击加载项类别
3. 在“管理”框中，单击“COM 加载项”，然后单击“转到”。
4. 查找名为“Load Test Report AddIn”的插件，然后取消选中它
5. 重启excel

This addin is installed with VS2010 Beta2

此插件随 VS2010 Beta2 一起安装

回答by Dmitry Frenkel

In Excel 2010, toggling Design Modebutton on the DeveloperRibbon Tab solves the problem for me.

在 Excel 2010 中，切换开发人员功能区选项卡上的设计模式按钮为我解决了这个问题。

回答by Emily

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.

我以前肯定遇到过这个问题，vba 编辑器会在我打字时格式化（不仅仅是当我转到另一行时）。对我来说，它似乎与我在打开的工作簿中拥有的 Microsoft Web 浏览器控件有关。当我拿出网络浏览器时，VBA 编辑器又开始正常运行了。我不知道为什么这有效，但确实如此。现在我避免在我的工作簿中使用该控件。

回答by Emily

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.

当您在 VBA 编辑器中时，某些东西会导致您的电子表格重新计算，这会“编译”您的代码，从而去除空格。您需要在编辑时停止重新计算单元格。在电子表格中将计算转为手动。工具 > 选项 > 计算应该可以解决问题。

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

由于 DDE 连接，当我重新计算单元格时，我注意到了这一点。

回答by griseldataborda

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.

阅读这篇文章后提到的所有不同的修复程序（没有一个对我有用），我记得我最近一直在玩 Internet Explorer Control，从 VBA 打开 Internet Explorer。

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)

这让我走上了正确的道路，为了修复它，我必须首先删除所有 iexplore.exe 实例（alt+control+delete），关闭 Excel 并再次打开 excel。（iexplore.exe 已经从 VBA 代码中隐形打开，我不知道它们正在运行）

That fixed the bug.

那修复了错误。

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

我已经用 VBA 大量编程超过 4 年了，从来没有遇到过这个问题。

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.

就在几周前，我开始使用 IE 控件，但我开始遇到这个问题……所以就我而言，我只能假设该错误与 IE 控件直接相关。

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

我在同一台 PC 上同时运行 excel 2003 和 2007，问题只发生在 excel 2007 上。

回答by cowb0y

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.

在将计时器事件添加到 Access 2007 表单后，我最近开始发生这种情况。每次事件触发时，VB 编辑器都会“完成”当前行（就像您已将光标移出语句；我已关闭自动语法检查）（最初我将其设置为 1 秒，现在设置为 5秒，行为会相应地扩展）。为了执行事件代码，应用程序必须确保它已被编译。据推测，编辑器需要处于“正常”状态才能执行此操作，因为它可能必须检查模块之间的代码依赖性。请注意，无论当时是否实际将任何依赖代码加载到编辑器中，都会发生该行为。另请注意，重置执行状态不会影响计时器事件触发。作为一个实际问题，

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.

与 abhishek 的评论相反，更改 Tools/Options.../General/Compile On Demand 和/或 Background Compile 的设置不会影响该问题。

回答by sradforth

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.

如果有帮助，以前的答案都没有解决我的问题。似乎唯一的解决方案是关闭 xls 文件并重新打开它。令人沮丧的是每 30 分钟必须做一次，但至少它有效。很想知道它为什么要重新编译和清理文本......应该是禁用文本清理但找不到它的选项。

回答by Douglas

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 :)

在 vba2003 上每次将它从设计模式中取出都会修复它 - 起初它让我发疯......我确实注意到它可能与网络控件有关，但是当我将它从设计模式中取出时它工作正常。 .. 只需切换它以使其正常运行:)

回答by abhishek

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

关掉Tools > Options > General > Background Compile。这为我解决了它。