Category Archives: UDF

Python in Excel: Python User Defined Functions

Posted on November 6, 2023 by fastexcel

This post explores using Python in Excel to create Python User Defined Functions. The example used is a function to calculate Gini coefficients (see https://fastexcel.wordpress.com/2011/05/21/fast-gini/). Defining the UDF Python UDFs live in the workbook global Python space and must be … Continue reading →

Posted in Calculation, Excel, Python, UDF | Tagged , , , | Leave a comment

Short-circuiting in Array Formulas

Posted on January 6, 2023 by fastexcel

The previous post demonstrated that IF and CHOOSE short-circuit but IFS and SWITCH do not. But following up a hint from Bill Wu that IF behaves differently in array formulas I decided to check whether it still short-circuits.(IF in array … Continue reading →

Posted in arrays, Calculation, Dynamic Arrays, Excel, UDF | Tagged | 5 Comments

FastExcel Version 4 – with Introductory Offer

Posted on February 5, 2020 by fastexcel

After several man-years of development and a lot of beta testing FastExcel Version 4 has finally hit general availability! FastExcel V4 is a major rewrite of FastExcel V3 and provides many of the things you have been asking for: Support … Continue reading →

Posted in arrays, Calculation, Debug formulas, Dynamic Arrays, Excel, Lookups, Memory, UDF, VBA | Leave a comment

Develop Excel Conference

Posted on July 30, 2018 by fastexcel

The Develop Excel conference will take place in London on Thursday October the 18th 2018. It will be held at the Microsoft Reactor (70 Wilson Street London EC2A 2DB). Are you responsible for building Excel based Addins and Solutions? Develop … Continue reading →

Posted in .NET, arrays, Excel, JS-API, UDF, VBA, XLL | 2 Comments

Excel JavaScript API Part 1: Overview & Comparison

Posted on March 13, 2017 by fastexcel

I have been spending time in the last few months experimenting with the new JavaScript Excel API. This is the first of a planned series of posts examining the JS-API from the point of view of an Excel Office developer. … Continue reading →

Posted in .NET, JS-API, UDF, Uncategorized, VBA, XLL | Tagged , , , , | 10 Comments

Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs

Posted on October 7, 2016 by fastexcel

For several years people have been asking the Excel Dev team to add the capability to create Intellisense for UDFs to work the same way as native Excel functions. Well, now Govert van Drimmelen, author of the wonderful Excel DNA, … Continue reading →

Posted in .NET, UDF, VBA, XLL | Tagged , , , , | 20 Comments

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

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