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

Question

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

For i = 0 To Sheets.Count - 1 as 0 counts.

Alex K.
– Alex K.

2015-10-29 17:37:26 +00:00
Commented Oct 29, 2015 at 17:37 — Alex K.
– Alex K., Commented Oct 29, 2015 at 17:37

user4039065 · Accepted Answer · 2015-10-29 17:39:31Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related