I'm in the planning stages of rewriting an excel model/application due
to it's poorly design and messy/repeated code. But I haven't yet been
able to decide on the best way to design / structure this application.
I've therefore decided to try to describe the basic functionality to
you guys, and then with some input and discussion I'll perhaps be able
to get a clearer view of how to do this.

The input is personal data: social security number, name, dates and a
lot of numbers (insurance/pension data), between 50-100 columns of
data. The data is stored in excel on 5-6 worksheets, with each
worksheet representing a different "member"-type, and each worksheet
has different input, mainly the insurance/pension data differs. To
clarify this one sheet contains working people, another retired
people, another people getting disability pension and so on. One row
on one of the worksheets contains all the information about one
person. Each worksheet can have thousands of rows.

The output is one row for each person containing data based on
calculations on the input numbers. All output data is to be outputted
on one single worksheet. So the output variables are the same for all
"member types", but the input and the calculations differ.

In theory the task is quite simple. Read the data from each
inputsheet, do the calculations, and write the output back to the
output sheet. Of course this is a very simplified description, and
there is a lot of extra functionality with file input/output, data
validation and advanced actuarial calculations but this is not what
I'm having troubles with. I just want to find the best way, using the
limited functionality i have in Excel 2003 VBA, to structure this
task.

I've been using object oriented languages for some time now and since
vba supports classes my natural thought was to read each persons
information into a user defined object with the calculations also
encapsulated in the class and store each object in a collection, and
then in turn iterate through the collection and write the output.

I've since realised that storing all input and inbetween calculations
in memory is pointless, all i need stored is what i'm going to write
out, namely the output from the calculations. So I've thought about
some solution with a input object and a output object, but I haven't
found what I feel is a good solution. So I'm really struggeling with
how to modell this with user defined classes in vba. I'm trying to
avoid repeated code, and since VBA doesn't support inheritace/
polymorphism I don't see any clear solution. The probems i'm faced
with is that each "member-type", i.e. each line on the 5-6 different
worksheets, contains some data that is shared, i.e. names, dates,
social security numbers etc, and some data that is specific. The same
thing goes for the calculations/manipulations I want to do on each
"member-type", some are shared, some are not. I'm starting to wonder
if perhaps just a procedural approach is easier...

I don't know if I have explained this well enough, so feel free to ask
about anything that is unclear. Also, i wanted to point out that I'm
fairly familiar with VB and .Net, but haven't done that much excel/VBA
programming before, so perhaps there are better options that I haven't
even concidered...

Anyways... Thanks in advance to anyone willing to dedicate some time
to this problem.