Of Interface Implementation and Workbook Modules

Greg Truby

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,030
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)
Code: 
'...
    ' __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
Code: 
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
Code: 
'// 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
Code: 
'// 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
Code: 
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
Code: 
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
Code: 
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
Code: 
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
Code: 
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.
 
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

B
  • Solved
Check if a code module exists in the Global.MPT instead of the ActiveProject does not work
2
Replies
12
Views
298
Artik
Artik
J
  • Question Question
Create protected PDFs (with an Open password and/or a Permissions password) with Adobe PDF Services API
Replies
0
Views
146
John_w
J
Jaafar Tribak
  • Question Question
Subclassing without crashing - (Testers required.)
2 3
Replies
22
Views
946
Jaafar Tribak
Jaafar Tribak
Jaafar Tribak
  • Question Question
Referencing all Running Instances Of Excel, Word and Access
2 3 4
Replies
32
Views
2K
Jaafar Tribak
Jaafar Tribak
Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
1
Views
583
Bib195
B

Forum statistics

Threads
1,226,795
Messages
6,193,046
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top