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

本文翻译自：How to avoid using Select in Excel VBA

I've heard much about the understandable abhorrence of using .Select in Excel VBA, but am unsure of how to avoid using it. 我已经听说了很多有关在Excel VBA中使用.Select的可理解的.Select ，但是不确定如何避免使用它。 I am finding that my code would be more re-usable if I were able to use variables instead of Select functions. 我发现如果我能够使用变量而不是Select函数，那么我的代码将更可重用。 However, I am not sure how to refer to things (like the ActiveCell etc.) if not using Select . 但是，如果不使用Select ，我不确定如何引用事物（例如ActiveCell等）。

I have found this article on ranges and this example on the benefits of not using select but can't find anything on how ? 我在范围上找到了这篇文章，在 这个示例中找到了不使用select的好处，但是找不到如何做的事情？

---

#1楼

参考：https://stackoom.com/question/ixGV/如何避免在Excel-VBA中使用选择

---

#2楼

Please note that in the following I'm comparing the Select approach (the one that the OP wants to avoid), with the Range approach (and this is the answer to the question). 请注意，下面我将比较Select方法（OP希望避免的一种方法）和Range方法（这是问题的答案）。 So don't stop reading when you see the first Select. 因此，当您看到第一个“选择”时不要停止阅读。

It really depends on what you are trying to do. 这实际上取决于您要执行的操作。 Anyway a simple example could be useful. 无论如何，一个简单的例子可能会有用。 Let's suppose that you want to set the value of the active cell to "foo". 假设您要将活动单元格的值设置为“ foo”。 Using ActiveCell you would write something like this: 使用ActiveCell可以编写如下代码：

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

If you want to use it for a cell that is not the active one, for instance for "B2", you should select it first, like this: 如果要将其用于非活动单元格，例如“ B2”，则应首先选择它，如下所示：

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

Using Ranges you can write a more generic macro that can be used to set the value of any cell you want to whatever you want: 使用范围，您可以编写一个更通用的宏，该宏可用于将所需的任何单元格的值设置为所需的任何值：

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

Then you can rewrite Macro2 as: 然后，您可以将Macro2重写为：

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

And Macro1 as: 和Macro1为：

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

Hope this helps to clear things up a little bit. 希望这有助于清除一些问题。

---

#3楼

Some examples of how to avoid select 如何避免选择的一些例子

Use Dim 'd variables 使用Dim变量

Dim rng as Range

Set the variable to the required range. Set变量设置为所需范围。 There are many ways to refer to a single-cell range 有很多方法可以引用单细胞范围

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

or a multi-cell range 或多单元格范围

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)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code. 您可以使用Evaluate方法的快捷方式，但这效率较低，通常应在生产代码中避免。

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

All the above examples refer to cells on the active sheet . 以上所有示例均涉及活动工作表上的单元格。 Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too 除非您特别想只使用活动工作表，否则最好也将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

If you do want to work with the ActiveSheet , for clarity it's best to be explicit. 如果您确实想使用ActiveSheet ，为清楚起见，最好是明确的。 But take care, as some Worksheet methods change the active sheet. 但是要小心，因为某些Worksheet方法会更改活动工作表。

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook . 同样，这是指活动工作簿 。 Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook , it is better to Dim a Workbook variable too. 除非您特别想只使用ActiveWorkbook或ThisWorkbook ，否则也最好对Workbook变量进行调暗。

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

If you do want to work with the ActiveWorkbook , for clarity it's best to be explicit. 如果您确实想使用ActiveWorkbook ，为清楚起见，最好是明确的。 But take care, as many WorkBook methods change the active book. 但是请注意，因为许多WorkBook方法都会更改活动书本。

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

You can also use the ThisWorkbook object to refer to the book containing the running code. 您还可以使用ThisWorkbook对象来引用包含运行代码的书。

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

A common (bad) piece of code is to open a book, get some data then close again 常见的（不好的）代码是打开一本书，获取一些数据然后再次关闭

This is bad: 这不好：

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

And would be better like: 最好是这样的：

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

Pass ranges to your Sub s and Function s as Range variables 将范围作为范围变量传递给Sub和Function

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

You should also apply Methods (such as Find and Copy ) to variables 您还应该将方法（例如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

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that 如果要遍历某个单元格的范围，通常最好（更快）先将范围值复制到一个变量数组，然后遍历该单元格

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

This is a small taster for what's possible. 这是一个可能的小尝试者。

---

#4楼

