Selection command changes to lower case in VBA

Question

When my colleague writes a macro with the Selection reference in it, for some reason it changes the word Selection to a lower case s and stops it from working.

Example:

Selection.Copy

changes to

selection.Copy

Could someone advise what is happening please? I would guess its his settings somewhere, as he has not previously written any macros.

He might have declared a variable called `selection` somewhere (public). — arcadeprecinct, Sep 23 '16 at 10:07
Or he recorded a macro called "selection". Just search his open workbooks' macros for the word selection. Maybe in the Personal.xlsb. — vacip, Sep 23 '16 at 10:10
See [Avoid re-purposing the names of Properties or Methods as your variables](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices/13075/avoid-re-purposing-the-names-of-properties-or-methods-as-your-variables) — , Sep 23 '16 at 10:30

score 9 · Accepted Answer · answered Sep 23 '16 at 12:45

The VB Editor will auto-capitalize according to the casings of the names your code uses for Variables, Sub-Routines and other types, and it will capitalize with roughly the same order as the scopes to a type.

You can resolve the problem by finding any variables called selection and changing the casing to Selection. If you can't find any, try temporarily adding a variable called Selection, and all of the in-scope casings will change.

For example, if you define a variable called workshEEts, notice that using ThisWorkbook.Worksheets.Count will use the same casing:

Dim workshEEts As Workbook
Debug.Print ThisWorkbook.workshEEts.Count

But there's also a glitch in the editor, because it cache's the names even after you delete one. For example, if you delete the first line from the example above, the casing of the 2nd line remains unchanged, and adding a new usage of Worksheets results in the cached casing:

Debug.Print ThisWorkbook.workshEEts.Count
ThisWorkbook.workshEEts(1).Select

So, how do you clear the cache? Create a new variable, even if only temporarily, with the casing you require, and then all of the scoped casings will be updated:

Dim Worksheets
Debug.Print ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(1).Select

Thanks for the answer, this makes sense, as when I deleted the macro (he had written a macro containing no commands but labelled 'selection') the selection reference worked but stayed in the same lower case format! Thanks for all your help! — W Beck, Sep 26 '16 at 13:16

Selection command changes to lower case in VBA

1 Answers1