Help needed to fix VBA in an Excel BF price scanning tool

Discussion in 'General Discussion' started by Rich, Mar 29, 2016.

  1. Rich

    Rich Member

    I am digging into some old butterfly scanning and pricing tools in Excel that folks worked on a few years ago and am trying to update them to use RTD instead of DDE. I have a VB script that I found to convert all DDE formulas to RTD by doing a string replacement, but it is not working quite right yet. I've racked my brain on this for long enough and have not gotten it to work. It is time to ask for help. I need help from someone who knows VBA better than I do right now. I'm working on improving my VBA skills but haven't had the time to take a full course on it yet. If anyone has already solved this automated formulas conversion problem, I would appreciate you sharing it with me and others. I will attach the BF pricing worksheet with embedded converter code.

    There are a lot of cool tools available from the DDE days of TOS, so the issue of converting TOS RTD formula text automatically is still important. I am loath to convert these worksheets by hand since some have dozens or hundreds of DDE formulas. That's what programming is for. I appreciate help from someone currently more VBA savvy than me right now.

    The macro code for ConvertWorksheet is in the VBA code module Convert_DDE_2_RTD.

    Thank you.

    Rich Lysakowski

    Here's the original post for the conversion code. I get errors in the ConvertFormulas sub. These instructions are not complete.
    Jack Webster Says:
    April 13, 2014 at 3:04 pm

    Here is some interesting information from TOS.
    Hello Jack,

    Thanks for the e-mail and kind words. In regards to your inquiry about DDE, we now support the Microsoft Excel function RTD(Real Time Data) which works in the same way.

    I could not attach the PDF file. I copied and pasted it. Contact TOS support for a copy.
    Jack Webster

    RTD on thinkorswim
    thinkorswim has the ability to use the Mircosoft Excel function RTD (Real Time Data.) This allows you to connect your Excel spreadsheet to thinkorswim for real time quote updates. To use this feature on thinkorswim bring up a watchlist with your desired symbols and columns. Then simply click on the printer icon in the top right of the list and choose Export to Excel.
    Pasting Visual Basic script to update to RTD
    If you have previously saved spreadsheets that use DDE you can update your spreadsheet to RTD. The following steps will show you how to run a Visual Basic script to perform this update. You may want to save your old DDE spreadsheet as a new document in case you want to reference the original document.
    Start by copying this script as we will need to paste it in Step 3
    Sub ConvertFormulas()
    Dim ws As Worksheet
    Dim rng As Range
    For Each Sheet In Worksheets
    ConvertWorksheet (Sheet)
    Next Sheet
    End Sub
    Function ConvertWorksheet(ByVal sht As Worksheet)
    On Error Resume Next
    For Each rng In sht.Cells.SpecialCells(xlCellTypeFormulas)
    Dim text As String
    Dim price As String
    Dim symbol As String
    Dim tosPrefix As String
    Dim separatorPosition As Integer
    Dim exclaimPos As Integer
    Dim isTOS As String
    text = rng.Formula
    isTOS = InStr(text, “=TOS”)
    separatorPosition = InStr(text, “|”)
    exclaimPos = InStr(text, “!”)
    If (separatorPosition > 0 And exclaimPos > 0 And isTOS > 0) Then
    tosPrefix = Left(text, InStr(text, “|”))
    price = Mid(text, InStr(text, “|”) + 1, exclaimPos – separatorPosition – 1)
    symbol = Right(text, Len(text) – exclaimPos)
    Dim result As String
    result = “=RTD(“”TOS.RTD””,,””” + Ucase(price) + “””,””” + Ucase(symbol) + “””)”
    rng.Formula = result
    End If
    Next rng
    End Function

    You will need the Developer Tab to perform this task. To show the Developer tab click on File and choose options. From the Excel Options1 window click on Customize Ribbon2 and check Developer3 in the Main Tabs section.
    1. Now you need to paste the Visual Basic script into a VBA Project. To start this click on the newly added Developer Tab1 and then click the Visual Basic button2.

    2. You need to paste the script into a specific location in Visual basic. Click on the ThisWorkbook Project1 in the right hand panel. This will bring up new VB (code) box2 where you want to paste your script. Select all of the default code in this box3 and paste your copied script.
    Now run this script to update your DDE functions for the entire workbook. Click on the Run Menu1 at the top and chose Run. This will bring you to the Macros2 window where you will click on ThisWorkbook.ConvertFormulas and choose Run3.

    You can close down the Visual Basic window and you will see your new updated spreadsheet. From here you can save the spreadsheet and use it as you have used DDE in the past.

    Attached Files:

  2. Robertf

    Robertf New Member

    Still need help with this? I've converted a couple spreadsheets from DDE to RTD so have some experience with that.


Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice