CODEWITHSUNDEEP
vbaexcel
Garry W
Garry W

Reputation: 311

VBA: For loop used for an array gives error

This following code gives me "For each control variable on arrays must be Variant" error. But ws_names is an array of variant, if I'm not mistaken. Can someone please explain? Thank you!

Public Sub test12()
    Dim ws_names() As Variant
    ws_names = Array("Sheet2", "Sheet3")
    Dim ws_name As String
    For Each ws_name In ws_names()
        ThisWorkbook.Worksheets(ws_name).Visible = False
    Next ws_name
End Sub

Upvotes: 3

Views: 902

Answers (3)

Irvin Escalante
Irvin Escalante

Reputation: 31

Try this piece of code

Public Sub test12()

    Dim ws_names() As Variant
    ws_names = Array("Sheet2", "Sheet3")
    Dim ws_name As Variant
    For Each ws_name In ws_names()
        'ThisWorkbook.Worksheets(ws_name).Visible = False
        Debug.Print ws_name
    Next ws_name

End Sub

Upvotes: 2

Valon Miller
Valon Miller

Reputation: 1156

The syntax of a For Each...Next Statement loop is: For Each element in group ... Next element

For collections, element can only be a Variant variable, a generic object variable, or any specific object variable. For arrays, element can only be a Variant variable.

Thus, the following would work:

Public Sub test12()
    Dim ws_names() As Variant
    ws_names = Array("Sheet2", "Sheet3")
    Dim ws_name As Variant
    For Each ws_name In ws_names()
        ThisWorkbook.Worksheets(ws_name).Visible = False
    Next ws_name
End Sub

However, as @GMalc pointed out in their answer, a loop is not even necessary for this one.

Upvotes: 2

GMalc
GMalc

Reputation: 2628

You don't need to loop through the array

Dim ws_names() As Variant
ws_names = Array("Sheet2", "Sheet3")

Sheets(ws_names).Visible = False

You can also use a one liner

Sheets(Array("Sheet2", "Sheet3")).Visible = False

Upvotes: 3

Related Questions