excel - 通过 ParamArray 打开多个工作簿时将工作簿分配给数组

Question

我使用 ParamArray 打开多个文件。

如何将工作簿分配给数组以单独访问它们？例如，获取每个工作簿的最后一行。

错误是

subscript out of range

Sub main()
 Call COMBINED_FILES("wk1.csv","wk2.xlsx","wk3.xls","wk4.csv")
End Sub

Sub COMBINED_FILES(ParamArray SOURCE_FILE_NAME_WITH_EXTENTION() As Variant) 

 Dim MASTER_WORK_BOOK_PATH As String
 MASTER_WORK_BOOK_PATH = ActiveWorkbook.Path & "\"

 Dim MASTER_WORK_BOOK As Workbook
 Set MASTER_WORK_BOOK = ActiveWorkbook

 Dim I As Integer

 For I = 1 To UBound(SOURCE_FILE_NAME_WITH_EXTENTION)
     Dim Resultworkbook() As Workbook
     Set Resultworkbook(I) = Workbooks.Open(MASTER_WORK_BOOK_PATH & SOURCE_FILE_NAME_WITH_EXTENTION(I))
     Debug.Print SOURCE_FILE_NAME_WITH_EXTENTION(I)
 Next I
End sub

Answer 1

COMBINED_FILES 中参数的括号不需要

Dim MASTER_WORK_BOOK As Workbook未使用

避免使用 ActiveWorkbook - How to avoid using Select in Excel VBA

避免 Integer - Why Use Integer Instead of Long?

VBA 中有一个约定，只对常量使用大写锁定

如果您在数组上循环，请尝试 For i = LBound(array) to Ubound(array)

ReDim ResultWorkbook(UBound(sourceFileNameWithExtension)) As Workbook用于初始化和维度化 ResultWorkbook() .如果未标注尺寸，则无法访问。

此代码有效:

Sub Main()
    CombinedFiles "wk1.xlsx", "wk2.xlsx", "wk3.xlsx", "wk4.xlsx"
End Sub

Sub CombinedFiles(ParamArray sourceFileNameWithExtension() As Variant)

    Dim masterWorkbookPath As String
    masterWorkbookPath = ThisWorkbook.Path & "\"

    Dim i As Long
    ReDim ResultWorkbook(UBound(sourceFileNameWithExtension)) As Workbook
    For i = LBound(sourceFileNameWithExtension) To UBound(sourceFileNameWithExtension)
        Set ResultWorkbook(i) = Workbooks.Open(masterWorkbookPath & sourceFileNameWithExtension(i))
        Debug.Print sourceFileNameWithExtension(i)
    Next i
End Sub