时间戳

首页 > 解决方案 > 如何将工作簿 - 工作表和范围作为变量引用

excel - 如何将工作簿 - 工作表和范围作为变量引用

问题描述

我一直收到运行时错误 9，“下标超出范围”。不知道该怎么做。我通过下面的最后一行代码得到错误。

Dim Sheetstarybpm As Worksheet
Dim countrowsoldbpm1 As Long
Dim rng2 As Range

With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        .Title = "Select BPM Report for previous month"
        .ButtonName = "OK"
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        oldbpm = .SelectedItems.Item(1)
End With

Workbooks.Open oldbpm

Set Sheetstarybpm = ActiveSheet

countrowsoldbpm1 = Range("a10", Range("a10").End(xlDown)).Rows.Count

Set myrange = Range("A10:CW" & countrowsoldbpm1)

Set rng2 = Workbooks(oldbpm).Worksheets(Sheetstarybpm).Range(myrange)

标签： excelvba

解决方案

您错误地使用了对象方法。存储后，Sheetstarybpm您可以进一步使用它...因此，在打开工作簿时，将其存储为工作簿对象，以便以后可以在需要时引用它。此外，在设置工作表对象时，您现在可以显式引用您的工作簿，因为您已经存储了它（更好的编码实践）。还建议在设置范围时明确定义工作表对象引用。请参阅下面的修改代码。

Dim Sheetstarybpm As Worksheet
Dim countrowsoldbpm1 As Long
Dim myWB As Workbook

With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        .Title = "Select BPM Report for previous month"
        .ButtonName = "OK"
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        oldbpm = .SelectedItems.Item(1)
End With

Set myWB = Workbooks.Open(oldbpm)

Set Sheetstarybpm = myWB.ActiveSheet

countrowsoldbpm1 = Sheetstarybpm.Range("a10", Range("a10").End(xlDown)).Rows.Count

Set myrange = Sheetstarybpm.Range("A10:CW" & countrowsoldbpm1)

编辑：从代码中删除，因为它与OP 在评论中指出rng2的相同。myrange

推荐阅读