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.

Hello Dear
I saw your email in your profile here( www.eggheadcafe.com ) and become interested; my name is;Kate please send me your email address ) to my box now so that i can send you my picture then we will know each other more;
Thanks Kate



Submitted via EggHeadCafe - Software Developer Portal of Choice
Mocking WCF Services Using Moq
http://www.eggheadcafe.com/tutorials...using-moq.aspx

On Oct 9, 5:30*am, junglebear wrote:
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.

I'm having a problem figuring out what you want but I'll bet it can
probably be simplified. If you could send this msg and a sample before/
after example along with your efforts to date, etc. , I'll take a
look. I don't speak vb or net.

On Oct 10, 12:37*am, Don Guillett Excel MVP
wrote:
On Oct 9, 5:30*am, junglebear wrote:





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.

I'm having a problem figuring out what you want but I'll bet it can
probably be simplified. If you could send this msg and a sample before/
after example along with your efforts to date, etc. , I'll take a
look. I don't speak vb or net.- Hide quoted text -

- Show quoted text -

What I want is to figure out how to accomplish the task described
above without repeated code using OOP / classes.

Regarding sample before/after I don't think that is an option. It's
sort of sensitive/secret code. The only option then would be to
construct an similar example... I'll see if I can make time to do
that...

One simple way...
Construct your sheets containing the source (input) data so that they
are all identical in terms of #columns/headings. Construct your output
sheet to collect the data required for its calculations from the
appropriate source sheets via formulas. IOW, your output sheet would
read input from the source sheets, do appropriate calcs, and display
the results in the proper columns. If not all results are identical
then some columns may be missing data if they're not supposed to
collect it.

This solution would require very little (if any) code since it's
primarily formula driven. Also, it will automatically update as input
data is updated. Note that it would also require heavy use of locally
defined (sheet level) names since the source sheets will be identical
in structure.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

The input sheets follows a shared business standard between companies,
so they are what they are. Also, they can never be the same, the input
data are different because the calculation of payments/pensions
differs depending on you are still working, if you are retired and so
on...

Also I prefer more code / less formulas. We're talking a lot of
data... and formulas demands more processing.



On Oct 10, 5:15*pm, GS wrote:
One simple way...
Construct your sheets containing the source (input) data so that they
are all identical in terms of #columns/headings. Construct your output
sheet to collect the data required for its calculations from the
appropriate source sheets via formulas. IOW, your output sheet would
read input from the source sheets, do appropriate calcs, and display
the results in the proper columns. If not all results are identical
then some columns may be missing data if they're not supposed to
collect it.

This solution would require very little (if any) code since it's
primarily formula driven. Also, it will automatically update as input
data is updated. Note that it would also require heavy use of locally
defined (sheet level) names since the source sheets will be identical
in structure.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

junglebear formulated the question :
The input sheets follows a shared business standard between companies,
so they are what they are. Also, they can never be the same, the input
data are different because the calculation of payments/pensions
differs depending on you are still working, if you are retired and so
on...

Also I prefer more code / less formulas. We're talking a lot of
data... and formulas demands more processing.



On Oct 10, 5:15*pm, GS wrote:
One simple way...
Construct your sheets containing the source (input) data so that they
are all identical in terms of #columns/headings. Construct your output
sheet to collect the data required for its calculations from the
appropriate source sheets via formulas. IOW, your output sheet would
read input from the source sheets, do appropriate calcs, and display
the results in the proper columns. If not all results are identical
then some columns may be missing data if they're not supposed to
collect it.

This solution would require very little (if any) code since it's
primarily formula driven. Also, it will automatically update as input
data is updated. Note that it would also require heavy use of locally
defined (sheet level) names since the source sheets will be identical
in structure.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

I see.., so the input is generated by another app[s] and so you can't
change them. Fair enough! That precludes, then, that your code needs to
read input sheets, do the calcs, then display the results on the output
sheet. This would need to be flexible so as to work correctly with each
differing input sheet, but that should be no problem sin ce you already
know the layout of each of those sheets. Perhaps using an array to hold
the input values might work. You could work on the individual elements
as required and put the results into another array, then dump that
array onto the next row in your output sheet. The arrays would be
reloaded for each record being worked on.

Alternatively, you could also use ADO and work with each input sheet as
a recordset.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc