So Microsoft is rolling out LET and LAMBDA. Using The Wisdom of the Ancients (also known as the Excel SDK) something similar can be done using good, old fashioned, xlfEvaluate. The function XLL.EVAL takes any Excel formula as its first argument and evaluates it using the remaining arguments. It uses _1, _2, ... in the formula to refer to the arguments. If you want named arguments use key-value pairs like {foo, 1.23}. These can be mixed and matched at your pleasure.

Their Thanksgiving example would be XLL.EVAL(Thanksgiving, {year, 2020}) where Thanksgiving is the string "TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), """mm/dd/yyyy""")", although "DATE(year,11,MOD(12-WEEKDAY(DATE(year,11,1)),7)+22)" seems simpler.

One thing to keep in mind is that references and defined names occurring in the first argument must be preceded with an exclamation point. Don’t ask me why because I don’t know. Excel is Excel.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s