如何加速 VBA 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3499878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to Speed Up the VBA Macros
提问by Meena
I am Generating a New Sheets using macros. For a New Sheet generation , Data is retrieved from more than 4 MS Access DB. Each DB had minimum 200 field. My Macro code includes
我正在使用宏生成新工作表。对于新工作表生成，从 4 个以上的 MS Access DB 中检索数据。每个 DB 至少有 200 个字段。我的宏代码包括
1. Cell locking
2. Alignment and formatting
3. One third of the cells in the sheet had a formulas
4. Cell reference with other Workbooks
My problem is every sheet generation it takes minimum one hour to complete the hole process. But it seems to me it's taking way too long.
我的问题是每一代板材至少需要一个小时才能完成孔加工。但在我看来，这花费的时间太长了。
I am already added the Application.ScreenUpdating = Trueto speed up the code but still it takes same time. How to do speed up the code , If you have any idea please guide me.
我已经添加了 Application.ScreenUpdating = True来加速代码，但仍然需要相同的时间。如何加速代码，如果您有任何想法，请指导我。
`For Ip = 5 To 150
resp = Range("B" & Ip).Value
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=henkel2;DBQ=C:\Hl-RF\RSF-Temp.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;" _
, Destination:=Range("IV4"))
.CommandText = "select Vles from " & Shtname & " where cint(PrductID)='" & resp & "' and cint(DepotID) = '" & cnt1 & "' and Mnth = '" & mnths & "' and Type='" & typs & "'"
.Name = "tab product"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\Hl-RF\tabct.odc"
.Refresh BackgroundQuery:=False
End With`
Is There Is any way to Reduce the loop iteration time
Thanks In advance
提前致谢
采纳答案by Oorang
There is some disucussion of this topic here.
有这个话题的一些disucussion这里。
Edit:Ok, then the next step is to identify which parts of your code are taking the longest. The simplest way to do this is to make a copy of your code and just start measuring various parts like this:
编辑：好的，下一步是确定代码的哪些部分花费的时间最长。最简单的方法是复制你的代码，然后开始测量像这样的各个部分：
Private Declare Function GetTickCount Lib "kernel32.dll" () As Long
Private mlngStrt As Long
Private mlngEnd As Long
Private Const u As Long = 10000000
Public Sub Example()
Dim i As Long
mlngStrt = GetTickCount
For i = 0 To u
Next
mlngEnd = GetTickCount
Debug.Print "Section1", mlngEnd - mlngStrt
mlngStrt = GetTickCount
ExampleSubCall
mlngEnd = GetTickCount
Debug.Print "ExampleSubCall", mlngEnd - mlngStrt
mlngStrt = GetTickCount
For i = 0 To (u * 1.5)
Next
mlngEnd = GetTickCount
Debug.Print "Section2", mlngEnd - mlngStrt
Debug.Print "Example Complete"
End Sub
Private Sub ExampleSubCall()
Dim i As Long
For i = 0 To (u * 0.75)
Next
End Sub
This approach is fairly straight-forward. The drawback here is that you need to insert all of the timing statements and then turn around and remove them. Which is why I would work on a copy.
这种方法相当直接。这里的缺点是你需要插入所有的时序语句，然后转身删除它们。这就是为什么我会在副本上工作。
Once you know what parts are taking the longest you know where to focus your attention and what to ask for help with.
一旦您知道哪些部分花费的时间最长，您就知道将注意力集中在哪里以及寻求帮助的内容。
回答by Kevin Ross
Surely you mean
你肯定是说
Application.ScreenUpdating = False
Apart from that you could also look to disable the recalculation of the workbook whilst the macro is running and see if that makes a difference. This is of course assuming that the bottle neck is with the spreadsheet part of the process, if its taking ages to get the data from access that might be an area to look at
除此之外，您还可以在宏运行时禁用工作簿的重新计算，看看这是否有所不同。这当然是假设瓶颈在于流程的电子表格部分，如果它需要很长时间才能从访问中获取数据，这可能是一个需要查看的区域
回答by Lunatik
Get hold of a copy of Professional Excel Developmentwhich includes an excellent profiling utility called PerfMon. It will allow you to see which parts of the report are taking all the time so you can analyse and rewrite
获取一份Professional Excel Development的副本，其中包括一个名为 PerfMon 的出色分析实用程序。它将允许您查看报告的哪些部分一直在占用，以便您可以分析和重写
回答by marg
You can try the usual vba optimization methods of setting calculation to manual and disabling ScreenUpdating.
您可以尝试将计算设置为手动并禁用 ScreenUpdating 的常用 vba 优化方法。
Dim calc As XlCalculation
calc = Application.Calculation
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
Application.Calculation = calc
Put your code or function call between Application.Calculation = xlCalculationManualand Application.ScreenUpdating = True.
将您的代码或函数调用放在Application.Calculation = xlCalculationManual和之间Application.ScreenUpdating = True。
Note:I coundn't find info weather or not you run the code from within Access or Excel. If you create the Excel Workbook from Access you probably have some code like this:
注意：我找不到信息天气或您是否从 Access 或 Excel 中运行代码。如果您从 Access 创建 Excel 工作簿，您可能有一些这样的代码：
Dim xlApp As Excel.Application
Set xlApp = new Excel.Application
In this case you would have to change Applicationin the code above to xlApp. For example:
在这种情况下，您必须将Application上面的代码更改为xlApp. 例如：
xlApp.Calculation = xlCalculationManual
回答by Aaron Kempf
I'd try to do MORE of the work on the database side. Generate the reports you want on the database side, and then export the results to Excel.
我会尝试在数据库方面做更多的工作。在数据库端生成你想要的报告，然后将结果导出到Excel。
Access is MUCH better at automating reports than Excel is.
Access 在自动化报告方面比 Excel 好得多。
回答by Chris
Yes, make a table in Access to hold your client IDs. Then create the query here and connect to it with the external data connector. After that refresh it manually or use VBA to refresh the connection whenever you're ready.
是的，在 Access 中制作一个表格来保存您的客户 ID。然后在此处创建查询并使用外部数据连接器连接到它。之后手动刷新它或在准备好时使用 VBA 刷新连接。
回答by Tiago Cardoso
Take a look at Chris comments. We believe that your performance bottleneck is likely to be in the way you're querying the database rather than in the VBA code that applies the data into the sheet.
看看克里斯的评论。我们认为您的性能瓶颈很可能在于您查询数据库的方式，而不是将数据应用到工作表中的 VBA 代码。
Simple questions about Access performance: - Your tables have indexes? - Are you using any kind of table join? - Are the Access databases local on your computer or being accessed remotely?
关于 Access 性能的简单问题： - 您的表有索引吗？- 你在使用任何类型的表连接吗？- Access 数据库是本地计算机上的还是远程访问的？
Again, I'm only reinforcing what Chris already commented.
同样，我只是在强调克里斯已经评论过的内容。
