How can I pass an Excel object as a parameter to an Excel function in Microsoft Garage script-lab?

Question

I'm trying to build a typescript function in excel. For example, I'd like to create a custom function like "=doThis(A1:B2)" and pass in an Excel.Range. I've tried both Microsoft Garage's script-lab (https://github.com/OfficeDev/script-lab) and the Yeoman Office Add-In (https://learn.microsoft.com/en-us/office/dev/add-ins/quickstarts/excel-custom-functions-quickstart?tabs=excel-windows).

The problem I'm having with script-lab is that I don't know how to reference the excel application (and thus workbook/worksheet/range) instance in the code. All of the FUNCTION samples pass in primitive value types like numbers or strings. I'd like to pass in an Excel Range OR be able to reference the Excel Range in the code.

For example, the basic sample function is this:

/**
 * Calculates the volume of a sphere.
 * @customfunction
 * @param {number} radius
 * @returns The volume of the sphere.
 */
function sphereVolume(radius) {
  return (Math.pow(radius, 3) * 4 * Math.PI) / 3;
}

This passes in a number. I'd like to pass an Excel object as the parameter; OR at least reference it in the code block.

Thank you for your help in advance.

Answer 1

I have the same question and after research I think we can only pass primitive or array as param for custom fuction. Array should do what you want, to do so, declare the param as below example from here as mentioned by Brian Gonzalez

/**
 * Returns the second highest value in a matrixed range of values.
 * @customfunction
 * @param {number[][]} values Multiple ranges of values.
 */
function secondHighest(values) {
  let highest = values[0][0],
    secondHighest = values[0][0];
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (values[i][j] >= highest) {
        secondHighest = highest;
        highest = values[i][j];
      } else if (values[i][j] >= secondHighest) {
        secondHighest = values[i][j];
      }
    }
  }
  return secondHighest;
}

However if you want to return a range, remember to define it as array too, for example:

/**
 * Get Numeric from each cell.
 * @customfunction
 * @param {Range} | {array}
 * @returns {number[][]} A dynamic array with only numerics.
 */
function tsNumeric(refRange: any[][]) {
  let resultRange: any[][] = refRange;
  resultRange.forEach(function(row, row_i) {
    row.forEach(function(cell, cell_i) {
      resultRange[row_i][cell_i] = String(cell).replace(/\D/g, '');
    });
  });

  return resultRange;
}