Background

I have a vba solution that I use to ingest investment text reports and reformat them for analysis in Excel. It works, but the macros involve a lot of direct manipulation of Excel objects, and have no unit-testing.

After finding RubberDuck, and reading several years' worth of excellent posts from @MathieuGuindon, I've decided to re-write the "brute force"-heavy solution as a way to learn these new concepts and techniques.

When ingesting from a report, I also pull additional attributes from excel tables. I'm beginning my re-write with those lookup tables. The first of which I'm submitting here.

Initial goals:

• Programming to Interfaces not classes


• Making Services and Proxies rather than direct access to Excel sheets and ranges


• Using the PredeclaredId attribute to enable a Create method


• Thorough unit testing

Apart from general review, I also have some specific questions, which I'll post following the code.

Code

IAssetTableProxy -- abstracts reference to the "physical" excel table's data rows

'@Folder("Services.Interfaces")

Option Explicit



Public Function GetAssetTableData() As Variant()

End Function

AssetTableProxy -- Implementation

'@Folder("Services.Proxies")

Option Explicit

Implements IAssetTableProxy



Public Function IAssetTableProxy_GetAssetTableData() As Variant()



    Dim tblName As String

    tblName = "AssetInfoTable"



    IAssetTableProxy_GetAssetTableData = Worksheets(Range(tblName).Parent.Name).ListObjects(tblName).DataBodyRange.value



End Function

AssetInfo -- a class to handle the three values for each row: Desc, Ticker, Type

'@PredeclaredId

'@Folder("Services")

Option Explicit



Private Type TAssetInfo

    Desc As String

    Ticker As String

    AssetType As String

End Type

Private this As TAssetInfo



Public Property Get Desc() As String

    Desc = this.Desc

End Property



Friend Property Let Desc(ByVal value As String)

    this.Desc = value

End Property



Public Property Get Ticker() As String

    Ticker = this.Ticker

End Property



Friend Property Let Ticker(ByVal value As String)

    this.Ticker = value

End Property



Public Property Get AssetType() As String

    AssetType = this.AssetType

End Property



Friend Property Let AssetType(ByVal value As String)

    this.AssetType = value

End Property



Public Property Get Self() As AssetInfo

    Set Self = Me

End Property



Public Function Create(ByVal theDesc As String, ByVal theTicker As String, ByVal theAssetType As String) As AssetInfo



    With New AssetInfo



        .Desc = theDesc

        .Ticker = theTicker

        .AssetType = theAssetType



        Set Create = .Self 



    End With



End Function

IAssetInfoService -- holds a collection of AssetInfo objects and
provides the needed lookups to data from AssetTableProxy

'@Folder("Services.Interfaces")

Option Explicit



Public Function Create(ByRef assetTbl As IAssetTableProxy) As IAssetInfoService

End Function



Public Function GetAssetTypeForDesc(ByVal Desc As String) As String

End Function



Public Function GetTickerForDesc(ByVal Desc As String) As String

End Function

AssetInfoService -- implementation

'@PredeclaredId

'@Folder("Services")

Option Explicit

Option Base 1

Implements IAssetInfoService



Private Type TAssetsTable

    AssetColl As Collection

End Type

Private this As TAssetsTable



Friend Property Get Assets() As Collection

    Set Assets = this.AssetColl

End Property



Friend Property Set Assets(ByRef coll As Collection)

    Set this.AssetColl = coll

End Property



Public Property Get Self() As IAssetInfoService

    Set Self = Me

End Property



Public Function IAssetInfoService_Create(ByRef assetTbl As IAssetTableProxy) As IAssetInfoService



    Dim twoDArr() As Variant



    twoDArr = assetTbl.GetAssetTableData



    With New AssetInfoService



        Dim tempAsset As AssetInfo



        Dim tempColl As Collection

        Set tempColl = New Collection



        Dim rw As Long

        For rw = 1 To UBound(twoDArr, 1)

            Set tempAsset = AssetInfo.Create(twoDArr(rw, 1), twoDArr(rw, 2), twoDArr(rw, 3))



            tempColl.Add tempAsset, key:=tempAsset.Desc

        Next rw



        Set .Assets = tempColl



        Set IAssetInfoService_Create = .Self 



    End With



End Function





Public Function IAssetInfoService_GetAssetTypeForDesc(ByVal Desc As String) As String



    Dim tempTp As String

    If Exists(this.AssetColl, Desc) Then

        tempTp = this.AssetColl(Desc).AssetType

    Else

        tempTp = "Unknown Asset"

    End If

    IAssetInfoService_GetAssetTypeForDesc = tempTp



End Function



Public Function IAssetInfoService_GetTickerForDesc(ByVal Desc As String) As String



    Dim tempTicker As String

    If Exists(this.AssetColl, Desc) Then

        tempTicker = this.AssetColl(Desc).Ticker

    Else

        tempTicker = "Unknown Asset"

    End If

    IAssetInfoService_GetTickerForDesc = tempTicker



End Function



Private Function Exists(ByRef coll As Collection, ByRef key As String) As Boolean



    On Error GoTo ErrHandler



    coll.Item key



    Exists = True

ErrHandler:

End Function

Unit Testing

AssetTableTestProxy -- proxy implementation for testing w/o dependency on actual excel table

'@Folder("Services.Proxies")

Option Explicit

Option Base 1

Implements IAssetTableProxy



Public Function IAssetTableProxy_GetAssetTableData() As Variant()



    Dim twoDArr(1 To 3, 1 To 3) As Variant



    twoDArr(1, 1) = "Asset1"

    twoDArr(1, 2) = "Tick1"

    twoDArr(1, 3) = "Type1"



    twoDArr(2, 1) = "Asset2"

    twoDArr(2, 2) = "Tick2"

    twoDArr(2, 3) = "Type2"



    twoDArr(3, 1) = "Asset3"

    twoDArr(3, 2) = "Tick3"

    twoDArr(3, 3) = "Type3"



    IAssetTableProxy_GetAssetTableData = twoDArr



End Function

TestAssetInfoService -- Unit tests for Asset Info Service

Option Explicit

Option Private Module

'@TestModule

'@Folder("Tests")



Private Assert As Object

Private Fakes As Object

Private assetTbl As IAssetTableProxy



'@ModuleInitialize

Public Sub ModuleInitialize()

    'this method runs once per module.

    Set Assert = CreateObject("Rubberduck.AssertClass")

    Set Fakes = CreateObject("Rubberduck.FakesProvider")

    Set assetTbl = New AssetTableTestProxy

End Sub



'@ModuleCleanup

Public Sub ModuleCleanup()

    'this method runs once per module.

    Set Assert = Nothing

    Set Fakes = Nothing

    Set assetTbl = Nothing

End Sub



'@TestInitialize

Public Sub TestInitialize()

    'this method runs before every test in the module.

End Sub



'@TestCleanup

Public Sub TestCleanup()

    'this method runs after every test in the module.

End Sub



'@TestMethod

Public Sub GivenAssetInTable_GetTicker()

    On Error GoTo TestFail



    'Arrange:

    Dim tbl As IAssetInfoService

    Set tbl = AssetInfoService.IAssetInfoService_Create(assetTbl)



    'Act:

    Dim tick As String

    tick = tbl.GetTickerForDesc("Asset2")



    'Assert:

    Assert.AreEqual "Tick2", tick, "Tick was: " & tick



TestExit:

    Exit Sub

TestFail:

    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description

End Sub



'@TestMethod

Public Sub GivenAssetInTable_GetAssetType()

    On Error GoTo TestFail



    'Arrange:

    Dim tbl As IAssetInfoService

    Set tbl = AssetInfoService.IAssetInfoService_Create(assetTbl)



    'Act:

    Dim assetTp As String

    assetTp = tbl.GetAssetTypeForDesc("Asset2")





    'Assert:

    Assert.AreEqual "Type2", assetTp, "AssetTp was: " & assetTp



TestExit:

    Exit Sub

TestFail:

    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description

End Sub



'@TestMethod

Public Sub GivenAssetNotInTable_GetUnknownAssetMsg()

    On Error GoTo TestFail



    'Arrange:

    Dim tbl As IAssetInfoService

    Set tbl = AssetInfoService.IAssetInfoService_Create(assetTbl)



    'Act:

    Dim tp As String

    tp = tbl.GetAssetTypeForDesc("unsub")



    'Assert:

    Assert.AreEqual "Unknown Asset", tp



TestExit:

    Exit Sub

TestFail:

    Assert.Fail "Test raised an error: #" & Err.Number & " - " & Err.Description

End Sub

Module1 -- additional sub to play around with functions

Option Explicit



Sub TestAssetInfoTable()



    Dim assetTbl As IAssetTableProxy

    Dim testAssetTbl As AssetTableTestProxy



    Set assetTbl = New AssetTableProxy

    Set testAssetTbl = New AssetTableTestProxy



    Dim assetSvc As IAssetInfoService

    Dim testAssetSvc As IAssetInfoService



    Set assetSvc = AssetInfoService.IAssetInfoService_Create(assetTbl)

    Set testAssetSvc = AssetInfoService.IAssetInfoService_Create(testAssetTbl)



    Dim tp As String

    Dim tick As String



    tp = assetSvc.GetAssetTypeForDesc("AMAZON COM INC (AMZN)")

    tick = assetSvc.GetTickerForDesc("AMAZON COM INC (AMZN)")



    MsgBox ("Real Svc: tp=" & tp & "; tick=" & tick)



    tp = testAssetSvc.GetAssetTypeForDesc("Asset3")

    tick = testAssetSvc.GetTickerForDesc("Asset3")



    MsgBox ("Test Svc: tp=" & tp & "; tick=" & tick)





End Sub

Specific questions:

1. I initially had the "proxy" logic in the service class. But it felt like I was duplicating too many functions when I created the AssetInfoTestService class. Breaking it out to AssetTableProxy and AssetTableTestProxy allowed me to keep only one version of the service functions. But is this carrying things (abstraction?) too far?


2. 
   

   Learning about interfaces, I believe I understand the following pieces:

   
   
   
   
   
   • the contract created by each Function mentioned in the interface;
   
   
   • the requisite coding of corresponding Interface_Function in the implementing class;
   
   
   • the dimm-ing of class var "as Interface"; and
   
   
   • accessing the functions with classVar.Function
     
     
     
     
     • However there seems to be an exception here. In TestAssetInfoTable I dim assetSvc as IAssetInfoService. That interface has a Create function, and in the concrete class, I have IAssetInfoService_Create defined. But when I try to call AssetInfoService.Create(…) I get a compile error that only clears when I change to AssetInfoService.IAssetInfoService_Create. What am I missing there?
     
     
     
     
   
   
   
   



3. I see the "Option Base 1" thing. Since leave C pointers long ago, I haven't really had a religious belief one way or the other on 0- vs 1-based arrays. I went with it here, because when I began playing with the (extremely handy) multiDimArray = Range I noted the returned arrays are 1-based. And I kept screwing myself up between coding for those, and coding for my own 0-based ones. So I just opted to go all 1-based. Rubberduck Code Inspections do always throw that decision back in my face though, so let me ask here: are compelling reasons to not do that, or work arounds/tips for the multiDimArray = Range 1-based thing?