THUNKS for Mortals

The topic of thunks is advanced², meaning an advanced concept (thunks) within an already advanced feature (LAMBDA). The purpose of this page is to make thunks in Excel usable by mere mortals. I'm going to try to explain them without using advanced computer science terms (because I don't really understand those anyway).

Why use thunks? Mainly to (1) improve calculation efficiency and/or (2) work around limitations of existing formulas, especially by using arrays of thunks.

When you define a function using LAMBDA, but do not call it, the code within the LAMBDA is not evaluated. It is only evaluated when you explicitly call the function using (). This basic property of the LAMBDA function, combined with the fact that Excel can store function definitions in arrays, is what enables the main reasons to use thunks.

What is a thunk?

Definition: In Excel, a thunk is a zero-argument LAMBDA function that (1) delays the evaluation of an expression and (2) is only evaluated when explicitly called with (). A thunk factory is a function that produces thunks programmatically, commonly written as LAMBDA(x,LAMBDA(x)).

The most basic thunk is:

=LAMBDA("hello")

or more generally

=LAMBDA(expression)

This is a LAMBDA that has no arguments. The body of the LAMBDA is only the expression "hello". The only thing it does is define the expression. It does not evaluate it ... yet.

Just like any other lambda =LAMBDA("hello") by itself will show a #CALC! "error" because it has not yet been called. The #CALC! error in this case simply means Excel is displaying a function value that hasn't been called yet.

You evaluate a thunk by using () when calling it like this:

=LAMBDA("hello")()

Result: "hello"

Important: A thunk is not a value. It is a function that produces a value when called.

We can use LET to (a) name the thunk and then (b) call it like this:

=LET(
    mythunk, LAMBDA("hello"),
    mythunk()
)

If you are familiar with LAMBDA, you may be wondering "What's the big deal? All we've done so far is point out that a thunk seems like the most useless and pointless version of a LAMBDA there could possibly be!" Well, let's move on to the good stuff.

Lazy Evaluation

Improving calculation efficiency is one of the main reasons for using thunks. Sometimes, efficiency can be improved by preventing unused logic branches from evaluating, or by delaying evaluation of an expression. Lazy isn't always better, but let's talk about situations where this property might help.

Most functions in Excel behave as if they are "Eager" in the sense that every expression is evaluated even when their results may not be used. However, a thunk is "Lazy" because it doesn't run until you call it. Remember that a thunk is a function, and any function you define has this same property: a LAMBDA function is not evaluated when defined - only when called.

A thunk is lazy: it defines an expression that is not evaluated until you explicitly call it with ().

Here is an example where the expression RAND() is defined but never called:

=LET(
  myThunk, LAMBDA( RAND() ),
  "Nothing random happens"
)

Even though RAND() appears in the formula, it is never evaluated because the function named myThunk is never called.

The simplest useful example I can think of is the SWITCH function. The SWITCH function is "Eager" because the expressions for all of the cases are evaluated, even though only one of the cases is returned.

=LET(
  sw,SWITCH("A",
    "A", 5,
    "B", NOW(),
    "C", RANDARRAY(10000,10000),
    "default"
  ),
  sw
)

In the above example, even though we only want to retrieve the expression for "A" (which is 5), Excel will also evaluate the expressions NOW() and RANDARRAY(10000,10000).

' UDF: prints "WasEvaluated label datetime" to the VBA Immediate window
' Is NOT volatile
Public Function WasEvaluated(Optional label As String = "") As Double
    Debug.Print "WasEvaluated " & label & " " & _
                Format(Now, "yyyy-mm-dd hh:nn:ss")
    WasEvaluated = True
End Function

' UDF: Does nothing except set Volatile=True and msg in debug
Public Function VolatileTRUE() As Double
    Application.Volatile True
    Debug.Print "Volatile ON " & Format(Now, "yyyy-mm-dd hh:nn:ss")
    VolatileTRUE = True
End Function

' UDF: Does nothing except set Volatile=False and msg in debug
Public Function VolatileFALSE() As Double
    Application.Volatile False
    Debug.Print "Volatile OFF " & Format(Now, "yyyy-mm-dd hh:nn:ss")
    VolatileFALSE = True
End Function

=LET(
  sw,SWITCH("A",
    "A", 5,
    "B", WasEvaluated("case B"),
    "default"
  ),
  sw
)

