Solved: Code location issues

[jwise]

My latest project utilized several userforms, with a main menu and the several information-gathering panels. I developed the code on a modular basis, so there were 5 or 6 subroutines that were used by the functional routines.

Assuming one of these functional routines is used by event code that is invoked from multiple userforms, must I put multiple copies of the subs in each event object?

I had all these functional subs in the "module 1" object with only 1 copy and everything seemed to work OK. I made what I thought was an unrelated change, and suddenly I started getting "missing subroutine" messages. I moved the subroutine code from the "module 1" object to the "form object" and proceeded to get the same message from another of these subroutines. Is there anyway I can have only one copy of these routines regardless of where it is being called from?

Does all this make any sense?

[lucas]

I had all these functional subs in the "module 1" object with only 1 copy and everything seemed to work OK. I made what I thought was an unrelated change, and suddenly I started getting "missing subroutine" messages.
Was the seemingly unrelated change something like changeing the sub to private sub?

as long as the macro's are public subs you should be able to access them from any userform or other routine.

[Norie]

What change did you make?

You haven't moved the code into the userform modules?

[Bob Phillips]

I don't think I have ever seen a 'missing subroutine' message, I get Sub or Function not defined if it can't find a proc.

can you post the workbook, if only for my interest?

[jwise]

The change I made was to a routine that displayed a second level window which accepts the user's choice of a month to process. This was done in two separate functions, i.e. one worked as designed, the second did not. When I made the change to make sub2 like sub1, I started getting the message (forgive my short memory, but basically it is "I can't find your sub!"


I thought about this problem and decided that I have asked a very nebulous question. Some say I'm very good at that. To correct this problem, I am going to show a much simpler code sample. This application's mainline displays a userform which has two option buttons that are mutually exclusive, and two command buttons. The option buttons are "opt_even" and "opt_odd"; the command buttons are "process" and "quit". The userform's name will be "frmChoice".
Sub Mainline()
frmChoice.Show
End Sub
'--------------------
Sub Process_Click()

If frmChoice.opt_even.Value = True Then Call Even_Sub

If frmChoice.opt_odd.Value = True Then Call Odd_Sub

Unload Me

End Sub



In this scenario, I beleive there are objects "This Workbook", "Sheet1", "Module1", and "frmChoice" where programs may be stored.

Q1. If I put "Mainline" in "Sheet1" or "This Workbook", then the sub is available only to this workbook. Is this correct?
Q2. If I put "Mainline" in "Module1", then the sub will be available to other workbooks as well. Is this correct?
Q3. If I put "Mainline in "frmChoice", then VBA will be unable to find it in any workbook. Is this correct?
Q4. I must put "Process_Click" in "frmChoice" because it is "event code". Is this correct?
Q5. "Process_Click" will call "Sub_even" or "Sub_odd". Are these subs REQUIRED to be in "frmChoice"?
FYI... Q5 is what is getting me into trouble.

Q6. Assuming that other subs of this application also need "Sub_even", and this code is in any other object other than "frmChoice", do I need multiple copies of "Sub_even"? This too is causing me some grief.
Q7. Is there some utility which will search all of the available objects of a workbook, and list all subroutine names? This would be really helpful. If not, is there a way to do this?

Hopefully, these are more answerable questions!

[Norie]

I can't answer all those questions - I suggest you have a wee read of the help file regarding scope.:)

But for Q5, yes if you have a command button called Process the code for it's click event should go in the userform module.

And no the Odd and Even subs don't need to be in the userform module.

There'd be no harm if they were but then they wouldn't easily be accessed elswhere.

As to Q7, I'm sure I saw recently some code that would list all functions.

That could probably be adapted to list all subs.

[jwise]

The attached file contains the program as previosuly described with subroutines in all places (the various objects). To actually test this, you would need to rename the subs in the routines where you didn't want it. The displayed message TELLS where the sub came from. The ONLY place I've been able to get the subroutines to work is if they are in the userform object (named frmChoice).

