Dynamically Naming Excel Worksheets the Headache Free Way

I’ve got a quickie, but a goodie for you today. I’m going to show you how you can avoid the Run-time Error ‘1004’ : You typed in an  invalid name for a sheet or chart error message by using an extremely simple custom function. It’s not often that I need to name excel sheets dynamically and when I do, I usually have a reasonably  good idea of what those names will be in advance. However, I’m sure this isn’t always the case and maybe someone out there is struggling with this right now. I know I struggled with this once upon a time when I was first learning vba. Let’s say for some strange reason I need to rename Sheet1 to each value in the Range(“A1:A3”). I also have some pretty strange strings in those cells.

A
1 My_Terrible[SheetName
2 AnotherBadSheetName?
3 And*Another

So I go ahead and write a quick little sub to loop through the range setting the Worksheet.Name = the cell values, but it throws the dreaded Run-time 1004 error on line 6.

Sub renameSheet() 
Dim rng As Range 
Dim i As Integer 
Set rng = Range("A1:A3") 
    For i = 1 To rng.Cells.Count 
        ActiveSheet.Name = rng.Cells(i).Value 
    Next i 
End Sub

Run-time 1004 Error Luckily, this run time error is actually helpful for a change. It tells us exactly what we need to do to fix the problem.  The worksheet name can not be more than 31 characters long and also  can not contain any of the characters:

  • :
  • \
  • /
  • ?
  • *
  • [
  • /

We’ll just create a private function to return a valid string utilizing the Replace() and Left() functions.

Private Function sheetNameSafeString(str As String) As String 
Dim strTemp As String 
'replace all invalid characters with underscores 
strTemp = Replace(str, "/", "_") 
strTemp = Replace(strTemp, "\", "_") 
strTemp = Replace(strTemp, "*", "_") 
strTemp = Replace(strTemp, "?", "_") 
strTemp = Replace(strTemp, "[", "_") 
strTemp = Replace(strTemp, "]", "_") 
'sheet.name can only be 31 characters 
strTemp = Left(strTemp, 31) 
'return 
sheetNameSafeString = strTemp 
End Function 

Now we can call our nifty little function from our original code, and voila! No more pesky runtime errors.

Sub renameSheet() 
Dim rng As Range 
Dim i As Integer 
Set rng = Range("A1:A3") 
For i = 1 To rng.Cells.Count 
    ActiveSheet.Name = sheetNameSafeString(rng.Cells(i).Value) 
Next i 
End Sub 
A
1 My_Terrible_SheetName
2 AnotherBadSheetName_
3 And_Another

I realize that this particular example would never happen in the real world. I just can’t imagine looping through a range renaming the same sheet over and over, but I can imagine looping through a range creating new worksheets with specified names. I also realize that this is beginner level vba, but we were all beginners once. Besides, it keeps me writing while I’m working a bigger project to post for you all. Until next time, get imaginative. What other characters wouldn’t you want to show up in your worksheet name? I only covered the characters that cause an error. Maybe you can figure out a way to get rid of the trailing underscore in cell A2. Most importantly, stop stressing out and have fun writing code again. Semper Cogitet Christopher J. McClellan

Advertisements

,

  1. Leave a comment

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: