Excel Summit South: Auckland Sydney Melbourne March 2016

We_HeaderI have spent the last 2 years putting together this stellar line-up of speakers (Excel MVPs – the Excel Dev Team – External speakers) for a series of 2 day conferences in Australia and New Zealand in March 2016.

SpeakersThis is a unique opportunity to:

  • Learn from six of the world’s leading Excel MVP’s as they discuss the Excel topics most useful to you.
  • Hear industry leading speakers from around the world give you the latest views on Financial Modelling best practices, standards and spreadsheet risk.
  • Shape the future of Excel: Interact with members of the Microsoft Excel Dev Team as you explore with them the future of Excel.
  • Choose the sessions that best suit your needs from 23 masterclass sessions over two days of twin tracks for modellers and analysts.

Find out more at the Excel Summit South website

Posted in Uncategorized | Tagged , , , , | Leave a comment

Excel User Voice and Workbook Calculate

Excel User Voice

At the recent Excel Global Summit the Excel team were keen to explain how they have started using Excel User Voice to ask for and prioritise product improvement suggestions.


There are 2 important things to note about this:

  1. The Excel Dev Team actually read your suggestions on User Voice!
  2. Starting with Excel 2016 the development and ship cycle is much much faster than the traditional 3 years.

So it really is worthwhile making your suggestions to improve the product.
You get 10 votes on suggestions, and the suggestions are categorised by end-point and product area. So if you want to make a performance suggestion for Excel on Android phones you can focus down, see what other suggestions have been made in this area, and either cast a vote for an existing suggestion or make a new suggestion.

Workbook Calculate

To try this out I made a suggestion (about calculation of course).

At the moment from the UI you can either calculate all open workbooks (F9 or Automatic) or a worksheet (Shift F9).
From VBA you can also calculate a range (Range.Calculate and Range.CalculateRowMajorOrder).

But you cannot calculate a single workbook.

This is really annoying when you have 2 versions of a large slow workbook open, or you have a small rapidly changing workbook open that links to a large mostly static workbook.

My suggestion is to add an additional calculation setting: Calculate Active Workbook Only and from VBA Workbook.Calculate.


So please vote for my suggestion if you think it’s a good idea!

Posted in Calculation | Tagged , , | 1 Comment

Writing Efficient VBA UDFs (Part13): MaxMinFair Resource Allocation – an array UDF example

Last year we got to spend a great day at Microsoft Research in Cambridge (UK). one presentation was about storage strategies and featured (amongst other stuff) an interesting algorithm called MaxMinFair.

The MaxMinFair algorithm

You can read about it here at WikiPedia, but the basic idea is to share a supply resource fairly between a number of demands without allowing the greedy demands to hog too much of the resource.

The algorithm starts by sharing the supply equally between the demands.
Then any excess supply (supply > demand) is re-shared equally between the demands that have not yet been met.
Then the algorithm continues to reallocate the excess supply until either all demands are met or there is no excess supply to reallocate.

Implementing MaxMinFair as a VBA array UDF

MaxMinFair makes a great example of writing an array formula UDF.
It has 2 parameters – Supply (a single number) and Demands (a set of numbers, usually a Range).

To keep things simple Supply has to be a single number >= 0.0, and Demands has to be a single column vertical range or array of numbers.

The parameters for the function are defined as variants so that the user can give a range or an array of constants or a calculation expression that returns an array of numbers.
The function is defined to return a variant. This allows the function to return either an error value, or a single number or an array of numbers.
The function starts by setting up the error handling and coercing the Ranges to values.
The results of the function are put in an array that is dynamically sized to match the number of demands.

The heart of the UDF is a DO loop that

  • Calculates allocation by dividing the available supply by the number of unsatisfied demands
  • Adds the allocation to each of the unsatisfied demands
  • Collects any excess allocation to be the available supply on the next iteration
  • Counts unsatisfied demands

The DO loop terminates when either there are no unsatisfied demands or there is no available supply to be allocated.

The function finishes by assigning the result array (dAllocated()) to the variant function.

The VBA Code

Here is the VBA code for the function:

Option Explicit
Option Base 1
Function MaxMinFair(Supply As Variant, Demands As Variant) As Variant
' Array function for Max-Min-Fair allocation of supply to demand
' Supply must be a scalar number >=0.0
' Demands must be a scalar number or a single column range or array of data
Dim nUnsat As Long          ''' number of unsatisfied demands
Dim dAlloc As Double        ''' amount to allocate to each unsatisfied demand
Dim dAllocated() As Double  ''' arrray of amounts allocated to each demand
Dim nRows As Long           ''' number of rows in Demands
Dim nCols As Long           ''' number of columns in Demands
Dim dAvailable As Double    ''' available supply this iteration
Dim j As Long
' set up error handling
On Error GoTo FuncFail
' return #Value if error
MaxMinFair = CVErr(xlErrValue)
' both parameters must contain data
If IsEmpty(Supply) Or IsEmpty(Demands) Then GoTo FuncFail
' convert ranges to values
If IsObject(Demands) Then Demands = Demands.Value2
If IsObject(Supply) Then Supply = Supply.Value2
' Supply must be a scalar number >=0
If IsArray(Supply) Then GoTo FuncFail
If Supply < 0# Then GoTo FuncFail
dAvailable = CDbl(Supply)
If Not IsArray(Demands) Then
' scalar demand: Minimum of supply and demand
If Demands < Supply Then
MaxMinFair = Demands
MaxMinFair = Supply
End If
' Demands must be a single column array
nRows = UBound(Demands, 1)
nCols = UBound(Demands, 2)
If nCols > 1 Then GoTo FuncFail
' setup output array
ReDim dAllocated(1 To nRows, 1 To nCols)
' count unsatisfied demands
For j = 1 To nRows
' if not number raise error
If dAllocated(j, 1) <> CDbl(Demands(j, 1)) Then nUnsat = nUnsat + 1
Next j
If nUnsat = 0 Then GoTo Finish
' iterate allocating available supply to unsatisfied demands
' amount to allocate to each unsatisfied demand
dAlloc = CDbl(dAvailable) / nUnsat
nUnsat = 0
dAvailable = 0#
' share available supply equally across unsatisfied demands
For j = 1 To nRows
If dAllocated(j, 1) < Demands(j, 1) Then
dAllocated(j, 1) = dAllocated(j, 1) + dAlloc
End If
Next j
' collect excess supply for next iteration
For j = 1 To nRows
If dAllocated(j, 1) >= Demands(j, 1) Then
' remove and accumulate excess supply
dAvailable = dAvailable + dAllocated(j, 1) - Demands(j, 1)
dAllocated(j, 1) = Demands(j, 1)
' count unsatisfied demands
nUnsat = nUnsat + 1
End If
Next j
' if all supply allocated or all demsnds met then finish
If nUnsat = 0 Or dAvailable = 0# Then Exit Do
' return array of results
MaxMinFair = dAllocated
End If
End Function


Here is a small example. The UDF is entered into C2:C8 as a multi-cell array formula (select C2:C8, type the formula into the formula bar without the { }, then press Control-Shift-Enter)


You can see that the total demand is 25.9 but the supply is only 18.3. MaxMinFair has satisfied all the demands except for the 2 largest ones, which have both been allocated the same 4.9.


MaxMinFair is a good choice when you want to allocate resources without allowing large resource demands to starve small resource demands.

Implementing the algorithm as an array UDF is a good opportunity to demonstrate a variety of VBA UDF techniques.

Posted in arrays, Calculation, UDF, VBA | Tagged , , | Leave a comment

Excel Circular References: Calculation, Detection, Control and Removal

Circular references in Excel are generally bad news:

  • They are slow to calculate
  • They can be hard to detect
  • An intentional circular reference can mask an unintended circular reference,
  • They do not always converge
  • The Status Bar always shows calculate even in Automatic Mode

What are Excel Circular References?

Excel circular references occur either when a formula refers to itself or when a chain of formulas links back to it’s starting point.
Most of the time circular references are created by mistake (unintended circular references) and should be corrected. They can also be used to solve iterative or goal-seeking problems in Finance and Engineering.

Why circular references are slow to calculate.

Excel uses a special iterative calculation method to solve circular references. First a standard recalculation is done which identifies the circular formulas that cannot be resolved.
Then Excel repeatedly calculates all the unresolved circular formulas using a sheet-by-sheet  left-to-right top-to-bottom calculation process.
The calculation only stops when either

  • The maximum number of iterations has been reached
  • or the largest change in values caused by an iteration is less than the maximum change

You can control these values using Options–>Formulas.


This calculation process is slow because:

  • The calculation is restricted to using a single core/cpu (single-threaded calculation)
  • The formulas are calculated repeatedly.

Detecting Unintended Circular References.

If ‘Enable Iterative Calculation’ is checked and you create a circular reference then Excel does not tell you that you have created a circular reference.

If ‘Enable Iterative Calculation’ is NOT checked and you enter formula that creates a circular reference:

In Automatic Calculation Mode Excel shows this message:


