expandFormula

This function is very useful, but quite hard to explain. It takes a few strings as arguments, and replaces itself with a formula. The best way to learn what it does is by looking at simple examples or real-world applications. For the inquisitive, there is also a section on how it works.

expandFormula(
    formulaString,
    expansion1,
    [expansion2,]
    […]
)

Arguments

formulaString

A string representing the original formula, which will be expanded according to the following arguments.

expansionString1

The first expansion expression.

expansionString2, …

More expansion expressions. Optional.

Changes from Autory 2 to Autory 3

Autory 3 does not automatically expand formulas just because a config value contains []. The old UseOldAutomaticFormulaExpansion run control option was removed.

Use expandFormula explicitly wherever formula expansion is intended.

Examples

Single expansion

expandFormula(
    "foo(x)",
    "x = 4 * [A] + 6 * [B]",
)

resolves to

4 * foo("A") + 6 * foo("B")

Single expansion with nested functions

expandFormula(
    "foo(bar(x))",
    "x = 4 * [A] + 6 * [B]",
)

resolves to

4 * foo(bar("A")) + 6 * foo(bar("B"))

Multiple expansions

You will only see the end result, but we show the internal steps here for clarity:

expandFormula(
    "foo(x, y)",
    "x = 4 * [A] + 6 * [B]",
    "y = 1 * [C] + 3 * [D]",
)

resolves to

expandFormula(
    "4 * foo("A", y) + 6 * foo("B", y)",
    "y = 1 * [C] + 3 * [D]",
)

which resolves to

expandFormula(
    "1 * (4 * foo("A", "C") + 6 * foo("B", "C")) + 3 * (4 * foo("A", "D") + 6 * foo("B", "D"))",
)

which resolves to

1 * (4 * foo("A", "C") + 6 * foo("B", "C"))
+
3 * (4 * foo("A", "D") + 6 * foo("B", "D"))

Multiple expansions with nested functions

You will only see the end result, but we show the internal steps here for clarity:

expandFormula(
    "foo(x, bar(y))",
    "x = 4 * [A] + 6 * [B]",
    "y = 1 * [C] + 3 * [D]",
)

resolves to

expandFormula(
    "4 * foo("A", bar(y)) + 6 * foo("B", bar(y))",
    "y = 1 * [C] + 3 * [D]",
)

which resolves to

expandFormula(
    "1 * (4 * foo("A", bar("C")) + 6 * foo("B", bar("C"))) + 3 * (4 * foo("A", bar("D")) + 6 * foo("B", bar("D")))",
)

which resolves to

1 * (4 * foo("A", bar("C")) + 6 * foo("B", bar("C")))
+
3 * (4 * foo("A", bar("D")) + 6 * foo("B", bar("D")))

Using with TableLookup

expandFormula with TableLookup is useful for looking up values in various tables, columns or rows without repeating the same formula manually.

Example	Result
expandFormula( "TableLookup('table1', 'row1', col)", "col = [A] + [B] + [C]", )	TableLookup('table1', 'row1', "A") + TableLookup('table1', 'row1', "B") + TableLookup('table1', 'row1', "C")
expandFormula( "TableLookup('table1', row, col)", "col = [A] + [B] + [C]", "row = [X] + [Y] + [Z]", )	TableLookup('table1',"X","A") + TableLookup('table1',"X","B") + TableLookup('table1',"X","C") + TableLookup('table1',"Y","A") + TableLookup('table1',"Y","B") + TableLookup('table1',"Y","C") + TableLookup('table1',"Z","A") + TableLookup('table1',"Z","B") + TableLookup('table1',"Z","C")

Using with loopTable

expandFormula with loopTable can be used to quickly created nested loops, or reference multiple columns in the same loop, without repeating the same formula manually.

Example	Result
expandFormula( "loopTable('table1', col)", "col = [A] + [B] + [C]", )	loopTable('table1', "A") + loopTable('table1', "B") + loopTable('table1', "C")
expandFormula( "loopTable(tableName, col)", "col = [A] + [B]", "tableName = [PolicyTable] + [ScenarioTable]", )	loopTable("PolicyTable", "A") + loopTable("PolicyTable", "B") + loopTable("ScenarioTable", "A") + loopTable("ScenarioTable", "B")

Using with arbitrary formulas inside the placeholders

In the previous examples, the contents of the [] placeholders were always interpreted as strings. Sometimes it may be necessary to specify a more complex formula inside a placeholder in the expansion expression.

Prefix the placeholder with = when the contents should be treated as a formula instead of a string:

expandFormula(
    "foo(x)",
    "x = [=A + B] + [Label]",
)

resolves to

foo(A + B) + foo("Label")

Without the =, A + B would be inserted as the string "A + B". Numeric placeholders are also treated as formulas, so [123] resolves to 123, not "123".

Edge cases

These examples are a bit more obscure, but follow the same pattern as the previous examples.

No placeholders

Example	Result	Comment
expandFormula("foo(x)", "x = 4 * [A]")	4 * foo("A")	Basic example.
expandFormula("foo(x)", "x = [A]")	foo("A")	Remove the 4 *. Intuitive enough.
expandFormula("foo(x)", "x = 4")	4	This might not be intuitive, but correct.

Empty placeholders

Example	Result	Comment
expandFormula("foo(x)", "x = 4 * [A]")	4 * foo("A")	Basic example.
expandFormula("foo(x)", "x = 4 * []")	4 * foo("")	Empty string, not an omitted argument.

Empty expansion expression

Example	Result	Comment
asdf(expandFormula("foo(x)", "x = [A] + [B]"))	asdf(foo("A") + foo("B"))	Basic example.
asdf(expandFormula("foo(x)", "x = [A]"))	asdf(foo("A"))	Remove one placeholder.
asdf(expandFormula("foo(x)", "x = "))	asdf()	Empty formula, not an empty string.

Real-world applications

expandFormula is most useful when the same formula pattern must be repeated over a configured list of names. Typical applications include:

• Looking up the same row from several columns in a config table with TableLookup.
• Reading several columns from the same loop table with loopTable.
• Creating nested loop formulas from a list of table names and column names.
• Building formulas from config tables, as shown in Using tables as dynamic function arguments.
• Reducing repetition in per-policy calculations, especially when many policy attributes are read through the same loopTable pattern.

How it works

expandFormula is evaluated before the resulting formula is sent to Excel. It applies each expansion argument from left to right:

1. Parse formulaString as the formula template.
2. For each expansion string, split it at the first =.
3. Treat the text before = as the variable name to replace in the current template.
4. Parse the text after = as the expansion formula.
5. For each [] placeholder in the expansion formula, copy the current template and replace the named variable with the placeholder value.
6. Use the expanded result as the template for the next expansion argument.

By default, placeholder contents are inserted as strings. For example, [A] becomes "A". Use [=A + B] to insert A + B as a formula instead.

Because each expansion works on the result of the previous expansion, the order of expansion arguments can affect the shape of the final formula even when the same placeholders are used.