Hi Greg,
In addition to the two instances you identified in (a) where this approach would help, the third, and an overriding concern for me, is code proliferation. Why duplicate code if you can avoid it? One example is the menu management code in every one of my add-ins. It's a class with a stub in the ThisWorkbook module to instantiate the menu manager object. The class is a duplicate -- well, it's supposed to be a duplicate -- of the master version in an not-yet-published "TM Utilities" add-in. The first problem is that even if I am disciplined enough to always change only the master, the changes don't propagate to the other add-ins until I manually replace the class module in each add-in with the new master version. That can take months, if not years since some add-ins are so stable that I haven't updated them in a few years. The other potential problem is if I have an 'emergency' update to to cater to the needs of a particular add-in. In this case, I may forget to update the master. Does that happen often? No. I am both rather disciplined and my code is not *that* bad. {grin} But, it did happen once.
Similarly, I have a lot of common code for different functionalities. Code to show help, code to visit my web site, code to...well, you get the idea. So, it would be great if I could take all that code and stick it in a "TM Add-In Control" add-in.
That said, I haven't fully implemented the common control add-in because in my initial tests I ran into some strange and recurring problem but one I could not replicate at will. Basically, every so often I would get the error -- and I paraphrase -- "Fatal error: The callee has disconnected." So, the whole approach got put on the back burner.
As far as the comment in (b) goes, no, I cannot think of any good design that would allow extensive changes to a class modules variables from outside of the module. In fact, *any* change from outside constitutes, IMO, bad design. Well, with one exception. See my comment on UDTs below.
Take that bad design comment a step further. I have a major gripe with the people who designed the core concept of a class module. By the nature of the design, all properties are 'global' to the code inside the module. That, IMO, results in sloppy code. And, there is no decent simple workaround for the problem. What do I mean by this? Consider a class module
Code:
dim iX as integer
property get X() as integer
X=iX
...
property let X(uX as integer)
iX=uX
...
sub someSub1
'Here I have unconditional and unconstrained access to iX
...
sub someSub2
'The same applies here. Effectively iX is a global variable in _
this class module.
As far as UDTs go, I've stopped using them for some time. I don't recall where and how I ran into various restrictions, but UDTs declared in a particular manner cannot be passed to certain procedures. And, they don't work well with classes. So, whenever I need a UDT, I am much more likely to just use a simplified class -- one that contains nothing more than a bunch of public variables.
One final comment. MS's current implementation simplifies the use of worksheet and workbook events as well as userforms. Unfortunately, it comes at a price that IMO is too high. For some material on events see
Monitoring events
http://www.tushar-mehta.com/excel/vba/vba-XL events.htm
particularly the section 'A seemingly simplified implementation within a worksheet object'
Greg Truby said:
Hi Tushar,
Thanks for adding that information. If I may, can I pick your brain for a minute?
Two main questions:
- Nice article, but I guess I'm just not imaginative to figure out where that might come in handy. I can only come up with two applications for needing to utilize this:
- If I had the class module code locked up in an add-in where I didn't want to share the code, but wanted to utilized the class module in a project where the code needed to be open/viewable.
- If the class module was quite large and the object-borrowing workbook was a template that would be replicated many times and therefore this solution to end up saving significant file storage resources.
Otherwise I would probably just import the class module into the project. In your experience when have you needed to do what you've described in your article?
- You are much more experienced with class modules than I. Generally speaking, wouldn't you normally want to put functions that act up the class object in some manner inside the class module itself? Have you had instances where you have coded functions/procedures that impact class properties and placed said code outside the class module? [Again, lack of imagination.] What types of problems might lend themselves to such a solution?
TIA for any knowledge you can share.
Regards,
