Tag Archives: array formula

Writing Efficient VBA UDFs Part 14: Handling whole columns using Implicit Intersection – Updated

Excel has an interesting and very efficient trick called Implicit Intersection which allows you to use large Named Ranges and whole column references efficiently. What is Implicit Intersection? When Excel expects to get a single cell reference but you give … Continue reading

Posted in arrays, Calculation, UDF, VBA, XLL | Tagged , , , , | 9 Comments

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

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

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

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


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