How To: Enforce Consistent Letter Casing in VBA

Anyone who has used version control with VBA understands the pain of VBA's case-changing "feature."

I commented on the behavior here:

How does one restore default case to a variable in VBA (Excel 2010)?

I documented the behavior here:

VBA’s Case Changing “Feature”

VBA likes to change the casing of our code (upper/lower/mixed). It may seem random, but it’s not. Read all the details here.

No Longer SetMike Wolfe

I lamented the behavior here:

Add support for case-insensitive VBA comparisons · Issue #599 · joyfullservice/msaccess-vcs-addin

VBA is (in)famous for helping developers by globally applying letter-case changes without regard to actual identifier scope, as I wrote about here: VBA’s Case Changing “Feature”. I’ve attempted to…

GitHubjoyfullservice

I shall now conquer the behavior here...

What's the Big Deal?

The challenge with VBA's case-changing behavior is that a minor change can ripple through the entire codebase, flooding your diff with false positives.

Want proof? Try this.

1. Export your source code and check it into version control
2. Add the following line of code literally anywhere in your codebase: Dim I
3. Export your source code and run a diff

Unless you've engaged in a quixotic lifetime quest against programming convention, your codebase is chock-full of lower-case "i" loop variables.

At least, it was until two minutes ago.

Now, every single lower-case "i" identifier in your codebase has been capitalized to upper-case "I".

The upshot? In my moderately-sized Access application, that one instance of I instead of i resulted in false positive code "changes" in 27 objects:

On the bright side, it's just as easy to change it back.

Change Dim I to Dim i, move to a new line of code, save and export, and all the changes are gone.

All that back and forth gets very tedious after a while, though.

"Snip snap, snip snap," as Michael Scott would say:

What is a frustrated VBA programmer to do?

Et tu, Brute (Force)?

In the past, I've used a custom AutoHotkey script to roll back letter-casing-only changes at the file level:

Managing VBA’s Case-Change Chaos: The 80/20 Approach to Smoother Access Version Control

Explore how VBA’s global casing changes flood Access version control with false positives and how ignoring case-only differences can help.

No Longer SetMike Wolfe

It's a great 80/20 solution workaround, but it in no way solves the problem.

After living and dealing with this issue for over 15 years, I've concluded that the only foolproof solution is the brute force approach:

Maintain a canonical listing of casing for every identifier.

"Don't Ever Change"

The trick is to insulate the canonical listing from the case-changing feature itself.

The way to do that? Comments.

Since comments are not affected by identifier case changes, we store the canonical version of the identifier in a trailing comment.

Sample Code:

' The comment contains the canonical letter-casing
Dim a 'a
Dim b 'b
Dim errHandler 'ErrHandler
Dim Rs 'rs
Dim SQL 'SQL
Dim SQLInsert 'SqlInsert
Dim X 'x
Dim Y 'y

Note that in the sample code above, the following letter-casing changes will be made:

• errHandler -> ErrHandler
• Rs -> rs
• SQLInsert -> SqlInsert
• X -> x
• Y -> y

Applying the Fix

To update the rest of the codebase to match the comment's casing, we loop through the code in a dedicated module and replace the identifier with its canonical form from the trailing comment.

Here's the basic idea:

For i = 1 To cm.CountOfLines
    Dim LineOfCode As String
    LineOfCode = Trim$(cm.Lines(i, 1))
    Dim CurrentCasing As String, CanonicalCasing As String
    Dim NamesMatch As String, CasingDiffers As Boolean
    If Left(LineOfCode, 3) = "Dim" Then
        CurrentCasing = Trim$(Mid$(LineOfCode, 5, InStr(5, LineOfCode, " ") - 5))
        CanonicalCasing = Trim$(Mid$(LineOfCode, InStr(1, LineOfCode, "'") + 1))

        'Perform a case-sensitive text comparison between the comment and its identifier counterpart
        CasingDiffers = (InStr(1, CurrentCasing, CanonicalCasing, vbBinaryCompare) = 0)
        If CasingDiffers Then
            cm.ReplaceLine i, "Dim " & CanonicalCasing & " '" & CanonicalCasing
        End If
    End If
Next i

1. Loop through each line of code in the module
2. Check for Dim at the start of the line
3. Extract the identifier from just after the Dim statement
4. Extract the trailing comment contents
5. Perform a case-sensitive text comparison of the identifier and the comment
6. If there's a mismatch, overwrite the code with the comment's letter-casing

Dealing With the Edge Cases

The final code below deals with a few edge cases the simple excerpt above ignores:

1. Outputs a message to the Immediate Window if the trailing comment doesn't match the identifier from the same line (e.g., Dim SqlString 'Sql_String)
2. Allows for overwriting the letter casing of API library names (e.g., "kernel32" or "kernel32.dll")
3. Allows for overwriting VBA reserved keyword identifiers (e.g., the Excel library's Range.End method)

' Set letter-casing for API call libraries
Private Declare PtrSafe Function zzz_kernel32 Lib "kernel32" () 'kernel32
Private Declare PtrSafe Function zzz_kernel32_dll Lib "kernel32.dll" () 'kernel32.dll

' Set letter-casing for reserved keywords (like Excel's Range.End method) here:
Private Declare Function zzz_End Lib "End" () 'End

Implementing the Solution

To get started, follow these steps (copying code from the "Final Code" section):

1. Copy the StandardizeLetterCasing() sub to any standard module in your project
2. Create a class module named clsStandardLetterCasing and copy in the sample code below
3. Populate the clsStandardLetterCasing module slowly over time
4. Run StandardizeLetterCasing from the Immediate Window as needed

