下标超出范围运行时错误9 [英] Subscript out of range run time error 9

问题描述

我正在创建一个从两个excel文件读取列标题的功能，然后将这些列标题放在复选框中，以便用户可以检查他将使用哪些列。当我使用一个文件进行操作时，它工作，但是当我修改它以处理两个文件时，我得到运行时错误9：下标超出范围，并突出显示行=>设置wks2 = ActiveWorkbook.Worksheets（SheetName2）。

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

 函数CallFunction（SheetName1作为Variant，SheetName2作为变体）As Long 
'This是一个用于检索列标题的函数，并将它们作为复选框放在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）。 b 
 $ b Workbooks.Open文件名：= SPathName& \& SFilename 
 Workbooks.Open文件名：= JPathName& \& JFilename 
 
设置wks1 = ActiveWorkbook.Worksheets（SheetName1）
 
对于j = 1到199 
如果修剪（wks1.Cells（4，j）.Value） =然后
 titlesj = j  -  1 
退出
结束如果
 jTitles（j  -  1）= wks1.Cells（4，j）.Value 
下一个
 
j = 1 
 
'将列标题从文件添加到列表框中作为复选框
对于j = 0到titlesj 
 Sheet1.ListBox1.AddItem jTitles （j）
 Sheet1.ListBox3.AddItem jTitles（j）
下一个
 
设置wks2 = ActiveWorkbook.Worksheets（SheetName2）'< ===这里POPS错误信息
 
对于s = 1至199 
如果修剪（wks2.Cells（1，s）.Value）=然后
 titless = s  -  1 
退出
 End If 
 sTitles（s  -  1）= wks2.Cells（1，j）.Value 
 Next 
 
s = 1 
 
对于s = 0要无止息
 Sheet1.ListBox2.AddItem sTitles（s）
 Sheet1.ListBox4.AddItem sTitles 
下一个
 
工作簿（JFilename ）。关闭
'工作簿（SFilename）。关闭
 
结束函数
  

解决方案

在这些情况下，当该工作簿工作表集合中不存在指定的工作表名称时， p>

我注意到你有两个指定的开放工作簿：

  Workbooks.Open文件名：= SPathName& \& SFilename 
 Workbooks.Open文件名：= JPathName& \& JFilename 
  

但是，您的工作表分配仅引用 ActiveWorkbook 。

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

特别是在工作时有多本书籍或工作表，总是最好避免使用Activate /选择方法 - 否则您需要跟踪哪个工作簿/工作表/等。是活动，这使得意大利面条代码和大量不必要的调用到 .Activate 方法。

我知道 SheetName1 存在于 JFilename 中，我假设 SheetName2 存在于工作簿 SFileName 。

相反，定义两个工作簿变量：

  Dim wb1 as Workbook 
 Dim wb2 as Workbook 
  

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

 设置wb2 = Workbooks.Open（文件名：= SPathName&\& SFilename） 
设置wb1 = Workbooks.Open（文件名：= JPathName&\& JFilename）
  

现在， wb1 是活动工作簿，所以工作表分配：

 设置wks1 = wb1.Worksheets（SheetName1）
  

code>她etname2 ：

 设置wks2 = wb2.Worksheets（Sheetname2）
  

否则，您的工作表名称或发送到此功能的字符串参数中会出现打字错误。双击/调试 SheetName2 的值是否正确和。

I am creating a function that reads column titles from two excel files and then place those column titles in checkboxes so the user can check which columns he will work with. It works when I do it with one file but when I modify it to work with two files I get the "run time error 9: Subscript out of range" and highlights the line => Set wks2 = ActiveWorkbook.Worksheets(SheetName2).

Even with this error message still works for the first file but it does not work with the second file. Can anybody help me to find the reason of this error message? Thank you very much in advance.

 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

解决方案

Subscript out of Range error arises in these circumstances when the specified sheetname does not exist in that workbooks Worksheets collection.

I notice you have two open workbooks specified by:

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

However, both of your worksheet assignments refer only to the ActiveWorkbook.

The cause of the error is certainly that SheetName2 does not exist in the ActiveWorkbok (which is specified by JFilename)

Especially when working with multiple books or worksheets, it is always preferable to avoid using Activate/Select methods-- otherwise you need to keep track of which workbook/worksheet/etc. is "Active", and that makes for spaghetti code and lots of unnecessary calls to the .Activate method.

I know that SheetName1 exists in JFilename, and I am assuming that SheetName2 exist in the workbook SFileName.

Instead, define two Workbook variables:

Dim wb1 as Workbook
Dim wb2 as Workbook

Assign the results of the Workbooks.Open method to these workbooks:

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

Now, wb1 is the "Active" workbook, so with the worksheet assignments:

Set wks1 = wb1.Worksheets(SheetName1)

And later for the Sheetname2:

Set wks2 = wb2.Worksheets(Sheetname2)

Otherwise, there is a typo in your worksheet names or the string parameters you're sending to this function. Doublecheck/debug that the value of SheetName2 is correct and that it exists.

这篇关于下标超出范围运行时错误9的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！