运行时'9'脚本超出范围 [英] Runtime eoor '9' Script out of range

问题描述

我必须为一个项目开发VBA脚本并重新找回一些很久以前使用过的老员工。对于这样简单的代码行：

I have to work on VBA script for a project and re-pick up some old staffs used a long time ago. For such simple lines of code:

Sheets("Sheet1").Select

或

or

Sheets("Sheet2").Cells(2, 5).Value = 100

我得到Runtime eoor'9'脚本超出范围。代码有什么问题？谢谢。

I got Runtime eoor '9' Script out of range. What's wrong in the code? Thanks.

推荐答案

确保名称为Sheet2的工作表存在。

Ensure a sheet by the name Sheet2 exists.

通过修改下面的代码修复问题，

Problem fixed by revising the code like that below,

Sub One()
    Dim shTrklon  As Worksheet
    Set shTrklon = Worksheets(2)
    shTrklon.Select
    shTrklon.Cells(2, 5).Value = 100
End Sub

引用：

（...）此错误有以下原因和解决方案：

• 您引用了不存在的数组元素。
• 您声明了一个数组但没有指定元素的数量。
• 您引用了一个不存在的集合成员。
• 您使用了一个隐式指定无效元素的缩写形式的下标。

(...)This error has the following causes and solutions:




• You referenced a nonexistent array element.
• You declared an array but didn't specify the number of elements.
• You referenced a nonexistent collection member.
• You used a shorthand form of subscript that implicitly specified an invalid element.





有关错误9的更多详细信息，请点击此处：错误9：下标超出范围 [ ^ ]





也许Solution1解决了这个问题，但是......我需要对它进行评论。





More details about Error 9 you'll find here: Error 9: subscript out of range[^]


Maybe Solution1 solves the issue, but... i need to comment it.





1. 使用Sheet vs之间存在已知的差异。工作表对象。
   
   Excel的工作表和表格集合 - 有什么区别？ [ ^ ]
2. 其次，代码没有上下文！
   
   假设有2个已打开的工作簿。激活第一个并运行以下代码：
   
   


1. There are known differences between using Sheet vs. Worksheet object.
   Excel's Worksheets and Sheets Collection - What's the Difference?[^]
2. Secondly, the code has no context!
   Let say, there are 2 opened workbooks. Activate the first one and run below code:
   

Worksheets(2).Range("A1") = "Stupid code"



现在，激活另一个并再次运行代码。

发生了什么事？ Workbook1和Workbook2已更改。要避免此行为，请定义上下文：




Now, activate another one and run the code again.
What's happend? Workbook1 and Workbook2 have been changed. To avoid this behaviour, define the context:


ThisWorkbook.Worksheets(2).Range("A1") = "Excellent code!"



无论哪个工作簿实际处于活动状态，代码都只修改一个工作簿。

• 如果您是初学程序员，强烈建议您按名称从集合中调用元素。
  
  

  
  No matter of which workbook is actually active, only one workbook is modified by code.

• If you're beginner programmer, it's strongly recommended to call element from collection by its name.
  

  Dim wsh = ThisWorkbook.Worksheets("Sheet1")

  
  
  为什么？改变woksheets的顺序！ 工作表（2）可以参考 Sheet1 ， Sheet2 ， Sheet3 等
  
  在某些特殊情况下，代码名称 [ ^ ]被使用。
  
  

• 不要使用选择方法，除非有必要！
  
  这对初学者程序员来说是不好的做法。它没有帮助！执行代码时，Excel窗口会闪烁！如果其他一些操作与 Sheet_Change 事件捆绑在一起，Excel会疯狂地从一个程序跳到另一个程序！浪费时间和资源！
• 向大师学习！
  
  Visual Basic编码约定 [ ^ ]
  
  Excel VBA性能编码最佳实践 [ ^ ]
  
  
  
  


Why? Change the order of woksheets! Worksheets(2) can refer to Sheet1, Sheet2, Sheet3, etc.
In some special cases, CodeName[^] is used.


• Do not use Select method, unless it's necessary!
  It's bad practice of beginner programmers. It does not help! When code is executed, Excel window is going blinking! If some other actions are binded with Sheet_Change event, Excel is going crazy jumping from one procedure to another! It wasting the time and resources!
• Learn from masters!
  Visual Basic Coding Conventions[^]
  Excel VBA Performance Coding Best Practices[^]
  
  




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