Runtime Warning - PostMessage Error 1816

[Giovanna Gomes]

Good evening.

I developed an UDF to mimic bloomberg's BDH function behavior. My function relies on ExcelAsyncUtil.Run to read data from a REST API and return the data assynchronously to the caller exactly as bloomberg does. Because bloomberg does not use arrays, the function only returns the first range as the function value and enqueue a macro in order to "paint" the rest of the values to the cells range. An ilustrative sample code is given bellow:

 public static object PDH(
              [ExcelArgument(AllowReference = true, Name = "symbol")] object symbol,
              [ExcelArgument(AllowReference = true, Name = "Field")] object field,
              [ExcelArgument(AllowReference = true, Description ="The start range date as an excel date or string in format YYYY-MM-DD", Name = "StartDate")] object startDate,
              [ExcelArgument(AllowReference = true, Description ="The end range date as an excel date or a string in format YYYY-MM-DD", Name = "EndDate")] object endDate
            )
        {

// Main run
            try {
                object result = ExcelAsyncUtil.Run("Fetch Historical Data",
                    new[] { symbol, field, startDate, endDate },  delegate { return FetchHistoricalData(psymbolStr,
                                                          field,
                                                          startDate,
                                                          endDate); });


                // Handle async response
                if (result.Equals(ExcelError.ExcelErrorNA))
                {
                    return CustomMessage.FETCHING_DATA;
                }
                else if (result is ApiResponseException)
                {
                    ApiResponseException ex = (ApiResponseException)result;
                    return "#N/A " + ex.Message;
                }
                // Put results to cells
                else
                {
                    List<DataHist> resultList = (List<DataHist>) result;

                    if (resultList.Any())
                    {
                        // Get reference to caller from excel application
                        Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
                        ExcelReference reference = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

                        // Get worksheet of function caller
                        var item = XlCall.Excel(XlCall.xlSheetNm, reference) as string;
                        int index = item.LastIndexOf(']');
                        string sheetName = item.Substring(index + 1);
                        string workbookName = item.Substring(1, index - 1);

                        Excel.Worksheet ws = app.Workbooks[workbookName].Sheets[sheetName];

                        // Emulate Bloomberg BDH function behavior. We return the first cell result
                        // to function call and paint the rest of the result with a macro running on background
                        // of excel process.
                        if (startDateTime != null && endDateTime == null)
                        {
                            return resultList[0].Value;
                        } else
                        {
                            ExcelAsyncUtil.QueueAsMacro(() => PaintResultToCells(resultList, reference, ws, app));
                            return DateTime.Parse(resultList[0].Date);
                        }
                       
                    }
                    else
                    {
                        return CustomMessage.ERROR_EMPTY_DATA;
                    }
                 
                }
            }
            catch (Exception e)
            {
                return "#N/A " + e.Message;
            }
        }

I am sorry if the code is not on the proper format, but it is the first time that I am using this forum to post a question.

The function used to work like a charm. However, when we call a macro that updates a lot of information at once, for example updating formulas calculation, gets data from bloomberg, uses my UDF function, etc, it gives the following Runtime Warning on Diagnostics Display:

Runtime [Warning] SynchronizationWindow - PostMessage Error 1816

Does any one knows what this warning means and what can I do to fix it? Google, unfortunatelly, didn't help in this case.

Also, any other references that could make my function implementation better are welcome.

Best,

Giovanna

[Govert van Drimmelen]

Hi Giovanna,

 

It’s not a problem I have seen reported before, though it is a tricky bit of code that implements the relevant part of Excel-DNA.

 

The warning you see from Excel-DNA will occur if the message queue is full for an Excel-DNA internal window used to set up the call back to Excel.

The error code 1816 is returned from an internal call to the Win32 PostMessage API, and is a fairly generic error that has the description "Not enough quota is available to process this command." But in this context it just seems to meant that the message queue is full.

 

It is unlikely that Excel-DNA code is filling up the message queue on this window, even with many QueueAsMacro calls outstanding.

However, other code running in the Excel process (including Excel itself) might also be posting message to the queue.

