arrays - 循环遍历工作表范围

Question

我正在尝试遍历一系列工作表。但是，从工作表的范围中选择工作表时出现错误。

Sub Calc_sheets()

Dim sheetnames As Variant
sheets("Calc_sheets").Select
'sheetnames = Worksheets("Calc_sheets").Range("a2:a4").Value # returns an error in row "Sheet = sheetnames(k)"

sheetnames = Array("Sheet1", "Sheet2") # works fine

For k = 0 To Application.CountA(sheetnames) - 1

        Sheet = sheetnames(k)
        sheets(Sheet).Select
        ActiveSheet.Calculate
Next k

End Sub

你知道如何解决这个问题吗？

最好的，

Answer 1

你必须使用 Set

Dim Sheet AS Worksheet
Set Sheet = sheetnames(k)

但你可以把它缩短到

Sub Calc_sheets()
    Dim sheetnames As Variant
    sheetnames = Array("Sheet1", "Sheet2")

    Dim Sheet As Variant
    For Each Sheet In sheetnames
        Sheets(Sheet).Calculate
    Next
End Sub

甚至更短:

Sub Calc_sheets()
    Dim Sheet As Variant
    For Each Sheet In Array("Sheet1", "Sheet2")
        Sheets(Sheet).Calculate
    Next
End Sub