TransWikia.com

Why does my custom Excel function never return a value ONLY on Windows?

Stack Overflow Asked by Jake Chambers on December 18, 2020

I am building an Office Excel Add-in using the web add-in framework provided by Microsoft.
This add-in also includes a custom function. Currently, the custom function is working on Excel for Mac, Excel online (device agnostic), but not on Windows?

The add-in loads fine, and there are no obvious errors. But when the function is run (on Windows) it just says:
#BUSY and then resolves to #VALUE! and stays like that.

The code also works when using the Shared Runtime configuration, but that requires that we make all our Javascript compatible with IE, which is definitely a possibility – but I would like to know why the regular configuration is not working.

WISE is the Excel function.

function WISE(symbol, parameter, year, quarter) {
  var param = parameter.replace(/s/g, '').toLowerCase();
  param = param.replace('&', 'and');
  symbol = symbol.toUpperCase();
  if (quarter == null) {
    return getAnnualData(symbol, param, year);
  } 
}

function getVal(data, param) {
  var apiResponseDataFormatted = {};
  for (var key in data) {
    apiResponseDataFormatted[key.replace(/ /g, '').toLowerCase()] = data[key];
  }
  var newValue = apiResponseDataFormatted[param];
  if (newValue !== 0 && !newValue) {
    newValue = 'Unavailable';
  }
  return newValue;
}

function getAnnualData(symbol, parameter, year) {
  var apiPath = requestMap[parameter];
  var response = "";
  var url = URL_API + "/" + apiPath + "/" + symbol + "?apikey=" + api_key;

  var request = new XMLHttpRequest();
  request.open('GET', url, false);  // `false` makes the request synchronous
  request.send(null);

  if (request.status === 200) {// That's HTTP for 'ok'
    response = JSON.parse(request.responseText);
  }else{
    return "Request Error: " + request.status + " " + url;
  }

  var apiResponseData;
  var currentYear = new Date().getFullYear();
  if (year != null && year !== currentYear) {
    apiResponseData = response[currentYear - year - 1];
  } else {
    apiResponseData = response[0];
  }
  result = getVal(apiResponseData, parameter)

  return result;
}

One Answer

sorry for the frustration. One of the main reason for this could be that currently on windows custom functions run in their own runtime, which is a seperate. While that runtime conserves memory, one of the limitations is it doesn't support Full CORS.

We have been recommending the use of the Shared Runtime as a way to get around this. This effectively will run the custom function in the same runtime as the taskpane, so I suspect will work for you. This also makes it easy to share state between taskpanes and functions.

Can you try that?

https://docs.microsoft.com/en-us/office/dev/add-ins/tutorials/share-data-and-events-between-custom-functions-and-the-task-pane-tutorial

(I realize the docs aren't super clear about this, so will follow up offline on this to get it corrected).

Answered by Keyur Patel - MSFT on December 18, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP