Interface Implementation in VBA and VB6

WARNING THIS CODE HAS A MEMORY LEAK: check UPDATE HERE

I have realized that setting Set mInstruments = Me in the Class inizialize method create a circular reference and so a memory leak. The new code is much better.

The Keyworkd Implements in VBA/VB6 allows you to Interface Implementation in VB6, which in turns allows for Polymorfism. This is indeed a great capabilites on VB6 language that is often overlooked.
While the VB6 version of interface implementation is not so elegant as the one from .NET environment, it still allows you to implement some nice pattern like the strategy or the abstract facotory one.
I will show you a very easy example on how to use it in VBA."
The Idea is to create an interface calle IInstrumets with just one property "Id", and have a Security class that implements its interface. You could also have a Fund, Porftolio or a Security Class that implements its interface. This wil allow for polymorfism.

Dim inst as IInstrument
Dim sec as Security
Dim fn as Fund

Set sec = new Security
Set fn = new Fund

Set inst = sec
Set inst = fn

As you can see, bot a security and a fund can be assigned to an Instruments object!
We first define a Class called IInstrument. The code is here

'This is an Interface for the Generic Financial Instrument

Public Property Get Id() As String
'Only Signature
End Property

Public Property Let Id(strId As String)
'Only Signature
End Property

We now create a new Class called Security that Implements the IInstrument one. This is a bit more tricky.
Once we implement an Interface, the Class that implements it in VB6 will declare those method as Private like that.

Private Property Get IInstrument_Id() As String
   IInstrument_Id = mId
End Property

This is a kind of unsual behaviour, because if I know that the Security class implements the IInstrument interface, I expect to have access to the same methods and property made available by the interface obejcts. For this reason, I usually code the Class using this rules
1) In the Class that implements the interface I declare at Class level a private object of the interface type

      Private mInstrument As IInstrument



2) In the constructor I assing the Obj to this instance. This will allow me to call the interface methods of my class

  Private Sub Class_Initialize()
    'I need to Access the IInstruments Methods
    Set mInstrument = Me
End Sub

3) I implement the interface methods as I normally do.

Private Property Get IInstrument_Id() As String

    IInstrument_Id = mId  
End Property

Private Property Let IInstrument_Id(strId As String)
   mId = strId
End Property

4) I crate public properties / methods mirroring the interface delegating their implementation to the
    mInstrument object
Public Property Get Id() As String

   Id = mInstrument.Id
End Property

Public Property Let Id(strId As String)
   mInstrument.Id = strId
End Property

Here you can find the Security Class Code

Implements IInstrument

Private mId As String
Private mInstrument As IInstrument

Public Ticker As String

Private Sub Class_Initialize()
  'I need to Access the IInstruments Methods
  Set mInstrument = Me
End Sub

Private Property Get IInstrument_Id() As String
       IInstrument_Id = mId
End Property


Private Property Let IInstrument_Id(strId As String)
  mId = strId
End Property


'Public Interface

Public Property Get Id() As String
     Id = mInstrument.Id
End Property

Public Property Let Id(strId As String)
  mInstrument.Id = strId
End Property

We can now test the code

Sub TestSecurity()
 Dim Sec1 As Security
 Dim Inst As IInstrument
 Dim Sec2 As Security
 Set Sec1 = New Security
 
 Sec1.Id = 10
 Sec1.Ticker = "MXEU"
 
 Set Inst = Sec1 'Upcast: A Security in an Instruments
 Debug.Print Inst.Id
 
 'DownCast, this should have been done explicit, but VBA does not support CType.
 'VB6 does. So instead of CType(Inst, "Security") we can do
 If TypeName(Inst) = "Security" Then
    Set Sec2 = Inst
    End If
 
 
 Set Sec2 = Inst

 Debug.Print Sec2.Id
 Debug.Print Sec2.Ticker
End Sub

VB6 and VBA Enumerations

An Enumeration in VBA/VB6 is a special type of long Variable.
A great resource can be found here on cpearson.com

