Legacy Mode - FXLData

Legacy Mode

Legacy Mode emulates the latest Microsoft 365 dynamic array behaviour in older Office Excel 2010-2019 versions by populating the cells neighbouring that holding an FXLData formula with the data result set.

In versions of Microsoft 365 Excel that do support dynamic arrays, legacy mode can also be used to write the result set directly into the worksheet. That's useful in situations where discrete data points are needed rather than an Excel array which has certain limitations.

Office Excel 2010-2019

FXLData always works in legacy mode when using Office Excel 2010-2019.

When an FXLData function is executed, the first cell of the result set will appear as the formula result. If automatic calculation is enabled, FXLData's dynamic array emulation will immediately populate the neighbouring cells with the remainder of the result set. If automatic calculation is disabled, Refresh must be used.

Forcing Microsoft 365 Excel to use Legacy Mode

FXLData functions in Microsoft 365 Excel operate as dynamic array formulas by default where the result set appears in the worksheet as an Excel array.

Using the Implicit intersection operator: @ however forces the FXLData function to operate in legacy mode causing the result set to be written directly into the worksheet.

=@FXLData("https://registry.domain.org/FusionRegistry","ECB,EXR,1.0","A..BGN+BEF..",100,"round=2","2010","2020","id",TRUE,FALSE,TRUE,FALSE)

In the above example the @ preceeding the FXLData function name instructs Excel to return the implicit intersection of the result set array which is just the first cell. If legacy mode automatic calculation is enabled, the FXLData's dynamic array emulation will populate the neighbouring cells. If not, Refresh must be used.