My problem pertains to a COM add-in for Microsoft Excel. The use case is as follows: 1. User clicks the add-in's button on the ribbon. 2. A form window appears. 3. User interacts with the form window and clicks an OK button. 4. Various reports are generated, while a progress bar on the form window shows progress. 5. At the end of the process, the form window closes.
The process works as designed on the first run, but after the form window has been closed there is no way to start a new "session." From the user's perspective the add-in button becomes non-responsive. When run in debug mode from Visual Studio, clicking the add-in button a second time generates an error message: "Cannot access a disposed object."
Clearly something is wrong with the way I have hooked everything up, but I haven't been able to find a simple description of how to do it correctly. Here is what I have:
In a public class a number of public (or "global" variables) are declared; the form is also declared and instantiated here:
Public Class GlobalVariables
Public Shared FormInstance As New MyFormDesign
End Class
The reason for declaring the form as a public object is to be able to be able to send progress values from various different subs and functions. The GlobalVariables class is imported by all modules that require it.
Behind the ribbon button is a single line of code:
FormInstance.Show()
Clicking the button instantiates and shows the form as intended. To keep things simple we can ignore the bulk of the code; simply clicking the "Cancel" button will trigger the problem. The code behind the "Cancel" button is straightforward:
Me.Close()
GC.Collect()
After closing the form it is no longer possible to create a new instance, per the error message cited above.
I don't really understand what is going on here, but it looks to me like the GlobalVariables class, once created, persists until the end of the Excel session. If that is correct the problem could presumably be cured by instantiating the form in a standard module. Instead of attempting to revive a form that has been disposed, the add-in would just create a new instance each time the user clicks the button. But if I go that route I can't figure out how to send progress values from other subs back to the form. It seems like a Catch-22.
There has got to be a way to both (a) create the form as a public object, and (b) create and destroy a new instance each time the add-in is run. Right? What am I doing wrong?
It has been a long journey, but I finally found out how to build the functionality described in my question. I will post the answer here, as it may help others in the future.
The challenge was to declare a form as Public in order to make it accessible throughout the project, so that subs and functions can send progress updates back to the form.
The problem was that the form, when declared and instantiated as described in my question, can only be created once per Excel session.
The solution is to declare the form as Public without instantiating it, then access it via a Public ReadOnly Property.
First, declare FormInstance as a public variable without instantiating it:
Public FormInstance As MyFormDesign
Second, define a Public ReadOnly Property. This establishes a way to call the form:
Public ReadOnly Property CallMyForm() As MyFormDesign
Get
Return FormInstance
End Get
End Property
Third, the ribbon button's Click event instantiates and shows the form:
Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
FormInstance = New MyFormDesign
FormInstance.Show()
End Sub
Now a new form instance will be created each time the ribbon button is clicked, but the form can still be accessed via the CallMyForm property.
Instead of ...
FormInstance.BackgroundWorker1.ReportProgress(i)
... we will use:
CallMyForm.BackgroundWorker1.ReportProgress(i)
This solves the conundrum laid out in the question.