Excel VBA-为DDE链接单元格中的每个更新值创建行

问题描述

假设单元格A1具有DDE链接"= dde(realtime_stock_price".每次更新时如何将值发送到列的新行?Change()函数不起作用，因为它不是用户执行的操作更改.我尝试了Calculate()函数，但不太确定如何实现它.

Lets say cell A1 has a DDE link, "=dde(realtime_stock_price". How can I send the value to a new row of a column everytime it updates? The Change() function doesnt work because its not the user doing the change. I tried the Calculate() function but not too sure how to implement it.

推荐答案

我不熟悉公式"= dde()".但是，您可以只创建自己的dde函数来调用事件代码...

The formula "=dde()" isn't something I am familiar with. However you could just make your own dde function which calls your event code...

Public Function dde2(ByVal app as string,ByVal topic as string,ByVal item as string) as variant
    with Application
        channelNumber = .DDEInitiate(app,topic) 
        dde2 = .DDERequest(channelNumber, "Topics")
        .DDETerminate(channelNumber)
    end with
    on error resume next   'incase macro doesn't exist
        Application.run "dde2_postExec", dde2
    on error goto 0
End Function

Sub dde2_postExec(ByVal message as string)
    'do stuff...
end sub

编辑

根据此问题，传递给 = DDE()的字符串为"Service | Topic！Item" .或者在我们的示例中:"App | Topic！Item" ，可以轻松地从字符串中提取该代码.但是请注意， DDE()不是excel的本机功能.因此，我们不太可能知道 DDE()如何制定一个干净的解决方案.

Edit

According to this question the string passed to =DDE() is "Service|Topic!Item". Or in our case: "App|Topic!Item" which can be extracted from the string easily. However do note that DDE() is NOT a native function of excel. So it is unlikely we will be able to know how DDE() is working to make a clean solution.

给出您的评论，说DDE正在流式传输"12 ... 13 ... 14 ..." ，并且您要输出 B1 = 12 ， B2 = 13 和 B3 = 14 .您的代码将是这样的[unested]:

Given your comment, say DDE is streaming "12...13...14..." and you want to output B1 = 12, B2=13 and B3=14. Your code would be something like this [untested]:

Sub dde2_postExec(ByVal message as string)
    dim v as variant: v=split(message,"...")
    for i = 1 to ubound(v)-1
        range("B" & i).value = v(i)
    next
end sub

但是请注意，如果将其作为公式调用，则可能无法正常工作.VBA运行时的设计使得在评估forumla时，除了调用它们的单元格之外，它们无法将值写入工作表中的单元格.因此，您可能不得不使用工作表事件，而不是使用公式的计算事件.

However do note that this may not work if called as a formula. The VBA runtime is designed such that when forumla's are evaluated they cannot write values to cells in the sheet, other than the cell that called them. For this reason you may be forced into hooking into worksheet events instead of using the formula's calculate event.

这篇关于Excel VBA-为DDE链接单元格中的每个更新值创建行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！