Obviously the point is to isolate where the subs can be located. I can only get this to work when _even, _odd, and Sub_3 are all in "frmChoice".

[Norie]

Here's a hint, lose Private from the sub declarations in module 1.:)

[jwise]

I appreciate your advice.




In one of my tests, I discovered (in the real code, not the simplified version) that I had something like this:
....
Unload userform
...
Call Sub_1
...



Apparently, if Sub_1 lives in the same object that userform lives in, then VBA can't find Sub_1.

I have a predisposition to believe that you should "Unload" and not "Hide". I thought I understood all the ramifications of this, but apparently I did not.

Does this sound reasonable? Is it reasonable to NEVER "Unload" a userform? My other testing (in the real code and in the test code) seems to indicate (understand that the actual code is a "click event") that VBA can't find ANYTHING outside its userform object. Is this right?

[Norie]

Well I'm not really surprised by that.:)

I think what you need to understand is that any code associated with events in a userform/worksheet etc like event code should probably be in that object's module.

Any other code should be in standard modules.

This doesn't mean that you can't call/run code in standard modules from class/object modules.

[lucas]

Hi Jwise,
see post #2 of this thread.
as Norie has pointed out for you again, if you wish for your subs to be available from other modules they must be public subs not private. Remove the private in the module 1 subs and this will work for you.

If it says private Sub instead of just sub then it will not be available in the list of macros either....that is a clue to why they can't be found by subs in other modules.

[jwise]

Thanks Norie and Lucas.

I'm not really trying to be hard-headed, I'm just dumb. I discovered that the "Private Sub X" was not listed on the macro choices ("ALT F8" from worksheet), and I liked that because I tend to have a lot of subroutines which won't work "stand-alone". Thus if they are visible, then the user might select them, and that would be wrong. Does that make sense?

The point is that I get my benefit of the subs not being listed but at the cost of not being able to find them from any other object, i.e. if the running code is in "ThisWorkbook" and it calls "Sub5" which is in "Sheet1", VBA will not be able to locate Sub5. I certainly did not expect this behavior. Perhaps this should not be a consideration, but I also included a "cmdRestart" sub which will start my app. The app present a menu (userform with 6 choices of programs to run). Normally the app is started by a workbook open event, so the user does not know that he/she is actually choosing a macro to execute. I put the "cmdRestart" macro which was the only macro when doing "ALT F8" so that they could restart the application without having to get out and re-enter Excel. One of those 6 choices is Exit out of the app, allowing the user to look at the worksheets created by this app.

The news is appreciated- but I don't like the implications of this design. Maybe what I need is an Excel menu button that would start the app. The user would not have to do "ALT F8" and see all the clutter of subroutines that would only cause problems if selected.

Thanks again.

[lucas]

if the running code is in "ThisWorkbook" and it calls "Sub5" which is in "Sheet1", VBA will not be able to locate Sub5.
or as Norie pointed out you can put it in the object and it will be available.
for instance if you have a workbook open event in thisworkbook that calls a private sub called test...it will run it if the private sub test is in the thisworkbook module too. And it will not show in the macro list. see attached....same goes for a userform. Only public subs such as your userform. show macro should be in a standard module and not be private.

[Norie]

I think what you need to understand is that any code associated with events in a userform/worksheet etc like event code should probably be in that object's module.

Any other code should be in standard modules.

This doesn't mean that you can't call/run code in standard modules from class/object modules.
Jwise

Do you understand what I mean here?

[jwise]

I really appreciate your replies and patience.

My design goes three levels deep on userforms on two of the 6 options. Perhaps this is a poor design, but my opinion is that it mimicks what I see in "professional code", i.e. code you have to buy. Why show someone a list of months unless you need a month to handle their request? This is the logic that led to the three layers of userforms.

To specifically answer Norie's question, I think I understand what you are telling me. My original complaint was about subs which were used by multiple event code routines. I think you are saying that these common subs can be in "ThisWorkbook" IF they are Public; otherwise they must be in the event code objects, and since these subs are used by multiple event code routines, they must be duplicated. It was this "duplicated" that I really disliked because of the obvious maintenance issues. But, if I change the "Private Sub X()" to "Sub X()", then all can live in a common library. The downside to this is that if the user does "ALT F8" then these routines will be listed. I did not want them listed so that the user would not be confused. Is this correct?