Here's the first cool thing about thunks: With very little complexity, we can "THUNK the SWITCH" so that SWITCH does not evaluate every case.

Yes, we are using THUNK as a verb now.

Definition of THUNK (as a verb): Writing the code so that a function returns thunks instead of values.

The following SWITCH has been THUNKED!

EXAMPLE: A THUNKED SWITCH
=LET(
  sw,SWITCH("A",
    "A", LAMBDA( 5 ),
    "B", LAMBDA( NOW() ),
    "C", LAMBDA( RANDARRAY(10000,10000) ),
    LAMBDA( "default" )
  ),
  sw()
)

Guess what? This code runs without the "Excel ran out of resources" error because RANDARRAY(10000,10000) is never evaluated!

All I did was change the code within SWITCH so that all the expressions it might return are now thunks. Now, because every case within SWITCH would return a thunk, we can consider the variable sw to be a named thunk. To evaluate sw we must call it using sw().

Let's summarize the above example

• Each LAMBDA(expression) is a thunk
• SWITCH() returns one of those thunks
• The value assigned to sw is a thunk
• sw() calls the thunk and triggers evaluation

To test your understanding, what would happen if all of the cases in the SWITCH were thunks, except for the default expression? In other words, what's wrong with this:

EXAMPLE: INCORRECTLY THUNKED SWITCH
=LET(
  sw,SWITCH("Q",
    "A", LAMBDA( 5 ),
    "B", LAMBDA( NOW() ),
    "C", LAMBDA( RANDARRAY(10000,10000) ),
    "default"
  ),
  sw()
)

Answer: There is no "Q" case, so SWITCH returns "default" which is not a thunk. This means that for the default case, sw is not a thunk - it is not a function - which means that sw() will fail with a #VALUE! error.

Here's what we've learned so far

• A thunk is LAMBDA(expression)
• A thunk is lazy - the expression is not evaluated right away
• A thunk becomes a value only when you call it with ()
• You can assign a thunk to a variable, making that variable a thunk
• If code returns thunks instead of values, we say it has been thunked
• Thunks can delay or avoid evaluation, which might improve efficiency

Lazy ≠ Better

Before we move on, I want to mention that even though by definition a thunk is always lazy, when it comes to efficiency, lazy is not always better. For example, if you plan to call myThunk() many times, and it contains an expensive calculation, it may be faster to evaluate it once and reuse the value instead.

Here is a very inefficient use of a thunk:

=LET(
  myThunk, LAMBDA( SUM(SEQUENCE(10000)) ),
  myThunk() + myThunk() + myThunk()
)

In this example, you end up evaluating SUM(SEQUENCE(10000)) three times.

Arrays of Arrays

Another primary reason for using thunks is as a work-around for things Excel cannot do normally.

Excel does not support arrays whose elements are arrays of different sizes. When you use VSTACK or HSTACK, the resulting array is always rectangular. But, we can simulate an "array of arrays" by creating an array of thunks.

In Excel, a named array can store number values, text values, and even function values. This means that you can create an array of thunks using VSTACK, MAKEARRAY, MAP, SCAN, etc.

It doesn’t matter how the thunk is defined or what it contains. When an array stores a function (a LAMBDA), Excel stores only the definition of the function, not the result of running it.

This means that each thunk in the array can return an array of a different size, or even a completely different type of value.

The thunk=package analogy: A thunk is a single "thing" from Excel's perspective. A thunk is like a package you can pass around without opening, and the package can contain nearly anything. Calling it with () is how you open the package. An array of thunks is just a structured collection of packages that Excel can store, move, and index without forcing the contents to be created, evaluated, or opened.

Here is a very simple example of an array of thunks:

=LET(
  th_array,
    VSTACK(
      LAMBDA( {1,2,3} ),
      LAMBDA( {4;5} ),
      LAMBDA( NOW() )
    ),
  res_th,INDEX(th_array,2,1),
  res_th()
)

This demonstrates:

• th_array is a 3x1 array of thunks, created by using VSTACK to stack 3 thunks
• INDEX(th_array,2,1) returns the function (thunk) in row 2 column 1, still unevaluated
• res_th() calls the thunk, which evaluates the expression and returns {4;5}
• NOW() is never evaluated in this example

This was just a very basic example of an array of thunks. But, what we have demonstrated here is that by storing zero-argument LAMBDA functions as an "array of thunks," we can store thunks representing arrays of different sizes within a single array, manipulate that array, and evaluate the thunk(s) later.

