How to structure specific macro task, oop or procedural approach?(Excel 2003)

[junglebear]

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.

 

[kate suomah]

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 ([email protected]) to my box now so that i can send you my picture then we will know each other more;
Thanks Kate
(e-mail address removed)


Submitted via EggHeadCafe - Software Developer Portal of Choice
Mocking WCF Services Using Moq
http://www.eggheadcafe.com/tutorial...61d435d81/mocking-wcf-services-using-moq.aspx

 

[Don Guillett Excel MVP]

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.

 

[junglebear]

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...

 

[GS]

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.

 

[junglebear]

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.

 

[GS]

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.

I see.., so the input is generated by another app 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.

 

[junglebear]

junglebear formulated the question :

I guess this is what the "old" model does. It just reads the inputdata
from a range into an variant array, and then does the calculations on
this data and saves it in an outputarray which is later assigned to
the output range. But again this is a procedural approach, I was
hoping to use user defined classes / OOP to accomplish this... but
perhaps thats just not possible.

Got any resources on ADO and recordssets? I'm not familiar with
this...

I see.., so the input is generated by another app 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 athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

 

[GS]

Got any resources on ADO and recordssets? I'm not familiar with

this...

You can download an example kit from Rob Bovey's website that explains
everything you need to know about using ADO with Excell workbooks.

http://www.appspro.com/conference/DatabaseProgramming.zip

It's written for Excel2003 and earlier so if you need to code for
Excel2007 and later then just substitute the using the Jet.4.0 provider
with the Ace.12.0 provider. Likewise, substitute using Excel 8.0 in
ExtendedProperties with Excel 12.0.

Example:
for Excel 11 and earlier:
"Provider=Microsoft.Jet.OLEDB.4.0;"
"ExtendedProperties=Excel 8.0;"

for Excel 12 and later:
"Provider=Microsoft.Ace.OLEDB.12.0;"
"ExtendedProperties=Excel 12.0;"

Note that to use ADO with Excel workbooks you don't need to open the
file in Excel. Not sure if this is what you want but just offered it as
an alternative suggestion as it would allow you to put your code in a
separate file (XLA for example) so the data file can be just a
normal XLS without code.