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

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
– freeflow, Commented Mar 28, 2021 at 13:19
Does MATCH not do it?? learn.microsoft.com/en-us/office/vba/api/… — Nathan_Sav
– Nathan_Sav, Commented Mar 28, 2021 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
– HowToTellAChild, Commented Mar 28, 2021 at 16:37
@Nathan_Sav Could u please describe where can I use MATCH function in my problem? I didn't get it.. :( — HowToTellAChild
– HowToTellAChild, Commented Mar 28, 2021 at 16:38

Tim Williams · Accepted Answer · 2021-04-02 16:12:20Z

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

edited Apr 2, 2021 at 16:12

answered Mar 29, 2021 at 6:41

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HowToTellAChild Over a year ago

This solve my problem! Just one thing: I coudn't add letter to enum constants but number. So if I wanted to have the letter reference of the colum, I need a transformation like: Split(Cells(, Sheet1Headers.AA).Address, "$")(1)

Tim Williams Over a year ago

Yes the enum value needs to be numeric, but that works fine with Cells

HowToTellAChild Over a year ago

True, I just didn't want to change my Range() related code to Cells().

Collectives™ on Stack Overflow

VBA Excel Interface implementation and inheritance issues

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related