如何加快VBA宏 [英] how to Speed Up the VBA Macros

问题描述

我正在使用宏生成新的表格。对于New Sheet生成，从多于4个MS Access数据库中检索数据。每个DB最少有200个字段。我的宏代码包括

  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屋！