Category Archives: XLL

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

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

Extracting Digits from Text: Using Formulas and Designing a Missing Excel Function: GROUPS

An Excel problem that crops up quite often is how to extract digits (0-9) from text. The text might be part numbers, or web addresses, or currency values or … Some cases are easy to handle with formulas: A fixed … Continue reading

Posted in UDF, VBA, XLL | Tagged , | Leave a comment

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

FastExcel V3 Released with Introductory Offer

FastExcel has been used successfully by thousands of users since it was first launched in 2001. The last version 2.4 was introduced in 2008 and since that time there have been major changes to Excel with Excel 2007, 2010 and 2013, … Continue reading

Posted in Calculation, Lookups, Memory, UDF, VBA, XLL | Leave a comment

Inserting a UDF and Launching the Function Wizard from VBA

In a previous post I suggested you could do this by entering the function using VBA into the selected cell using a comma as the argument to the function, for example =LISTDISTINCTS.SUM(,) , and then calling the function wizard using … Continue reading

Posted in UDF, VBA, XLL | Leave a comment

Finding out if a function is Volatile or Multithreaded using VBA: UDFs for UDFs

Part of my new Profiling Formulas and Functions command requires the code to determine whether a Function is  a native built-in Excel function, or an XLL function, or some other kind (VBA, Automation). And I also want to know if … Continue reading

Posted in Calculation, UDF, VBA, XLL | Leave a comment

UNIQUES and DISTINCTS: exploring lists with LISTDISTINCTS

I just added some options to the SpeedTools LISTDISTINCTS functions that make them surprisingly powerful. You can now easily find the most frequently occurring item in a list, or find the item with the largest sum or average of a … Continue reading

Posted in Calculation, UDF, XLL | Tagged , , | Leave a comment

Finding missing items in lists: VLOOKUP vs COMPARE.LISTS performance and ease of use

Returning to the subject of finding the missmatches between 2 lists I want to compare using VLOOKUP with using SpeedTools COMPARE.LISTS. Test Data My test data consists of 2 lists of 500000 6-digit numbers. Most of these numbers match, but … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Leave a comment

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 2

In part 1 I looked at how FastExcel SpeedTools MEMLOOKUP and AVLOOKUP2 compared to VLOOKUP and INDEX/MATCH for ease of use and power. This post will benchmark the performance of the SpeedTools lookups against the standard Excel functions. Download the … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Tagged , , , | 3 Comments

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 1

Its time for some peformance tests to see how the new functions in SpeedTools stack up against the standard Excel functions. First up is MEMLOOKUP and AVLOOKUP2 vs VLOOKUP! SpeedTools Lookups are easier to use, more powerful and less error … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Tagged | 2 Comments