VBA editor auto-deletes spaces at the ends of lines

Solution 1:

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

Solution 2:

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

Solution 3:

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.

Solution 4:

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.

Solution 5:

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.