一样的代码在不同的环境下快慢差异太大

Henry D. Sy

目的:
从Access中复制数据到Excel中,
并设置单元格字体格式
(同一个单元格中部分字符,不同字体,本例中是单元格中第7,第8两个字体设为7号楷体)
如果直接在Access中操作,需要8秒中(仅设置格式部分),
而同样(几乎一致)的代码,在Excel中运行,则只需1秒,甚至不到1秒.
我想Access中的代码应该是写的不合理,
大家指点一下,如何优化代码?


Excel中的代码(只需1秒)

1. Private Sub CommandButton1_Click()
   
2.     Dim R As Long, C As Long, I As Long, J As Long
   
3.     Dim t As Date
   
4. t = Now
   
5.     R = Rcount
   
6.     C = Ccount
   
7.     Application.ScreenUpdating = False
   
8.     For I = 4 To R
   
9.         For J = 1 To C
   
10.             With Sheets("A").Cells(I, J).Characters(7, 2).Font
    
11.                 .Name = "楷体"
    
12.                 .FontStyle = "常规"
    
13.                 .Size = 6
    
14.             End With
    
15.         Next
    
16.     Next
    
17.     Application.ScreenUpdating = True
    
18.     t = t - Now
    
19.     MsgBox t
    
20. End Sub

复制代码

Access中的代码(需要8秒-10秒)

1. Private Sub Command0_Click()
   
2.     Dim XLA As New Excel.Application
   
3.     Dim rs As New ADODB.Recordset
   
4.     Dim XLB As Workbook
   
5.     Dim XLS As Worksheet
   
6.     Dim I As Integer, J As Integer
   
7.     Dim R As Long, C As Long
   
8.     Dim t As Date
   
9.     Set XLB = Nothing
   
10.     Set XLS = Nothing
    
11.     Set XLB = XLA.Workbooks().Open(CurrentProject.Path & "\Try.xlsb", , True)
    
12.     XLB.SaveAs CurrentProject.Path & "" & Format(Now, "yyyy-mm-dd hh nn ss") & ".xlsb"   '另存为不同的文件名"
    
13.     '---------------
    
14.     '导出Excel
    
15.     '---------------
    
16.     rs.Open "B", CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    
17.     Set XLS = XLB.Worksheets("A")
    
18.     XLS.Activate
    
19.     For I = 0 To rs.Fields.Count - 1
    
20.         XLS.Cells(3, 1 + I) = rs.Fields(I).Name
    
21.     Next
    
22.     XLS.Range("A4").CopyFromRecordset rs
    
23.     XLS.Range(XLS.Cells(3, 1), XLS.Cells(rs.RecordCount + 3, rs.Fields.Count)).Borders.LineStyle = xlContinuous
    
24.     R = rs.RecordCount + 3
    
25.     C = rs.Fields.Count
    
26.     rs.Close
    
27.     t = Now
    
28.     XLA.ScreenUpdating = False
    
29.     For I = 4 To R
    
30.         For J = 1 To C
    
31.             With XLS.Cells(I, J).Characters(7, 2).Font
    
32.                 .Name = "楷体"
    
33.                 .FontStyle = "常规"
    
34.                 .Size = 7
    
35.             End With
    
36.         Next
    
37.     Next
    
38.     XLA.ScreenUpdating = True
    
39.     t = t - Now
    
40.     MsgBox t
    
41.     XLB.Save
    
42.     XLA.Visible = True
    
43.     XLA.WindowState = xlMaximized
    
44.     Set rs = Nothing
    
45.     Set XLS = Nothing
    
46.     Set XLB = Nothing
    
47.     Set XLA = Nothing
    
48. End Sub
    

复制代码

另外以前感觉Excel打开并在屏幕上可见才需要XLA.ScreenUpdating = False
现在发现,即便Excel在内存里打开,并没有显示在屏幕上,如果不加XLA.ScreenUpdating = False
更慢,而且是非常慢,大家可以试试.

附上附件,请大家帮忙优化优化.

tmtony

建议你可在每个重要的计算前面加处 Timer -dblStartTime
来计算一下这些代码所花的时间，从而知道哪一句占时最长。可保留小数位，这样时间更精确

Henry D. Sy

主要是设置字体这一部分的对比

tmtony

会不会是因为设置字体 需要将excel显示出来？

Henry D. Sy

可能,否则关闭屏幕刷新作用不可能这么大

roych

需要格式化输出Excel时，我通常是预设好格式，然后导出数据，再引用即可。
不过你这个代码里有个地方比较麻烦，就是每个单元格的某2个字符改为楷体。预设格式不太好处理这部分。

Henry D. Sy

roych 发表于 2020-4-19 22:11
需要格式化输出Excel时，我通常是预设好格式，然后导出数据，再引用即可。
不过你这个代码里有个地方比较 ...

是的,没办法预设