Where put project code

[whburling]

CONTEXT
The project i am working on has three fundamental tasks
(1) I must manually enter data, that describes a house, into worksheets
(2) I then run code that transcribes worksheet data into objects and collections that define a house
(3) i then run code that applies National Electric Code (NEC) to the coded house to define an electrical design for that house

MY QUESTION
Sooo, being a novice, i am uncertain where to put my code. I sense that i do not want large hunks of code but many small
modules of code that can be tested out individually. But where do i put all this code. I can imagine it is a lot of code. maybe too much
to sift through within a single project manager?

SOME OF MY THINKING ABOUT POSSIBLE SOLUTIONS
Some thoughts that I explored (with ignorance) was to put the code that transcribes worksheet data into objects and collections in the
workbook that has the worksheets. Then put the code that knows the NEC rules into a second workbook. Similar to the first workbook,
the rules are transcribed into coded objects and collections.

Finally, a workbook is created and holds the code for the design results. The code in this workbook applies the objects and collections
of the above two workbooks and displays the results in this last third workbook.

QUESTIONS THAT SURFACE WITH MY POSSIBLE SOLUTION
If the above is a reasonable strategy...and i welcome hearing alternatives, then how does the code of one workbook talk to the code of
the other workbooks? Maybe if they are all concurrently open the code is available to be run by the interpreter as long as all code is uniquely
defined and hence present no conflict to the interpreter?

How do you guys handle code location and the immense number of small modules of code for your large projects?

Thank you....
bil

[romperstomper]

I cannot see any reason to separate the code into three workbooks.

[mogul]

I've worked on a few large engineering projects related to estimation power, performance and area of semiconductor devices.  Here is what I've learned:

1. Do not mix code and data in the same workbook.  The requirements are always changing requiring code changes.  The data may also change.  It is a nightmare to update the code in a workbook that contains code.  I create an addin (.xlam) that contains all code and a workbook that contains user data.  I can easily update the addin without impacting the worksheet data. 

2. Plan for user workbook data to change.  I was constantly adding new columns to add info to worksheets.  Rather than refer to specific columns I always used enums or dictionaries to get the column number.
3.  Format all user data as a table.  Use database compatible names.  It is easier to extract data using SQL.

4.  Use a database to store all engineering (NEC) data and other related info.  I used an Access database to store all engineering data.  You'll need to learn about databases and SQL but it makes your app more supportable.

5.  Use an installer to distribute app.  I use Inno Setup (free).  It makes it easier to update.

[whburling]

Thank you, Mogul!!!! Your advice really spoke to me.

[whburling]

Thank you, rory, for your help.