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

我已经听到很多有关在Excel VBA中使用.Select的可理解的厌恶,但是不确定如何避免使用它。我发现如果我能够使用变量而不是Select函数,那么我的代码将更可重用。但是,如果不使用Select,我不确定如何引用事物(例如ActiveCell等)。

我发现有关范围的这篇文章< / a>和此示例有关不使用的好处选择,但在如何上找不到任何内容?

标签:excel,vba

回答1:

一些避免选择的例子

使用Dim d变量

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

同样,这是指活动工作簿。除非您特别想只使用ActiveWorkbookThisWorkbook,否则最好也将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 s和Function s

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

您还应该将方法(例如FindCopy)应用于变量

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

这是一个可能的小品尝师。

回答2:

.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

回答3:

我将在上面给出的所有出色答案中添加一点重点:

要避免使用"选择",最大的可能就是尽可能 在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

很明显,MonthsMonthlySales的命名范围包含什么,以及该过程在做什么。

为什么这很重要?部分原因是因为它使其他人更容易理解它,但即使您是唯一会看到或使用您的代码的人,您仍应使用命名范围和良好的变量名,因为 您会忘记< / em> ,一年后您打算做什么,而 您会浪费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列中添加带有年份的新列! ",或者在"月份"和"销售"列之间放置一个费用列,或者在每个列中添加标题。现在,您的代码已损坏。而且由于使用了可怕的变量名,因此花了更多的时间弄清楚如何解决它,而不是应该花费的时间。

如果您使用命名范围作为开头,则MonthsSales列可以随意移动,并且代码将继续正常运行。

回答4:

我要给出简短的答案,因为其他所有人都给出了很长的答案。

每当记录宏并重用它们时,都会获得.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"

这些方法有多种组合,但这将是尽可能简短地表达给像我这样不耐烦的人的总体思路。

回答5:

" ...并且我发现,如果我能够使用变量而不是Select函数,那么我的代码将更可重用。"

虽然我只能想到少数几个情况,其中.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,因为它与.SelectActiveCell密切相关。作为工作表属性,它还有许多其他用途。

(是的,我知道这个问题是关于.Select而不是Selection的,但是我想消除新手VBA编码人员可能推断出的任何误解。)< / sub>

回答6:

请注意,下面我将比较Select方法(OP希望避免的一种方法)和Range方法(这是问题的答案)。因此,当您看到第一个"选择"时不要停止阅读。

这实际上取决于您要执行的操作。无论如何,一个简单的例子可能会有用。假设您要将活动单元格的值设置为" foo"。使用ActiveCell可以编写如下代码:

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

如果要将其用于非活动单元格(例如" B2"),则应首先选择它,如下所示:

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

使用范围,您可以编写一个更通用的宏,该宏可用于将所需的任何单元格的值设置为所需的任何值:

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

希望这有助于将事情清除一些。

回答7:

避免使用SelectActivate可以使您成为更好的VBA开发人员。通常,记录宏时使用SelectActivate,因此Parent工作表或范围始终被视为活动表。 >

在以下情况下,这是避免使用SelectActivate的方法:


添加新工作表并在其上复制单元格:

从(用宏记录器生成的代码):

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]

不复制值,而是取值

通常,如果您愿意选择,则很可能是您要复制某些内容。如果您只对值感兴趣,这是避免选择的好方法:

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


尝试始终也参考工作表

这可能是

回答8:

始终说明工作簿,工作表和单元格/范围。

例如:

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

因为最终用户将始终只单击按钮,并且一旦焦点移到工作簿上,代码就想使用它,那么事情就完全出错了。

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

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

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

回答9:

这些方法都受到了侮辱,因此以@Vityata和@Jeeped为首,以便在沙子上画一条线:

为什么不调用.Activate.SelectSelectionActiveSomething方法/属性

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

但是,此定义解决了需要调用它们的情况:

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

基本上是在您期望最终用户在执行中起作用的时候。

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

另一方面,当您可以推断用户的下一个动作并且希望您的代码指导用户时,可以使用.Select.Activate。给他一些时间和鼠标单击。例如,如果您的代码只是创建了图表的全新实例或已更新的实例,则用户可能想将其检出,您可以在其或其工作表上调用.Activate来保存用户寻找时间;或者如果您知道用户需要更新某些范围值,则可以通过编程方式选择该范围。

回答10:

IMHO对.select的使用来自与我一样的人,他们像我一样开始根据需要通过记录宏来学习VBA,然后修改代码而没有意识到.select和随后的< code>选择只是不必要的中间人。

通过直接使用已经存在的对象可以避免

.select,因为已经发布了许多方法,因此可以避免各种间接引用,例如以复杂的方式计算i和j,然后编辑单元格(i ,j)等。

否则,.select本身没有任何隐含的错误,您可以轻松找到其用途,例如我有一个电子表格,其中填充了日期,然后激活宏以对其进行魔术处理并将其以可接受的格式导出到另一张纸上,但是这需要一些最终的手动(不可预测的)输入到相邻的单元格中。因此,.select的时刻到了,为我节省了额外的鼠标移动和单击。

回答11:

快速解答:

为避免使用.Select方法,可以将变量设置为所需属性。

►例如,如果要在CellA1中使用值,则可以设置一个等于该单元格的value属性的变量。

  • 示例valOne=Range("A1").Value

►例如,如果要使用" Sheet3"的代号,则可以设置一个等于该工作表的代号属性的变量。

  • 示例valTwo=Sheets("Sheet3").Codename

我希望能有所帮助。如果您有任何问题,请告诉我。

回答12:

我注意到这些答案都没有提到。Offset属性。也可以用来避免在处理某些单元格时使用Select操作,尤其是在引用选定单元格时(如OP中使用ActiveCell提到的那样)。

这里有几个例子。

我还将假定" ActiveCell"为 J4

ActiveCell.Offset(2,0).Value=12

  • 这会将单元格J6的值更改为12
  • 负-2会引用J2

ActiveCell.Offset(0,1).复制ActiveCell.Offset(,2)

  • 这会将k4中的单元格复制到L4
  • 请注意,如果不需要,则offset参数中不需要" 0"(,2)
  • 类似于前面的示例,负1将是i4

ActiveCell.Offset(,-1).EntireColumn.ClearContents

  • 这将清除k列中所有单元格中的值。

这些并不是说它们比上述选项"更好",而只是列出了替代方案。

回答13:

使用.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 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

回答14:

这是一个示例,它将清除单元格" A1"的内容(如果选择类型为xllastcell等,则为更多内容)。无需选择单元格即可完成所有操作。

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents 

我希望这对某人有帮助。

回到顶部