Hidden Features

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.

Mike Wolfe

Nov 14, 2020 • 8 min read

As a strong proponent of version control in Microsoft Access, I need to talk about my biggest gripe with the VBA development environment: automatic "recasing" of identifiers. Think of this as an expansion of my answer to a question about this "feature" on stackoverflow.

I’m going to approach this article in two parts. In Part 1, I will define the behavior of the development environment. In Part 2, I will discuss my theory about why it works this way.

Part 1: Defining the behavior

If you have spent any amount of time writing code in VBA, I’m sure you have noticed this "feature." As you are typing in identifiers—variables, function names, enums, etc.—you may notice that the IDE automatically changes the casing of these identifiers. For example, you may type out a variable name in all lowercase letters, but as soon as you move to a new line, the first letter of your variable suddenly switches to uppercase.

The first time you see this it can be jarring. As you continue programming, the IDE continues changing the case on you seemingly at random. But, if you spend enough time in the IDE, eventually the pattern reveals itself.

To save you from having to spend ten-plus years of your life waiting for the pattern to reveal itself to you, I will now describe the pattern as I have come to understand it. To my knowledge, Microsoft has never officially documented any of this behavior.

All automatic case changes are global to the VBA project.
Whenever the declaration line of any of the following types of identifiers is changed, every other identifier with the same name has its casing changed as well:
- Sub name
- Function name
- Type name
- Enum name
- Variable name
- Constant name
- Property name
Whenever an enum item name is changed anywhere in the code, the casing of the enum item name is updated to match everywhere.

Let’s talk about each of these behaviors in a bit more detail now.

Global changes

As I wrote above, identifier case changes are global to a VBA project. In other words, the VBA IDE completely ignores scope when changing the case of identifiers.

For example, let’s say you have a private function named AccountIsActive in a standard module. Now, imagine a class module elsewhere in that same project. The class module has a private Property Get procedure. Inside that Property Get procedure is a local variable named accountIsActive. As soon as you type the line Dim accountIsActive As Boolean into the VBA IDE and move to a new line, the function AccountIsActive that we defined separately in its own standard module has its declaration line changed to Private Function accountIsActive() to match the local variable inside this class module.

That's a mouthful, so let me demonstrate it better in code.

Step 1: Define AccountIsActive function

'--== Module1 ==--
Private Function AccountIsActive() As Boolean
End Function

Step 2: Declare accountIsActive local variable in different scope

'--== Class1 ==--
Private Sub Foo()
    Dim accountIsACTIVE As Boolean
End Sub

Step 3: VBA IDE...what have you done?!?!

'--== Module1 ==--
Private Function accountIsACTIVE() As Boolean
End Function

VBA Case-Obliteration's Non-Discrimination Policy

Not content to simply ignore scope, VBA also ignores differences among kinds of identifiers in its quest to impose casing consistency. In other words, every time you declare a new function, subroutine, or variable that uses an existing identifier name, all other instances of that identifier have their case changed to match.

In each of these examples below, the only thing I'm changing is the first module listed. The VBA IDE is responsible for all the other changes to previously defined modules.

Step 1: Define a function

'--== Module1 ==--
Public Function ReloadDBData() As Boolean
End Function

Step 2: Define a sub with the same name

NOTE: This is perfectly valid as long as the procedures are in different modules. That said, just because you *can* do something, doesn't mean you *should*. And you *should* avoid this situation if at all possible.

'--== Module2 ==--
Public Sub ReloadDbData()
End Sub

'--== Module1 ==--
Public Function ReloadDbData() As Boolean
End Sub

Step 3: Define a type with the same name

NOTE: Again, please don't define a sub, function, and type all with the same name in a single project.

'--== Module3 ==--
Private Type ReLoadDBData
    Dummy As Variant
End Type

'--== Module2 ==--
Public Sub ReLoadDBData()
End Sub

'--== Module1 ==--
Public Function ReLoadDBData() As Boolean
End Sub

Step 4: Define an enum with the same name

NOTE: Please, please, please, for the love of all things holy...

'--== Module4 ==--
Public Enum ReloadDbDATA
    Dummy
End Enum

'--== Module3 ==--
Private Type ReloadDbDATA
    Dummy As Variant
End Type

'--== Module2 ==--
Public Sub ReloadDbDATA()
End Sub

'--== Module1 ==--
Public Function ReloadDbDATA() As Boolean
End Sub

Step 5: Define a variable with the same name

NOTE: We're actually still doing this?

'--== Module5 ==--
Public reloaddbdata As Boolean

'--== Module4 ==--
Public Enum reloaddbdata
    Dummy
End Enum

'--== Module3 ==--
Private Type reloaddbdata
    Dummy As Variant
End Type

'--== Module2 ==--
Public Sub reloaddbdata()
End Sub

'--== Module1 ==--
Public Function reloaddbdata() As Boolean
End Sub

Step 6: Define a constant with the same name

NOTE: Oh, come on. Seriously?

'--== Module6 ==--
Private Const RELOADDBDATA As Boolean = True

'--== Module5 ==--
Public RELOADDBDATA As Boolean

'--== Module4 ==--
Public Enum RELOADDBDATA
    Dummy
End Enum

'--== Module3 ==--
Private Type RELOADDBDATA
    Dummy As Variant
End Type

'--== Module2 ==--
Public Sub RELOADDBDATA()
End Sub

'--== Module1 ==--
Public Function RELOADDBDATA() As Boolean
End Sub

Step 7: Define a class property with the same name

NOTE: This is getting silly.

'--== Class1 ==--
Private Property Get reloadDBData() As Boolean
End Property

'--== Module6 ==--
Private Const reloadDBData As Boolean = True

'--== Module5 ==--
Public reloadDBData As Boolean

'--== Module4 ==--
Public Enum reloadDBData
    Dummy
End Enum

'--== Module3 ==--
Private Type reloadDBData
    Dummy As Variant
End Type

'--== Module2 ==--
Public Sub reloadDBData()
End Sub

'--== Module1 ==--
Public Function reloadDBData() As Boolean
End Sub

Enum Items?!?!

For this third point, it's important to distinguish between an Enum type and an Enum item.

Enum EnumTypeName   ' <-- Enum type
    EnumItemAlice   ' <-- Enum item
    EnumItemBob     ' <-- Enum item
End Enum

We already showed above that Enum types are treated the same as other kinds of declarations, like subs, functions, constants, and variables. Whenever the declaration line for an identifier with that name is changed, every other identifier in the project with the same name has its casing updated to match the latest change.

Enum items are special in that they are the only kind of identifier whose casing can get changed whenever any line of code that contains the enum item name is changed.

Step 1. Define and populate the Enum

'--== Module7 ==--
Public Enum EnumTypeName
    EnumItemAlice
    EnumItemBob
End Enum

Step 2. Refer to the Enum items in code

'--== Module8 ==--
Sub TestEnum()
    Debug.Print EnumItemALICE, EnumItemBOB
End Sub

Outcome: Enum type declaration changes to match regular line of code

'--== Module7 ==--
Public Enum EnumTypeName
    EnumItemALICE
    EnumItemBOB
End Enum

Part 2: How did we get here?

I've never spoken to anyone on the internal VBA development team. I've never seen any official documentation on why the VBA IDE works the way it does. So, what I'm about to write is pure conjecture, but I think it makes some sense.

For a long time, I wondered why in the world the VBA IDE would have this behavior. After all, it's clearly intentional. The easiest thing for the IDE to do would be...nothing. If the user declares a variable in all caps, let it stand in all caps. If the user then references that variable in lower case a few lines later, leave that reference in lower case and the original declaration in all caps.

This would be a perfectly acceptable implementation of the VBA language. After all, the language itself is case insensitive. So, why go to all the trouble to automatically change identifier casing?

Ironically enough, I believe the motivation was to avoid confusion. (Swing and a miss, if you ask me.) I scoff at this explanation, but it does make some sense.

Contrast with case-sensitive languages

First, let's talk about programmers coming from a case sensitive language. A common convention in case-sensitive languages, such as C#, is to name class objects with capital letters and to name instances of those objects the same name as the class, but with a leading lower-case letter.

That convention won't work in VBA, because two identifiers that differ only in casing are considered equivalent. In fact, the Office VBA IDE won't let you simultaneously declare a function with one type of casing and a local variable with a different kind of casing (we covered this exhaustively above). This prevents the developer from assuming that there is a semantic difference between two identifiers with the same letters but different casing.

Making wrong code look wrong

The likelier explanation in my mind is that this "feature" exists to make equivalent identifiers look identical. Think about it; without this feature, it would be easy for typos to turn in to runtime errors. Don't believe me? Consider this:

Private mAccountName As String
Private Const ACCOUNT_NAME As String = "New User"

Private Sub Class_Initialize()
    mAccountName = ACCOUNT_NAME
End Sub

Public Property Get MyAccountName() As String
    MAccountName = Account_Name
End Property

Public Property Let MyAccountName(AccountName As String)
    mAccountName = Account_Name
End Property

If you glance quickly at the above code, it looks pretty straightforward. It's a class with a .MyAccountName property. The member variable for the property is initialized to a constant value when the object is created. When setting the account name in code, the member variable is again updated. When retrieving the property value, the code merely returns the contents of the member variable.

At least, that's what it is supposed to do. If I copy the above code and paste it into a VBA IDE window, the casing of the identifiers becomes consistent and the runtime bugs suddenly show themselves:

Private mAccountName As String
Private Const ACCOUNT_NAME As String = "New User"

Private Sub Class_Initialize()
    mAccountName = ACCOUNT_NAME   ' <- This is OK
End Sub

Public Property Get MyAccountName() As String
    mAccountName = ACCOUNT_NAME   ' <- This is probably not what we intended
End Property

Public Property Let MyAccountName(AccountName As String)
    mAccountName = ACCOUNT_NAME   ' <- This is definitely not what we meant
End Property

Implementation: Is this really the best approach?

Umm, no. Don't get me wrong. I actually really like the idea of automatically changing the capitalization of identifiers to maintain consistency. My only real gripe is that the change is made to every identifier with that name in the entire project. Much better would be to change the capitalization of only those identifiers that refer to the same "thing" (whether that "thing" is a function, sub, property, variable, etc.).

So why doesn't it work this way? I expect the VBA IDE developers agree with my perspective on how it should work. But, there is a very good reason why the IDE does not work that way. In a word, performance.

Unfortunately, there is only one reliable way to discover which identifiers with the same name actually refer to the same thing: parse every line of code. That is sloooowwwww. This is more than a simple hypothesis on my part. The Rubberduck VBA project actually does exactly this; it parses every line of code in the project so that it can do automated code analysis and a bunch of other cool stuff.

The project is admittedly heavyweight. It probably works great for Excel projects. Unfortunately, I've never been patient enough to use it in any of my Access projects. Rubberduck VBA is a technically impressive project, but it's also a cautionary tale. Respecting scope when changing capitalization for identifiers would be nice to have, but not at the expense of VBA IDE's current blazingly fast performance.

Final Thoughts

I understand the motivation for this feature. I think I even understand why it's implemented the way it is. But it is the single most maddening quirk of VBA for me.

If I could make a single recommendation to the Office VBA development team, it would be to offer a setting in the IDE to disable automatic case changes. The current behavior could remain enabled by default. But, for power users who are trying to integrate with version control systems, the behavior could be completely disabled to prevent nuisance "code changes" from polluting the revision history.

Image by Free-Photos from Pixabay