Of Interface Implementation and Workbook Modules

[Greg Truby]

BACKGROUND


I have a suite of over a dozen related month-end reports that all involve similar processes. An abridged overview would be:

• import data from mainframe report
• parse
• append data to monthly workbook
• [optional] append to consolidated data sets
• [optional] update pivot table(s)

This obviously lends itself to developing a common update process for the entire suite. In order to manage the various processing options I use PROPERTY statements in the workbooks. Example (succint)

'...
    ' __objects_________
    Dim owbReport As Object, owsNew As Object
'...
    Set owbReport = wbReportFile
'...
    '// consolidate the data
    If owbReport.HasConsol Then
        '// Each report workbook has a SpecProcPreConsol property
        '// that will return the name of a procedure that needs to be
        '// run prior to doing the consolidation.
        If owbReport.SpecProcPreConsol <> vbNullString Then
            '// if project name has spaces, enclose
            '// in single quotes else RUN will fail
            Application.Run ("'" & wbReportFile.Name & "'!" & owbReport.SpecProcPreConsol)
        End If
        Set wsToConsol = owbReport.ConsolSheet
'...
        If owbReport.SpecProcPostConsol <> vbNullString Then
            Application.Run ("'" & wbReportFile.Name & "'!" & owbReport.SpecProcPostConsol)
        End If
'...
        If owbReport.HasPivot Then
            If owbReport.SpecProcPivot <> vbNullString Then
                Application.Run ("'" & wbReportFile.Name & "'!" & owbReport.SpecProcPivot)
            End If
            Set wsToPivot = owbReport.PivotSheet
        End If
'...
        End If '// has pivot
    End If '// has consol

Where I get into trouble is when I add a new report to the suite, sometimes I would forget to include all of the needed properties.

So, I says's to myself -- "Aha! Here's a chance to use that fancy IMPLEMENTS statement I read about in Bullen & Bovey's book! That would make the common interface schtuff explicit instead of implicit." And so I whipped up a test environment. And lo and behold, I found that using and applying IMPLEMENTS / INTERFACES with workbooks and worksheets is not as easy or as straightforward as it is with UserForms.




The test environment objectives were:

• Test objects within the project itself
  • Userform
    • Using Interface Object
  • Workbook
    • Using Interface Object
    • Using Workbook Object
    • Using generic OBJECT Object
  • Worksheet
    • Using Interface Object
    • Using Workbook Object
    • Using generic OBJECT Object
• Test on objects located outside the project:
  • Workbook
    • Using Interface Object
    • Using Workbook Object
    • Using generic OBJECT Object
  • Worksheet
    • Using Interface Object
    • Using Workbook Object
    • Using generic OBJECT Object

Test Environment:
THE "FOREIGN" PROJECT

(represents the individual report workbooks)
class module IclsInterfaceTest
Note: for this to work you must open the properties window in the VBE and change the Instancing property from 1 - Private to 2 - PublicNotCreateable

Option Explicit
Public Enum ge_Hillbillies
    eClampettUncleJed = 1
    eMosesGranny
    eClampettEllyMay
    eBodineJethro
    eDrysdaleMilburn
    eHathawayJane
End Enum
Public Property Get FavoriteHillbilly() As ge_Hillbillies
End Property
Public Property Get FavoriteCastaway() As String
End Property

code module for first worksheet OUTSIDE the project

'// sheet1 : Other workbook
Option Explicit
Implements IclsInterfaceTest
Public Property Get IclsInterfaceTest_FavoriteCastaway() As String
    IclsInterfaceTest_FavoriteCastaway = "Ginger"
End Property
Public Property Get IclsInterfaceTest_FavoriteHillbilly() As ge_Hillbillies
    IclsInterfaceTest_FavoriteHillbilly = eHathawayJane
End Property

code module for the workbook OUTSIDE the project

'// Other workbook
Option Explicit
Implements IclsInterfaceTest
Public Property Get IclsInterfaceTest_FavoriteCastaway() As String
IclsInterfaceTest_FavoriteCastaway = "Professor"
End Property
Public Property Get IclsInterfaceTest_FavoriteHillbilly() As ge_Hillbillies
IclsInterfaceTest_FavoriteHillbilly = eClampettUncleJed
End Property

* * * * *
Test Environment:
LOCAL PROJECT
(represents the Add-In that runs the show)
class module IclsInterfaceTest

Option Explicit
Public Enum ge_Hillbillies
    eClampettUncleJed = 1
    eMosesGranny
    eClampettEllyMay
    eBodineJethro
    eDrysdaleMilburn
    eHathawayJane
End Enum
Public Property Get FavoriteHillbilly() As ge_Hillbillies
End Property
Public Property Get FavoriteCastaway() As String
End Property

code module for first worksheet w/in the project

Option Explicit
Implements IclsInterfaceTest
Public Property Get IclsInterfaceTest_FavoriteCastaway() As String
    IclsInterfaceTest_FavoriteCastaway = "Mary Ann"
End Property
Public Property Get IclsInterfaceTest_FavoriteHillbilly() As ge_Hillbillies
    IclsInterfaceTest_FavoriteHillbilly = eClampettEllyMay
End Property

workbook module w/in the project

Option Explicit
Option Explicit
Implements IclsInterfaceTest
Public Property Get IclsInterfaceTest_FavoriteCastaway() As String
    IclsInterfaceTest_FavoriteCastaway = "Mrs. Howell"
End Property
Public Property Get IclsInterfaceTest_FavoriteHillbilly() As ge_Hillbillies
    IclsInterfaceTest_FavoriteHillbilly = eMosesGranny
End Property

userform module within the project

Option Explicit
Implements IclsInterfaceTest
Private Property Get IclsInterfaceTest_FavoriteCastaway() As String
    IclsInterfaceTest_FavoriteCastaway = "Gilligan"
End Property
Private Property Get IclsInterfaceTest_FavoriteHillbilly() As ge_Hillbillies
    IclsInterfaceTest_FavoriteHillbilly = eBodineJethro
End Property

the Standard Module that then ran through the above test schema

