How can one disable autoformatting in Excel's VBA editor?

Question

The single most annoying feature in Excel's built-in VBA editor is—in my opinion—the aggressive autoformatting of the code, which insists on rewriting what I have typed as soon as the cursor leaves the line. It is particularly distressing that the editor collapses all whitespace, thus preventing any meaningful code alignment. For example, if I try to align a sequence of assignments by the equals sign with values aligned by the decimal separator:

price    = 10.01
quantity =  3.2
vat      =  0.11

the editor inevitably scrambles it by collapsing all spaces:

price = 10.01
quantity = 3.2
vat = 0.11

Is there any way to avoid this kind unwelcome autoformatting?

Answer 1

Assignment cosmetics :-)

There's neither a special VBE property to change the VBE (autoformatting) options directly nor a way to do it programatically. - So afaik VBE irrevocably forces autoformatting upon the user by partial workarounds.

a) Class method

For the sake of the art and just for fun an actually (very) basic class approach to give you a starting idea; assignment arguments are passed as strings allowing any optical formatting - if that's what you really want:

Example call in current module

Sub ExampleCall()
    Dim x As New cVars
    
    x.Add "price    =    11.11"           ' wrong assignment
    '...
    x.Add "price    =    10.01"           ' later correction
    x.Add "quantity =  1241.01"
    x.Add "vat      =     0.11"
    
    Debug.Print "The price is $ " & x.Value("price")
End Sub

Class module cVars

Option Explicit
Private dict As Object

Sub Add(ByVal NewValue As Variant)
    'split string tokens via equal sign
    Dim tmp
    tmp = Split(Replace(Replace(NewValue, vbTab, ""), " ", "") & "=", "=")
    'Identify key and value item
    Dim myKey As String, myVal
    myKey = tmp(0)
    myVal = tmp(1): If IsNumeric(myVal) Then myVal = Val(myVal)
    'Add to dictionary
    If dict.exists(myKey) Then
        dict(myKey) = myVal
    Else
        dict.Add myKey, myVal
    End If
    'Debug.Print "dict(" & myKey & ") =" & dict(myKey)
End Sub

Public Property Get Value(ByVal myVarName As String) As Variant
    'get variable value
    Value = dict(myVarName)
End Property

Private Sub Class_Initialize()
    'set (late bound) dict to memory
    If dict Is Nothing Then Set dict = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Class_Terminate()
    Set dict = Nothing
End Sub

---

Edit #1 as of 3/3 2021

b) Rem Evaluation method

Once again only for the sake of the art a way to read assignments entered into outcommented code lines via, yes via Rem (heaving a deep sigh for this archaic use originating from former Basic times) as it allows to format data with any wanted spaces or tabs and won't be mixed up hopefully with current outcommentings via apostrophe '.

This Test procedure only needs the usual declarations plus some assignment calls as well as the mentioned Rem part. Two simple help procedures get code lines, analyze them via a dictionary class cVars and eventually assign them.

Note that the following example

• needs a library reference to Microsoft Visual Basic Extensibility 5.3 and
• uses the unchanged class cVars of section a) simply to avoid rewriting it.

Option Explicit
Private Const THISMODULE As String = "Module1"      ' << change to current code module name

Sub Test()                                          ' procedure name of example call
    'Declare vars
    Dim price    As Double: Assign "price", price
    Dim quantity As Double: Assign "quantity", quantity
    Dim vat      As Double: Assign "vat", vat
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'Enter assignments via Rem(ark)
    '(allowing any user defined formatting therein)
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Rem price    =    10.01
    Rem quantity =  1241.01
    Rem vat      =     0.11
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    Debug.Print quantity & " à $" & price & " = " & Format(quantity * price, "$#,##0.00")
    
End Sub

Help procedure Assign evaluating Rem codelines in procedure Test

Sub Assign(ByVal myVarName As String, ByRef myvar)
    Const MyProc As String = "Test"
    Dim codelines
    getCodelines codelines, THISMODULE, ProcedureName:=MyProc
    'Debug.Print Join(codelines, vbNewLine)
    
    Dim x As New cVars                          ' set class instance to memory
    Dim line As Variant, curAssignment
    For Each line In codelines
        curAssignment = Split(line, "Rem ")(1)  ' remove Rem prefix from codelines
        If curAssignment Like myVarName & "*" Then
            x.Add curAssignment
            myvar = x.Value(myVarName)
        End If
    Next
    
End Sub

Help procedure getCodelines

Called by above proc Assign. Returns the relevant Rem Codelines from the calling procedure Test. - Of course it would have been possible to filter only one codeline.

Sub getCodelines(ByRef arr, ByVal ModuleName As String, ByVal ProcedureName As String)
    Const SEARCH As String = "Rem "
    'a) set project
    Dim VBProj As Object
    Set VBProj = ThisWorkbook.VBProject
    If VBProj.Protection = vbext_pp_locked Then Exit Sub    ' escape locked projects
    'b) set component
    Dim VBComp As Object
    Set VBComp = VBProj.VBComponents(ModuleName)
    Dim pk As vbext_ProcKind

    'd) get relevant code lines
    With VBComp.CodeModule
        'count procedure header lines
        Dim HeaderCount As Long:  HeaderCount = .ProcBodyLine(ProcedureName, pk) - .ProcStartLine(ProcedureName, pk)
        'get procedure code
        Dim codelines
        codelines = Split(.lines(.ProcBodyLine(ProcedureName, pk), .ProcCountLines(ProcedureName, pk) - HeaderCount), vbNewLine)
        'filter code lines containing "Rem" entries
        codelines = Filter(codelines, SEARCH, True)
    End With
    'return elements
    arr = codelines
End Sub

Don't forget to integrate the class module CVars from section a)!