logo标签列表

如何避免在Excel VBA中使用Select

excelvba
638

我听说使用.Select在Excel VBA中会被人们所反感，但我不知道该如何避免使用它。如果能够使用变量而不是Select函数，那么我的代码将更加可重用。然而，如果不使用Select，我不确定如何引用诸如ActiveCell等内容。

我找到了这篇关于范围的文章和这个避免使用select的例子，但我找不到任何有关“如何”的信息。

23
需要注意的是，在某些情况下，完全无法避免使用 Select 和/或 ActiveSheet 等等。这里有一个我找到的例子：https://dev59.com/6mEh5IYBdhLWcg3wHAND - Rick
18
有时候会需要进行编辑幻灯片中基于 Excel 文件的图表数据，这时可能需要使用“激活”或“选择”的功能。 - brettdj
@brettdj - 这里有一个最近的例子。要将工作簿中所有工作表设置为相同的值，似乎需要使用.Select / .Selection。 - BruceWayne
4
似乎不是这样。 - chris neilsen
15个回答
666

一些避免使用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]

以上所有示例都是针对活动工作表上的单元格。除非你特别只想使用活动工作表，否则最好也声明一个 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

将范围传递给你的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

你还应该将方法（如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

这只是一个小小的展示，展示了可能性。

12
在补充这个出色的答案时，需要说明的是，在处理一个范围时，你不需要知道它的实际大小，只要知道左上角的位置就可以了。例如，即使rng1只设置为[A1:A10]，rng1(12, 12)也能够使用。 - MikeD
4
@chrisneilsen ，我相信你也可以在缩写的单元格引用符号前使用工作表前缀，以免像这样输入Range：ActiveSheet.[a1:a4]或ws.[b6]。 - Logan Reed
7
在这篇文章中，@AndrewWillems已经提到了“48次”，但是谁在数呢。😊 但说真的，当使用保存对象的变量时，很容易忘记Set关键字。一个 variant 变量在你给它赋值之前不需要 Set 关键字。例如，Dim x: x = 1 是可以的，但是 Dim x: x = Sheets("Sheet1") 会生成错误 438。然而，为了混淆/说明，Dim x: x = Range("A1") 不会产生错误。为什么？因为它将对象的值赋给了变量，而不是对象本身的引用（因为它相当于 Dim x: x = Range("A1").Value）。 - ashleedawg
3
@user3932000，这会是一个有趣的问题。我相信有处理它的方法...你在SO上已经待了很长时间，知道劫持旧问题的评论线不是正确的方式。 - chris neilsen
3
一个小建议：我发现给变量命名时不要用"wb1"和"wb2"这样的名称，而是使用"wbTarget"和"wbSource"之类的名称，特别是在复制过程中。这有助于跟踪哪个参数放在哪里。 - LWChris
显示剩余7条评论
252

避免使用.Select、.Activate、Selection、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

这是一个很好的答案，但我在这个主题上缺少的是我们实际需要使用Activate的情况。每个人都说它不好，但没有人解释任何使用它有意义的情况。

以下是一些无法避免使用.Activate/.Select的情况。(我会在发现更多链接时添加)

  1. 当您想向用户展示工作表以供其查看时。
  2. 像Working macro returns error when run from form control这样的场景强制您使用.Activate
  3. 当常规方法的Text To Columns / .Formula = .Formula不起作用时，可能必须使用.Select
19
这是一个很好的回答，但我在这个话题上缺少的是何时实际需要“激活”（Activate）。每个人都说它不好，但没有人解释任何使用它有意义的情况。例如，我正在使用2个工作簿，并且无法在未先激活其一的情况下启动其中一个工作簿上的宏。您能否详细说明一下？另外，例如当我从一个工作表复制范围到另一个工作表时不激活工作表，当我执行程序时，似乎程序隐式地还是会激活相应的工作表。 - user3032689
7
我认为重点不是完全避免使用它们，而是尽可能减少使用。如果您想保存一个工作簿，以便当某人打开它时选择特定工作表中的特定单元格，那么您必须选择该工作表和单元格。复制/粘贴是个坏例子，至少在值的情况下，可以通过如Sheets(2).[C10:D12].Value = Sheets(1).[A1:B3].Value这样的代码更快地完成。 - robotik
3
@Nick 你不需要激活工作表就可以粘贴或筛选它们。在你的粘贴或筛选命令中使用工作表对象即可。随着你通过实践学习Excel对象模型，这将变得更加容易。我相信我只有在创建新工作表时才会使用“.Activate”，但是当代码完成时，我希望原始工作表出现。 - phrebh
3
你不需要使用.Activate来返回到原工作表，只需使用Application.Goto即可。 - GMalc
1
@user3032689 我认为Activate应该只在处理过程中使用，例如当你想让工作表对用户可见时...没有其他太多的用途... - Andras
显示剩余2条评论
109

