Quantcast
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

TOS RTD with Excel 2016

Discussion in 'Options' started by Rahul Sarin, Nov 25, 2015.

  1. Rahul Sarin

    Rahul Sarin Guest

    Hey guys,
    I just noticed that my TOS RTD functions don't work with Excel 2016. They do however work with Excel 2010 and 2013.
    I think this is a know issue which TD is aware of.
    Anyone has a fix or workaround?

    Thanks,

    Rahul
     
  2. David Stewart

    David Stewart Well-Known Member

    Are you using Excel on a windows computer or a Macbook?
     
  3. Rahul Sarin

    Rahul Sarin Guest

    Windows.
    TOS RTD never worked with Mac version of Excel anyway.
     
  4. David Stewart

    David Stewart Well-Known Member

    Yes, Rahul, you are correct as that was my experience. That plus OptionVue was the original reason I added Parallels to my Macbook Pro. I just wasn't sure if you were doing something that I was not aware of. I didn't even know there was a new Excel. I think I still am using the Excel 13 version. And by the way not surprised RTD not working again as I know there have been problems in the past. We will see if anyone else can further inform us.
     
  5. Rahul Sarin

    Rahul Sarin Guest

    I have office 365 subscription... so my Excel 2013 updated to Excel 2016 by default.

    I called TD and they are aware of if. Office 2016 was officially released for windows 2 months back... TD still hasn't fixed the issue.
    There is no way for me to go back to Excel 2010 / 2013.
     
  6. David Stewart

    David Stewart Well-Known Member

    geez, I'm a dummy, I have the new release of Excel also, just didn't remember it updating :)
     
  7. Kevin Lee

    Kevin Lee Well-Known Member

    Wow... thanx. Good to know. I'm not upgrading to Excel 2016 then.
     
  8. Rahul Sarin

    Rahul Sarin Guest

    Guys I fixed the issue. There seems to be some issue on RTD function by MS and TD cannot do anything about it.
    I wrote this code which you paste in a new module in your file and run it once. It will fix the issue for Excel 2016.
    I cannot seem to paste code in this editor, so I am pasting a screenshot.
    upload_2015-11-25_11-22-49.png
     
    Timo and tom like this.
  9. GreenZone

    GreenZone Well-Known Member

    I've just upgraded to Office 2016, and have started testing out RTD functionality

    It seems to be working fine, as is, but only on *new* spreadsheets:
    Excel2016-RTD.jpg

    Excel-2016-version.jpg

    I then tried opening an existing spreadsheet which has RTD data in it, and it's not working.
    I type the exact same formula into both spreadsheets, and it works in the new spreadsheet but not in the existing one.
    I'll continue testing later and let you know my progress.
     
    Last edited: Nov 26, 2015
    Timo likes this.
  10. GreenZone

    GreenZone Well-Known Member

    Rahul, I tried adding your Excel code and running it.

    I got this error:
    error.jpg

    When I debug the script, it's showing an issue here:
    debug.jpg

    Any ideas on what's wrong ?
     
  11. David Stewart

    David Stewart Well-Known Member

    Wow, you guys are doing great work, above my pay grade. Let me know when you figure it out.
     
    Mark17 likes this.
  12. Rahul Sarin

    Rahul Sarin Guest

    Ron,
    Before this line, but the following code:
    On Error Resume Next

    This is happening cause one of your worksheets in the workbook doesn't have any formulae.
    upload_2015-11-27_12-4-59.png
     
  13. GreenZone

    GreenZone Well-Known Member

    I've added the extra code you suggested.
    It's now no longer giving me an error when I run the sub, but it's also not resulting in re-enabling the RTD functionality.
    Here's the test Excel 2016 file, which also includes the sub.
     

    Attached Files:

  14. tom

    tom Administrator Staff Member

    This is the code Ron gave me to paste into this forum thread:

    Code:
        With [Application]
            .EnableEvents = False
            .ScreenUpdating = False
        End With
       
        For Each [wks] In ThisWorkbook.Worksheets
            On Error Resume Next
            Set [rngFormula] = [wks].Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
            If [rngFormula] Is Nothing Then: Exit Sub
            For Each [cell] In [rngFormula]
                If InStr(1, [cell].Formula, "RTD") > 0 Then
                    [cell].Formula = Replace([cell].Formula, ", ,", ",""tos.rtd"",", 1)
                End If
            Next [cell]
            [wks].Calculate
        Next [wks]
       
        Set [rngFormula] = Nothing
       
        With [Application]
            .EnableEvents = True
            .ScreenUpdating = True
        End With
           
    End Sub
    To insert code safely, use the "Insert" button, and choose "Code"
    2015-11-28_12-47-48.png
     
  15. Rahul Sarin

    Rahul Sarin Guest

    Ron,
    I noticed your formula was slightly different from mine. Issue fixed for both types.
    Updated file enclosed.
     

    Attached Files:

    GreenZone likes this.
  16. Rahul Sarin

    Rahul Sarin Guest

    Tom, please paste this code instead in the forum thread. For some reason I don't have privileges to upload code.
    I have attached a text file with the code and also pasted an image here.

    upload_2015-11-28_10-29-20.png
     

    Attached Files:

  17. GreenZone

    GreenZone Well-Known Member

    Rahul, that updated code is now working perfectly.
    Thank you !!!
    I'll try to create a video explaining how to apply these steps in Excel, targeted to people who may not be familiar with Excel macros.
     
  18. Rahul Sarin

    Rahul Sarin Guest

    Great. You are welcome.
     
  19. GreenZone

    GreenZone Well-Known Member

    Here's the code from Rahul:

    Code:
    '..... Code Developed by Rahul Sarin to Fix Excel 2016 RTD issue with TOS........
    Public Sub UpdateRTDFunction()
        Dim wks As Worksheet
        Dim rngFormula As Range, cell As Range
      
        With [Application]
            .EnableEvents = False
            .ScreenUpdating = False
        End With
      
        For Each [wks] In ThisWorkbook.Worksheets
            On Error Resume Next
            Set [rngFormula] = [wks].Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
            If [rngFormula] Is Nothing Then: Exit Sub
            For Each [cell] In [rngFormula]
                If InStr(1, [cell].Formula, "RTD") > 0 Then
                    If InStr(1, [cell].Formula, ", ,") > 0 Then
                        [cell].Formula = Replace([cell].Formula, ", ,", ",""tos.rtd"",", 1)
                    Else
                        [cell].Formula = Replace([cell].Formula, ",,", ",""tos.rtd"",", 1)
                    End If
                End If
            Next [cell]
            [wks].Calculate
        Next [wks]
      
        Set [rngFormula] = Nothing
      
        With [Application]
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    '...................................................................................
    
     
  20. Rahul Sarin

    Rahul Sarin Guest

    Quick update.
    After implementing the code written above, your TOS RTD file will work with Excel 2010 / 2013 and 2016 without further modifications.
     

Share This Page