Are Excel Calculations faster than using VBA on a worksheet (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 16:31
Joined
Oct 22, 2009
Messages
2,803
Was asked if Excel built-in Calculations are faster than using VBA on a worksheet when applied to large datasets.

Wanted to run this up the flagpole to make sure it is correct.
And also, to ask about any custom C# options.
Am I correct in this answer / conclusion?

After searching the Internet and based on my own experience, this is what I think the answer should be:
The Excel Program with its functions and formulas are compiled in C++.
So, the Excel program will always be faster.

The VBA code is an interperted code, that along will make it slower.
Interop is one way to write C# code to automate Excel.
However, it too must interface (cross the application boundry) with Excel.

Basically, adding a single line of formulas to Excel with automation and
then avoid looping through the rows. Alternativelly put the formula in the first row and copy down to the other rows to use the built in Excel functionality. This will be much faster.
This appears to be true for VBA or for C#.

Conclusion: Always use the built in Excel functionality for large data sets where formula or formatting is applied.

Tip: If the message "you have copied a large amount of data..." pops up, use the (in VBA) Application.CutCopyMode = False

This was an interesting Interop example written in C#:
Code:
object missing = System.Reflection.Missing.Value;
object objFalse = false;
object objTrue = true;
 
string path = @"g:\spreadsheets\"; 
DirectoryInfo di = new DirectoryInfo(path); 
 
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
"Running".Dump();
app.Visible=false;
 
foreach (FileInfo file in di.GetFiles())
{
        if (!file.Name.StartsWith("~") && (file.Name.EndsWith("xlsx") || file.Name.EndsWith("xls")))
        {
                object filename=file.FullName;
                filename.Dump();
 
                // create Excel variables
                Microsoft.Office.Interop.Excel.Workbook oBook;
                Microsoft.Office.Interop.Excel.Worksheet oSheet;
                Microsoft.Office.Interop.Excel.Range oRange;
 
                oBook = app.Workbooks.Open(file.FullName, 0, false, 1, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, true, XlCorruptLoad.xlNormalLoad);
                app.Calculation = XlCalculation.xlCalculationManual;
                Worksheet os = (Worksheet)oBook.Worksheets[1];
 
                int maxR=os.Cells.Find("*", missing, missing, missing, missing, XlSearchDirection.xlPrevious, objFalse, missing, missing).Row;
 
                oRange = os.get_Range("J2:W2", missing);
                ((Range)oRange.Cells[1, 1]).Value2="=MIN(E2:E12)";
                ((Range)oRange.Cells[1, 2]).Value2="another formula";
                ((Range)oRange.Cells[1, 3]).Value2="you get the picture";
 
                object oCopy=oRange.Copy();
                oRange=os.get_Range("J3:W"+maxR.ToString(), missing);
                oRange.PasteSpecial();
 
                app.Calculation = XlCalculation.xlCalculationAutomatic;
                oBook.Close(objTrue, oMissing, oMissing);
        }
}
app.Quit();

In addition, since only the first row is being applied for either VBA or C#, the difference between them adding a first row would be very small.
It would be difficult to justify writing code in C# if speed was the only consideration.
 

Users who are viewing this thread

Top Bottom