While Excel is busy, the queue would not be services, so any such messages from elsewhere could pile up and cause the error.

The one thing you can check in your code is that you are never calling to the COM object model from another thread (or process).

Such calls all have to pass through the main thread’s message queue, and so might be interfering.

The intention behind the QueueAsMacro mechanism is to ensure you can run code on the main thread.

The code you show seems OK, but you suggest there is other work going on in parallel somehow.

If that other work is talking to the Excel COM object model, it _must_ be running on the main Excel thread.

 

Excel-DNA does try to handle this situation correctly, and will retry the work later. So this Warning message shows that something has happened, but it is not an error and your code should still execute correctly.

With the default logging settings, you would not normally see this warning (since the LogDisplay is only shown if there is an error).

Are you showing the LogDisplay yourself, or is there a later error message also?

 

 

If you want to investigate further, you might want to find a simple situation which recreates the problem.

Perhaps can you make a new tiny add-in with some of the slow calls and some QueueAsMacro work that would be outstanding.

This would allow me to have a closer look if I can then reproduce on my machine.

You might also test that you can recreate the problem with a simple add-in, running in Excel with no other add-ins running, on a Windows instance with nothing strange running.

 

-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/af72d416-d3f5-4970-b970-2007a5695b67n%40googlegroups.com.

[Giovanna Gomes]

Hi Govert,

Thank you very much for such good explanation.

Excel-DNA does try to handle this situation correctly, and will retry the work later. So this Warning message shows that something has happened, but it is not an error and your code should still execute correctly.

Indeed, the code executed correctly. Just to give you a little bit more context, this add-in is used on some worksheets on my company, so I was not the person who was using it when the Diagnostics window popped up. I tried to run the same macro on my machine and I couldn't reproduce the error at all. I know that my machine have better configure compared of the person machine who runned the macro, but I don't know if it relates to the Post message warning. I will ask a collegue tomorrow to try to run the same code and see if the warnings shows. It is the first time we saw this behaviour.

As I mentioned, we have a macro that opens a lot of other excel's worksheets and call other and other macros. I don't understand the process to be honest and I didn't wrote the macros, but it seems like some heavy calculations are occurring under the hood.

With the default logging settings, you would not normally see this warning (since the LogDisplay is only shown if there is an error). Are you showing the LogDisplay yourself, or is there a later error message also?

Nope. I didn't even know that we could customize the logs before you said hehe. I searched and I found out that there is a way to customize logs in excel-dna through a .config file (https://github.com/Excel-DNA/ExcelDna/wiki/Diagnostic-Logging). Is that this file you are referring when you asked for a logging settings? I can confirm that the only .config file that I see in my project is the packages.config, but I am not sure if we are talking about the same thing. I just didn't understand if this is a default file created by Excel Dna or if I have to create one in the root folder with the name convention specified in the link (AddiName.xll.config). I would be glad if you can clarify.

If you want to investigate further, you might want to find a simple situation which recreates the problem.

Sure, I will try to recreate exactly as you told me and I get back to you tomorrow (if I had success or not hehe).

Kind Regards,

Giovanna

[Govert van Drimmelen]

If you don’t have a special App.config file in your project (or <MyAddIn>.xll.config file in the output) then you’ll be running the default logging configuration.

In this case the user should only have seen the LogDisplay window if there was some error that occurred, in addition to the warning you post.

That error might be more important than this warning.

 

By setting up an app.config file as described in the Diagnostics logging wiki you point to, you can also write warnings and errors to a log file – that might help you monitor the situation.

I think Verbose logging will be too much, though. You should check in your environment.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/0f14f955-2354-4639-a98d-02298e253fb9n%40googlegroups.com.

[Giovanna Gomes]

Hi Govert,

I could reproduce the error on a machine with worst configuration than mine. This was the error that popped up with the Runtime warnings:

Runtime [Error] Unhandled exception in async delegate call. : XlCallException - Exceção do tipo 'ExcelDna.Integration.XlCallException' foi acionada.

