What is the best way to copy an Excel formula to a large range?

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

What is the best way to copy an Excel formula to a large range?
 
bttfa




Posts: 3
Joined: 2010-02-10
My worksheet has two columns of numbers. I want to sum the numbers for each row in column C.

Doing this manually, I would enter "=sum(a1:b1)" in cell C1 and then copy it down the column (by double-clicking the black rectangle in the lower right corner of the cell). My worksheet has 200,000 rows of data, and Excel took less than 2 seconds to display the results. However, I am struggling with how to do this via code (takes about 1 minute).


Dim wbCount As Integer = ExcelApp.Workbooks.Count 'how many workBOOKS are open?
For i = 1 To wbCount 'step through each WorkBOOK
  Dim wbi As Excel.Workbook = CType(Me.ExcelApp.Workbooks(i), Excel.Workbook)
  Dim wsCount As Integer = wbi.Worksheets.Count 'how many workSHEETS are in this workBOOK?
  For j = 1 To wsCount 'step through each WorkSHEET of this workBOOK.
    Dim wsj As Excel.Worksheet = CType(wbi.Worksheets(j), Excel.Worksheet)
    For k = 1 To 200000
      Dim wsCell As Excel.Range = CType(wsj.Cells(k, 3), Excel.Range)
      wsCell.Formula = "=sum(RC[-2]:RC[-1])"
      Marshal.ReleaseComObject(wsCell)
    Next
  Next
Next


I presume the instantiation of wsCell (and release) is the bottleneck.

What is the best technique (fastest) to calculate the sums I want in column C? The numbers in columns A and B are static, so I do not need to use a formula (summing in VB and setting the Value property is fine). I presumed a ForEach loop on a range (C1:C200000) would work, but I failed.

I want my Addin to work with Excel 2000, XP, and up.

Microsoft Visual Studio 2008, Version 9.0.30729.1 SP
Microsoft .NET Framework, Version 3.5 SP1
Add-in Express™ 2008 for .NET v4.4.1913
Posted 10 Feb, 2010 18:04:00 Top
Andrei Smolin


Add-in Express team


Posts: 17122
Joined: 2006-05-11
Yes, the bottleneck is picking up the cells one by one. Instead, you need to get an Excel.Range containing all the cells and assign an appropriately dimensioned array containing required values to Range.Formula or Range.Value.

    Private Sub AdxRibbonButton1_OnClick(ByVal sender As System.Object, ByVal control As AddinExpress.MSO.IRibbonControl, ByVal pressed As System.Boolean) Handles AdxRibbonButton1.OnClick
        Dim wbi As Excel.Workbook = Me.ExcelApp.ActiveWorkbook
        Dim wsj As Excel.Worksheet = CType(wbi.ActiveSheet, Excel.Worksheet)
        Dim n As Integer = 5
        Dim arr(n, 1) As Object
        For k = 0 To n - 1
            arr(k, 0) = "=sum(RC[-2]:RC[-1])"
        Next
        Dim r As Excel.Range = wsj.Range("C1", "C5")
        r.Formula = arr
    End Sub


Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Feb, 2010 08:10:58 Top
bttfa




Posts: 3
Joined: 2010-02-10
Andrei,

Thank you for your quick and professional reply. Your example code helped me very much. If you have a moment, I have two brief follow-up questions;

1) I presume I need to release the objects "wbi", "wsj" and "r" in your code above by adding "Marshal.ReleaseComObject(wbi)", etc. statements. Is this correct?

2) The array method you explained is taking about 24 seconds for my 200,000 rows (essentially all of the elapsed time is in line "r.Formula = arr") versus 2 seconds when I do it manualy in Excel as described above. Is this normally what you expect (12x)?

Thank you again.
Posted 11 Feb, 2010 10:35:50 Top
Andrei Smolin


Add-in Express team


Posts: 17122
Joined: 2006-05-11
bttfa writes:
1) I presume I need to release the objects "wbi", "wsj" and "r" in your code above by adding "Marshal.ReleaseComObject(wbi)", etc. statements. Is this correct?


This is more than correct. This is a must, if you write a trouble-free add-in. Just recently I've tested the following scenario:
a) a standalone application runs Excel via Automation and stores a reference to the just started Excel.Application
b) an add-in gets ExcelApp.ActiveWorkbook and doesn't release it
c) the user closes the Excel UI
d) the application releases the Excel.Application object

In that scenario, Excel hangs in the Task Manager window. Releasing the COM interface in step b) above allows Excel to quit smoothly.

bttfa writes:
2) The array method you explained is taking about 24 seconds for my 200,000 rows (essentially all of the elapsed time is in line "r.Formula = arr") versus 2 seconds when I do it manualy in Excel as described above. Is this normally what you expect (12x)?


I can't say that I expected that figure. But note that when you do this in the Excel UI, you deal with Excel internals that should be optimized for inserting the same formula into many cells.

Try creating an XLL add-in with the following function:

public static object[,] GetSum(object arg)
{
    if (!Module.IsInFunctionWizard)
    {
        if (arg is object[,])
        {
            object[,] arr = (object[,])arg;
            AddinExpress.MSO.ADXExcelRef caller = XLLContainer.Caller;
            object[,] output = new object[caller.RowLast - caller.RowFirst + 1, 1];
            for (int i = 0; i < output.GetLength(0); i++)
            {
                output[i, 0] = Convert.ToInt32(arr[i, 0]) + Convert.ToInt32(arr[i, 1]);
            }

            return output;
        }
    }
    return null;
}


That function expects a range containing just two columns and an arbitrary number of rows.

Regards from Belarus (GMT+2),

Andrei Smolin
Add-in Express Team Leader
Posted 15 Feb, 2010 10:08:55 Top