Getting subscript out of range error for one variable specifically in an array

Question

I am trying to set up a subroutine that protects and hides worksheets labeled as "restricted" and only protect worksheets labeled as "read only", as specified by the user in certain cells (see the image attached where the user would specify the status for each sheet). The code seems to work without a problem, only for the restricted portion of the sheets. However, as soon as I add the condition to check for the read only sheets, I get the subscript out of range error for the line marked by **, but the weird part is that its for the fourth element of the array always, so not sure why the first 3 work fine, or why adding the second condition stops it from working. If the "Case Read Only" and condition lines are taken out, it works fine. Maybe I am missing something obvious, I am fairly new to VBA as you can see by what is likely my inefficient code. Any help is very appreciated!

User table for selecting restricted and read only sheets

Private Sub Test()

Dim NumberOfSheets As Integer     'Variable for counting the sheets in model
NumberOfSheets = Application.Sheets.Count - 2 'Number of Sheets ignores Master Cmd sheet, and array starts at 0 so 2 is subtracted
Dim iCounter As Integer           'Counter for looping through the array
ReDim CheckWorksheets(NumberOfSheets) As String 'Restricted worksheet name variable declaration

Worksheets("Master Cmd").Activate 'Activate Master Cmd
Range("C7").Activate 'Activate first cell with sheet name

For iCounter = 0 To NumberOfSheets 'Loop to cycle through worksheet names

    CheckWorksheets(iCounter) = ActiveCell.Offset(iCounter, 0) 'Setting array variable equal to worksheet name

Next iCounter

For iCounter = 0 To NumberOfSheets 'Loop to change restricted worksheets status to very hidden

    Select Case ActiveCell.Offset(iCounter, 2)

        Case "Restricted"
            Worksheets(CheckWorksheets(iCounter)).Protect password:=AdminPassword 'Protecting sheets

            Worksheets(CheckWorksheets(iCounter)).Visible = xlSheetVeryHidden 'Condition to see if status is restricted in Master Cmd sheet

        Case "Read Only"
            **Worksheets(CheckWorksheets(iCounter)).Protect password:=AdminPassword**

    End Select

Next iCounter

End Sub

Debug.Print CheckWorksheets(iCounter) - is it what you expect? — BigBen
– BigBen, Commented May 28, 2020 at 16:49
That suggests the worksheet does not exist. Btw, try to avoid activate. — SJR
– SJR, Commented May 28, 2020 at 16:50
You are referring to two arrays with that statement: the CheckWorksheets array, and the Worksheets array. The index iCounter may be out of bounds for the first, and the name/string returned from CheckWorksheets may be out of bounds for the second. You'll have to check both to see which is causing the error. — PeterT
– PeterT, Commented May 28, 2020 at 16:54
Yes the 'Debug.Print CheckWorksheets(iCounter)' shows what I expect, but I still get the error for the same element in the array. Why should I avoid using activate? Is there something wrong with it? As for the second array, I am not sure why there are two arrays? I only declared one I thought. The 'Worksheets' section I was just trying to use it the same as the line 'Worksheets("sheetname"), does it not work in the same way when its a variable instead of quotation marks? — Alejandro Alarcon Carrillo
– Alejandro Alarcon Carrillo, Commented May 29, 2020 at 19:13

Tim Williams · Accepted Answer · 2020-06-02 16:07:36Z

0

I would ditch the array. Untested:

Private Sub Test()
    Const VAL_RESTRICTED = "Restricted"
    Dim wb As Workbook, c As Range

    Set wb = ThisWorkbook '? or ActiveWorkbook ?

    For Each c In wb.Worksheets("Master Cmd").Range("C7").Resize(wb.Worksheets.Count - 1, 1).Cells
        With wb.Worksheets(c.Value)
            .Protect Password:=AdminPassword 'Protecting sheets
            If c.Offset(0, 2).Value = VAL_RESTRICTED Then .Visible = xlSheetVeryHidden
        End With
    Next c
End Sub

Edit: to debug the original problem you could modify your code slightly.

Dim shtName 

'...
'...

For iCounter = 0 To NumberOfSheets

    shtName = CheckWorksheets(iCounter)
    Debug.Print iCounter, shtName

    Select Case ActiveCell.Offset(iCounter, 2)

        Case "Restricted"
            Worksheets(shtName).Protect password:=AdminPassword 
            Worksheets(shtName).Visible = xlSheetVeryHidden 

        Case "Read Only"
            Worksheets(shtName).Protect password:=AdminPassword

    End Select

Next iCounter

Note also it's good practice to explcitly qualify your Worksheets with a specific workbook. In this case it should be ThisWorkbook.Worksheets()

edited Jun 2, 2020 at 16:07

answered May 28, 2020 at 16:57

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Alejandro Alarcon Carrillo Over a year ago

Thank you for this, this seems to work but I would like to understand why the array doesn't work for my own learning purposes. Was there something I was missing or do the arrays have this kind of limitation?

Tim Williams Over a year ago

I can only guess at the problem - it may be because you do not have an explicit Workbook object defined. Is the code operating on its own workbook (ThisWorkbook) or another one (ActiveWorkbook)?

Collectives™ on Stack Overflow

Getting subscript out of range error for one variable specifically in an array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related