In Manual Calculation Mode no message is shown, but the next time you press F9 to recalculate the status bar shows a circular reference message:

If the circular reference is on the active sheet the message shows one of the cells in the circular reference loop:


If the circular reference is not on the active sheet then Excel does not tell you where the circular reference is (but if you select any sheet with a circular reference the status bar shows the cell reference).

You can also use Excel’s circular reference tool on the Formulas Tab:


But none of Excel’s built-in tools will always show you where the circular references are or detect all of the circular references.

I recommend Jan Karel Pieterse’s RefTree Analyzer too for detecting circular references (warning: the detection process is slow on large workbooks.)

Controlling Circular References

A  useful technique recommended by Stephen Bullen is to add an IF switch to your circular reference loops so that you can disable them.


If A1 is zero and iteration is disabled then Excel will not regard this calculation as a circular reference, so any circular reference detected is probably intentional. Set A1 to 1 and enable iteration to request Excel to solve using iteration.
Note that not all circular calculations converge to a stable solution. Another useful piece of advice from Stephen Bullen is to test the calculation in manual calculation with the number of iterations set to 1. Pressing F9 will single-step the calculation so you can watch the behaviour and see if you have genuinely converged to the correct solution.

Removing Intended Circular References

Because circular references are slow and difficult to check it is a good idea to remove them wherever possible. Most circular references in financial models can be replaced by simple non-circular formulas.

Unrolling cash flow Interest calculations

You can calculate interest compounded a number of times on a cash balance amount using circular references:
Total cash = Total Cash * (1+interest%)

Or you can unroll the calculation into steps:
Interest accumulated once per period:
Total Cash=Cash before Interest *(1+Interest%)
Interest compounded N times per period:
Total Cash=Cash before Interest *((1+Interest%)^N)

Fee calculated as % of final cost including Fee

Using circular references:
Fee=Fee% * Total Cost
Total Cost=Costs Excluding Fee + Fee

A little algebra rearranges this to avoid the circular reference:
Fee=Fee%*Costs excluding fee / (1-Fee%)
Total Cost=Costs excluding fee + Fee

Tax deductible as % of Profit after Tax

This is an example of circular references from John Walkenbach’s Excel Bible. A company decides to give a % of their after tax profits to charity. But the charity donation is tax deductible, so the charity donation and the tax and the after tax profit are all inter-related.

Using circular references:
Gross Profit=Revenue-Expenses
Profit Before Tax=Gross Profit – Charity Donation
Tax= Profit Before Tax * Tax%
Charity= Profit after Tax * Charity%
Profit after Tax=Profit Before Tax – Tax

Some algebra rearranges this to avoid the circular references:
Gross Profit=Revenue-Expenses
Profit Before Tax=Gross Profit – Charity Donation
Tax= Profit Before Tax * Tax%
Charity= Profit after Tax * Charity%
Profit after Tax=Gross Profit / ((1/1-Tax%) + Charity%)


  • Circular References should be avoided wherever possible
  • Most Financial circular references can be eliminated
Posted in Calculation | Tagged , , , | Leave a comment

Excel ForceFullCalculation: Trading off Editing speed vs Calculation speed

A recent post at Stack Overflow reminded me that I have not posted about the pros and cons of Excel’s Workbook.ForceFullCalculation property.

So what does Workbook.ForceFullCalculation do?

The simple answer is that it switches off Excel’s smart recalculation algorithm, so that every calculation recalculates every formula in all open workbooks.

Why is ForceFullCalculation Bad?

ForceFullCalculation slows down calculation because every formula is treated as though it is volatile and gets calculated, rather than only calculating the formulas that depend either on something that has changed since the last calculation or on a volatile function.

When is ForceFullCalculation Good?

When ForceFullCalculation is switched on Excel does not need to build and maintain the dependency trees. The dependency trees are created each time you open a workbook, and are maintained every time you make a change to a workbook.

So if

  • Your workbook takes a long time to open
  • Deleting or changing formulas takes a long time

then switching on ForceFullCalculation could speed things up a lot.

Also the dependency trees can take up a lot of memory so eliminating them can be good.

So its a trade-off: faster workbook opening and editing, and less memory footprint versus slower calculation.
(And you almost certainly need to be using Manual Calculation mode.)

So how do you switch it on?

There are several ways of setting ForceFullCalculation:

  • Click Alt-F11 to go to the Visual Basic Editor, select the workbook in the Project Explorer, Press F4 to see the workbook properties and change ForceFullCalculation to true.
  • Add this VBA statement to a VBA sub and execute it.
  • If you have FastExcel V3 installed then you can set it from the FastExcel Workbook Calculation Options page.

