VSTO Slow Performance Compared to VBA

Question

I am new to stackoverflow therefore I will directly start with my question.

I have few year experience with developing macros for Excel via integrated VBA and half year ago I started learning new language C#. I liked it, it's possibilities and I have also found option to develop Office Plugins directly in C# via Visual Studio (VSTO required). Problem I have is that after doing multiple tests where I tried to compare speed of addin created using C# and macro created using VBA (e.g. paste string "Test String" to each cell from A1 to A10 000 individually). The problem is that integrated VBA was multiple times faster than mentioned Addin created using Visual Studio (C#). I decided not to bother at that time with this problem however later on I came to the same problem however this time instead of cca 10 seconds (VBA) process took more than 2 minutes!

After doing research on google I found out that VSTO is not good in terms of performance especially when you require your App to interact with Excel (therefore there are ways how to workaround the problem such as saving your data into dynamic Array and then directly populating whole array into activesheet); however there ARE cases when you simply NEED to interact with excel (like I had to Unprotect many sheets within workbook dynamically...and here you simply have to say ActiveSheet.Unprotect)

My question to you guys would be, is there any way except from slow VSTO, how to create (maybe some other software) Addins for Excel/Word/Other Office products that are easily deployable? Thank you very much.

Regards, Robert

"I will directly start with my question." Err... you have a funny way of starting directly with the question. The question is hidden right at the end. — Mark Byers
I started with "Hi all", however it does not show for some reason...maybe it auto-filters "unnecessary content" in order for users not to waste their time :D — Robert J.
I think the issue with anything other than "native" VBA macros is that the need to go across process boundaries creates a lot of overhead: hence the speedup when "batching" operations in VSTO (operating on a whole range at once or reading/putting an array to a worksheet only takes one call vs. potentially thousands when going cell-by-cell). — Tim Williams
Thank you guys for such a fast resposne! I know but as I said there are situations when you simply have to interact with Excel (as I said unprotecting worksheets in workbook, etc..). Also @MarkByers I didn't say I cannot use VBA, I actually do use it a lot, however when I want to create solution/addin and I want to redistribute it to clients, it is not as easy as via VSTO (which after publishing gives you install file which you simply run and you don't care about anything else). I don't know how to do this via VBA (customizing Ribbon panel, programatically adding code to PERSONAL.XLS, etc) — Robert J.

Jeremy Thompson Jeremy Thompson · Accepted Answer · 2012-11-09T04:36:39

Populating 10000 cells individually is making you think VSTO is extremely slow. I can do it in a fraction of that time with the Dynamic Array method you mention:

static public void PopulateABigNumberOfCells(Excel.Application xlApp, DataTable dataTable)
{
//Turn off Excel updating
SwitchApplicationSettings(xlApp,false);

//Populate a 2D array - via a DataTable in this example
object[,] values = (object[,])Array.CreateInstance(typeof(object), new int[2] { dataTable.Rows.Count, dataTable.Columns.Count }, new int[2] { 1, 1 });
for (int i = 0; i < dataTablea.Rows.Count; i++)
{
 for (int j = 0; j < dataTable.Columns.Count; j++)
 {
  values[i + 1, j + 1] = dataTable.Rows[i][j] == DBNull.Value ? 0 : dataTable.Rows[i][j];
 }
}

//Populate all cells in one swoop 
leftCellNum = XlHelper.ColumnNameToNumber(leftColumn);
string rightBottom = XlHelper.ColumnNumberToName(leftCellNum + dataTable.Columns.Count - 1);
using (var targetRange = xlApp.Range[leftColumn + (startingRow) + ":" + rightBottom + (startingRow + dataTable.Rows.Count - 1)].WithComCleanup())
{
targetRange.Resource.Value2 = values;
}

//Turn on Excel updating
SwitchApplicationSettings(xlApp,true);
}

static public void SwitchApplicationSettings(Excel.Application xlApp, bool on)
{
xlApp.ScreenUpdating = on;
xlApp.DisplayAlerts = on;
xlApp.Calculation = on ? XlCalculation.xlCalculationAutomatic : XlCalculation.xlCalculationManual;;
xlApp.UserControl = on;
xlApp.EnableEvents = on;
}

I use VSTO Contrib for better memory mgt.

Using the Object Model is not 12 times slower than VBA unless your talking specifically about populating thousands of cells individually.

VSTO Slow Performance Compared to VBA

2 Answers