# Category Archives: Calculation

## Writing Efficient VBA UDFs (Part13): MaxMinFair Resource Allocation – an array UDF example

Last year we got to spend a great day at Microsoft Research in Cambridge (UK). one presentation was about storage strategies and featured (amongst other stuff) an interesting algorithm called MaxMinFair. The MaxMinFair algorithm You can read about it here … Continue reading

## Excel Circular References: Calculation, Detection, Control and Removal

Circular references in Excel are generally bad news: They are slow to calculate They can be hard to detect An intentional circular reference can mask an unintended circular reference, They do not always converge The Status Bar always shows calculate … Continue reading

## 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 | 10 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 | | 3 Comments

## 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 | | 9 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 | | 10 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 | | 5 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