The Quirks of ForceFullCalculation

There are a few more things you should know about this property:

  • Although it is a Workbook Property it works at Application level and so affects all open workbooks.
  • It does get saved with the workbook and, if True, re-establishes itself when the workbook is reopened.
  • It makes “Calculate” appear in the StatusBar, and you cannot make it go away with any of the F9 calculate commands.
  • Once you have turned it on in an Excel session turning it off again does not restore Smart Recalculation: you have to close and restart Excel.

Trying ForceFullCalculation out

Here are the Jimwinz’s instructions from Stack Overflow for creating a demonstration of the problem:

  1. Create a new file (save and exit other Excel files!)
  2. To create the data to count, in A2 enter =RANDBETWEEN(0,10000) if you have a slow CPU or RANDBETWEEN(0,20000) if you have a faster CPU
  3. Copy A2 down to row A10k or A20k.
  4. Copy and paste values for column A
  5. Switch to Manual Calculation
  6. In B2, enter =COUNTIF($A$1:A2,A2), copy down and press F9 to calculate
  7. Once sheet is fully calculated, select all the CountIf cells in column B and use the Clear All function. This is the stage where Excel seems to freeze for a long time.
  8. Now click Undo to get the formulas back.
  9. Switch on ForceFullCalculation using one of the methods shown above. The statusbar should show Calculate and pressing F9 does not make it go away. Now Clear All is virtually instantaneous.

So what is your experience with this nifty workbook property?



Posted in Calculation, Memory, Uncategorized, VBA | Tagged | 7 Comments

Timing Excel Formula Calculations

I have written a couple of posts previously about the methodology of timing Excel calculations:

A Quantum of Time – measuring time accurately

The XIPS Challenge – how fast does Excel calculate?

But rather than focus exclusively on how to time Excel calculations, this also post looks at why you might want to and what should you measure.

Why time Excel calculations anyway?

There are only a few reasons I can think of for why you might want to time calculations:

  • a) To find out if it is the calculation that is slowing things down or something else.
    • Could be VBA, Screen Refresh, Data Refresh, not enough RAM, Addins …
  • b) To prioritize the formulas that are responsible for slow calculation.
  • c) To compare 2 different ways of calculating the same result to see which is faster.
  • d) Because its interesting if you are a geek like me.

Most people focus on c) followed by b).

Exactly what should you be measuring?

Elapsed time vs CPU Time

Usually you measure elapsed time: how much time passes from starting something (a macro, a calculation, …) to finishing it.
Sometimes it would be nice to measure CPU time (the amount of time your CPU cores are being used), so that you can see if you have enough RAM available or if you are using all the cores you have available efficiently.
Of course measuring elapsed time is a lot easier, so that is what we do most of the time.

And anyway as Excel users it’s elapsed time that we want to minimize.

Overhead time vs Calculation time vs Screen Repaint time

If you think about what Excel has to do to calculate formulas you can break it down into a series of steps:

Overhead Time

  • Work out which formulas need to be calculated, depending on what has changed since the last calculation and what is volatile.
  • Separate the formulas into chains of interdependent formulas and allocate them to the available calculation threads.
  • Process each formula in the calculation chains in turn

Calculation Time

  • Parse the formula into a sequence of executable sub-expressions and functions.
  • Get the data needed by the sub-expressions and functions and pass the data to them.
  • Evaluate the sequence of sub-expressions and functions and return the results to the next sub-expression.
  • Return the result to the Excel value layer.

Screen Repaint Time

  • If the cell containing the formula is in the visible part of the screen then Excel will format the result using the formatting and conditional formatting rules for that cell. This can be slow!

 Multi-threaded or Single-threaded calculation

Given that most modern PCs have multiple cores Excel’s calculation time is heavily dependent on making good use of the available cores.
Some functions (INDIRECT, GETPIVOTDATA and most UDFs) are single-threaded.

Don’t use a single-threaded calculation method such as Range.Calculate to compare a single-threaded function to a multi-threaded function.

Comparing Formulas

If you want to compare formula efficiency it does not much matter if the time measured includes overhead and screen repaint time as well as calculation time.

What matters is to compare like with like: each measurement should include the same amount of overhead and screen repaint etc.

Recalculation vs Full Calculation

Most of the time when Excel calculates it’s smart recalculation engine only recalculates the stuff that has changed since the last recalculation (and any volatile formulas).
So usually you want to measure and optimize recalculation time.
Of course if you change ALL the data in a workbook then ALL the formulas need to be recalculated.

