我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶,但我不确定如何避免使用它。我发现,如果我能够使用变量而不是Select函数,我的代码将更具可重用性。然而,我不确定如果不使用选择如何引用事物(如ActiveCell等)。
我找到了这篇关于范围的文章和这个关于不使用select的好处的例子,但我找不到任何关于如何使用的东西。
我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶,但我不确定如何避免使用它。我发现,如果我能够使用变量而不是Select函数,我的代码将更具可重用性。然而,我不确定如果不使用选择如何引用事物(如ActiveCell等)。
我找到了这篇关于范围的文章和这个关于不使用select的好处的例子,但我找不到任何关于如何使用的东西。
当前回答
两个主要原因,为什么.Select, .Activate, Selection, Activecell, Activesheet, Activeworkbook等应该避免
它会降低代码的速度。 它通常是运行时错误的主要原因。
我们如何避免它?
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的情况。(将添加更多的链接,当我遇到他们)
当您希望将工作表显示给用户以便用户可以看到它时。 例如工作宏从表单控件运行时返回错误,强制使用.Activate 当通常的Text To Columns / .Formula = .Formula方法不起作用时,您可能不得不求助于.Select
其他回答
我注意到这些答案都没有提到. 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函数偏移量,因为它是一个易失函数。
使用. 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
始终声明工作簿,工作表和单元格/范围。
例如:
Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)
因为终端用户总是只会点击按钮,一旦焦点从代码想要使用的工作簿上移开,事情就会完全出错。
永远不要使用工作簿的索引。
Workbooks(1).Worksheets("fred").cells(1,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
很明显,命名范围monthales和monthsale包含了什么,以及过程在做什么。
为什么这很重要?部分原因是其他人更容易理解它,但即使你是唯一一个看到或使用你的代码的人,你仍然应该使用命名范围和良好的变量名,因为你会忘记一年后你打算用它做什么,你会浪费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列就可以随意移动,代码就可以继续正常工作。
从不使用选择或活动表的主要原因是,大多数人在运行宏时至少会打开另外几个工作簿(有时是几十个),如果他们在宏运行时从工作表中单击离开,并单击他们已经打开的其他工作簿,那么“活动表”会发生变化,而不满足“选择”命令的目标工作簿也会发生变化。
在最好的情况下,你的宏会崩溃,在最坏的情况下,你可能会在错误的工作簿中写入值或更改单元格,而无法“撤消”它们。
我有一个我遵循的简单的黄金法则:为工作簿对象和工作表对象添加名为“wb”和“ws”的变量,并始终使用它们来引用我的宏书。如果我需要引用多本书或多张表格,我会添加更多变量。
例如,
Dim wb as Workbook
Dim ws as Worksheet
Set wb = ThisWorkBook
Set ws = wb.sheets("Output")
“Set wb = ThisWorkbook”命令绝对是关键。“ThisWorkbook”是Excel中的一个特殊值,它意味着您的VBA代码当前正在运行的工作簿。一个非常有用的快捷方式来设置你的工作簿变量。
当你在你的Sub顶部做了这些之后,使用它们再简单不过了,只要在你使用“Selection”的地方使用它们:
因此,要将“Output”中的单元格“A1”的值更改为“Hello”,而不是:
Sheets("Output").Activate
ActiveSheet.Range("A1").Select
Selection.Value = "Hello"
我们现在可以这样做:
ws.Range("A1").Value = "Hello"
如果用户使用多个电子表格,它不仅更可靠,而且不太可能崩溃;它也更短,更快,更容易写。
作为额外的奖励,如果你总是将变量命名为“wb”和“ws”,你可以将代码从一本书复制粘贴到另一本书,通常只需要进行最小的更改就可以工作。