CODEWITHSUNDEEP
vbaexcel
Wakan Tanka
Wakan Tanka

Reputation: 8042

Run-time error '9': Subscript out of range when accessing Sheets

I have following function which returns me list of current sheets

Function getListOfSheetsW() As Variant
  Dim i As Integer
  Dim sheetNames() As Variant

  ReDim sheetNames(1 To Sheets.Count)
  For i = 1 To Sheets.Count
    sheetNames(i) = Sheets(i).name
  Next i

  getListOfSheetsW = sheetNames
End Function

This function returns array starting at position 1. My goal was to create same function but starting with position 0, I've tried:

Function getListOfSheetsNW() As Variant
  Dim i As Integer
  Dim sheetNames() As Variant

  ReDim sheetNames(Sheets.Count - 1)
  For i = 0 To Sheets.Count
    sheetNames(i) = Sheets(i + 1).name
  Next i

  getListOfSheetsNW = sheetNames
End Function

But this return me:

Run-time error '9': Subscript out of range

What is wrong with my code?

PS: I'm calling those functions following way:

Sub callGetListOfSheetsW()
    Dim arr() As Variant
    ' arr = getListOfSheetsW()
    arr = getListOfSheetsNW()

    MsgBox arr(1)
    MsgBox arr(2)

End Sub

Upvotes: 1

Views: 102

Answers (1)

user4039065
user4039065

Reputation:

The worksheet count will always be one based. 

Function getListOfSheetsNW() As Variant
  Dim i As Integer
  Dim sheetNames() As Variant

  ReDim sheetNames(Sheets.Count - 1)
  For i = 0 To Sheets.Count - 1    '<~~This. Alternately as For i = 0 To UBound(sheetNames)
    sheetNames(i) = Sheets(i + 1).name
  Next i

  getListOfSheetsNW = sheetNames
End Function

Upvotes: 2

Related Questions