VB Modules

[dhstein]

I'm trying to understand the Naming/Use of Modules in VB. I have several
Excel workbooks with VB macros that I've either developed or copied from the
web. At the moment, I have only one workbook open and I see in the VB
Project window that I have 9 modules - some of which are completely empty.
Does Excel keep track of modules and name each one uniquely? What happens if
I copy the code from module 9 into module 1 and another workbook has module 1
defined? Any help would be appreciated. Thanks.

 

[Gary''s Student]

Fortunately you are in control. You can always rename the module:

Sub Whats_In_A_Name()
ThisWorkbook.VBProject.VBComponents("Module1").Name = "dhstein"
End Sub


Naturally you would rename the module to something related to its contents.

 

[dhstein]

Thanks for your response. I think I wasn't clear in my question. Let me try
again. I don't necessarily want to rename the module, I want to understand
why I have 9 modules and does Excel automatically use the next sequential
module number when I create a new macro? I also want to understand if there
is a problem or a conflict if I copy code from module 9 in this workbook into
module 1 in this workbook. This is more of a "How does this work" question
rather than a "What do I do " question. Thanks.

 

[Gary''s Student]

I don't understand why you have lots of modules.

I normally bring up VBE and use:

Insert > Module

to create a module. Excel names it Module1.

I then add subs and functions to Module1 as I see fit. There are no other
modules.

If I needed another module, I would go back to:

Insert > Module

and create another one. The only reasons I would create more than one
module are:

1. organization by function
2. limit scope
3. selectively export some stuff and not others

 

[dhstein]

I'm not creating these other modules intentionally - I'm sure it's because of
the way I'm doing things. But I'm not trying to fix it yet - I'm trying to
understand it. So my questions are the following:

1) Does Excel keep track of modules from workbook to workbook, so that
there are unique modules associated with specific workbooks?

2) I have code in Module 1 of Workbook A - and I have code in module 2 of
workbook B. Workbook A is open and workbook B is not open. My VB project
window shows a module 2 with no code. If I copy the code from module 1 into
module 2 - what happens when I open workbook B - does the code get
overwritten - does the code get merged ? I understand that I need to fix
this, but I'm trying to understand how Excel stores the VB code within
modules and workbooks - so that I can organize what I already have. Not
every VB function will be used in every workbook - so I may end up with
multiple modules. Thanks.

 

[ShaneDevenshire]

Hi,

1. Everytime you close your workbook and then reopen and record a new macro,
Excel begins a new Module. If you record your modules all during one session
(while the workbook stays open) Excel records all the code into the same
module. But again, not so if you close the file and reopen and begin a new
recording.

2. Excel uniquely names each module just like sheets in a workbook.

3. You can delete any module by right clicking it and choose Remove....

4. You can copy code from one module to another even in another workbook

5. You can, and should, rename your modules. You can do that if the
Properties window is displayed and you are on a module in the Projects
window. The first property is Name (no spaces allowed in names)

6. You can refer to a module in code if necessary, for example if you have
two workbooks open and you want to execute the code in a module with the same
name as one in the active workbook, but in the other workbook. But this is
getting a little far into the VBA area.

 

[dhstein]

Thanks Shane. So if I understand you, then "Module1" in Workbook A is
completely separate from "Module1" in Workbook B ?

 

[dhstein]

Shane - Thanks very much - that clears it up for me.

David

 

[ShaneDevenshire]

Hi,

Exactly!

cheers,

Shane Devenshire

Thanks Shane. So if I understand you, then "Module1" in Workbook A is
completely separate from "Module1" in Workbook B ?