时间戳

首页 > 解决方案 > 获取特定于数组中的一个变量的下标超出范围错误

arrays - 获取特定于数组中的一个变量的下标超出范围错误

问题描述

我正在尝试设置一个子例程来保护和隐藏标记为“受限”的工作表，并且只保护标记为“只读”的工作表，由用户在某些单元格中指定（请参阅用户指定状态的附加图像每张纸）。该代码似乎没有问题，仅适用于工作表的受限部分。但是，只要我添加条件来检查只读表，我就会得到由 ** 标记的行的下标超出范围错误，但奇怪的是它总是用于数组的第四个元素，所以不知道为什么前 3 个工作正常，或者为什么添加第二个条件会阻止它工作。如果去掉“Case Read Only”和条件行，它工作正常。也许我错过了一些明显的东西，我对 VBA 相当陌生，正如您所看到的可能是我效率低下的代码。非常感谢任何帮助！

用于选择受限和只读工作表的用户表

在此处输入图像描述

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

标签： arraysvbapassword-protection

解决方案

我会放弃阵列。未经测试：

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

编辑：要调试原始问题，您可以稍微修改代码。

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

Worksheets另请注意，使用特定工作簿明确限定您是一种很好的做法。在这种情况下应该是ThisWorkbook.Worksheets()

推荐阅读