3

I have been running into type errors when casting worksheets that implement an interface of my own design into objects of the same type as the interface. Whilst these are not 100% reliable (sometimes the cast works) I have not been able to identify any rhyme or reason behind the situations when it does or does not.

The issue persists in a simplified environment, and can be seen clearly by following the below steps (at least in Excel 2010):

Create a new workbook, add a class module, make it public-non-createable and call it ITest, create a standard module called Tools (although I believe this could have any name) and paste the following into the code modules for ITest, Tools, and Sheet1 respectively:

ITest:

Option Explicit

Public Function Test() As Boolean

End Function

Tools:

Option Explicit

Public Function IsRangeInSheetImplementingTest(rngIn As Excel.Range) As Boolean
    Dim oWks As Object
    Dim oITest As ITest

    On Error Resume Next
    Set oWks = rngIn.Worksheet
    Set oITest = oWks
    If Err.Number <> 0 Then
        IsRangeInSheetImplementingTest = False
    Else
        IsRangeInSheetImplementingTest = True
    End If

End Function

Sheet1:

Option Explicit

Implements ITest


Public Function ITest_Test() As Boolean
    ITest_Test = True
End Function

You should find that calling IsRangeInSheetImplementingTest(Range("A1")) returns False. Commenting out On Error Resume Next demonstrates that this is due to a Type Mismatch on the line Set oITest = oWks. Can anyone tell me (1) why is this happening and (2) what can I do to make the interface get recognised properly?

As a side note, sometimes placing the same call in a cell within the workbook gives the correct result - but only sometimes. Very confused as to what's going on here - any help very gratefully appreciated!

tobriand
  • 1,095
  • 15
  • 29
  • 1
    I don't use interfaces much, but I think you can't set an object to an interface, you can only implement it. What is it you are trying to do, more generally? – Doug Glancy Sep 09 '13 at 14:13
  • 1
    You can definitely set an object to an interface - it's largely the point of them, in fact (although I've been known to use them for other things too). To test this, create a class (say, `CTest`) paste in the Sheet1 code, and then in a subroutine recreate the following: `Dim oCTest as New CTest : Dim oITest as ITest : Set oITest = oCTest` and run it. It'll execute with no errors. The problem is whether there are any specific rules for built in object types - and what they are! – tobriand Sep 09 '13 at 14:45
  • You aren't qualifying the range of your input here: `IsRangeInSheetImplementingTest(Range("A1"))`, so this is actually `IsRangeInSheetImplementingTest(ActiveSheet.Range("A1"))`, does the activesheet implement the interface? `IsRangeInSheetImplementingTest(Sheet1.Range("A1"))` worked fine for me – SWa Sep 09 '13 at 15:00
  • It looks like this does work. Obviously, the example is a microcosm of what I'm trying to do; I thought it was representative; it looks like it isn't. In the live example, I'm also having trouble getting at the native workbook methods as a result of the implemented interface (e.g. wks.name). I don't think qualification is the only issue, though, since inserting a formula in sheet1 of the form `=IsRangeInSheetImplementingTest(A1)` begins by returning false (changes to true if you access the object directly from the immediate window, e.g. `debug.print Sheet1.Name`) – tobriand Sep 09 '13 at 15:24
  • Well that's kind of one of the points of an interface - that you can't get at the members of the class that aren't in the interface - it provides a consistent interface regardless of the class's underlying properties and methods. – SWa Sep 09 '13 at 15:45
  • Ok, found a method to fix the issue in my live workbook, meaning in this example, the lack of qualification most likely was the issue. However, I'm still interested, if anyone has an answer, why the UDF doesn't work. @Kyle if you re-post your comment as an answer, I'll accept it as an answer for the original question... – tobriand Sep 09 '13 at 15:50
  • @Kyle: Not exactly what I mean. The UDF would take the Worksheet property of A1 and assign it to the oITest object, and return True if the interface was working. This will initially be false. However, if you then go into the Immediate window and type `?Sheet1.Name`, and recalculate, the formula will then return true. In either case, though, I'm not trying to access properties or methods of the object that are inconsistent with the interface it's currently being treated as. Any ideas why would be very much of interest. – tobriand Sep 09 '13 at 15:56
  • [This recent question](http://stackoverflow.com/q/18500674/1048539) and the comments there indicate worksheets/implements don't get along well in VBA. Might be relevant to your question. – enderland Sep 09 '13 at 18:46
  • @enderland: Thanks - really interesting reading. Eventually managed to solve it (for now); somehow the worksheet object has become corrupt, such that e.g. ?Sheet1.Name caused an error. Copying all code out, saving, closing excel, re-opening and replacing the code fixed the issue - but I would not be surprised if it recurs at some point (hope not though). – tobriand Sep 17 '13 at 11:14
  • I would suggest read this: [Office 2000: Interface Basics](http://msdn.microsoft.com/en-us/library/office/aa165323%28v=office.10%29.aspx) and follow the links on the left side of window. – Maciej Los Jan 08 '14 at 17:41

1 Answers1

2

I can't answer the first part of your question (the "why") but after struggling with this myself, I've found a way around it.

I've built a Connect function in each of the worksheet modules that I use to implement an interface (in your example, ITest). Then I use it to set to an instance of ITest later.

The Connect function is very easy:

' Place in the worksheet that implements the ITest interface
Public Function Connect() As ITest
  Set Connect = Me
End Function

In your IsRangeInSheetImplementingTest replace

Set oWks = rngIn.Worksheet

with

Set oWks = rngIn.Worksheet.Parent.Sheets(rngIn.Worksheet.Name).Connect

It is UGLY, but it works every time.

dweedul
  • 61
  • 4
  • 2
    That is CUNNING! I actually ended up abandoning the idea because of a slightly different reason (essentially, interfaces on worksheets are prone to periodic workbook corruption, even when they're valid code - this sucks). That said, though, I might be able to explain the why give how you get to the object. Crucial thing is that COM seems to sacrifice interfaces as it binds, so an object dimmed as Object has all interfaces when querying IUnknown, but if you pass it to a typed entity and back, it loses all but those associated with the type. And the Sheets collection returns Object objects! – tobriand Apr 11 '14 at 14:42