在之前给出的所有优秀答案中，我想要强调一点：

尽可能多地在你的VBA代码中使用命名区域（结合有意义的变量名），这可能是避免使用Select语句最大的事情。虽然上面提到了这一点，但它被略过了一点，不过它值得特别关注。

以下是使用命名区域的几个额外原因，虽然我肯定可以想出更多。

命名区域使你的代码更易读、易懂。

例如：

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列可以自由移动，你的代码仍然可以正常工作。

7
有关命名区域是否是良好电子表格设计的争论仍在继续——我坚决持反对态度。根据我的经验，它们会增加错误发生的可能性（对于没有编程需求的普通用户而言）。 - brettdj
6
一个参考文献：命名区间是否会影响初学者的调试性能?。 - brettdj
12
我同意你的开发理念，但我认为这篇论文毫无意义。它谈论了命名范围会让初学者在调试电子表格时感到困惑，但是任何使用初学者来查看复杂电子表格的人都自作自受！我曾经在一家审核财务电子表格的公司工作过，我可以告诉你，这不是一个适合初学者的工作。 - DeanOC
9
没有有意义的辩论。反对使用已定义名称的人没有花时间充分了解它们的影响。已命名的公式可能是 Excel 中最深刻和最有用的构造之一。 - Excel Hero
12
你的引用是正确的，但你忘了提到它后面有六个“除外”的短语之一是：“在宏编码中，除了作为单元格引用的替代品之外，始终使用Excel名称作为单元格引用的替代品，以避免插入额外行或列导致宏编码不再指向预期源数据而出现错误。” - Marcus Mangelsdorf
显示剩余14条评论
55
我将给出简短的答案，因为其他人都给出了长篇大论。
每当录制宏并重复使用时，您将获得.select和.activate。当您选择一个单元格或工作表时，它只是使其处于活动状态。从那时起，无论何时使用未经限定的引用，如Range.Value，它们只使用活动单元格和工作表。如果您不注意代码的位置或用户点击工作簿，则这也可能会有问题。
所以，您可以通过直接引用单元格来消除这些问题。具体做法如下：
'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)

或者你可以

'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"

这些方法可以有各种组合，但对于像我这样的急躁的人来说，这就是简短地表达这个基本思想。

40
"...并且我发现如果能够使用变量而不是选择函数，我的代码将更具可重用性。"
虽然我想不出除了个别情况外，.Select比直接引用单元格更好的选择，但我会为Selection辩护，并指出它不应该因与.Select相同的原因而被淘汰。
有时候，将短小的、省时的宏子程序分配给热键组合，只需轻按几个键即可节省大量时间。当处理不符合工作表全局数据格式的数据时，能够选择一组单元格来执行操作代码非常有效。就像您可能选择一组单元格并应用格式更改一样，选择一组单元格来运行特殊的宏代码可以节省大量时间。
基于选择的子框架示例：
Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

实际处理的代码可以是从单行到多个模块的任何内容。我使用这种方法来启动长时间运行的例程，对包含外部工作簿文件名的不规则选择进行操作。

简而言之，不要因为它与 .Select 和 ActiveCell 密切相关就丢弃 Selection。作为工作表属性，它有许多其他用途。

（是的，我知道这个问题是关于 .Select 而不是 Selection，但我想消除初学者 VBA 程序员可能会推断出的任何误解。）

14
“Selection” 可以是工作表中的任何内容，因此最好在将其分配给变量之前先测试对象的类型，因为您已明确声明它为“Range”。 - L42
1
我进一步开发了一个 Excel 插件，它可以在用户选择的单元格上工作，并添加了一个按钮到右键菜单中，当用户选择单元格时，该按钮会出现。我的用户非常喜欢它。对他们来说，我的宏就像是 Excel 的内置功能一样，他们并不知道有什么不同。 - Geoff Griswald
39
避免使用 Select 和 Activate 可以使您成为更好的 VBA 开发人员。通常情况下，当宏被录制时，Select 和 Activate 被使用，因此父工作表或范围始终被视为活动对象。
以下是您可以避免使用 Select 和 Activate 的方法：

添加新工作表并将单元格复制到其中：

源代码（由宏录制器生成）：

Sub Makro2()
    Range("B2").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

收件人：

Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

当您想要在工作表之间复制范围时：

来自：

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste

收件人：

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

使用漂亮的命名范围

您可以使用[]访问它们，与其他方式相比，这真的很美丽。自己检查一下：

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

上面的例子将看起来像这样：
Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

不是复制值，而是取值

通常情况下，如果你想要选择（select）某物，很可能你正在复制它。如果你只对值感兴趣，那么这是一个避免使用选择的好选择：

Range("B1:B6").Value = Range("A1:A6").Value

