有没有办法在excel中响应条件格式的变化？

I have an excel sheet that has dde links to real time market data. I use a timer to watch the dde prices every second. Then submit orders when certain conditions are met. I tried an infinite loop with DoEvent in the middle which works for 5 seconds then freezes the workbook.

我有一张excel表,其中有dde链接到实时市场数据。我用计时器每秒观看一次dde价格。然后在满足某些条件时提交订单。我尝试在中间使用DoEvent进行无限循环,然后冻结工作簿。

Is there a way to respond to changes in dde updates? Change event doesn't detect them. It just detects when user makes manual change.

有没有办法来响应dde更新中的更改?更改事件未检测到它们。它只是检测用户何时进行手动更改。

I was told that if I have conditional formatting there's a way to pick up that event. So I can create a cell formula to turn true when my condition is met and then conditional format that cell to some formatting when it's true and then pick up the format change event. Is that possible? If so how. Any suggestions would be appreciated.

有人告诉我,如果我有条件格式,那么就有办法接受这个事件。因此,我可以创建一个单元格公式,以便在满足条件时转为true,然后条件格式化该单元格为某些格式化时为真,然后选择格式更改事件。那可能吗?如果是这样的话。任何建议,将不胜感激。

To Clarify: I want to pick up an event IN VBA that would submit an order to trade a stock. The way i'm doing this right now is with a timer which loops over all the rows looking for true cell in the trigger column. Once found it shuts off the flag for that row (sets the true condition to false) and submits the order.

澄清:我想在VBA中提交一个提交订单来交易股票的事件。我现在这样做的方式是使用一个计时器,它遍历所有行,在触发列中寻找真正的单元格。一旦找到它就会关闭该行的标志(将真实条件设置为false)并提交订单。

The problem is that one second is an eternity for fast moving stocks. So I need an event to be thrown in VBA when a cell in trigger column turns true so I can respond immediately and not wait for the second interval of the timer class.

问题是一秒钟是快速流动股票的永恒。因此,当触发器列中的单元格变为true时,我需要在VBA中抛出一个事件,这样我就可以立即响应,而不是等待计时器类的第二个时间间隔。

As far as I know, you can't call on the timer with a value of less than a second. If I could use milliseconds my problem would be solved. I'd just loop over the list every 10 milliseconds.

据我所知,您不能使用小于一秒的值来调用计时器。如果我可以使用毫秒,我的问题将得到解决。我只是每10毫秒循环遍历列表。

As far as I know I can't create another thread in VBA. If I could I would make an infinite loop and put it to sleep after every iteration for 10 milliseconds or so.

据我所知,我无法在VBA中创建另一个线程。如果可以的话,我会做一个无限循环,并在每次迭代后将其置于睡眠状态10毫秒左右。

As far as I know, I can't pull dde directly into VBA or even .net for that matter since MSDN says it's no longer supported.

据我所知,我不能直接将dde拉入VBA甚至.net,因为MSDN说它不再受支持了。

I hope this clarifies. All suggestion are appreciated.

我希望这澄清一下。所有建议都表示赞赏。

3 个解决方案

#1

If you create a dummy function which has your DDE output cells as parameters then you should be able to respond to the Worksheet_Calculate event?

如果您创建一个将DDE输出单元格作为参数的虚拟函数,那么您应该能够响应Worksheet_Calculate事件?

I'm guessing that might work, but I've no experience with DDE: the DDE update may even trigger the Calculate event directly.

我猜这可能有用,但我没有使用DDE的经验:DDE更新甚至可能直接触发Calculate事件。

#2

If you are asking if you can use conditional formatting to trigger an event, yes that is possible. I am not familiar with the DDE model myself, however, and mixing conditional formatting to trigger an event from a data condition does seem like an extra step, as Stepan1010 notes.

如果您询问是否可以使用条件格式来触发事件,那么可以。然而,我自己并不熟悉DDE模型,混合条件格式以从数据条件触发事件似乎是一个额外的步骤,正如Stepan1010所说。

You may want to consider the discussion in Mr Excel in this link, as the issue centers around changing a cell value based changes made to cells from a DDE connection: http://www.mrexcel.com/forum/showthread.php?176508-Comments-VBA-amp-Min-Max

您可能希望在此链接中考虑Excel中的讨论,因为问题在于更改基于单元格值的DDE连接对单元格所做的更改:http://www.mrexcel.com/forum/showthread.php?176508 -Comments-VBA-AMP-最小 - 最大

You may also consider using a DoEvent with a loop set for a time period based on how long you will actually implement the macro, if that applies to your application. This SO article is focused on a status bar, but I think the same logic applies in terms of an event execution, say based on a conditional within a loop: Force a screen update in Excel VBA

如果适用于您的应用程序,您还可以考虑使用具有循环集的DoEvent一段时间,具体取决于实际实现宏的时间。这篇SO文章主要关注状态栏,但我认为同样的逻辑适用于事件执行,比如基于循环中的条件:强制Excel VBA中的屏幕更新

Hopefully this is helpful to you =)

希望这对你有帮助=)

~JOL

#3

Why don't you just recreate whatever conditional formatting logic you have in that cell into a seperate cell?

为什么不直接将该单元格中的条件格式逻辑重新创建为单独的单元格?

For example your conditional formatting logic might highlight a cell when it is above a certain number - you could just put that logic in another cell- eg. =if(A1>100,TRUE,FALSE)

例如,当条件格式化逻辑高于某个数字时,它可能会突出显示一个单元格 - 您可以将该逻辑放在另一个单元格中 - 例如。 = IF(A1> 100,TRUE,FALSE)

So I guess my question is - Why pick up the format change event when you can just pick up the event itself?

所以我想我的问题是 - 当你可以自己拿起活动时,为什么要选择格式更改事件?

Edit for your clarification:

编辑以供澄清:

If you want to run a macro continuously in VBA you don't need a timer - you can just do a continuous loop like this:

如果你想在VBA中连续运行一个宏,你不需要一个计时器 - 你可以像这样做一个连续的循环:

Sub macro1()

    Dim i As Double

    With Sheet1

        Do

            '.Cells(5, 4).Value = i

            i = i + 1

            .Cells(1, 1).Value = i

            ' you are going to want to comment this out if you want to don't need to do other things
            DoEvents

            If Sheets("Sheet1").Range("A2").Value = True Then
                ' put your code here.
            End If
        Loop

    End With
End Sub

So I must still be having trouble understanding your situation.

所以我仍然无法理解你的情况。

#1