Writing VBA in an object oriented way

[verysunnyday]

There are lots of information on the internet relating to object oriented coding.

However, they tend to focus on languages such as Java and C++, the reason being VBA does not have Inheritance and Polymorphism, at least not exactly like those found in the aforementioned languages.

What annoys me most is the material provided tends to say words to the effect that classes are only good for large applications.

Every application must start of small, then grows, so surely one shouldn't start writing VBA in a procedural way, then migrate to oo only when it reaches a certain size.

I think to drive the point home, what would be good is to show a VBA application written in both styles ie procedural and oo, then explain why the latter might be more beneficial.

Can someone please provide such an example?

Thanks

 

[6StringJazzer]

I have done VBA programming using an OO style but nobody would bother to do the same project both ways except as an academic exercise. I am not aware of any. If you had one, what would you do with it? Are you looking for ammunition to argue with people on the Internet? Or something for your own analysis?

As you note, VBA has object-oriented features but is handicapped as an OOL because of the lack of inheritance and polymorphism. (It also lacks support for multithreading but that is not an OO issue.)

I disagree that size is what dictates whether OO should be used, at least in the case of VBA. I would be curious to see even one cite for this. IMO performance is the most important criterion.

Migrating to OO once an application reaches a certain size would be pointless.

VBA is an interpreted language so is more sensitive to performance issues than a compiled language. (Java is also interpreted but I believe in a more sophisticated way than VBA, and generally performance is addressed by throwing hardware at it. In the early days of Java, performance was considered a major project risk.)

 

[verysunnyday]

I have done VBA programming using an OO style but nobody would bother to do the same project both ways except as an academic exercise. I am not aware of any. If you had one, what would you do with it? Are you looking for ammunition to argue with people on the Internet? Or something for your own analysis?

As you note, VBA has object-oriented features but is handicapped as an OOL because of the lack of inheritance and polymorphism. (It also lacks support for multithreading but that is not an OO issue.)

I disagree that size is what dictates whether OO should be used, at least in the case of VBA. I would be curious to see even one cite for this. IMO performance is the most important criterion.

Migrating to OO once an application reaches a certain size would be pointless.

VBA is an interpreted language so is more sensitive to performance issues than a compiled language. (Java is also interpreted but I believe in a more sophisticated way than VBA, and generally performance is addressed by throwing hardware at it. In the early days of Java, performance was considered a major project risk.)

Thanks for your reply.

You misunderstood my point re writing an application using both methods. I mean it would be good to do that just to show people like me, how / why oo might be preferable.

Obviously I'm not saying every assignment should show both methods.

In terms of performance, from my own experience, using classes tends to be slower because of the getters and setters, so if speed is required, I would say definitely don't use classes but I could be wrong.

Can you share some program you've written in an oo way and explain how it might be more complicated or maybe even impossible to do without using classes?

 

[6StringJazzer]

I only have one project that is pretty large that is done OO, and it's still in testing so I don't want to share it just yet. However, I used to teach OOD around 1990 when it was just emerging and not yet widely accepted as a de facto standard method.

In the 1970s, Ed Yourdon wrote a seminal book on design philosophy (before anyone ever talked about OO) that identified the characteristics of coupling and cohesion, along with the rationale for what methods are better and why. When you use good OO methods, you pretty much automatically get loose coupling and strong cohesion, the most desirable flavors of those characteristics. This is true regardless of what language you are using. OO defines interfaces to objects that require passing parameters to communicate data (loose coupling) and placing functionality together that is closely related to the purpose of the functionality (strong cohesion).

OO provides encapsulation of data by an object, to insulate components from having to know the implementation details of an object. This greatly increases maintainability and reliability, because when you have to make a change you make it in one place, and when you have a bug you can isolate it much quicker.

OO also provides a very clean way to partition the development work. The state and behavior of an object can be understood and implemented without having to know a lot of detail about any other object (ideally, though usually not absolutely true), and so development of different classes can be easily assigned to different developers to work on in parallel.

This is just a very quick description of why OO is good. However, many VBA applications are pretty special-purpose, do not tend to have a wide user base, tend to require little or no maintenance, and usually written by one programmer. The cost/benefit tradeoff may not be worth it if a person can whip off a quick VBA sub or two to calculate some financial metrics instead of going through an OOD phase and writing three or four classes.

Performance can be a drag because OOD tends to create smaller procedures with deeper call stacks, even for simple operations. A procedure call is a relatively expensive operation.

I will see if I can provide something a little more concrete than this with some real examples.

________________________
I am retired after 42 years in the software and system development industry. I have a BS in Computer Science and worked as a software developer for about 15 years before coming a project manager, and eventually a program manager/executive. I ran a training program to teach OOD and OOP to professional software developers in 1989-91 in my company, a large system integrator that doesn't exist anymore but it had about 100,000 employees at the time. I have developed code in several languages, including OOP methods in C++, Java, and Ada (I've also used Fortran, PL/1, C, and JOVIAL). I have used Excel and VBA extensively within my own companies but have never worked as an Excel consultant to develop VBA applications for other people.

 

[verysunnyday]

I only have one project that is pretty large that is done OO, and it's still in testing so I don't want to share it just yet. However, I used to teach OOD around 1990 when it was just emerging and not yet widely accepted as a de facto standard method.

In the 1970s, Ed Yourdon wrote a seminal book on design philosophy (before anyone ever talked about OO) that identified the characteristics of coupling and cohesion, along with the rationale for what methods are better and why. When you use good OO methods, you pretty much automatically get loose coupling and strong cohesion, the most desirable flavors of those characteristics. This is true regardless of what language you are using. OO defines interfaces to objects that require passing parameters to communicate data (loose coupling) and placing functionality together that is closely related to the purpose of the functionality (strong cohesion).

OO provides encapsulation of data by an object, to insulate components from having to know the implementation details of an object. This greatly increases maintainability and reliability, because when you have to make a change you make it in one place, and when you have a bug you can isolate it much quicker.

OO also provides a very clean way to partition the development work. The state and behavior of an object can be understood and implemented without having to know a lot of detail about any other object (ideally, though usually not absolutely true), and so development of different classes can be easily assigned to different developers to work on in parallel.

This is just a very quick description of why OO is good. However, many VBA applications are pretty special-purpose, do not tend to have a wide user base, tend to require little or no maintenance, and usually written by one programmer. The cost/benefit tradeoff may not be worth it if a person can whip off a quick VBA sub or two to calculate some financial metrics instead of going through an OOD phase and writing three or four classes.

Performance can be a drag because OOD tends to create smaller procedures with deeper call stacks, even for simple operations. A procedure call is a relatively expensive operation.

I will see if I can provide something a little more concrete than this with some real examples.

________________________
I am retired after 42 years in the software and system development industry. I have a BS in Computer Science and worked as a software developer for about 15 years before coming a project manager, and eventually a program manager/executive. I ran a training program to teach OOD and OOP to professional software developers in 1989-91 in my company, a large system integrator that doesn't exist anymore but it had about 100,000 employees at the time. I have developed code in several languages, including OOP methods in C++, Java, and Ada (I've also used Fortran, PL/1, C, and JOVIAL). I have used Excel and VBA extensively within my own companies but have never worked as an Excel consultant to develop VBA applications for other people."

"However, many VBA applications are pretty special-purpose, do not tend to have a wide user base, tend to require little or no maintenance, and usually written by one programmer. The cost/benefit tradeoff may not be worth it if a person can whip off a quick VBA sub or two to calculate some financial metrics instead of going through an OOD phase and writing three or four classes."

This is the point I meant earlier, when I said if the assignment is small, oo is rarely used.

But surely it still could be? So for educational purposes, it would be good to see how, even if it's an overkill.

I'll try to paste some of my assignments and let you and others point to where oo might be an improvement.