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]

Sorry, found a couple typos..:

... Solution/workaround: .....And when terminated, I remove boyh both form and tem temp files used......

... The code module of template form: The module calling on forms (in test phase)...

 

[RoryA]

And every time I need a userform, I rename template dynamically, export template and import back as a new form

Why?

 

[Gabor_Torok]

Why?

Not sure that I understand your question.
Which action doesn't make sense to you?

 

[RoryA]

The bit I quoted.

 

[Gabor_Torok]

The bit I quoted.

I am terribly sorry Rorry, for being thick, and still not understanding, so here is my thinking behind each action in quoted text:

1. I rename template prior to saving to a new, unique user form name to avoid error on trying to import a form/form name that already exists in project.
2. I export template so I will have something to import.
3. I add new userform via import for this is the only way I found that allows me to create/add any new user form during runtime that has codes in its code module . All other ways to add code to userform/module runtime resets the whole project for me.

And just to prove, how confused I am here is the reason for needing undefined number of user forms at random times:
The whole project is about testing/simulating different forecast methods for inventory items with known history. And there are a losts of items.
And most of the calculated details and stats are just temporary data, e.g. moving average, stored in RAM, that I will not save or export or even write out to spreadsheet.
Nonetheless, I would like to see those data, and their charts, preferably more than one sets at a time, side to side for quick comparison. For no particular reason but personal preference.

Sorry again for overcomplicating/oversharing but I am really confused. I mean, I do not like the idea of using temp files and will only stomach it because I will need temp files anyways for the graphs displayed on forms too. But would happily switch to a better solution if you have a suggestion.

 

[RoryA]

Since you appear to be using the existing form simply as a template, why can't you just declare a new instance of it?

 

[Gabor_Torok]

Since you appear to be using the existing form simply as a template, why can't you just declare a new instance of it?

Declare how?

 

[RoryA]

Like this:

dim uf as templateform
set uf = new templateform

 

[Gabor_Torok]

Like this:

dim uf as templateform
set uf = new templateform

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