Array string not able to pass through to 'Worksheets'

Question

I'm trying to set up a code which essentially looks at an array of worksheets, and perform the exact same check for each of those. Unfortunately I seem to be getting the following message anytime I try to access the With... function (through which I would like the array to operate):

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

Although the variable

WshtNames(w)

does end up taking the name required, it cannot seem to store it so that it can be applied to the respective worksheet? Any help greatly appreciated. Code below:

    Option Explicit

Public Cell, WshtNames As Variant, Check_Tab, Bench_OAS_Level, Bench_OAS_Change, Bench_Spread_Dur, Bench_Duration, Bench_Convexity, ws As Worksheets, FirstHeaderColumn, LRow, LastRow As Long

Sub Definitions()

Set Bench_OAS_Level = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Bench OAS Level")
Set Bench_OAS_Change = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Bench OAS Change")
Set Bench_Spread_Dur = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Bench Spread Dur")
Set Bench_Duration = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Bench Duration")
Set Bench_Convexity = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Bench Convexity")
Set Check_Tab = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Check")

End Sub

Sub Check_OOT()

Call Definitions

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

Dim CalcRange, FirstHeaderRow, LastColumn As Range
Dim w As Long

WshtNames = Array("Bench_OAS_Level", "Bench_OAS_Change", "Bench_Spread_Dur", "Bench_Duration", "Bench_Convexity")

For w = LBound(WshtNames) To UBound(WshtNames)

  With Worksheets(WshtNames(w))

    Set LastColumn = .UsedRange.Find("Total", , xlValues, xlWhole)
    Set FirstHeaderRow = .UsedRange.Find("Level 1", , xlValues, xlWhole)
    FirstHeaderColumn = FirstHeaderRow.Column + 2
    LastRow = .Cells(Rows.Count, LastColumn.Column).End(xlUp).Row

    Set CalcRange = .Range(.Cells(FirstHeaderRow.Row + 2, FirstHeaderRow.Column + 3), .Cells(LastRow, LastColumn.Column))

       For Each Cell In CalcRange
          If Not IsError(Cell) Then
           If Cell > 500 Or Cell < -100 Then
           Cell.Font.Bold = True
           Cell.Interior.ColorIndex = 36
           Cell.EntireRow.Copy
              Call Copy_into_Check_Tab
           End If
        End If
       Next Cell

  End With

Next w

End Sub

Answer 1

It seems like the names of the worksheets are:

• Bench OAS Level
• Bench OAS Change
• Bench Spread Dur
• Bench Duration
• Bench Convexity

But that you're storing them in the array as:

• Bench_OAS_Level
• Bench_OAS_Change
• Bench_Spread_Dur
• Bench_Duration
• Bench_Convexity

(with underscores).

Does this fix the problem?

Also, it might be helpful to add a dictionary object to the public scope:

public worksheetDictionary as object

And change the definitions code to:

Sub Definitions()
    set worksheetDictionary = CreateObject("Scripting.Dictionary")

    With Workbooks("TEST Overview Tool Barclays May 2018")
        Call worksheetDictionary.Add("Bench_OAS_Level", .Worksheets("Bench OAS Level"))
        Call worksheetDictionary.Add("Bench_OAS_Change",.Worksheets("Bench OAS Change"))
        Call worksheetDictionary.Add("Bench_Spread_Dur", .Worksheets("Bench Spread Dur"))
        Call worksheetDictionary.Add("Bench_Duration", .Worksheets("Bench Duration"))
        Call worksheetDictionary.Add("Bench_Convexity", .Worksheets("Bench Convexity"))

        Set Check_Tab = Workbooks("TEST Overview Tool Barclays May 2018").Worksheets("Check")
    End With
End Sub

Then, you'd change the for loop in your main block to start like this:

For each k in worksheetDictionary.Keys
    With worksheetDictionary(k)

Hope that helps!

Answer 2

Try changing...

Dim CalcRange, FirstHeaderRow, LastColumn As Range
Dim w As Long

WshtNames = Array("Bench_OAS_Level", "Bench_OAS_Change", "Bench_Spread_Dur", "Bench_Duration", "Bench_Convexity")

 For w = LBound(WshtNames) To UBound(WshtNames)

     With Worksheets(WshtNames(w))

To...

Dim CalcRange, FirstHeaderRow, LastColumn As Range
Dim w As variant

WshtNames = Array("Bench_OAS_Level", "Bench_OAS_Change", "Bench_Spread_Dur", "Bench_Duration", "Bench_Convexity")

 Foreach w in wshtNames

     With Worksheets(w)

Answer 3

When using UBound and LBound you must use the second parameter to tell the compiler which dimension of the array that youre wanting to loop through.

 example:  For w = LBound(WshtNames,1) To UBound(WshtNames,1)

or

 example: For w = LBound(WshtNames,2) To UBound(WshtNames,2)

Youre getting out of range b.c the compiler doesnt knwo what/where youre trying to tell it to go.

I ran this for fun and works as expected

Here is the proper way to address setting workbook values

WshtNames = Array("Bench_OAS_Level", "Bench_OAS_Change", "Bench_Spread_Dur", "Bench_Duration", "Bench_Convexity")

set wb = Workbooks(“workbookname.filextension”)

For w = LBound(WshtNames) To UBound(WshtNames)

  With wb.Worksheets(WshtNames(w))