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

Answer 1

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.

Answer 2

The performance problem with VSTO-Excel is caused by the need to add the Interop layer on top of the Com interface to Excel that VBA/VB6 use. So VSTO interaction with Excel using this method will always be slow.

It is possible to use the XLL interface with C# using products such as Excel DNA (free) and Addin Express (not free). Using this interface will be fast, but its more limited than the Com interface.

Here is one comparison of the various technologies for developing UDFs.

Distributing VBA code is is easy - either create an Addin (XLA/XLAM) that functions at an application level or distribute a workbook with embedded VBA code for a document-specific solution.