Dynamic Excel DDE link to TOS (ThinkOrSwim)

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.
 

Attachments

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]
 
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
 
You must log in or register to reply here.
Back
Top