how to complete a background process in Excel-DNA correctly

[mymail.j...@gmail.com]

Hello!

I am developing an add-in for Excel using the Excel-DNA. The add-in make calculations based on the data in the Excel workbook. This process takes a long time. In order to inform the user of status of implementation, I use a form with ProgressBar. To update ProgressBar on the form and allow the user to cancel I tried to use computing background process using the class BackgroundWorker.

Everything works fine, but after closing the book, Excel stays as uncompleted process. I found this is due to Excel is a single-threaded application and complete the background processes necessary to use Runtime.InteropServices.Marshal.ReleaseComObject (ExcelDna.Integration.ExcelDnaUtil.Application). I added it to my code. As a result, Excel began to shut down, but I found when I close Excel pops up Windows Troubleshoot process. I opened the Reliability Monitor and saw when I use my Excel add-in, it always closed with the exception. Exceptions come in two modules : VBE7.DLL_unloaded or ole32.dll. I could not understand why this is happening .

Could you tell me how to solve this problem? Perhaps there is another way to implement background processes in Excel with Excel-DNA?

Thanks in advance

[Govert van Drimmelen]

Hi,

My recommendation would be to never talk to Excel on any thread other than the main thread.

If you follow this rule, you never need to call ReleaseComObject or worry about COM references going wrong.

Excel-DNA has a helper function to help you transition from another thread to the main thread - just call ExcelAsyncUtil.QueueAsMacro(...) with a delegate that you want to run in the main thread.

-Govert

[mymail.j...@gmail.com]

Govert, I recently started using Excel-DNA, and therefore do not quite understand how to properly use ExcelAsyncUtil.QueueAsMacro (...)

Perhaps my description may seem complicated, but it will help you better understand what I'm trying to do. I have the following project structure in VB.Net. There Calculation class that performs the calculations and generates Processing Event to inform the user about the progress. To be able to cancel computing by user I use property Canceled (stop  the calculation if it's True). Into class AddIn for AutoOpen I use ExcelAsyncUtil.Initialize (), and for AutoClose - ExcelAsyncUtil.Uninitialize ().


Friend Class Сalculation

    Friend Event Processing(ByVal Count As Long)
    Friend Canceled As Boolean

    Friend Sub Start()
        ExcelAsyncUtil.QueueAsMacro(AddressOf Сalculate)
    End Sub

    Private Sub Сalculate()
        ' Procedure in which all the calculations occur
        ...
        ' Event generation to inform the user about process
        RaiseEvent Processing(i)
        ' Check of Cancellation property.
        If Me.Canceled Then Exit Sub
        ...
    End Sub

End Class

Next, I declare class Calculation in the Form1 class and when event Shown happens I run the calculation process. Form subscribes to an event class Processing to update ProgressBar. If the user clicks Cancel, property Canceled becomes "True" and calculations should be stopped.

Public Class Form1

    'Subscribes to the events class Сalculation
    Private WithEvents XlСalculation As Сalculation

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        ' Class initialization
        Me.XlСalculation = New Сalculation
        Me.XlСalculation.Canceled  = False
    End Sub

    Private Sub Form1_Shown(sender As Object, e As System.EventArgs) Handles Me.Shown
        ' Launch of calculation
        Me.XlСalculation.Start
    End Sub

    Private Sub Cancel_Click(sender As System.Object, e As System.EventArgs) Handles Cancel.Click
        ' Processing cancellation when Cancel button pressed
        Me.XlСalculation .Canceled = True
        Me.Cancel.Enabled = False
    End Sub

    Private Sub XlСalculation_Processing(ByVal Count As Long) Handles XlСalculation.Processing
        ' Processing of event Processing of Сalculation class
        ' here I inform the user about the progress
        ' and refresh ProgressBar
    End Sub
   
End Class

I faced few problems:
- When I press the Cancel button is no shutdown
- Canceled property in Form1_Load is not set to False.
- ProgressBar is not updated (as it was in the BackgroundWorker). Also on Form1 I use animated GIF image, it also ceased to updated until calculations completed.
- How do I know that the class Form1 Calculation finished?

Could you tell me how to properly use ExcelAsyncUtil.QueueAsMacro regarding all these issues?

I would be grateful for any help from you

вторник, 29 апреля 2014 г., 19:53:05 UTC+3 пользователь Govert van Drimmelen написал:

[Govert van Drimmelen]

Hi,

How do you show the form - as a modal dialog?

How does this interact with Excel?

To show a modeless form it might be necessary to run it on a separate thread.

It is the communication back to Excel (via the Application object) that needs to be done via ExcelAsyncUtil.QueueAsMacro. Do you set the results back in Excel or something?

-Govert

[mymail.j...@gmail.com]

I show the form as a modal dialog box.

It's an add-in for Excel and interact with it in such way

1) read data from workbook

2) user select which data should be processed and then program make calculation like a break-even definition (use Goal Seek method)

3) after calculation, the add-in insert the result to new worksheet in active workbook

среда, 30 апреля 2014 г., 14:53:55 UTC+3 пользователь Govert van Drimmelen написал:

[mymail.j...@gmail.com]

Govert, could you have an example how can I realize a progressbar into a background (asynchronous) process with Excel-DNA? Calculation process which realized by me do not return result as UDF. It makes calculation and inserts result by itself to new worksheet. Perhaps I should use another features of Excel-DNA for user information of calculation process with cancelation ability. Could you help me regarding this?

The issue is that calculation process into main threat do not allow to realize a proper visualization of execution for user, and main problem is user have no ability of cancellation during calculation. If I have programmed in VBA I use DoEvents. But I think .Net and Excel-DNA give more opportunities.

среда, 30 апреля 2014 г., 16:43:53 UTC+3 пользователь mymail.j...@gmail.com написал:

[Govert van Drimmelen]

Hi,

If your form is to interact with Excel, for example to write partial results to it, then it can't be shown as a modal dialog on the main Excel thread. You can create a new thread on which you show the form as a modal dialog, then wrap any access to the Excel object model as delegates inside ExcelAsyncUtil.QueueAsMacro(...)

If you just want to create a long-running macro, write status information to the status bar, and give the user a chance to cancel out, you can use the C API with XlCall.xlAbort to check for the escape key. But it sounds like you have more interaction going on in the form?

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of mymail.j...@gmail.com [mymail.j...@gmail.com]
Sent: 30 April 2014 11:05 PM
To: exce...@googlegroups.com
Subject: [ExcelDna] Re: how to complete a background process in Excel-DNA correctly

--
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 post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[mymail.j...@gmail.com]

Thank you, Govert. I will try.

четверг, 1 мая 2014 г., 5:33:23 UTC+3 пользователь Govert van Drimmelen написал:

To post to this group, send email to exc...@googlegroups.com.