Do not waste your time trying to populate the clsStandardLetterCasing class with every single existing identifier from your project. Instead, build the list of letter-case fixes organically, one identifier at a time, as they rear their ugly little heads in your version control diffs.

Final Code

Copy and paste the code below into any standard code module in your project:

' ----------------------------------------------------------------
' Procedure : StandardizeLetterCasing
' Date      : 5/7/2025
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/standardizelettercasing/
' Purpose   : Enforce consistent letter-casing for VBA identifiers
'               as a workaround for VBA's case-changing "feature."
'  5/23/25  : Add "PtrSafe" to avoid compile errors in 64-bit VBA.
' ----------------------------------------------------------------
Sub StandardizeLetterCasing()
    Const StandardLetterCasingModuleName As String = "clsStandardLetterCasing"

    'Get the Standard Letter Casing class module
    Dim Comp As Object  'VBIDE.VBComponent
    Dim cm As Object    'VBIDE.CodeModule
    For Each Comp In Application.VBE.ActiveVBProject.VBComponents
        Set cm = Comp.CodeModule
        If cm.Name = StandardLetterCasingModuleName Then Exit For
    Next Comp
    If cm.Name <> StandardLetterCasingModuleName Then
        Debug.Print "Could not find '" & StandardLetterCasingModuleName & "' code module"
        Exit Sub
    End If

    'Loop through each line of code and replace the identifier name with its
    '   canonical form in the trailing comment if casing is different
    Dim i As Long
    For i = 1 To cm.CountOfLines
        Dim LineOfCode As String
        LineOfCode = Trim$(cm.Lines(i, 1))
        Dim CurrentCasing As String, CanonicalCasing As String
        Dim NamesMatch As String, CasingDiffers As Boolean
        If Left(LineOfCode, 3) = "Dim" Then
            CurrentCasing = Trim$(Mid$(LineOfCode, 5, InStr(5, LineOfCode, " ") - 5))
            CanonicalCasing = Trim$(Mid$(LineOfCode, InStr(1, LineOfCode, "'") + 1))
            NamesMatch = (UCase$(CurrentCasing) = UCase$(CanonicalCasing))
            If NamesMatch Then
                'Perform a case-sensitive text comparison between the comment and its identifier counterpart
                CasingDiffers = (InStr(1, CurrentCasing, CanonicalCasing, vbBinaryCompare) = 0)

                If CasingDiffers Then
                    cm.ReplaceLine i, "Dim " & CanonicalCasing & " '" & CanonicalCasing
                End If
            Else
                Debug.Print "Identifier mismatch on line " & i & " of " & _
                            StandardLetterCasingModuleName & " module: " & _
                            LineOfCode
            End If
        ElseIf Left(LineOfCode, 32) = "Private Declare PtrSafe Function" Then
            Dim StartPos As Long, EndPos As Long
            StartPos = InStr(1, LineOfCode, """") + 1
            EndPos = InStr(StartPos, LineOfCode, """") - 1
            CurrentCasing = Mid(LineOfCode, StartPos, EndPos - StartPos + 1)
            
            CanonicalCasing = Trim$(Mid$(LineOfCode, InStr(1, LineOfCode, "'") + 1))
            NamesMatch = (UCase$(CurrentCasing) = UCase$(CanonicalCasing))
            If NamesMatch Then
                CasingDiffers = (InStr(1, CurrentCasing, CanonicalCasing, vbBinaryCompare) = 0)

                If CasingDiffers Then
                    cm.ReplaceLine i, "Private Declare PtrSafe Function zzz_" & Replace(CanonicalCasing, ".", "_") & _
                                      " Lib """ & CanonicalCasing & """ '" & CanonicalCasing
                End If
            Else
                Debug.Print "Identifier mismatch on line " & i & " of " & _
                            StandardLetterCasingModuleName & " module: " & _
                            LineOfCode
            End If
        
        End If
    Next i
End Sub

Create a class module that matches the Const StandardLetterCasingModuleName constant declared above (clsStandardLetterCasing by default).

As you export code to source, add a new line of code with canonical letter-casing in the comment each time a casing-only difference appears in your version control diffs.

Here's a sample of what the clsStandardLetterCasing module looks like:

Option Compare Database
Option Explicit

' Copy, paste, and run the following two commands to the Immediate Window
'
'    StandardizeLetterCasing
'    Application.Run "MSAccessVCS.HandleRibbonCommand", "btnExport"
'
' Line 1: Function that updates the identifiers below with proper casing
' Line 2: The MS Access VCS Addin command to export to source from VBA
'           (replace with your source control add-in's equivalent command)


' Set letter-casing here for:
'    - API calls
'    - reserved keywords (like Excel's Range.End method)
Private Declare PtrSafe Function zzz_End Lib "End" () 'End
Private Declare PtrSafe Function zzz_kernel32 Lib "kernel32" () 'kernel32
Private Declare PtrSafe Function zzz_kernel32_dll Lib "kernel32.dll" () 'kernel32.dll


' Set letter-casing for all other identifiers here:
Dim a 'a
Dim b 'b
Dim ErrHandler 'ErrHandler
Dim Fld 'Fld
Dim rs 'rs
Dim SQL 'Sql
Dim SqlInsert 'SqlInsert
Dim TempTableName 'TempTableName
Dim x 'x
Dim y 'y

More elegant solutions to this problem are being discussed on this GitHub issues page.

In terms of simplicity, though, it's hard to beat one procedure in a standard module and one class module with a list of canonical letter casing.

Conclusion

Commentavi.
Documentavi.
Lamentavi.
Vici.

_{Google translation}

Acknowledgements

• Cover image generated by Imagen3-Fast

UPDATE [2025-05-23]: Updated the function and sample code to include the PtrSafe keyword for 64-bit VBA compatibility.