none
The Charts.Add method brakes R1C1-style notation references for Range objects RRS feed

  • Question

  • Hi,

    I wrote a piece of code that was supposed to loop through certain items and add a chart sheet for each of them. I noticed that after a chart sheet was added to the workbook, the R1C1-style notation references for formulas would point to incorrect cells. Is this a bug in Excel or am I doing something wrong?

    Here is a sample code which should allow you to reproduce the issue. The first loop adds worksheet type sheets to show that the references remain correct. The second loop adds one row of cell formulas (which are correct) and then, with every subsequent iteration, adds a chart sheet type before adding cell formulas - this is from this point that the cell references get mixed up.

    Option Explicit
    
    Sub ChartsCellRefTest()
        
        Dim wkbRefTest          As Excel.Workbook
        Dim wksRefTest          As Excel.Worksheet
        Dim lSheetCount         As Long
        Dim bytCount            As Byte
        
        Set wkbRefTest = ThisWorkbook.Parent.Workbooks.Add
        Set wksRefTest = wkbRefTest.Worksheets(1)
        wksRefTest.Name = "WorksheetsAdd"
        
        For bytCount = 0 To 2 Step 1
            With wksRefTest
                .Parent.Worksheets.Add
                .Range("A2").Offset(bytCount, 0).Value = bytCount
                .Range("B2").Offset(bytCount, 0).FormulaR1C1 = "=RC[-1]"
                .Range("C2").Offset(bytCount, 0).FormulaR1C1 = "=RC[1]"
                .Range("D2").Offset(bytCount, 0).FormulaR1C1 = "=R[1]C"
                .Range("E2").Offset(bytCount, 0).FormulaR1C1 = "=R[-1]C"
                .Range("F2").Offset(bytCount, 0).FormulaR1C1 = "=R[1]C[1]"
                .Range("G2").Offset(bytCount, 0).FormulaR1C1 = "=R[-1]C[-1]"
            End With
        Next bytCount
        
        Set wksRefTest = wkbRefTest.Worksheets.Add
        wksRefTest.Name = "ChartsAdd"
        
        For bytCount = 0 To 2 Step 1
            With wksRefTest
                If bytCount > 0 Then .Parent.Charts.Add
                .Range("A2").Offset(bytCount, 0).Value = bytCount
                .Range("B2").Offset(bytCount, 0).FormulaR1C1 = "=RC[-1]"
                .Range("C2").Offset(bytCount, 0).FormulaR1C1 = "=RC[1]"
                .Range("D2").Offset(bytCount, 0).FormulaR1C1 = "=R[1]C"
                .Range("E2").Offset(bytCount, 0).FormulaR1C1 = "=R[-1]C"
                .Range("F2").Offset(bytCount, 0).FormulaR1C1 = "=R[1]C[1]"
                .Range("G2").Offset(bytCount, 0).FormulaR1C1 = "=R[-1]C[-1]"
            End With
        Next bytCount
        
    End Sub


    -- Igor M.



    • Edited by Igor-M Tuesday, September 12, 2017 4:03 PM
    Tuesday, September 12, 2017 3:40 PM

Answers

  • Hi Igor-M,

    Great! I have test as you said selecting the "ChartAdd" sheet after adding a chart, and then the issue is resolved. Thanks for sharing solution. I would suggest you mark your solution to help other developers use this forum efficiently. Thanks for your understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Igor-M Thursday, September 13, 2018 9:04 PM
    Friday, September 29, 2017 8:36 AM

All replies

  • Hi IgorMI5,

    I could reproduce your issue. I would suggest you go to File->Feedback to make a feedback.

    Ask a workaround for your issue, you could set cells formula directly like this.

    .Range("A2").Offset(0, 1).Formula = "=" & ws.Range("A2").Offset(0, 1).Offset(0, -1).Address

    Best Regards,

    Terry

    Wednesday, September 13, 2017 7:18 AM
  • Thank you Terry!

    I've reported this issue using the feedback option. In the meantime, I noticed that if a worksheet (any worksheet type sheet in the workbook) is either selected or activated after a chart sheet had been added and before a R1C1 formula is inserted, the references are correct.

    Kind regards,


    -- Igor M.


    Wednesday, September 27, 2017 8:00 PM
  • Hi Igor-M,

    Great! I have test as you said selecting the "ChartAdd" sheet after adding a chart, and then the issue is resolved. Thanks for sharing solution. I would suggest you mark your solution to help other developers use this forum efficiently. Thanks for your understanding.

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Igor-M Thursday, September 13, 2018 9:04 PM
    Friday, September 29, 2017 8:36 AM