excel - 如何修复 VBA Find 方法的 "Subscript out of range"错误？

Question

我的代码的第一部分应该查找工作表中的最后一个事件行 - 我的数据集由可能返回空白的公式组成，因此我确实想使用“查找”方法。工作表“WS1 Bid Data”存在，但当我尝试运行代码时出现“下标超出范围”错误。

我知道有很多类似的问题，但我一直无法找到有效的解决方案。有人有想法吗？

    start_row = Worksheets("Import").Range("B23").Value
    start_col = Worksheets("Import").Range("B24").Value
    start_ref = Worksheets("Import").Range("B19").Value
    With Worksheets("WS1 Bid Data")
        end_row = Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      LookAt:=x1Part, _
                      LookIn:=x1Formulas, _
                      SearchOrder:=x1ByRows, _
                      SearchDirection:=x1Previous, _
                      MatchCase:=False).Row
    End With

Answer 1

正如评论所示，请考虑 Excel VBA 中的一些最佳实践:

1. 在运行 VBA 的每个工作表或模块中使用Option Explicit。这将引发未声明变量的编译错误，因此应该捕获 Excel 常量中 1 的拼写错误(即 x1Part 与 xlPart)。

   事实上，请立即在 VBA IDE 中将此规则设置为全局选项:工具 > 选项 > 编辑器 > 选中“需要变量声明”。

2. 始终明确地将对象限定为其父级。切勿依赖激活光标或屏幕的位置来调整工作簿或工作表上下文并影响代码。并始终避免 Select or .Activate .

   具体来说，在您的 Worksheet 对象上使用 ThisWorkbook.(或通过 Set wb = ... 分配的工作簿)。

3. 在 With...End block 中充分使用句点限定符。同样，这可以避免误解代码运行的上下文，因为没有句点的 Cells 引用假定为事件工作表。这不一定与 With block 的工作表相同。

执行上述操作后，您的代码应该更加稳定和可维护:

Option Explicit

Public Sub myMacro()
    ...
    With ThisWorkbook.Worksheets("Import")                    ' USE ThisWorkbook.
       start_row = .Range("B23").Value
       start_col = .Range("B24").Value
       start_ref = .Range("B19").Value
    End With

    With ThisWorkbook.Worksheets("WS1 Bid Data")              ' USE ThisWorkbook.
        end_row = .Cells.Find(What:="*", _                    ' USE .Cells
                              After:=.Range("A1"), _
                              LookAt:=xlPart, _               ' VERIFY WITH Option Explicit
                              LookIn:=xlFormulas, _           ' SAME AS ABOVE
                              SearchOrder:=xlByRows, _        ' SAME AS ABOVE
                              SearchDirection:=xlPrevious, _  ' SAME AS ABOVE
                              MatchCase:=False).Row
    End With
    ...
End Sub