vba .Select、.Activesheet、.Activecell 等...

.Select, .Activesheet, .Activecell etc...

提问by Trace

For this question, I refer to the post below to clarify myself:
Why is my conditional format offset when added by VBA?

对于这个问题，我参考下面的帖子来澄清自己：
为什么我的条件格式被 VBA 添加时会发生偏移？

In many, many posts I see these days, OP's are silently allowed to use .Activate, .Select, .Offset, etc... while they are an open door to potential bugs (most often caused by the end users).
The code is sometimes even supported.

在我这些天看到的许多帖子中，OP 被默默地允许使用 .Activate、.Select、.Offset 等……而它们却是潜在错误的大门（通常由最终用户引起）。
有时甚至支持代码。

My question: Is there one valid situation where you would use any of these statements without direct alternatives being available that catch typical bugs resulting from these stmts?

我的问题：是否存在一种有效的情况，您会在没有直接替代方法的情况下使用这些语句中的任何一个来捕获由这些 stmts 导致的典型错误？

I'm referring to dynamicsolutions that in my opinion are a mustwhen developing for Excel. Personally, in more than 6 years I can't remember a single case where I needed it; it seems always to be one of the the worst options available. In my previous company, it was a silent rule never to use it and it only made my VBA life (and that of the end user) better.

我指的是动态解决方案，在我看来，在为 Excel 开发时是必须的。就个人而言，在 6 年多的时间里，我记不起一个需要它的案例；它似乎总是可用的最糟糕的选择之一。在我以前的公司中，永远不要使用它是一个沉默的规则，它只会让我的 VBA 生活（以及最终用户的生活）变得更好。

Why I create this question is because I think that it is worthful to make newcomers into VBA aware of the risks they take when using these statements (by experience proven risks when the End Users do something unexpected - in the end they don't have any affection with VBA) and to propose direct alternatives (I won't state I always did that before myself, but I feel in my gut that there is something wrong with just offering quick solutions on already bug monsters).

为什么我创建这个问题是因为我认为让 VBA 新手意识到他们在使用这些语句时所承担的风险是值得的（通过经验证明当最终用户做一些意想不到的事情时的风险 - 最后他们没有任何对 VBA 的感情）并提出直接的替代方案（我不会说我总是在自己之前这样做，但我觉得在我的直觉中只是为已经存在的错误怪物提供快速解决方案是有问题的）。

I believe that when silently allowed (which it automatically enhances in this case), starting VBA developers will create a growing amount of tools the wrong way (and thus also newcomers will inherit the behaviour - which they will also learn from Stack Overflow since Google returns the results they look for (!)).
If the developer is not aware why he "can" use a "select" and in which situations it is a potential bug, (s)he should never use it imho. Personally I might use the select stmt in the immediate window to do some quick checks on dynamic range definition (bug mode), but not in written code.

我相信，当默默地允许时（在这种情况下它会自动增强），开始 VBA 开发人员将以错误的方式创建越来越多的工具（因此新手也会继承这种行为 - 他们也将从 Google 回归后的 Stack Overflow 中学习他们寻找的结果（！））。
如果开发人员不知道为什么他“可以”使用“选择”以及在哪些情况下这是一个潜在的错误，那么他永远不应该使用它恕我直言。就我个人而言，我可能会在即时窗口中使用 select stmt 对动态范围定义（错误模式）进行一些快速检查，但不会在书面代码中使用。

The result makes VBA in the end even more unpopular than it is already; the language will be made the victim in case trouble appear (yet it is imho still the "best" programming support available for the Excel and Access applications). I've seen this happen too many times in a large company where VBA is always "shit".

结果最终使 VBA 比现在更不受欢迎；如果出现问题，该语言将成为受害者（但恕我直言，它仍然是 Excel 和 Access 应用程序可用的“最佳”编程支持）。我已经在一家 VBA 总是“狗屎”的大公司中看到这种情况发生了太多次。

This is only my own honest experience.
It is not a question of being right or wrong; I am interested in hearing your point of view on the question.

这只是我自己的诚实经历。
这不是对与错的问题；我很想听听你对这个问题的看法。

回答by Dick Kusleika

I agree about Select and Activate, but not ActiveWorkbook, ActiveSheet, and ActiveCell (I agree that they are abused, but not that they should be avoided, per se). There are definitely legitimate uses for those. I have a program that automates a "fill series" that does so from the ActiveCell. My program can't predict what cells will be used; it's up the user to select it. That's part of the user interface.

我同意 Select 和 Activate，但不同意 ActiveWorkbook、ActiveSheet 和 ActiveCell（我同意它们被滥用，但不应该避免它们本身）。这些肯定有合法用途。我有一个程序可以自动执行从 ActiveCell 执行的“填充系列”。我的程序无法预测将使用哪些单元格；由用户选择它。这是用户界面的一部分。

However, there are three situations where I have had to use Select (now four that I read about zoom, but I don't ever use it).

但是，在三种情况下我不得不使用 Select（现在有四种情况我读过关于缩放，但我从未使用过它）。

1. Conditional Formatting. There is a work around using Application.ConvertFormula, but it's worse than just storing the selection, selecting the right cell, doing the deed, and reselecting the previous selection.
2. Data Validation. Same reason.
3. Shapes. I wish I could remember the details, but it's been too long since I've worked with Shapes. There was something I couldn't do without selecting the shape first.

1. 条件格式。有一个使用 Application.ConvertFormula 的解决方法，但它比仅仅存储选择、选择正确的单元格、执行操作并重新选择以前的选择更糟糕。
2. 数据验证。同样的原因。
3. 形状。我希望我能记住细节，但我已经很久没有使用 Shapes 了。如果不先选择形状，我就无法做到这一点。

Ridding code of Select and Activate is a noble fight.

消除 Select 和 Activate 的代码是一场高尚的战斗。

回答by user3357963

There are a few methods in Excel that require Activate or ActiveSheet/ActiveWorkbook etc as I've been caught with a gotchas on occasion. The only one I can remember at the moment is the zoom property. Zoom affects only the sheet that's currently active in the window so to zoom all sheets you would need something like

Excel 中有一些方法需要 Activate 或 ActiveSheet/ActiveWorkbook 等，因为我有时会遇到一些问题。我目前唯一能记住的是缩放属性。缩放仅影响窗口中当前处于活动状态的工作表，因此要缩放所有工作表，您需要类似

Sub SetZoom()
Dim ws As Worksheet
    Application.screenupdating = false

    For Each ws In Worksheets
        ws.Select
        ActiveWindow.Zoom = 80
    Next ws

    Application.screenupdating = true
End Sub

回答by enderland

You can use .Selectto determine what a user's view is after running code - for example if you create a new workbook in your code, without using Activateor Selectyour user may not know this happens.

您可以使用.Select来确定运行代码后用户的视图是什么 - 例如，如果您在代码中创建了一个新工作簿，而没有使用，Activate或者Select您的用户可能不知道会发生这种情况。

I frequently end a long operation creating a new workbook or other largescale data manipulations with

我经常结束一个长时间的操作，创建一个新的工作簿或其他大规模的数据操作

FinalViewWorkbook.FinalViewSheet.Range("A1").Select

Just to inform the end user about something - "oh, this created a new workbook of reports!" etc.

只是为了通知最终用户一些事情 - “哦，这创建了一个新的报告工作簿！” 等等。

回答by Verzweifler

I think it is important in this matter to distinguish some:

我认为在这个问题上区分一些很重要：

• Active-something: Only use this if it is absolutely necessary to know what the user is handling right now. In my experience, this is usually Data Validation or Active Sheet Detection (e.g. "Update the Sheet where the user just pressed a button").
• Selection: Somewhat the same as Active, only use readingly. Userful either for Data Validation, or for gimmicks like "Interpret the cell value as path and open it in a new Explorer Window".
• Select, Activate: Imho different from Selection, as it actually changes the selected Cell, Sheet etc. Never everuse this to read or write data, since it enables a user to mess up your program by just clicking. Users love to click. Only use this to Zoom (see answer by @user3357963) or clean up a view after your code has finished working (see answer by @enderland). (I'm not sure, but I think handling the PageView also requires ActiveSheet).
• Select, Activatethe 2nd: If you are new to VBA and are learning via Macro Recorder, you will find a lot of code generated like this: First Range("A5").Select, then Selection.Value="NewValue". Join this to Range("A5").Value="NewValue".
• Offset: Personally, I don't have a problem using .Offset()- I never encountered problems with this command. Instead, I think it's a handy way of saying "The cell next to this" without having to go through "This cell's sheet at this cell's row and column+1" every time.

• Active-something：只有在绝对有必要了解用户现在正在处理的内容时才使用它。根据我的经验，这通常是数据验证或活动工作表检测（例如“更新用户刚刚按下按钮的工作表”）。
• Selection: 和 有点一样Active，仅供阅读。对于数据验证或诸如“将单元格值解释为路径并在新的资源管理器窗口中打开它”之类的噱头很有用。
• Select, Activate: Imho 与 不同Selection，因为它实际上更改了选定的单元格、工作表等。永远不要使用它来读取或写入数据，因为它使用户只需单击即可弄乱您的程序。用户喜欢点击。仅使用它来缩放（请参阅@user3357963 的答案）或在代码完成工作后清理视图（请参阅@enderland 的答案）。（我不确定，但我认为处理 PageView 也需要 ActiveSheet）。
• Select，Activate第二：如果你是VBA的新手并且正在通过Macro Recorder学习，你会发现很多这样生成的代码：首先Range("A5").Select，然后Selection.Value="NewValue"。将此加入到Range("A5").Value="NewValue".
• Offset: 就个人而言，我使用.Offset()没有问题- 我从未遇到过使用此命令的问题。相反，我认为这是说“此旁边的单元格”的一种方便方式，而不必每次都通过“此单元格的行和列 +1 处的此单元格的工作表”。

In many, many posts I see these days, OP's are silently allowed to use .Activate, .Select, .Offset, etc...

在我这些天看到的许多帖子中，OP 被默默地允许使用 .Activate、.Select、.Offset 等...

I agree with this. Even though it's easier to just give the necessary answer to make a piece of code work, the use of ActiveCell.Valueand the like should be discouraged. This will be much easier if there's a well explained Thread to link to, as this here is hopefully becoming :-)

我同意这一点。尽管给出必要的答案使一段代码工作更容易，但ActiveCell.Value不应鼓励使用 等。如果有一个很好解释的 Thread 链接到，这将容易得多，因为这有望成为:-)

回答by David Zemens

From my perspective, with few exceptions, the only time you should use Selectis as a user input, and only then after careful consideration of alternative design/UI requirements.

从我的角度来看，除了少数例外，您应该使用的唯一时间Select是作为用户输入，并且只有在仔细考虑替代设计/UI 要求之后。

For example, I'd say it's generally not advisable to rely on Selectionto let user define a Range object when this method keeps execution within the code:

例如，我会说Selection当此方法在代码中保持执行时，通常不建议依赖于让用户定义 Range 对象：

Dim myRange as Range
Set myRange = Application.InputBox("Select your range", Type:=8)

However, if you need to prompt users to select a particular shape or object on the worksheet, then maybeit's better to let them make a Selection(however, this can open up a Pandora's Box of problems without good error-handling and logic to prevent undesired user actions...).

但是，如果您需要提示用户选择工作表上的特定形状或对象，那么也许最好让他们制作一个Selection（但是，这可能会打开一个潘多拉魔盒，而没有良好的错误处理和逻辑来防止不想要的用户操作...）。

Here is an example of one such exception that I have in PowerPoint. I have some RibbonUI XML and VBA that adds buttons to the Shapesright-click context menu in PowerPoint, and adds similar buttons to the Ribbon itself. These are seamless UI that give the end-user a more "native" experience with the application -- users want to be able to right-click the chart and then run some macro procedures against that selected chart or table, etc. They don't want to press a button to open up a user form and scroll through a listbox of generic shape names or GUIDs.

这是我在 PowerPoint 中遇到的一个此类异常的示例。我有一些 RibbonUI XML 和 VBA，它们将按钮添加到ShapesPowerPoint 中的右键单击上下文菜单，并将类似的按钮添加到功能区本身。这些是无缝 UI，可为最终用户提供更“本机”的应用程序体验——用户希望能够右键单击图表，然后针对所选图表或表格运行一些宏程序，等等。他们没有不想按下按钮来打开用户表单并滚动浏览通用形状名称或 GUID 的列表框。

The procedure code needs to examine the Selectionin order to handle it properly so I can use something like below, where

过程代码需要检查Selection以正确处理它，因此我可以使用如下所示的内容，其中

Sub UpdateOrEditSelection(update As Boolean)
'This procedure invoked when user edits/updates a chart.
Dim uid As Variant
Dim sel As Selection
Dim s As Integer
Dim chartsToUpdate As Object
Dim multipleShapes As Boolean
Dim sld As Slide
Set sel = ppPres.Windows(1).Selection

If update Then
    Set chartsToUpdate = CreateObject("Scripting.Dictionary")
    Select Case sel.Type
        Case ppSelectionShapes
            For s = 1 To sel.ShapeRange.count
                uid = sel.ShapeRange(s).Name
                '....
                '...
                '..
                '.
            Next
        Case ppSelectionSlides
            For Each sld In sel.SlideRange
                For s = 1 To sld.Shapes.count
                    uid = sld.Shapes(s).Name
                    '....
                    '...
                    '..
                    '.

                Next
            Next
        Case ppSelectionText
            s = 1
            If sel.ShapeRange(s).HasTable Or sel.ShapeRange(s).HasChart Then
                uid = sel.ShapeRange(s).Name
                '....
                '...
                '..
                '.

            End If
    End Select
 '....
 '...
 '..
 '.

Where does it come from?

它从何而来？

The Macro Recorder. Essentially, this feature records every literal user input: scrolling, selecting, viewing, activating, default properties, etc., to the point of overkill. While this is sometimes helpful, it does encourage bad code written by people who don't know that it's bad, but I will not belabor that point which has been made here:

宏记录器。本质上，此功能记录了每个字面用户输入：滚动、选择、查看、激活、默认属性等，到了矫枉过正的地步。虽然这有时会有所帮助，但它确实鼓励那些不知道它不好的人编写的坏代码，但我不会详细说明这里提出的这一点：

How to avoid using Select in Excel VBA macros

如何避免在 Excel VBA 宏中使用 Select

What is better, conceptually?

从概念上讲，什么更好？

Program to the objects directly. If you're merely using VBA to mimic keystrokes and mouseclicks, you're doing it wrong.

直接对对象进行编程。如果您只是使用 VBA 来模拟击键和鼠标点击，那么您就错了。

Exceptions:

例外：

I've found when applying formatting to series data in charts, where Selectis occasionally necessary. This seems IMO to be a bug with Excel and not a design feature.

我发现在对图表中的系列数据应用格式时，Select偶尔需要。这似乎是 IMO 的 Excel 错误，而不是设计功能。

Other applications (because VBA is not onlyExcel):

其他应用程序（因为 VBA不仅是Excel）：

• Word is a different animal, which relies a lot more on Selection object
• In PowerPoint there are some sort of operations that can only be performed when the application and slide/shape are visible or otherwise in view. While you don't usually need to "select" anything, it does require more cumbersome code.

• Word 是一种不同的动物，它更多地依赖于 Selection 对象
• 在 PowerPoint 中，有一些操作只能在应用程序和幻灯片/形状可见或以其他方式显示时才能执行。虽然您通常不需要“选择”任何东西，但它确实需要更繁琐的代码。

I found this snippet in my App:

我在我的应用程序中找到了这个片段：

    Set tb = cht.Shapes.AddTextbox(msoTextOrientationHorizontal, ptLeft, tBoxTop, ptWidth, ptHeight)
    tb.Select  '<--- KEEP THIS LINE OTHERWISE TEXTBOX ALIGNMENT WILL NOT WORK ## ## ##

And this:

和这个：

    'PPT requires selecting the slide in order to export an image preview/jpg
sld.Select
ppPres.Windows(1).View.GotoSlide sld.SlideIndex
sld.Shapes(1).Chart.Export imgPath, ppShapeFormatJPG

And this, dealing with individual Pointobjects:

而这个，处理单个Point对象：

        pt.Select
        pt.format.Line.Visible = msoTrue
        pt.format.Line.Visible = msoFalse
        pt.MarkerSize = pt.MarkerSize + 2

This is not an exhaustive list, just some examples of exceptions that I found. While these were from PowerPoint, the charts in PowerPoint use the same object model as Excel so I would not be surprised if some of these also need to be hacked in Excel, and Word, too.

这不是一个详尽的列表，只是我发现的一些例外情况的例子。虽然这些来自 PowerPoint，但 PowerPoint 中的图表使用与 Excel 相同的对象模型，因此如果其中一些也需要在 Excel 和 Word 中进行破解，我不会感到惊讶。

• Outlook: I don't do much with Outlook, it is a lot like Word and actually uses the Word object model in the Inspector, but what little I do with Outlook does rely on things like ActiveInspector, etc.

• Outlook：我不怎么使用 Outlook，它很像 Word，实际上在 Inspector 中使用 Word 对象模型，但我对 Outlook 所做的很少依赖于 ActiveInspector 等。

Neither Word or PowerPoint have a "macro recorder" anymore (actually, I think Word might but it's so damn impotent as to be useless) and by the time most people do any development in other applications, they've figured most of this out already.

Word 或 PowerPoint 都不再有“宏记录器”（实际上，我认为 Word 可能有，但它太无能以至于没用），当大多数人在其他应用程序中进行任何开发时，他们已经弄清楚了大部分.