我可以使这个宏更有效或更快速吗? [英] Can I make this macro more efficient or faster?

问题描述

我是编码新手.这个宏运行缓慢，我希望有人能帮我清理它.在此先感谢您的帮助.

I am brand new to coding. This macro runs slow and I'm hopeful that someone can help me clean it up. Thanks in advance for your help.

我开发了代码以使用从外部来源购买的新潜在客户更新我公司的呼叫路由器"工作表.潜在客户以原始格式出现在名为 Fresh Agents Leads 的工作表中.一旦将新代理线索"表复制到包含呼叫路由器"工作表的MSS 呼叫路由主列表"文件中，宏就会减少原始数据，从而消除我们不使用的部分.然后它重新格式化剩下的内容以匹配旧呼叫路由器工作表的格式并将两者合并.然后将新的 Master 表重命名为 Call Router.

I developed the code to update my company's "Call Router" worksheet with new leads purchased from an outside source. The leads come to us in raw format in a worksheet called Fresh Agents Leads. Once the "Fresh Agent Leads" sheet is copied into the "MSS Call Routing Master List" file which houses the "Call Router" Worksheet, the macro reduces the raw data so that the parts we do not use are eliminated. It then re-formats what remains to match the formatting of the Old Call Router Worksheet and merges the two. It then renames the new Master sheet to Call Router.

该代码旨在从包含新代理潜在客户表的工作簿中开始.用户被指示在执行代码之前在桌面上打开 Fresh Agents Leads File 和 MSS Call Routing Master List.

The code is designed to start inside the workbook that houses the Fresh Agent Leads Sheet. The user is instructed to have both the Fresh Agents Leads File and the MSS Call Routing Master List opened on the desktop before executing the code.

    Sheets("Fresh Agent Leads").Select
    Sheets("Fresh Agent Leads").Copy After:=Workbooks( _
        "MSS Call Routing Master List.xlsx").Sheets(1)
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    Selection.Copy
    Columns("F:F").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("G:S").Select
    Selection.Delete Shift:=xlToLeft
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RIGHT(RC[1],4))"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C1048575")
    Range("C1:C1048575").Select

    Sheets("Call Router").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("Fresh Agent Leads").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Application.Run "PERSONAL.xlsb!MergeIdenticalWorksheets"
    Columns("C:C").Select
    Selection.NumberFormat = "0000"
    Range("A:A,B:B,F:F").Select
    Range("F1").Activate
    Selection.ColumnWidth = 14
    Columns("E:E").Select
    Selection.ColumnWidth = 25
    Columns("C:C").Select
    Selection.ColumnWidth = 8.29
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.RowHeight = 30
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Columns("D:D").Select
    Selection.EntireColumn.Hidden = True
    Range("E2").Select
    ActiveWindow.FreezePanes = True
    Sheets(Array("Call Router", "Fresh Agent Leads")).Select
    Sheets("Call Router").Activate
    ActiveWindow.SelectedSheets.Delete
    Sheets("Master").Select
    Sheets("Master").Name = "Call Router"
    Range("C23").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.Save
End Sub

推荐答案

虽然这段代码几乎属于 CR 而不是 SO，因为我手头有很多时间，我决定至少发布一些关于你的代码的事情.

While this code pretty much belongs in CR rather than SO, as I have a lot of time in my hands, I've decided to at least post a few things about your code.

===

一些提示:

1. 尽可能避免.Select 和.Activate.录制宏是 VBA 的良好开端，但第一步是离开这些属性提供的舒适区".一开始，它们很好，但从长远来看，它们必然会产生问题.

1. Avoid .Select and .Activate as much as possible. Recording macros is a good start to VBA, but the first big step is leaving the "comfort zone" provided by these properties. In the beginning, they're good, but they're bound to create problems in the long run.

阅读以下基本"程序:复制/粘贴/插入范围、创建/删除工作表，以及确定具有相关数据的工作表或范围的最后一行/列.这三个是你最好的朋友.把这三个背下来，你可以在Excel VBA中操作很多.

Read up on the following "basic" procedures: copying/pasting/inserting ranges, creating/deleting sheets, and determining the last rows/columns of a sheet or range that has relevant data. These three are your bestfriends. By learning these three by heart, you can manipulate a lot in Excel VBA.

在 (2) 之后，开始学习如何标注变量和/或对象的尺寸.撇开行话不谈，这基本上类似于给您正在处理的每件重要事情都取昵称".假设您正在处理 3 张纸.您不想继续引用 ThisWorkbook.Sheets("Sheet1") 等等.您更愿意使用 Sh1 或 Sh2 来代替.

After (2), start learning how to dimension variables and/or objects. Setting the jargon aside, this is basically just akin to giving each important thing you're working on "nicknames". Say you're working on 3 sheets. You don't want to keep on referring to ThisWorkbook.Sheets("Sheet1") and so on and on. You'd much rather want Sh1 or Sh2 instead.

了解如何使用 Union、With 等将类似的过程组合在一起.这与上面的 (1) 密切相关.稍后您将看到一个示例.

Learn how to bunch similar procedures together using Union, With, or the like. This goes hand in hand with (1) above. You'll see an example later on this.

Application.ScreenUpdating - Excel VBA 中最好的节省时间的技巧之一.

Application.ScreenUpdating - one of the best time-shaving tricks in Excel VBA.

现在，举几个例子:

(1) 避免 .Select ||学习使用非常好的 .Copy one-liner

(1) Avoiding .Select || Learning to use the very nice .Copy one-liner

这部分...

Range("A1").Select
Selection.Copy
Columns("F:F").Select
ActiveSheet.Paste

...可以简化为:

Range("A1").Copy Range("F:F")

从四行到只有一行.它更具可读性.上面的第二个代码片段基本上是将 A1 的值复制到整个 F 列".请注意，这实际上非常占用内存，就像在 Excel 2010 中一样，您实际上是使用该命令粘贴到一百万甚至更多行.最好是具体的，比如Range("F1:F1000").

From four lines to just one. And it's much more readable. The second code snippet aboves basically reads, "Copy A1's value to the whole F column". Note that that's actually quite memory intensive, as in Excel 2010, you're actually pasting to a million and more rows with that command. Better be specific, like Range("F1:F1000").

(2) 将命令组合在一起

在书面"VBA 中将命令组合在一起与您在宏中执行的方式不同.由于录制了宏，因此一切都基于实时修改.在书面"VBA 中，您可以指定一个操作，该操作将允许您对多个对象应用单个操作.例如，假设您要删除 A 列和 C 列，同时将所有相关数据向左移动.

Bunching commands together in "written" VBA is different from the way you do it in macros. Since macros are recorded, everything is based on real time modifications. In "written" VBA, you can specify an action that will allow you to apply a single action on multiple objects. Let's say for example you want to delete Columns A and C while shifting all relevant data to the left.

录制宏来执行此操作时，您可以同时选择 A 和 C 并删除它们.然而，大多数初学者采取安全的方式并记录删除列一次，这虽然安全但非常违反直觉.在删除之前选择两者是最好的选择.

When recording a macro to do this, you can select both A and C and delete them at the same time. However, most beginners take the safe path and record the deletion of columns one at a time which--while safe--is highly counterintuitive. Selecting both before deleting is the best option.

在书面 VBA 中，上面的第二种方法是一个巨大的禁忌(或者至少，它不是常态).除非有特定和必要的原因，否则将类似的命令集中在一起是惯例，因为它既可以在很大程度上消除错误又不占用资源.

