如何避免在Excel VBA中使用Select

Question

我听说过很多关于在Excel VBA中使用.select的憎恶是可以理解的，但我不确定如何避免使用它。我发现，如果我能够使用变量而不是select函数，那么我的代码将更加可重用。但是，如果不使用select，我不确定如何引用东西（如ActiveCell等）。

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

Answer 1

在前面给出的所有出色答案的基础上，我要强调一点：

避免使用Select的最大方法可能是尽可能地在VBA代码中使用命名范围（结合有意义的变量名）。这一点上面提到过，但稍稍被遮遮掩掩了一下；但是，值得特别关注。

这里有几个额外的理由来自由地使用命名范围，尽管我确信我可以想出更多的理由。

示例：

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
' E.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
' E.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

非常明显的是，命名范围months和monthlysales包含什么内容，以及该过程正在执行什么操作。

为什么这很重要？部分原因是其他人更容易理解它，但即使您是唯一一个会看到或使用您的代码的人，您仍然应该使用命名范围和好的变量名，因为一年后您会忘记您想要用它做什么，并且您将浪费30分钟来弄清楚您的代码在做什么。

考虑一下，如果上面的例子是这样写的：

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1
    Debug.Print rng2(rng3.Row)
Next rng3

这段代码一开始会很好--直到您或未来的用户决定“哎呀，我想我要在列a中添加一个新列，其中包含年份！”，或者在月份和销售列之间添加一个支出列，或者为每一列添加一个标题。现在，你的代码坏了。而且因为你使用了糟糕的变量名，所以你要花更多的时间来找出如何修复它。

如果您一开始就使用了命名区域，那么months和sales列可以随意移动，您的代码将继续正常工作。

Answer 2

应避免使用.select、.activate、select、ActiveCell、ActiveSheet、ActiveWorkbook等的两个主要原因

1. 它会降低代码的速度。
2. 它通常是导致运行时错误的主要原因。

我们如何避免呢？

1）直接处理相关对象

请考虑以下代码

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

这段代码也可以写成

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2）如果需要，声明变量。上面的代码可以写成

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

Answer 3

使用dimd变量

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]

以上所有示例都是指活动工作表上的单元格。除非您特别想只处理活动工作表，否则最好也将worksheet变量调暗：

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，为了清晰起见，最好是显式的。但是要小心，因为某些worksheet方法会更改活动工作表。

Set rng = ActiveSheet.Range("A1")

同样，这指的是活动的工作簿。除非您特别希望仅使用activeworkbook或thisworkbook，否则最好也将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

将范围作为范围变量传递给子%s和函数%s：

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

这是一个小品尝什么是可能的。