none
Problem with setting PlotArea.Left in Excel 2016 RRS feed

  • Question

  • We are developing an addin for Excel and are trying to support the newly released Excel 2016.

    Most of our existing logic works out of the box, however, we have a problem with the logic that inserts a new chart. When we try to change the PlotArea's Left property on a newly inserted chart an exception occurs. This only happens when using Excel 2016 (the same code works just fine in Excel 2010 and 2013). This is the code that triggers the exception:

    chart.PlotArea.Left = -4;

    The exception that occurs is:

    System.Runtime.InteropServices.COMException was caught
      HResult=-2147467259
      Message=Unknown error (Exception from HRESULT: 0x80004005 (E_FAIL))
      Source=""
      ErrorCode=-2147467259
      StackTrace:
           at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
           at Microsoft.Office.Interop.Excel.PlotArea.set_Left(Double value)
           at ZebraBI.VisualizationLogic.Chart.SetPlotArea() in c:\Users\mitja.bezensek\Documents\Visual Studio 2012\Projects\ZebraBI\ZebraBI\src\VisualizationLogic\Chart.cs:line 552
           at ZebraBI.VisualizationLogic.Chart.FinalizeChart() in c:\Users\mitja.bezensek\Documents\Visual Studio 2012\Projects\ZebraBI\ZebraBI\src\VisualizationLogic\Chart.cs:line 530
           at ZebraBI.VisualizationLogic.PlusMinusChart.Plot() in c:\Users\mitja.bezensek\Documents\Visual Studio 2012\Projects\ZebraBI\ZebraBI\src\VisualizationLogic\PlusMinusChart.cs:line 41
           at ZebraBI.Controller.Controller.InsertAChart(ChartTask chartTask, ChartShape chartShape, ChartOrientation chartOrientation, Boolean absolute, ChartOptions chartOptions, VisualizationData visualizationData, Style style, Object fullSelection, List`1 chartNames) in c:\Users\mitja.bezensek\Documents\Visual Studio 2012\Projects\ZebraBI\ZebraBI\src\Controller\Controller.cs:line 78
      InnerException: 

    When debugging I noticed that if I mouse over the property before it gets assigned then the code works normally. Which lead to trying to read the property before assigning a value to it:

    var temp = chart.PlotArea.Left;
    chart.PlotArea.Left = -4;

    This works. 

    Settings this property does not trigger an exception in all cases. The one case that is problematic is if we try to assign a property soon after programmatically inserting a chart. If I insert a chart from the ribbon and then assign the property programmaticaly this error does not occur.

    Is this a known issue in Excel 2016? 

    EDIT: It seems that all of the following properties have the same issue: Left, Top, Width, Height.

    Friday, September 25, 2015 3:35 PM

Answers

  • Hi Mitja, to close the loop on this issue, we will be tracking a fix for this bug in a future version of Office.  In the meantime you can use the workarounds mentioned on this post. 

    This simplest and least impactful one might be to call DoEvents 3 times before calling PlotArea.*

    Regards,
    - Reza (MSFT)


    Reza Chitsaz (MSFT)

    Thursday, February 4, 2016 1:09 AM

All replies

  • Hi Mitja,

    I made a test with your description, and I could reproduce your issue. It seems an issue with Excel 2016, and I suggest you submit a feedback by clicking the smile face in the right top corner of Excel.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, September 28, 2015 9:56 AM
  • Thanks for testing this out.

    I do not have the smiley icon on the top right corner. It seems that it was removed as the release was getting close (I have the full version already). I also cannot find File->Feedback option, that was mentioned as an alternative.

    Is there another way to send the information about this problem?


    Monday, September 28, 2015 10:50 AM
  • This fails with PlotArea.Left, .Top, .Width, and .Height

    The following defensive coding avoids the error, but it means lots of existing working code needs to be rewritten:

      On Error Resume Next
      c.PlotArea.Left = -4
      If Err.Number <> 0 Then
        Debug.Print "Error " & Err.Number & ": " & Err.Description
        '' Error -2147467259: Method 'Left' of object 'PlotArea' failed
        c.PlotArea.Left = -4
      End If
      On Error GoTo 0
    
    I've sent a frown.


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    • Proposed as answer by Tony---- Tuesday, September 29, 2015 3:34 AM
    Monday, September 28, 2015 8:26 PM
  • Any update on this?
    Thursday, January 28, 2016 3:04 PM
  • Mitja -

    As you know, it's still an issue. I've replicated the problem with .PlotArea.Left, .Top, .Width, and .Height. I've avoided it with the workaround posted months ago, with three DoEvents before the offending line (one or two aren't enough), and by doing anything else in the chart, including something as simple as 

    Debug.Print .PlotArea.Left

    Do you find the problem with any other chart elements or other code?


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Thursday, January 28, 2016 5:53 PM
  • I've avoided it with the reading the property first, then setting it, like I described in the opening post. I was just interested if there was any progress on this issue - is there a plan to fix it or not, when can we expect a solution.
    Thursday, January 28, 2016 6:00 PM
  • Hello Mitja, I am a program manager on the Excel team at Microsoft and can repro the issue you're running into, but I also see the same issue when I run the code in Office 2010 and in Office 2013. 

    Applying the workaround of adding three DoEvents before the .PlotArea.Left call does workaround the issue for all versions of Office.

    Can you please specify the environment (Office and Windows, versions and platforms) where the same code is running fine for you?

    Thank you,
    Reza Chitsaz
    Program Manager - MSFT


    Reza Chitsaz (MSFT)

    Friday, January 29, 2016 10:11 PM
  • I have Excel 2010, Excel 2013, Excel 2016 64 bit installed on Windows 10 64 bit machine. Before I upgraded to Windows 10 I was on Windows 7 with 32 bit version of Excel 2010 and Excel 2013.  Of all those configurations only the one with Excel 64 bit has this problem.

    We didn't have the above-mentioned workaround in place in our add-in until our customers started reporting problems when installing it in Excel 2016. So, at least from my experience, this is limited to Excel 2016.


    Saturday, January 30, 2016 3:28 PM
  • I've replicated the problem in 32-bit Excel 2010 and 2016, in 64-bit Windows 10.

    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Saturday, January 30, 2016 3:35 PM
  • Jon has this always been present for you, or was it added with one of the office updates?
    Saturday, January 30, 2016 3:40 PM
  • I never noticed it until I saw this thread. Usually changing the plot area size is the last thing my code does, so I would not have encountered it.

    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Saturday, January 30, 2016 3:43 PM
  • Hi Mitja, to close the loop on this issue, we will be tracking a fix for this bug in a future version of Office.  In the meantime you can use the workarounds mentioned on this post. 

    This simplest and least impactful one might be to call DoEvents 3 times before calling PlotArea.*

    Regards,
    - Reza (MSFT)


    Reza Chitsaz (MSFT)

    Thursday, February 4, 2016 1:09 AM