Search Results for: writing efficient

Writing efficient VBA UDFs (Part5) – UDF Array Formulas go faster!

Posted on June 20, 2011 by fastexcel

Just in case you thought the previous posts on writing efficient VBA UDfs (Part1, Part2, Part3, Part 4) meant we had finished making UDFs run faster, think again – its time to explore UDF Array Formulas. Single and Multi-Cell Array … Continue reading →

Posted in UDF, VBA | 31 Comments

Writing efficient VBA UDFs (Part 4) – Variants, References, Arrays, Calculated Expressions, Scalars

Posted on June 20, 2011 by fastexcel

In part 1 and part 2 of “Writing efficient UDFs” I used parameters defined as Range to get data from Excel. Function VINTERPOLATEB(Lookup_Value As Variant, Table_Array As Range, Col_Num As Long) This works OK if the function is called from … Continue reading →

Posted in UDF, VBA | 3 Comments

Writing efficient VBA UDFs (Part 3) – Avoiding the VBE refresh bug – Updated

Posted on June 13, 2011 by fastexcel

In Part 1 and Part 2 of Writing Efficient VBA UDFs I looked at some simple ways of changing the VBA code you write to make it run massively faster. In this post I look at a bug in Excel … Continue reading →

Posted in UDF, VBA | 14 Comments

Writing efficient VBA UDFs (Part 2) – using Excel Functions inside a UDF

Posted on June 6, 2011 by fastexcel

In part 1 of Writing efficient VBA UDFs I looked at more efficient ways for the UDF to process a Range of data by reading it all into a Variant array. In this post I look at a case (using … Continue reading →

Posted in UDF, VBA | 18 Comments

Writing efficient VBA UDFs (Part 1) – It ain’t what you do it’s the way that you do it

Posted on May 25, 2011 by fastexcel

Its fairly easy to write a User Defined Excel function using VBA: Suppose you want to write a function that calculates the average of a range of cells, but exclude from the average anything that is not a number or … Continue reading →

Posted in UDF, VBA | 24 Comments

Making your VBA UDFs Efficient

Posted on April 23, 2015 by fastexcel

Jeff keeps telling me that it is a hassle to find the posts in my series on writing efficient VBA UDFs. So Here you are Jeff: a headline page. There are currently 22 posts in this series. They sort of … Continue reading →

1 Comment

Fast Gini – calculating Gini coefficients in Excel efficiently

Posted on May 21, 2011 by fastexcel

“The Spirit Level” is a very interesting book about the effects of inequality on health and wellbeing. It introduced me to the Gini coefficient, which is a number ranging from 0 to 1 that measures the degree of inequality in … Continue reading →

Posted in UDF, VBA, XLL | 21 Comments

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

Posted on September 26, 2014 by fastexcel

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

From VBA to C Part 7 – Developing an XLL array function

Posted on July 7, 2012 by fastexcel

In a post last year I showed how to develop an array version of the VBA AverageTol UDF. This was a simple and not very useful extensionto the AverageTol function that allowed the function to return an array of results … Continue reading →

Posted in UDF, VBA, XLL | Leave a comment

From VBA to C Part 6 – Argument Types – VBA Variants, Ranges and Doubles vs C++ Values, References and Doubles

Posted on July 5, 2012 by fastexcel

In my previous series of posts on “Writing Efficient VBA UDFs – Parts 1 to 10” I spent some time discussing how the data type you use for function arguments can have a large effect on performance. Well the same … Continue reading →

Posted in UDF, VBA, XLL | 2 Comments