Returning Multiple Cell Results From Extended Functions

Input AlchemyJ Extended Function in array formula

Many AlchemyJ Extended Functions can return results across multiple cells. For example, ajDBRunSQL can return a list of records from a SQL query. The retrieved records are usually returned as a matrix like those shown in the below screenshot. On the other hand, ajJSON returns a JSON string. However, the JSON string may be too long for one cell to show so the result will span across multiple cells.

AlchemyJ uses array formula in Excel to specify such behavior. When entering an AlchemyJ Extended Function that is expected to return a multiple-cell result, select the range that the result should be shown, e.g. B4:F9 input the formula and press CTRL+SHIFT+ENTER.

Please note that the current version of AlchemyJ does not support the standard Excel array formulas yet. When using array formula for AlchemyJ Extended Function, remember not to add other Excel standard functions around it. For example: {IFERROR(ajJSONToTable(A4:D4, B2), "")} will not work.

Overflow handling Sample

The return result array will be wrote to selected range. It is possible that the return result will be more than the selected range. Below settings is to indicate the handling when the selected range cannot show all returned records.

In this sample, there are 7 records in DB but only 6 rows selected as data return range.

Overflow option 0 - Show as many rows as possible

The default option to handle the array overflow is Show as many as possible. That means user can only get the result in the size of selected range. No error will be raised.

Overflow option 1 - Return #VALUE!

Once the array overflow, only #VALUE! will be returned to the first cell of selected range. No error will be raised, user can handle the error or exception in other ways.

Overflow option 2 - Raise #VALUE! and raise error

Besides returning #VALUE! in the first cell of selected range. An error will be raised to user.

Overflow option 3 - Show as many rows as possible with more data signals

When output data overflow, the last row or column will show "#N/A" in the return array. No error will be raised by system.