0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HowToTellAChild
  • 623
  • 1
  • 9
  • 21
  • 1
    The underscore is a significant seperator in VBA interfaces. It should not appear in any method name except between the interface and the method name so method names such as 'Call_me_if_You_Can' should be renamed to 'CallMeIfYouCan' – freeflow Mar 28 '21 at 13:19
  • Does `MATCH` not do it?? https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.match – Nathan_Sav Mar 28 '21 at 13:58
  • @freeflow ty the information! My exact code a little different in the way of method naming. I updated the question to show more like the original one. – HowToTellAChild Mar 28 '21 at 16:37
  • @Nathan_Sav Could u please describe where can I use MATCH function in my problem? I didn't get it.. :( – HowToTellAChild Mar 28 '21 at 16:38
  • `application.WorksheetFunction.Match("DD",rows(1),false)` – Nathan_Sav Mar 30 '21 at 15:19

1 Answers1

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125