Search

Recent Posts
 Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs
 Writing Efficient VBA UDFs Part 14: Handling whole columns using Implicit Intersection – Updated
 Excel: Evil and Hero Functions
 INDIRECT – Excel’s Most Evil Function
 Special Offer for Excel Summit South
 2015 in review
 Excel WhatIf Data Tables: Faster calculation with VBA
 Excel Full Column References and Used Range: Good Idea or Bad Idea?
 Timing Calculation Consistently: Windows Update/Background Process Woes
 Excel Summit South: Auckland Sydney Melbourne March 2016
Recent Comments
Post Archive
 October 2016 (2)
 July 2016 (1)
 April 2016 (1)
 February 2016 (1)
 December 2015 (4)
 November 2015 (2)
 September 2015 (2)
 May 2015 (2)
 February 2015 (1)
 January 2015 (2)
 December 2014 (1)
 November 2014 (2)
 October 2014 (3)
 September 2014 (2)
 August 2014 (2)
 June 2014 (1)
 April 2014 (1)
 February 2014 (1)
 January 2014 (3)
 December 2013 (2)
 November 2013 (4)
 October 2013 (6)
 September 2013 (4)
 February 2013 (3)
 January 2013 (3)
 December 2012 (4)
 November 2012 (2)
 October 2012 (2)
 September 2012 (1)
 July 2012 (6)
 June 2012 (7)
 April 2012 (1)
 March 2012 (2)
 February 2012 (1)
 January 2012 (3)
 December 2011 (1)
 November 2011 (3)
 October 2011 (2)
 September 2011 (2)
 August 2011 (1)
 July 2011 (4)
 June 2011 (8)
 May 2011 (4)
Categories
Blogroll
RSS
Blog Stats
 590,231 hits
 Follow Excel and UDF Performance Stuff on WordPress.com
Category Archives: XLL
Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs
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
Writing Efficient VBA UDFs Part 14: Handling whole columns using Implicit Intersection – Updated
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 array formula, array function, Excel Formulas, Excel functions, UDF
2 Comments
Stacking (Appending) Arrays/Ranges in Excel: Formulas versus Functions Shootout
This post is a follow up to my last post Using Constant Arrays and Expressions in Excel Formulas. I will explore how some of the general purpose arrayhandling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions. … Continue reading
Posted in arrays, Calculation, Lookups, Uncategorized, XLL
Tagged array constant, array expression, array function, Arrays, calculation speed, Excel Formulas, Excel functions, XLL
6 Comments
Calling XLAM/XLL/Automation UDFs from VBA: Evaluate, Run, or Reference?
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 array formula, array function, calculation speed, UDF, XLL
4 Comments
Timing the Ins and Outs of User Defined Functions: MultiCell array formulas can be slow
I was looking at some multicell 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 array formula, array function, calculation speed, Excel 2010 Calculation Bug, UDF, XLL
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 (09) 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
Getting Used Range in an XLL UDF: Multithreading and COM
In two previous blog posts I discussed why handling wholecolumn references efficiently in VBA UDFs meant that you had to find the used range for the worksheet containing the wholecolumn reference. The posts also discussed how using a cache for … Continue reading
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 builtin 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