Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class versus Another Way, Wbk and Sheet Custom Data Needs
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. -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class versus Another Way, Wbk and Sheet Custom Data Needs
Writing code using classes has an advantage when you create a new application you can simply copy the class from one workbook to another very easily. It is also nice when somebody else modifies you macros the code is orgainize in a style that will make it easier for other people to understand your code. You have to decide if it is worth the time of re-writing a macro that is already working. When you gererate a new macro you have to look at macro you already have writen and see if you are going to re-use older code or generate new code using a different style. If your older code is hard to understand because you used non-standard code then it may be worth the effot to change the code. It really depends on how well you code is documented and how often you are going to modify the code in the future. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207233 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class versus Another Way, Wbk and Sheet Custom Data Needs
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, -- Neal Z "joel" wrote: Writing code using classes has an advantage when you create a new application you can simply copy the class from one workbook to another very easily. It is also nice when somebody else modifies you macros the code is orgainize in a style that will make it easier for other people to understand your code. You have to decide if it is worth the time of re-writing a macro that is already working. When you gererate a new macro you have to look at macro you already have writen and see if you are going to re-use older code or generate new code using a different style. If your older code is hard to understand because you used non-standard code then it may be worth the effot to change the code. It really depends on how well you code is documented and how often you are going to modify the code in the future. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207233 http://www.thecodecage.com/forumz . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
class modules versus modules | Excel Programming | |||
Import data from another sheet that matches to a class list? | Excel Worksheet Functions | |||
Custom Class In VBA | Excel Programming | |||
.XLA versus XLS sheet code | Excel Programming | |||
Custom Class and Properties | Excel Programming |