Passage par macro d'une matrice d'objets de VB.NET vers Excel VBA

**__bv__** · 07/11/2018, 10h33

Bonjour,

J'ai un projet VB.NET qui attache une feuille Excel. Dans le projet VB, j'utilise une classe perso avec une vingtaine de membres de données essentiellement numériques.
Un tableau dynamique d'objets de cette classe est instanciée dans VB.NET, j'y fais un bon nombre de calculs.
Pour m'assurer le passage de données vers Excel, j'ai copié la définition de ma Classe dans un autre projet de type Class Library, et j'ai ajouté une Référence à la feuille Excel (XLSM) pour pouvoir l'utiliser. J'ai d'abord pensé faire un copié-collé de la définition de la Classe dans VBA mais ça ne fonctionne pas.
Dans le programme VB.NET, je passe un 'pointeur' à Excel grace à une macro, et la macro récupère bien les données.

Toutefois, après plusieurs essais, il s'avère que je n'arrive à passer les données que si le type de l'objet (la matrice) n'est pas définie dans la macro (donc Variant).
Comme il me semble qu'il n'y pas de 'typecasting' dynamique en VBA, je ne sais pas comment faire mieux.
Bref, j'ai mes données mais je n'ai pas accès aux membres de la classe pourtant correctement définie (enfin je pense...), et ma question est la suivante:
Comment faire la même chose plus 'proprement', c'est à dire, pouvoir retrouver la classe sous VBA comme je l'utilise en VB.
Merci d'avance
Bruno

**MABROUKI** · 08/11/2018, 14h00

bonkour

Il faut que ton class soit defini comme un Class Com donc visible en Com pour qu'il soit référencé par VBA (outils-> ajouter une référencé )