Repeatable Timings

There are many reasons why you can get quite significant differences between timings:

  • Windows multi-tasking means other processes can be stealing time
  • Modern CPUs have Turbo modes that are workload-dependent
  • Modern CPUs have high-speed caches that may or may not contain the required data or program code
  • Windows may be pageing stuff in or out of RAM
  • Excel progressively optimizes both multi-threading and the calculation chain in successive calculations
  • Excel’s smart recalculation engine tries to minimize the amount that needs to be calculated but this depends on the previous calculation
  • Excel’s memory management algorithms may need to reorganize memory

To minimize these problems you should repeat the timing of the calculation several times and pick the most consistent timing.

Calculation Methods

Excel VBA gives you a variety of callable calculation methods. Which one you use for timing purposes can make a large difference to the comparison.


Most people writing a VBA calculation timing routine use Range.Calculate in Manual Calculation mode,.

Range.Calculate is usually the best method to use when comparing formulas.

  • If you switch off screen updating whilst timing the range calculate then you eliminate screen repaint time.
  • If you turn on Automatic calculation after the Range.Calculate then the formula(s) you calculated and all their dependents and all volatile formulas will be recalculated.
  • Range.Calculate is always single-threaded so does not include the multi-threaded calculation overhead.
  • Range.Calculate always calculates all the formulas in the selected range.
  • With a high-resolution timer you can compare even single-cell formulas reasonably accurately.

Don’t use Range.Calculate to compare a single threaded formula with a multi-threaded formula.


CalculateRowMajorOrder ignores dependencies within the selected range and so may be faster but less realistic than Range.Calculate.


Worksheet.Calculate does a multi-threaded smart recalculate of a worksheet. To do a Full calculation of the worksheet (all the formulas on the worksheet) you need to toggle Worksheet.EnableCalculation to False and then back to True before Worksheet.Calculate.


Recalculates all the open workbooks using the multi-threaded smart recalc engine.


Calculates ALL the formulas in all the open workbooks using multi-threading.


Rebuilds the dependency trees and calculation chain (this is slow) and then does a multi-threaded full calculation.

FastExcel Calculation Profiling

My FastExcel V3 Profiler product has a variety of optimized calculation timing and profiling tools that are designed to simplify drilling down to the calculation bottlenecks and timing formulas. FastExcel Profiler can save you a lot of time and effort.


You can:

  • Time workbooks, worksheets and formulas using each of the available calculation methods
  • Profile a Workbook to prioritize the slow worksheets and measure volatility and multi-threading efficiency
  • Profile Worksheets to find out which are the slow areas of formulas
  • Profile Formulas: Time and prioritize each of the unique formulas on a worksheet and discover which formulas are volatile or single-threaded.


  • Work out what you are trying to achieve.
  • Design your timing test to be as close as possible to the real-life problem.
  • Repeat the timing several times until you get some timing consistency.
  • Choose your calculation method according to what you want to optimize.
  • Don’t compare single-threaded calculation times to multi-threaded calculation times.
  • Be careful that screen rendering/refresh times do not distort your comparisons.
  • Use the Windows high-resolution timer API to measure elapsed time.


Posted in Calculation, Uncategorized | Tagged , , , | 1 Comment

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 120,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 5 days for that many people to see it.

Click here to see the complete report.

Posted in Uncategorized | 4 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 array-handling functions in FastExcel SpeedTools Extras compare with formulas using native Excel functions.
I asked Sam (sgbhide@gmail.com who frequently comments on these blog posts) to collaborate by creating the formulas using native Excel functions. He has done a great job!

Why would you want to stack/append Arrays/Ranges anyway?

A few functions allow you to input multiple arguments that effectively get stacked together. For example this formula works well:

=SUM(Sheet1!$A$1:$B$10, Sheet2!$Y$43:$Z$99)

But if you need to do a Lookup across multiple sheets this formula does not work:

=VLOOKUP($C$32,(Sheet1!$A$1:$B$10, Sheet2!$Y$43:$Z$99),2,False)

And you cannot use SUMIF like this:


Similarly if you are using a separate worksheet for each time period (week, month …) you often want to stack the time periods alongside one another.

In many cases there are ways of handling these situations using standard Excel Formulas, but often these methods are slow or complex.

Creating arrays containing a mixture of Array Constants and Ranges, stacking row-wise or column-wise.

As the last post showed you cannot put range references directly between the { … } of an array constant.
But Sam has some cunning formulas using the array version of CHOOSE:

Stacking Rows:


