检查项目是否在ParamArray VBA中

Question

我正在编写一个函数，以便在工作簿的所有工作表上运行一些宏，但排除的工作表列表除外。我将排除的工作表列表作为ParamArray传递，但在根据ParamArray中的列表检查当前工作表时遇到了问题。

Public Sub RunThingsOnSheets(ParamArray excludedSheets())

Dim ws as Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws Not In excludedSheets Then  'In Pseudocode this is what I want
        "do things"
    End If
Next ws

End Sub

Public Sub Test()

Call RunThingsOnSheets(SheetOne, SheetTwo)

End Sub

Answer 1

Public Sub RunThingsOnSheets(ParamArray excludedSheets())
Dim ws As Worksheet, o, inList As Boolean

    For Each ws In ActiveWorkbook.Worksheets
        inList = False
        For Each o In excludedSheets
            If o.Name = ws.Name Then
                inList = True
                Exit For
            End If
        Next
        If Not inList Then
            Debug.Print "not in excludedsheets: " & ws.Name
        End If
    Next ws

End Sub

Public Sub Test()

    RunThingsOnSheets Sheet1, Sheet3

End Sub

Answer 2

在使用VBA作为工具包的一部分时，有一个通用的数组搜索函数是有用的，因为语言中没有内置的函数。

Public Function ArraySearch(ByVal a As Variant, v As Variant, _
                            Optional ByRef found_index As Variant) As Boolean
    Dim i As Long

    ArraySearch = False

    If Not IsArray(a) Then Exit Function

    For i = LBound(a) To UBound(a)
        If (VarType(a(i)) And vbArray) <> 0 Then
            ' Don't compare arrays
        ElseIf (VarType(v) And vbArray) <> 0 Then
            ' Don't compare arrays
        ElseIf VarType(a(i)) = VarType(v) Then
            If IsObject(v) Then
                If a(i) Is v Then
                    ArraySearch = True
                    Exit For
                End If
            ElseIf a(i) = v Then
                ArraySearch = True
                Exit For
            End If
        ElseIf VarType(a(i)) = vbError Or _
            VarType(v) = vbError Or _
            VarType(a(i)) = vbObject Or _
            VarType(v) = vbObject _
        Then
            ' Guard against type mismatch
        ElseIf a(i) = v Then
            ArraySearch = True
            Exit For
        End If
    Next

    If ArraySearch And Not IsMissing(found_index) Then found_index = i
End Function

然后可以使用此函数搜索ParamArray。

Public Sub RunThingsOnSheets(ParamArray excludedSheets())
    Dim ws as Worksheet

    For Each ws In ActiveWorkbook.Worksheets: Do
        If ArraySearch(excludedSheets, ws) Then Exit Do

        ' do things
    Loop While False: Next
End Sub

两个注释：

1. 我在每个循环中都使用了一个idiom to simulate C's "continue" statement。根据您的编程风格首选项，您可能喜欢也可能不喜欢这一点。
2. 此ArraySearch实现按值传递数组。这是支持ParamArrays所必需的。但是，如果您经常使用它来搜索由数千个元素组成的庞大数组，那么这里传递的值可能会造成内存或性能问题。