How To: Enforce Consistent Letter Casing in VBA
Conquer VBA's case-changing "feature" once and for all with this simple, brute-force solution: the StandardizeLetterCasing() function.

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:

I lamented the behavior here:
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.
- Export your source code and check it into version control
- Add the following line of code literally anywhere in your codebase:
Dim I
- 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:

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
- Loop through each line of code in the module
- Check for
Dim
at the start of the line - Extract the identifier from just after the
Dim
statement - Extract the trailing comment contents
- Perform a case-sensitive text comparison of the identifier and the comment
- 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:
- 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
) - Allows for overwriting the letter casing of API library names (e.g.,
"kernel32"
or"kernel32.dll"
) - 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 Function zzz_kernel32 Lib "kernel32" () 'kernel32
Private Declare 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):
- Copy the
StandardizeLetterCasing()
sub to any standard module in your project - Create a class module named
clsStandardLetterCasing
and copy in the sample code below - Populate the
clsStandardLetterCasing
module slowly over time - 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."
' ----------------------------------------------------------------
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, 24) = "Private Declare 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 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 Function zzz_End Lib "End" () 'End
Private Declare Function zzz_kernel32 Lib "kernel32" () 'kernel32
Private Declare 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.
Acknowledgements
- Cover image generated by Imagen3-Fast