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.

 

[RoryA]

One thing you could look at is using API calls instead - search for Stephen Bullen's PastePicture code.

 

[Gabor_Torok]

One thing you could look at is using API calls instead - search for Stephen Bullen's PastePicture code.

Thanks a lot Rory, found it, will give it a go.
And again, I truly appreciate your help and guidance. Pointed me way beyond original issue.
And as a result, now I am wasting time with learning, experimenting, instead of making progress, haha

Btw, funniest thing I have found is that:
If I need the instance of the form within the same project runtime
and I only need to customize the content of the form, not its own properties, then
I probably don't even need an instance at all

More properly, I don't have to do the instancing explicitly (explicit feels better practice tho):

Option Explicit

'   Implicit instance
Public Sub Instanceform1()
    Static mCounter%
    Dim mLabel As MSForms.Label
    
    mCounter = mCounter + 1
    
    Set mLabel = UserForm1.Controls.Add("Forms.Label.1", "Label1")
    mLabel.Caption = "Instance" & mCounter

    UserForm1.Show vbModeless
    
End Sub

'   Explicit instance
Public Sub Instanceform2()
    Static mCounter%
    Dim mForm As UserForm1
    Dim mLabel As MSForms.Label
    
    mCounter = mCounter + 1
    
    Set mForm = New UserForm1
    Set mLabel = mForm.Controls.Add("Forms.Label.1", "Label1")
    mLabel.Caption = "Instance" & mCounter
    
    mForm.Show vbModeless

End Sub

 

[RoryA]

Yes, userforms have a default instance. That will only allow you to use the form once at any time though, of course, not use it as a template for multiple loaded forms.