[lucas]

hmm, you're even confusing me now.

I think you are saying that these common subs can be in "ThisWorkbook" IF they are Public
This is not exactly correct....public subs should be kept in standard modules....

[Bob Phillips]

To specifically answer Norie's question, I think I understand what you are telling me. My original complaint was about subs which were used by multiple event code routines. I think you are saying that these common subs can be in "ThisWorkbook" IF they are Public; otherwise they must be in the event code objects, and since these subs are used by multiple event code routines, they must be duplicated.

I doubt it. The code should either be in the code module that the eent pertains to, or a standard code module. If you put it in ThisWorkbook, even making it public means that you need to qualify it with the module name, it works, but hardly good practice.


It was this "duplicated" that I really disliked because of the obvious maintenance issues. But, if I change the "Private Sub X()" to "Sub X()", then all can live in a common library. The downside to this is that if the user does "ALT F8" then these routines will be listed. I did not want them listed so that the user would not be confused. Is this correct?

So precede the module with Option Private Module, then all of the Public subs are private to the project, that is they can be seen by all other modules in that project, but not elsewhere (including Alt-F8 in Excel).

[jwise]

I appreciate your patience.

The objects as listed in VBE are "ThisWorkbook", "Sheet3", several event (userform) objects (call them EV1, EV2, ... EV6), and "Module1". ALthough there are several additional worksheets (up to about 17 at year end), only "Sheet3" contains any code. Now there is no code in "Module1" (it was all moved).

I assume that the phrase "standard code module" refers to "Sheet3". Is this correct? I have no idea what " ... even making it public means you have to qualify it with the module name" means? Perhaps you could provide some kind of code snippet?

FYI: The "workbook open event" code makes "Sheet3" the ActiveWorkbook. The "cmdRestart" macro also does this as I plowed up several problems in my testing by closing Excel down with one of these other worksheets active. When I restarted, all kinds of things went wrong.






Here is a little snippet of what I think the code in "EV1" could look like:
Sub cmd1_Click()
Call EV1_handler()
End Sub





There would be similar code for all possible events across the appropriate userform objects. Here is a snippet of what "Sheet3" code would look like:
Sub cmdRestart()
' Set up to restart here. Normal start in Workbook open
End Sub

Option Private Module

Private Sub EV1_handler()
' ...
End Sub

Private Sub EV2_handler()
' ...
End Sub

...

Private Sub Rtn1()
' Called by multiple events
...
End Sub

Private Sub Rtn3()
' Called only by EV4
...
End Sub



All code would be in either an event object (userform) or in "Sheet3". Only "cmdRestart" would be listed if the user did ALT F8. There would be only 1 copy of any sub since all routines were actually in Sheet3 with the exception of the event shell code.

Is this what you are telling me to do?

[jwise]

After the "Option Private Module", all the subroutines should have read "Sub ..." and not "Private Sub ...".

[lucas]

I assume that the phrase "standard code module" refers to "Sheet3". Is this correct?
no....standard module in your post is module1

[Bob Phillips]

After the "Option Private Module", all the subroutines should have read "Sub ..." and not "Private Sub ...".

They should not be Private, but I would go further and dedclare them as Public.

[lucas]