sur ce lien intitulé "Walkthrough: Creating COM Objects with Visual Basic | Microsoft Docs" (creation pas à pas d"un Com Class)

https://docs.microsoft.com/en-us/dot...ng-com-objects
bon code...

**Phil Rob** · 08/11/2018, 14h03

Je crois que la vraie question est : Comment référencer dans VBA Excel la dll écrite et compilée sous VS ?
Il semble simple d'ajouter une référence (Outils/Références.../Parcourir ...),
mais le message en retour est "Impossible d'ajouter une référence au fichier spécifié" !
Je veux bien la réponse ...

**__bv__** · 08/11/2018, 15h12

Je ne me suis sans doute pas exprimé clairement dans mon premier message.
Tout fonctionne plus ou moins comme je le voulais:
La classe (importée dans mon projet a été compilée de manière séparée en COM et j'y accède dans Excel après l'avoir ajoutée en référence.
Le passage de données par macro marche parfaitement. La seule chose est que la macro reçoit la matrice en référence comme une variable non définie même si lorsque je teste avec TypeName elle semble bien être du type la classe.
Je peux accéder à toutes les méthodes mais VBA ne reconnait pas de facto le type. Il faut donc connaître la structure de la classe pour l'utiliser dans VBA.

**Phil Rob** · 08/11/2018, 16h27

OK, alors tout va bien chez toi (ou presque) et c'est chez moi que ça ne va pas cette fois : Comment fais-tu pour référencer ta DLL dans le projet VBA ?
Je conçois que tu doives bien connaître ta classe pour pouvoir l'utiliser et je voudrais tester, mais je n'ai pas accès à ma DLL !
Voici mon problème en quelques illustrations :
Nom : DLLXLS1.jpg
Affichages : 49
Taille : 56,9 Ko

Nom : DLLXLS1.jpg
Affichages : 49
Taille : 56,9 Ko

Nom : DLLXLS2.jpg
Affichages : 48
Taille : 18,7 Ko

Si tu as de quoi me dépanner ...

**__bv__** · 08/11/2018, 23h51

As tu compilé ta Class Library (DLL) avec l'option Com Interop ?
https://www.screencast.com/t/EyflS9VWucZ
Ensuite dans Excel VBA, il suffit de la référencer avec le fichier TLB.

**rv26t** · 09/11/2018, 00h20

Bonjour,

Je ne sais pas si cela pourra t'aider, mais voici en lien deux sujets traitant de ce genre de problèmatique.
Echange d'info vb.net vb6 - post 27.
et
exe vb6 et dll vb.net - post 10.
Tu trouvera peut être des similitudes avec vba ou des pistes.

A+

**Phil Rob** · 09/11/2018, 08h27

Merci Bruno, merci rv,
je vais travailler un peu ces pistes.
Très bonne journée à vous ...

Suite ...

@Bruno
Ta copie d'écran super explicite m'a permis de résoudre le référencement de ma dll dans Excel.
Effectivement, il faut connaître la classe pour pouvoir l'exploiter dans Excel.
J'ai tenté la documentation xml du code de la DLL mais Excel n'y voit rien.
...

Nom : DLLXLS3.jpg
Affichages : 45
Taille : 21,0 Ko

**__bv__** · 09/11/2018, 09h55

Hervé, j'ai parcouru les liens et je me pose une question sur le même sujet (pourquoi je n'ai pas accès aux 'accessors' dans VBA):
Pourrait il y avoir une non-équivalence (genre Integer dans VB.NET et Long en VBA) qui troublerait VBA et empêcherait la bonne interprétation de la classe même si par le casting dynamique de ces types de base, tout marche en fin de compte ?

**__bv__** · 09/11/2018, 13h06

Phil,
Je n'aurais jamais pensé à toucher au XML ! Comme mentionné dans mon dernier message, est ce que ça pourrait être dû à une correspondance imparfaite des types ?

J'ai encore une question subsidiaire.
Ma matrice arrive donc bien dans VBA et les calculs vont bon train mais c'est toujours l'affichage dans Excel est qui reste très lent.
Je code plusieurs timers en VBA pour afficher les données avec des priorités différentes, par exemple MaMatrice(i).DonnéeUrgente sur un Timer 1 seconde, et MaMatrice(i).DonnéeMoinsUrgente sur un timer 5 secondes etc, "i" pouvant couvrir des centaines d'enregistrements.
Quelle serait la méthode la plus efficace pour copier le contenu complet d'un champ sur une colonne d'une feuille Excel ?

**Phil Rob** · 09/11/2018, 14h03

A mon avis, une boucle VB qui balaye un Range de cellule Excel sera la solution la plus rapide.
Je pense d'ailleurs que, hormis quelques opérations très exceptionnelles, tout devrait être fait sous VB et Excel ne devrait plus servir que d'interface d'affichage.
...

**__bv__** · 09/11/2018, 14h35

En fait je crée un serveur de données avec des calculs fondamentaux assez lourds en VB.NET mais je veux laisser une certaine flexibilité à l'utilisateur côté Excel.
Quoiqu'il en soit, c'est bien l'affichage qui me préoccupe ici.
Pour le moment, j'en reste à une version avec des Timers pour limiter la fréquence d'affichage. Quand j'affiche tout en vrac, cela prend presque une seconde (proc i7 relativement rapide et 16GB RAM) pour afficher le tableau complet (30 membres, 250 enregistrements) et je trouve ça lent sachant qu'en mode "production" il pourra y avoir au moins quelques milliers d'enregistrements.
L'autre solution serait de faire appel à la macro de transfert vers Excel sur des variations de données ponctuelles et avoir peut-être moins à afficher à chaque fois.

**MABROUKI** · 09/11/2018, 16h14

bonjour

C'est un faux problème que tu soulèves là ....!!!
Pour utiliser l'intellisense en VBA et accéder aux props et méthodes il suffit de
1/ déclarer ton class Perso comme un class Com
2/ le class Wrapper qui utilise un array fu class Perso.
2/ de les referencer en VBA

Supposons pour fixer les idées et ne pas débiter des neutralistes confuses & sans fin
le code suivant
1/ coté vb.net :
'code du class PersoNet

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
Public Class PersoNet
    Private rnd As New Random
    Public Sub New()
        MyBase.New()
    End Sub
    Public Sub New(ByVal N As Integer)
        Me.New()
 
        IntArr = New Integer(N) {}
        DoubleArr = New Double(N) {}
        StrArr = New String(N) {}
 
        FillArrays(N)
    End Sub
    Private Sub FillArrays(ByVal NB As Integer)
        For i As Integer = 0 To NB - 1
            IntArr(i) = rnd.Next(1, 51)
            DoubleArr(i) = Math.Round(rnd.NextDouble() * 100, 2)
            StrArr(i) = rnd.Next(1, 51)
        Next
    End Sub
 
    Private m_IntArr As Integer()
    <ComVisible(True)>
    Public Property IntArr As Integer()
        Get
            Return m_IntArr
        End Get
        Set(ByVal value As Integer())
            m_IntArr = value
        End Set
    End Property
    Private m_DoubleArr As Double()
    <ComVisible(True)>
    Public Property DoubleArr As Double()
        Get
            Return m_DoubleArr
        End Get
        Set(ByVal value As Double())
            m_DoubleArr = value
        End Set
    End Property
    Private m_StrArr As String()
    <ComVisible(True)>
    Public Property StrArr As String()
        Get
            Return m_StrArr
        End Get
        Set(ByVal value As String())
            m_StrArr = value
        End Set
    End Property
End Class

code du class Wrapper declarant un array du class PersoNet

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Public Class ListWrapper
    Public Sub New()
        MyBase.New()
        UnList = New PersoNet(1) {
		New PersoNet(5), 
		New PersoNet(7)}
 
    End Sub
    Private m_unList() As PersoNet
    <ComVisible(True)>
    Public Property UnList As PersoNet()
        Get
            Return m_unList
        End Get
        Set(ByVal value As PersoNet())
            m_unList = value
        End Set
    End Property
End Class

2/ cote vba on peut ecrire le code sauvant avec l'intellisense à merveille:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
Private SNC  As LibClass.SimpleNetClass
Private WRAPPER   As LibClass.ListWrapper
Private Sub UserForm_Initialize()
	Call Test
End Sub
Private Sub Test()
    Dim TheInts() As Long
    Dim TheDoubles() As Double
    Dim TheStrings() As String
 
    Set WRAPPER = New ListWrapper
    Dim counter As Integer
    counter = 1
    Worksheets(1).Cells(counter, 1).Value = "instance 1"
    For Each item In WRAPPER.Unlist
        Set SNC = item
        TheInts = SNC.IntArr
        TheDoubles = SNC.DoubleArr
        TheStrings = SNC.StrArr
 
		Dim sh As Worksheet
		Set sh = Worksheets(1)
        For J = LBound(TheInts) To UBound(TheInts)
 
            sh.Cells(counter + 2, J + 1).Value = TheInts(J)
            sh.Cells(counter + 3, J + 1).Value = TheDoubles(J)
            sh.Cells(counter + 4, J + 1).Value = TheStrings(J)
         Next
         counter = 8
         Worksheets(1).Cells(counter, 1).Value = "instance 2"
    Next
End Sub

nota bene :
En Com qui est caché derrière VBA et même VB6
- tous les types valeurs de .Net son reconnus (integer;double,boolean,byte etc...)
- le type string de longueur variable
- les tableaux des types valeurs .Net ci-dessus quand ils sont déclares explicitement et même les tableaux d'objets divers Les tableaux sont castes en VBA zt VB6 en Variant

**Phil Rob** · 09/11/2018, 16h50

...j'en reste à une version avec des Timers pour limiter la fréquence d'affichage...

OK, mais une intervention par seconde est beaucoup, surtout quand tu nous dis que l'affichage prends ± 1 seconde.
Vu la "rapidité" de réaction de l'utilisateur, une intervention toutes les 2 secondes serait encore bien suffisant.
Mais n'y a-t-il pas moyen de se passer de ces Timer qui "provoquent" (si je comprends bien) un affichage à interval régulier (même s'il n'y a rien de neuf à afficher) ?
Ne peux-tu rafraîchir l'affichage sur base d'une action de l'utilisateur ? Par exemple le clic d'une cellule qui a été modifiée entre temps, ou l'exécution d'une macro particulière, ou même une demande explicite de rafraichissement par l'utilisateur lui-même ?

**__bv__** · 09/11/2018, 18h41

Phil, demander à l'utilisateur d'actualiser manuellement n'est pas envisageable. Même si un Timer n'est pas une très bonne solution, je continue dans cette voie.
J'ai vu que j'ai de temps à autres des problèmes lorsque le prog VB.net agit sur la matrice en même temps que VBA tourne mais cela n'est pas bloquant.

Mabrouki, merci, je ne connaissais pas cette solution par Wrapper. Je vais essayer !!!

**Phil Rob** · 10/11/2018, 09h17

Bonjour Bruno,

Tu ne veux pas que l'utilisateur demande lui-même le rafraichissement : OK. Mais tu peux peut-être décider à sa place quand il faut le faire ...
Si tu déclares dans VB ton instance d'Excel avec WithEvents, ton application VB peut avoir connaissance des événements générés par les activités de l'utilisateur et donc, agir en conséquence ... (cf. l'illustration ci-dessous).
C'est juste une idée ...

Je vais aussi m'intéresser aux Wrappers ... VOIR LA SUITE CI-DESSOUS !!!

Bonne journée,

Nom : DLLXLS4.jpg
Affichages : 41
Taille : 81,7 Ko

SUITE ..............

Voici le code de ma dll (simpliste, pour l'exemple) :
A noter d'abord que les 2 première lignes

Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)>

ont été fournies dans une discussion par Mabrouki le 18/1/2011 ! Merci à lui.
Sans ces 2 lignes, ma dll était exploitable sous VBA à condition de bien la connaître ...

Avec ces lignes, on dispose de toute l'aide nécessaire.
Nom : DLLXLS5.jpg
Affichages : 37
Taille : 32,7 Ko

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)>
Public Class UnNombre
    Private Nombre As Integer
 
    Public Sub New()
        MyBase.New()
        Nombre = 0
    End Sub
 
    Public Sub New(ByVal N As Integer)
        MyBase.New()
        Nombre = N
    End Sub
 
    Public Property LeNombre() As Integer
        Get
            Return Nombre
        End Get
        Set(ByVal N As Integer)
            Nombre = N
        End Set
    End Property
 
    Public Function DonneNombre() As Integer
        Return Nombre
    End Function
 
    Public Sub PrendNombre(ByVal N As Integer)
        Nombre = N
    End Sub
 
End Class

**__bv__** · 15/11/2018, 20h05

Mabrouki,
Dans ton exemple la Classe Wrapper est elle indispensable ? J'arrive à peu près à ce que je veux avec juste ma Classe compilée en COM avec l'interface.
Par ailleurs faut il dans Excel VBA faire du nettoyage au moment de la fermeture de la feuille Excel ?
Merci d'avance
Bruno

**MABROUKI** · 15/11/2018, 21h56

Rebonjour

la Classe Wrapper n'est necessaire que si tu veux transmettre -carrement- un tableau de ton class Perso lui meme et non une instance ...
Si tes tableaux sont des types valeur (int32,double ,string etc...) ,tu n'as besoin de transmettre q 'une instance de ton class Perso..
bon code...

**__bv__** · 16/11/2018, 10h48

Hello Phil,
Je n'arrive toujours pas à résoudre ce problème avec le Designer, et pourtant il me serait utile de pouvoir au moins connaître dans VB.NET si l'utilisateur Excel a fermé le spreadsheet.
Je vais t'envoyer une version expurgée de mon projet ce weekend, si ça te dit toujours d'y jeter un oeil (ou les deux

).
J'ai par ailleurs parfois des crashs dans Excel que je ne comprends pas vraiment. J'ai d'abord pensé que cela pouvait être dû à une incompatibilité de type mais je n'en suis pas sûr.
Il me semble que Integer dans VB.NET est l'équivalent de Long en VBA / VB6 (je ne teste pas encore si le client Excel est de niveau VB6 ou VB7).
Quoiqu'il en soit le type est inscrit dans la COM DLL et je ne sais pas comment VBA ensuite l'interprète.

Merci par avance.
Bruno

**Phil Rob** · 16/11/2018, 19h05

Bonsoir Bruno,

Pour ce qui est diu Wrapper, si tu veux revoir mon message du 10/11 à 9h17' (#16), j'ai seulement ajouté les 2 premières lignes (de Mabrouki, 2011) à ma classe pour recompiler la dll et la référencer à nouveau dans XLS. Je ne pense donc pas que le Wrapper soit autrement nécessaire sauf erreur de ma part, la question initiale était d'obtenir l'Intellisense pour l'usage de la DLL das VBA XLS.

Je ne crois pas que tes problèmes avec le Designer viennent de tes relations avec Excel. Et je ne crois pas non plus aux différences de typ. En fait, des plantages du Designer, j'en ai eu dans le passé, sans être en programmation avec Office. Je n'ai jamais constaté (ni cherché) une explication correcte du phénomène (j'aurais dû

).
Je soupçonne toujours les changements accidentels de la plateforme (gestionnaire de configurations). En faisant quelques tests le week-end dernier, j'ai obtenu un plantage du Designer et de fait, ma plateforme avait changé, mais c'est événement était fortuit et le pis, c'est que je n'ai pas pu reproduire le problème !!!

Et puis, comme je te l'ai communiqué dans un autre message, j'ai eu des problèmes avec l'interopérabilité () uniquement parce que je lançais VS en tant qu'administrateur. Note au passage que j'ai dû travailler en Administrateur pour mener à bien mes tests avec la DLL. Que se passe-t-il chez toi si tu lance VS en tant qu'administrateur ?

Pour ce qui est de l'événement je t'ai également transmis mon test Private Sub XLSAPP_WorkbookBeforeSave(... ) sur une autre de nos discussions. Cela fonctionne très bien !

Enfin, je dois bien reconnaître que je suis aussi démuni que toi face au problème du Designer ...

Mais je connais un outil sûr pour planter le Designer : NotePad. L'éditeur VS convient aussi. Vois la petite vidéo que je t'ai placé sur Dropbox : https://www.dropbox.com/sh/vkrepy2b0...x8M6yO4Ia?dl=0
Cela te donnera peut-être l'idée de quoi chercher à corriger dans le fichier .Designer ... , mais ça ramène à ce que j'ai déjà exprimé : corriger le fichier .Designer c'est pas gagné ! Dans la vidéo c'est facile parce que j'ai commis l'erreur expressément.

Je chercher encore ...