如何避免在 Excel VBA 中使用 Select

Question

提问by BiGXERO

I've heard much about the understandable abhorrence of using .Selectin Excel VBA, but am unsure of how to avoid using it. I am finding that my code would be more re-usable if I were able to use variables instead of Selectfunctions. However, I am not sure how to refer to things (like the ActiveCelletc.) if not using Select.

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

I have found this article on rangesand this example on the benefits of not using selectbut can't find anything on how?

我发现这篇关于范围的文章和这个关于不使用 select 的好处的例子，但找不到关于如何使用的任何内容？

Answer 1

回答by chris neilsen

Some examples of how to avoid select

如何避免选择的一些示例

Use Dim'd variables

使用Dim'd 变量

Dim rng as Range

Setthe variable to the required range. There are many ways to refer to a single-cell range

Set变量到所需的范围。有多种方法可以引用单个单元格范围

Set rng = Range("A1")
Set rng = Cells(1,1)
Set rng = Range("NamedRange")

or a multi-cell range

或多单元格范围

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)

You canuse the shortcut to the Evaluatemethod, but this is less efficient and should generally be avoided in production code.

您可以使用该Evaluate方法的快捷方式，但这效率较低，通常应避免在生产代码中使用。

Set rng = [A1]
Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheetvariable too

以上所有示例均引用活动工作表上的单元格。除非您特别想只使用活动工作表，否则最好也将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

If you dowant to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheetmethods change the active sheet.

如果您确实想使用ActiveSheet，为了清楚起见，最好是明确的。但要小心，因为某些Worksheet方法会更改活动表。

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbookor ThisWorkbook, it is better to Dim a Workbookvariable too.

同样，这指的是活动工作簿。除非您特别想只使用ActiveWorkbookor ThisWorkbook，否则最好也将Workbook变量变暗。

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

If you dowant to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBookmethods change the active book.

如果您确实想使用ActiveWorkbook，为了清楚起见，最好是明确的。但要小心，因为许多WorkBook方法会更改活动书。

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbookobject to refer to the book containing the running code.

您还可以使用该ThisWorkbook对象来引用包含运行代码的书籍。

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

一个常见的（坏的）代码是打开一本书，获取一些数据然后再次关闭

This is bad:

这不好：

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

And would be better like:

并且会更好：

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

Pass ranges to your Subs and Functions as Range variables

将范围作为范围变量传递给您的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

You should also apply Methods (such as Findand Copy) to variables

您还应该将方法（例如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

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that

如果您在一系列单元格上循环，通常最好（更快）先将范围值复制到变体数组，然后再循环

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

This is a small taster for what's possible.

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

Answer 2

回答by Siddharth Rout

Two Main reasons why .Select/.Activate/Selection/Activecell/Activesheet/Activeworkbooketc... should be avoided

应该避免.Select/ .Activate/ Selection/ Activecell/ Activesheet/ Activeworkbooketc... 的两个主要原因

It slows down your code.
It is usually the main cause of runtime errors.

它会减慢您的代码速度。
它通常是运行时错误的主要原因。

How do we avoid it?

我们如何避免它？

1)Directly work with the relevant objects

1)直接使用相关对象

Consider this code

考虑这个代码

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

This code can also be written as

这段代码也可以写成

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

2)If required declare your variables. The same code above can be written as

2)如果需要，声明您的变量。上面同样的代码可以写成

Dim ws as worksheet

Set ws = Sheets("Sheet1")

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

Answer 3

回答by Rick supports Monica

One small point of emphasis I'll add to all the excellent answers given above:

我将在上面给出的所有优秀答案中添加一个小的重点：

Probably the biggest thing you can do to avoid using Select is to as much as possible, use named ranges (combined with meaningful variable names) in your VBA code. This point was mentioned above, but glossed over a bit; however, it deserves special attention.

为了避免使用 Select，您可以做的最重要的事情可能是在您的 VBA 代码中尽可能多地使用命名范围（结合有意义的变量名称）。这一点在上面已经提到过，但稍微掩盖了；然而，它值得特别关注。

