Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
I have a user form with alot of code in it. As of now all the code is in the
Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
Hi Brian,
1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
You have a lot of flexibility in the ways you can distribute code
within modules. I generally organize code as follows. First, the only code that resides in a user form's code module is that code required directly by the form -- that is, event procedures for each control. Similarly, the only code that resides in ThisWorkbook or one of the Sheet module is the event handling for the object, and those procedures should generally just call other procedures located elsewhere to do the real work. Keep the core logic out of ThisWorkbook the sheet modules and user forms. Everything else goes in other modules. Code is distributed amongst several modules based on the code's function. General utility functions in one module, data access functions in another, and core business functionality in yet another module or two. Procedures should be as generic as possible, allowing them to be called in many circumstances without changing any code. All relevant information should be passed in as parameters. Module- or Project-scoped variables should be avoided as much as possible. If a procedure relies on a value that is not passed in (and thus can be modified by other code), you run the risk of unintentional side effects that arise when you change a seemingly unrelated procedure that modifies a non-parameter or non-local variable. If your application works with specific a data type, the number of instances of which may vary at run time, consider using a Class module to encapsulate then entire function of the entity into a single module. Use properties of the class to set the values that define the class, and use methods of the class to carry out actions. You can think of your code base as a set of Lego blocks, each procedure being one block. Then, you create the application as a whole as a matter of just building up one block upon another. If you do this consistently and do it well, you'll find that you can easily re-use code within your project and also in other projects. My standard library consists of about 200 individual modules, each self-contained and devoted to a specific task or set of tasks. and each is fully tested. For example, if I need to access the system registry, I just import my library's modRegistry module and then I'm done with it. No more code to write. If I need XML functions, I just import my modXML module and call upon its prewritten and tested functions. Generally, a procedure in one module can call a procedure in any other module. Unless the method is scoped as Private, it doesn't matter (as far as the compiler goes -- organization is another question) where the code resides. See http://www.cpearson.com/Excel/scope.aspx for an explanation of scope and the visibility and access of code and variables within a project. There is a limit to the number of lines in a procedure and to size of a module. However, if you find yourself running up against those limitations, then the code really needs to be restructured. As a general rule, your code should tend to have a large number of small procedures rather than a relatively small number of large procedures. If you find that your procedures are over, say, 200 lines of code, or you find that your code does the same operations in several locations, then you should seriously consider rewriting the code. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 2 Feb 2010 10:14:03 -0800, Brian wrote: I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
I was hoping to make all the modules Private Sub. I do not want them being
changed after I am done. All the subs in the User Form are are Private. Can I just cut and paste them into a module and then in User Code Window call that Sub. Private Sub Update_Installer_Forms_10_Click() Call Update_Installer_Forms_10_Click() 'Macro1 located in Module1 End Sub "JLGWhiz" wrote: Hi Brian, 1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
You have probably read Chip's narrative by now. He paints a broader picture
than what I got into. However, to clarify the difference between public and private procedures, Public simply means that it is accessible by other code and can be incorporated into different procedures by call up during runtime. Private does not mean that other people cannot access the code. It means that the code is restricted to operate within the particular object that holds the code, such as UserForm, ThisWorkbook or sheet modules and generally speaking cannot be called by a procedure outside of those objects. Notice that I say, generally speaking. There are exceptions to the rule but I will not go into that here. For planning purposes in designing a project, stick with the basic conditions of Public and Private and you will be better off. You really need to get a book that explains the basics of the VBA conventions, although some of these things are explained in one of the manuals included in VBA help file. You just have to look through it for a while to find the parts that give the broader explanations. You can test a lot of the things you want to do and if they won't work, the worst that can happen is you get an error message saying you can't do that. But here is something that might make sense to you as an engineer. You can do modular programming without using a bunch of different code windows. Here is a form of modular programming that can all be in one code window. Sub Main() Macro1 MsgBox "Macro1 Completed" Macro2 MsgBos "Macro2 Completed" Macro3 MsgBox "Program Completed" End Sub Sub Macro1() 'Declarations 'Variable assignment 'Code body End Sub Sub Macro2() 'Same pincipal as Macro1 End Sub Sub Macro3() 'Same principal as Macro1 End Sub The Main procedure simply calls the other 3 procedures which can perform completely different actions, maybe one copies data, another sets formats and the other does clean-up work or opens a UserForm for user interface. The point is that by breaking the code into small procedures it is being modularized and any one of thoses separate procedures can be called in any order by the main procedure. It is like taking tools out of a tool kit as you need them. "Brian" wrote in message ... I was hoping to make all the modules Private Sub. I do not want them being changed after I am done. All the subs in the User Form are are Private. Can I just cut and paste them into a module and then in User Code Window call that Sub. Private Sub Update_Installer_Forms_10_Click() Call Update_Installer_Forms_10_Click() 'Macro1 located in Module1 End Sub "JLGWhiz" wrote: Hi Brian, 1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
P.S. If you need to call an event procedure from outside its host object
(i.e. UserForm or Sheet) then you probably did not need an event procedure to begin with. It should have been written as a public Sub procedure and then called by the event code if needed for the event, instead of the reverse of trying to call an event procedure from a public code window. "Brian" wrote in message ... I was hoping to make all the modules Private Sub. I do not want them being changed after I am done. All the subs in the User Form are are Private. Can I just cut and paste them into a module and then in User Code Window call that Sub. Private Sub Update_Installer_Forms_10_Click() Call Update_Installer_Forms_10_Click() 'Macro1 located in Module1 End Sub "JLGWhiz" wrote: Hi Brian, 1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
Lets say I take a really big Sub Procedure that is in the User Form Code
Window and break it down into 6 smaller subs and put the 6 subs into a module. Now I have 6 Small Subs sitting there in a module. In place of the Big Sub Procedure can I put a call statement there to call the Smaller Subs? Can I just call the entire module to run or do I need to call each Smaller Sub to run 1 at a time. I assume they will run in the order they are in. Is this the same as example you gave me? Module1 Sub1 Sub2 Sub3 Located in the Main User Form Code Window ----------------------------------------------------- Sub Main() Macro1 MsgBox "Macro1 Completed" Macro2 MsgBos "Macro2 Completed" Macro3 MsgBox "Program Completed" End Sub Located in the Module ------------------------------------------- Sub Macro1() 'Declarations 'Variable assignment 'Code body End Sub Sub Macro2() 'Same pincipal as Macro1 End Sub Sub Macro3() 'Same principal as Macro1 End Sub Did I get it correct or am I still missing something? "JLGWhiz" wrote: P.S. If you need to call an event procedure from outside its host object (i.e. UserForm or Sheet) then you probably did not need an event procedure to begin with. It should have been written as a public Sub procedure and then called by the event code if needed for the event, instead of the reverse of trying to call an event procedure from a public code window. "Brian" wrote in message ... I was hoping to make all the modules Private Sub. I do not want them being changed after I am done. All the subs in the User Form are are Private. Can I just cut and paste them into a module and then in User Code Window call that Sub. Private Sub Update_Installer_Forms_10_Click() Call Update_Installer_Forms_10_Click() 'Macro1 located in Module1 End Sub "JLGWhiz" wrote: Hi Brian, 1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
The core answer to all of your questions is really that it depends on what
you put into the code as to what it will do. The programmer is in control and the processor is only as smart as the programmer. If you have six sub procedures in module1, you can use other code to call those six procedures in any order you wish to call them in. However, if you have written those procedures in a fashion that make one reliant upon another, then you would need to run those in a specific order to get the desired results. Otherwise, they can run independently and in any order. Terminology is important. When I refer to calling a procedure, I mean that a line of code initiates a procedure. That line of code is simply the procedure name. If a procedure is named myMacro and it has no variable arguments in the title line of the procedure, then all that is needed to call it is the name: myMacro That will initiate that procedure. If the procedure has arguments then it would be called: Call myMacro("arg1") The conventions are in the VBA help file for calling procedures. The same six procedures can be manually initiated individually by assigning keyboard shortcuts to them, or assigning them to individual command buttons. Like Chip said, a lot of flexibility when they are in the public module. What you cannot do is run an event procedure from the public module while it has the event code title line. That means that: Private Sub CommandButton1_Click() 'some code here End Sub would not run. You will hear a beep. At least my computer beeps if I try it. Any event code must be executed from an appropriate code window. Worksheet_Change from a worksheet code module, Workbooks_Open from the ThisWorkbook code module, UserForm_Initialize from a UserForm code module. The internal operation of the VBA compiler is geared to ignore these title lines if they are not in the appropriate code module, but it will beep to let you know that something is not right. I don't know why they did not make an error message for that. "Brian" wrote in message ... Lets say I take a really big Sub Procedure that is in the User Form Code Window and break it down into 6 smaller subs and put the 6 subs into a module. Now I have 6 Small Subs sitting there in a module. In place of the Big Sub Procedure can I put a call statement there to call the Smaller Subs? Can I just call the entire module to run or do I need to call each Smaller Sub to run 1 at a time. I assume they will run in the order they are in. Is this the same as example you gave me? Module1 Sub1 Sub2 Sub3 Located in the Main User Form Code Window ----------------------------------------------------- Sub Main() Macro1 MsgBox "Macro1 Completed" Macro2 MsgBos "Macro2 Completed" Macro3 MsgBox "Program Completed" End Sub Located in the Module ------------------------------------------- Sub Macro1() 'Declarations 'Variable assignment 'Code body End Sub Sub Macro2() 'Same pincipal as Macro1 End Sub Sub Macro3() 'Same principal as Macro1 End Sub Did I get it correct or am I still missing something? "JLGWhiz" wrote: P.S. If you need to call an event procedure from outside its host object (i.e. UserForm or Sheet) then you probably did not need an event procedure to begin with. It should have been written as a public Sub procedure and then called by the event code if needed for the event, instead of the reverse of trying to call an event procedure from a public code window. "Brian" wrote in message ... I was hoping to make all the modules Private Sub. I do not want them being changed after I am done. All the subs in the User Form are are Private. Can I just cut and paste them into a module and then in User Code Window call that Sub. Private Sub Update_Installer_Forms_10_Click() Call Update_Installer_Forms_10_Click() 'Macro1 located in Module1 End Sub "JLGWhiz" wrote: Hi Brian, 1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Breaking User Form Code into modules question
copy all of the code below to a public code module, thin run Sub Main()
Sub Main() proc1 proc2 proc3 proc1 MsgBox "Notice that proc1 ran twice" End Sub Sub proc1() x = InputBox("Enter a name", "NAME") MsgBox x & " was entered for a name." End Sub Sub proc2() MsgBox "This is second procedure" End Sub Sub proc3() MsgBox "This is third procedure" End Sub "Brian" wrote in message ... Lets say I take a really big Sub Procedure that is in the User Form Code Window and break it down into 6 smaller subs and put the 6 subs into a module. Now I have 6 Small Subs sitting there in a module. In place of the Big Sub Procedure can I put a call statement there to call the Smaller Subs? Can I just call the entire module to run or do I need to call each Smaller Sub to run 1 at a time. I assume they will run in the order they are in. Is this the same as example you gave me? Module1 Sub1 Sub2 Sub3 Located in the Main User Form Code Window ----------------------------------------------------- Sub Main() Macro1 MsgBox "Macro1 Completed" Macro2 MsgBos "Macro2 Completed" Macro3 MsgBox "Program Completed" End Sub Located in the Module ------------------------------------------- Sub Macro1() 'Declarations 'Variable assignment 'Code body End Sub Sub Macro2() 'Same pincipal as Macro1 End Sub Sub Macro3() 'Same principal as Macro1 End Sub Did I get it correct or am I still missing something? "JLGWhiz" wrote: P.S. If you need to call an event procedure from outside its host object (i.e. UserForm or Sheet) then you probably did not need an event procedure to begin with. It should have been written as a public Sub procedure and then called by the event code if needed for the event, instead of the reverse of trying to call an event procedure from a public code window. "Brian" wrote in message ... I was hoping to make all the modules Private Sub. I do not want them being changed after I am done. All the subs in the User Form are are Private. Can I just cut and paste them into a module and then in User Code Window call that Sub. Private Sub Update_Installer_Forms_10_Click() Call Update_Installer_Forms_10_Click() 'Macro1 located in Module1 End Sub "JLGWhiz" wrote: Hi Brian, 1: My question is in the UserForm Code Window how do I refer to a module? A1: You can call any procedure that is in the public modules (Module1, Mocule2, etc.) from the UserForm code module. If you are using event code to respond to some user's interaction, then in the event code you would call the working procedure out of the public module: Private Sub Listbox1_Click() Call Macro1 'Macro1 located in Module1 End Sub 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc... A2: Yes, any public module can call from and respond to any other public module. .. 3: Most of my Code is With statements / End With then more code. A3: So? That is just a style of writing code. I do not believe it would influence whether the code can be called from another module or not. As a general rule, private procedures can call any public procedure, but the reverse is not necessarily true. "Brian" wrote in message ... I have a user form with alot of code in it. As of now all the code is in the Userform Code window, but there is so much code that it's getting confusing. I have one Sub that is to large and needs to be split into several subs. When I was done I was going to break all the Subs down into different modules to clean it up. I guess I will have to do that now, but I am not sure how to do it. 1: My question is in the UserForm Code Window how do I refer to a module? 2: If the Code is to large can one Module call another module? Something like User form Code calls Module 1 and at the end of module 1 code, it calls for module 2, etc.... 3: Most of my Code is With statements / End With then more code. Here is an Example of the Code in the UserForm Code Window: Private Sub Update_Installer_Forms_10_Click() With Workbooks("Master Installer Forms.xlsm").Sheets("Install Pack Con") **Code for the sub is here** End With With Workbooks("Master Installer Forms.xlsm").Sheets("Install Chk List") **Code for the sub is here** End With End Sub I really need to sort this out so it's not so confusing to work on. Can anyone please help me or explain it in english to me. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
List Modules in a user form | Excel Programming | |||
VBA & User Form modules vanish | Excel Programming | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
When to code in sheet or userform modules and when to use modules | Excel Programming | |||
Scope of variable includes all Form _and_ Code modules?? | Excel Programming |