始终引用工作表

这可能是中最常见的错误。每当您复制范围时，有时会没有引用工作表，因此VBA会将错误的工作表视为活动工作表。

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

我真的永远不能使用.Select或.Activate吗？

  • 一个很好的例子是当你希望出于视觉原因确保选择了特定的工作表时，你可以正当地使用.Activate和.Select。例如，无论在关闭文件时哪个工作表是活动工作表，你的Excel总是会首先选择封面工作表。

因此，像下面的代码是完全可以接受的：

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub
1
你可以使用Application.Goto代替Worksheets.Activate。这样风险会稍微小一些。 - Geoff Griswald
1
晚些回复FYI - 一个需要.Select的不错且有些出乎意料的例子 - 以及我的解决方法 - 可以在如何将相同信息写入所有工作表中找到 - @Vityata :) - T.M.
1
@T.M. - 的确是一个有趣的例子，对于100多个工作表可能可以节省一些毫秒，但如果我在某个地方看到它，我可能会劝阻使用。无论如何，选择区域并没有明确写出，而是由.FillAcrossSheets的结果决定的，因此这在VBA分类中处于某种中间状态（至少在我对VBA分类的理解中是这样）。 - Vityata
36
请注意，接下来我将比较Select方法（这是OP想要避免的方法）和Range方法（这是问题的答案）。因此，请在看到第一个Select时不要停止阅读。
这实际上取决于你想做什么。无论如何，一个简单的例子可能会有用。假设你想将活动单元格的值设置为“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

并且宏1如下：

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub
1
谢谢您如此迅速地给出了这么好的答复。那么这是否意味着，如果我通常会向范围添加单元格、命名范围并遍历它，我应该直接创建数组？ - BiGXERO
我不确定我理解你的意思，但是你可以使用单个指令创建一个范围（例如Range("B5:C14")），并且你甚至可以一次性设置它的值（如果每个单元格的值都相同），例如Range("B5:C14").Value = "abc"。 - Francesco Baruchelli
20

始终指明工作簿、工作表和单元格/范围。

例如：

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)

因为最终用户总是只会单击按钮，一旦焦点移开工作簿，代码想要处理的事情就会完全出错。

而且永远不要使用工作簿的索引。

Workbooks(1).Worksheets("fred").cells(1,1)

当用户运行您的代码时，您不知道会打开哪些其他工作簿。

7
工作表的名称也可能会更改，你知道吗？使用代号代替原名称。 - Rick
工作表名称可能会更改，但我不同意您应该过度复杂化代码来尝试减轻这种情况。如果用户更改了工作表的名称并且他们的宏停止工作，那就是他们的问题。我通常假设工作表名称将保持不变。对于特别关键的宏，我会在启动实际宏之前运行一个小型预检查，仅检查以确保所有预期找到的工作表都存在，并且如果有任何缺失，则通知用户哪个工作表缺失。 - Geoff Griswald
18
这些方法有点被玷污，所以我们要以 Vityata 和 Jeeped 为例来划清界限：
调用 .Activate、.Select、Selection、ActiveSomething 方法/属性
主要是因为它们主要被调用来通过应用程序 UI 处理用户输入。由于它们是在用户通过 UI 处理对象时调用的方法，它们是宏记录器记录下来的方法，这就是为什么对大多数情况来说调用它们要么脆弱要么多余的原因：你不必选择一个对象，然后立即使用 Selection 执行动作。
然而，这个定义解决了它们被调用的情况：
何时调用 .Activate、.Select、.Selection、.ActiveSomething 方法/属性
基本上是当您期望最终用户在执行中扮演角色时。
如果您正在开发并希望用户选择要处理的对象实例，则适用于 .Selection 或 .ActiveObject。
另一方面，当您能够推断用户的下一步操作并且希望您的代码指导用户时，.Select和.Activate会很有用，可能会为用户节省一些时间和鼠标点击。例如，如果您的代码刚刚创建了一个全新的图表实例或更新了一个图表实例，用户可能想要查看它，您可以在其上调用.Activate或其工作表以节省用户搜索它的时间；或者如果您知道用户需要更新某些范围值，您可以通过编程方式选择该范围。
“Jeeped”指的是什么答案？目前该用户名下没有任何答案或评论（用户名可能随时更改）- 包括已删除的答案。一个备选项是user4039065的答案（现在已删除用户）。 - Peter Mortensen
11
为避免使用 `.Select` 方法，您可以将变量设置为所需的属性。例如，如果您想要单元格 A1 中的值，可以将变量设置为该单元格的值属性。
例如：valOne = Range("A1").Value 同样地，如果您想要 'Sheet3' 的代码名称，可以将变量设置为该工作表的 Codename 属性。
例如：valTwo = Sheets("Sheet3").Codename
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接