death by rows

[RobBroekhuis]

Hi all,
A ran across a strange thing today. I was troubleshooting some code, and it kept hanging up excel - completely, no recovery possible. Finally, I traced the problem to the innocuous-looking code:

rows=rows+1

The "rows" variable had not been declared, and I always work in "option explicit", so it was odd that Excel hadn't flagged it as a problem upon compiling. Anyway, I changed the variable name to nrows and declared it properly, and the problem went away.
However, I'm still curious what was going on. When I start up Excel, go into the VBA editor's immediate window, and type

?rows

Excel hangs completely. It does the same when I type

?columns

Other previously undeclared variables don't have the same problem.

Any ideas what's going on?
Rob

 

[xlbo]

They are reserved keywords.
Rows
Columns
Cells
Any of the names of excel objects will cause major upsets if you try and use them as variables

If you want to use variable names that apply to what they are, use my before them ie
myRows
myColumns
myCells


From Help file:

· You must use a letter as the first character.
· You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
· Name can't exceed 255 characters in length.
· Generally, you shouldn't use any names that are the same as the functions, statements, and methods in Visual Basic. You end up shadowing the same keywords in the language. To use an intrinsic language function, statement, or method that conflicts with an assigned name, you must explicitly identify it. Precede the intrinsic function, statement, or method name with the name of the associated type library. For example, if you have a variable called Left, you can only invoke the Left function using VBA.Left.

· You can't repeat names within the same level of scope. For example, you can't declare two variables named age within the same procedure. However, you can declare a private variable named age and a procedure-level variable named age within the same module. Rgds
~Geoff~

 

[RobBroekhuis]

Hmmm... I could understand if Excel balked at me and told me "you're using this keyword inappropriately". But to just go off into lock-up land seems extreme.
How are Rows, Columns, and Cells different from any other Excel object property names (e.g., Row, Column, Font, Characters, none of which have this problem)?
Rob

 

[xlbo]

The problem is that the words themselves are all valid within a sub so excel won't tell you that you can't use them. If, however, when you do use them, you do so in a way that excel doesn't recognise or can't handle, it tends to lock up eg
rows = rows + 1

Are you sure that none of the other words that you mentioned create similar problems.....I'd go off the help file extract that I copied previously and say that you just shouldn't use ANY keywords when declaring variables. If you must use them, prefix them with a my or similar Rgds
~Geoff~