In written VBA, the second method above is a massive no-no (or at least, it's not the norm). Unless there's a specific and necessary reason, bunching similar commands together is the convention as it both eliminates error to a large degree and is not resource intensive.

在您的代码中...

Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("G:S").Select
Selection.Delete Shift:=xlToLeft

...读起来很痛苦.我们不知道为什么那里有两次删除，我们不确定 S 列中的数据最初在哪里，等等.在这种情况下，提前确定要删除的范围并执行删除是完美的方式.

... is such a pain to read. We don't know why there are two deletions there, we don't know for sure where the data in Column S originally was, etc, etc. In instances like this, determining ahead of time the ranges you want to delete and executing the deletion is the perfect way.

例如，假设您想删除 A、C、E 和 F 到 O 列.像下面这样的简洁方法将非常快速有效地完成此操作.

Let's assume for example's sake that you want to delete the columns A, C, E, and F to O. A neat approach like follows will pull this off quite quickly and efficiently.

Union(Range("A:A"),Range("C:C"),Range("E:E"),Range("F:O")).Delete

Union 是您早期最好的朋友之一.与数学中的集合表示法一样，您指定的范围被放在一起成为一组范围并同时被操作(在这种情况下，.Deleted 同时).由于默认的移位是向左移动，我们可以完全删除 Shift:=xlToLeft 行(另一个漂亮的 VBA 事实).

Union is one of your early bestfriends. As with set notation in math, ranges you specify are put together into a set of ranges together and are actioned upon at the same time (in this case, .Deleted at the same time). Since the default shift is to the left, we can altogether remove the Shift:=xlToLeft line (another nifty VBA fact).

(3) With - 一件事你离不开

(3) With - one thing you cannot live without

此时，您可能会想，在这些范围内执行多个操作怎么样?我们只在多个范围内执行了单个操作，而不是相反.这就是 With 出现的地方.在这种情况下，With 将仅用于 Ranges 但它几乎可以用于任何VBA.对象、范围、外部应用程序等.我不会深入研究这一点，但可以说使用 With 就像在你想用几个过程处理的事情上使用锚点一样.

At this point, you might be thinking, what about multiple actions on these ranges? We've only done single actions on multiple ranges and not the other way around. This is the point where With comes in. In this context, With will be used only on Ranges but it can be used on almost anything in VBA. Objects, ranges, external applications, etc. I will not delve on this, but suffice to say that using With is like using an anchor on something you want to work on with a few procedures.

在您的代码中，我们发现...

In your code, we find...

Columns("C:C").Select
Selection.ColumnWidth = 8.29
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
Rows("1:1").Select
Selection.RowHeight = 30
With Selection
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

... 可以简化为:

With Columns("C:C")
    .ColumnWidth = 8.29
    .HorizontalAlignment = xlCenter
End With
With Rows(1:1)
    .RowHeight = 30
    .WrapText = True
End With

基本上，我们在这里做了两件事.首先，我们锚定在 C 列上，并对其进行了两个操作:设置列宽，然后是水平对齐方式.在锚定到 C 列并对其进行修改后，我们将锚点更改为整个第 1 行并修改其高度并将其设置为将文本换行到单元格宽度.从 24 行，我们将该宏块减少到仅 8 行.为简洁起见如何?:)

Basically, we did two things here. First, we anchored on Column C, and did two actions on it: set the column width, then the horizontal alignment. After anchoring to Column C and modifying it, we change the anchor to the whole of row 1 and we modify its height and set it to wrap text to cell width. From 24 lines, we've reduced that macro block to just 8 lines. How's that for brevity? :)

为什么我制作时没有其他线条?与前面的示例 (Union) 一样，我们可以处理一些无论如何都是默认值或未修改的行.这些都会有例外，但它们会很少，而且现在有点偏离你的水平.你会到达那里.

Why did I make without the other lines? As in the previous example (Union), we can make do with some lines that are either the default value anyway or are not modified. There will be exceptions to these, but they will be far and few and are a bit off your level for now. You'll get there.

(4) 创建/修改工作表并避免.Activate，以及对尺寸的触摸

(4) Creating/modifying sheets and avoiding .Activate, and a touch on dimensions

VBA 初学者的陷阱之一是他们经常使用 ActiveWorkbook、ActiveSheet 和 .Activate.这本身并不坏，但也不好.使用起来很方便，但是如果你把它合并到非常复杂的子程序和函数中，就会引起无数的头痛.

One of the pitfalls of beginners in VBA is that they use ActiveWorkbook, ActiveSheet, and .Activate a lot. This is not bad per se, but it's not good either. It's convenient to use, but it will cause a myriad of headaches if you incorporate it in really complex subroutines and functions.

为了解决这个问题，我们首先考虑了对您的对象进行尺寸标注或限定的想法.这是通过首先声明关键字然后声明数据类型来完成的.我不会进一步深入研究，因为有很多 VBA 教程可供您阅读，所以我只指出一些重要的.

To combat this, we first go into the idea of dimensioning or qualifying your objects. This is done by first declaring a keyword and then a data type. I will not delve into this further, as there are lots of VBA tutorials you can read for this, so I'll just point out some important ones.

假设您正在处理两个打开工作簿.我们可以为他们中的每一个创建一个昵称"，这样您就可以引用它们而无需输入整行引用.

Let's say you're working on two open workbooks. We can create a "nickname" for each of them so you can refer to them without having to type whole lines of reference.

Dim SourceWbk As Workbook
Dim TargetWbk As Workbook

上面的两行读作，SourceWbk/TargetWbk 是我的昵称，我的尺寸是一本工作簿，所以我期待被提及工作簿".既然我们已经为它们创建了维度，我们可以将它们指向它们将代表什么.

The two lines above read as, "SourceWbk/TargetWbk is my nickname and I am dimensioned as a workbook, so I'll be expecting to be referred to a workbook". Now that we've created dimensions for them, we can point them to what they will stand for.

Set SourceWbk = ThisWorkbook
Set TargetWbk = Workbooks("I AM THE MASTER REPORT")

注意这里的=".现在，我们基本上已经声明，从现在开始，SourceWbk 将引用包含此代码的工作簿，TargetWbk 将引用打开名为我是主报告"的工作簿.现在让我们看看将工作表从 SourceWbk 复制到 TargetWbk 的简单操作.

Note the "=" here. Now, we've basically declared that from now on, SourceWbk is going to refer to the workbook containing this code, and TargetWbk is going to refer to the open workbook named "I AM THE MASTER REPORT". Now let's look at the simple act of copying a sheet from SourceWbk to TargetWbk.

SourceWbk.Sheets("Sheet1").Copy After:=TargetWbk.Sheets("Sheet1")

看起来很眼熟?那是因为这与这段记录的代码块几乎相同:

Looks familiar? That's because this is pretty much the same as this recorded block of your code:

Sheets("Fresh Agent Leads").Select
Sheets("Fresh Agent Leads").Copy After:=Workbooks( _
    "MSS Call Routing Master List.xlsx").Sheets(1)

现在，您可以更进一步，为工作表命名，然后复制它们.示例如下:

Now, you can go one step further and name the sheets themselves, then copy them. Example follows:

Dim FAL As Worksheet 'Expects a worksheet.
Dim LastSheet As Worksheet

Set FAL = SourceWbk.Sheets("Fresh Agent Leads")
Set LastSheet = TargetWbk.Sheets("Sheet1") 'You can use a number index or specific name

FAL.Copy After:=LastSheet

在这一点上，代码已经非常、非常简短和甜蜜了.没有麻烦，您真正需要做的唯一努力就是记住昵称"指的是什么.请注意，您应该不要将某些特定词用作变量名.尽可能使其个性化但合理.简单地将一张工作表命名为 Sh 是好的，但它会让你在一个包含 100 张不同用途的工作表的文件中无处可去.

At this point the code is very, very short and sweet already. No hassles, and the only effort you actually need is to remember what the "nicknames" refer to. Take note that there are specific words you should NOT be using as variable names. As much as possible, make it personalized but reasonable. Simply naming a sheet as Sh is good but it gets you nowhere in a file with 100 sheets each with different purposes.

(5) Application 技巧手册

(5) The Application Trickbook

在 Excel VBA 中，您可以通过一些操作来提高代码效率.毕竟说了又做了，宏只是一个重复的动作.运行记录的或书面的两者都会带您完成这些操作..Select 将选择特定范围，您会看到它们被选中..Activate 或多或少会做同样的事情..Copy 将向您展示那些蚂蚁"以及它们留下的亮点.所有这些都会导致代码的视觉执行时间更长，而且通常很草率.下面是 ScreenUpdating 技巧"的步骤.

In Excel VBA, there are a few things you can manipulate to increase the efficiency of your code. After all is said and done, a macro is just a repeated action. Running a recorded or a written one both will take you through the actions. .Select will select specific ranges and you'll see them get selected. .Activate will do the same, more or less. .Copy will show you those "ants" and the highlights they leave behind. All these contribute to a longer and often sloppy visual execution of the code. Herein steps the ScreenUpdating "trick".

请注意，这不是真正的技巧.大多数人认为它们是代码中非常重要的部分，但将它们包含在外行"VBA 模块中仍然很有帮助.最佳实践之一是在子例程的开头设置 Application.ScreenUpdating = False，然后在最后将其设置回 True.

Mind you, it's not really a trick. Most people consider them highly important parts of their code, but their inclusion into "layman" VBA modules are nonetheless helpful. One of the best practices is setting Application.ScreenUpdating = False at the beginning of a subroutine then setting it back to True at the end.

ScreenUpdating 将冻结"您的屏幕，让一切都在您看不见的情况下发生.您不会看到项目被复制或范围被选择.您不会看到已关闭的工作簿被打开和关闭.虽然这只会在您调用 Excel 时影响它，但它仍然非常宝贵.

ScreenUpdating will "freeze" your screen, making everything happen without you seeing them. You won't see items getting copied or ranges getting selected. You won't see closed workbooks being opened and closed. While this only affects Excel when you call it, it's nonetheless invaluable.

Application 技巧的快速而肮脏的列表(不要将其用作绝对参考！):

A quick and dirty list (do not use this as an absolute reference!) of Application tricks:

• .ScreenUpdating (False/True):消除False时Excel的视觉更新.复制粘贴或删除行时绝对需要.
• .Calculation (xlCalculationAutomatic/xlCalculationSemiautomatic/xlCalculationManual):类似于Formulas >计算选项 功能区，将此设置为手动将暂停所有计算.强烈推荐，尤其是当您更新依赖于 VLOOKUP 或 INDEX 公式的范围时.
• .EnableEvents(False/True):禁用触发基于事件的程序.有点高级，但足以说明，如果您在基于事件的更改上有一些自动宏触发，这将暂停它们以支持当前正在运行的宏.

• .ScreenUpdating (False/True): Eliminates the visual updating of Excel when False. Absolutely necessary when copy-pasting or deleting rows.
• .Calculation (xlCalculationAutomatic/xlCalculationSemiautomatic/xlCalculationManual): Similar to the Formulas > Calculation Options ribbon function, setting this to Manual will suspend all calculations. Highly recommended especially when you're updating ranges that are depended on by loads of VLOOKUP or INDEX formulas.
• .EnableEvents(False/True): Disables triggering event based procedures. A bit advanced, but suffice to say that if you've got some automatic macro triggering on event-based changes, this will suspend them in favor of the current running macro.

还有很多其他的，学习其中的大部分对您最有利.;)

There are loads of others and it will be to your best interest to learn most of them. ;)

大结局

这是从您录制的宏中提取的示例代码，它使用了上述所有技术并考虑了您在宏上执行的过程.这不是你的全部代码.阅读这个，测试这个，修改这个，你会在一天之内进步很多.

Here's a sample code taken from your recorded macro that uses all the above techniques and takes into consideration the procedures you executed on your macro. This is not your whole code. Read this, test this, modify this and you'll improve a lot in a day.

Sub RefinedCode()

    Dim SourceWbk As Workbook, TargetWbk As Workbook
    Dim FALSht As Worksheet, FALSht2 As Worksheet, MasterSht As Worksheet

    Application.ScreenUpdating = False 'We won't see the copy-paste and column deletion happening but they will happen. 

    Set SourceWbk = ThisWorkbook
    Set TargetWbk = Workbooks("MSS Call Routing Master List")
    Set FALSht = SourceWbk.Sheets("Fresh Agent Leads")

    With TargetWbk          
        Set MasterSht = .Sheets("Master") 'Basically reads as Set MasterSht = TargetWbk.Sheets("Master")
        FAL.Copy After:= .Sheets(1)
        Set FALSht2 = .Sheets("Fresh Agent Leads")
    End With

    With FALSht2
        Union(.Range("A:A"),.Range("C:C"),.Range("E:O")).Delete
        With .Rows(1)
            .RowHeight = 30
            .WrapText = True
        End With
        .Range("A1").Copy .Range("F1:F100")
    End With

    MasterSht.Name = "Call Router"
    TargetWbk.Save
    SourceWbk.Close

    Application.ScreenUpdating = True 'Return to default setting.

End Sub

希望这会有所帮助.

这篇关于我可以使这个宏更有效或更快速吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！