Stacking Columns:


The first argument for CHOOSE indicates which of the following arguments to return. But if you give an array as the first argument all the value choices are returned.
If you want to stack rows above one another you use a semi-colon (;) separator in the first CHOOSE argument, and if you want to stack columns alongside one another you use a comma (,), (if you use the wrong separator CHOOSE returns weird answers).

Note: you have to enter these formulas as multi-cell array formulas by selecting multiple cells, typing the formula and then pressing Control+Shift+Enter.

Unfortunately this method only works with either single columns or single rows: it does not handle 2 dimensional ranges.

So Sam came up with this method of handling 2-D ranges instead (but I have not got space to explain it!):

Suppose we have 2 ranges with defined names of Arry1 and Arry2:


And we have some more defined names containing formulas like this:

Arry1 =Sheet1!$D$6:$E$7
Arry2 =Sheet1!$D$10:$F$13
PadArry1 =(–(ROW(INDIRECT(“1:”&TotRows))*IF(COLUMN(INDIRECT(“RC1:RC”&TotCols,0)),1)<=rRows))
PadArry2 =(–(ROW(INDIRECT(“1:”&TotRows))*IF(COLUMN(INDIRECT(“RC1:RC”&TotCols)),1)>rRows))
rRows =ROWS(Arry1)
TotCols =MAX(COLUMNS(Arry1),COLUMNS(Arry2))
TotRows =ROWS(Arry1)+ROWS(Arry2)

Then this formula, entered as a multi-cell array formula into 6 rows by 3 columns:


Produces this:


Notice that Sam’s cunning set of formulas has padded the gaps in the top 2 rows of column 3 with zeros, rather than leaving them as #N/A.
This approach only works when all the cells in the ranges are numeric because Sam has used + to add the numbers together.

If some of the columns in the ranges are alphabetic and some are numeric life gets a lot more difficult.

Using FastExcel SpeedTools ROW.ARRAY and COL.ARRAY Functions

Well Sam’s formulas are difficult for many users so I developed some FastExcel SpeedTools array functions to make life easier.
To stack ranges below one another and pad out the gaps use ROW.ARRAY. The equivalent to Sam’s set of formulas is


The first argument (0 in this case) dictates what to use to pad out missing values.
To do a lookup on both these arrays use

=VLOOKUP(95,ROW.ARRAY(0,Arry1,Arry2),3,False) returns 7 (this does not need to be array entered).

You can use COL.ARRAY in a similar way to stack ranges alongside one another.

Note: ROW.ARRAY and COL.ARRAY are limited in total to the Excel row and column limits.

More FastExcel V3 Array functions

The FastExcel Arrays functions include:



It is useful to have a set of efficient functions to stack/append ranges and arrays.

Challenge: can you provide more efficient methods using standard Excel formulas?

Posted in arrays, Calculation, Lookups, Uncategorized, XLL | Tagged , , , , , , , | 4 Comments

Using Constant Arrays and Array Expressions in Excel Formulas

How Excel handles array expressions and constants in single-cell and multi-cell formulas is not well documented.
So here is my attempt to shed some light on the subject.

What are Constant Arrays?

