How to dynamically add elements in an array inside a loop

Question

I have a workbook with several sheets and I need to create an array with only the wanted sheets. So I have this code that skips some hardcoded sheet names, but I don't know how to add the wanted sheets in my array 'sheets_names_array'. I have this code:

    ' make an array with the sheet names we want to parse
Dim sheets_names_array() As Variant, sheet As Variant

For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Qlik Ingestion"
            'Do nothing
        Case "Dropdown Values"
            'Do nothing
        Case "VBmacro"
            'Do nothing
        Case Else
             'MsgBox ws.Name
             sheets_names_array.Add (ws.Name)
    End Select
Next

But the 'Add' method doesnt work. Do you know how to solve this please? I have seen documentation that uses ReDim but I am not sure how to loop through the elements of the 'sheets_names_array' table

yes, it could be a collection. is it looped the same way? can I do For Each sheet In sheets_names_array ? — Electra
– Electra, Commented Sep 27, 2022 at 11:46

Pᴇʜ · Accepted Answer · 2022-09-27 11:51:34Z

1

You can use a collection instead

Dim sheets_names_col As New Collection

and add your items like

sheets_names_col.Add ws.Name

Dim sheets_names_col() As New Collection

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Qlik Ingestion", "Dropdown Values", "VBmacro"
            ' do nothing
        Case Else
            sheets_names_col.Add ws.Name
    End Select
Next ws

And you can loop it like

Dim sheet As Variant
For Each sheet In sheets_names_col
    Debug.Print sheet
Next sheet

answered Sep 27, 2022 at 11:51

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

How to dynamically add elements in an array inside a loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related