Tag Archives: array function

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

Posted on October 3, 2016 by fastexcel

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

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

Posted on September 16, 2015 by fastexcel

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

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

Posted on January 16, 2015 by fastexcel

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

Using Constant Arrays and Array Expressions in Excel Formulas

Posted on January 11, 2015 by fastexcel

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

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

Posted on December 13, 2014 by fastexcel

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

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

Posted on October 24, 2014 by fastexcel

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