下标超出范围运行时错误9

我正在创建一个函数，该函数从两个 Excel 文件中读取列标题，然后将这些列标题放入复选框中，以便用户可以检查他将使用哪些列。当我使用一个文件执行此操作时它可以工作，但是当我修改它以使用两个文件时，我收到“运行时错误 9：下标超出范围”并突出显示该行 => Set wks2 = ActiveWorkbook.Worksheets(SheetName2)。

即使出现此错误消息，仍然适用于第一个文件，但不适用于第二个文件。有人可以帮我找到这个错误消息的原因吗？预先非常感谢您。

 Function CallFunction(SheetName1 As Variant, SheetName2 As Variant) As Long
' This is a function used to retrieve column titles and place them as checkboxes in a listBox

 Dim jTitles(200) As String
 Dim sTitles(200) As String
 Dim titless As Integer
 Dim titlesj As Integer
 Dim wks1 As Worksheet
 Dim wks2 As Worksheet
 Dim Item(200) As String

 SPathName = Range("F18").Value
 SFilename = Range("F19").Value

 JPathName = Range("F22").Value
 JFilename = Range("F23").Value

 Workbooks.Open Filename:=SPathName & "\" & SFilename
 Workbooks.Open Filename:=JPathName & "\" & JFilename

 Set wks1 = ActiveWorkbook.Worksheets(SheetName1)

 For j = 1 To 199
     If Trim(wks1.Cells(4, j).Value) = "" Then
        titlesj = j - 1
        Exit For
    End If
        jTitles(j - 1) = wks1.Cells(4, j).Value
 Next

 j = 1

 ' Add column titles from files into the listbox as checkboxes
 For j = 0 To titlesj
    Sheet1.ListBox1.AddItem jTitles(j)
    Sheet1.ListBox3.AddItem jTitles(j)
 Next

 Set wks2 = ActiveWorkbook.Worksheets(SheetName2)  ' <=== HERE POPS THE ERROR MESSAGE

 For s = 1 To 199
    If Trim(wks2.Cells(1, s).Value) = "" Then
        titless = s - 1
        Exit For
    End If
        sTitles(s - 1) = wks2.Cells(1, j).Value
 Next

 s = 1

 For s = 0 To titless
    Sheet1.ListBox2.AddItem sTitles(s)
    Sheet1.ListBox4.AddItem sTitles(s)
 Next

    Workbooks(JFilename).Close
    ' Workbooks(SFilename).Close

End Function

当该工作簿中不存在指定的工作表名称时，在这些情况下会出现下标超出范围错误Worksheets收藏。

我注意到您有两个打开的工作簿，指定为：

Workbooks.Open Filename:=SPathName & "\" & SFilename
Workbooks.Open Filename:=JPathName & "\" & JFilename

但是，您的两个工作表分配仅涉及ActiveWorkbook.

错误的原因肯定是SheetName2不存在于ActiveWorkbok（由指定JFilename)

尤其是在处理多本书或工作表时，最好避免使用激活/选择方法-- 否则您需要跟踪哪个工作簿/工作表/等。是“Active”，这会导致意大利面条式代码和大量不必要的调用.Activate method.

I know that SheetName1存在于JFilename，我假设SheetName2存在于工作簿中SFileName.

相反，定义两个Workbook变量：

Dim wb1 as Workbook
Dim wb2 as Workbook

分配结果Workbooks.Open这些工作簿的方法：

Set wb2 = Workbooks.Open(Filename:=SPathName & "\" & SFilename)
Set wb1 = Workbooks.Open(Filename:=JPathName & "\" & JFilename)

Now, wb1是“活动”工作簿，因此工作表分配：

Set wks1 = wb1.Worksheets(SheetName1)

后来为了Sheetname2:

Set wks2 = wb2.Worksheets(Sheetname2)

否则，您的工作表名称或发送给此函数的字符串参数存在拼写错误。仔细检查/调试该值SheetName2是正确的and它存在。