Loop through cells in range

Add-in Express™ Support Service
That's what is more important than anything else

Loop through cells in range
 
ewessely




Posts: 55
 Joined: 2019-01-31
Hello,
I've to find formulas on a given worksheet. 


Range formulaRange = null;
Range usedRange = null;
...
usedRange = worksheet.UsedRange;
formulaRange = usedRange.SpecialCells(XlCellType.xlCellTypeFormulas, Type.Missing);
foreach (Range cell in formulaRange)
{
   //do something with the cell

   ....

   if (cell != null)
   {
       Marshal.ReleaseComObject(cell);
   }
}
if (usedRange != null)
{
    Marshal.ReleaseComObject(usedRange);
}
if (formulaRange != null)
{
    Marshal.ReleaseComObject(formulaRange);
}


But following the articel https://www.add-in-express.com/creating-addins-blog/2013/11/05/release-excel-com-objects/ we should not use a foreach loop.

I googled a lot but was not able to convert this into a for loop.
The COM object formulaRage.Count gives correctly the number of found formulas, but how can I iterate them?
Using the formulaRange.Cells and also FormulaRange.Cells.Item needs a rowindex and columnindex.

Or is it in this case safe to use a foreach?

Thanks for suggestions

Erich
ew
Posted 30 Nov, 2020 05:50:54 Top
Andrei Smolin


Add-in Express team


Posts: 18826
 Joined: 2006-05-11
Hello Erich,

_Worksheet is Worksheet minus events. 

Excel._Worksheet worksheet = ExcelApp.ActiveSheet as Excel._Worksheet;
Excel.Range formulaRange = null;
Excel.Range usedRange = worksheet.UsedRange;
formulaRange = usedRange.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Type.Missing);
for (int i = 1; i <= formulaRange.Count; i++)
{
    Excel.Range cell = formulaRange[i] as Excel.Range;

    if (cell != null)
    {
        System.Diagnostics.Debug.WriteLine("!!! " + cell.Address);
        Marshal.ReleaseComObject(cell);
    }
}
if (usedRange != null)
{
    Marshal.ReleaseComObject(usedRange);
}
if (formulaRange != null)
{
    Marshal.ReleaseComObject(formulaRange);
}



Andrei Smolin
Add-in Express Team Leader
Posted 30 Nov, 2020 08:22:32 Top
ewessely




Posts: 55
 Joined: 2019-01-31
Hello Andrej,
many thanks for the prompt reply.
I allready tried this, but I got an invalid result.
Steps to reproduce:

Open a new blank worksheet
Put 2 values on the sheet
in A5: 1
in A6: 1

Enter formula in A1:
=SUM(A5:A6)
Correct Result:
!!! $A$1

Enter additional formula in B1:
=SUM(A5:A6)
Correct Result:
!!! $A$1
!!! $B$1

Enter a further formula in D1:
=SUM(A5:A6)
WRONG Result:
!!! $A$1
!!! $B$1
red

Doing it with foreach is giving a correct result
!!! $A$1
!!! $B$1
!!! green$1

Any ideas?

BR
Erich
ew
Posted 30 Nov, 2020 11:26:10 Top
Andrei Smolin


Add-in Express team


Posts: 18826
 Joined: 2006-05-11
Hello Erich,

I've reproduced this issue.

It looks like a bug in the SpecialCells method. It constructs the range so that using a FOR loop produces a wrong cell.

Your only way is to use a FOREACH loop and make these calls when you're done with that range or with the method that uses that range:

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()


Andrei Smolin
Add-in Express Team Leader
Posted 01 Dec, 2020 07:03:57 Top
ewessely




Posts: 55
 Joined: 2019-01-31
Hello Andrej,

thanks for beeing able to reproduce, so I'm not alone with that.
Thank you also for your suggestion with the GC

Best Regards

Erich
ew
Posted 01 Dec, 2020 08:17:48 Top