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

Default300 · Jun 8, 2010

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.

xenou · Jun 8, 2010

Yes, standard module.

The thing to remember is that a sub in a form does not "exist" unless the form is instantiated - its only a "blueprint" for a form until then. Apparently Excel is kind enough to create the form for you so you can run the sub, but that's a dubious favor.

Edit: note, you will find that you can call the public subs in a standard module from the form, should you need to do that.
This article is very helpful I think, on working with userforms where there is information to be passed back and forth:
http://www.dailydoseofexcel.com/archives/2004/07/22/passing-arguments-to-a-userform/

Default300 · Jun 8, 2010

Thanks xenou. Will do that then.

... a sub in a form does not "exist" unless the form is instantiated - its only a "blueprint" for a form until then. Apparently Excel is kind enough to create the form for you so you can run the sub, but that's a dubious favor.

Thanks for the explanation of "Why". I find the following behaviour strange though...

Even if the UserForm is Instantiated / Loaded, other Modules can't Run the Sub. (I get Error 35 "Compile Error - Sub or Function Not Defined").

If I specify UserFormName.PublicSubName, it creates a New Instance of the Form to attempt to Run the Sub, but as far as I remember, it Errors out once the Form is Loaded. (Just tested it quickly. First time it created a new instance, with no error, but don't think it ran the Sub. Second time, I don't think it created a new instance, and I got "Run-time error '91': Object variable or With block variable not set".)

Anyway, the short answer is: Create Public Sub in Standard Module. Thanks.

PS: Re: editting previous posts. Is it impossible after the ten minute time out, do you know?

Just spotted that I wrote Command_Cilck, not Command_Click!

xenou · Jun 8, 2010

Hi,
This is interesting. I wasn't really aware of this behavior. You may also like to look at this test case I created.

It looks like the problem is that you are referencing the "blueprint", not any form which you have instantiated yourself. That is, by using the syntax UserFormName.PublicSubName you are calling a class method of the object, but not an actual object's method. Does that make sense?

Here's how to create a test workbook and userform to show the difference.
(I am providing a downloadable <a href="http://northernocean.net/etc/mrexcel/20100608_uftest.zip">Sample Workbook (zip format)<a/> for convenience so you need not type the code):
1) Create a form called TestForm in a New Workbook
2) Add a textbox to it
3) Add a public function called foo to return the value from the textbox.
4) Handle the closing so it doesn't unload when closed (it should only be hidden when closed by the user).
5) Instantiate the form, enter a value in the textbox, and close it
6) Use a message box to show the value from the form when calling the method of the instantiated form and the method of the form class.

The code in a standard module to show the form and return values in the two cases is below.
As you might guess now, the form you created and reference by its object variable "F" returns the value you typed into the textbox. The other form is created "on the fly" by Excel and has no value in its textbox.

Code:

[COLOR="Navy"]Sub[/COLOR] TestIt()
[COLOR="Navy"]Dim[/COLOR] F [COLOR="Navy"]As[/COLOR] TestForm
[COLOR="Navy"]Set[/COLOR] F = [COLOR="Navy"]New[/COLOR] TestForm
F.Show

    MsgBox "Form 'F' Textbox Value: | " & F.Foo & " |"
    MsgBox "New Form Textbox Value: | " & TestForm.Foo & " |"

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Default300 · Jun 9, 2010

Hi xenou. Thanks for your interest.

I haven't had time to try your test workbook and form. I will do later though, so thanks for taking the time to create the examples to show me.

Reading the code box though, I double-checked my own code. I think I'm doing the same thing as you, when I call the form intentionally (as opposed to unintentionally creating another Instance when I call the Sub). But would you mind checking if there are minor but important differences.

The code that Instantiates and Shows the UserForm intentionally resides in a Worksheet Module.

Specifically, it is in the Click Event Procedure of a Toggle Button (embedded on the Worksheet).

The Click Event is a Private Sub.

This is the code (first If statement to Load; second If statement to Unload):

Code:

[FONT=Courier New]Option Explicit

Private Sub tglLoadUnloadFormSortFilterNavigate_Click()

    Dim frmSFN As frmSortFilterNavigate46
    Dim frmUserForm As Object
    
    If tglLoadUnloadFormSortFilterNavigate.Value = True Then
            
        Set frmSFN = New frmSortFilterNavigate46
        
        With frmSFN
        
'(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
        
            .Show
    
        End With
    
        Exit Sub
        
    End If
    
    
    If tglLoadUnloadFormSortFilterNavigate.Value = False Then
        
        If UserForms.Count > 0 Then
        
            For Each frmUserForm In UserForms
        
                If frmUserForm.Name = "frmSortFilterNavigate46" Then
        
                    Unload frmUserForm
                    
                End If
                    
            Next frmUserForm
                    
        End If
    
        Exit Sub
    
    End If
    
End Sub[/FONT]

Incidentally, at the point that the code was written, there was only one UserForm (modeless). In the future there may be more than one open at a given time, so I may need to change the Unload code.

Would you mind enlightening me on a couple of other questions too...

Is "frmSFN" an Object Variable?
Does "frmSFN" die once the Sub is Exitted? (Is its scope limited to that Sub).

If I ask in the Immediate Window:

Code:

[FONT=Courier New]?Userforms.Count
'(It Returns...)
 1

?UserForms(0).Name
'(It Returns...)
frmSortFilterNavigate46
'(Not frmSFN)[/FONT]

Is there a way return the Index of a UserForm, given its Name, along the lines of:

Code:

[FONT=Courier New]'(Pseudocode...)
?UserForms(frmSortFilterNavigate46).Index
 1
'(Or)
?UserForms("frmSortFilterNavigate46").Index
 1[/FONT]

TIA

xenou · Jun 9, 2010

Some thoughts:

The object variable you are using (frmSFN) is lost when the sub ends. So you don't have any good "handle" on that form. The form itself lives on, since its modeless - Excel is keeping an internal handle of its own.

Regarding getting an index property: I don't think userforms have an index property. There's probably some API call to get the window handle for the form that the operating system uses - I don't have any examples, and haven't tried this myself. You can google around if you are interested (pearson has some information here). However ... see an alternative suggestion below.

Regarding your code in a toggle button: For myself, I don't like having code in buttons. I'd suggest you only put in the button a call to a sub in the worksheet module, or a standard module. It may not matter, but sometimes your code in other parts of the Excel environment can't see the button code very well (i.e., scope issues).

And regarding keep track of one or more forms: You could consider implementing private module-scope object variables or a public project-scope object variables, rather than declaring your object variable in a subroutine. That way, when you create the form, you'll have a reference to it that you can use later. You can also use the reference to call to it, test for its existence, and destroy it when finished. This introduces new things to think about (how will you keep track of the form via its variable now). But it may help you out later on. Another thought though, since you are tagging the forms with the sheet names, is to loop through your forms and test their tag properties. Perhaps you can set a rule of only one form created per sheet - so each tag will be unique (you'd have to check before creating a new one though). I'm not experienced with Modeless forms in Excel (I've used them in Access to some extent). Be aware that the complexity of your application is considerably elevated by using modeless forms, and yet again by using several of them at once.

If the above hasn't clearly answered your question, then I will try to do so briefly here:

Is "frmSFN" an Object Variable?
Does "frmSFN" die once the Sub is Exitted? (Is its scope limited to that Sub)

Yes - the object variable is lost when the Sub exits (since it is dimmed in that sub - it has "local" scope only).

If I ask in the Immediate Window:
Code:
?Userforms.Count
'(It Returns...)
1
?UserForms(0).Name
'(It Returns...)
frmSortFilterNavigate46 '(Not frmSFN)

This is interesting and I think by using the name property of an Object in this case, anyway, Excel is returning the class name of the userform. It would probably be the same name if you had two of these forms loaded.

Is there a way return the Index of a UserForm, given its Name, along the lines of:
Code:
'(Pseudocode...)
?UserForms(frmSortFilterNavigate46).Index
1
'(Or)
?UserForms("frmSortFilterNavigate46").Index
1

I don't find an index property but you can try other ideas such as: Getting the Hwnd handle, creating persistent object variables that you can use while the form exists, using .Tag values as a way to keep track of your forms (I discussed all these above)

Default300 · Jun 9, 2010

Thanks very much xenou.

Really interesting, informative and helpful.

Think I'll take your advice Re moving the Load Code to (probably) a Standard Module (rather than Event code of the control). More confident in my ability to do this now than a year or so ago when I was just starting out.

Re: Finding Index of UserForms. What you said concurs with what I've found so far. Seems funny though that VBA seems to Index them, but doesn't give access to the index. By which I mean that if you ask for UserForm(Index).Name it's no problem, eg UserForms(0).Name, but (unlike Worksheet Names) you can't ask the reverse. Shame. It could probably be done with a loop.

