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