Runtime error "9" in VBA[excel] - Subscript out of range

Question

I am currently working on a code that takes the date from the user, opens a calendar, sees if the month is present, and if it isn't, creates a worksheet with that month name.

The code looks like this where m_y is a string, and has values such as "January 2014" [the " signs included]:

Sub addmonth(m_y)
  On Error Resume Next
  CalendarWorkbook.Worksheets(m_y).Select

  If Err.Number<>0 Then
     'code to add sheet and format it

I tried putting it in a With/End With command, I have no Option Explicit in the code. Other methods mentioned in answers such as using the .Range() instead of the .Select; however I had no luck in succeeding.

Any help provided would be appreciated.

no. when i change the name to say, "February 2014", it works because the sheet is present. However, when i put in "February 2015", it does not work. — user3094302, Jan 30 '14 at 03:42
i do not have option explicit because i generally, as a rule, use `On Error GoTo errdesc` where `errdesc: MsgBox Err.Number & " " & Err.Description` — user3094302, Jan 30 '14 at 06:42
I agree with @pnuts. That is no reason/excuse for not using Option Explicit :) — Siddharth Rout, Jan 30 '14 at 07:07
@SiddharthRout, Option Explicit and `On Error GoTo errdesc` are often contradictory, and the latter provides a smidgen more detail in most cases. That's why I prefer using that over Options Explicit. — user3094302, Feb 11 '14 at 02:18

score 1 · Accepted Answer · edited May 23 '17 at 10:27

.Select in most cases is the main cause of runtime errors. I believe you have another workbook open. INTERESTING READ

Try this another way which doesn't use .Select

Option Explicit

Sub test()
    addmonth ("""Feb2015""")
End Sub

Sub addmonth(m_y)
    Dim calendarworkbook As Workbook
    Dim ws As Worksheet

    Set calendarworkbook = ThisWorkbook

    On Error Resume Next
    Set ws = calendarworkbook.Worksheets(m_y)
    On Error GoTo 0

    If ws Is Nothing Then calendarworkbook.Worksheets.Add.Name = m_y
End Sub

Note: OERN (On Error Resume Next) should be used judiciously. Ensure that it just curbs the message for only the part that you want and not for the rest. Else it is not good error handling :)

score 0 · Answer 2 · answered Jan 30 '14 at 03:52

0

This worked for me

Sub test()
    addmonth ("""Feb2015""")
End Sub

Sub addmonth(m_y)
    Dim calendarworkbook As Workbook
    Set calendarworkbook = ThisWorkbook
    On Error Resume Next
    calendarworkbook.Worksheets(m_y).Select

    If Err.Number <> 0 Then
        With calendarworkbook.Worksheets.Add
            .Name = m_y
        End With
    End If
End Sub

answered Jan 30 '14 at 03:52

sam092

1,325
1
8
8

i get the same problem with the line `calendarworkbook.Worksheets(m_y).Select`, runtime error 9- subscript out of range – user3094302 Jan 30 '14 at 03:56
alright, then one possibility is that you have another error before that which is not resumed. See if this thread helps. http://stackoverflow.com/questions/15459332/on-error-resume-next-seemingly-not-working – sam092 Jan 30 '14 at 04:05
i just tried the same code on a different laptop, and it works fine; however on mine, it seems to crash. Could this be a software malfunction? – user3094302 Jan 30 '14 at 06:43
hard to say. best if you could upload your original spreadsheet so that we can take a look – sam092 Jan 30 '14 at 06:48
I can't. It contains confidential data that im working with... moreover it's against the company policies. – user3094302 Jan 30 '14 at 06:51
Have you tried duplicating the data in another file? Does the same thing happen? – sam092 Jan 30 '14 at 06:57

Runtime error "9" in VBA[excel] - Subscript out of range

2 Answers2