如何在不进行PC爬网的情况下在VBA中执行长时间运行的过程?

问题描述

限时删除！！

我有一个VBA应用程序，该应用程序创建COM对象的实例，然后连续轮询对象的 DataReady 属性以查看是否有新数据.当有新数据可用时，它将数据粘贴到电子表格中.问题是此宏(vba子例程)持续运行，这会降低excel的爬网速度，并使计算机在进程运行时无法使用.有没有一种方法可以在单独的线程上启动此过程，或者执行.NET后台工作程序之类的方法?

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?

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

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

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

第二次尝试肯定会更好，但是仍然会大大降低速度.有更好的解决方案吗?

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

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

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?

推荐答案

您是否尝试过使用DoEvents?

Have you tried using DoEvents?

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

这篇关于如何在不进行PC爬网的情况下在VBA中执行长时间运行的过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..