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

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

我已经找到了这篇关于量程的文章和这个例子讲述了不使用select的好处，但我在如何上找不到任何东西。

364914 次浏览
小开

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

这真的取决于你想要做什么。总之，举个简单的例子会很有用。假设您想要将活动单元格的值设置为" food "。使用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
小开
最佳答案

一些如何避免选择的例子

使用# eyz0d变量

Dim rng as Range

Set变量到所需的范围。有很多种方法来指代单格范围:

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

将范围传递给Subs和Functions作为范围变量:

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

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

小开

.Select， .Activate， Selection， Activecell， Activesheet， Activeworkbook等应该避免使用的两个主要原因

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

我们如何避免它?

直接使用相关对象

考虑下面的代码

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

这段代码也可以写成

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

如果需要，声明你的变量。上面相同的代码可以写成

Dim ws as worksheet


Set ws = Sheets("Sheet1")


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

这是一个很好的答案，但我在这个话题上忽略了我们真正需要Activate的时候。每个人都说它不好，但没有人解释在什么情况下使用它是有意义的。

无法避免使用.Activate/.Select时的情况。(将添加更多的链接，当我遇到他们)

  1. 当您希望将工作表显示给用户以便用户可以看到它时。
  2. 像工作宏从窗体控件运行时返回错误这样的场景，您被迫使用.Activate
  3. 当Text To Columns / .Formula = .Formula中的通常的方法不起作用时，你可能不得不求助于.Select
小开

我要给出简短的答案，因为其他人都给出了长答案。

当您记录宏并重用它们时，您将得到.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"

这些方法有多种组合，但对于像我这样没有耐心的人来说，这将是尽可能简短地表达的总体思想。

小开

我将在之前给出的所有优秀答案中补充一个小重点:

为了避免使用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列就可以随意移动，并且您的代码将继续正常工作。

小开

始终声明工作簿，工作表和单元格/范围。

例如:

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

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

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

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

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

小开

“…我发现，如果我能够使用变量而不是选择函数，我的代码将更加可重用。”

虽然我想不出更多的情况，在这些情况下.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

要处理的实际代码可以是从单行到多个模块的任何内容。我曾使用此方法在包含外部工作簿文件名的不规则单元格上启动长时间运行的例程。

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

(是的，我知道这个问题是关于.Select，而不是Selection，但我想消除VBA新手程序员可能推断出的任何误解。)

小开

避免Select和Activate会让你成为一个更好的VBA开发者。一般来说，Select和Activate在宏被记录时被使用，因此Parent工作表或范围总是被认为是活动的。

这就是在以下情况下避免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，很可能你在复制一些东西。如果你只对值感兴趣，这是一个避免选择的好选项:

# EYZ0

试着经常引用工作表

这可能是中最常见的错误。无论何时复制范围，有时工作表没有被引用，因此VBA将错误的工作表视为ActiveWorksheet。

'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
小开

为了避免使用.Select方法，您可以将一个变量设置为您想要的属性。

例如，如果你想要Cell A1中的值，你可以设置一个变量等于该单元格的value属性。

  • 例# EYZ0

例如，如果你想要'Sheet3'的代码名，你可以设置一个变量等于该工作表的Codename属性。

  • 例# EYZ0
小开

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

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

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

小开

这些方法是相当不光彩的，所以以的Vityata和jeepep#为例，为了划清界限:

调用.Activate， .Select， Selection， ActiveSomething方法/属性

基本上是因为调用它们主要是为了通过应用程序UI处理用户输入。因为它们是用户通过UI处理对象时调用的方法，所以它们是宏记录器所记录的方法，这就是为什么在大多数情况下调用它们不是脆弱就是多余的原因:您不必选择一个对象以便在之后立即使用Selection执行操作。

然而，这个定义适用于需要它们的情况:

何时调用.Activate， .Select， .Selection， .ActiveSomething方法/属性

基本上，当您希望最终用户在执行中发挥作用时。

如果您正在开发并希望用户为您的代码选择要处理的对象实例，那么.Selection或.ActiveObject是合适的。

另一方面，.Select和.Activate在您可以推断用户的下一个操作并且希望您的代码指导用户时非常有用，这可能会节省用户的时间和鼠标点击。例如，如果您的代码刚刚创建了一个全新的图表实例或更新了一个图表，用户可能想要查看它，您可以在它或其表上调用.Activate来节省用户搜索它的时间;或者，如果您知道用户将需要更新一些范围值，您可以通过编程方式选择该范围。

