Monthly Archives: July 2012

From VBA to C Part 9 – the additional Types of UDF you can create in an XLL

In VBA you can create a UDF that returns any VBA datatype, but there are really only 2 kinds of UDFs: Volatile and Non-Volatile. In the C XLL UDF world there are several other types that you can create. Features … Continue reading

Posted in Uncategorized | Leave a comment

From VBA to C Part 8 – Using Containers from the Standard Template Library

In the VBA world we make a lot of use of arrays and Collections. And we can also use the Dictionary object from the VBScript library by adding a reference to the VBScripting runtime. All of these are types of … Continue reading

Posted in XLL | 5 Comments

Comparing Two Lists – VBA UDF shootout between Linear Search, Binary Search, Collection and Dictionary

I often run into the problem of having to compare two lists in Excel, to see what items are in the list to look for that can’t be found in the list to look in. So when I saw Dick … Continue reading

Posted in UDF, VBA | 18 Comments

Makeing the most of your XIPS Part2 – when 40 MXIPS for AVERAGEIFS is too slow

Peter wants to calculate a rolling average over 600K rows. His data consists of a Timestamp in Column A and a Value in column B: +-Timestamp-+-value-+ | 1340816430|  .02  | ——————— He is using an array formula in each of … Continue reading

Posted in Calculation | 1 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