我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶,但我不确定如何避免使用它。我发现,如果我能够使用变量而不是Select函数,我的代码将更具可重用性。然而,我不确定如果不使用选择如何引用事物(如ActiveCell等)。
我找到了这篇关于范围的文章和这个关于不使用select的好处的例子,但我找不到任何关于如何使用的东西。
我听说过很多关于在Excel VBA中使用. select的可以理解的厌恶,但我不确定如何避免使用它。我发现,如果我能够使用变量而不是Select函数,我的代码将更具可重用性。然而,我不确定如果不使用选择如何引用事物(如ActiveCell等)。
我找到了这篇关于范围的文章和这个关于不使用select的好处的例子,但我找不到任何关于如何使用的东西。
当前回答
为了避免使用. select方法,可以将变量设置为所需的属性。
例如,如果你想要单元格A1中的值,你可以设置一个变量等于该单元格的value属性。
示例valOne = Range("A1")。价值
例如,如果你想要“Sheet3”的代码名,你可以设置一个变量等于该工作表的codename属性。
示例valTwo = Sheets("Sheet3")。代号
其他回答
我要给出简短的答案,因为其他人都给出了长答案。
当您记录宏并重用它们时,您将得到.select和.activate。当你选择一个单元格或工作表时,它会使它激活。从那时起,当你使用像Range这样的非限定引用时。值他们只是使用活动单元格和表。如果您没有观察代码放置的位置或用户单击工作簿,这也会产生问题。
因此,您可以通过直接引用单元格来消除这些问题。这是:
'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
很明显,命名范围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列就可以随意移动,代码就可以继续正常工作。
如何避免复制粘贴?
让我们面对现实吧:这个在记录宏时经常出现:
Range("X1").Select
Selection.Copy
Range("Y9").Select
Selection.Paste
而这个人唯一想要的是:
Range("Y9").Value = Range("X1").Value
因此,与其在VBA宏中使用复制粘贴,我建议使用以下简单的方法:
Destination_Range.Value = Source_Range.Value
避免选择和激活是一个举动,使你更好的VBA开发人员。一般来说,在记录宏时使用“选择”和“激活”,因此父工作表或范围总是被认为是活动的。
这是在以下情况下避免选择和激活的方法:
添加一个新的工作表并复制一个单元格:
从(宏记录器生成的代码):
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
To:
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
To:
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]
不是复制值,而是取值
通常,如果你愿意选择,很可能你是在复制一些东西。如果你只对值感兴趣,这是一个避免选择的好选项:
范围(“B1: B6”)。值=范围("A1:A6")。价值
试着经常引用工作表
这可能是vba中最常见的错误。无论何时复制范围,有时工作表没有被引用,因此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
另一个很好的例子是当你需要将所有的表导出到一个PDF文件中,正如在这种情况下提到的那样——如何避免在本例中VBA中的选择/活动语句? 当一个命令只适用于ActiveWindow时,如ActiveWindow。缩放或ActiveWindow。FreezePanes
使用. 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