Object Oriented VBA

[dug doogie]

I understand the title of the forum is Visual Basic but as I've seen other people have posted Excel and macros, I suspect it extends to VBA. My question relates to writing VBA in an object oriented fashion.

For example, this shows Compostion (to make up for the lack of Inheritance in VBA):

' ClsArea:

Option Explicit

    Private pLength As Double
    Private pWidth As Double

Public Property Get Length() As Double    
    Length = pLength    
End Property

Public Property Let Length(ByVal L As Double)
    pLength = L    
End Property

Public Property Get Width() As Double
    Width = pWidth    
End Property

Public Property Let Width(ByVal W As Double)
    pWidth = W    
End Property

Public Function CalcArea() As Double
    CalcArea = Me.Length * Me.Width
End Function


'ClsVolume

Option Explicit

    Private pHeight As Double
    Private pArea As ClsArea

Private Sub Class_Initialize()
    Set pArea = New ClsArea    
End Sub

Public Property Get Height() As Double
    Height = pHeight    
End Property

Public Property Let Height(ByVal H As Double)
    pHeight = H    
End Property

Public Property Get Area() As ClsArea    
    Set Area = pArea    
End Property

Public Property Set Area(ByVal A As ClsArea)
    Set pArea = A    
End Property

Public Function CalcVol() As Double

    'EITHER

        CalcVol = Me.Area.Length * Me.Area.Width * Me.Height

    'OR

        CalcVol = Me.Area.CalcArea * Me.Height
    
End Function


' Standard Module

Option Explicit

Sub Test()

    Dim MyVol As ClsVolume   
    Set MyVol = New ClsVolume    
    MyVol.Area.Length = 10    
    MyVol.Area.Width = 20    
    MyVol.Height = 30    
    Debug.Print MyVol.CalcVol
    
End Sub

Note in ClsVolume, I have:

Public Function CalcVol() As Double

    'EITHER

        CalcVol = Me.Area.Length * Me.Area.Width * Me.Height

    'OR

        CalcVol = Me.Area.CalcArea * Me.Height
    
End Function

I know it's a minor point, as both return the same (correct) result but would one be preferable over the other?

I am aware of the following:

   

    23 Design Patterns
    SOLID Principles

though have not really seen it  / aware it's been applied in VBA.

My main obstacle lies in the preparation stage, ie BEFORE coding takes place. I don't know how to model the task.

   

    How many classes wil be required?
    What should each be doing?
    How might they interact with each?

(My example above of Area vs Volume is one simple example (I think) showng a base class (Area) and its derived class (Volume)).

I have read a lot of OO but the material tends to cover other languages. The closest I've come across is RubberDuck (https://rubberduckvba.com/) but I found it hard to understand.

The standard response for writing OO is that it's good for large projects and makes things scalable.

Well every program starts off small and since nobody knows if it'll grow bigger, surely that means all programs should be written in an OO way?

Can someone provide examples of a VBA program written in both styles, ie "the usual way" vs in an OO way, then point out why OO might be better?

Thanks

[Tim Holloway]

Does VBA even work anymore? VB itself has been dead for half an æon.

VB.Net may actually have some OOP support, since it's based on the same language environment as C#.net. But I haven't programmed for Windows since, well VB days.

Welcome to the Ranch!

[dug doogie]

VBA is dying but not totally dead.

Regardless of the language used, how should one plan when writing an OO program?

[Tim Holloway]

I think you might find that difficult. C and Python managed to become OOP, but only modifying the core language (C++ and Python's hacked "class" support).

You could kind of fake it by storing related data in a data structure, but that doesn't allow for member functions, so the best you could do would be to set up a standard function naming convention that includes the "object" "class" as part of the procedure name. The original C++ was a pre-processor that converted C++ to C and it did something similar, although the C++ front-end managed the actual OOP functionality.

If your primary concern for going OOP would be to aid in expandability and maintainability, though, sticking with a dead language and pretending OOP is a lot of work for a dead end. Keep the VBA as long as you can, use something that's actually OOP for new stuff.

[dug doogie]

I understand VBA is not fully OO and it's not the ideal language to learn OO.

The motivation is I have to support VBA code and sometimes, I have come across VBA code that has been written in a (convoluted) OO way, possibly because the programmer was origianlly C# conversant and thought VBA needed to be written in a similar fashion.

I have trawled through the internet and the best I've seen is these kind of info:

https://morsagmon.com/blog/object-oriented-programming-with-excel-vba-part-1/

https://www.youtube.com/watch?v=vUg-NH-Wqss

Unfortunately they don't go through the process in detail, ie the planning of the number of classes that will be required before coding starts.

Perhaps a high level UML diagram would have been more informative but how many VBA programmers use that?

[Tim Holloway]

Ah. Planning and coding are two different things.

UML isn't much used these days, but if it helps you, go for it. I like the ArgoUML app myself, if it's still available and functioning.

Another popular technique involve using paper filecards, one per class.

I myself do a lot of stuff back-of-the-envelope, but as the process develops, I employ something like Knuth's Literate Programming style, where I write the comments that describe a class and what it does, then flesh out the class itself over time.