Macro Modules

[Jeff]

Hi, I'm a new programmer, and I'm starting to accumulate alot of scattered
modules. Any suggestions about how to keep these things all in one place, in
an organized fashion?

 

[Gord Dibben]

I would move them all to one workbook, then get rid of hard-coded references to
sheet and workbook names in the macros by using ActiveSheet and ActiveWorkbook.

Organize into several modules by category then change the module names to
reflect the categories.

Save as an Excel Add-in which would be available for all open workbooks.


Gord Dibben MS Excel MVP

 

[Gary''s Student]

Separate functions and subs into modules by related functionality. Put the
"working parts" of worksheet code, workbook code and button code into modules
as well and have the event routines call the public routines to get stuff
done.

Finally give the modules logical names to help find stuff. Modules can be
renamed with something like:

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

 

[Gord Dibben]

Manually...........Right-click on the Module and View>Properties and change the
name.


Gord Dibben MS Excel MVP

 

[Jeff]

Thank you, I knew there was some piece I was missing - I think the add-in was
what I was looking for, along with public functions/subroutines, though I'll
have to do some reading up on how all that's done. Thanks again.