Comment puis-je convertir de manière fiable une feuille de calcul qui implémente une interface en un objet du type de l'interface ?

Question

Je rencontre des erreurs de type lors de la conversion des feuilles de calcul qui implémentent une interface de ma propre conception en objets du même type que l'interface. Bien que cela ne soit pas fiable à 100 % (parfois la conversion fonctionne), je n'ai pas été capable d'identifier de motif derrière les situations où cela fonctionne ou non.

Le problème persiste dans un environnement simplifié, et peut être clairement vu en suivant les étapes ci-dessous (au moins dans Excel 2010) :

Créez un nouveau classeur, ajoutez un module de classe, rendez-le public-non-créable et appelez-le ITest, créez un module standard appelé Outils (bien que je crois que cela pourrait avoir n'importe quel nom) et collez ce qui suit dans les modules de code pour ITest, Outils et Feuil1 respectivement :

ITest :

Option Explicit

Public Function Test() As Boolean

End Function

Outils :

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

Feuil1 :

Option Explicit

Implémente ITest

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

Vous devriez constater que l'appel de IsRangeInSheetImplementingTest(Range("A1")) renvoie False. En commentant On Error Resume Next, on démontre que cela est dû à une Erreur de type sur la ligne Set oITest = oWks. Est-ce que quelqu'un peut me dire (1) pourquoi cela se produit et (2) que puis-je faire pour que l'interface soit correctement reconnue ?

En passant, parfois, appeler la même fonction dans une cellule du classeur donne le résultat correct - mais seulement parfois. Très confus quant à ce qu'il se passe ici - toute aide serait très appréciée !

Answer 1

Je ne peux pas répondre à la première partie de votre question (le "pourquoi") mais après avoir moi-même lutté avec cela, j'ai trouvé une solution pour contourner le problème.

J'ai créé une fonction Connect dans chacun des modules de feuille de calcul que j'utilise pour implémenter une interface (dans votre exemple, ITest). Ensuite, je l'utilise pour définir plus tard une instance de ITest.

La fonction Connect est très simple:

' Placez dans la feuille de calcul qui implémente l'interface ITest
Public Function Connect() As ITest
  Set Connect = Me
End Function

Dans votre IsRangeInSheetImplementingTest remplacez

Set oWks = rngIn.Worksheet

avec

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

C'est LAID, mais ça fonctionne à chaque fois.