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.
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
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:
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
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