我可以让这个宏更有效率还是更快？ [英] Can I make this macro more efficient or faster?

问题描述

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

我开发了代码，以更新我公司的呼叫路由器工作表，并从外部来源购买新的线索。线索以原始格式发送到我们的名为Fresh Agents Leads的工作表中。一旦将新鲜代理引线表复制到容纳呼叫路由器工作表的MSS呼叫路由主列表文件中，宏将减少原始数据，从而消除不使用的部分。然后重新格式化与旧呼叫路由器工作表格式相匹配的内容，并将其合并。然后将新的主页面重命名为呼叫路由器。

该代码旨在在包含新鲜代理引导页的工作簿中开始。在执行代码之前，要求用户同时在桌面上同时启动新鲜代理引导文件和MSS呼叫路由主列表。

 表格（新鲜代理线索）选择
表格（新鲜代理线索）。 =工作簿（_ 
MSS Call Routing Master List.xlsx）。表（1）
列（F：F）。选择
 Selection.Insert Shift：= xlToRight，CopyOrigin ：= xlFormatFromLeftOrAbove 
范围（A1）。选择
 Selection.Copy 
列（F：F）。选择
 ActiveSheet.Paste 
列（ A：A）。选择
 Application.CutCopyMode = False 
 Selection.Delete Shift：= xlToLeft 
列（C：C）。选择
 Selection.Delete Shift： = xlToLeft 
列（E：E）。选择
 Selection.Delete Shift：= xlToLeft 
 Selection.Delete Shift：= xlToLeft 
列（G：S） 。选择
 Selection.Delete Shift：= xlToLeft 
行（1：1）。选择
 Selection.Delete Shift：= xlUp 
列（C：C） 。选择
 Selection.Insert Shift：= xlToRight，CopyOrigin：= xlFormatFromLeftOrAbove 
 Range（C1）。选择
 A ctiveCell.FormulaR1C1 == CONCATENATE（RIGHT（RC [1]，4））
 Range（C1）。选择
 Selection.AutoFill目的地：=范围（C1：C1048575）
范围（C1：C1048575）。选择
 
表格（呼叫路由器）选择
行（1：1）选择
选择。选择
 Rows（1：1）选择
 Selection.Insert Shift：= xlDown 
 Application.CutCopyMode = False 
 Application.RunPERSONAL.xlsb！MergeIdenticalWorksheets
列（C：C）。选择
 Selection.NumberFormat =0000
范围（A：A，B ：B，F：F）选择
范围（F1）。激活
 Selection.ColumnWidth = 14 
列（E：E）选择
选择.ColumnWidth = 25 
列（C：C）。选择
 Selection.ColumnWidth = 8.29 
带选择
 .Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlBottom 
 .WrapText = False 
 .Orientation = 0 
 .AddIn dent = False 
 .IndentLevel = 0 
 .ShrinkToFit = False 
 .ReadingOrder = xlContext 
 .MergeCells = False 
结束
行（1： 1）。选择
 Selection.RowHeight = 30 
带有选择
 .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 
结束
列（D：D）。选择
 Selection.EntireColumn.Hidden = True 
范围（E2 ）。选择
 ActiveW indow.FreezePanes = True 
 Sheets（Array（Call Router，Fresh Agent Leads））。选择
 Sheets（Call Router）。激活
 ActiveWindow.SelectedSheets.Delete 
表格（Master）选择
表格（Master）Name =Call Router
范围（C23）选择
 ActiveSheet.Protect DrawingObjects = True，Contents：= True，Scenarios：= True 
 ActiveWorkbook.Save 
 End Sub 
  

解决方案

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

===

一些提示：

1. 避免。尽可能选择和。激活。录制宏是VBA的一个很好的开始，但第一大步是离开这些属性提供的舒适区。一开始，他们是好的，但是长期来看它们一定会产生问题。




2. 阅读以下基本程序：复制/粘贴/插入范围，创建/删除工作表以及确定具有相关数据的工作表或范围的最后一行/列。这三个是你的最好的朋友。通过学习这三个内容，您可以在Excel VBA中操作很多。




3. 在（2）之后，开始学习如何维度变量和/或对象。将行话放在一边，这基本上只是类似于给你每个重要的事情，你在昵称。说你正在做3张纸。您不想继续参考 ThisWorkbook.Sheets（Sheet1）等等。你更希望 Sh1 或 Sh2 。




4. 了解如何使用 Union ，与等一起使用类似的过程。这与（1）以上是一致的。您稍后会看到一个例子。




5. Application.ScreenUpdating - 最好的时间之一在Excel VBA中的技巧。

现在，几个示例：

（1）避免。选择 ||学习使用非常好的 .Copy one-liner

这部分...

 范围（A1）。选择
 Selection.Copy 
列（F：F）。选择
 ActiveSheet.Paste 
  

...可以缩减为：

$复制范围（F：F）

从四行到只一个。而且它的可读性更好。第二个代码片段基本上读取将A1的值复制到整个F列。请注意，这实际上是相当的内存密集型，如在Excel 2010中，实际上您使用该命令粘贴到一百万行。更好地具体，比如 Range（F1：F1000）。

（2）一起

在写入VBA中一起处理命令与您在宏中执行的方式不同。由于宏被记录，所有的都是基于实时修改。在写入VBA中，您可以指定一个操作，允许您在多个对象上应用单个操作。例如，您要删除列A和C，同时将所有相关数据向左移动。

当记录宏执行此操作时，您可以选择A和并同时删除它们。然而，大多数初学者采取安全的路径，并记录删除一个一个 ，而安全的 - 高度违反直觉。在删除之前选择两者是最好的选择。

在VBA中，上面的第二个方法是一个非常大的no-no（或至少不是规范）。除非有特定和必要的原因，否则将类似的命令集中在一起是惯例，因为它在很大程度上消除了错误，而不是耗费资源。

在您的代码中。

  Selection.Delete Shift：= xlToLeft 
列（C：C）。选择
 Selection.Delete Shift：= xlToLeft 
列（E：E）。选择
 Selection.Delete Shift：= xlToLeft 
 Selection.Delete Shift：= xlToLeft 
列（ G：S）。选择
 Selection.Delete Shift：= xlToLeft 
  

...是这么痛苦的阅读。我们不知道为什么有两个删除，我们不知道列S中的数据原来是等等。在这种情况下，提前确定要删除和执行的范围删除是完美的方式。

我们假设您想要将列A，C，E和F列删除为O。联合（范围（A：A），范围（C：C），可以快速高效地将其关闭。

  ），范围（E：E），范围（F：O））删除
  

联盟是您早期的最佳朋友之一。与数学中的设置符号一样，您指定的范围将一起放在一组范围内，并同时处理（在这种情况下， .Delete d在同时）。由于默认的转移是在左边，所以我们可以删除 Shift：= xlToLeft 行（另一个漂亮的VBA事实）。

（3）有 - 有一件事你不能没有

在这一点上，您可能会想，这些范围的多个动作呢？我们只对多个范围进行单独操作，而不是相反。这是带有的点。在这种情况下，与将仅在范围，但它几乎可以用于VBA中的任何内容。对象，范围，外部应用程序等。我不会深入了解这一点，但足以说使用与一样，就像在一个你想要处理的东西上使用一个锚点很少的程序。

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

 列（C：C）。选择
 Selection.ColumnWidth = 8.29 
选择
 .Horizo​​ntalAlignment = xlCenter 
 .VerticalAlignment = xlBottom 
 .WrapText = False 
 .Orientation = 0 
 .AddIndent = False 
 .IndentLevel = 0 
 .ShrinkToFit = False 
 .ReadingOrder = xlContext 
 .MergeCells = False 
结束
行（1：1）。选择
 Selection.RowHeight = 30 