Constant arrays are Excel formula expressions that allow you to create arrays containing numbers, text, logical values or error values. Constant arrays are enclosed in braces ({}).
Columns are separated from one another by , and rows are separated by ; so
{1,2,3;”A”,”B”,45.6;#N/A,”Z”,99} creates


You have to enter all the items in a row first before adding ; to move to the next row.
Constant arrays must be rectangular: each row must contain the same number of columns.

Constant arrays do not allow you to create arrays containing Dates or Ranges or Defined Names or Expressions or other Constant Arrays.

What are Array Expressions?

Array expressions are parts of Excel formulas that produce a result array of values when they are evaluated. Some examples are:

A3:A6 – returns a column array of the 4 values in cells A3 to A6
A3:A6*10 – returns a column array of the 4 values in cells A3 to A6, each multiplied by 10
A3:A6*{1;2;3;4} – returns a column array of the 4 values A3*1 , A4*2, A5*3, A6*4
Each element gets multiplied by the corresponding element in the other array

A3:A6+B1:B4 – returns a 4 value column array – A3+B1, A4+B2, A5+B3, A6+B4

What happens when the Arrays in an expression are  different sizes or shapes?

When combining 2 or more component array expressions that are different sizes or shapes Excel expands each of the component arrays so that they are all the same size.

  • The expanded arrays will have the same number of rows as the greatest number of rows across all the components.
  • The expanded arrays will have the same number of columns as the greatest number of columns across all the components.

Suppose you have 2 ranges A1:B4 and C1:D1

Arrays3     Arrays2

Then the array expression A1:B4*C1:D1 will expand each of the component arrays to 4 rows by 2 columns:

Arrays4 and  Arrays5

Then, since the array expression says multiply, each corresponding element in the first array gets multiplied by the corresponding element in the second array, like this:

Arrays7 which creates this 4 row 2 column result array  Arrays6

The Expansion Rules

Expanding two component arrays is straightforward if one component is only a single value or a single cell: the single value/cell gets duplicated as required.
This method is also used when one component array is a single row and the other component is a single column.

But Excel does something different when one or more of the components is a 2-dimensional array and the other component array has a miss-matching but greater than 1 number of rows or columns.
Rather than repeat it fills the missing cells with #N/A.

So if you have a range A1:C4 with 3 columns and 4 rows multiplied by a range E1:F1 with 1 row and 2 columns (A1:C4*E1:F1), the E1:F1 gets expanded across by adding a #N/A:

Arrays8 and Arrays9 you get this expansion

Arrays10 which gives this result  arrays11

Note that Excel expanded E1:F1 downwards because it was a single row.

Multi-cell Array Formulas

To return the resulting array from an array expression to Excel you need to enter the formula as a multi-cell array formula:

  • Select the cells required to match the dimensions of the result array, for instance J1:L4
  • Type the formula in the formula bar: Arrays12
  • Enter the formula by holding down Control and Shift and then press Enter.
  • The formula bar now shows Arrays13 Excel has added the curly braces to the formula to show you that its an array formula: don’t add the curly braces yourself.

If selected cells smaller than the result array

If the range selected as the output for the multi-cell array formula has less rows or columns than the result array then Excel still calculates the complete result array but only returns the subset of the array values that match the selected cells positions.

If selected cells larger than the result array

If the range selected as the output for the multi-cell array formula has more rows or columns than the result array then Excel pads out the excess cells with #N/A.

Built-in Excel functions designed to return Arrays

Some of the native Excel functions are designed to return multi-cell arrays, including:


Single-cell Array Formulas

Many of Excel’s built-in functions such as SUM, AGGREGATE, MAX, MIN, AVERAGE etc. can calculate array expressions  when entered into a single cell using Ctrl+Shift+Enter.

For example =SUM(A1:A4) can be entered as an ordinary formula, but SUM(A1:A4*10) has to be entered using Control+Shift+Enter, otherwise it just returns #Value.

A few of Excel’s built-in functions such as SUMPRODUCT can calculate array expressions without being entered as an array formula.

Posted in arrays, Calculation | Tagged , , , , | 8 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


This method requires converting your UDF call and its parameters to strings. I have not explored it further.

Dim FuncString as String

FuncString="XLLFunction(" & FirstParam
FuncString=FuncString & "," & SecondParam

FuncString=FuncString & ")"



You can use Declare to make functions in an external DLL available to VBA. Declare works well with XLL UDFs that only require simple parameters but I don’t know how to use Declare if you want to pass something like a variant array as a parameter to an XLL.

Declare Function DoNothingX_12 Lib "MarshalTest.xll" () As Long

dNothing = DoNothingX_12()


You can add references (VBE->Tools->References) to any COM based UDF (XLAM, Automation …) to your VBA project and then CALL the UDFs from VBA as though they were included in your project. This is very efficient, but does not work with XLL UDFs.


Here I have added references to 2 files – TestProj.xlam and AutoProj.dll (a VB6 automation addin).


You can use Application.Evaluate or Worksheet.Evaluate to get the result of an Excel formula string. Like ExecuteExcel4Macro you have to convert your UDF call and its parameters to strings. This makes it difficult to pass something like a variant array but easy to pass the address of a range using Excel formula syntax.
Evaluate has some quirks that you need to be aware of: see this post for details.

Sub TimeEval2()
Dim dTime As Double
Dim j As Long
Dim dNothing As Double
Dim str As String
str = "=DoSomething(A1:F10301)"
dTime = microtimer()
For j = 1 To 100000
dNothing = ActiveSheet.Evaluate(str)
Next j
dTime = microtimer - dTime
End Sub


This method is the easiest one to use and works with both COM-based and XLL-based UDFs. It will convert your function parameters to an appropriate type as required.
The only major drawback to Application.Run is that the parameters are handled By Value as opposed to By Reference, which means that each parameter is copied before being passed to the function. This is fine for scalar values and objects such as a Range object but is slow for arrays (objects get passed as pointers so your UDF has to know how to handle whatever data structure or object the pointer points to).

Sub TimeRunVarrX2()
Dim dTime As Double
Dim j As Long
Dim dNothing As Double
Dim str As String
Dim rng As Range
Dim varr As Variant
Dim jFunc As Long

str = "DoSomethingVarrX"
Set rng = Range("A1:F10301")
' get the register ID of the XLL function
jFunc = Evaluate(str)

dTime = microtimer()
dTime = microtimer()
For j = 1 To 100
' pass range object to be converted
dNothing = Application.Run(jFunc, rng)
Next j
dTime = microtimer - dTime

End Sub

You can pass Application.Run either the name of the function/sub as a string or as a Register ID if its an XLL function. And you can use Evaluate to convert the name of the XLL function to its Register ID, by calling Evaluate(“FunctionName”) rather than Evaluate(“FunctionName()”).

If you are repeatedly calling the function using Application.Run its much faster to use the Register ID rather than the function name.


I ran a series of performance tests comparing Application.Run, Call (using References or Declare) and Evaluate for VBA XLAM UDFs, C++ XLL UDFs and VB6 Automation addin UDFs.

Each function was called 100000 times.

The DoNothing UDF

This UDF takes no parameters and returns a single digit. I used this to measure the calling overhead. The VBA version looks like this:

Public Function DoNothing() As Double
DoNothing = 1#
End Function

Here are the timings:
DoNothingTimesApplication.Run shows 2 timings for both the XLL and the VB6 Automation UDFs

  • The first XLL time is using the Name of the function, the second (nearly 50 times faster) is using the Register ID
  • The first Automation time is using the simple name of the function, the second is using the fully qualified name ( Application.Run(“AutoProj.TestFuncs.DoNothingA”) )

Considering that these timings are for 100K calls all the times are fast, but Evaluate is definitely the slowest.

The DoSomething UDF

This UDF takes a single parameter of a range object and just returns the number of rows in the range. The objective is to findout how the various methods handle an object.

Public Function DoSomething(theRange As Range) As Long
DoSomething = theRange.Rows.Count
End Function

DoSomethingTimesThe timings for passing a Range object (which is just a pointer) or a reference by value are very similar to the Do Nothing case.

The DoSomethingVArr UDF

This UDF takes a single parameter as a variant and can handle either a Range object or a variant array. If passed a Range object it coerces it to values.
The UDF returns the number of rows in the range or array.

The objective is to compare the time taken to pass a copy of a variant array with the time taken to pass a Range Object and then coerce it.

I ran the tests using a range which is 10301 rows by 6 columns conatining a mixture of text and numbers.

Public Function DoSomethingVArr(theArray As Variant) As Long
Dim theArray2 As Variant
If IsObject(theArray) Then
theArray2 = theArray.Value2
DoSomethingVArr = UBound(theArray2)
DoSomethingVArr = UBound(theArray)
End If
End Function

Note that because Application.Run passes parameters by value the UDF has to coerce the range object to a local Variant array:  the code below does not work because you cannot change theArray: theArray does not get coerced to its values.

theArray = theArray.Value2

There are 3 sets of timings for RUN and CALL:

  • Variant Array – this shows the time to pass the large variant array as a parameter.
  • Range – coerce in function – this shows the time tp pass the range as an aobject or reference and then coerce it to its values inside the function.
  • Get Variant Array and Pass – this shows the time taken by VBA to convert the range to values and then to pass the resulting array to the function.

DoSomethingVarrThe results seem to show that it takes about 9.5 milliseconds (or 950 seconds for 100K calls) to coerce the range to its values or to pass the resulting array of values to the function.

But using Application.Run to pass the variant array to an XLL function takes 3 times as long as to pass it to a VBA or VB6 function (2700 versus 957). Presumably this is because the variant array needs to be converted to an xlOper.

If you just pass the range object/reference using Application.Run and then coerce inside the function the times are nearly the same (983 versus 1023).


  •  By far the most efficient method of calling UDFs is by using a Reference or Declare.
  • The least efficient method is generally EVALUATE.
  • Application.Run automatically handles converting UDF parameters between VBA and XLL, but this conversion can be relatively expensive for large arrays.
  • Application.Run passes parameters to the UDF by Value. This means that the UDF is not allowed to modify the parameters even locally within the scope of the UDF.
  • Objects are passed by Application.Run as read-only pointers by Value.
  • All these methods have acceptable performance unless they are used a large number of times.

OK – so what have I missed?

Posted in Calculation, UDF, VBA, XLL | Tagged , , , , | 4 Comments