小开

我注意到这些答案中没有一个提到.Offset财产。这也可以用来避免在操作某些单元格时使用Select操作，特别是在引用选定的单元格时(如OP中提到的ActiveCell)。

这里有几个例子:

我还假设ActiveCell是阁下。

# EYZ0

  • 这将从activecell(即卫星)向下两行更改单元格的值为12
  • -2会将值12放在J2上面两行

# EYZ0

  • 这将把右一列的单元格(k4)复制到activecell (L4)的两列单元格。
  • 注意，0可能在offset参数中被省略
    • 所以:ActiveCell.Offset(,2)和ActiveCell.Offset(0,2)是一样的
  • 类似于前面的例子，-1将是左边的一列(预告)

这并不是说这些是更好的而不是上面的选项，但它肯定比使用select要好。注意，应该避免在工作表中使用EXCEL函数偏移量，因为它是一个volatile函数。

小开

使用.Parent特性，这个示例展示了如何仅设置一个myRng引用就可以动态访问整个环境，而不需要任何. select、. activate、. activecell、. activeworkbook、. activesheet等等。(没有任何通用的.Child特性。)

Sub ShowParents()
Dim myRng As Range
Set myRng = ActiveCell
Debug.Print myRng.Address                    ' An address of the selected cell
Debug.Print myRng.Parent.name                ' The name of sheet, where MyRng is in
Debug.Print myRng.Parent.Parent.name         ' The name of workbook, where MyRng is in
Debug.Print myRng.Parent.Parent.Parent.name  ' The name of application, where MyRng is in


' You may use this feature to set reference to these objects
Dim mySh  As Worksheet
Dim myWbk As Workbook
Dim myApp As Application


Set mySh = myRng.Parent
Set myWbk = myRng.Parent.Parent
Set myApp = myRng.Parent.Parent.Parent
Debug.Print mySh.name, mySh.Cells(10, 1).Value
Debug.Print myWbk.name, myWbk.Sheets.Count
Debug.Print myApp.name, myApp.Workbooks.Count


' You may use dynamically addressing
With myRng
.Copy


' Pastes in D1 on sheet 2 in the same workbook, where the copied cell is
.Parent.Parent.Sheets(2).Range("D1").PasteSpecial xlValues


' Or myWbk.Sheets(2).Range("D1").PasteSpecial xlValues


' We may dynamically call active application too
.Parent.Parent.Parent.CutCopyMode = False


' Or myApp.CutCopyMode = False
End With
End Sub
小开

从不使用选择或活动表的主要原因是，大多数人在运行宏时至少会打开另外几个工作簿(有时是几十个)，如果他们在宏运行时从工作表中点击离开，然后单击他们打开的其他书籍，那么“活动表”将会自动关闭。更改，而目标工作簿为不合格的“选择”;命令也会改变。

最好的情况下，你的宏会崩溃，最坏的情况下，你可能会在错误的工作簿中写入值或更改单元格，而无法“撤销”。他们。

我有一个简单的黄金法则，我遵循:添加变量"wb"和“;ws"一个工作簿对象和一个工作表对象，并总是使用它们来引用我的宏书。如果我需要引用多本书或多张表格，我会添加更多变量。

例如,

Dim wb as Workbook
Dim ws as Worksheet
Set wb = ThisWorkBook
Set ws = wb.sheets("Output")

Set wb = thisworkbook;命令绝对是关键。“ThisWorkbook"是Excel中的一个特殊值，它意味着你的VBA代码是当前运行从的工作簿。一个非常有用的快捷方式来设置你的工作簿变量。

当你在你的子的顶部，使用他们不能更简单，只要使用他们，你会使用"选择"

要改变单元格a1的值;在“Output"改为“Hello"”，而不是:

Sheets("Output").Activate
ActiveSheet.Range("A1").Select
Selection.Value = "Hello"

我们现在可以这样做:

ws.Range("A1").Value = "Hello"

如果用户使用多个电子表格，它不仅更可靠，而且不太可能崩溃;它也更短，更快，更容易写。

作为额外的奖励，如果你总是命名你的变量"wb"而且，你可以将代码从一本书复制粘贴到另一本书，如果需要更改的话，它通常只需要最小的更改就可以工作。

小开

如何避免复制粘贴?

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

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

而这个人唯一想要的是:

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

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

Destination_Range.Value = Source_Range.Value

提交答案