VBA Excel Interface implementation and inheritance issues

Question

My task is a little complicated, first I show you the problem, then what I tried and where i get lost.

So let's assume I have three worksheets (A, B, C). These sheets have a table. The table has titles with similar and different names. Also the position of the titles is different, and the order is important:

• A sheet table titles: AA, BB, CC, DD, FF
• B sheet table titles: BB, AA, DD, EE
• C sheet table titles: AA, DD, BB, CC

I have different Modules (Module1, Module2, ...) in my code. These modules are assigned to sheets. So:

• Module1, Module2 -> A sheet
• Module3, Module4 -> B sheet
• Module5, Module6 -> C sheet

The As-is functionality is these modules has static reference to the table title's column name ->

• Module1 and Module2 'know' title 'DD' in sheet 'A' is at column 'D'.
• Module3 and Module4 'know' title 'DD' in sheet 'B' is at column 'C'.
• Module5 and Module6 'know' title 'DD' in sheet 'C' is at column 'B'.
• and so on....

My problem with this is when I have to add a new title inside any of the sheets, it is a kind of pain in the ass ->

• Sheet A new title -> AA, BB, XX, CC, DD, FF

The As-is functionality at this point is I have to debug all of my Modules related to Sheet 'A' to change the column reference because of the new title. At the example above I have to change titles CC, DD, FF column references in every modules (Module1 and Module2).

So I tried to design some centralized column reference store, where I place the column reference of titles related to every sheet. The modules retrieve column information from this store so if a new title pop up anytime I have to change the column reference of the titles just here. Seems like a thing I really love coding :D

My original idea was to create a ClassModule to every sheet. This class module would have public function, what are the references of the column of the titles ->

Function titleDDinsheetA() As String
   titleDDinsheetA = "D"
End Function 

But as you can see at the example there are similar title names, like 'AA' exists all of the sheets. So I planned to create an interface to make sure every common occurrence of a title would appear in every classmodule implementation. So ->

Interface called ICommonTitles

Option Explicit

Public Function titleDD()
End Function

Class module to Sheet A called SheetATitles

Option Explicit
Implements ICommonTitles

Public Function ICommonTitles_titleDD()
    ICommonTitles_titleDD = "D"
End Function

But as you see in the title structure above I have sheet specify titles, like FF in sheet A. Also there are titles what is exists in the subset of all sheets -> title CC exists in sheet A and C. So I tried to add custom public functions to the SheetATitles classmodule, like ->

Option Explicit
Implements ICommonTitles

Public Function ICommonTitles_titleDD()
  ICommonTitles_titleDD = "D"
End Function

Public Function titleCC()
  titleCC = "C"
End Function

At this point I started to get errors. The example above give this:

Sub test()
  Dim testcls As ICommonTitles 
  Set testcls = New SheetATitles

  MsgBox testcls.titleDD
  MsgBox testcls.titleCC
End Sub

At this point I got error like Method or data member not found at line MsgBox testcls.titleCC. I tried to change the instantiate to:

Sub test()
  Dim test_cls As SheetATitles
  Set test_cls = New SheetATitles

  MsgBox testcls.titleDD
  MsgBox testcls.titleCC
End Sub

At this point I got the same error but at line MsgBox test_cls.titleDD.

I tried to do something like interface inheritance. So I create a sheet specific interface and I implemented that in SheetATitles. So:

Interface called ICommonTitles

Option Explicit

Public Function titleDD()
End Function

Interface called ISheetATitles

Option Explicit
Implements ICommonTitles

Public Function ICommonTitles_titleDD()
End Function

Public Function titleCC()
End Function

Class module to Sheet A called SheetATitles

Option Explicit
Implements ISheetATitles

Public Function ISheetATitles_ICommonTitles_titleDD()
  ISheetATitles_ICommonTitles_titleDD = "D"
End Function

Public Function ISheetATitles_titleCC()
  ISheetATitles_titleCC="C"
End Function

But at this point I got compile error, like: Object module needs to implement ICommonTitles_titleDD for interface ISheetATitles.

So I got lost at this point :D First I simply don't get why I can not create custom public function at classmodule when I implement an interface inside the classmodule. Also I don't get why I get the error above. Also I come from Java side of programming world and didn't get deep into VBA yet, so I may miss something fundamental.

Any ideas guys?

rgds,

'Child

Answer 1

Simpler approach:

Enum Sheet1Headers
    AA = 1
    BB = 2
    CC = 3
End Enum

Enum Sheet2Headers
    AA = 1
    BB = 3
    CC = 5
    DD = 7
End Enum

Enum Sheet3Headers
    AA = 1
    BB = 3
    XX = 4
    DD = 7
End Enum

Then you can do something like:

With Sheet1.Cells(2, Sheet1Headers.AA)

If you also want the option to use column letters:

Function Letter(v As Long)
    Letter = Replace(Cells(1, v).Address(False, False), "1", "")
End Function

Then:

? letter(Sheet2Headers.BB)  'C