如何解决OnTime函数越用越慢的问题

如风（鞠春阳）

在做数据大屏幕的时候，想用OnTime这个函数，做定时数据的刷新（几秒更新一次）， 包括倒计时（每秒刷新一次），代码如下！
程序里面，一共用了五个OnTime，有些数据是一秒刷新一次，有些是3秒，还有10秒，还有60秒刷新的。

但是遇到一个问题，就是这个程序刚开始的时候，执行没问题，但执行几十分钟后开始越来越慢，几小时后，经常就会卡住，更本不可能一秒一更新，有时间会好几秒才能更新一次！ 请问这是哪里出问题了？应该怎么优化下？


代码如下：


'每10秒钟刷新一次数据
Sub ZuoShang()

'左上角订单刷新
If Time() > CDate(Sheet2.Range("A" & DingDan)) Then

    ActiveSheet.Shapes.Range(Array("Picture 5")).Select  '更新左上角订单
        Selection.Formula = "=Sheet2!A" & DingDan - 6 & ":D" & DingDan  '更新左上角订单
    ActiveSheet.Shapes.Range(Array("Picture 11")).Select  '取消选择

    DingDan = DingDan + 1
    Sheet3.Range("E12") = DingDan '更新今日引流数量
    Sheet3.Range("E14") = Sheet3.Range("E14") + 1 '更新总引流量
End If

Application.OnTime Now + TimeValue("00:00:10"), "ZuoShang"  '10秒刷新一次左上角

End Sub


Sub ZhongJian()

Sheet6.Range("C1") = Sheet6.Range("C1") + Int(100 * Rnd() + 66) '刷新中间大数字

Sheet3.Range("L1") = Now() '更新当前时间

Application.OnTime Now + TimeValue("00:00:01"), "ZhongJian"  '1秒刷新一次中间大数据
End Sub

Sub YouXia()

'右下角排名滚动
Sheet4.Range("A1:D1").Copy Sheet4.Range("A21:D21")
Sheet4.Rows(1).Delete
ActiveSheet.Shapes.Range(Array("Picture 17")).Select '更新右下角排名
    Selection.Formula = "=Sheet4!A1:D5" '更新右下角排名
ActiveSheet.Shapes.Range(Array("Picture 11")).Select '取消选择

Application.OnTime Now + TimeValue("00:00:03"), "YouXia"  '3秒刷新一次右下数据
End Sub

Sub ZuoZhong()

Sheet3.Range("B" & JinTian & ":B" & JinTian + Hour(Now())).Copy Sheet3.Range("J2:J" & Hour(Now()) + 2) '刷新左中业绩折线图

Application.OnTime Now + TimeValue("00:01:00"), "ZuoZhong"  '1分钟刷新一次折线图
End Sub


