如何加快VBA宏 [英] how to Speed Up the VBA Macros
问题描述
1.单元锁定
2.对齐和格式化
3.三分之一表格中的单元格具有公式
4.与其他工作簿的单元格引用
我的问题每一个片材生成它需要最少一个小时才能完成孔过程。但是在我看来,这太长了。
我已经添加了 Application.ScreenUpdating = True
加快代码,但仍然需要相同的时间。如何加快代码,如果你有任何想法请指导我。
`对于Ip = 5到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;_
,目的地:=范围(IV4))
.CommandText =选择Vles from &安培; Shtname& 其中cint(PrductID)='&分别'和cint(DepotID)='& cnt1& '和Mnth ='& mnths& '和Type ='&典型值'
.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
有
有没有办法减少循环迭代时间
感谢提前
这个主题有一些问题这里。
编辑: strong>
好的,那么下一步是确定你的哪些部分代码最长。最简单的方法是复制代码,然后开始测量各种部件,如下所示:
私有声明函数GetTickCount Libkernel32.dll()As Long
私人mlngStrt As Long
私人mlngEnd As Long
私有统计数据长度= 10000000
Public Sub Example()
Dim i As Long
mlngStrt = GetTickCount
For i = 0 To u
Next
mlngEnd = GetTickCount
Debug.PrintSection1,mlngEnd - mlngStrt
mlngStrt = GetTickCount
ExampleSubCall
mlngEnd = GetTickCount
Debug.PrintExampleSubCall,mlngEnd - mlngStrt
mlngStrt = GetTickCount
对于i = 0 To(u * 1.5)
下一个
mlngEnd = GetTickCount
Debug.PrintSection2,mlngEnd - mlngStrt
Debug.PrintExample Complete
End Sub
Private Sub ExampleSubCall()
Dim i As Long
For i = 0 To(u * 0.75)
下一个
End Sub
这种方法相当简单。这里的缺点是您需要插入所有的定时语句,然后转过身来删除它们。这就是为什么我会在一份副本上工作。
一旦你知道什么部分是最长的时间,你知道在哪里集中注意力和要求帮助。 / p>
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
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 = True
to 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.
`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
There is some disucussion of this topic here.
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.
这篇关于如何加快VBA宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!