Code:

[FONT=Courier New]Dim strUserFormName As String
Dim bytUserFormIndex() As Byte
Dim blnUserFormIndexFound As Boolean
Dim bytUserFormNameFoundCount As Byte
Dim bytLoop1 As Byte

If UserForms.Count <> 0 Then

    ReDim bytUserFormIndex(0 To UserForms.Count - 1) As Byte
    
    For bytLoop1 = 0 To UserForms.Count - 1

        If UserForms(bytLoop1).Name = strUserFormName Then
        
            blnUserFormIndexFound = True

            bytUserFormNameFoundCount = bytUserFormNameFoundCount + 1
            
            bytUserFormIndex(bytUserFormNameFoundCount - 1) = bytLoop1
            
        End If
        
    Next bytLoop1
    
End If

If blnUserFormIndexFound = True Then

    If bytUserFormNameFoundCount = 1 Then
    
        UserForms(bytUserFormIndex(0)).Hide        '(For Example)
        
    Else
    
        'Deal With Multiple Forms With Same Name But Different Indices
        
    End If

End If[/FONT]

I changed bytUserFormIndex into an array to cater for multiple clones.

Re: Keeping track of Forms using Object Variables. I might well implement that option. Would there be much of an impact on memory etc? Does the Variable just point / refer to the Object, rather than holding all the values of its parameters, properties etc. (VBA Help says an Object Variable costs 4 Bytes - cheap at the price!!)

Thanks again for your help.

xenou · Jun 9, 2010

Re: Keeping track of Forms using Object Variables. I might well implement that option. Would there be much of an impact on memory etc? Does the Variable just point / refer to the Object, rather than holding all the values of its parameters, properties etc. (VBA Help says an Object Variable costs 4 Bytes - cheap at the price!!)

Your instincts are correct again

Just a pointer to the object is all the object variable is. But where it actually exists - got me! Somewhere "on the heap" is all I know. With the index question, I'm guessing that userforms don't actually have "names" - there's the rub (the name you see in your code is, I'm almost certain, the class name, and not a unique name for the instance of the class). And there could be more than one of them (if you whip two of your forms into existence they'd be identical copies in most respects - I don't know what "name" would distinguish them at that point). It's interesting to think about, anyway.

ξ

Default300 · Jun 10, 2010

I see what you're saying... so there may not be a way to distinguish the instances.

When 2 instances of the same UserForm are in existence the name is definitely the same. It is the Name of the UserForm Module / Template.

In the Immediate Window:

Code:

[FONT=Courier New]?Userforms.Count[/FONT]
[FONT=Courier New]'(Returns...)[/FONT]
[FONT=Courier New]2[/FONT]
 
[FONT=Courier New]?UserForms(0).Name[/FONT]
[FONT=Courier New]'(Returns...)[/FONT]
[FONT=Courier New]frmSortFilterNavigate46[/FONT]
 
[FONT=Courier New]?UserForms(1).Name[/FONT]
[FONT=Courier New]'(Returns...)[/FONT]
[FONT=Courier New]frmSortFilterNavigate46[/FONT]

Perhaps I could use the UserForms Collection Index to distinguish. I think it would be safe to assume that the form with the higher Index number was loaded later than one(s) with lower index numbers, wouldn't it? Might not bother though, as it's geting a bit messy and unpredictable!!

The other weird thing is that even though I apparently have 2 instances, only one is visible (ie, if I move the form there is nothing underneath). Perhaps they move together? I have not tried hiding one but not the other. If I close 1 then 2 close, and the QueryClose routine runs 2 times.

I tried to write code to prevent a second one loading if one already exists. Didn't work. Don't remember where I put the code though.

RoryA · Jun 10, 2010

Default300 said:
The other weird thing is that even though I apparently have 2 instances, only one is visible (ie, if I move the form there is nothing underneath). Perhaps they move together? I have not tried hiding one but not the other. If I close 1 then 2 close, and the QueryClose routine runs 2 times.

That should not happen. How are you creating the two instances?

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

Default300

Board Regular

xenou

MrExcel MVP

Default300

Board Regular

xenou

MrExcel MVP

Default300

Board Regular

xenou

MrExcel MVP

Default300

Board Regular

xenou

MrExcel MVP

Default300

Board Regular

RoryA

MrExcel MVP, Moderator

Similar threads

Share this page

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

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP, Moderator

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock