none
Selection.Areas(x).Formula = Selection.Areas(x).Value2 - Not working in C# RRS feed

  • Question

  • Hi,

    While a simple VBA macro like the following helps me a lot in having the formulas be converted to values in case of multiple selections in a single go, while attempting to switch over to C# now, I am having trouble with this very first attempt.

    Sub Test()
        For x = 1 To Selection.Areas.Count
            Selection.Areas(x).Formula = Selection.Areas(x).Value2
        Next
    End Sub

    In VSTO

            private void BtnF2V_Click(object sender, RibbonControlEventArgs e)
            {
                Microsoft.Office.Interop.Excel.Application XL = Globals.ThisAddIn.Application;
                Range TotalRange = XL.Selection;
                for (int RegionNumber = 0; RegionNumber <= TotalRange.Areas.Count; RegionNumber++)
                {
                    TotalRange.Areas[RegionNumber].Formula = TotalRange.Areas[RegionNumber].Value2;
                }
            }

    I face the complaint:

    System.Runtime.InteropServices.COMException occurred
      HResult=0x800A03EC
      Message=Exception from HRESULT: 0x800A03EC
      Source=
      StackTrace:
    <Cannot evaluate the exception stack trace>

    with the line

    TotalRange.Areas[RegionNumber].Formula = TotalRange.Areas[RegionNumber].Value2;

    being highlighted.

    Any advice what I am doing wrong.

    Thanks

    Saturday, November 4, 2017 6:55 PM

Answers

  • Hi Faraz A.Qureshi,

    Please start the index from 1.

     for (int RegionNumber = 1; RegionNumber <= TotalRange.Areas.Count; RegionNumber++)
                {
                    TotalRange.Areas[RegionNumber].Formula = TotalRange.Areas[RegionNumber].Value2;
                }

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; 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.

    Monday, November 6, 2017 1:41 AM

All replies

  • Hi Faraz A.Qureshi,

    Please start the index from 1.

     for (int RegionNumber = 1; RegionNumber <= TotalRange.Areas.Count; RegionNumber++)
                {
                    TotalRange.Areas[RegionNumber].Formula = TotalRange.Areas[RegionNumber].Value2;
                }

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; 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.

    Monday, November 6, 2017 1:41 AM
  • Excellent!
    However, any special reason for starting from an index 1, while I had found at most of the places that the C# considers the very first one to be 0.
    THANKS AGAIN!

    Monday, November 6, 2017 11:16 AM