24

At some point I accidentally named a variable range (all lower-case) and since then the Visual Basic for Applications editor refuses to use Range (Pascal-cased); it will auto-correct it to lower-case. How can I reset its "brain" to forget about my past transgression?

NOTE: I've tried doing a global replace-all, but the moment I edit any single line -- it re-corrects once again.

Obviously this is a very very minor, cosmetic issue but watching the R go limp every time I type Range is irritating.

Neil C. Obremski
  • 18,696
  • 24
  • 83
  • 112
  • 7
    It's a very *very* minor, cosmetic issue until you start trying to put your VBA code into version control systems that can't ignore case (mercurial, for example). At which point it becomes very *very* aggravating. – mwolfe02 Jan 31 '11 at 17:01

2 Answers2

36

You need to change the name of the variable in a declaration line (Dim statement for example) somewhere (anywhere, actually).

This is one of the most annoying "features" of the VBA IDE (as if version control weren't already hard enough with VBA, the constant case-changing drives me batty). Variables have their case changed globally in the VBA IDE, regardless of the variable's actual scope. The VBA IDE seems to take the approach of most recent declaration wins.

Usually all you need to do is just update the case in a declaration line somewhere, but VBA can be obstinate. I've yet to crack the code completely.

Note: As @Scorchio points out in the comments below, the variable case is changed globally within the current project; other projects that may be open in the VBA IDE (such as other workbooks when working in Excel) are NOT affected.

UPDATE: I expanded on this entry in my Access/VBA blog today: VBA's Case Changing "Feature"

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • 9
    Gah, of course! I just put a "Dim Range as String" in, VBA fixed all occurances to that, and then deleted that line. Thank you :) – Neil C. Obremski Jan 31 '11 at 16:58
  • 4
    How did I survive without this knowledge?! – Simon Cowen Feb 01 '11 at 12:39
  • 5
    "This is one of the most annoying "features" of the VBA IDE". You nailed it. I cannot stand this behavior – JohnZaj Mar 20 '13 at 21:49
  • I thought I was losing it - thanks for clearing that up. – DaveU Sep 25 '14 at 22:48
  • 2
    "Variables have their case changed globally in the VBA IDE, regardless of the variable's actual scope." I don't think that's true, I've tested this on Excel 2010 and **it seems to be project-global**, not IDE-global. – Scorchio Jan 16 '15 at 17:36
  • @Scorchio: Thanks for the tip. I do most of my VBA work in MS Access where I work exclusively with a single open project. For me there is no practical difference between IDE-global and project-global, which is probably why I never picked up on it. I updated my answer to incorporate your comment. – mwolfe02 Jan 16 '15 at 19:42
  • you probably just saved a handful of my hair. Thanks! – Boris Jul 13 '16 at 12:38
11

Last declaration in time wins.

  1. Go on first line and type Dim Range As String
  2. Move to next line
  3. Go back and remove your Dim Range As String
thanos.a
  • 2,246
  • 3
  • 33
  • 29
  • 1
    Likewise, if you managed to name a variable the same as a standard object, and now the object's casing is messed up, do a similar trick: `Private WithEvents querytable As querytable` followed by `Private WithEvents QueryTable As QueryTable`. Hit enter, both should capitalize. – mcw Oct 07 '15 at 15:57