如何在 VBA 中执行长时间运行的进程而不使电脑爬行？

发布于 2024-09-13 21:09:16 字数 714 浏览 2 评论 0原文

我有一个 VBA 应用程序，它创建 COM 对象的实例，然后连续轮询对象的 DataReady 属性以查看是否有新数据。当新数据可用时，它将数据粘贴到电子表格中。问题是这个宏（vba 子例程）不断运行，这会减慢 excel 的速度，并使计算机在进程运行时有些无法使用。有没有一种方法可以让我在单独的线程上启动此进程或执行 .NET 后台工作程序之类的操作？

我的两次尝试是使用像这样的 while 循环..

While(True)
    If(myObject.DataReady)
        Do some stuff here
    End If
WEnd

然后是

Sub GrabNewPoint()
If (myModule.NewDataReady_Receiver = True) Then
            Do some stuff here...
End If

If (StopTest = False) Then
    NextTime = Now() + TimeValue("00:00:20")
    Application.OnTime NextTime, "GrabNewPoint"
End If

第二次尝试肯定效果更好，但它仍然大大减慢了速度。有更好的解决方案吗？

我的 COM 对象是我用 C# 编写的类库。我可以添加数据准备好时在类库中触发的事件，但如何在 VBA 程序中侦听这些事件？

原文

I have a VBA application that creates an instance of a COM object and then continuously polls the objects DataReady property to see if there is new data. When new data is available it sticks the data into a spread sheet. The problem is this macro (vba sub routine) continually runs and this slows down excel to a crawl and makes the computer somewhat unusable while the process is running. Is there a way that I can start this process on a separate thread or do something like a .NET background worker?

My two attempts were to use a while loop like this..

While(True)
    If(myObject.DataReady)
        Do some stuff here
    End If
WEnd

and then this

Sub GrabNewPoint()
If (myModule.NewDataReady_Receiver = True) Then
            Do some stuff here...
End If

If (StopTest = False) Then
    NextTime = Now() + TimeValue("00:00:20")
    Application.OnTime NextTime, "GrabNewPoint"
End If

The second attempt definitly works better but it still slows things down considerably. Is there a better solution?

My COM object is a class library that I wrote in C#. I can add events that fire in the Class Library when data is ready but how do I listen for those events in the VBA program?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

尘曦 2024-09-20 21:09:16

您尝试过使用 DoEvents 吗？

While(True)
    If(myObject.DataReady)
        'your code here
    End If
    DoEvents
WEnd

Have you tried using DoEvents?

While(True)
    If(myObject.DataReady)
        'your code here
    End If
    DoEvents
WEnd

回复收藏 0 原文

送君千里 2024-09-20 21:09:16

我认为最好的解决方案是让 COM 对象在数据准备就绪时引发 VBA 事件。

如果这不是一个选项（无法控制 COM 对象等），那么您将不得不旋转 CPU。您所能做的就是增加检查 DataReady 属性之间的时间间隔，您已经在第二个选项中发现了这一点。我会计算出在不失去功能的情况下可以增加多少间隔，然后将其留在那里。

回复收藏 0 原文

浮生面具三千个 2024-09-20 21:09:16

尝试一下，看看情况是否有所改善。

Just pause, let the CPU fly...  key is to trap it here so it releases as long as you like
    Public Sub App_Hard_Wait_DoEvents(dblSeconds As Double)
      If dblSeconds = 0 Then Exit Sub
      Dim varStart As Variant
      varStart = Timer
      Do While Timer < (varStart + dblSeconds)
        DoEvents
      Loop
    End Sub


    DO
     Call App_Hard_Wair_DoEvents(10)
    loop until (myObject.DataReady)

Try this, see if things improve.

Just pause, let the CPU fly...  key is to trap it here so it releases as long as you like
    Public Sub App_Hard_Wait_DoEvents(dblSeconds As Double)
      If dblSeconds = 0 Then Exit Sub
      Dim varStart As Variant
      varStart = Timer
      Do While Timer < (varStart + dblSeconds)
        DoEvents
      Loop
    End Sub


    DO
     Call App_Hard_Wair_DoEvents(10)
    loop until (myObject.DataReady)

回复收藏 0 原文

~没有更多了~