Solution/workaround for dynamically created UserForms resetting project on customization

[Gabor_Torok]

I have come across a seemingly undocumented VBA phenomenom that I would like to share with the only solution/workaround I could figure out.
My apologies for the length.

Histrory:
I am working on an VBA project that requires multiple UserForms being open at the same time where the number of possibly open forms are undefined.
To achieve this:

1. Forms need to be open vbModeless allowing for multiple forms being open
2. Forms need to be created and destroyed runtime, on demand, allowing for undefined number of forms to be created without accumulating undefined garbage.

Problem:
Creating, even formatting UserForms programmatically is not an issue. Plenty tutorials and blogposts covering steps required and they all work just fine. (As long as result is to be displayed vbModal as described below)

Destroying these forms is a whole different story that revealed a possibly more general limitation of certain VBcomponents.
In my case, the best way to destroy these forms would be an event procedure called on termination. For UserForms added as VBComponents are not supporting automation out of the box, the required procedures must be created programmatically too.

In theory, the Add-in model supports this type of customization by allowing for modification of the Code Module of the component. VBA even offers different ways to edit/alter code, like adding code line by line or importing full code as string even importing code from file.

In practice, this “support” seems to be very limited to say the least. I have found that any programmatical modification of the CodeModule resest the whole project automatically, without any warning.

And I have also found this phenomenon to be undocumented. All mentioned tutorials are creating forms as a standalone project, a demo if you like. In these tutorials the whole project is to create a form and demonstrate the ability of customization so when the project resets itself on competition is not an issue.
Full reset wouldn’t be an issue either for projects consist of isolated, stand-alone routines, without any linked procedures and/or any module level and/or global variables and/or user created objects etc.
That is if said form is displayed vbModal.
Displaying dynamically added UserForm vbModeless seems to be impossible if CodeModule is altered programmatically. The code runs immediately to the end which is full reset, so form is just flashing up then disappear.

To make situation worse, I was advised on Microsoft Support that the behavior is normal .. and that the complier must comply the text and also the designer wasn’t designed to modify running projects
Without going into why this explanation raises more question to me than gives answer to I just add that mu admittedly uneducated guess is that it is hopefully not the complier calling for reset for:

• Complier doesn’t seem to comply anything when code module is altered and the project is reset as opposed to the advice I was given.
• Complier doesn’t seem to comply anything either when I alter code module manually of any UserForm.
• Complier doesn’t seem comply anything either if I am importing a userform as form from a file.

And the latter is my workaround, for importing whole UserForm from file does not trigger full reset.

Solution/workaround:

To destroy dynamically created userforms the only procedure I need is a termination event procedure calling on form removal sub. And this procedure can be generalized/uniformized as per code below.
Therefore my workaround is to use an unformatted but pre-coded user form created manually as a template. And every time I need a userform, I rename template dynamically, export template and import back as a new form. And when terminated, I remove boyh form and tem files used.
This solution is everything but elegant and requires quite a garbage management, that I probably overdid, but does the job anyways.

The code module of template form:

The module calling on forms (in test phase)

Option Explicit

'  Calling Me.Name directly from Terminate is crashing application
Private mName As String

Private Sub UserForm_Initialize()
    mName = Me.Name
End Sub

Private Sub UserForm_Terminate()
    RemoveIfExist mName
End Sub

Driver module

Option Explicit
Option Base 1

Private Const mFormName As String = "mForm"
Private mCounter%

'   Driver
Public Sub ShowForm()
    Dim mName As String
    
    mCounter = mCounter + 1
    mName = mFormName & CStr(mCounter)
    
    Call AddNewFormToProject(mName)
    Debug.Print mCounter; mName
    
    VBA.UserForms.Add(mName).Show vbModeless

End Sub

'   Importing form from file
Private Sub AddNewFormToProject(ByVal a_Name As String)
    Dim mFile As String
    Dim mColl As VBIDE.VBComponents
    Dim mForm As VBIDE.VBComponent
    
    mFile = GetFile(a_Name)
    
    Set mColl = ThisWorkbook.VBProject.VBComponents
    Set mForm = mColl.Import(mFile)

    Application.VBE.MainWindow.Visible = False
    With mForm
        ' work in progress for testing only
        .Activate
        .Properties("Height") = UFORMHEIGHT ' project constant
        .Properties("Width") = UFORMWIDTH   ' project constant
        .Properties("Caption") = a_Name
        'Call AddControls(.Designer)
    End With
    
    Set mForm = Nothing
    Set mColl = Nothing
    Call RemoveTheseTempFiles(a_Name)
    
End Sub

'   Creating required file by:
'       Renaming manually created template
'       Exporting renamed template
'       Reverting template back to default
Private Function GetFile(ByVal a_Name As String) As String
    Dim mFile As String
    Dim mColl As VBIDE.VBComponents
    Dim mObj As VBIDE.VBComponent

'   if any garbage left behind previously
    Call RemoveIfExist(a_Name)

'   default location
    mFile = Application.DefaultFilePath & Application.PathSeparator & a_Name & ".frm"
    
    Set mColl = ThisWorkbook.VBProject.VBComponents
    Set mObj = mColl("UFormTemplate")
    
    Application.VBE.MainWindow.Visible = False
    
    mObj.Activate
    mObj.Name = a_Name
    mObj.Export mFile
    mObj.Name = "UFormTemplate"
    
    GetFile = mFile

End Function

Public Sub RemoveIfExist(ByVal a_Name As String)
    Call RemoveTheseTempFiles(a_Name)
    Call RemoveThisVBComponent(a_Name)
End Sub

Private Sub RemoveTheseTempFiles(a_Name As String)
    Dim mPath As String
    
    mPath = Application.DefaultFilePath & Application.PathSeparator
    
    If Dir(mPath & a_Name & ".frm") <> "" Then Call Kill(mPath & a_Name & ".frm")
    If Dir(mPath & a_Name & ".frx") <> "" Then Call Kill(mPath & a_Name & ".frx")
    
End Sub

Private Sub RemoveThisVBComponent(ByVal a_Name As String)
    Dim mColl As VBIDE.VBComponents
    Dim mObj As VBIDE.VBComponent
    
    Set mColl = ThisWorkbook.VBProject.VBComponents
    
    On Error Resume Next                    ' if object doesn't exist
    Set mObj = mColl(a_Name)
    
    If Err.Number = 0 Then Call mColl.Remove(mObj)

End Sub

'   Houskeeping, called by Workbook.BeforeClose event
Public Sub CleanUpForms()
    Call RemoveAllTempFiles
    Call RemoveAllAddedForms
End Sub

'   Removing all temp files left behind
Private Sub RemoveAllTempFiles()
    Dim mDir As String
    
    mDir = Dir(Application.DefaultFilePath & Application.PathSeparator, vbDirectory)
    
    Do While mDir <> ""
        If InStr(1, mDir, mFormName) <> 0 Then
            Kill mDir
        End If
       mDir = Dir()
    Loop
End Sub


'   Removing all forms left behind
Private Sub RemoveAllAddedForms()
    Dim mColl As VBIDE.VBComponents
    Dim mObj As VBIDE.VBComponent
    
    Set mColl = ThisWorkbook.VBProject.VBComponents
    
    For Each mObj In mColl
        If InStr(1, mObj.Name, mFormName) <> 0 Then
        End If
    Next
End Sub

Notes:

• Environment: Win10Pro 64Bit, Microsoft 365
• I haven't tested CodeModule alterations of other vbComponents but UserForms only.
• I am not a formally or otherwise trained professional, that is probably obvious from the code, so please consider all above accordingly.

 

[Gabor_Torok]

if only
On my end this code does only one thing and only once: deleting my template.
After deleted, rerun, obviously, yields complie error for user defined type doesn't exist

but only deletes template if there is anything in code module.
If form is perfectly blank, code does nothing really but select template, then on second run displays it.

 

[RoryA]

There is nothing in that code that would delete anything unless you have code in the form’s Initialize event that does that. All that code does is create a new form instance. It does not show it as I assumed you’d want to manipulate it first.

 

[Gabor_Torok]

if only
On my end this code does only one thing and only once: deleting my template.
After deleted, rerun, obviously, yields complie error for user defined type doesn't exist

But only deletes form if it has code in its codemodule.
If form is perfectly blank, declaration does nothing really but:
On first run activates form in designer window
On secon run displays form

 

[Gabor_Torok]

There is nothing in that code that would delete anything unless you have code in the form’s Initialize event that does that. All that code does is create a new form instance. It does not show it as I assumed you’d want to manipulate it first.

Yep, that is exacly the case. I do have code in initialization and I need it.
Also, like I said, it doesn't create anything to me, not even a perfectly blank one.

 

[RoryA]

I’m not saying you can’t have code. I’m saying that if the code I posted deletes anything, it can only be because you have code in the initialise event that deletes it. You should not need that.

As I also said, the code does not show the form that is created because I assumed you would want to manipulate it first. If you run that code in isolation, you will not see anything but it is creating a form.

 

[Gabor_Torok]

I’m not saying you can’t have code. I’m saying that if the code I posted deletes anything, it can only be because you have code in the initialise event that deletes it. You should not need that.

As I also said, the code does not show the form that is created because I assumed you would want to manipulate it first. If you run that code in isolation, you will not see anything but it is creating a form.

I think I finally get it and seems brilliant
Am I right assuming that forms created this way are object instances within project and once out of scope (i.e. closed) garbage collection is automatic? Even for class object instances added to form?

Also, will need to think through how to to avoid using Initialize for it would be handy to initialize and drive all class and other objects added to form. But I will figure out

Again, thanks, I am loving it:

Option Explicit

Private Const mFormName As String = "Test"
Private mCounter%


Sub showForm4()
    Dim mForm As TestTemplate
    Dim mName As String
    
    mCounter = mCounter + 1
    mName = mFormName & CStr(mCounter)
    
    Set mForm = New TestTemplate
    
    With mForm
        .Caption = mName
        .Width = .Width + mCounter * 10
        .Height = .Height + mCounter * 10
    End With
    
    mForm.Show vbModeless

End Sub

 

[RoryA]

Am I right assuming that forms created this way are object instances within project and once out of scope (i.e. closed) garbage collection is automatic? Even for class object instances added to form?

Yes to the first part. The class instances follow normal rules of scope and lifetime, so the answer will depend on where and how they are declared.

 

[Gabor_Torok]

Yes to the first part. The class instances follow normal rules of scope and lifetime, so the answer will depend on where and how they are declared.

Will keep in mind, many thanks.
BTW, any chance that you have something up in your sleeves for adding/displaying chart(object) to/on form without creating temp chart on worksheet and exporting as GIF and importing back where chart data comes from project data, not worksheet data?

 

[RoryA]

No, not really something I’ve ever needed. The Office Web Components were good for that but they were deprecated back when 2007 was released.

 

[Gabor_Torok]

No, not really something I’ve ever needed. The Office Web Components were good for that but they were deprecated back when 2007 was released.

Thanks, GIF it is then