Databases
StackOverflow2013
Postsdbo
POHow can I cast a worksheet that implements and interface into an object of the type of the interface reliably?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I cast a worksheet that implements and interface into an object of the type of the interface reliably?
    text
    copied!<p>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.</p> <p>The issue persists in a simplified environment, and can be seen clearly by following the below steps (at least in Excel 2010):</p> <p>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:</p> <p>ITest:</p> <pre><code>Option Explicit Public Function Test() As Boolean End Function </code></pre> <p>Tools:</p> <pre><code>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 &lt;&gt; 0 Then IsRangeInSheetImplementingTest = False Else IsRangeInSheetImplementingTest = True End If End Function </code></pre> <p>Sheet1:</p> <pre><code>Option Explicit Implements ITest Public Function ITest_Test() As Boolean ITest_Test = True End Function </code></pre> <p>You should find that calling <code>IsRangeInSheetImplementingTest(Range("A1"))</code> returns <code>False</code>. Commenting out <code>On Error Resume Next</code> demonstrates that this is due to a <code>Type Mismatch</code> on the line <code>Set oITest = oWks</code>. Can anyone tell me (1) why is this happening and (2) what can I do to make the interface get recognised properly?</p> <p>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!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload