?Scope of Public Sub Procedure In UserForm Module: I Can Not Call it From Worksheet Module

[Default300]

Hi.

I want to write a Sub.
I want to Call the Sub from a UserForm Module (CommandButton_Cilck Event).
I want to Call the Sub from a Worksheet Module (Selection_Change Event).

What Type of Module must the Sub be in?
What Type of Sub should it be? (Presumably Public)

Currently, my Sub is a Public Sub.
Currently, it is in the UserForm Module.
I can Call it from the UserForm Module (CommandButton_Cilck Event).
I cannot Call it from the Worksheet Module (Selection_Change Event).
The latter outcome is the same whether UserForm is Loaded or not Loaded.

The Sub is not "visible" to IntelliSense from the Worksheet Module by default.
The Sub is "visible" to IntelliSense from the Worksheet Module, if I prefix the Sub Name with the name of the UserForm (eg UserForm1.PublicSubName).
However, if I run this code, it ?reinitializes the userform or ?creates a new instance of it, which obviously is useless.

Is the answer to the question: Create a Public Sub in a Standard Module?

PS: I may not see your reply for a few hours.

 

[Default300]

Hi Rory. Thanks.

Regarding 'Load', when you run the Set frm = New ... statement, you load the form, so you don't need Load. (Or Unload, since setting the variable to Nothing unloads it)

Re: Unloading Form:

If Form is no longer required, (and all relevant data has been harvested so no need to Hide first)...

[?] Which is better practice:
(1) Set Object Variable to Nothing, causing the Form to Unload
(2) Use the Unload method, and (presumably) subsequently Set Object Variable to Nothing

Post #5 above shows my old Form Unload (and Load) code. [ #5 ]

I've now moved these to 2 separate procedures in a Standard Module as follows:

[FONT=Courier New]Public Sub gnlFrmInstanceSFMLCLoad()

'(P)    [AIM]   LOAD "SORT FILTER & NAVIGATE" FORM

'[NB]   Project Level Variable (frmInstanceSFMLC)
'       Declared In (PublicVariableDeclarations) Standard Module

    Set frmInstanceSFMLC = New frmSortFilterNavigate46
    
    With frmInstanceSFMLC
    
'(i)    Store Current Worksheet Name In Order To:
'       Ensure Toggle Button On Correct Sheet Is Reset When Form Is Unloaded
'       Provide Warning Message On Attempt To Edit Another "DATAENTRY" Sheet (In Case Acccidental)
    
        .Tag = ActiveSheet.Name
    
'(i)    Show Form

        .Show
    
    End With
    
End Sub

Public Sub gnlFrmInstanceSFMLCUnload()

'(P)    [AIM]   UNLOAD "SORT FILTER & NAVIGATE" FORM

    Dim frmUserForm As Object
    
    If UserForms.Count <> 0 Then
    
        For Each frmUserForm In UserForms
    
            If frmUserForm.Name = "frmSortFilterNavigate46" Then
    
                Unload frmUserForm
                
            End If
                
        Next frmUserForm

    End If

    Set frmUserForm = Nothing

End Sub[/FONT]

I think I may cut the Unload Sub down to the following:

[FONT=Courier New]Public Sub gnlFrmInstanceSFMLCUnload()

'(P)    [AIM]   UNLOAD "SORT FILTER & NAVIGATE" FORM

'[NB]   Project Level Variable (frmInstanceSFMLC)
'       Declared In (PublicVariableDeclarations) Standard Module

    Set frmInstanceSFMLC = Nothing
    
End Sub[/FONT]

The loop in the Unload Sub is probably not required now, for two reasons:

(1) Hopefully I will erradicate the problem of accidental multiple instancing altogether.

(2) The loop was introduced to ensure that all instances of the Form were Unloaded. However, (as mentioned earlier) if there are 2 instances in existence... even though UserForms.Count = 2 initially, when the Loop Unloads the first Form that matches the "Name" (possibly Class Name rather than UserForm Name, see xenou's Post #6 [ #6 ]) both instances Unload anyway.

Incidentally, if remember correctly, if I used a For Next Loop (rather than a For Each) then I got an error because the For Variable is initially set to 2 (UserForms.Count), but when it attempted Loop 2, UserForms.Count = 0, and (unless I rechecked If UserForms.Count <>0), the Unload method failed.


Re: Public Level Variables & "LifeTime":

[?] Once Declared and Initialised with a Value, do they stay "alive" until the WorkBook is closed?

[?] If so, how can you "Kill" them (ie return them to their Uninitialised state)?

I know that you can Set ObjectVariable = Nothing.

But how do you do the equivalent with Numerical, Boolean, and Strings and Arrays?

As far as I remember, I can NOT Let Variable = Null.

I know DataType Default Values as follows:
Booleans = False
Numericals = 0
Strings = "" (or ?vbNullString)

Are these Values the same as Null / Uninitialised?

 

[xenou]

Hi Columbo.

Some quick shots at these questions:

[?] Which is better practice:
(1) Set Object Variable to Nothing, causing the Form to Unload
(2) Use the Unload method, and (presumably) subsequently Set Object Variable to Nothing

Use Number 2 - unload, then set to nothing. I don't know if you'd find any negative impact in the actual case here, but its good practice, and a good habit. Generally speaking, if an object has a close, unload, or quit method, its good to call it when you are finished with the object, even though this is not always necessary. An interesting test would be to see if the form close events and form unload events fire when you set them to nothing without unloading them first. Offhand, I'm not sure.

On changing your loop - yes, the loop should no longer be necessary if there aren't multiple instances. When you use a loop like this based on the form indexes, the form indexes probably "drop" every time one is removed, so if you did use a loop try it this way: unload form(0), and use a do while to continue doing so while the form count is greater than or equal to 1. Or use the loop counter from last to first (For x = Forms.Count - 1 to 0 Step -1). As an aside, with a for each, you'll get a reference to each form in turn - so it would only unload one at a time still (your test for the name would only be applied to the form that its currently on. They all have the same class name but Excel keeps track of them internally as separate instances of the class). If you are sure you aren't getting any more multiple instances than its moot anyway.

Publics and Private variables with project or module scope last during the Excel session. They can be reset under some circumstances (Rory mentioned this in an earlier post) - after a crash, or if you hit reset in the vba environment. As you said, you "kill" object variables by setting them to nothing. With variants you can set them equal to empty (x = Empty). Primitives such as strings or integers also can use the Empty keyword in an assignment statement. Or you can just use their default values such as zero for integers, empty strings for strings, and false for booleans - as you guessed, this is what they are equal to if you've never used them.

 

[Default300]

Hi xenou.

Just a quick message to thank you for your reply and advice, and to apologise for not doing so sooner.

I've been working on the project, applying some of the changes discussed, and so haven't had time to write again.

I probably will in the next few days, as I've had a problem with the userform not unloading immediately (more details when I write).

In the meantime, cheers for your help.