Search Results for: writing efficient

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

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

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

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

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

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

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

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

“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

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

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

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