See Owen Price's article for another example of how an array of thunks can contain arrays of different sizes.

Let's look at example where the efficiency gained from using thunks is based on delayed evaluation.

/* Choice in cell B1: dropdown: "Sales", "Inventory", "Exceptions" */
=LET(
  choice, B1,
  report_thunk,
    SWITCH(choice,
      "Sales",      LAMBDA(SORT(FILTER(SalesTbl, SalesTbl[Amount]<>0), 3, -1)),
      "Inventory",  LAMBDA(SORTBY(InventoryTbl, InventoryTbl[SKU])),
      "Exceptions", LAMBDA(FILTER(LogTbl, LogTbl[Status]="ERROR")),
      LAMBDA("UNHANDLED")
    ),
  report_thunk()
)

=LET(
  choice, B1,
  report_names, {"Sales";"Inventory";"Exceptions"},
  report_thunks,
    VSTACK(
      LAMBDA(SORT(FILTER(SalesTbl, SalesTbl[Amount]<>0), 3, -1)),
      LAMBDA(SORTBY(InventoryTbl, InventoryTbl[SKU])),
      LAMBDA(FILTER(LogTbl, LogTbl[Status]="ERROR"))
    ),
  INDEX(report_thunks, XMATCH(choice, report_names, 0))()
)

=LET(
  sales, SORT(FILTER(...)),
  inv,   SORTBY(...),
  exc,   FILTER(...),
  SWITCH(choice, "Sales", sales, "Inventory", inv, "Exceptions", exc)
)

Thunks as a Work-Around

If delayed evaluation was all that thunks were good for, they would still be valuable. However, there is another powerful reason to use thunks: to work around the limitations of existing Excel functions!

Each iteration of MAKEARRAY or MAP normally only lets you return a single value (not an array). That single value could be a thunk. So, if you want each iteration to return an array, you can thunk the array first. Then, unpack the thunk later.

=LET(
  data, {6;7;42},
  th_array, MAP( data, LAMBDA(v,
    LAMBDA( SEQUENCE(1,v) )
  )),
  th, INDEX(th_array,2,1),
  th()
)

Result: {1,2,3,4,5,6,7}

Another work-around example: SCAN is a popular function for thunking because it outputs the accumulator at each iteration (unlike REDUCE which only outputs the final result). SCAN doesn't like it when you try to store an array as the accumulator (unlike REDUCE), but SCAN is perfectly happy storing a thunk as the accumulator.

LET(
  comment_1,"A sequence of moves (row_offset, col_offset)",
  moves, {0,1; 1,0; 1,0; 0,-1; -1,0; 0,1},
  comment_2,"SCAN returns an array of thunks, one thunk per move",
  th_array,
    SCAN(
      LAMBDA( {0,0} ),
      SEQUENCE(ROWS(moves)),
      LAMBDA(path_th,k,
        LET(
          rem_1, "Unpack the path_th thunk to get the array of locations",
          path, path_th(),
          rem_2, "Get the last location row from the array",
          last, TAKE(path, -1),
          rem_3, "Get the next offset from the moves array",
          step, INDEX(moves, k, ),
          rem_4, "Add the offset to the last location to get the next location",
          next, last + step,
          rem_5, "Thunk the stacking of the previous path with the next location",
          LAMBDA( VSTACK(path, next) )
        )
      )
    ),
  comment_3, "Call the thunk after the 4th move to get the path so far",
  INDEX(th_array, 4, 1)()
)

Result:
 { 0,0;
   0,1;
   1,1;
   2,1;
   2,0 }

...
next, last + step,
stacked, VSTACK(path,next),
LAMBDA( stacked )
...

To summarize this section:

• Excel can store an array of thunks, like storing an array of packages.
• Each thunk in the array can hold a different expression, an expression that when evaluated (unpackaged) could produce an array of a different size. This allows us to work with arrays of arrays (in a way).
• We can use thunks with functions like SCAN, MAP, and MAKEARRAY to work around the normal limitation of returning only a single value within each iteration.

Remember the thunk=package analogy. The last couple examples showed how MAP and SCAN could create and return arrays of thunk packages. That leads to our next topic: thunk factories.

Thunk Factories

Up to this point, every thunk we've looked at has been written directly as LAMBDA(expression). But how do you process a bunch of data programmatically, to automatically convert structured data into arrays of thunks?

The answer is another lambda pattern that you may see described as a "thunk" but which I will refer to as a "thunk factory." It is this:

=LAMBDA(x, LAMBDA(x))

In Excel terms, this function evaluates the value passed as x and then returns the thunk LAMBDA(x). You can think of this function as converting x into a thunk, a function that wraps (or packages) x in a thunk, or a function that builds thunks.

A thunk factory is a function that creates thunks by packaging values inside zero-argument LAMBDA functions

Here is a very simple example of converting the value 5 into a thunk and then calling the thunk to return 5.

=LET(
  makeThunk, LAMBDA(x, LAMBDA(x)),
  th, makeThunk(5),
  th()
)

This type of thunk factory does not delay evaluation of the expression passed to it. We can test this by using our RANDARRAY(10000,10000) trick.

=LET(
  makeThunk, LAMBDA(x, LAMBDA(x)),
  th, makeThunk(RANDARRAY(10000,10000)),
  10
)

Here, the result should be 10 and we aren't even using the th variable after defining it. But, makeThunk still evaluates RANDARRAY(10000,10000) before wrapping it in a thunk, resulting in an "Excel ran out of resources" error message.

If a thunk factory does not delay evaluation, why is a thunk factory important? Answer: Instead of manually defining each individual array or function, the thunk factory lets you programmatically create a thunk or an array of thunks.

You can pass that array of thunks into functions like SCAN, or create thunks within functions like MAP, MAKEARRAY or SCAN that would otherwise not be able to return arrays of arrays - like the examples shown at the end of the last section.

One Final Example

I used AI to help me come up with another example of the "array of different sizes" and I like this example because it reminds us to consider WHEN and HOW MANY TIMES to evaluate a thunk. Should we wrap a FILTER expression as a thunk or should we evaluate the FILTER expression first and wrap the result as a thunk?

Category Amount
A        10
A        15
B        5
C        8
C        12
C        20

=LET(
  comment_1, "First, make an array of unique categories",
  cats, UNIQUE(Orders[Category]),
  comment_2, "Second, for each row of cats, create a filtered result",
  thunks,
    BYROW(cats,
      LAMBDA(cat,
        LAMBDA( FILTER(Orders[Amount], Orders[Category]=cat) )
      )
    ),
  thunks
)

=LET(
  cats, UNIQUE(Orders[Category]),
  thunks,
    BYROW(cats,
      LAMBDA(cat,
        LET(
          f, FILTER(Orders[Amount], Orders[Category]=cat),
          LAMBDA( f )
        )
      )
    ),
  thunks
)

=LET(
  cats, UNIQUE(Orders[Category]),
  thunks,
    BYROW(cats,
      LAMBDA(cat,
        LET(f, FILTER(Orders[Amount], Orders[Category]=cat),
          LAMBDA( f )
        )
      )
    ),
  sums,   MAP(thunks, LAMBDA(th, SUM(th()) )),
  avgs,   MAP(thunks, LAMBDA(th, AVERAGE(th()) )),
  counts, MAP(thunks, LAMBDA(th, ROWS(th()) )),
  VSTACK(
    {"Category","SUM","AVERAGE","COUNT"},
    HSTACK(cats, sums, avgs, counts)
  )
)

Conclusion

It got kind of crazy there in the end, but that's because the use of thunks is an advanced² topic.

We showed how thunks are lazy, and how we can use that to our advantage. One of the uses for thunks is to avoid evaluation of unused logical branches, or to delay evaluation until you explicity need the result.

We've also seen how thunks can be used to work around some limitations of Excel, allowing us to simulate arrays of arrays by creating arrays of thunks.

Performance gains from thunks are not always about laziness. Sometimes they come from decreasing the cost of lookups by changing how data is packaged and delivered. This distinction becomes critical when working with iterative functions like SCAN, where array lookup cost may dominate performance.

Hopefully, this article was a useful primer on the concept of thunks, making them a bit more understandable.

One final parting thunk:

=LET(th, LAMBDA( LAMBDA( LAMBDA( "cheers") ) ), th()()() )

Comments or Suggestions?

Please email me or message me on LinkedIn.

Acknowledgements

Big thanks to Owen Price, Craig Hatmaker, Peter Bartholomew and Diarmuid Early for their feedback and suggestions!

See Also

Create LAMBDA Functions, Candidates for the Library