VBA: Qualify Worksheets for dynamic range

Question

I am just getting started with VBA.

I have a workbook containing many numbered sheets (1,2,3,4 etc).

I now have read that Qualifying a Worksheet as per below:

Dim 1 As Worksheet: Set 1 = ThisWorkbook.Worksheets("1")

then calling it as:

1.Range("B15").Value

is faster than activating it (as I have done) eg.

Worksheets(1).Activate

I would like though to access my sheets dynamically through a loop, as in:

Worksheets(i).Activate

Can this be done with the Qualifying method? I'm stuck.

Answer 1

Worksheets have an Index which is the order they are currently in within the workbook. So the leftmost worksheet is Worksheet(1). This is not necessarily the same as the name of the e.g. Worksheet("Sheet1") or Worksheet("1").

Say I have sheets "2" ,"1", "3", in that order in the workbook:

1. Sheet 2 A1 has "First sheet named "2" "
2. Sheet 1 A1 has "Second Sheet named "1" "
3. Sheet 3 A1 has "Third sheet named "3" "

Then run the following:

Option Explicit

Public Sub test()

    Dim arr(), i As Long
    arr = Array("1", "2", "3") '<== Sheet names

    Debug.Print "Looping by name: "
    For i = LBound(arr) To UBound(arr)           '<== Looping by name
        Debug.Print ThisWorkbook.Worksheets(arr(i)).Range("A1").Value
    Next i

    Debug.Print String(40, Chr$(61))
    Debug.Print vbNewLine
    Debug.Print "Looping by sheet index: "

    For i = 1 To ThisWorkbook.Worksheets.Count   '<== Looping by index (order from left to right)
        Debug.Print ThisWorkbook.Worksheets(i).Range("A1").Value
    Next i

End Sub

You get:

Note: You can also For Each over the Worksheet collection which will loop in the same order as by Index skipping any Chart sheet objects.

Answer 2

I wouldn't use a number as a variable name. You can loop through the worksheets collection in two ways - the first is marginally more efficient I believe but doubt you'll notice the difference.

As you say there is rarely any need to actually activate a sheet.

Sub x()

Dim ws As Worksheet, i As Long

'option 1
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    ws.Range("A1") = 1 'no need to activate first
Next ws

'option 2
For i = 1 To ActiveWorkbook.Worksheets.Count
    Worksheets(i).Activate
Next i

End Sub

Answer 3

Everything is simple:

1) When you pass string (say, "1"), you're referring to sheet's name

2) When you pass number (say, 1), you're referring to position in sheets' collection.

The important thing is to understand what is sheets' collection.

There are several types of sheets: worksheet, chart sheet, dialog sheet (also two types of macro sheets; you can see all five of them via right-click sheet's tab -> Insert). So, it is very important to understand which collection to use.

Say, you have four sheets (from left to right):

1. Dialog sheet (name "dialog1")
2. Worksheet (name "sheet1")
3. Chart sheet (name "chart1")
4. Worksheet (name "sheet2")

So, all sheets can be enumerated through Sheets collection. Thus:

1. Sheets(1) returns "dialog1" dialog sheet
2. Sheets(2) returns "sheet1" worksheet
3. Sheets(3) returns "chart1" chart sheet
4. Sheets(4) returns "sheet2" worksheet

If you need the collection of only chart sheets, you use Charts collection. In our case Charts(1) will return "chart1" chart sheet.

If you need the collection of only worksheets, you use Worksheets collection. In our case Worksheets(2) will return "sheet2" worksheet.

The same goes for dialog sheets - use DialogSheets collection (although it's not visible in IntelliSense, you can see it: F2 -> Right-click -> Show Hidden Members).

And last but not the least, every sheet has Index property. But be careful with it since it returns the value of index in Sheets collection. This means that in our case Worksheets(2).Index will return 4.