?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 again Rory. Thanks for your help in the past.

Generally, I don't have this (2 instances) problem.

It occurs when I'm experimenting, such as as described in the original post:

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.

Incidentally, it was you who first advised me re how to load a UserForm: [ http://www.mrexcel.com/forum/showpost.php?p=2011787&postcount=11 ]

Overall it's been working fine up to now, but as I mentioned in Post #7 above, I'll move the code out of the ToggleButton_Click Event and into a Standard Module Sub.

It's the same project btw!! Just as well I'm not a professional!!

Cheers.

 

[RoryA]

You should not use:

 UserForm1.PublicSubName

you should use your variable:

 frmSFN.PublicSubName

to avoid using default instancing.

 

[Default300]

Ok Rory. Thanks.

Not sure if you saw the discussion that xenoua and I had above though, about the fact that (currently) the Object Variable is Dimmed within a Private Sub, and so its scope means that it does not exist after that intitial code runs.

Quote:
<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Is "frmSFN" an Object Variable?
Does "frmSFN" die once the Sub is Exitted? (Is its scope limited to that Sub)



</TD></TR></TBODY></TABLE>
Yes - the object variable is lost when the Sub exits (since it is dimmed in that sub - it has "local" scope only).

Bottom line though is that I should rewrite a couple of things, which probably will remove the problem altogether.

Still, if you feel like discusssing your suggestion further, are you suggesting to use a Public / Project Level Object Variable for loading the UserForm, rather than the Local / Procedure Level Variable?

Are you also saying that, if required, I could run Subs that are located within the UserForm Module, provided I use a Public / Project Level Object Variable to Load / Instance it initially?

I take it I have written the start of the code correctly, have I?

    Dim frmSFN As frmSortFilterNavigate46
 
'...
 
    Set frmSFN = New frmSortFilterNavigate46

Unlike "frmUserForm" I do not specifically Dim "frmSFN" as an Object.
But "frmSortFilterNavigate46" (a UserForm module / design / template) is an object, isn't it.
So I presume that when I Set "frmSFN" = New "frmSortFilterNavigate46", it "frmSFN" is / becomes an Object Variable.

I think xenou already clarified this anyway.

 

[RoryA]

Still, if you feel like discusssing your suggestion further, are you suggesting to use a Public / Project Level Object Variable for loading the UserForm, rather than the Local / Procedure Level Variable?

Yes, if it's a modeless form.

Are you also saying that, if required, I could run Subs that are located within the UserForm Module, provided I use a Public / Project Level Object Variable to Load / Instance it initially?

Yes. Whether it's the best setup is another question...

I take it I have written the start of the code correctly, have I?

    Dim frmSFN As frmSortFilterNavigate46
 
'...
 
    Set frmSFN = New frmSortFilterNavigate46

Unlike "frmUserForm" I do not specifically Dim "frmSFN" as an Object.
But "frmSortFilterNavigate46" (a UserForm module / design / template) is an object, isn't it.
So I presume that when I Set "frmSFN" = New "frmSortFilterNavigate46", it "frmSFN" is / becomes an Object Variable.

It is a variable of type frmSortFilterNavigate46, which is also of type Object. (technically you are declaring that it implements the frmSortFilterNavigate46 interface)

 

[Default300]

Thanks for all those clarifications.

It is a modeless form. I think I'll change to using a Public Project Level Object Variable to Instantiate it. Not sure if I need to access it during the rest of its lifetime, but nice to have the option!

Re: Running Subs located in UserForm Module. I'm just curious about feasability. I don't plan on doing so. In fact I plan the opposite - moving existing Subs out of it, though it will require more Variable or Parameter passing, or more Public Variables.

Can I ask a question Re: the Use of Public Variables:
Why are many experienced programmers averse to use of Public Variables. Is it purely to do with avoiding unnecesary use of memory? Or is it more to do with housekeeping, keeping things tidy and avoiding loose ends.

In my project, I have approx 15 Public Constants (Integers); 30 Public Variables (of which 10 are Booleans; 10 Single Cell Range; and 10 assorted String, Long, Byte and Date). I also have 1 Public Array 2D (7 x 12) (mainly Integers and short Strings). Altogether it's not even 1KB.

I know this has probably been covered many times, so I'd be happy with a pointer to a good article if you know of one.

Another topic which is starting to confuse me but which has doubtless been covered over and over for newbies is Re Terminology for Variable Scope. I've got a handle on most of it from VBA help, and some help you gave me about six months ago. But one that is confusing me currently is about Global vs Project Level vs Module Level vs Public. Are they equivalent, or can Globals be see from different Projects (implying a Reference has been established). Again a pointer to a clear structured article would be great. Failing that, I could check the forums, but it seems that each programmer has his or her own idiosyncrasies of verbal language as well as programming style, especially if they have used several programming languages.

Thanks.

 

[Default300]

Sorry for double post. I need to ask one more thing and don't want to get caught by that 10 minute editing time restriction again!

To change to using a Public Project Level Object Variable to Instantiate my UserForm, is this how?

At the top of any Standard Module:

[FONT=Courier New]Public frmUserFormSFNC As frmSortFilterNavigate46[/FONT]

In (the same or another) Standard Module, in the Sub where I will put the code to launch / Instantiate the UserForm...

[FONT=Courier New]Set frmUserFormSFNC = New frmSortFilterNavigate46

frmUserFormSFNC.Show[/FONT]

By the way, I changed the variable name slightly (from frmSFN to frmUserFormSFNC)

I just noticed that, as in the code above, I have not been using the Load method for the last several hundred versions. I presume that's correct is it? Seemed to work fine!!

 

[xenou]

Hi, well you are getting your money's worth out of this post

Some thoughts on your questions from me:
If a form doesn't exist it will be loaded when you use the .Show method. That's usually how I go with this too (but if you wanted to load a form but not show it, then you'd be able to load it and show it later).

On running subs in userforms. I would try to avoid this - it sounds messy. I'd try to keep the code in the userform that is run only via interaction with the form and its controls, not from elsewhere -- at least as a general principle - exceptions are up to the programmer to define and implement. In my earlier example I provide a function in the form which made a little more sense to me, since the form may have some bit of information that is needed. So with a function you "ask" the form for it, as it were.

On Publics/Globals, I think one danger is that you can forget what is being done with such variables - perhaps Sub B is changing a public variable and you think its only being changed by Sub A. This could lead to some erroneous assumptions and hard-to-find bugs. Especially if you're coming back to it two years later and really forgot how it all works. Then you'd have to look at the entire project to figure out all the possible ways a variable could be changed! See what I mean? By contrast, if variables aren't public, they can only be changed in the module where they are declared. That makes the module and its procedures the "gatekeeper" and can help you to establish rules and processes for using those variables. And if you or someone else came back to this application two years from now, it would be easier to understand how it works.

On Terminology: no help there. It is confusing. I don't even use terms at all - I use descriptions such as Public Project-Scope variables and Private Module-Scope variables. It might be simpler to say Publics and Privates ... but I don't want to be misunderstood (and if someone doesn't know what I mean then they may finally ask the right questions such as "what is scope" or "how is project scope different from module scope?"). At the end of the day, this is something that a programmer must thoroughly understand himself or herself. In programming generally, the term Global is often used where in VBA the keyword Public is used.

Hope this helps a little. I'm trying to avoid asking dumb questions like "why are you using multiple instances of a form, or "why are you running subs in forms ...". But it may be time for some nitty gritty as this project continues...

Cheers,
ξ

 

[Default300]

xenou, as always, thanks a million.

(And apologies for small typo in your name previously: an extra letter left over from a cut and paste edit).

Answers to the unasked "dumb questions" (at end of post):

(1) I really don't want to have any more than one instance of the same form running at any one time.

Most of my questions have concerned trying to detect accidental multiple instancing in order to avoid errors.

Now, following the advice that I've received, I hope to avoid creating them in the first place!


(2) The Subs in the UserForm mostly directly concerned and affected the Controls in the Form (changing their format, values, or combobox content etc).

Also, up to now, there was no need for any of them to run if the UserForm was not loaded. Recently, I needed a Sub to be available / "runnable" whether or not the Form was loaded. Hence the original post / question.

And hence the solution I think I've found through your and Rory's help, which is to move the Sub to a Standard Module, and ensure that it is Public, and to pass anything required to it as Public Variables, or as Parameters.


(?) I may go further, and move any Subs which are not Control Event Procedures out of the UserForm Module to a Standard Module, but I'm not sure if the pros are sufficient to outweigh the cons there.

Previously when I did that, I found I had to create many more Variables or Parameters to pass values to and fro.

I also found I had increased complexity with Specificity and so on. Within the UserForm Module, I could refer to Controls directly, whereas in the Standard Module, I had to specify the UserForm by ?Name, ?Index or (following our discussion), maybe it was the ?Class Module I was referring to.


RE: Getting my money's worth!! I think I remarked before (perhaps to RoryA) that I should have taken the TV detective Columbo as my alias. What I always remember about him was that after questioning the suspect he'd leave the room, but then, just as the suspect breathed a sigh of relief, he'd come back in and say: "Aahh... Just one more question madam..." Or "There's just this one thing I don't understand..." That's me to a tee!! Just ask Rory, or see the post I linked to earlier. In fact, I just uploaded a picture of Peter Falk's character as my avatar.

 

[xenou]

RE: Getting my money's worth!! I think I remarked before (perhaps to RoryA) that I should have taken the TV detective Columbo as my alias. What I always remember about him was that after questioning the suspect he'd leave the room, but then, just as the suspect breathed a sigh of relief, he'd come back in and say: "Aahh... Just one more question madam..." Or "There's just this one thing I don't understand..." That's me to a tee!! Just ask Rory, or see the post I linked to earlier. In fact, I just uploaded a picture of Peter Falk's character as my avatar.

LoL. I noticed Columbo there. Good old show. I can sympathize with the convenience of using public variables when communicating with forms. I've gone that route myself. If you really want to avoid that, the following link is helpful (I think I've read through it four or five times now):
http://www.dailydoseofexcel.com/archives/2004/07/22/passing-arguments-to-a-userform/

Still, if the app is working, it may not be worth the time to re-design those pieces - don't mess with success

 

[RoryA]

Regarding scope:
Global and Public are the same. Global is old-fashioned (doesn't exist in .Net)
Dim and Private are the same.

Public variables are a pain because they're hard to debug (trying to work out what changed a public variable and when can be a nightmare) and they can get reset under certain situations (such as unhandled errors, or debugging your code). Generally best to give a variable the narrowest scope possible, but you do occasionally have to use Public variables. (Public constants are fine, though)

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)