Enum Position
  [_First] = -1
  RelativeDynamic = 0
  RelativeStatic = 1
  Absolute = 2
  [_Last] = 3
End Enum


Enum FactorEngine
  [_First] = -1
  AA = 0
  [_Last] = 1
End Enum

  

The code above specify two kind of Enumerations, Position and FactorEngine.
The [_First] and [_Last] are not necessary but they can be used to validate the Enumerated variables.
The _ makes the Enumrated variable hidden to the intellisense, while the [ makes it a valid character for the VB6 interpreter.
This is an example on how to Validate Enumerations

Sub TestEnum()
Dim Fac As FactorEngine
Dim i As Long
Dim IsValid As Boolean

Fac = AA
IsValid = False

For i = FactorEngine.[_First] To FactorEngine.[_Last]

  If Fac = i Then
      IsValid = True
      Exit For
  End If
Next i

If IsValid = True Then
   Debug.Print Fac & " Is a valid Engine"
Else
   Debug.Print Fac & " Is NOT a valid Engine"
End If


End Sub

UML Association, dependency, composition and aggregation

These are a couple of forum post I found that goes straight to the point (I think)

An association almost always implies that one object has the other object as a field/property/attribute (terminology differs). A dependency typically (but not always) implies that an object accepts another object as a method parameter, instantiates, or uses another object. A dependency is very much implied by an association.

A dependency is very general and lowering complexity is about diminishing dependencies as much as possible. An association is a strong (static) dependency. Aggregation and Composition are even stronger


Dependency is like when you define a method that takes a String(in Java, C#, as string is a object in them) as a parameter, then your class is dependent on String class.

Association is like when you declare a string as an attribute in your class. then your code is associated with the string class.
String name = null //: is a association.


Aggregation is a kind of association that specifies a whole/part relationship between the aggregate (whole) and component part. This relationship between the aggregate and component is a weak “has a” relationship as the component may survive the aggregate object. The component object may be accessed through other objects without going through the aggregate object. The aggregate object does not take part in the lifecycle of the component object, meaning the component object may outlive the aggregate object. The state of the component object still forms part of the aggregate object.




An example of aggregation is a History-Class object contains zero or more of Student objects. The state of each Student object has an influence on the state of the History-Class object. If the History-Class object is destroyed, the Student objects may continue to exist.

Composition is a kind of association very similar to aggregation except where the composite object has sole responsibility for the disposition of the component parts. The relationship between the composite and the component is a strong “has a” relationship, as the composite object takes ownership of the component. This means the composite is responsible for the creation and destruction of the component parts. An object may only be part of one composite. If the composite object is destroyed, all the component parts must be destroyed, or the reference and responsibility of the component part must be handed over to another object. Composition enforces encapsulation as the component parts usually are members of the composite object.




An example of composition is a House object contains zero or more Room objects. The state of each Room object has an influence on the House object. If the House object is destroyed, the Room objects will also be destroyed.



To to me is kind of like

Compostion -> Aggegation -> Association -> Dependency

How to decouple the business layer and the DAL suing the provider model pattern

A way to make the DAL layer indipendent of the BLL is as follow
The Layer between the Business and Data Layer will be made of Abstact Classes (instead of interfaces)
The Idea is that each abstract class has a public static get property "Instance" that use reflection to create a concrete class. The concrete class to be created is defined in a configuration file
The code call will look like this

IRSBodyProvider irsBodyProvider = IRSBodyProvider.Instance

Or if we create a helper class DataProvider we will have

IRSBodyProvider irsBodyProvider = DataProvider.IRSBody

---------------------------
Code
---------------------------

public Abstract IRSBodyProvider
{

   static private ArticlesProvider _instance = null;
   ///

   /// Returns an instance of the provider type specified in the config file
   ///

    static public ArticlesProvider Instance
   {
        get
        {
             if (_instance == null)
            _instance = (ArticlesProvider)Activator.CreateInstance(
                     Type.GetType(Globals.Settings.Articles.ProviderType));
            return _instance;
         }
   }
}

static public class DataProvider
{
    public static IRSBodyProvider IRSBody
   {
       get { return IRSBodyProvider.Instance; }
   }
}


Another way to make the DAL Layer indipendent of the BLL is
1) Create a Layer between the DAL and BLL with Provider Interfaces
     IIRSBodyProvider (with the CRUD Method signature)
     ITRSBodyProvider (with the CRUD Method signature)

2) Then we add in the same Layer an abstract factory "ProviderFactory" that creates/gets the provider inferfaces

IIRSBodyProvider IProviderFactory.GetIRSBodyProvider()
ITRSBodyProvider IProviderFactory.GetTRSBodyProvider()


3) Then we create in the DAL some concrete implementation of the IProviderFactory.

class SQLProvideFactory : IProviderFactory {

   IIRSBodyProvider IProviderFactory.GetIRSBodyProvider()
   ITRSBodyProvider IProviderFactory.GetTRSBodyProvider()
}

calss XMLProviderFactory : IProviderFactory {

   IIRSBodyProvider IProviderFactory.GetIRSBodyProvider()
   ITRSBodyProvider IProviderFactory.GetTRSBodyProvider()
}
Note how we are implementing the Provider Model Patter + Abstract Factory Patter to create decoupling from the Layers.
In Addition note how the two methods are the equivalent of the Instance method created in the first solution of the abstact class. They are the ones creating the concrete class. In this solution is the factory that is creating the class non the static method of the abstract class)

4) We then create a static class client of the factory called DataProvider that instanziate a concrete IProviderFactory using Reflection.

The call to the code will look like this

IIRSBodyProvider irsBodyProvider = DataProvider.IRSBody

We will use this kind of call in the retrive method of the IRSBody object.
The decoupling of the BOL and the DAL is complete

This is a Static Class. It works more or less like this

DataProvider.IRSBody.Retrieve(irsId, trsId)
DataProvider.TRSBody.Retrieve(trid, fundId)



-------------------------------------------------------------------------------------------------

PSEUDO CODE FOR THE CLASS

--------------------------------------------------------------------------------------------------

static Class DataProvider {
    //We provide a private member to hold and instance of the class
    static private IProviderFactory _factory = null;
    static DataProvider()
   {
     //NomeAssembly and SomeProviderFactory can be read from some configuration file
     string nome = "NomeAssembly";
     Assembly assembly = Assembly.Load(nome);
     _factory = (IDataProviderFactory)assembly.CreateInstance(nome+".SomeProviderFactory");
    }

  public static IIRSBody IRSBody()
  {
    // Here we assume that the factory does not create a new instance of the provide each time we call the
    // method get.
      return factory.GetIRSBodyProvider();
   }
}
 
 

How to Create a Strongly typed Collection in Vba

An Excel File with the sample code can be found here

In this blog post I will show you how to create a Strongly Type Collection Class wich has both an Item as default property and for wich we can use the For Each Loop.
First we create a very simple Class named Person with just three properties Name, Surname and Date of Birth

Option Explicit

Dim mName As String
Dim mSurname As String
Dim mDateofBirth As Date

Public Property Get Name() As String
  Name = mName
End Property
Public Property Let Name(strName As String)
   mName = strName
End Property
Public Property Get Surname() As String
   Surname = mSurname
End Property
Public Property Let Surname(strSurname As String)
   mSurname = strSurname
End Property
Public Property Get DateOfBirth() As Date
  DateOfBirth = mDateofBirth
End Property
Public Property Let DateOfBirth(dteDateofBirth As Date)
   mDateofBirth = dteDateofBirth
End Property 

Then we can create the Collection Class called People

Option Explicit

'This is going to be a stroingly type Collection

Private mCol As Collection

Private Sub Class_Initialize()
  Set mCol = New Collection
End Sub
Private Sub Class_Terminate()
  Set mCol = Nothing
End Sub
Property Get Item(Index As Variant) As Person
   'Attribute Item.VB_UserMemId = 0
   'This Attribute makes Item the default property
      
   Set Item = mCol.Item(Index)
End Property
Property Get NewEnum() As IUnknown
  'Attribute NewEnum.VB_UserMemId = -4
  'Attribute NewEnum.VB_MemberFlags = "40"
  
  'The first Attribute makes it the Default Enumerator Property
  'The second Attribute makes the Enumerator a hidden property. This does not work with the VBA intellisense
 
  
  'This Routine  Get the Enumerator for the Collection.
  'To get this to work you must add two attributes
  
  
  
  Set NewEnum = mCol.[_NewEnum]
End Property
Public Sub Add(Item As Person, Optional key As Variant)
  Call mCol.Add(Item, key)
End Sub
Public Function Count() As Long
   Count = mCol.Count
End Function
Public Sub Remove(Index As Variant)
   mCol.Remove (Index)
End Sub

There are few Attributes that you cannot see in the VBA IDE. If you export a file from one of your VB6 procedures and view it, you'll notice that some Attributes, not visible while editing your code, are added to the top of the routine(s). The two properties in question will look something like this:

Property Get Item(Index As Variant) As Parameter
       Attribute Item.VB_UserMemId = 0   

       Set Item = m_Collection.Item(Index)
End Property

Property Get NewEnum() As IUnknown

   Attribute NewEnum.VB_UserMemId = -4  
   Attribute NewEnum.VB_MemberFlags = "40"

  Set NewEnum = Me.mCollection.[_NewEnum]

End Property

Note that the Attribute directive must be just below the functions signatures, otherwise the code will not work.
Now the above all looks "normal" except for the addition of the three "Attribute" Lines.
In the Item Property the line "Attribute Item.VB_UserMemId = 0" makes it the default property.
In the NewEnum, the "Attribute NewEnum.VB_UserMemId = -4" makes it the Default Enumeration Property (I'm sure you recognize the "-4" part.)
The Attribute NewEnum.VB_MemberFlags = "40" is to make the Enumerator a Hidden property, but, technically, this is not recognized in VBA, so it will be visible in IntelliSense, but I don't find that a big deal.

The solution is to
(1) Make your Class
(2) SAVE
(3) Export the Class
(4) Remove the Class (steps 3 and 4 can be combined into one, as it asks you if you wish to "Export" when you right-click and choose "Remove")
 (5) Manually add the Attribute Lines as shown above
 (6) Re-Import the edited Class

 An easier way, as one of my reader pointed out is
1) To write the Attributes directives directly on the VBA Ide. You will get a syntax error. Ignore it
2)  Export the Class
3) Remove the Class
4) Reinport it again.
 The Attribute will be in the .cls file, but they will not be visible (you can add the Attribute NewEnum.VB_MemberFlags = "40" line if you wish -- it won't hurt anything -- but it won't be recognized in VBA, it will just be quietly ignored. So there's no reason to bother doing this, really.)
As you know, editing the code thereafter has some propensity to lose these properties (even in VB6) and so this may have to be repeated occassionally. (A bit of a pain.) The alternative is to create your class 100% within VB6 and then import it into your VBA Project. Or, even better, make it in VB6, debugg it, get it running 100%, compile to DLL and then add this DLL to your references. This last concept is probably the most solid, but there could be deployment issues as your DLL now has to be correctly Registered on the Client machine. Not that this is a big problem, but it's not as easy as distributing a VBA Project...

This is the Code you can use to test the Class

Option Explicit

Sub prova()

Dim Employee As Person
Dim Director As Person
Dim Team As People
Dim p As Person
Dim i As LongSet Employee = New Person
Employee.DateOfBirth = "10 Jan 1974"
Employee.Name = "Mario"
Employee.Surname = "Rossi"Set Director = New Person
Director.DateOfBirth = "10 Mar 1970"
Director.Name = "Giulia"
Director.Surname = "Verdi"Set Team = New People
Call Team.Add(Employee)
Call Team.Add(Director)

For i = 1 To Team.Count
 Debug.Print Team(i).Name
  
Next i

For Each p In Team
  Debug.Print p.Name
NextEnd Sub