mxpelite
Registered: Mar 2009
Posts: 2 |
07-05-11 04:52 PM
I've seen many posts looking for the ability to dynamically copy and paste Excel DDE formulas containing cell references.
This is not possible in Excel because the DDE formulas can only be static.
But, here you have a quite convenient workaround which wll allow you to save a lot of time an effort.
All the effort has been applied to link to the ThinkOrSwim thinkDesktop client, but it could be taken as a model to do a similar thing with other DDE servers.
To use this piece of software you need to copy the following code to the VBA code window (you need to open the Visual Basic editor) of your Excel 2010 workbook (it could work in Excel 2003 and 2007 but I have not tested it there)
code:
Public Function TOSDDE(symbol As String, tosfield As String)
ddeformula = "=TOS|" & UCase(tosfield) & "!'" & UCase(symbol) & "'"
'TOSDDE = Application.Evaluate(ddeformula) 'if you don't need to refresh the link this simple solution is good enough
'TOSDDE.Formula = ddeformula 'if this was possible that would be the perfect solution (try find a way to do that!)
TOSDDE = ddeformula 'this allows the use of Activate/Deactivate subs to activate/deactivate the DDE links
End Function
Sub ActivateTOSDDElinks()
Dim fullRange As range
Dim formulaRange As range
Dim cel As range
Set fullRange = ActiveSheet.Cells
Set formulaRange = fullRange.SpecialCells(xlCellTypeFormulas)
For Each cel In formulaRange
If InStr(1, cel.Formula, "=TOSDDE(", vbTextCompare) = 1 Then
cel.ClearComments
cel.AddComment (cel.Formula)
cel.Formula = cel.Value
End If
Next
End Sub
Sub DeActivateTOSDDElinks()
Dim fullRange As range
Dim formulaRange As range
Dim cel As range
Dim comm As String
Set fullRange = ActiveSheet.Cells
Set formulaRange = fullRange.SpecialCells(xlCellTypeFormulas)
For Each cel In formulaRange
comm = ""
On Error Resume Next
comm = cel.Comment.Text
If InStr(1, comm, "=TOSDDE(", vbTextCompare) = 1 Then
cel.Value = cel.Formula
cel.Formula = comm
cel.ClearComments
End If
Next
End Sub
once copied you can inmediately use it in your Excel worksheet.
As you can see, there are one function and two subs.
The first thing to do is to code some DDE formulas using the provided TOSDDE() function. The syntax is:
=TOSDDE(symbol, DDE_field)
examples:
=TOSDDE("AAPL", "BID")
Having
cell A2: "AAPL"
cell B1: "BID"
=TOSDDE($A2, B$1)
You will see that the cell is populated with the static DDE expression:
=TOS|BID!AAPL
You can use complex symbols like ".AAPL110716P330" (AAPL July 16, 2011 330 put), this will generate things like =TOS|DELTA!'.AAPL110716P330'
Now you can drag, copy and paste the TOSDDE formulas and the cell references will adjust.
When you decide to, you can execute the sub "ActivateTOSDDElinks" and the DDE data will start to flow through the existing formulas of the active sheet.
You will notice that the cells containing DDE formulas have a small red corner. This is an indication that there is a comment in that cell. The comment contains the original TOSDDE function that was introduced to allow the functionality that I explain in the next paragraph.
If you need to, you may also use the opposite function, "DeActivateTOSDDElinks" and the DDE access will stop and the original formulas will revert back to its original form. This can help if problems arise with the DDE feed or if you want to save CPU resources stopping not needed links.
Both this subs act at the sheet level, if you want to apply them to different sheets you need to go sheet by sheet.
And that's it, I would appreciate any comments and if any of you can find a 1-step way to activate the DDE feed when introducing the TOSDDE function (I couldn't), please, let me know. This would avoid the need to use the subs.
I hope you will enjoy this...
|
