我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶,但我不确定如何避免使用它。我发现,如果我能够使用变量而不是Select函数,我的代码将更具可重用性。然而,我不确定如果不使用选择如何引用事物(如ActiveCell等)。
我找到了这篇关于范围的文章和这个关于不使用select的好处的例子,但我找不到任何关于如何使用的东西。
我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶,但我不确定如何避免使用它。我发现,如果我能够使用变量而不是Select函数,我的代码将更具可重用性。然而,我不确定如果不使用选择如何引用事物(如ActiveCell等)。
我找到了这篇关于范围的文章和这个关于不使用select的好处的例子,但我找不到任何关于如何使用的东西。
当前回答
如何避免复制粘贴?
让我们面对现实吧:这个在记录宏时经常出现:
Range("X1").Select
Selection.Copy
Range("Y9").Select
Selection.Paste
而这个人唯一想要的是:
Range("Y9").Value = Range("X1").Value
因此,与其在VBA宏中使用复制粘贴,我建议使用以下简单的方法:
Destination_Range.Value = Source_Range.Value
其他回答
这些方法都是被污名化的,所以以Vityata和jeomed为首为了划清界限:
调用.Activate, .Select, Selection, ActiveSomething方法/属性
基本上是因为调用它们主要是为了通过应用程序UI处理用户输入。因为它们是用户通过UI处理对象时调用的方法,所以它们是宏记录器所记录的方法,这就是为什么在大多数情况下调用它们不是脆弱就是多余的原因:您不必选择一个对象以便在随后执行Selection操作。
然而,这个定义适用于需要它们的情况:
何时调用.Activate, .Select, .Selection, .ActiveSomething方法/属性
基本上,当你希望最终用户在执行过程中发挥作用时。
如果您正在开发并希望用户为您的代码选择要处理的对象实例,那么. selection或. activeobject是合适的。
On the other hand, .Select and .Activate are of use when you can infer the user's next action and you want your code to guide the user, possibly saving him/her some time and mouse clicks. For example, if your code just created a brand new instance of a chart or updated one, the user might want to check it out, and you could call .Activate on it or its sheet to save the user the time searching for it; or if you know the user will need to update some range values, you can programmatically select that range.
请注意,在下面我将比较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
一些如何避免选择的例子
使用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
这是对可能性的小小尝试。
始终声明工作簿,工作表和单元格/范围。
例如:
Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)
因为终端用户总是只会点击按钮,一旦焦点从代码想要使用的工作簿上移开,事情就会完全出错。
永远不要使用工作簿的索引。
Workbooks(1).Worksheets("fred").cells(1,1)
当用户运行您的代码时,您不知道还会打开哪些工作簿。
我注意到这些答案都没有提到. offset属性。这也可以用来避免在操作某些单元格时使用Select操作,特别是在引用选定的单元格时(正如OP中使用ActiveCell提到的那样)。
这里有几个例子:
我还假定ActiveCell是J4。
ActiveCell。Offset(2,0).Value = 12
这将从activecell(即J6)往下两行更改单元格的值为12 A -2会把值12放在J2上面两行
ActiveCell.Offset(0, 1)。ActiveCell.Offset副本(2)
这将把右边一列的单元格(k4)复制到活动单元格(L4)两列的单元格。 注意,offset参数中0可能被省略 因此:activecell。offset(,2)和activecell。offset(0,2)是一样的 类似于前面的例子,-1是左边的一列(i4)
这并不是说这些选项比上面的选项更好,但它肯定比使用select要好。注意,在工作表中应该避免使用EXCEL函数偏移量,因为它是一个易失函数。