Sorry, the error message is on my native language, but it only says that a XlCallException was thrown. As you can note, everything on my code is surrounded with a try/catch, but it seems that this exception was not caught.

I runned the code on debug mode on vs code and I see that the code "crashes" when I return the result of REST API call. This is the code that fatches data from the API:

   public static object FetchHistoricalData(string psymbol,
                                                             string field,
                                                             DateTime? startDate,
                                                             DateTime? endDate)
        {
            /**
             * <summary>Fetch data from historical data (PDH) endpoint. </summary>
             **/

            string url = apiUrl + "/pdh/" + field + "/" + psymbol;

            // Add start and end date to endpoint if required
            string dateFormat = "yyyy-MM-dd";

            if(startDate != null)
            {
                url += "?start_date=" + ((DateTime) startDate).ToString(dateFormat); 
            }
            // @TODO: There must exist a better way to build an url endpoint on C#.
            if(endDate != null)
            {
                if(startDate != null)
                {
                    url +=  "&";
                } else
                {
                    url += "?";
                }
                url += "end_date=" + ((DateTime) endDate).ToString(dateFormat);
            }

            try
            {
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

                using (HttpWebResponse res = (HttpWebResponse)request.GetResponse())
                {
                  
                        using (Stream stream = res.GetResponseStream())
                        {
                            using (StreamReader reader = new StreamReader(stream))
                            {
                                string data = reader.ReadToEnd();

                            if (data != null)
                            {
                                if (res.StatusCode.Equals(HttpStatusCode.OK))
                                {
                                    try { // Here the code crashes

                                        SuccessResponse<List<PandDataHist>> successResponse = JsonConvert.DeserializeObject<SuccessResponse<List<PandDataHist>>>(data);
                                        return successResponse.Data;
                                    } catch
                                    {
                                        return "ERROR";
                                    }
                                }
                                else
                                {
                                    ErrorResponse errorResponse = JsonConvert.DeserializeObject<ErrorResponse>(data);
                                    return new ApiResponseException(errorResponse.Message);
                                }
                            }
                            else
                            {
                                return new ApiResponseException("NULL");
                            }
                            }
                        }
                   

                }
            }
            catch (Exception e)
            {
                // Obs: We return and not throw exceptions because Excel Dna' ExcelAsyncUtil.Run
                // does not deal well with exceptions.
               
                return new ApiResponseException("Unable to fetch from API url " + url + ": " + e.Message);
            }
        }

I left a comment where the code crashes. I confirm that the code runs well in an environment that I am not running a macro with multiple calls. What is really weird to me is that this exception is not caught in none of the try/catch blocks. Do you have any clue of this kind of error? I will try to make a generic function in order for you to reproduce this error if you don't.

Thanks!

[Craig Crevola]

HI Giovanna

 

Given Govert’s answer regarding the message queue being full, how many concurrent requests are you queueing before this ‘fails’? Looking at the code posted, the methods used in WebRequest are blocking calls, so perhaps using their Async counterparts may help. I have not used the ExcelAsyncUtil before so am not sure if this will make a difference. Also the WebRequest class is not recommended to be used by Microsoft anymore, in favour of HttpClient, however, I do understand that for corporate work you may not be in a position to make that sort of change.

 

For some more logging you can wire up a tracewriter (I use Serilog FileSink) to capture any additional information for debugging your clients issues.

 

One advice I may give, is that I’d try and separate the data gathering from the presentation/calculation logic. I have done realtime web sockets excel plugins for financial services before and favoured RTD as the middleware mechanism between the socket server, data cache and excel sheet. This can isolate any latencies or issues you may encounter on the request side of things. Sockets based programming is more reactive, but since you mentioned the history function, it may not be relevant directly. I’d still try and keep the http requests as seperated as possible from the main excel thread and favour the use of the async methods to limit/mitigate any blocking.

 

Hope that helps

 

Craig.  

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/ed77ed9b-dfff-4e6c-bb8a-9ae7da36a0b8n%40googlegroups.com.

[Giovanna Gomes]

Hi Craig,