Option Explicit
Const mc_strOtherWBName As String = "test implements on wb module.xls"
Sub TestingImplementations()
    Dim strStep$
 
    Dim ufIface As IclsInterfaceTest, _
        wbThisWB As Workbook, _
        objThisWB As Object, _
        ifcThisWB As IclsInterfaceTest, _
        wsSheet1 As Worksheet, _
        objSheet1 As Object, _
        ifcSheet1 As IclsInterfaceTest
    Dim wbThatWB As Workbook, _
        objThatWB As Object, _
        ifcThatWB As IclsInterfaceTest, _
        wsThatSheet1 As Worksheet, _
        objThatSheet1 As Object, _
        ifcThatSheet1 As IclsInterfaceTest
 
    On Error GoTo ErrorHandler
    '// __userform located w/in project____
    Set ufIface = New UserForm1
    '// no problem using short form
    Debug.Print "ufIface.FavoriteCastaway --> "; ufIface.FavoriteCastaway
    Debug.Print "ufiface.FavoriteHillbilly --> "; ufIface.FavoriteHillbilly
    '// won't compile
    'Debug.Print "ufIface.FavoriteCastaway --> "; ufIface.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "ufiface.FavoriteHillbilly --> "; ufIface.IclsInterfaceTest_FavoriteHillbilly
 
 
 
    '// __workbook located w/in project____
 
    strStep = "wbThisWB"
    Set wbThisWB = ThisWorkbook
    '// raises object does not support error
    Debug.Print "wbThisWB.FavoriteCastaway -->"; wbThisWB.FavoriteCastaway
    Debug.Print "wbThisWB.FavoriteHillbilly -->"; wbThisWB.FavoriteHillbilly
    '// works using long form
    Debug.Print "wbThisWB.FavoriteCastaway -->"; wbThisWB.IclsInterfaceTest_FavoriteCastaway
    Debug.Print "wbThisWB.FavoriteHillbilly -->"; wbThisWB.IclsInterfaceTest_FavoriteHillbilly
 
    strStep = "objThisWB"
    Set objThisWB = ThisWorkbook
    '// raises object does not support error
    Debug.Print "ObjThisWB.FavoriteCastaway -->"; objThisWB.FavoriteCastaway
    Debug.Print "ObjThisWB.FavoriteHillbilly -->"; objThisWB.FavoriteHillbilly
    '// works using long form
    Debug.Print "ObjThisWB.FavoriteCastaway -->"; objThisWB.IclsInterfaceTest_FavoriteCastaway
    Debug.Print "ObjThisWB.FavoriteHillbilly -->"; objThisWB.IclsInterfaceTest_FavoriteHillbilly
 
    strStep = "ifcThisWB"
    Set ifcThisWB = ThisWorkbook
    '// no problem
    Debug.Print "ifcThisWB.FavoriteCastaway -->"; ifcThisWB.FavoriteCastaway
    Debug.Print "ifcThisWB.FavoriteHillbilly -->"; ifcThisWB.FavoriteHillbilly
    '// won't compile
    'Debug.Print "ifcThisWB.FavoriteCastaway -->"; ifcThisWB.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "ifcThisWB.FavoriteHillbilly -->"; ifcThisWB.IclsInterfaceTest_FavoriteHillbilly
 
 
    '// __worksheet located w/in project___
 
    strStep = "wsSheet1"
    Set wsSheet1 = Sheet1
    Debug.Print strStep; "..."; wsSheet1.Name
    '// won't compile
    'Debug.Print "wsSheet1.FavoriteCastaway -->"; wsSheet1.FavoriteCastaway
    'Debug.Print "wsSheet1.FavoriteHillbilly -->"; wsSheet1.FavoriteHillbilly
    '// won't compile
    'Debug.Print "wsSheet1.FavoriteCastaway -->"; wsSheet1.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "wsSheet1.FavoriteHillbilly -->"; wsSheet1.IclsInterfaceTest_FavoriteHillbilly
 
    strStep = "objSheet1"
    Set objSheet1 = Sheet1
    Debug.Print strStep; "..."; objSheet1.Name
    '// raises object does not support error
    Debug.Print "objSheet1.FavoriteCastaway -->"; objSheet1.FavoriteCastaway
    Debug.Print "objSheet1.FavoriteHillbilly -->"; objSheet1.FavoriteHillbilly
    '// works using long form
    Debug.Print "objSheet1.FavoriteCastaway -->"; objSheet1.IclsInterfaceTest_FavoriteCastaway
    Debug.Print "objSheet1.FavoriteHillbilly -->"; objSheet1.IclsInterfaceTest_FavoriteHillbilly
    strStep = "ifcSheet1"
    Set ifcSheet1 = Sheet1
    '// won't compile
    'Debug.Print strStep; "..."; ifcSheet1.Name
    '// no problem
    Debug.Print "ifcSheet1.FavoriteCastaway -->"; ifcSheet1.FavoriteCastaway
    Debug.Print "ifcSheet1.FavoriteHillbilly -->"; ifcSheet1.FavoriteHillbilly
    '// won't compile
    'Debug.Print "ifcSheet1.FavoriteCastaway -->"; ifcSheet1.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "ifcSheet1.FavoriteHillbilly -->"; ifcSheet1.IclsInterfaceTest_FavoriteHillbilly
 
 
 
    '// __workbook located outside of the project____
 
    strStep = "wbThatWB"
    Set wbThatWB = Workbooks(s)
    Debug.Print String(20, "~")
    Debug.Print "That workbook name: "; wbThatWB.Name
 
    '// raises object does not support error
    Debug.Print "wbThatWB.FavoriteCastaway -->"; wbThatWB.FavoriteCastaway
    Debug.Print "wbThatWB.FavoriteHillbilly -->"; wbThatWB.FavoriteHillbilly
    '// works using long form
    Debug.Print "wbThatWB.FavoriteCastaway -->"; wbThatWB.IclsInterfaceTest_FavoriteCastaway
    Debug.Print "wbThatWB.FavoriteHillbilly -->"; wbThatWB.IclsInterfaceTest_FavoriteHillbilly
    strStep = "objThatWB"
    Set objThatWB = Workbooks(s)
    Debug.Print "That (object) workbook name: "; objThatWB.Name
    '// raises object does not support error
    Debug.Print "ObjThatWB.FavoriteCastaway -->"; objThatWB.FavoriteCastaway
    Debug.Print "ObjThatWB.FavoriteHillbilly -->"; objThatWB.FavoriteHillbilly
    '// works using long form
    Debug.Print "ObjThatWB.FavoriteCastaway -->"; objThatWB.IclsInterfaceTest_FavoriteCastaway
    Debug.Print "ObjThatWB.FavoriteHillbilly -->"; objThatWB.IclsInterfaceTest_FavoriteHillbilly
    strStep = "ifcthatWB"
    '// raises type mismatch error
    Set ifcThatWB = Workbooks(s)
    '// raises object not set (due to mismatch error)
    Debug.Print "ifcThatWB.FavoriteCastaway -->"; ifcThatWB.FavoriteCastaway
    Debug.Print "ifcThatWB.FavoriteHillbilly -->"; ifcThatWB.FavoriteHillbilly
    '// won't compile
    'Debug.Print "ifcThatWB.FavoriteCastaway -->"; ifcThatWB.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "ifcThatWB.FavoriteHillbilly -->"; ifcThatWB.IclsInterfaceTest_FavoriteHillbilly
 
    '// __worksheet located outside project___
 
    strStep = "that worksheet"
    Set wsThatSheet1 = wbThatWB.Sheets(1)
    Debug.Print strStep; "..."; wsThatSheet1.Name, "code = "; wsThatSheet1.CodeName
    '// won't compile
    'Debug.Print "wsThatSheet1.FavoriteCastaway -->"; wsThatSheet1.FavoriteCastaway
    'Debug.Print "wsThatSheet1.FavoriteHillbilly -->"; wsThatSheet1.FavoriteHillbilly
    '// won't compile
    'Debug.Print "wsThatSheet1.FavoriteCastaway -->"; wsThatSheet1.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "wsThatSheet1.FavoriteHillbilly -->"; wsThatSheet1.IclsInterfaceTest_FavoriteHillbilly
 
    strStep = "objThatSheet"
    Set objThatSheet1 = wsThatSheet1
    Debug.Print strStep; "..."; objThatSheet1.Name
    '// raises object does not support error
    Debug.Print "objThatSheet1.FavoriteCastaway -->"; objThatSheet1.FavoriteCastaway
    Debug.Print "objThatSheet1.FavoriteHillbilly -->"; objThatSheet1.FavoriteHillbilly
    '// works using long form
    Debug.Print "objThatSheet1.FavoriteCastaway -->"; objThatSheet1.IclsInterfaceTest_FavoriteCastaway
    Debug.Print "objThatSheet1.FavoriteHillbilly -->"; objThatSheet1.IclsInterfaceTest_FavoriteHillbilly
    strStep = "ifcSheet1"
    '// works if inside project.  but if outside
    '// project then raises type mismatch error
    Set ifcThatSheet1 = objThatSheet1
    '// raises object not set (due to mismatch error)
    Debug.Print "ifcThatSheet1.FavoriteCastaway -->"; ifcThatSheet1.FavoriteCastaway
    Debug.Print "ifcThatSheet1.FavoriteHillbilly -->"; ifcThatSheet1.FavoriteHillbilly
    '// won't compile
    'Debug.Print "ifcSheet1.FavoriteCastaway -->"; ifcSheet1.IclsInterfaceTest_FavoriteCastaway
    'Debug.Print "ifcSheet1.FavoriteHillbilly -->"; ifcSheet1.IclsInterfaceTest_FavoriteHillbilly
 
    Exit Sub
ErrorHandler:
    Debug.Print strStep & ": Err #:" & Err.Number & ", " & Err.Description
 
    Resume Next
End Sub

I will try to post a matrix that will summarize this in another (I fear HTML is turned off in this forum)

It would have been beautiful if the outside-of-project use of the "interface object" would have worked as well as it does inside the project. Unfortunately this is not the case. What I found most puzzling was the difference in behaviour of the Worksheet object versus the Workbook object. A WB object will still allow one to access the properties using long form (ugly, but OK). However it won't even compile on a worksheet.

If anyone knows of a way to get the interface that is implemented in an out-of-project workbook to be callable using "short form" PLEASE let me know! Otherwise, anyone that is daft enough to test all this - let me know if your results mimic my own.

 

[Greg Truby]

HTML is disable in this forum, so posted the results matrix in the main forum:

http://www.mrexcel.com/forum/showthread.php?t=338318