INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

Refer to Worksheets more effecively in a Procedure by Bowers74
Posted: 30 Aug 03 (Edited 20 Dec 03)

I have noticed that a lot of the procedures in this forum contain references to Worksheets like Sheets("Sheet1").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") 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") 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").Activate should be sBudget.Activate.

You can also shorten the range references in your code as well:

sBudget.[A1].Select

instead of:

Worksheets("Budget").Range("A1").Select

Isn't that better?!

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

In addition you can change the Reference Name at run-time.  It is a bit more difficult than just changing it at design-time.  I wrote the following procedure that asks you if you want to change the Reference Name for the active Worksheet 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") = 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


Now, if you would like to change all of your Worksheet Reference Name's to 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


The problem with accessing a VBA project at run-time is that (at least w/ Office XP) you have to "allow" for this to happen.  Changing the Worksheets' Reference Names at run-time might cause an error message to appear because the acces to VBA Projects using macros has not been turned on.  To do this, do the following:

1.  In Excel goto Tools->Options . . .
2.  Select the Security tab and click the Macro Security . . . button.
3.  In the Trusted Sources tab check the Trust Access to Visual Basic Project checkbox.

That should do it!  Now you shouldn't have any problems accessing a VBA Project on your PC.


I hope this helps someone!


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close