VBA 脚本中的运行时错误 9

Run-time error 9 in VBA script

提问by pavel

So basically I probably have a lame question. I've used VBA for about two hours now and I'm just trying to make a macro that would would create a new speadsheet and copy data from existing one.

所以基本上我可能有一个蹩脚的问题。我现在已经使用 VBA 大约两个小时了，我只是想制作一个宏来创建一个新的电子表格并从现有的电子表格复制数据。

I've dealt with compilation errors there were, but now when I try to compile the project I get a "Run-time Error 9: Subscript out of range". In the code below the error comes up in the line where Name variable gets assigned its value.

我已经处理过编译错误，但是现在当我尝试编译项目时，出现“运行时错误 9：下标超出范围”。在下面的代码中，错误出现在 Name 变量被赋值的那一行。

I've had a look at other threads with the same issue but with my limited knowledge of VBA I couldn't figure out what is wrong or whether this code would work at all. Any help will be greatly appreciated! Thanks everyone!

我已经查看了其他具有相同问题的线程，但由于我对 VBA 的了解有限，我无法弄清楚出了什么问题，或者这段代码是否能正常工作。任何帮助将不胜感激！谢谢大家！

Option Explicit

Sub PointsCopy()


'Declaring variables for correct naming

    Dim Pit As String
    Dim RL As Integer
    Dim Pattern As Integer
    Dim Name As String
    Dim DataBook As String
    Dim DataSheet As String
    Dim oBook As Workbook
    Dim oSheet As Worksheet
    Dim NewBook As Workbook
    Dim NewSheet As Worksheet
    Dim Rows As Integer
    Dim Pts As String



'Figuring out active workbook and worksheet

    Set oBook = ActiveWorkbook
    Set oSheet = ActiveSheet

    DataBook = ThisWorkbook.Name
    DataSheet = ActiveSheet.Name


'Getting pit, RL and pattern name from cell A2 and assigning to variable

Error 9 comes in this line Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text

错误 9 出现在这一行 Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text

    Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text
    Pit = Mid(Name, 4, 2)
    RL = Mid(Name, 7, 4)
    Pattern = Right(Name, 4)
    Pts = "" & Pit & "_" & RL & "_" & Pattern & "_pts.csv"


'Adding new workbook with a proper name

Set NewBook = Workbooks.Add
    With NewBook
    .SaveAs Filename:="" & Pts & ""

    Set NewSheet = Workbooks(NewBook).Sheets("Sheet1")

'Activating new worksheet
NewSheet.Activate

'Adding column names to the new workbook

    Range("A1").Value = "MQ2_PIT_CODE"
    Range("B1").Value = "BLOCK_TOE"
    Range("C1").Value = "PATTERN_NUMBER"
    Range("D1").Value = "BLOCK_NAME"
    Range("E1").Value = "EASTING"
    Range("F1").Value = "NORTHING"
    Range("G1").Value = "RL"
    Range("H1").Value = "POINT_NO"

'Activate original data sheet

    Workbooks(oBook).Sheets(oSheet).Activate

'Count number of data rows in the original spreadsheet

    Rows = Application.Count(Range("A2:A"))

'Activate the new spreadsheet and enter pit code, block toe and pattern number

    NewSheet.Activate
    Range("A2:A" & Rows) = "" & Pit & ""
    Range("B2:B" & Rows) = "" & RL & ""
    Range("C2:C" & Rows) = "" & Pattern & ""

'Copying data for easting, northing, rl and point number from original spreadsheet to the new one

    Workbooks(oBook).Sheets(oSheet).Activate
    Range("C2:C" & Rows).Select
    Selection.Copy

    NewSheet.Activate
    Range("D2").PasteSpecial Paste:=xlPasteValues

    Workbooks(oBook).Sheets(oSheet).Activate
    Range("E2:E" & Rows).Select
    Selection.Copy

    NewSheet.Activate
    Range("H2").PasteSpecial Paste:=xlPasteValues

    Workbooks(oBook).Sheets(oSheet).Activate
    Range("G2:I" & Rows).Select
    Selection.Copy

    NewSheet.Activate
    Range("E2").PasteSpecial Paste:=xlPasteValues

    Workbooks(NewBook).Sheets(NewSheet).Save

End With

End Sub

UPDATE

更新

I've figured out why I was having this error - I was referring to a workbook and worksheet with String type of variable, so I've changed the erroring line the following way:

我已经弄清楚为什么会出现此错误 - 我指的是具有 String 类型变量的工作簿和工作表，因此我已按以下方式更改了错误行：

Name = ActiveSheet.Range("A2").Text

No I don't get Error 9but I'm getting Error 13: Type mismatchif the following line:

不，我没有收到错误 9，但我收到错误 13：如果出现以下行，则类型不匹配：

Set NewSheet = Workbooks(NewBook).Sheets("Sheet1")

Any clues on what is wrong here? Thanks again!

关于这里出了什么问题的任何线索？再次感谢！

回答by David Zemens

NewBookis alreayd a workbook objectso the correct way to do this assignment is like Set NewSheet = NewBook.Sheets("Sheet1")

NewBook已经是一个工作簿对象，所以做这个分配的正确方法就像Set NewSheet = NewBook.Sheets("Sheet1")

The construct Workbooks(_something_)takes a string argument, as the nameof a workbook. What you're passing instead of a string is a workbook object, which will raise an error since it is not the expected data type.

该构造Workbooks(_something_)采用字符串参数作为工作簿的名称。您传递的是工作簿对象而不是字符串，它会引发错误，因为它不是预期的数据类型。

For grins, you could do:

对于咧嘴笑，你可以这样做：

... = Workbooks(NewBook.Name).Sheets("Sheet1")

... = Workbooks(NewBook.Name).Sheets("Sheet1")

But that is obviously redundant, and kind of defeats the purpose of using object variables in your code.

但这显然是多余的，并且有点违背了在代码中使用对象变量的目的。

NOTENameis also a semi-reserved word, since it's a type of object in Excel. While this may not raise any errors for you, it might be confusing, and personally I try to avoid using variable names that are identical to, or very similar to objects.

NOTEName也是一个半保留词，因为它是 Excel 中的一种对象。虽然这可能不会给您带来任何错误，但它可能会令人困惑，而且我个人尽量避免使用与对象相同或非常相似的变量名。

回答by Alan K

In response to the comment that you put under David's answer...

为了回应您在大卫的回答下发表的评论......

This:

这个：

DataBook = ThisWorkbook.Name
DataSheet = ActiveSheet.Name

is potentially dangerous when combined with this:

与此结合使用时具有潜在危险：

Name = Workbooks(DataBook).Sheets(DataSheet).Range("A2").Text

Reason? Think about it for a sec. It should work fine IF the workbook that the code is embedded in is ALSO the current workbook. However if any OTHER workbook is active at the time that the code is executed then the variable DataSheet will pick up the name of a sheet in THAT workbook, not one in the "ThisWorkbook" object. Consequently you could be asking VBA to find a sheet name that certainly does exist in the ACTIVE workbook at the time, but may not exist in "ThisWorkbook"; that is, the workbook that the code is running from.

原因？考虑一下。如果嵌入代码的工作簿也是当前工作簿，它应该可以正常工作。但是，如果任何 OTHER 工作簿在执行代码时处于活动状态，则变量 DataSheet 将在该工作簿中选取工作表的名称，而不是“ThisWorkbook”对象中的工作表名称。因此，您可能会要求 VBA 查找当时确实存在于 ACTIVE 工作簿中但可能不存在于“ThisWorkbook”中的工作表名称；也就是说，运行代码的工作簿。

That will in turn give you a subscript out of range error or, to put it another way, "you're looking for a sheet in that does not exist in this workbook". (And again I emphasise, "ThisWorkbook" is the book that the code runs from, NOT (necessarily) the active workbook at the time.)

这反过来会给你一个下标超出范围的错误，或者换句话说，“你正在寻找一个工作簿中不存在的工作表”。（我再次强调，“ThisWorkbook”是运行代码的书，而不是（必须）当时的活动工作簿。）

Either change the DataBook reference to the Active workbook, or activate ThisWorkbook (depending on what your intention is) to make sure that the sheet is in the same workbook that you're referring to.

更改对活动工作簿的 DataBook 引用，或激活 ThisWorkbook（取决于您的意图）以确保该工作表与您所引用的工作簿位于同一工作簿中。

回答by pavel

Thanks for the help! Everything has solved itself when I used different approach in referring to workbooks. It looks strange but it seems to work fine:

谢谢您的帮助！当我使用不同的方法来引用工作簿时，一切都已经解决了。看起来很奇怪，但似乎工作正常：

Set wsNewSheet = Workbooks.Open("C:\Minestar_exports\" & Pts & "")
Set wsO_Sheet = Workbooks.Open("" & OldBookName & "")

I figured that I didn't have to refer to a specific worksheet if I'm only interested in the default one and it works great now!

我想如果我只对默认的工作表感兴趣，我就不必参考特定的工作表，而且它现在很好用！