时间戳

首页 > 解决方案 > 使用记录集的 excel 宏 - 优化

excel - 使用记录集的 excel 宏 - 优化

问题描述

我一直在尝试优化以下代码：

Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public rs2 As New ADODB.Recordset
Public strsQL$

…</p> 

For c = 9 To 50

strsQL = "some SQL query"
rs.Open strsQL, cn
For r = 2 To 1000
        t = 0
        If c <> 9 Then
            For f = 9 To c - 1
                t = t + Sheet9.Cells(8 + (r - 2) * 6, f)
            Next f
        End If

        Set rs2 = rs.Clone
        rs2.Find ("x='" & Sheet9.Cells(r, 2) & "'")
        If rs2.EOF Then
            Sheet9.Cells(r, c) = ""
        Else
            If rs2.Fields("Total").Value - t <> 0 Then
                Sheet9.Cells(r, c) = rs2.Fields(1).Value - t
            End If
        End If
    Next r
    rs.Close
    rs2.Close
Next c
cn.Close
Close Connection
Set rs = Nothing
Set cn = Nothing
Set rs2 = Nothing

这是我的想法（或者实际上是因为它更慢）：

…</p> 

For c = 9 To 50        
strsQL = "some SQL query "
rs.Open strsQL, cn

Do While rs.EOF = False
t = 0
If Not ThisWorkbook.Sheet9.Range("B:B").Find(what:=rs.Fields(0).Value, 
LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then

P_row = ThisWorkbook.Sheet9.Range("B:B").Find(what:=rs.Fields(0).Value, LookIn:=xlValues, lookat:=xlWhole).Row
adr_1 = ThisWorkbook.Sheet9.Cells(P_row + 4, 9).Address
adr_2 = ThisWorkbook.Sheet9.Cells(P_row + 4, c - 1).Address
t = Application.WorksheetFunction.Sum(ThisWorkbook.Sheet9.Range(adr_1 & ":" & adr_2))

If rs.Fields(1).Value - t > 0 Then
Sheet9.Cells(P_row, c) = rs.Fields(1).Value - t
End If
End If
rs.MoveNext
Loop
rs.Close
Next c

cn.Close
Set rs = Nothing
Set cn = Nothing

我不明白为什么第二个代码的运行时间更差。第一个代码遍历每一列，并为每一列打开记录集并遍历工作表中 B 列的每一行，以查找该行值是否包含在记录集中。因此，即使搜索到的行不包含在记录集中，记录集也会被命令 rs.find 整个搜索。由于每个记录集的记录都比 1000 少得多，我很确定每列通过记录集并只为找到的记录放置值会更好，因为其余的无论如何都可以留空。但似乎这不是真的。

而且我还使用了 application.worksheetfunction.sum 而不是在循环中求和，但我不希望它有那么大的意义。

也许任何人都可以帮助我找出我的理解有什么问题？

标签： excelvbarecordset

解决方案

推荐阅读