我是编码的新手。这个宏运行缓慢,我希望有人可以帮助我清理它。在此先感谢您的帮助。
我开发了代码,以使用从外部来源购买的新线索来更新公司的“呼叫路由器”工作表。潜在客户会以原始格式在名为Fresh Agents Leads的工作表中找到我们。将“新代理线索”工作表复制到包含“呼叫路由器”工作表的“ MSS呼叫路由主列表”文件后,该宏会减少原始数据,从而消除了我们不使用的零件。然后,它重新格式化剩余的内容以匹配旧呼叫路由器工作表的格式,并将两者合并。然后,它将新的主表重命名为“呼叫路由器”。
该代码旨在从包含“新鲜代理线索表”的工作簿中开始。在执行代码之前,指示用户在桌面上同时打开“新鲜代理线索”文件和“ MSS呼叫路由主列表”。
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,但是由于我有很多时间,我还是决定至少发布一些有关您的代码的内容。
===
一些提示:
避免.Select
并.Activate
尽可能。录制宏是VBA的良好开端,但第一步是离开这些属性提供的“舒适区”。从一开始,它们就很好,但是从长远来看,它们必然会产生问题。
阅读以下“基本”过程:复制/粘贴/插入范围,创建/删除工作表以及确定具有相关数据的工作表或范围的最后一行/列。这三个是你最好的朋友。通过认真学习这三个方面,您可以在Excel VBA中进行很多操作。
在(2)之后,开始学习如何标注变量和/或对象的尺寸。撇开行话,这基本上类似于给您处理“昵称”的每个重要事项。假设您正在处理3张纸。您不想继续引用ThisWorkbook.Sheets("Sheet1")
等等。您宁愿想要Sh1
还是Sh2
取而代之。
了解如何一堆类似的程序一起使用Union
,With
或等。这与上面的(1)齐头并进。稍后您将看到一个示例。
Application.ScreenUpdating
-Excel VBA中最好的节省时间的技巧之一。
现在,一些示例:
(1)避免.Select
|| 学习使用非常漂亮.Copy
的单线
这部分...
Range("A1").Select
Selection.Copy
Columns("F:F").Select
ActiveSheet.Paste
...可以简化为:
Range("A1").Copy Range("F:F")
从四行到一行。而且更具可读性。上面的第二个代码片段基本上读取为“将A1的值复制到整个F列”。请注意,这实际上是占用大量内存的,就像在Excel 2010中,您实际上是使用该命令粘贴到一百万或更多行。最好像一样具体Range("F1:F1000")
。
(2)将命令捆绑在一起
在“书面” VBA中将命令绑定在一起与在宏中执行命令的方式不同。由于记录了宏,因此所有内容都基于实时修改。在“书面” VBA中,您可以指定一个动作,该动作将允许您对多个对象应用单个动作。假设您要删除列A和C,同时将所有相关数据向左移动。
录制宏时,您可以同时选择A和C并将它们删除。但是,大多数初学者都采取了安全的方法,并记录了一次删除列的过程,尽管这是安全的,但非常违反直觉。最好在删除之前同时选择两者。
在书面VBA中,上面的第二种方法是大规模的否定(或者至少不是规范)。除非有特定且必要的原因,否则将类似的命令捆绑在一起是惯例,因为这样既可以在很大程度上消除错误,又不会占用大量资源。
在您的代码中...
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列中的数据原来在哪里,等等。在这种情况下,请提前确定要删除的范围并执行删除是完美的方法。
例如,假设您要删除A,C,E和F列到O列。像下面这样的简洁方法可以非常快速有效地完成此操作。
Union(Range("A:A"),Range("C:C"),Range("E:E"),Range("F:O")).Delete
Union
是您早期的最好的朋友之一。与数学中的设置符号一样,您指定的范围将放在一起成为一组范围,并且将同时作用(在这种情况下,.Delete
是同时作用d)。由于默认移位位于左侧,因此我们可以完全删除该Shift:=xlToLeft
行(另一个漂亮的VBA事实)。
(3)With
-一件你不能没有的生活
在这一点上,您可能会想,在这些范围内执行多个操作会如何?我们只对多个范围执行了一次操作,而没有相反的方法。这就是问题所在With
。在这种情况下,With
它将仅Ranges
用于VBA ,但几乎可以用于任何VBA。对象,范围,外部应用程序等。我不会对此进行深入研究,但足以说出使用With
就像在要通过一些过程进行处理的对象上使用锚点一样。
在您的代码中,我们发现...
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行。为了简洁起见?:)
为什么我没有其他内容就做?与前面的示例(Union
)一样,我们可以处理一些行,这些行无论如何都是默认值,或者没有被修改。这些会有一些例外,但是它们将会越来越少,并且暂时偏离您的水平。你会到达那里。
(4)创建/修改图纸并避免.Activate
和尺寸接触
一个在VBA初学者的陷阱是,他们使用ActiveWorkbook
,ActiveSheet
和.Activate
不少。这本身并不坏,但这也不是一件好事。它使用起来很方便,但是如果将其合并到非常复杂的子例程和函数中,则会引起许多麻烦。
为了解决这个问题,我们首先考虑为您的对象设定尺寸或进行限定的想法。首先声明一个关键字,然后声明一个数据类型。我不会进一步研究这个问题,因为您可以阅读很多VBA教程,因此,我仅指出一些重要的教程。
假设您正在处理两个打开的工作簿。我们可以为它们每个创建一个“昵称”,这样您就可以引用它们,而不必键入完整的引用行。
Dim SourceWbk As Workbook
Dim TargetWbk As Workbook
上面两行的内容为:“ SourceWbk
/TargetWbk
是我的昵称,我的名称是工作簿,因此希望可以将其引用为工作簿”。现在我们已经为它们创建了尺寸,我们可以将它们指向它们所代表的意义。
Set SourceWbk = ThisWorkbook
Set TargetWbk = Workbooks("I AM THE MASTER REPORT")
注意“ =”在这里。现在,我们基本上已经声明,从现在开始,SourceWbk
将引用包含此代码的工作簿,并将TargetWbk
引用名为“ I AM THE MASTER REPORT”的打开的工作簿。现在,让我们来看看从复制片的简单的动作SourceWbk
来TargetWbk
。
SourceWbk.Sheets("Sheet1").Copy After:=TargetWbk.Sheets("Sheet1")
看起来很熟悉?这是因为这与您记录的代码块几乎相同:
Sheets("Fresh Agent Leads").Select
Sheets("Fresh Agent Leads").Copy After:=Workbooks( _
"MSS Call Routing Master List.xlsx").Sheets(1)
现在,您可以更进一步,自己命名工作表,然后进行复制。示例如下:
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张工作目的不同的文件的文件中,您将一无所获。
(5)Application
技巧手册
在Excel VBA中,可以进行一些操作以提高代码效率。说到底,宏就是重复的动作。既执行录制的任务又执行书面的任务,将带您完成所有操作。.Select
将选择特定范围,您将看到它们被选中。.Activate
或多或少都会做同样的事情。.Copy
将向您显示这些“蚂蚁”及其留下的亮点。所有这些使代码的可视化执行时间更长,而且通常草率。在此踩ScreenUpdating
“技巧”。
提醒您,这不是真正的把戏。大多数人认为它们是代码中非常重要的部分,但是将它们包含在“外行” VBA模块中仍然很有帮助。最佳实践之一是Application.ScreenUpdating = False
在子例程的开始处设置,然后True
在结束时将其重新设置。
ScreenUpdating
将“冻结”您的屏幕,使所有事情都发生而您却看不到它们。您不会看到项目被复制或范围被选择。您不会看到打开和关闭的已关闭工作簿。虽然这只会在您调用Excel时对其产生影响,但它是无价的。
快速而肮脏的列表(不要将其用作绝对参考!)的Application
技巧:
False
。复制粘贴或删除行时绝对必要。Formulas > Calculation Options
功能区功能类似,将此功能设置为“手动”将暂停所有计算。强烈建议使用此功能,尤其是在更新由VLOOKUP
或INDEX
公式的加载量决定的范围时。还有很多其他的东西,学习其中的大多数将是您的最大利益。;)
大决赛
这是从录制的宏中获取的示例代码,该代码使用上述所有技术,并考虑了在宏上执行的过程。这不是您的全部代码。阅读此书,对其进行测试,对其进行修改,您一天之内就会有所改善。
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
希望这可以帮助。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句