Category Archives: Calculation

Excel ForceFullCalculation: Trading off Editing speed vs Calculation speed

A recent post at Stack Overflow reminded me that I have not posted about the pros and cons of Excel’s Workbook.ForceFullCalculation property. So what does Workbook.ForceFullCalculation do? The simple answer is that it switches off Excel’s smart recalculation algorithm, so … Continue reading

Posted in Calculation, Memory, Uncategorized, VBA | Tagged | 4 Comments

Timing Excel Formula Calculations

I have written a couple of posts previously about the methodology of timing Excel calculations: A Quantum of Time – measuring time accurately The XIPS Challenge – how fast does Excel calculate? But rather than focus exclusively on how to … Continue reading

Posted in Calculation, Uncategorized | Tagged , , , | 1 Comment

Stacking (Appending) Arrays/Ranges in Excel: Formulas versus Functions Shootout

This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. … Continue reading

Posted in arrays, Calculation, Lookups, Uncategorized, XLL | Tagged , , , , , , , | 4 Comments

Using Constant Arrays and Array Expressions in Excel Formulas

How Excel handles array expressions and constants in single-cell and multi-cell formulas is not well documented. So here is my attempt to shed some light on the subject. What are Constant Arrays? Constant arrays are Excel formula expressions that allow … Continue reading

Posted in arrays, Calculation | Tagged , , , , | 8 Comments

Calling XLAM/XLL/Automation UDFs from VBA: Evaluate, Run, or Reference?

I have this nagging feeling that I do not know the best way to call UDFs from VBA. So after a couple of days of research here is what I have found out so far. The Available Methods Application.ExecuteExcel4Macro This … Continue reading

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

Timing the Ins and Outs of User Defined Functions: Multi-Cell array formulas can be slow

I was looking at some multi-cell array formula UDFs with John and Rich and could not understand why they seemed so much slower than I expected. Each UDF (written as C++ XLLs) read data from around 200 cells using around … Continue reading

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

Getting Used Range in an XLL UDF: Multi-threading and COM

In two previous blog posts I discussed why handling whole-column references efficiently in VBA UDFs meant that you had to find the used range for the worksheet containing the whole-column reference. The posts also discussed how using a cache for … Continue reading

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

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