Capitalisation change in Excel Worksheet Names

1
votes

I have some excel vba code that references worksheets by object name:

price = wks_PRICE.Range("A1")

My problem is that we have now changed all our worksheet names to camelcaps, so the worksheet object is now called "wks_Price".

But excel is still auto capitalising everything in the code back to the original capitalisation and it is then failing to run through.

ie. I will type the code:

price = wks_Price.Range("A1")

and vba will then auto change it back to

price = wks_PRICE.Range("A1")

even though "wks_PRICE" is no longer defined.

It will then crash with "Compile Error: Variable not defined"

Any ideas of how to fix? (other than changing all worksheet object names back)

3
vbaexcel
Your problem is elsewhere. wks_PRICE is a worksheet object (or possibly a variant containing a worksheet object). It is NOT the "name" of the worksheet. It is probably declared as a worksheet object at the beginning of your code. And there is no need (other than cosmetic) to change it. However, it is likely that, in changing the worksheet name, you did not properly change how this object is declared. - Ron Rosenfeld
The "Name" of the worksheet is "Price". The Name of the object is wks_Price. I know about the difference between object name and "Name", My problem is that excel is auto capitalising my object name. - user3516422
Why would you expect that changing the worksheet name would have any affect on the capitalization of a worksheet object name? As I wrote, you need to see how that variable is declared. - Ron Rosenfeld
as Ron Rosenfeld mentioned already: im pretty sure your "failing to run through" is not caused by capitalising. you mentioned the worksheet name is "Price"...anywhere in the code there must be "dim wks_PRICE as Worksheet" this have to be changed to dim "wks_Price as Worksheet". This should solve your capitalising problem. - Denyo
To clarify. I know the worksheet "name" has no effect when calling the worksheet using the object name. The worksheet object name is never defined in the vba code. Instead it is defined in the object properties window of the vba editor. Capitalisation of this name does matter, and the vba editor will autocapitalise any instance of the object name in your code to match what it thinks the object is named. My problem is that excel is still using the old name, and not recognising that it has changed. - user3516422

3 Answers

2
votes

Declare the variable worksheet wks_Price using the name you want and VBE will auto-correct all occurances of your variable to excatly how you declared it. If it does already change it to CAPS then it means that you have this variable declared somewhere (search your module/project).

2
votes

Search your code for something like :

set wks_Price = Activeworkbook.sheets("Price")

if you cannot find anything like it, then add it =)

1
votes

The answer I discovered:

Change the object name to something different in the object properties window. eg.

wks_Price => wks_Price1

leave it for a moment. And then change it back.

wks_Price1 => wks_Price

I think if you only change the capitalisation, then excel doesn't realise the object has changed name, and continues correcting your code back to the old name. It then crashes when it runs because that object name is no longer defined.