arrays - 在 msoFileDialogFilePicker 之后从 SelectedItems 创建数组

Question

尝试创建一个文件数组，稍后我可以“循环”以从每个文件中提取信息(文件的布局相同/在 excel 中表格)。收到“6”溢出错误，怀疑这是我的循环引起的错误？

Sub WorkOrderList()
'This compiles an array of Files by picking from the folder
Dim objFileDialog As Office.FileDialog
Dim SelectedFile As Variant
Dim arFiles() As Variant
Dim myCount As Integer
Set objFileDialog = Application.FileDialog(msoFileDialogFilePicker)
With objFileDialog
.AllowMultiSelect = True
.ButtonName = "Select"
.Title = "Work Order Picker"
If (.Show > 0) Then
End If
If (.SelectedItems.Count > 0) Then
For Each SelectedFile In .SelectedItems
Do Until SelectedFile = ""
myCount = myCount + 1
ReDim Preserve arFiles(1 To myCount)
arFiles(myCount) = SelectedFile
Loop
Next SelectedFile
Else
End If
End With
Set objFileDialog = Nothing
End Sub

我希望得到一个数组 arFiles，数组的每个元素都是从 msoFileDialogFilePicker 中选择的文件。

Answer 1

您不需要像这样嵌套循环来复制它们。你得到溢出是因为你有一个 Do无法退出的循环:

        For Each SelectedFile In .SelectedItems
            Do Until SelectedFile = ""      '<-- This will never be true.
                myCount = myCount + 1
                ReDim Preserve arFiles(1 To myCount)
                arFiles(myCount) = SelectedFile
            Loop
        Next SelectedFile

这将继续增加 myCount直到溢出。鉴于数组的大小总是与所选项目的数量相同，我建议使用简单的 For而是循环。调整数组大小一次( as @TimWilliams suggested )，然后在 SelectedItems 上使用索引器复制它们:

    myCount = .SelectedItems.Count
    If myCount > 0 Then
        ReDim arFiles(1 To myCount)
        Dim idx As Long
        For idx = 1 To myCount
            arFiles(idx) = .SelectedItems(idx)
        Next
    End If