Here are a couple additional reasons to make liberal use of named ranges though I am sure I could think of more.

以下是自由使用命名范围的几个额外原因，尽管我相信我可以想到更多。

Named ranges make your code easier to read and understand.

命名范围使您的代码更易于阅读和理解。

Example:

例子：

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

It is pretty obvious what the named ranges Monthsand MonthlySalescontain, and what the procedure is doing.

命名范围Months和MonthlySales包含的内容以及过程正在执行的操作非常明显。

Why is this important? Partially because it is easier for other people to understand it, but even if you are the only person who will ever see or use your code, you should still use named ranges and good variable names because YOU WILL FORGETwhat you meant to do with it a year later, and you will waste30 minutes just figuring out what your code is doing.

为什么这很重要？部分是因为其他人更容易理解它，但即使你是唯一会看到或使用你的代码的人，你仍然应该使用命名范围和好的变量名，因为你会忘记你打算用它做什么一年后，你将浪费30 分钟来弄清楚你的代码在做什么。

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

命名范围确保您的宏不会在（不是如果！）电子表格的配置更改时中断。

Consider, if the above example had been written like this:

考虑一下，如果上面的例子是这样写的：

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

This code will work just fine at first - that is until you or a future user decides "gee wiz, I think I'm going to add a new column with the year in Column A!", or put an expenses column between the months and sales columns, or add a header to each column. Now, your code is broken. And because you used terrible variable names, it will take you a lot more time to figure out how to fix it than it should take.

这段代码一开始可以正常工作 - 直到您或未来的用户决定“哎呀，我想我要在 Column 中添加一个带有年份的新列A！”，或者在月份和月份之间添加一个费用列销售列，或为每列添加标题。现在，您的代码已损坏。而且因为你使用了糟糕的变量名，你会花更多的时间来弄清楚如何修复它而不是它应该花费的时间。

If you had used named ranges to begin with, the Monthsand Salescolumns could be moved around all you like, and your code will continue working just fine.

如果您开始使用命名范围，则可以随意移动Months和Sales列，并且您的代码将继续正常工作。

Answer 4

回答by MattB

I'm going to give the short answer since everyone else gave the long one.

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

You'll get .select and .activate whenever you record macros and reuse them. When you .select a cell or sheet it just makes it active. From that point on whenever you use unqualified references like Range.Valuethey just use the active cell and sheet. This can also be problematic if you don't watch where your code is placed or a user clicks on the workbook.

每当您录制宏并重复使用它们时，您都会获得 .select 和 .activate 。当您 .select 一个单元格或工作表时，它只会使其处于活动状态。从那时起，无论何时您使用不合格的引用，就像Range.Value他们只使用活动单元格和工作表一样。如果您不注意代码的放置位置或用户单击工作簿，这也可能会出现问题。

So, you can eliminate these issues by directly referencing your cells. Which goes:

因此，您可以通过直接引用您的单元格来消除这些问题。这是：

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

Or you could

或者你可以

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

There are various combinations of these methods, but that would be the general idea expressed as shortly as possible for impatient people like me.

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

Answer 5

回答by MattB

"... and am finding that my code would be more re-usable if I were able to use variables instead of Select functions."

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

While I cannot think of any more than an isolated handful of situations where .Selectwould be a better choice than direct cell referencing, I would rise to the defense of Selectionand point out that it should not be thrown out for the same reasons that .Selectshould be avoided.

虽然我想不出.Select比直接单元格引用更好的少数几种情况，但我会捍卫Selection并指出不应出于.Select应避免的相同原因将其丢弃。

There are times when having short, time-saving macro sub routines assigned to hot-key combinations available with the tap of a couple of keys saves a lot of time. Being able to select a group of cells to enact the operational code on works wonders when dealing with pocketed data that does not conform to a worksheet-wide data format. Much in the same way that you might select a group of cells and apply a format change, selecting a group of cells to run special macro code against can be a major time saver.

有时，将简短、省时的宏子例程分配给可用的热键组合，只需轻按几个键即可节省大量时间。在处理不符合工作表范围数据格式的袋装数据时，能够选择一组单元格来制定操作代码创造奇迹。与您可能选择一组单元格并应用格式更改的方式非常相似，选择一组单元格来运行特殊的宏代码可以节省大量时间。

Examples of Selection-based sub framework:

基于选择的子框架示例：

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

The actual code to process could be anything from a single line to multiple modules. I have used this method to initiate long running routines on a ragged selection of cells containing the filenames of external workbooks.

要处理的实际代码可以是从一行到多个模块的任何内容。我已经使用这种方法在包含外部工作簿文件名的参差不齐的单元格选择上启动长时间运行的例程。

In short, don't discard Selectiondue to its close association with .Selectand ActiveCell. As a worksheet property it has many other purposes.

总之，不要丢弃，Selection因为它与.Select和密切相关ActiveCell。作为工作表属性，它还有许多其他用途。

_{(Yes, I know this question was about .Select, not Selectionbut I wanted to remove any misconceptions that novice VBA coders might infer.)}

_{（是的，我知道这个问题是关于.Select，不是，Selection但我想消除新手 VBA 编码人员可能推断出的任何误解。）}

Answer 6

回答by Francesco Baruchelli

Please note that in the following I'm comparing the Select approach (the one that the OP wants to avoid), with the Range approach (and this is the answer to the question). So don't stop reading when you see the first Select.

请注意，在下面我将 Select 方法（OP 希望避免的方法）与 Range 方法（这是问题的答案）进行比较。所以当你看到第一个 Select 时不要停止阅读。

It really depends on what you are trying to do. Anyway a simple example could be useful. Let's suppose that you want to set the value of the active cell to "foo". Using ActiveCell you would write something like this:

这真的取决于你想要做什么。无论如何，一个简单的例子可能很有用。假设您要将活动单元格的值设置为“foo”。使用 ActiveCell 你会写这样的东西：

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

If you want to use it for a cell that is not the active one, for instance for "B2", you should select it first, like this:

如果要将它用于非活动单元格，例如“B2”，则应先选择它，如下所示：

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

Using Ranges you can write a more generic macro that can be used to set the value of any cell you want to whatever you want:

使用范围，您可以编写一个更通用的宏，该宏可用于将您想要的任何单元格的值设置为您想要的任何值：

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

Then you can rewrite Macro2 as:

然后你可以将 Macro2 重写为：

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

And Macro1 as:

和 Macro1 为：

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub

Hope this helps to clear things up a little bit.

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

Answer 7

回答by Vityata

Avoiding Selectand Activateis the move that makes you a bit better VBA developer. In general, Selectand Activateare used when a macro is recorded, thus the Parentworksheet or range is always considered the active one.

避免Select和Activate是使您成为更好的 VBA 开发人员的举措。一般情况下，Select和Activate当宏被记录的使用，因此，Parent工作表或范围始终被认为处于活动状态。

This is how you may avoid Selectand Activatein the following cases:

这是你可以如何避免Select与Activate以下情况：

Adding a new Worksheet and copying a cell on it:

添加一个新的工作表并在其上复制一个单元格：

From (code generated with macro recorder):

来自（用宏记录器生成的代码）：

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

When you want to copy range between worksheets:

当您想在工作表之间复制范围时：

From:

从：

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

Using fancy named ranges

使用花哨的命名范围

You may access them with [], which is really beautiful, compared to the other way. Check yourself:

与[]其他方式相比，您可以使用访问它们，这真的很漂亮。自行检查：

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

The example from above would look like this:

上面的例子看起来像这样：

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

Not copying values, but taking them

不是复制值，而是获取它们

Usually, if you are willing to select, most probably you are copying something. If you are only interested in the values, this is a good option to avoid select:

通常，如果您愿意select，很可能您正在复制某些东西。如果您只对值感兴趣，这是避免选择的好选择：

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

Try always to reference the Worksheet as well

尝试始终参考工作表

This is probably the most common mistake in vba. Whenever you copy ranges, sometimes the worksheet is not referenced and thus VBA considers the wrong sheet the ActiveWorksheet.

