This is how I do it (a Document is also attached): Sub MakeDDE() Dim ExpDt As Date For CurRow = 2 To 11 DDERoot = "=TOS|MARK!'" 'Single then Double Quote at the end StkSym = "." & Cells(CurRow, 1) '''Period is needed for Options ExpDt = Cells(CurRow, 2) StrkPr = Cells(CurRow, 3) CallPut = Cells(CurRow, 4) YYMMDD = Right(ExpDt, 2) & _ WorksheetFunction.Text(Month(ExpDt), "00") & _ WorksheetFunction.Text(Day(ExpDt), "00") DDEFormula = DDERoot & _ StkSym & _ YYMMDD & _ CallPut & StrkPr & "'" '''Dbl, Single, Dbl Quotes Cells(CurRow, 5) = DDEFormula Selection.Replace What:="=", Replacement:="=" Next End Sub
I know this doesn't sound logical, but I've found doing a replace on the equal sign can activate DDE formulas after they are copied in Excel. The VBA statement is: Selection.Replace What:="=", Replacement:="="
Turning Dynamic Data Exchange DDE on and off Click here to see what Microsoft says about turning DDE on/off According to Microsoft: âWhen a new workbook is created, the default value for the UpdateRemoteReferences property is True and dynamic data exchange (DDE) links and OLE links update automatically. If the value is False, DDE links and OLE links do not update automatically or during recalculation.â I have not been able to get UpdateRemoteReferences to do what it is advertised to do. You may have better luck. Code: [b]Sub DDE_OnOffSwitch()[/b] ActiveWorkbook.UpdateRemoteReferences = Not ActiveWorkbook.UpdateRemoteReferences [b]End Sub[/b] ------------------------------------------------------------------------------------------------------------------------------------------------------- If you donât mind turning ALL spreadsheet calculations off, this will turn ALL spreadsheet calculations on/off â including DDE. Code: [b]Sub Calc_OnOffSwitch()[/b] If Application.Calculation = xlManual Then Application.Calculation = xlAutomatic Else Application.Calculation = xlManual End If [b]End Sub[/b] ---------------------------------------------------------------------------------------------------------------------------------------------------------- A workaround way to turn just DDE off would be to run the On/Off Subs below. Comment out the DDE formulas then change the DDE formulas font color to hide the formulas: Code: [b][size=3]ROW COLUMN A[/size][/b] 1 =TOS|MARK!'.SPY121117C142' 2 =TOS|MARK!'.SPY121117C143' 3 =TOS|MARK!'.SPY121117C144' 4 =TOS|MARK!'.SPY121117C145' 5 =TOS|MARK!'.SPY121117C146' 6 =TOS|MARK!'.SPY121117C147' 7 =TOS|MARK!'.SPY121117C148' 8 =TOS|MARK!'.SPY121117C149' 9 =TOS|MARK!'.SPY121117C160' 10 =TOS|MARK!'.SPY121117C161' [b]Sub StopDDE()[/b] Range("A1:A10").Select With Selection.Font .ThemeColor = xlThemeColorDark1 '''Turn Font White .TintAndShade = 0 End With Selection.Replace What:="=", Replacement:="'=" '''Change the Formula to a Comment Range("A1").Select [b]End Sub[/b] [b]Sub StartDDE()[/b] Dim i As Integer Range("A1:A10").Select Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats Application.CutCopyMode = False For i = 1 To 10 Cells(i, 1).Select ActiveCell.Formula = Cells(i, 1).Value '''Activate the DDE Formula Next Range("A1:A10").Select With Selection.Font .ColorIndex = xlAutomatic '''Change Font Color to Black .TintAndShade = 0 End With Range("A1").Select [b]End Sub[/b]
Dynamic Data Exchange (DDE) with Thinkorswim and Excel See the attached spreadsheet for a Quick and easy way to use DDE with Thinkorswim and Excel.
Excel Macros to turn formulas on/off start/stop label/formula enable/disable Code: [b]Sub FormulaON()[/b] Application.ScreenUpdating = False '''Makes the code run a lot faster Application.Calculation = xlManual Range("A1:A5").Select '''Cells containing the formulas With Selection .Font.ColorIndex = xlAutomatic '''Optional - Show the formula .Formula = .Formula End With Application.ScreenUpdating = True Application.Calculation = xlAutomatic Range("A1").Select [b]End Sub[/b] [b]Sub FormulaOFF()[/b] Application.ScreenUpdating = False Application.Calculation = xlManual Range("A1:A5").Select With Selection .Font.ColorIndex = 2 '''Optional - Hide the formula (white font) .Replace What:="=", Replacement:="'=" 'ââReplacement:=double quote, single quote, equal sign, double quote End With Application.ScreenUpdating = True Application.Calculation = xlAutomatic Range("A1").Select [b]End Sub[/b]
Remove apostrophe from beginning of formula The attached spreadsheet might help if your DDE shows formulas instead of results.
I would like to thank all prior postersfor bringing this content forward, this is some quality content. It should be possible to add text to column steps to any of these macros. Millerd1