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...
Hi mxpelite, The function and macro works great! Thank you so much, now I can ditch my OpenOffice DDE spreadsheet Cheers, tangpd
I am unfamiliar with VBA code/coding and was unsuccessful getting this code to run. I pasted the code to Sheet1(Code) area (In the âMicrosoft Visual Basic for Applicationsâ window), saved the file, then went to Sheet1 and input: =TOSDDE(âAAPLâ, âBIDâ) when I press enter, all I get is â#NAME?â. After that, I tried executing the macro: Sheet1.ActivateTOSDDElinks but nothing happens/changes. Is there something else that needs to be done in order for this code to work that I havenât done? I would greatly appreciate any help on this! I am using Office 2012.
I am unfamiliar with VBA code/coding and was unsuccessful getting this code to run. I pasted the code to Sheet1(Code) area (In the âMicrosoft Visual Basic for Applicationsâ window), saved the file, then went to Sheet1 and input: =TOSDDE(âAAPLâ, âBIDâ) When I press enter, all I get is â#NAME?â. After that, I tried executing the macro: Sheet1.ActivateTOSDDElinks But nothing happens/changes. Is there something else that needs to be done in order for this code to work that I havenât done? I would greatly appreciate any help on this! I am using Office 2012.
You need to create Module and put the code in there. These are written as subroutines not a spreadsheet formulas.