VBA下标超出范围 - 错误9 [英] VBA Subscript out of range - error 9

问题描述

有人可以帮助我这个代码，我得到一个下标超出范围错误：

创建表格后的行以黄色突出显示在调试器中

 '验证年
如果TextBox_Year.Value =格式（TextBox_Year.Value，0000）然后
 
'创建流程
'创建新工作表
 Workbooks.Add 
 ActiveWorkbook.SaveAs FileName：= _ 
&工作簿（Temperature Charts Sheet Creator）。表格（MENU）。单元格（4,12）.Value& 数据表& ComboBox_Month.Value& & TextBox_Year.Value& .xls，FileFormat _ 
：= xlNormal，Password：=，WriteResPassword：=，ReadOnlyRecommended：= _ 
 False，CreateBackup：= False 
 
'创建单据
 Windows（Data Sheet  - & ComboBox_Month.Value&& TextBox_Year.Value&.xls）。激活
 
表格（Sheet3 ）。选择
表格（Sheet3）。Name =31& ComboBox_Month.Value 
表格（Sheet2）。选择
表格（Sheet2）。Name =30& ComboBox_Month.Value 
表格（Sheet1）。选择
表格（Sheet1）。Name =29& ComboBox_Month.Value 
 
对于i = 28至1步骤-1 
 
 Sheets.Add 
 ActiveSheet.Name = i& & ComboBox_Month.Value 
 
下一个
  

解决方案

建议以下简化：从 Workbooks.Add 中获取返回值，而不是下面的$ code> Windows（），如下所示：

 设置wkb = Workbooks.Add 
 wkb.SaveAs ... 
 
 wkb。激活而不是Windows（表达式）。激活
  

哲学建议：

避免使用Excel的内置函数：ActiveWorkbook，ActiveSheet和Selection：捕获返回值，并改为使用合格的表达式。

仅在最外面的宏（subs）中使用内置函数并在宏开始时捕获，例如

 设置wkb = ActiveWorkbook 
设置wks = ActiveSheet 
设置sel =选择
  

宏和内部宏不依赖这些内置名称，而是捕获返回值，例如

 设置wkb = Workbooks.Add而不是Workbooks.Add没有返回值捕获
 wkb.Activate'而不是Activeworkbook.Activate 
  

此外，尝试使用合格的表达式，例如

  wkb.Sheets（Sheet3）。Name =foo而不是Sheets（Sheet3）。Name =foo
  

或

 设置newWks = wkb.Sheets.Add 
 newWks.Name =bar'而不是ActiveSheet.Name =bar
  

使用合格表达式，例如

  newWks.Name =bar'而不是xyz.Select其次是Selection.Name =bar
  

这些方法一般会更好一些，减少混乱的结果，在重构时会更加强大（例如，在方法内部和之间移动代码行），并且可以在Excel版本之间更好地工作。例如，选择在宏执行期间会从一个版本的Excel更改为另一个版本。

另请注意，您可能会发现您不会需要。激活几乎一样多使用更多合格的表达式。 （这可能意味着用户的屏幕会闪烁较少）。因此，整个行 Windows（表达式）.Activate 可以简单地被消除，而不是被 wkb.Activate 。

（另请注意：我认为您显示的.Select语句没有贡献，可以省略。）

（我认为Excel的宏记录器负责使用ActiveSheet，ActiveWorkbook，Selection和Select选择这种更加脆弱的编程风格;这个风格有很大的改进空间。）

Can somebody help me with this code, I am getting a subscript out of range error:

The line after the 'creating the sheets is highlighted in yellow in debugger

'Validation of year
 If TextBox_Year.Value = Format(TextBox_Year.Value, "0000") Then

 'Creating Process
'Creation of new sheet
Workbooks.Add
ActiveWorkbook.SaveAs FileName:= _
    "" & Workbooks("Temperature Charts Sheet Creator").Sheets("MENU").Cells(4, 12).Value & "Data Sheet - " & ComboBox_Month.Value & " " & TextBox_Year.Value & ".xls", FileFormat _
    :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    False, CreateBackup:=False

'Creating of the sheets
Windows("Data Sheet - " & ComboBox_Month.Value & " " & TextBox_Year.Value & ".xls").Activate

    Sheets("Sheet3").Select
    Sheets("Sheet3").Name = "31 " & ComboBox_Month.Value
    Sheets("Sheet2").Select
    Sheets("Sheet2").Name = "30 " & ComboBox_Month.Value
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "29 " & ComboBox_Month.Value

For i = 28 To 1 Step -1

    Sheets.Add
    ActiveSheet.Name = i & " " & ComboBox_Month.Value

Next

解决方案

Suggest the following simplification: capture return value from Workbooks.Add instead of subscripting Windows() afterward, as follows:

Set wkb = Workbooks.Add
wkb.SaveAs ...

wkb.Activate ' instead of Windows(expression).Activate

General Philosophy Advice:

Avoid use Excel's built-ins: ActiveWorkbook, ActiveSheet, and Selection: capture return values, and, favor qualified expressions instead.

Use the built-ins only once and only in outermost macros(subs) and capture at macro start, e.g.

Set wkb = ActiveWorkbook
Set wks = ActiveSheet
Set sel = Selection

During and within macros do not rely on these built-in names, instead capture return values, e.g.

Set wkb = Workbooks.Add 'instead of Workbooks.Add without return value capture
wkb.Activate 'instead of Activeworkbook.Activate

Also, try to use qualified expressions, e.g.

wkb.Sheets("Sheet3").Name = "foo" ' instead of Sheets("Sheet3").Name = "foo"

or

Set newWks = wkb.Sheets.Add
newWks.Name = "bar" 'instead of ActiveSheet.Name = "bar"

Use qualified expressions, e.g.

newWks.Name = "bar" 'instead of `xyz.Select` followed by Selection.Name = "bar" 

These methods will work better in general, give less confusing results, will be more robust when refactoring (e.g. moving lines of code around within and between methods) and, will work better across versions of Excel. Selection, for example, changes differently during macro execution from one version of Excel to another.

Also please note that you'll likely find that you don't need to .Activate nearly as much when using more qualified expressions. (This can mean the for the user the screen will flicker less.) Thus the whole line Windows(expression).Activate could simply be eliminated instead of even being replaced by wkb.Activate.

(Also note: I think the .Select statements you show are not contributing and can be omitted.)

(I think that Excel's macro recorder is responsible for promoting this more fragile style of programming using ActiveSheet, ActiveWorkbook, Selection, and Select so much; this style leaves a lot of room for improvement.)

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