Thank you very much for your time.

Given Govert’s answer regarding the message queue being full, how many concurrent requests are you queueing before this ‘fails’?

Well, as I told Govert, this fails when running a macro to update update worksheet formulas and the prices that comes from bloomberg API. I cannot explain what it doest precisely not because it is some sort of secret, but because I really don't know hehe. But regarding the calls to my function, we have exactly 6 formulas on a worksheet and they are updated in a part of the routine that calls Application.CalculateFullRebuild. I think that they should be doing 6 calls, but I don't know if maybe there is other part of the code that is forcing more and more calls so that it breaks. It is worth mentioning that it does not breaks if I have 100 formulas in the worksheet and call Application.CalculateFullRebuild only.

Looking at the code posted, the methods used in WebRequest are blocking calls, so perhaps using their Async counterparts may help. I have not used the ExcelAsyncUtil before so am not sure if this will make a difference.

I believe that Govert is a better person to explain you this correctly. But as I understand, what the ExcelAsyncUtils.run does it to run a function outside the main thread of the excel, assynchronously. If you ever used the Bloomberg's API before, it acts exactly like it. For example, I can call the function  and keep using my spreadsheet normally, without it "freezing" the worksheet, as you expect a macro to do. But, as I said, I may be mistaken. That is why I chose to make a "normal" http request without using C# async capabilities. I thought that AsyncUtils would do the trick, and it seems it is doing. I would like to know from Govert if he seems any problem doing it that way.

Also the WebRequest class is not recommended to be used by Microsoft anymore, in favour of HttpClient, however, I do understand that for corporate work you may not be in a position to make that sort of change.

Fortunately, I am in the position of doing any chance. I didn't know about WebRequest, thank you for the information. I am a Java Developer, I am used to it and it is the first time that I code in C#. I will for sure take your experience in consideration. I used an REST API in the place of RTD because
1) I think it would suit well the case of historical data

2) It was easily for me to understand what is going on.

Do you think that, even for historical data, it is better to use RTD? For the examples  that I saw on the internet, RTD would delivery data continuously and I only want to gather data once the user requested. Is there any way to have this behaviour using RTD?

Other point that I would like to mention is my comment regading the previous code that I posted. I return an expection instead of throwing it because I was not able to handle it properly on the main code. I simply couldn't catch it, even that the PDH code is surrounded on a try/catch block. Probably I am doing something wrong.

Regards,

Giovanna

[Giovanna Gomes]

One update: I was able to reproduce the Runtime [Error] Unhandled exception in async delegate call in a macro that opens 4 workbooks. After opening the workbooks, the error occurs when we call Application.Calculation = xlAutomatic.

It is worth mentioning that this only occured when I runned the macro at the first time. Even after restarting my computer, I was not able to see this error again.

Best,

Giovanna

[Craig Crevola]

HI Giovanna

 

With regards to RTD, I only mentioned it as sometimes I see people doing many calls to Http service and it causing them issues. From what you have described and your use case I agree it could probably complicate things unnecessarily since you are only doing small amounts of calls. My only suggestions at the moment are to try and add some logging into the FetchHistoricalData method to see how much it is getting called to try and isolate the problem.

 

Thanks

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/45a2a3c1-1c0e-4e13-93a6-93ffe14edff4n%40googlegroups.com.

[Giovanna Gomes]

Hi Craig,

Sure, I already set up an NLog that will log all INFO, WARN, ERROR and CRITICAL messages. I put an info message when the API is called and when it returns a success value.

Refering to the code that I said that I had the error in the morning, I was able to run it successfuly without any errors. There are no API calls, as it should be. This code only opens the sheets and set the calculation mode, which should not affect UDF functions. I will let you know if I can get a log from an execution with errors.

Regards

[Giovanna Gomes]

Craig and Govert,

I created a new topic about this because as Govert said, my problem was not the Runtime Warning but and Error. I thought that it would be more intuitive for people in the future creating a new topic regarding this error and included the full log of an execution to check if you can help me about it.

Thank you very much for the support,

Giovanna