Class versus Another Way, Wbk and Sheet Custom Data Needs

[Neal Zimm]

Hi All,
What are the most important factors I should consider to answer the
following question.

Should I convert a pretty large amount of WORKING vba code to use a
class for workbooks and the sheets within them so I can set up properties
for
workbooks and sheets that are germaine to my application ?

I know this is more of a consulting question, than how to code it, but I'm
at a key point in time in the development of a prettly large add-in and now
is the time to continue as I have been, or rewrite code.

Two examples: Sheet, Threshold and Inventory Count. (there are about 5
workbook "properties" and 15 for a sheet.)

Users will work on selected sheets converting data from an old application
to the new. Once a Count of inventory-type items reaches a varying threshold
the sheet is 'production' ready. Threshold is based on geographic area(a
value in a cell). Once Count is >= the threshhold, the values do not change
all that much.

Background:
- Am self taught in vba, (I've programmed in other languages, used Walken-
bach's book, ask lots of questions on this board) but never learned about
classes as it seemed not applicable at the time. In hindsight, probably a big
mistake.

- If workbooks had a .CustomProperties property(which I just found out
about) like sheets, I would go with that solution. It's a contender for my
sheets solution. The 'fact finding' code is in the example procs below.

- None of my addin's needs are hardware related, (Walkenbach's class
example in his book is toggling the NumLock key.)

- In kind of 'pseudo code', what I currently do at workbook open event and
sheet activation event, (as well as at other times) uXXXXX are Public Type
records holding the App's "custom properties" as field values.

workbook example
Call WbkFacts(Wbk, uWbkPropsAy()) 'fact array for open wbks, (#) is same
'as workbooks collection item #

sheet example
uWsFacts = WsFactsGetF(ActiveSheet) 'function makes sheet-facts record

- The calls above are also used in a "gateway" macro that lets/prevents a
user menu selected task from running against the 'wrong' workbook or the
'wrong' sheet. (e.g. Very few of the addin's procs will run when the
activeworkbook is named Like "*Personal*.xls" or "*Personal*.xlsm"

Factors I've thought about: (back to my question of what are others?)
- Maybe it's time to learn about classes, sooner or later I will need to.
- Public Type records are easily maintainable, add a field, some new code.
- Don't know how "non standard" my solution is if someone else has to
change the code.
- Since true properties stay with the object I wouldn't have to run the
fact gathering procs nearly as often. (Although they run pretty quickly)

Looking forward to your advice,
Thanks,
Neal Z.

 

[joel]

Writing code using classes has an advantage when you create a ne
application you can simply copy the class from one workbook to anothe
very easily. It is also nice when somebody else modifies you macros th
code is orgainize in a style that will make it easier for other peopl
to understand your code.

You have to decide if it is worth the time of re-writing a macro tha
is already working. When you gererate a new macro you have to look a
macro you already have writen and see if you are going to re-use olde
code or generate new code using a different style.

If your older code is hard to understand because you used non-standar
code then it may be worth the effot to change the code. It reall
depends on how well you code is documented and how often you are goin
to modify the code in the future.

 

[Neal Zimm]

Thanks Joel,
Being able to re-use classes that I would set up in other projects will
probably be one of the major deciding factors. With the current application,
that is not an easy call.

Thanks again,