这可能是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

Can I really never use `.Select`or `.Activate`for anything?

我真的可以永远不使用`.Select`或`.Activate`用于任何东西吗？

A good example of when you could be justified in using .Activateand .Selectis when you want make sure that a specific Worksheet is selected for visual reasons. E.g., that your Excel would always open with the cover worksheet selected first, disregarding which which was the ActiveSheet when the file was closed.

一个很好的例子，说明何时可以合理使用.Activate以及.Select何时需要确保出于视觉原因选择特定的工作表。例如，您的 Excel 将始终以首先选择的封面工作表打开，而不管文件关闭时哪个是 ActiveSheet。

Thus, something like the code below is absolutely OK:

因此，像下面这样的代码绝对没问题：

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub

Another good example is when you need to export all sheets into one PDF file, as mentioned in this case - How to avoid select/active statements in VBA in this example?
When a command only works with ActiveWindowlike ActiveWindow.Zoomor ActiveWindow.FreezePanes

另一个很好的例子是，当您需要将所有工作表导出到一个 PDF 文件时，如本例中所述 -如何避免本例中 VBA 中的选择/活动语句？
当命令只适用ActiveWindow像ActiveWindow.Zoom或ActiveWindow.FreezePanes

Answer 8

回答by user1644564

Always state the workbook, worksheet and the cell/range.

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

For example:

例如：

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

Because end users will always just click buttons and as soon as the focus moves off of the workbook the code wants to work with then things go completely wrong.

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

And never use the index of a workbook.

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

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

You don't know what other workbooks will be open when the user runs your code.

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

Answer 9

回答by LFB

These methods are rather stigmatized, so taking the lead of @Vityata and @Jeeped for the sake of drawing a line in the sand:

这些方法被污名化了，所以以@Vityata 和@Jeeped 为首，在沙子里画一条线：

Why not call `.Activate`, `.Select`, `Selection`, `ActiveSomething`methods/properties

为什么不调用`.Activate`, `.Select`, `Selection`,`ActiveSomething`方法/属性

Basically because they're called primarily to handle user input through the Application UI. Since they're the methods called when the user handles objects through the UI, they're the ones recorded by the macro-recorder, and that's why calling them is either brittle or redundant for most situations: you don't have to select an object so as to perform an action with Selectionright afterwards.

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

However, this definition settles situations on which they are called for:

然而，这个定义解决了需要它们的情况：

When to call `.Activate`, `.Select`, `.Selection`, `.ActiveSomething`methods/properties

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

Basically when you expect the final userto play a role in the execution.

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

If you are developing and expect the user to choose the object instances for your code to handle, then .Selectionor .ActiveObjectare apropriate.

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

On the other hand, .Selectand .Activateare of use when you can infer the user's next action and you want your code to guide the user, possibly saving him 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 .Activateon 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 programatically select that range.

在另一方面，.Select并且.Activate在使用时，你可以推断出用户的下一个行动，你希望你的代码，以引导用户，可能为他节省了一些时间和鼠标点击。例如，如果你的代码刚刚创建了一个全新的图表实例或更新了一个图表，用户可能想要查看它，你可以调用.Activate它或它的工作表来节省用户搜索它的时间；或者，如果您知道用户需要更新某些范围值，则可以通过编程方式选择该范围。

Answer 10

回答by Eleshar

IMHO use of .selectcomes from people, who like me started learning VBA by necessity through recording macros and then modifying the code without realizing that .selectand subsequent selectionis just an unnecessary middle-men.

恕我直言，使用.select来自那些像我这样的人，他们通过录制宏开始学习 VBA，然后在没有意识到这一点的情况下修改代码.select，随后selection只是一个不必要的中间人。

.selectcan be avoided, as many posted already, by directly working with the already existing objects, which allows various indirect referencing like calculating i and j in a complex way and then editing cell(i,j), etc.

.select可以避免，因为许多已经发布，通过直接使用已经存在的对象，这允许各种间接引用，例如以复杂的方式计算 i 和 j 然后编辑单元格（i，j）等。

Otherwise, there is nothing implicitly wrong with .selectitself and you can find uses for this easily, e.g. I have a spreadsheet that I populate with date, activate macro that does some magic with it and exports it in an acceptable format on a separate sheet, which, however, requires some final manual (unpredictable) inputs into an adjacent cell. So here comes the moment for .selectthat saves me that additional mouse movement and click.

否则，.select它本身并没有任何隐含的错误，您可以轻松找到它的用途，例如，我有一个用日期填充的电子表格，激活对它执行一些魔术的宏，并以可接受的格式将其导出到单独的工作表上，，然而，需要一些最终的手动（不可预测）输入到相邻单元格中。所以现在是时候为.select我节省额外的鼠标移动和点击。

如何避免在 Excel VBA 中使用 Select

提问by BiGXERO

回答by chris neilsen

回答by Siddharth Rout

回答by Rick supports Monica

Named ranges make your code easier to read and understand.

命名范围使您的代码更易于阅读和理解。

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

命名范围确保您的宏不会在（不是如果！）电子表格的配置更改时中断。

回答by MattB

回答by MattB

回答by Francesco Baruchelli

回答by Vityata

Adding a new Worksheet and copying a cell on it:

添加一个新的工作表并在其上复制一个单元格：

When you want to copy range between worksheets:

当您想在工作表之间复制范围时：

Using fancy named ranges

使用花哨的命名范围

Not copying values, but taking them

不是复制值，而是获取它们

Try always to reference the Worksheet as well

尝试始终参考工作表

Can I really never use `.Select`or `.Activate`for anything?

我真的可以永远不使用`.Select`或`.Activate`用于任何东西吗？

回答by user1644564

回答by LFB

Why not call `.Activate`, `.Select`, `Selection`, `ActiveSomething`methods/properties

为什么不调用`.Activate`, `.Select`, `Selection`,`ActiveSomething`方法/属性

When to call `.Activate`, `.Select`, `.Selection`, `.ActiveSomething`methods/properties

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

回答by Eleshar

相关推荐

最近更新

标签

如何避免在 Excel VBA 中使用 Select

提问by BiGXERO

回答by chris neilsen

回答by Siddharth Rout

回答by Rick supports Monica

Named ranges make your code easier to read and understand.

命名范围使您的代码更易于阅读和理解。

Named ranges ensure that your macros do not break when (not if!) the configuration of the spreadsheet changes.

命名范围确保您的宏不会在（不是如果！）电子表格的配置更改时中断。

回答by MattB

回答by MattB

回答by Francesco Baruchelli

回答by Vityata

Adding a new Worksheet and copying a cell on it:

添加一个新的工作表并在其上复制一个单元格：

When you want to copy range between worksheets:

当您想在工作表之间复制范围时：

Using fancy named ranges

使用花哨的命名范围

Not copying values, but taking them

不是复制值，而是获取它们

Try always to reference the Worksheet as well

尝试始终参考工作表

Can I really never use .Selector .Activatefor anything?

我真的可以永远不使用.Select或.Activate用于任何东西吗？

回答by user1644564

回答by LFB

Why not call .Activate, .Select, Selection, ActiveSomethingmethods/properties

为什么不调用.Activate, .Select, Selection,ActiveSomething方法/属性

When to call .Activate, .Select, .Selection, .ActiveSomethingmethods/properties

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

回答by Eleshar

相关推荐

将行复制到新工作表 VBA

vba CDate 类型不匹配错误

用于计算 VBA 中总行数的 Excel 文件

如何引用 Excel 工作表而不在 VBA 中对其名称进行硬编码？

相关推荐

最近更新

标签

Can I really never use `.Select`or `.Activate`for anything?

我真的可以永远不使用`.Select`或`.Activate`用于任何东西吗？

Why not call `.Activate`, `.Select`, `Selection`, `ActiveSomething`methods/properties

为什么不调用`.Activate`, `.Select`, `Selection`,`ActiveSomething`方法/属性

When to call `.Activate`, `.Select`, `.Selection`, `.ActiveSomething`methods/properties

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