Category Archives: Calculation

Threading and Hyper-Threading: Optimizing Excel Calculation speed by changing the number of threads

Posted on January 29, 2014 by

I have just implemented measuring multi-threaded calculation efficiency in FastExcel V3 Profile Workbook: so I thought it would be interesting to see the effect on calculation speed of varying the number of threads, and switching off hyper-threading. Hyperthreading Almost all … Continue reading →

Posted in Calculation | Tagged , | 3 Comments

Excel 2013 SDI Bug: “Calculate” in Status Bar strikes again

Posted on December 19, 2013 by

In the old days (I’m talking Excel 5 to Excel 2003 here) there was a worrying situation you could find yourself in where, no matter what you did, Excel would show you “Calculate” in the statusbar. Even when actually nothing … Continue reading →

Posted in Calculation, VBA | 4 Comments

Finding out if a function is Volatile or Multithreaded using VBA: UDFs for UDFs

Posted on December 9, 2013 by

Part of my new Profiling Formulas and Functions command requires the code to determine whether a Function is  a native built-in Excel function, or an XLL function, or some other kind (VBA, Automation). And I also want to know if … Continue reading →

Posted in Calculation, UDF, VBA, XLL | Leave a comment

Formula References between Sheets versus within Sheets shootout: Which calculates faster and uses more Memory

Posted on November 8, 2013 by

I thought I would revisit the differences between formulas that reference other worksheets and formulas that only reference their own worksheet. Referencing other worksheets always used to be a memory hog, but so much has changed between Excel 2003 and … Continue reading →

Posted in Calculation, Memory, VBA | Tagged | 1 Comment

Exploring Range.Calculate and Range.CalculateRowMajorOrder: fast but quirky formula calculation

Posted on November 3, 2013 by

The Range.Calculate methods are very useful additions to Excel’s other calculation methods (Application level Calculate, CalculateFull, CalculateFullRebuild and Worksheet.calculate: the missing one is Workbook.Calculate!). You can use the Range Calculate methods to: Force calculation of a block of formulas or … Continue reading →

Posted in Calculation, VBA | Tagged , | 1 Comment

Parsing Functions from Excel Formulas using VBA: Is MID or a Byte array the best method?

Posted on October 27, 2013 by

As part of extending the performance profiling abilities of FastExcel, I wanted to develop a Function Profiler Map. A key component of this is to extract the names of the functions embedded in Excel formulas. So I experimented with some … Continue reading →

Posted in Calculation, VBA | Leave a comment

UNIQUES and DISTINCTS: exploring lists with LISTDISTINCTS

Posted on October 14, 2013 by

I just added some options to the SpeedTools LISTDISTINCTS functions that make them surprisingly powerful. You can now easily find the most frequently occurring item in a list, or find the item with the largest sum or average of a … Continue reading →

Posted in Calculation, UDF, XLL | Tagged , , | Leave a comment

Exploring Conditional Format Performance Part 3: What’s slow, whats buggy and whats faster!

Posted on October 11, 2013 by

This is the third in a series of Posts on Conditional Formats (see part 1 and Part2). This post looks at the effects  on the performance of Conditional Formats of: Application.Screenupdating Application.EnableConditionalFormatsCalculation Application.Calculation Whether the cells containing the conditional formats … Continue reading →

Posted in Calculation, Formatting, UDF, VBA | Tagged , | 4 Comments

Exploring Conditional Format Performance Part 2: What’s slow, whats buggy and whats faster!

Posted on October 10, 2013 by

This is the second in a series of Posts on Conditional Formats (see part 1). This post looks at the effects (and the resulting bugs!)  on Conditional Formats of: Application.Screenupdating Application.EnableConditionalFormatsCalculation Application.Calculation Whether the cells containing the conditional formats are … Continue reading →

Posted in Calculation, Formatting, UDF, VBA | Tagged , | Leave a comment

Exploring Conditional Format Performance Part 1: What’s slow, whats buggy and whats faster!

Posted on October 9, 2013 by

Patrick wanted to know if I had any information on Conditional Format calculation and performance, and I have  not looked at it for several years, so here goes! I have done a series of experiments, using Excel 2007, 2010 and … Continue reading →

Posted in Calculation, Formatting, UDF, VBA | Tagged , | Leave a comment