Category Archives: Calculation

FastExcel V3 Released with Introductory Offer

FastExcel has been used successfully by thousands of users since it was first launched in 2001. The last version 2.4 was introduced in 2008 and since that time there have been major changes to Excel with Excel 2007, 2010 and 2013, … Continue reading

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

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

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 , | 7 Comments

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

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 | 5 Comments

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

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

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

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?

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

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!

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!

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