Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Refer to Worksheets more effecively in a Procedure
VBA How To
I have noticed that a lot of the procedures in this forum contain references to Worksheets like [color blue]Sheets("Sheet1").Activate[/color].
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 [color blue]sBudget[/color] (or even mySheet). Now you can refer to that Worksheet in any module in your application as [color blue]sBudget[/color] (or mySheet).
This can be, and should be done for all of the Worksheets in a workbook. for example:
[color red]Worksheets("Annual Budget").Activate[/color] should be [color green]sBudget.Activate[/color].
You can also shorten the range references in your code as well:
[color green]
[/color]
instead of:
[color red]
[/color]
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
[color blue]
[/color][color red]ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = NewRef[/color][color blue]
[/color]
Now, if you would like to change all of your Worksheet Reference Name's to contiguous Names:
[color blue]
[/color]
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!![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
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 [color blue]sBudget[/color] (or even mySheet). Now you can refer to that Worksheet in any module in your application as [color blue]sBudget[/color] (or mySheet).
This can be, and should be done for all of the Worksheets in a workbook. for example:
[color red]Worksheets("Annual Budget").Activate[/color] should be [color green]sBudget.Activate[/color].
You can also shorten the range references in your code as well:
[color green]
Code:
sBudget.[A1].Selectinstead of:
[color red]
Code:
Worksheets("Budget").Range("A1").SelectIsn'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
[color blue]
Code:
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:
Code:
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 SubNow, if you would like to change all of your Worksheet Reference Name's to contiguous Names:
[color blue]
Code:
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 SubThe 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!