如何避免在Excel VBA中使用选择

我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶，但我不确定如何避免使用它。我发现，如果我能够使用变量而不是Select函数，我的代码将更具可重用性。然而，我不确定如果不使用选择如何引用事物(如ActiveCell等)。

我找到了这篇关于范围的文章和这个关于不使用select的好处的例子，但我找不到任何关于如何使用的东西。

当前回答

一些如何避免选择的例子

使用Dim变量

Dim rng as Range

将变量设置为所需的范围。有很多种方法来指代单格范围:

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

或者一个多单元格范围:

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

您可以使用Evaluate方法的快捷方式，但这效率较低，通常应该在生产代码中避免使用。

Set rng = [A1]
Set rng = [A1:B10]

以上所有示例都引用活动工作表上的单元格。除非你特别想只使用活动表，否则最好将工作表变量也调暗:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

如果你确实想要使用ActiveSheet，为了清晰起见，最好是显式的。但是要小心，因为有些工作表方法会更改活动表。

Set rng = ActiveSheet.Range("A1")

同样，这是指活动工作簿。除非你特别想只使用ActiveWorkbook或ThisWorkbook，否则最好也使用Dim Workbook变量。

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

如果您确实想使用ActiveWorkbook，为了清晰起见，最好是显式的。但是要注意，因为许多WorkBook方法会更改活动簿。

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

您还可以使用ThisWorkbook对象来引用包含运行代码的书籍。

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

一个常见的(糟糕的)代码片段是打开一本书，获得一些数据，然后再次关闭

这很糟糕:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

最好是这样:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

将范围传递给子函数和函数作为范围变量:

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

你还应该对变量应用方法(比如Find和Copy):

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

如果你循环遍历一个单元格范围，通常更好(更快)复制范围值到一个变量数组，然后循环遍历它:

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

这是对可能性的小小尝试。

2012-05-23 10:23:22

其他回答

在我看来，.select的使用来自于像我这样的人，他们通过记录宏然后修改代码开始学习VBA，而没有意识到.select和后续的选择只是一个不必要的中间人。

.select可以避免，就像许多人已经发布的那样，通过直接使用已经存在的对象，这允许各种间接引用，如以复杂的方式计算I和j，然后编辑cell(I,j)等。

否则，.select本身并没有什么隐含的错误，你可以很容易地找到它的用途，例如，我有一个电子表格，我用日期填充，激活宏，对它做一些魔法，并在一个单独的表格上以可接受的格式导出它，然而，这需要一些最终手动(不可预知的)输入到相邻的单元格。这时，使用.select可以节省额外的鼠标移动和点击。

2016-11-20 15:02:00

请注意，在下面我将比较Select方法(OP想要避免的方法)和Range方法(这是问题的答案)。所以当你看到第一个选择时不要停止阅读。

这真的取决于你想要做什么。总之，举个简单的例子会很有用。让我们假设您想要将活动单元格的值设置为“foo”。使用ActiveCell，你可以这样写:

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

如果你想将它用于一个非活动单元格，例如“B2”，你应该首先选择它，如下所示:

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

使用Ranges，你可以编写一个更通用的宏，可以用来设置任何你想要的单元格的值:

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

然后你可以重写Macro2为:

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

而Macro1为:

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

2012-05-23 06:11:28

如何避免复制粘贴?

让我们面对现实吧:这个在记录宏时经常出现:

Range("X1").Select
Selection.Copy
Range("Y9").Select
Selection.Paste

而这个人唯一想要的是:

Range("Y9").Value = Range("X1").Value

因此，与其在VBA宏中使用复制粘贴，我建议使用以下简单的方法:

Destination_Range.Value = Source_Range.Value

2020-10-30 15:55:25