VBA下标超出范围-错误9。

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

3 个解决方案

#1

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

建议以下简化:从工作簿中获取返回值。然后，添加而不是下标Windows()，如下:

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.

避免使用Excel的内置函数:ActiveWorkbook、ActiveSheet和Selection:捕获返回值，并使用符合条件的表达式。

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

只在最外面的宏(subs)中使用内置函数，并在宏启动时捕获。

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.

这些方法在一般情况下会更有效，给出的结果不那么令人困惑，在重构时(例如，在方法内部和方法之间移动代码行)和在Excel的不同版本中工作的效果会更好。例如，从一个版本的Excel到另一个版本，选择在宏执行过程中会有不同的变化。

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.

另外，请注意，您可能会发现您不需要。在使用更符合条件的表达式时，几乎同样会激活。(这可能意味着对用户来说屏幕将会变得更少。)因此，整个行窗口(表达式)。激活可以被简单地消除，而不是被wkb.Activate取代。

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

(注:我认为您所展示的. select语句没有贡献，可以省略。)

(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.)

(我认为Excel的宏记录器负责使用ActiveSheet、ActiveWorkbook、Selection和Select等方式来促进这种更加脆弱的编程风格;这种样式留下了许多改进的余地。

#2

Subscript out of Range error occurs when you try to reference an Index for a collection that is invalid.

当您试图引用一个无效集合的索引时，就会出现范围错误。

Most likely, the index in Windows does not actually include .xls. The index for the window should be the same as the name of the workbook displayed in the title bar of Excel.

很可能，Windows中的索引实际上不包括.xls。窗口的索引应该与在Excel的标题栏中显示的工作簿的名称相同。

As a guess, I would try using this:

作为猜测，我会试着用这个:

Windows("Data Sheet - " & ComboBox_Month.Value & " " & TextBox_Year.Value).Activate

#3

Option Explicit

Private Sub CommandButton1_Click()
Dim mode As String
Dim RecordId As Integer
Dim Resultid As Integer
Dim sourcewb As Workbook
Dim targetwb As Workbook
Dim SourceRowCount As Long
Dim TargetRowCount As Long
Dim SrceFile As String
Dim TrgtFile As String
Dim TitleId As Integer
Dim TestPassCount As Integer
Dim TestFailCount As Integer
Dim myWorkbook1 As Workbook
Dim myWorkbook2 As Workbook


TitleId = 4
Resultid = 0

Dim FileName1, FileName2 As String
Dim Difference As Long



'TestPassCount = 0
'TestFailCount = 0

'Retrieve number of records in the TestData SpreadSheet
Dim TestDataRowCount As Integer
TestDataRowCount = Worksheets("TestData").UsedRange.Rows.Count

If (TestDataRowCount <= 2) Then
  MsgBox "No records to validate.Please provide test data in Test Data SpreadSheet"
Else
  For RecordId = 3 To TestDataRowCount
    RefreshResultSheet

    'Source File row count
    SrceFile = Worksheets("TestData").Range("D" & RecordId).Value
    Set sourcewb = Workbooks.Open(SrceFile)
    With sourcewb.Worksheets(1)
      SourceRowCount = .Cells(.Rows.Count, "A").End(xlUp).row
      sourcewb.Close
    End With

    'Target File row count
    TrgtFile = Worksheets("TestData").Range("E" & RecordId).Value
    Set targetwb = Workbooks.Open(TrgtFile)
    With targetwb.Worksheets(1)
      TargetRowCount = .Cells(.Rows.Count, "A").End(xlUp).row
      targetwb.Close
    End With

    ' Set Row Count Result Test data value
    TitleId = TitleId + 3
    Worksheets("Result").Range("A" & TitleId).Value = Worksheets("TestData").Range("A" & RecordId).Value

    'Compare Source and Target Row count
    Resultid = TitleId + 1
    Worksheets("Result").Range("A" & Resultid).Value = "Source and Target record Count"
    If (SourceRowCount = TargetRowCount) Then
       Worksheets("Result").Range("B" & Resultid).Value = "Passed"
       Worksheets("Result").Range("C" & Resultid).Value = "Source Row Count: " & SourceRowCount & " & " & " Target Row Count: " & TargetRowCount
       TestPassCount = TestPassCount + 1
    Else
      Worksheets("Result").Range("B" & Resultid).Value = "Failed"
      Worksheets("Result").Range("C" & Resultid).Value = "Source Row Count: " & SourceRowCount & " & " & " Target Row Count: " & TargetRowCount
      TestFailCount = TestFailCount + 1
    End If


    'For comparison of two files

    FileName1 = Worksheets("TestData").Range("D" & RecordId).Value
    FileName2 = Worksheets("TestData").Range("E" & RecordId).Value

    Set myWorkbook1 = Workbooks.Open(FileName1)
    Set myWorkbook2 = Workbooks.Open(FileName2)

    Difference = Compare2WorkSheets(myWorkbook1.Worksheets("Sheet1"), myWorkbook2.Worksheets("Sheet1"))
    myWorkbook1.Close
    myWorkbook2.Close


    'MsgBox Difference

    'Set Result of data validation in result sheet
    Resultid = Resultid + 1

    Worksheets("Result").Activate
    Worksheets("Result").Range("A" & Resultid).Value = "Data validation of source and target File"

    If Difference > 0 Then
        Worksheets("Result").Range("B" & Resultid).Value = "Failed"
        Worksheets("Result").Range("C" & Resultid).Value = Difference & " cells contains different data!"
        TestFailCount = TestFailCount + 1
    Else
      Worksheets("Result").Range("B" & Resultid).Value = "Passed"
      Worksheets("Result").Range("C" & Resultid).Value = Difference & " cells contains different data!"
      TestPassCount = TestPassCount + 1
    End If


  Next RecordId
End If

UpdateTestExecData TestPassCount, TestFailCount
End Sub

Sub RefreshResultSheet()
  Worksheets("Result").Activate
  Worksheets("Result").Range("B1:B4").Select
  Selection.ClearContents
  Worksheets("Result").Range("D1:D4").Select
  Selection.ClearContents
  Worksheets("Result").Range("B1").Value = Worksheets("Instructions").Range("D3").Value
  Worksheets("Result").Range("B2").Value = Worksheets("Instructions").Range("D4").Value
  Worksheets("Result").Range("B3").Value = Worksheets("Instructions").Range("D6").Value
  Worksheets("Result").Range("B4").Value = Worksheets("Instructions").Range("D5").Value
End Sub

Sub UpdateTestExecData(TestPassCount As Integer, TestFailCount As Integer)
  Worksheets("Result").Range("D1").Value = TestPassCount + TestFailCount
  Worksheets("Result").Range("D2").Value = TestPassCount
  Worksheets("Result").Range("D3").Value = TestFailCount
  Worksheets("Result").Range("D4").Value = ((TestPassCount / (TestPassCount + TestFailCount)))
End Sub