Excel VBA / Sheets.Add.Name Loop throws Error 9

Question

I'm new to Excel VBA and started to build a time tracking workbook for learning. A part of that is a For-Loop which shall add one named worksheet for every month in a year:

Sub newyear()

Dim month(12) As String
Dim i As Integer

month(1) = "Januar"
month(2) = "Februar"
...
month(12) = "Dezember"

For i = 1 To 12
On Error Resume Next
Sheets.Add(Tabelle1).Name = month(i)
MsgBox Err.Number    <- this throws Error 9: "Subscript Out Of Range" after
                        every worksheet added during the loop
Next i

End Sub

During Runtime while the loop is adding worksheet after worksheet the MsgBox pops up after every single added sheet with Error 9: "Subscript Out Of Range".

I don't know why this is happening, started reading up quite a bit on the web and still have no solution.. maybe I'm missing something basic, because I'm a beginner.

Please help me.

Thanks in advance!

Please mark as the solution if my code fits with you

Karthick Gunasekaran
– Karthick Gunasekaran

2016-03-31 10:23:30 +00:00
Commented Mar 31, 2016 at 10:23 — Karthick Gunasekaran
– Karthick Gunasekaran, Commented Mar 31, 2016 at 10:23

Karthick Gunasekaran · Accepted Answer · 2016-03-31 14:03:06Z

2

Please see the below code.

Sub newyear()
On Error Resume Next
Dim month(12) As String
Dim i As Integer
month(1) = "January"
month(2) = "February"
month(3) = "March"
month(4) = "April"
month(5) = "May"
month(6) = "June"
month(7) = "July"
month(8) = "August"
month(9) = "September"
month(10) = "October"
month(11) = "November"
month(12) = "Dezember"
Dim ws As Worksheet
For i = 1 To 12
    With ThisWorkbook
        If Worksheets(month(i)).Name <> month(i) Then
            Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
            ws.Name = month(i)
        End If
    End With
    If Err.Description <> "" Then
        Err.Clear
        'do what you want to do 
    End If
Next i
End Sub

edited Mar 31, 2016 at 14:03

answered Mar 31, 2016 at 10:11

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Fr4gFr0g Over a year ago

Thanks for the suggestion! Sadly after adding 'MsgBox = Err.Number' before 'Next i' to your code Error 9 ist still popping up after each added sheet :(.

Karthick Gunasekaran Over a year ago

@Fr4gFr0g, May i know why you adding Err.Number before Next i

Karthick Gunasekaran Over a year ago

@Fr4gFr0g, edited the above answer. Pls mark as solution if its correct.

Fr4gFr0g Over a year ago

Okay, so initially I wanted the loop to function correctly, even if sheets for some months already exist. When that's the case the loop adds an unnamed sheet for every existing one <- I wanted to detect this by checking for error like this If Err.Description <> "" Then activesheet.delete (pseudo code). The Problem is that the named sheets also get deleted because for every sheet added (even if it's not already existing) Error 9 pops up.. and sadly, with your solution this Error 9 still happens.

Fr4gFr0g Over a year ago

Well.. this just clears the Error. It still doesn't prevent it from happening in the first place.

|

Ivan Tokarev · Accepted Answer · 2016-03-31 14:18:13Z

0

I am not sure why you want to avoid having an error. You can probably make a longwinded code for that, but there no use for it. You can also try something shorter:

Sub newyear()

   Dim month(12) As String
   Dim i As Integer

   month(1) = "January"
   month(2) = "February"
   month(3) = "March"
   month(4) = "April"
   month(5) = "May"
   month(6) = "June"
   month(7) = "July"
   month(8) = "August"
   month(9) = "September"
   month(10) = "October"
   month(11) = "November"
   month(12) = "Dezember"

   On Error Resume Next
   For i = 1 To 12
       If Worksheets(month(i)) Is Nothing Then
           Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = month(i)
       End If
   Next

End Sub

answered Mar 31, 2016 at 14:18

Ivan Tokarev

1016 bronze badges

2 Comments

Fr4gFr0g Over a year ago

Yeah.. well that's the conclusion I also cam to: Why actually try to avoid the error?! But, you know, I thought when my code throws an error than obviously I coded something wrong, no? I thought the goal is to write clean code and naive me thought clean code shouldn't generate errors. Whatever thanks for the input :).

Ivan Tokarev Over a year ago

Sometimes an error is not a problem, but a feature :) In VBA, as in Excel, you can use errors to your benefit. It's a good shortcut for many cases.

Collectives™ on Stack Overflow

Excel VBA / Sheets.Add.Name Loop throws Error 9

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related