带有选择
 .VerticalAlignment = xlBottom 
 .WrapText = True 
 .Orientation = 0 
 .AddIndent = False 
 .ShrinkToFit = False 
 .ReadingOrder = xlContext 
 .MergeCells = False 
结束
  

...可以减少到：

 带列（C：C）
 .ColumnWidth = 8.29 
 .Horizo​​ntalAlignment = xlCenter 
结束
带行（1：1）
 .RowHeight = 30 
 .WrapText = True 
结束
  

基本上，我们在这里做了两件事情。首先，我们锚定在C列，并对其进行了两个操作：设置列宽，然后设置水平对齐。锚定到列C并进行修改后，我们将锚点更改为整行1，并修改其高度并将其设置为将文本包围到单元格宽度。从24行开始，我们将该宏块减少到只有8行。为了简洁，怎么样？ ：）

为什么没有其他行？如前面的例子（ Union ），我们可以使用一些默认值或不修改的行。这些都会有例外，但现在很少，几乎没有什么水平。你会到达那里。

（4）创建/修改工作表，避免 .Activate 触摸尺寸

VBA初学者的一个缺陷是使用 ActiveWorkbook ActiveSheet 和 .Activate 很多。这本身不坏，但也不好。使用起来很方便，但如果将它并入到非常复杂的子程序和功能中，会引起无数头痛。

为了打击这一点，我们首先介绍一下对您的对象进行尺寸标注或限定。这是通过首先声明一个关键字然后一个数据类型来完成的。我不会进一步深入，因为有很多VBA教程可以读取，所以我只是指出一些重要的。

让我们说你在两个开放工作簿上进行工作。我们可以为每个人创建一个昵称，以便您可以引用它们，而无需键入整个参考线。

  Dim SourceWbk As Workbook 
 Dim TargetWbk As Workbook 
  

上面两行读作 code> SourceWbk / TargetWbk 是我的昵称，我的大小是作为一个工作簿，所以我期待被称为工作簿。现在我们为他们创建了维度，我们可以指出他们将要支持什么。

  Set SourceWbk = ThisWorkbook 
设置TargetWbk =工作簿（我是主要报告）
  

请注意=这里。现在，我们基本上宣称，从现在开始， SourceWbk 将会引用包含此代码的工作簿，而 TargetWbk 将参考名为我是主要报告的 打开 工作簿。现在，我们来看一下从 SourceWbk 复制表格到 TargetWbk 的简单行为。

  SourceWbk.Sheets（Sheet1）。复制之后：= TargetWbk.Sheets（Sheet1）
  pre> 
 
 
看起来很熟悉？这是因为这与您的代码记录块几乎相同：
 表格（新鲜代理潜在客户）选择
表（新鲜代理线索）复制之后：=工作簿（_ 
MSS呼叫路由主列表xlsx）。表（1）
  
现在，您可以进一步，自己命名这些工作表，然后复制它们。示例如下：
  Dim FAL As Worksheet'期望工作表。 
 Dim LastSheet As Worksheet 
 
 Set FAL = SourceWbk.Sheets（Fresh Agent Leads）
设置LastSheet = TargetWbk.Sheets（Sheet1）'您可以使用一个数字索引或具体名称
 
 FAL.Copy之后：= LastSheet 
  
在此点的代码非常，非常短和甜蜜已经。没有麻烦，你实际需要的唯一的努力是记住昵称是指什么。请注意，您应该使用具体的字词不使用变量名称。尽可能地使其个性化但合理。简单地将一张表命名为 Sh 是好的，但是它在一个文件中没有任何地方，每个页面都有不同的目的。
 
 
 
 （5）应用程序 Trickbook  
 
 
 
在Excel VBA中，几个可以操作的东西可以提高代码的效率。毕竟说完了，一个宏只是一个反复的行动。运行一个记录或一个书面的两个将带你通过的行动。 。选择将选择特定的范围，您将看到它们被选中。  .Activate 将会做或多或少的相同。  .Copy 将向您展示这些蚂蚁及其留下的亮点。所有这些都有助于更长时间和经常恶意的视觉执行代码。在这里步骤 ScreenUpdating 伎俩。
 
 
 
注意，这不是一个伎俩。大多数人认为他们的代码是非常重要的部分，但是他们被纳入外行VBA模块仍然是有帮助的。最好的做法之一是在子例程的开头设置 Application.ScreenUpdating = False ，然后将其设置回 True  
 
 
 
  ScreenUpdating 将冻结您的屏幕，使所有事情都没有看到它们。您不会看到被复制的项目或范围被选中。您不会看到已关闭的工作簿正在打开和关闭。虽然这只是在你打电话时影响Excel，但仍然是非常宝贵的。
 
 
 
一个快速而肮脏的列表（不要将此作为绝对参考！）应用程序技巧：
 
 
 
 
 
 .ScreenUpdating（False /  True ）：消除 False 时可视化更新Excel。绝对需要复制粘贴或删除行。
 
 
 .Calculation（ xlCalculationAutomatic  / xlCalculationSemia自动/ xlCalculationManual）：类似于公式>计算选项功能区功能，将其设置为手动将暂停所有计算。特别是当您更新由 VLOOKUP 或 INDEX 公式加载的范围更新的范围时。
 
 
 .EnableEvents（False /  True ）：禁用触发事件的过程。有点高级，但足以说如果您在基于事件的更改中有一些自动宏触发，则会暂停它们，以支持当前运行的宏。
 
 
 
 
 
有很多其他人，最有趣的是学习他们的大部分。 ;）
 
 
 
   THE BIG FINALE   
 
 
 
从您记录的宏中获取的示例代码，它使用所有上述技术，并考虑到您在宏上执行的过程。  这不是你的整个代码。 阅读这篇文章，测试一下，修改这个，你一天会改善很多。
  Sub RefinedCode（）
 
 Dim SourceWbk As Workbook，TargetWbk As Workbook 
 Dim FALSht As Worksheet，FALSht2 As Worksheet，MasterSht As Worksheet 
 
 Application.ScreenUpdating = False'我们不会看到复制粘贴和列删除发生，但会发生。 
 
设置SourceWbk = ThisWorkbook 
设置TargetWbk =工作簿（MSS呼叫路由主列表）
设置FALSht = SourceWbk.Sheets（新鲜代理引导）
 
使用TargetWbk 
设置MasterSht = .Sheets（Master）'基本上读为Set MasterSht = TargetWbk.Sheets（Master）
 FAL.Copy After：= .Sheets（1） 
设置FALSht2 = .Sheets（Fresh Agent Leads）
结束
 
与FALSht2 
联合（.Range（A：A），。范围（C：C），。范围（E：O））删除
用.Rows（1）
 .RowHeight = 30 
 .WrapText = True 
结束
 .Range（A1）。复制.Range（F1：F100）
结束
 
 MasterSht.Name =呼叫路由器
 TargetWbk.Save 
 SourceWbk.Close 
 
 Application.ScreenUpdating = True返回默认设置。 
 
 End Sub 
  
希望这有帮助。
 
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. 

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. 

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

解决方案
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.

===

A few tips:

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.
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.
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.
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 - one of the best time-shaving tricks in Excel VBA.
Now, a few samples:

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

This part...
Range("A1").Select
Selection.Copy
Columns("F:F").Select
ActiveSheet.Paste
...can be reduced to:
Range("A1").Copy Range("F:F")
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) Bunching commands together

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.

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.

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.

In your code...
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
... 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.

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 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 - one thing you cannot live without

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
... which can be reduced to:
With Columns("C:C")
    .ColumnWidth = 8.29
    .HorizontalAlignment = xlCenter
End With
With Rows(1:1)
    .RowHeight = 30
    .WrapText = True
End With
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? :)

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) Creating/modifying sheets and avoiding .Activate, and a touch on dimensions

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.

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
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")
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
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) The Application Trickbook

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

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

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


.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. ;)

THE BIG FINALE

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
Hope this helps.

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