used a VBA object name as a variable

Question

For a sloppy reason, I made a variable named 'cells' in my code like this

   Dim cells as Range

But now in all of my modules, the 'Cells' object has been replaced with the lowercase variable 'cells'. If I type in 'Cells', the VBA editor will automatically replace the text with 'cells'. Is there a way to fix this?

score 5 · Answer 1 · answered Oct 04 '11 at 00:00

5

The only way to fix this is to rename cells to be Cells.

answered Oct 04 '11 at 00:00

GSerg

76,472
17
159
346

1

+1 - Similar problem with the same solution here: http://www.ozgrid.com/forum/showthread.php?t=71829&page=1 – Reafidy Oct 04 '11 at 02:16

score 0 · Answer 2 · answered Oct 04 '11 at 01:04

0

You could also just find and replace one at a time, it would be tedious though.

answered Oct 04 '11 at 01:04

Jon49

4,444
4
36
73

Won't work, the editor will replace it all back as soon as you type in one more `cells`. – GSerg Oct 04 '11 at 12:50

score 0 · Answer 3 · answered Oct 04 '11 at 23:16

The bigger problem is that normally an unqualified use of Cells refers to the active sheet and thus returns a range (i.e. ActiveSheet.Cells) containing all of the cells on that active sheet (as long as the active sheet is a worksheet).

If your own variable called cells is in scope then it will take precedence over the global use of Cells. As both refer to a Range object there won't be any syntax errors - just difficult to find semantic ones.

I would rename the variable cells to something totally different instead. After that if Excel still changes Cells to cells then the suggestions in the link reafidy supplied above should help

used a VBA object name as a variable

3 Answers3