Initializing VBA classes - looking for a more elegant solution

Question

I'm currently working on a project where I want to initialize two different classes. I'm using a collection-class to store the sheets data.

First of all I'd like to iterate through the classes' properties; sadly it doesn't seem to be possible in VBA (without making some hacked solution).

The two functions below are almost the same, with the difference of one property. Can I somehow make a function that is more reusable in VBA?

Private Sub PasteCMSData()

    Dim PipelineCMSData As Collection
    Dim WonCMSData As Collection
    Dim I As Integer: I = 0

    Set PipelineCMSData = CollectPipeline()
    Set WonSheetName = CollectWon()

End Sub

Private Function CollectPipeline() As Collection

Private Function CollectPipeline() As Collection

    Const StartPos As Integer = 2

    Dim I As Integer: I = 0
    Dim PL As cPipeline
    Dim WorkbookData As Worksheet

    Set CollectPipeline = New Collection

    Set WorkbookData = Workbooks(WorkbookName).Worksheets(PLSheetName)

    For I = StartPos To WorkbookData.UsedRange.Rows.Count
        Set PL = New cPipeline
        With PL
            .ProjectType = WorkbookData.Cells(I, PLProjectType)
            .Segment = WorkbookData.Cells(I, PLSegment)
            .Customer = WorkbookData.Cells(I, PLCustomer)
            .Project = WorkbookData.Cells(I, PLProject)
            .Note = WorkbookData.Cells(I, PLNote)
            .CRM = WorkbookData.Cells(I, PLCRM)
            .Probability = WorkbookData.Cells(I, PLProbability)
            .Owner = WorkbookData.Cells(I, PLOwner)
            .SalesPhase = WorkbookData.Cells(I, PLSalesPhase)
            .NREPotential = WorkbookData.Cells(I, PLNREPotential)
            .RoyaltyPotential = WorkbookData.Cells(I, PLRoyaltyPotential)
            .Defcon = WorkbookData.Cells(I, PLDefcon)
            .ProjectStart = WorkbookData.Cells(I, PLProjectStart)
            .ProjectDuration = WorkbookData.Cells(I, PLProjectDuration)
        End With
        CollectPipeline.Add PL
    Next I

End Function

Private Function CollectWon() As Collection

Private Function CollectWon() As Collection

    Const StartPos As Integer = 2

    Dim I As Integer: I = 0
    Dim WO As cWon
    Dim WorkbookData As Worksheet

    Set CollectWon = New Collection

    Set WorkbookData = Workbooks(WorkbookName).Worksheets(WonSheetName)

    For I = StartPos To WorkbookData.UsedRange.Rows.Count
        Set WO = New cWon
        With WO
            .ActualCloseDate = WorkbookData.Cells(I, WOActualCloseDate)
            .ProjectType = WorkbookData.Cells(I, WOProjectType)
            .Segment = WorkbookData.Cells(I, WOSegment)
            .Customer = WorkbookData.Cells(I, WOCustomer)
            .Project = WorkbookData.Cells(I, WOProject)
            .Note = WorkbookData.Cells(I, WONote)
            .CRM = WorkbookData.Cells(I, WOCRM)
            .Probability = WorkbookData.Cells(I, WOProbability)
            .Owner = WorkbookData.Cells(I, WOOwner)
            .SalesPhase = WorkbookData.Cells(I, WOSalesPhase)
            .NREPotential = WorkbookData.Cells(I, WONREPotential)
            .RoyaltyPotential = WorkbookData.Cells(I, WORoyaltyPotential)
            .Defcon = WorkbookData.Cells(I, WODefcon)
            .ProjectStart = WorkbookData.Cells(I, WOProjectStart)
            .ProjectDuration = WorkbookData.Cells(I, WOProjectDuration)
        End With
        CollectWon.Add WO
    Next I

End Function

You could add a parameter to the function to tell it which type of object to create, then declare the object variable as Object and only assign the additional property in an `If TypeOf obj is CWon` clause. You could also use an Interface class and declare the variable as that type. — Rory, Jun 30 '14 at 10:22

score 1 · Answer 1 · edited Dec 30 '14 at 19:28

You could;

Create a CommonAttributes class that stores the data shared by both classes (.ProjectType to .ProjectDuration). Both cWon & cPipeline hold an internal instance of this class.
Move the initialize logic into the classes: CollectionWon.Add PL.LoadFromRange(I)
Make the index values enumerations within the classes;

enum WOStuff WOActualCloseDate = 1 WONREPotential = 9 ... end enum

Make LoadFromRange parse & store any class specific data (.ActualCloseDate), create an instance of CommonAttributes and invoke a method to load the common data:

myCommonAttributes.LoadFromRange(I, [paramArray of enum indexes])

or [Interfaces :)](http://stackoverflow.com/questions/19373081/how-to-use-the-implements-in-excel-vba/19379641#19379641) — , Jun 30 '14 at 10:40

score 0 · Answer 2 · edited May 23 '17 at 10:26

This will never get too elegant, but there is one improvement that can be made on the list of initializing variables.

On the sheet where they are saved, you can put them in a two-column {UniqueKey}{Value} pair and convert that range into a table Ctrl-NT.

The table can be given some column names, like Key and Value, and a unique name. Then in VBA, you can access the values by looking up the Key programmatically and finding its corresponding value. That way the table can be reordered, and some keys can be 'missed' allowing the VBA code to pump in pre-programmed default values.

The table can be accessed in VBA as a ListObject.

On the internal storage of the Key-Value pairs. You may want to consider storing it in a Dictionary if that is more suitable for your implementation. That way you can lookup the Object by Key rather than only sequentially as in a Collection.

Have a look here to see examples of a Dictionary:

Does VBA have Dictionary Structure?

Initializing VBA classes - looking for a more elegant solution

2 Answers2