Referencing Worksheets More Effectively! 10

[Bowers74]

I have noticed that a lot of the procedures in this forum contain references to Worksheets like Sheets("Sheet1&quot.Activate.

This should never have to be done! You can change the Worksheet Reference Name or "(Name)" with parenthases in the VBE. The Worksheet Reference Name differs from the Worksheet Name in that the Worksheet Name is the Name that you see on the Worksheet Tab in Excel and the Worksheet Reference Name is the Name that can be used in procedures to refer to a specific Worksheet in a Workbook.

Some of the procedures I have seen even have the Worksheets set as a variable like Set mySheet = Worksheets("Sheet1&quot to allow the programmer to refer to the Worksheet later in his/her code as mySheet.

This is unnecessary, because when you change the Worksheet Reference Name in the VBE it is pretty much the same as setting a Global variable for that sheet.

To change the Worksheet Reference Name, click on the Worksheet object in the VBE and go to the Properties for that sheet. Change the "(Name)" (not "Name&quot to something like sBudget (or even mySheet). Now you can refer to that Worksheet in any module in your application as sBudget (or mySheet).

This can be, and should be done for all of the Worksheets in a workbook. for example:

Worksheets("Annual Budget&quot.Activate should be sBudget.Activate.



The same can be done for Workbooks by changing ThisWorkbook to wbBudget.

After that you will never have to type Workbooks("Budget Analysis NA.xls&quot.Worksheets("Budget&quot.Activate again. Now you will only have to type wbBudgetNA.sBudget.Activate!!!



Now, Have fun going through all of your previous applications and changing your Worksheet references! ;-)



I hope this helps someone!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[willyboy58]

Helps me!

 

[marklenel]

Excellent tip - I've spent (or should that now be wasted?) a lot of time creating worksheet variables.

Question - is there any way to change the Reference Name from code during run-time? This might be useful during the creation/use of new worksheets/workbooks.

Mark

 

[Bowers74]

Hi marklenel,

I'm glad I could help. To answer your question . . . Yes! You can change the Reference Name at Runtime. It is a bit more difficult than just calling it though. I just wrote the following procedure that asks you if you want to change the reference for the activesheet and then will change it to what you input into the InputBox:

I have marked the line that changes the Reference in red

Sub Change_WS_ReferenceName()
Dim msg As String
Dim NewRef
Dim ws As Worksheet
Set ws = ActiveSheet
' Create Message
msg = "Do you want to change the VBA Reference Name for: " & vbCrLf
msg = msg & ws.Name & vbCrLf & "The VBA Reference Name is:" & vbCrLf
msg = msg & ws.CodeName & vbCrLf
' Confirm Change
If MsgBox(msg, vbQuestion + vbYesNo, "Change Code Name") = vbNo Then Exit Sub
' Set New Reference
NewRef = InputBox("Enter the new VBA Reference Name", "Change Code Name")
If NewRef = vbCancel Or NewRef = "" Then Exit Sub
' Change Reference
On Error GoTo RefExists:

ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName&quot = NewRef

MsgBox "New VBA Reference Name for: " & ws.Name & _
        " is now " & ws.CodeName, vbOKOnly, "Code Name Changed"
Exit Sub
RefExists:
On Error GoTo 0
MsgBox "This VBA Reference already exits, please try again.", vbCritical, "Invalid Entry"
Call Change_WS_ReferenceName
End Sub

I hope this helps! I am going to add this information to my FAQ later.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

i asked excel help how to do that and it said it canny be done.
that helps things a huge amount, well done

 

[Bowers74]

I just wanted to add that I have updated the FAQ!

Check it out!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

Hey Mike,
that is very handy bit of code!
i tried it out and it is great to change the code name of a sheet, but i was having problems when i tried to reference a sheet in a different workbook ( seperate workbook to the code).
i dont know what the problem was (probably just that it is a monday)
it changed the code name of any sheet no problem but i could not then use either .Select or .Activate for the sheet then.

What is the proper way to do this?

 

[Bowers74]

VBAva,

I don't know what problem you were having with the code. I just tested it to change the Reference Name for a worksheet in another workbook.

You need to understand that this code will only change the Reference Name for the Activesheet and you are not supposed to refer to the new Reference Name within this procedure because the new Reference hasn't been set yet and the "variable" won't be recognised.

If you refer to the new sheet in any other procedure, then it will work.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

For those of you who would like to change all of their Sheet Reference Names to a contiguous names:

Sub BatchChange_WSRefName()
' Changes the Reference Names for all Worksheets
' in the active Workbook to Sheet + incrementing integer
Dim i As Integer, ws As Worksheet
i = 0
' Change to Temp first to prevent Naming errors
For Each ws In ActiveWorkbook.Worksheets
    i = i + 1
    On Error Resume Next
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Temp" & i
    On Error GoTo 0
Next ws
' Change to Sheet + incrementing integer
i = 0
For Each ws In ActiveWorkbook.Worksheets
    i = i + 1
    On Error Resume Next
    ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _
        "Sheet" & i
    On Error GoTo 0
Next ws
Set ws = Nothing
End Sub

I hope you like this one VBAva! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[DrBowes]

The VBComponent stuff is very useful so thanks and have a star.

The only comment I would make is that I have always thought that by creating object variables (using Set) code will run faster (though in most cases this wil be negligible).

 

[TonyJollans]

Hi All,

Can I offer a warning and a little explanation of this? It is possible to change Codenames (or Reference Names) in code, as Mike has shown, and there may be good reasons for doing it but it is not something for the faint of heart, and you must understand what you are doing.

VBA is compiled, not interpreted. This means that variable names are resolved at compile time - up front before any code is run (this is termed "early binding&quot. You would not change a variable name at run time and expect it to have immediate effect - imagine the confusion if it did.

The Codename of a Sheet is effectively a variable name and is used in VBA code in just the same way as any other variable name. If you change it via code, the change does have immediate effect and all later references to it must use (or will return) the new name. Direct references to it in the running procedure, however, have already been made and at the time that they were made the old name was in use and the new name did not exist.

There are many factors to consider and references in other procedures which you call, to either or both of the (old and new) names should be treated with immense caution.

Enjoy,
Tony

 

[Bowers74]

Good point Tony,

The two procedures I wrote are meant to be used as "administrative" tools and not to change the Code Names at runtime (I guess I should have explained that ).

There wouldn't really be any point to changing the Code Names for Worksheets in an already running and/or finished application because the Code Names in the Modules do not change automatically like formula references when you move cells.

Thanks for the explanation!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

By the way . . .

The FAQ that I have been referring to the whole time is located at:

faq707-4090

I hope that this has helped and will help others in the future!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Don't forget this FAQ! FAQ707-4090

It can help too!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

For those of you who haven't seen this yet!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

hello Mike

i have been away from the office for a few days so was not able to explain the problem i was having properly. ill give it another go and hope that there is a way around it.

As i said before the code works great for changing the name of the activesheet, but then i run into problems refering to the sheet by its code name rather than just the Name.

this problem exists even if the codename has not been changed, general referencing of sheets by codenames.

The command ActiveWorkbook.Sheets("Sheet1&quot.Select (or .Activate) can be used to select a sheet in the active workbook.

but you cannot (well I cannot) use ActiveWorkbook.SheetCodeName.Select (or .Activate) to select the sheet in the active workbook.

if SheetCodeName.Select is used on its own, it tries to select the sheet in the same workbook as the code, even if it is not active.

if Windows("Whatever.xls&quot.SheetCodeName.Select (or .Activate) or Workbook("Whatever.xls&quot.SheetCodeName.Select (or.Avtivate)
it gives an error.

How can you select a sheet in the active workbook by using the code name?

i think this is explained a little better
(i have not been at VBA for very long so if this is a no-brainer, im sorry)

Ava

 

[Bowers74]

Hi Ava,

Sorry for the confusion. You're right about referring to sheets in other workbooks! I removed this part of the FAQ until I can explain that process better. I hope that this didn't cause too much trouble.

You can still use the SheetCodeName.[A1] = OtherSheetCodeName.[A1] though, but only within ThisWorkbook.

Again, I apologize . . . .



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[VBAva]

Kein Problem!

As i need to refer to sheets in another workbook ill have to keep using the Namp property of the sheets instead of the code name.

would there be a way to stop the user from changing the sheetname? hmmm, that might be just as good, then i wont have to worry about getting errors, if i try to select a sheet if they changed the name.....

ill try to figure it out on monday

Ava

 

[Bowers74]

To refer to another workbook, or anything within another workbook, you will have to create object variables (Dim'ing and using Set).

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

Just wanted to let you all know that I updated my FAQ!

Refer to Worksheets more effecively in a Procedure - faq707-4090

I received a request from another user in an Email regarding, why he couldn't change the Sheet Reference (CodeName) at run-time. He was getting an error message.

I explained the fix to this at the bottom of the FAQ, below the code samples for changing the CodeNames at run-time.

Enjoy, and I hope this helps!



Happy Ho Ho!!! [Cheers]

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884