0

I want to add custom methods to an Excel Sheet: 

Option Explicit
Implements IReadable

'Get the fields as defined on the sheet
Public Function getFields() As Variant
    'test 
    Debug.Print Me.Name
End Function

I want to add them to the sheets explicitly and call them from outside the sheet.
After adding a bunch of decorators and utility classes, the number of modules already starts to rise (there are no packages / subfolders in VBA I'm aware of (?)).
It makes sense to abstract logic related to the sheet within the sheet itself instead of creating yet another custom module for it.

Is there a way that I can do something like: 

dim oSheet as IReadable
set oSheet = thisworkbook.sheets("someSheet") 
oSheet.getFields()

This doesn't work, because the Excel sheet object itself doesn't expose this method. Yet, I'd really like to add public methods of my own that I can call from outside the Sheet module.

Thanks

Edit: I guess that I could trigger an event or something of that kind, but that smells like pretty dirty stuff...

Community
  • 1
  • 1
html_programmer
  • 18,126
  • 18
  • 85
  • 158
  • For one particular sheet or for all sheets? – Dick Kusleika Oct 22 '15 at 21:24
  • @DickKusleika It is a general question. I want to have the ability for all sheets when I need it, but currently I have one data "dump" file. I temporarily solved the problem with creating a custom decorator class that serves as a wrapper for the sheet. Kinda ugly but it works – html_programmer Oct 22 '15 at 21:55
  • 1
    Making a wrapper class for a sheet object is typically how I'd do it. – Tim Williams Oct 22 '15 at 23:15

1 Answers1

1

Your worksheet module code isn't right. It should be:

Implements IReadable

'Get the fields as defined on the sheet
Private Function IReadable_getFields() As Variant
    'test
    Debug.Print Me.Name

End Function

and then, as long as the IReadable interface class has its Instancing set to 'Public - Not creatable' (it can't be Private), you can use:

Dim oSheet                As IReadable
Set oSheet = ThisWorkbook.Sheets("Sheet1")
oSheet.getFields
Rory
  • 32,730
  • 5
  • 32
  • 35
  • Agreed, it turns out you need to add the IReadable_ prefix. Unfortunately, this doesn't solve the problem: the runtime throws a type mismatch when setting the sheet as object of type IReadable. – html_programmer Oct 23 '15 at 08:38
  • Did you make the interface class Public? – Rory Oct 23 '15 at 08:48
  • Did you recompile after making it Public? It works for me. If you still can't get it to work, you can always just add public functions to the sheet, although it's not as neat as an interface. – Rory Oct 23 '15 at 09:16
  • It works, I didn't set the right instancing flag on the interface. Thanks! – html_programmer Oct 23 '15 at 09:50
  • Was excited about this, but then I saw [this](https://stackoverflow.com/questions/63989741/bug-when-using-interfaces-on-larger-projects/64188053?r=SearchResults&s=1|0.0000#64188053) . I suppose you can still make a wrapper class with just the properties and methods you need. – klausnrooster Oct 10 '20 at 14:44