jwise...take a look at this (http://slucas.virtualave.net/Wink/CustomMenuItem.htm). It is for word but the vbe works the same.

[Bob Phillips]

The objects as listed in VBE are "ThisWorkbook", "Sheet3", several event (userform) objects (call them EV1, EV2, ... EV6), and "Module1". ALthough there are several additional worksheets (up to about 17 at year end), only "Sheet3" contains any code. Now there is no code in "Module1" (it was all moved).

I assume that the phrase "standard code module" refers to "Sheet3". Is this correct?

Absolutely not. Sheet3 is a worksheet code module. The only standard code module here is Module1.


I have no idea what " ... even making it public means you have to qualify it with the module name" means? Perhaps you could provide some kind of code snippet?



Call ThisWorbook.SomeProcedureInThisWorkbook





Here is a little snippet of what I think the code in "EV1" could look like:
Sub cmd1_Click()
Call EV1_handler()
End Sub



There would be similar code for all possible events across the appropriate userform objects. Here is a snippet of what "Sheet3" code would look like:
Sub cmdRestart()
' Set up to restart here. Normal start in Workbook open
End Sub

Option Private Module

Private Sub EV1_handler()
' ...
End Sub

Private Sub EV2_handler()
' ...
End Sub

...

Private Sub Rtn1()
' Called by multiple events
...
End Sub

Private Sub Rtn3()
' Called only by EV4
...
End Sub



All code would be in either an event object (userform) or in "Sheet3". Only "cmdRestart" would be listed if the user did ALT F8. There would be only 1 copy of any sub since all routines were actually in Sheet3 with the exception of the event shell code.

Is this what you are telling me to do?

[INDENT]

I am afraid that I do not follow that code at all, but one thing to note is that the declaration of Option Private Modulke is only required in standard code modules, and should precede any procedure declarartions (and any mnodule scope variables).

[Bob Phillips]

They should not be Private, but I would go further and declare them as Public.

[jwise]

Lucas- Thanks! This is really slick. My user won't have to look for the restart, nor sort it out of other options. Very nice! I really liked the demo concept, too. I'd like to be able to create docs for my stuff that way.

XLD- Thanks for the clarification on "standard module" and the "referenced call". I've never seen that construct before. I'll have to do some experiments with this item. I've been workig under the assumption that the "normal" place to put code was in the relevant worksheet. Now it seems that "Module1" is the place. Maybe this little tidbit will eliminate some problems since regardless of the selected worksheet, the code will be available.

[Bob Phillips]

It will, but you will have to be careful that you are referencing the correct sheet.

[Bob Phillips]

Lucas- Thanks! This is really slick. My user won't have to look for the restart, nor sort it out of other options. Very nice! I really liked the demo concept, too. I'd like to be able to create docs for my stuff that way.

You can. It is a free product called Wink.

[lucas]

You can. It is a free product called Wink.
Thanks for sharing that Bob....I'm finding it useful

[jwise]

Thanks for the reference to Wink. I'll definitely give it a look.

As to the "worksheet reference" problem you mentioned, I already bought that problem when I inadvertently quit my spreadsheet while in one of those "data only worksheets". When I restarted, my code went beserk. When I realized the amount of code involved, then I started thinking about "ThisWorksheet" versus "ActiveWorksheet", and it all made sense. So I added a few statements to make the correct worksheet active. Now that I have moved the code from the worksheet to "Module1", I think I have reduced the potential for error again. I am also more careful as to how I make references to worksheets. When I first began this project, I used "Cells()" in several places, and this led to problems. When I understood what "ws.Cells" meant, then I didn't have to change the "Active" worksheet anymore, and whatever sheet the user wanted to view stopped being a problem.

Thanks again to all responders.

[Norie]

Why are you using Option Private Module?

[jwise]

Norie,

Thanks again for your interest.

The Option Private Module was recommended to me so that the macro names would not appear in the list you see after doing ALT F8. My code has several subroutines which are not usable by themselves. I thought it would be confusing to the user to see a list of several macros, and then be penalized for choosing the wrong one due to typo or misunderstanding. When I learned that "Private Sub" was hiding my subroutines from use by my routines that lived in other places (typically this was a "Sheet1 versus Module1 or ThisWorkbook problem), I complained about these subs being listed when the user did ALT F8. So it was suggested that I use "Option Private Module" and use "Sub x()" instead "Private Sub x()". I also mistakenly thought that the "Sheet" objects were the standard place to put modules... I don't know where I got that idea.

[Bob Phillips]

You have been advised correctly and I do not see why Norie has questioned it.