[ACCEPTED]-Excel Interop - Efficiency and performance-vsto

When using C# or VB.Net to either get or 10 set a range, figure out what the total size 9 of the range is, and then get one large 8 2 dimensional object array...

//get values
object[,] objectArray = shtName.get_Range("A1:Z100").Value2;
iFace = Convert.ToInt32(objectArray[1,1]);

//set values
object[,] objectArray = new object[3,1] {{"A"}{"B"}{"C"}};
rngName.Value2 = objectArray;

Note that its 7 important you know what datatype Excel is 6 storing (text or numbers) as it won't automatically 5 do this for you when you are converting 4 the type back from the object array. Add 3 tests if necessary to validate the data 2 if you can't be sure beforehand of the type 1 of data.

This is for anyone wondering what the best 20 way is to populate an excel sheet from a 19 db result set. This is not meant to be a 18 full list by any means but it does list 17 a few options.

Some performance numbers while 16 attempting to populate an excel sheet with 15 155 columns and 4200 records on an old Pentium 14 4 3GHz box including data retrieval time 13 which was never more than 10 seconds in 12 order of slowest to fastest is as follows...

1. One 11 cell at a time - Just under 11 minutes

2. Populating a dataset by 10 converting to html + Saving html to disk 9 + Loading html into excel and saving worksheet 8 as xls/xlsx - 5 minutes

3. One column at a time - 4 minutes

4. Using 7 the deprecated sp_makewebtask procedure 6 in SQL 2005 to create an HTML file - 9 Seconds 5 + Followed by loading the html file in excel 4 and saving as XLS/XLSX - About 2 minutes.

5. Convert .Net dataset 3 to ADO RecordSet and use the WorkSheet.Range[].CopyFromRecordset 2 function to populate excel - 45 seconds!

I ended up 1 using option 5. Hope this helps.

If you're polling values of many cells you 8 can get all the cell values in a range stored 7 in a variant array in one fell swoop:

Dim CellVals() as Variant
CellVals = Range("A1:B1000").Value

There 6 is a tradeoff here, in terms of the size 5 of the range you're getting values for. I'd 4 guess if you need a thousand or more cell 3 values this is probably faster than just 2 looping through different cells and polling 1 the values.

Use excels builtin functionality whenever 5 possible, for example: Instead of searching 4 a whole column for a given string, use the 3 find command available in the GUI by Ctrl-F:

Set Found = Cells.Find(What:=SearchString, LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not Found Is Nothing Then
    Found.Activate
    (...)
EndIf

If 2 you want to sort some lists, use the excel 1 sort command, don't do it manually in VBA:

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Performance also depends a lot on how you 11 automate Excel. VBA is faster than COM automation 10 is faster than .NET automation. And typically 9 early (compile time) binding is faster than 8 late binding, too.

If you have serious performance 7 problems you could think of moving the critical 6 parts of the code to a VBA module and call 5 that code from your COM/.NET automation 4 code.

If you use .NET you should also use 3 the optimized primary interop assemblies 2 available from Microsoft and not use custom-built 1 interop assemblies.

As Anonymous Type says: reading/writing 9 large range blocks is very important to 8 performance.

In cases where the COM-Interop 7 overhead is still too large you may want 6 to switch to using the XLL interface, which 5 is the fastest Excel interface.

Although 4 the XLL interface is primarily meant for 3 C++ users, both XL DNA and Addin Express 2 provide .NET to XLL bridge capability which 1 is significantly faster than COM-Interop.

Another big thing you can do in VBA is to 26 use Option Explicit and avoid Variants wherever 25 possible. Variants are not 100% avoidable 24 in VBA, but they make the interpreter do 23 more work at runtime and waste memory.

I 22 found this article very helpful when I was 21 starting with VBA in Excel.
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

And this book

http://www.amazon.com/VB-VBA-Nutshell-Language-OReilly/dp/1565923588

Similar 20 to

 app.ScreenUpdates = false //and
 app.Calculation = xlCalculationManual

you can also set

 app.EnableEvents = false //Prevent Excel events
 app.Interactive = false  //Prevent user clicks and keystrokes

although they don't 19 seem to make as big a difference as the 18 first two.

Similar to setting Range values 17 to arrays, if you are working with data 16 that is mostly tables with the same formula 15 in every row of a column, you can use R1C1 14 formula notation for your formula and set 13 an entire column equal to the formula string 12 to set the whole thing in one call.

app.ReferenceStyle = xlR1C1
app.ActiveSheet.Columns(2) = "=SUBSTITUTE(C[-1],"foo","bar")"

Also, creating 11 XLL add-ins using ExcelDNA & .NET (or 10 the hard way in C) is also the only way 9 you can get UDFs to run on multiple threads. (See 8 Excel DNA's ExcelFunction attribute's IsThreadSafe 7 property.)

Before I transitioned to Excel 6 DNA completely, I also experimented with 5 creating COM visible libraries in .NET to 4 reference in VBA projects. Heavy text processing 3 is a bit faster than VBA that way, as are 2 using wrapped .NET List classes instead 1 of VBA's Collection, but Excel DNA is better.