Sub ZheXianYeJi()
'左中业绩曲线图滚动报数
Select Case YeJiXunHuan
    Case 0
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M3:N5"
        Selection.ShapeRange.Top = 450
        Selection.ShapeRange.Left = 63
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 1
    Case 1
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M6:N8"
        Selection.ShapeRange.Top = 475
        Selection.ShapeRange.Left = 76
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 2
    Case 2
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M9:N11"
        Selection.ShapeRange.Top = 490
        Selection.ShapeRange.Left = 89
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 3
    Case 3
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M12:N14"
        Selection.ShapeRange.Top = 500
        Selection.ShapeRange.Left = 102
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 4
    Case 4
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M15:N17"
        Selection.ShapeRange.Top = 500
        Selection.ShapeRange.Left = 115
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 5
    Case 5
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M18:N20"
        Selection.ShapeRange.Top = 502
        Selection.ShapeRange.Left = 128
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 6
    Case 6
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M21:N23"
        Selection.ShapeRange.Top = 505
        Selection.ShapeRange.Left = 141
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 7
    Case 7
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M24:N26"
        Selection.ShapeRange.Top = 485
        Selection.ShapeRange.Left = 154
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 8
    Case 8
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M27:N29"
        Selection.ShapeRange.Top = 482
        Selection.ShapeRange.Left = 167
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 9
    Case 9
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M30:N32"
        Selection.ShapeRange.Top = 465
        Selection.ShapeRange.Left = 180
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 10
    Case 10
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M33:N35"
        Selection.ShapeRange.Top = 460
        Selection.ShapeRange.Left = 193
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 11
    Case 11
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M36:N38"
        Selection.ShapeRange.Top = 450
        Selection.ShapeRange.Left = 206
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 12
    Case 12
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M39:N41"
        Selection.ShapeRange.Top = 445
        Selection.ShapeRange.Left = 219
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 13
    Case 13
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M42:N44"
        Selection.ShapeRange.Top = 430
        Selection.ShapeRange.Left = 232
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 14
    Case 14
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M45:N47"
        Selection.ShapeRange.Top = 410
        Selection.ShapeRange.Left = 245
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 15
    Case 15
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M48:N50"
        Selection.ShapeRange.Top = 415
        Selection.ShapeRange.Left = 258
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 16
    Case 16
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M51:N53"
        Selection.ShapeRange.Top = 420
        Selection.ShapeRange.Left = 271
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 17
    Case 17
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M54:N56"
        Selection.ShapeRange.Top = 425
        Selection.ShapeRange.Left = 284
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 18
    Case 18
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M57:N59"
        Selection.ShapeRange.Top = 430
        Selection.ShapeRange.Left = 297
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 19
    Case 19
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M60:N62"
        Selection.ShapeRange.Top = 432
        Selection.ShapeRange.Left = 310
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 20
    Case 20
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M63:N65"
        Selection.ShapeRange.Top = 425
        Selection.ShapeRange.Left = 323
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 21
    Case 21
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M66:N68"
        Selection.ShapeRange.Top = 420
        Selection.ShapeRange.Left = 336
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 22
    Case 22
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M69:N71"
        Selection.ShapeRange.Top = 425
        Selection.ShapeRange.Left = 349
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 23
    Case 23
        ActiveSheet.Shapes.Range(Array("Picture 2")).Select
        Selection.Formula = "=Sheet3!M72:N74"
        Selection.ShapeRange.Top = 422
        Selection.ShapeRange.Left = 362
        ActiveSheet.Shapes.Range(Array("Picture 11")).Select
        YeJiXunHuan = 0

End Select


Application.OnTime Now + TimeValue("00:00:03"), "ZheXianYeJi"  '启动每秒刷新
End Sub


Sub 宏8()

ZhongJian
ZuoShang
YouXia
ZuoZhong
ZheXianYeJi

End Sub

Sub 宏4()
Application.OnTime Now + TimeValue("00:00:01"), "ZhongJian", , False '1秒刷新一次中间大数据
Application.OnTime Now + TimeValue("00:00:10"), "ZuoShang", , False '10秒刷新一次左上角
Application.OnTime Now + TimeValue("00:00:03"), "YouXia", , False '3秒刷新一次右下数据
Application.OnTime Now + TimeValue("00:01:00"), "ZuoZhong", , False '1分钟刷新一次折线图
Application.OnTime Now + TimeValue("00:00:03"), "ZheXianYeJi", , False '启动每秒刷新

End Sub

wanghan519

可不可改成数据由rtd提供，然后绘折线图之类，用exceldna写个rtd服务，然后设置更新间隔，如图中为200毫秒

如风（鞠春阳）

有高手在吗

如风（鞠春阳）

wanghan519 发表于 2023-4-19 06:05
可不可改成数据由rtd提供，然后绘折线图之类，用exceldna写个rtd服务，然后设置更新间隔，如图中为200毫秒
...

大侠，这是啥？exceldna....   怎么用？ 也是VBA设计吗？  我一个新手，不会这个，还望详细解惑

wanghan519

主要Excel是单线程的，ontime也会等之前的操作结束才会执行，太多总会卡，rtd函数是专门用来展示实时数据的，但需要自己写个服务，最简单的办法是ExcelDNA，用c#或vb.net，但还是很复杂，github上有项目是个固定的rtd服务，但topic是从redis里获取的，这样就把Excel和redis连起来了，不用每次写c#，只需要写入redis就可以实时显示在Excel，比较方便。但说实在的，Excel还是不太适合做这种展示页面的，真要动态实时的展示，网页比这简单多了。。。