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

That's what is more important than anything else

 Forums    Topics    Search    Help    Login    Register
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

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

 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