First-time Action Issue when loading ExcelDna Add-In

[Dennis]

Hello,

I have one issue to report when I developed ExcelDna Add-In with UDF to get data via RESTful API and fill in Excel spreadsheet using dynamic array feature. The issue occurs only when the action is taken for the first time during the Add-In session. I called it as "First-time Action Issue"

Below is the detailed description:

• Issue Overall Explanation
  • First-time Action: 
    1. Open spreadsheet and load the ExcelDna Add-In
    2. select one cell with any UDF, input ENTER to run the UDF
  • Observation:
    • Observe1: not only the selected cell is running, but all other cells with UDF (same or different UDF) are also running
    • Observe2: after all cells run finished, some columns (Date or Time formatting) will result in wrong format - Numeric instead of Date or Time (Ex. 44318 instead of 5/2/2021); columns with other format such as Text are always correct
    • Observe3: after First-time Action, UDF functionality back to normal: 
      1. one cell action (Ex. input ENTER) will only trigger the selected cell running
      2. no formatting issue anymore
• UDF Code Info
  • Use ExcelFunction attribute
  • Use System.Net.Http.HttpClient for the Async helper function (Ex. async Task<object[,]>) to make HTTP requests to Web API; use Newtonsoft.Json to parse the JSON data from API response
  • Use ExcelTaskUtil.RunTask() method to call the above helper function within the UDF function to get the API data
  • UDF will return object[,] as dynamic array in Excel spreadsheet
  • Use XlCall.xlfCaller to get the current cell (where user place the UDF) reference information
  • Use Microsoft.Office.Interop.Excel to handle output range formatting process (Ex. Date or Time format for cell)
• Testing UDF Spec
  • Test1(A, B, N)
    • Input: A (string), B (string), N (double)
    • Output:
      • data
        • column "A" = {A}, column "B" = {B}, column "Now" = executing time
        • total {N} rows
      • format:
        • column "Now" = "m/d/yyyy h:mm:ss"
        • other columns = "General"

• Test2(C, B, N)
  • Input: C (string), D (double [Date]), N (double)
  • Output:
    • data
      • column "C" = {C}, column "Today" = {D}, column "Now" = executing time
      • total {N} rows
    • format
      • column "Today" = "m/d/yyyy"
      • column "Now" = "m/d/yyyy h:mm:ss"
      • other columns = "General"
• Issue Detailed Report
  • Before First-time Action
    1. Open the Test workbook
    2. Load the Test Add-In
    3. Observe: current tables before the First-Time Cell Action

• Launch First-time Action
  1. Select one cell with UDF (Ex. $C$7 in the Test1 Tab)
  2. First-time Action: Input ENTER in the selected cell to run the UDF (Ex. Test1())
  3. Observe: not only the selected cell is running, but all other cells with UDF (Test1() or Test2()) are also running  (Ex. "#GETTING_DATA")

• Check the Results of First-time Action
  • Observe1: By checking "Now" column in both Test1 and Test2 Tabs, it seems that all cells running behaviors are triggered almost at the same time

• Observe2: By checking "Today" column in Test2 Tab, sometimes it will cause formatting issue (Numeric instead of Date "m/d/yyyy")

• After First-time Action: UDF functionality back to normal
  • Observe1: One cell action will only trigger that selected cell running

• Observe2: No formatting issue anymore

For my project, formatting is quite important, so I really want to fix this issue. I'm quite new to ExcelDna community, any ideas or suggestions will be extremely appreciated. I know this is too much (words + pics), but if you have any questions please also let me know, I will explain ASAP.

 Thanks so much!

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

Easily add Excel-DNA support to your private or corporate GitHub account.

--------------------------------------------------

Hi Dennis,

First (for the benefit of other readers) I should note that you are using a "Dynamic Arrays" version of Excel, which makes the async array results work.

Otherwise that part would already present big problems.

Next, I would say doing the formatting from the UDF can be a bit tricky, and it would help to show exactly how you're doing that.

I suggest you post a test function that exhibits the same problem, with maybe just a Task.Delay(....) before returning a fixed array, instead of the http back-end call.

I'm wondering if the timing between your formatting and Excel deciding how to spill the array might not be the problem.

Excel is probably not expecting you to change the formatting between the start of the function call and the array spill.

Since the first call to the RTD server (which implements the async function) takes a different path, then timing or sequence might be different.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/c20510bb-4607-497a-bf7e-4bcd130a9e1dn%40googlegroups.com.

[denn1s]

Hello Govert,

Thanks for your reply. Please see bellows:

1. Yes, I'm primarily using Excel 365 for the Dynamic Array support.
2. Yes, I'd like to show you code example but not sure how to share? Could you please let me know what is the appropriate method.
3. My concern is: the formatting issue seems to occur only for the first call, which also triggers all other cells running. I'm wondering if multiple concurrent async http calls cause some timing issue. Actually, I have no idea why "all cells running instead of only the selected cell running" for the first call. Maybe we can focus on this triggering issue first, before the formatting issue?

Thanks! Best regards

[Govert van Drimmelen]

Hi Dennis,

Best is if you can post some sample code to GitHub or as a GitHub Gist.

I think it also works if you mail a zip file to the Google group as an attachment.

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/be50c5ae-4da7-4f09-9156-68a447933aeen%40googlegroups.com.

[Govert van Drimmelen]

Hi Dennis,

Looking at your code, one thing I can recommend is to put the COM-based formatting code inside a call to ExcelAsyncUtil.QueueAsMacro - something like this:

            // Normal Case -> Post process for Date/Time columns

            int rowNum = ((object[,])ret).GetLength(0);

            int colNum = ((object[,])ret).GetLength(1);

            // Run the formatting in a macro context when calculation is complete

            ExcelAsyncUtil.QueueAsMacro(() =>

            {

                HelperExcelFormat(rowNum, colNum, rowStart, colStart, shtReference, colDT);

            });

            return ret;

-Govert

On Wed, May 26, 2021 at 11:14 PM denn1s <j...@energycognito.com> wrote:

Hello Govert,

Sorry for the delay since I need to make sure there is no sensitive code snippet. Please check the attached zip folder. Below is the structure overview for the code example:

• Test2: ExcelDna Function code
• retHeaders: helper attribute for format process
• HelperHttpGetAsync: helper function to make HTTP request, parse JSON response (with headers) and update retHeaders
• HelperExcelFormat: helper function to process the Excel cell format (based on retHeaders and colDT dictionary)

I didn't replace the http backend call with Task.Delay() since the HelperHttpGetAsync will also update retHeaders helper array, which is used for the downstream format processing.

Please let me know if there is any question. Thanks!

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/221d52e1-82be-4753-9e1f-3ffa91a02f75n%40googlegroups.com.

[denn1s]

Thanks Govert, will try that way.