动态数组的 VBA 错误

Question

我是 VBA 初学者。我正在尝试编写一个宏来检查多个文件中是否存在丢失的数据。如果数据丢失，它将把文件名添加到一个数组中，最后给出数据不完整的文件列表。

我需要一个可变数组，但无论我做什么来修复它，我都会不断收到错误。请参阅下面的代码。任何帮助将非常感激！

Sub Macro()

Dim Path1 As String
Dim Path2 As String
Dim Path3 As String
Dim Path4-5-6 As String
Dim FileName_1 As String
Dim FileName_2 As String
Dim FileName_3 As String
Dim FileName_4 As String
Dim FileName_5 As String
Dim FileName_6 As String
Dim W As Long
Dim Missing As Boolean
Dim len_ListMissing As Long
Dim Found As Boolean
Dim k As Variant
Dim dict
Set dict = CreateObject("Scripting.Dictionary")
Dim n As Integer, i As Variant
ReDim ListMissing(n)

Path1 = "https://_path1_"
Path2 = "https://_path2_"
Path3 = "https://_path3_"
Path4-5-6 = "https://_path4-5-6_"
FileName_1 = "file1.xlsx"
FileName_2 = " file2.xlsx"
FileName_3 = " file3.xlsx"
FileName_4 = " file4.xlsx"
FileName_5 = " file5.xlsx"
FileName_6 = " file6.xlsx"

W = Workbooks("_currentfile_.xlsm").Sheets("Sheet1").Cells(1, "A").Value
dict.Add Path1 & FileName_1, FileName_1
dict.Add Path2 & FileName_2, FileName_2
dict.Add Path3 & FileName_3, FileName_3
dict.Add Path4-5-6 & FileName_4, FileName_4
dict.Add Path4-5-6 & FileName_5, FileName_5
dict.Add Path4-5-6 & FileName_6, FileName_6

ListMissing(0) = "test"

For Each k In dict.keys
Workbooks.Open (k)
    If Workbooks(dict(k)).Sheets("Sheet2").Cells(W + 20, “X”).Value = "" Or Workbooks(dict(k)).Sheets("Sheet2").Cells(W + 20, “X”).Value = "0" Or Workbooks(dict(k)).Sheets("Sheet2").Cells(W + 20, “Y”).Value = "" Or Workbooks(dict(k)).Sheets("Sheet2").Cells(W + 20, “Y”).Value = "0" Or Workbooks(dict(k)).Sheets("Sheet2").Cells(W + 20, “Z”).Value = "" Or Workbooks(dict(k)).Sheets("Sheet2").Cells(W + 20, “Z”).Value = "0" Then
        Missing = True
    Else
        Missing = False
    End If
    
    n = 0
    If Missing = True Then
        For Each i In ListMissing
            If i = dict(k) Then
                Found = True
            Else
                Found = False
            End If
                
            If Found = False Then
                n = n + 1
                ReDim Preserve ListMissing(1 To n)
                ListMissing(n) = dict(k)
            Else

            End If

        Next i
    End If
Workbooks(dict(k)).Close SaveChanges:=False
Next

len_ListMissing = UBound(ListMissing) - LBound(ListMissing) + 1
If len_ListMissing = 1 Then
    MsgBox "Data is complete."
Else
    MsgBox "Data is incomplete in the below files:" & vbCrLf & vbCrLf & ListMissing(i)
End If

End Sub

Answer 1

这里有一个使用字典存储缺失状态（代码而不是测试集）的示例，

dict.Add sFullPath1, true
dict.Add sFullPath2, true

for each k in dict
   set wbToCheck = Workbooks.Open(k)
   if wbToCheck.Sheets("Sheet2").Cells(W + 20, “X”).Value = "" then 'only one condition in this example
     dict(k)= true
   else
     dict(k) = false
   end if
   wbToCheck.Close SaveChanges:=False
next

因此您不需要这个额外的数组。