Custom function result is displayed in a loop with loading...

[schebotar]

Hi there
I have custom function to load currency exchange rates from external site.

    [ExcelFunction]
    public static object EXCHANGERATE(double dateField)
    {
        ICurrencyClient currencyClient = AddIn.ServiceProvider.GetRequiredService<ICurrencyClient>();

        if (ExcelAsyncUtil.Run("Database request", dateField, delegate
        {
            DateTime date = dateField == 0 ? DateTime.Today : DateTime.FromOADate(dateField);
            var exchangeRate = currencyClient.GetCurrencyCourse(date)
                .GetAwaiter()
                .GetResult();

            return exchangeRate ?? -1m;
        }) is not decimal requestResult)
        {
            return "Loading...";
        }
        else
        {
            if (requestResult < 0)
            {
                return ExcelError.ExcelErrorNA;
            }
            else
            {
                return Math.Round(requestResult, 2);
            }
        }
    }

ICurrencyClient implementation:

public class CurrencyClient : ICurrencyClient
{
    private readonly HttpClient _httpClient;
    private readonly IMemoryCache _memoryCache;
    private const string requestAddress = @"https://www.cbr.ru/scripts/XML_daily.asp?date_req=";
    public HttpStatusCode StatusCode { get; private set; }

    public CurrencyClient(HttpClient httpClient, IMemoryCache memoryCache)
    {
        _httpClient = httpClient;
        _memoryCache = memoryCache;
    }

    public async Task<decimal?> GetCurrencyCourse(DateTime date)
    {
        if (!_memoryCache.TryGetValue(date, out decimal? exchangeRate))
        {
            string request = $"{requestAddress}{date.Date:dd/MM/yyyy}";
            HttpResponseMessage response = await _httpClient.GetAsync(request);
            try
            {
                response.EnsureSuccessStatusCode();
                string xml = await response.Content.ReadAsStringAsync();
                XElement valCourses = XElement.Parse(xml);
                exchangeRate = decimal.Parse(valCourses.Elements("Valute")
                    .Where(e => e.Element("Name").Value == "Евро")
                    .FirstOrDefault()
                    .Element("Value").Value);
                return exchangeRate;
            }
            catch
            {
                StatusCode = response.StatusCode;
                exchangeRate = null;
            }

            var cacheEntryOptions = new MemoryCacheEntryOptions()
                .SetSlidingExpiration(TimeSpan.FromHours(1));
            _memoryCache.Set(date, exchangeRate, cacheEntryOptions);
        }
        return exchangeRate;
    }
}

And I'm in stuck with this looping effect of this function work on array

Single cell request works just fine. What's the issue?

[govert]

Are you perhaps using a volatile function like =TODAY() as the input? Or are the dates in the left side hard coded?

[schebotar]

=TODAY() function, yes
Just checked hard coded dates. Works fine.

[govert]

OK, that's kind of 'by design' since the volatile function is recalculating after the cell updates, then your function starts calculating again.
Some usere make their own "=TODAY.NV()" function which just returns today but is not volatile. Another good approach is to cache such results (even for a short time), and check in the cache and return directly before calling ExcelDnaUtil.Run.

[schebotar]

I got it. Thank you!

[govert]

Also if you have more than one function, you should be careful with that first parameter of ExcelAsyncUtil.Run where you have "Database Request". It must be different when called from different functions (used together with the parameter info in the second argument of Run).
So rather than

    public static object EXCHANGERATE(double dateField)
    {
        ICurrencyClient currencyClient = AddIn.ServiceProvider.GetRequiredService<ICurrencyClient>();

        if (ExcelAsyncUtil.Run( "Database request" , dateField, delegate
        {

I'd suggest

    public static object EXCHANGERATE(double dateField)
    {
        ICurrencyClient currencyClient = AddIn.ServiceProvider.GetRequiredService<ICurrencyClient>();

        if (ExcelAsyncUtil.Run( nameof(EXCHANGERATE) , dateField, delegate
        {