How to change case of method name in VBA?

Question

While learning VBA I did a lot of copy-paste of code from the internet. At some point I have noticed that whenever I use a method which name is "Add", the VBA IDE automatically change it to "add". For example when I type ActiveSheet.PivotTables.Add it's automatically converted to ActiveSheet.PivotTables.add. The same happens with Worksheets.Add which is changed to Worksheets.add. How can I restore default case of a method?

I know that VBA IDE changes variable's case accordingly to how it was declared (see: How does one restore default case to a variable in VBA (Excel 2010)?). But I am not sure if it might be related to my issue.

Is this even happening when you open a blank worksheet in IDE or is it just in a particular vba project? — Pankaj Jaju, Jul 08 '14 at 14:59
Is this in any way affecting performance? I have noticed some quirks where certain methods are lower-cased, but in my experience it does not matter and the code should still execute. Are you having errors? — David Zemens, Jul 08 '14 at 15:31
This is one of those obscure VBA editor quirks. I use it to my advantage since the autocorrect means I've not misspelled the name of a long variable. In combination with `Ctrl-j` (Intellisense), it can be quite useful for writing miles of code. If it's an issue, I've discussed the reasons/ a possible solution in the answer below. — hnk, Jul 08 '14 at 15:48

hnk · Accepted Answer · 2014-07-08T16:04:00.900

This will happen because you have declared some variable or function or sub as add in your Project (or in some add-in you might have loaded).

The VBA environment will get the last declared case representation of your variable names/function names and apply them to every instance where they occur.

E.g.

Function Func1()    
Dim ART As Double
ART = 1
' in one function
End Function

' Now if I type the following in another function
Function Func2()
Dim aRt as Double
End Function

' the original declaration in the first function becomes
Dim aRt As Double
aRt = 1

This behavior may be desirable or undesirable, but it's definitely by design.

If you want to declare variables and not want them to change case (useful for certain Enums, etc.) you need to do something like

#If False Then
   Dim HeWhoseCaseShallNotBeChanged
   Dim ADd     ' Even methods can be "declared" here. It will have no effect on execution
#End If

This will work for Enums, etc. And you can declare this compiler directive at the top of your module page.

How to change case of method name in VBA?

1 Answers1