Two Main reasons why .Select / .Activate / Selection / Activecell / Activesheet / Activeworkbook etc... should be avoided 为什么主要有两个原因.Select / .Activate / Selection / Activecell / Activesheet / Activeworkbook等...应避免

1. It slows down your code. 它会减慢您的代码速度。
2. It is usually the main cause of runtime errors. 它通常是运行时错误的主要原因。

How do we avoid it? 我们如何避免呢？

1) Directly work with the relevant objects 1）直接处理相关对象

Consider this code 考虑这段代码

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

This code can also be written as 该代码也可以写成

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

2) If required declare your variables. 2）如果需要，声明您的变量。 The same code above can be written as 上面的相同代码可以写成

Dim ws as worksheet

Set ws = Sheets("Sheet1")

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

---

#5楼

I'm going to give the short answer since everyone else gave the long one. 我要给出简短的答案，因为其他所有人都给出了很长的答案。

You'll get .select and .activate whenever you record macros and reuse them. 每当记录宏并重用它们时，您都会获得.select和.activate。 When you .select a cell or sheet it just makes it active. 当您选择一个单元格或工作表时，只会使其处于活动状态。 From that point on whenever you use unqualified references like Range.Value they just use the active cell and sheet. 从那时起，每当您使用不合格的引用（如Range.Value它们仅使用活动单元格和工作表。 This can also be problematic if you don't watch where your code is placed or a user clicks on the workbook. 如果您不注意代码的放置位置或用户单击工作簿，这也会造成问题。

So, you can eliminate these issues by directly referencing your cells. 因此，您可以通过直接引用单元格来消除这些问题。 Which goes: 哪去了：

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

Or you could 或者你可以

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

There are various combinations of these methods, but that would be the general idea expressed as shortly as possible for impatient people like me. 这些方法有各种各样的组合，但是对于像我这样不耐烦的人，这将是尽可能短的表达的总体思路。

---

#6楼

One small point of emphasis I'll add to all the excellent answers given above: 我将在上面给出的所有出色答案中添加一点重点：

Probably the biggest thing you can do to avoid using Select is to as much as possible, use named ranges (combined with meaningful variable names) in your VBA code . 要避免使用Select，最大的可能就是尽可能在VBA代码中使用命名范围（结合有意义的变量名称） 。 This point was mentioned above, but glossed over a bit; 上面已经提到了这一点，但略有掩饰。 however, it deserves special attention. 但是，它值得特别注意。

Here are a couple additional reasons to make liberal use of named ranges though I am sure I could think of more. 尽管我确信我可以想到更多的原因，但还有另外两个原因可以自由使用命名范围。

Named ranges make your code easier to read and understand. 命名范围使您的代码更易于阅读和理解。

Example: 例：

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

It is pretty obvious what the named ranges Months and MonthlySales contain, and what the procedure is doing. 很明显， Months和MonthlySales的命名范围包含什么，以及该过程在做什么。

Why is this important? 为什么这很重要？ Partially because it is easier for other people to understand it, but even if you are the only person who will ever see or use your code, you should still use named ranges and good variable names because YOU WILL FORGET what you meant to do with it a year later, and you will waste 30 minutes just figuring out what your code is doing. 部分原因是因为它使其他人更容易理解它，但是即使您是唯一会看到或使用您的代码的人，您仍应使用命名范围和良好的变量名，因为您将忘记使用它的意图一年后， 您将花费 30分钟来弄清楚您的代码在做什么。

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes. 命名范围可确保您的宏在电子表格配置更改时（如果没有！）不会中断。

Consider, if the above example had been written like this: 考虑一下，如果上面的示例是这样写的：

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

This code will work just fine at first - that is until you or a future user decides "gee wiz, I think I'm going to add a new column with the year in Column A !", or put an expenses column between the months and sales columns, or add a header to each column. 这段代码起初会很好用-直到您或将来的用户决定“老兄，我想我要在A列中添加年份的新列！”，或在月份之间插入费用列和销售列，或为每个列添加标题。 Now, your code is broken. 现在，您的代码已损坏。 And because you used terrible variable names, it will take you a lot more time to figure out how to fix it than it should take. 而且由于使用了可怕的变量名，因此花了更多的时间弄清楚如何解决它，而不是应该花费的时间。

If you had used named ranges to begin with, the Months and Sales columns could be moved around all you like, and your code will continue working just fine. 如果您使用命名范围作为开头，则可以根据需要随意移动“ Months和“ Sales列，并且代码将继续正常运行。