Création de notre premier complément XLA

Notions avancées !

Pour suivre cet article et le trouver intéressant, une bonne aisance avec VBA est indispensable

Les macros (Sub et Function) peuvent se placer dans le classeur actuel, ou dans Perso.XLS, afin de les avoir à disposition tout le temps. Perso.XLS est systématiquement chargé à chaque démarrage d'Excel, mais il existe une autre technique, ce sont les compléments. Les compléments sont des classeurs Excel masqués qui peuvent contenir des UserForms, des modules dans lesquels on peut avoir des prodécures et des fonctions.

Les programmeurs d'Excel, chez Microsoft, ont décidé de ne pas mettre à disposition tous les outils d'Excel directement dans les menus. En effet, s'ils l'avaient fait, Excel mettrait 10 minutes à se lancer.

Une partie des fonctionnalités d'Excel se trouvent dans des compléments (*.XLA - eXceL Additive). Par exemple, le solveur, outil qui se trouvait autrefois par défaut dans le menu Outils d'Excel, n'est maintenant plus accessible qu'au travers du complément Solver.XLA. Ce qui est une bonne chose, car qui utilise cet outils, a part quelques scientifiques très pointus ? (Le solveur est un outil mathématique permettant de résoudre des équations à plusieurs inconnues, mais le sujet de cet article n'est pas d'apprendre à l'utiliser)

Actuellement, lorsque vous allez dans le menu Outils, vous constatez que "Solveur" n'est pas présent.

Marche à suivre pour l'avoir a disposition :

1. Menu Outils/Macros complémentaires (Disponible seulement si vous avez un classeur ouvert)
2. Cochez la case "Complément solveur"
3. OK

Maintenant, lorsque vous cliquez sur le menu Outils, vous trouverez le sous-menu Solveur.

Si vous quittez Excel, et que vous le rouvrez, le solveur sera toujours a disposition sans avoir besoin de nouveau de cocher la case.

Vous constatez qu'il y a plein de compléments dans Outils/Macros complémentaires. Ce qu'il y a de bête, c'est que ce n'est pas la peine d'appuyer sur F1 en sélectionannt une macro complémentaire, l'aide d'Excel ne renseigne pas sur son contenu !

Par exemple, j'ai appris que pour arrondir un montant à 5 centimes à l'aide d'une fonction, les fonctions ARRONDI ne peuvent pas le faire. Mais il existe une fonction ARRONDI.AU.MULTIPLE qui permet de le faire facilement. Cette fonction n'apparaîtra dans la liste des fonctions que si vous cochez la macro complémentaire Utilitaires d'analyse.

C'est parti !

IMPORTANT : Suivez les instructions suivantes le plus précisément possible pour bien comprendre ! Commencez par carrément fermer Excel pour être certain de commencer sans rien d'ouvert dans Excel.

Nous allons créer une macro Soleil qui va simplement afficher "Il fait beau", qui va se trouver dans un module d'un complément CompTest.XLA.

1. Lancez Excel
2. Vous avez un classeur vierge sous les yeux : "Classeur1"
3. Allez dans VBA
4. Créez un nouveau module dans ce classeur
5. Créez la macro suivante :
   Sub Soleil()
      MsgBox "Il fait beau"
   End Sub
6. Revenez dans Excel
7. Allez dans le menu Outils/Macros/Macros
8. (Constatez que Soleil est présent dans la liste des macros)
9. Exécutez-là
10. Allez dans le menu Fichier/Enregistrer
11. Comme nom de fichier écrivez CompTest
12. Type de fichier : Choisissez "Macro complémentaire Microsoft Office Excel" (Tout en bas)
13. Constatez que le chemin d'accès en haut de la boîte de dialogue vous redirige vers l'emplacement par défaut des macros complémentaires créées par les utilisateurs :
    C:\Documents and Settings\VotreNom\Application Data\Microsoft\Macros complémentaires
14. Cliquez sur Enregistrer
    C'est ici qu'il faut bien suivre !
15. Fermez "Classeur1" - car c'est bien Classeur1 qui est sur votre écran : Ca ferme votre complément CompTest.XLA (Oui je sais c'est bizarre)
16. Vous êtes dans votre Excel tout gris
17. Menu Fichier : dans la liste des derniers fichiers utilisés, il y a CompTest.XLA. Cliquez dessus pour l'ouvrir
18. Constatez que le classeur est masqué, comme s'il n'avait rien ouvert - Et si vous allez dans le menu Fenêtre/Afficher, vous constaterez que même là vous ne pouvez pas afficher ComptTest.XLA.
19. Allez dans le menu Outils/Macros
20. Constatez que très étrangement, la macro Soleil n'existe pas. MAIS : Dans la zone "Nom de la macro", tout en haut, écrivez soleil, et cliquez sur exécuter : Il l'exécute !!! La macro est bel et bien là, mais le classeur et ses macros sont fortement cachés.
21. Allez dans VBA, constatez que CompTest.XLA est bien actif, et sa macro soleil bien visible.
22. Revenez dans Excel
23. Quittez Excel
24. Rouvrez Excel
25. Nous sommes bien d'accord que maintenant, Soleil n'est plus disponible. Testez (Outils/Macros/macros, Ecrivez Soleil et Exécuter) : Il ne donne pas d'erreur, mais il croit que vous voulez créer une macro Soleil dans un module de ce nouveau classeur qui est apparu par défaut à l'ouverture d'Excel
26. Re-quittez Excel, et Rouvrez le encore une fois, ne sauvegardez rien du tout
27. Dans la pratique, il ne s'agit pas d'ouvrir le complément CompTest.XLA de manière traditionnelle, mais comme ceci :
28. Allez dans le menu Outils/Macros complémentaires
29. Cochez la case CompTest.XLA. Comment sait-il que vous avez précédemment créé ce complément ? Parce que à l'étape 13, il vous a "imposé" un chemin d'accès vers lequel Excel sait que des macros complémentaires des utilisateurs se trouvent.
30. OK
31. A ce stade, à chaque démarrage d'Excel, CompTest.XLA sera chargé à chaque démarrage d'Excel, mais le classeur sera masqué (En fait, un .XLA possède-t-il même un classeur ?) Et les macros seront disponibles, mais invisibles dans Outils/Macros/Macros. Pour tester, maintenant que la case est cochée :
32. Fermez et rouvrez Excel
33. Menu Outils/Macros/Macros. Ecrivez Soleil et cliquez sur Exécuter : La macro s'exécute

Evolution du complément

Attention maintenant si vous désirez ajouter d'autres macros dans votre complément. Admettons que vous désiriez ajouter la macro suivante :

Sub Pluie()
   MsgBox "Il Pleut"
End Sub

Si vous l'ajoutez et que vous retournez dans Excel et que vous allez dans le menu Fichier/Enregistrer, il va vous enregistrer le classeur visible à l'écran, mais en tout cas pas CompTest.XLA !

Pour enregistrer les modifications apportées à votre compléments, vous DEVEZ le faire DEPUIS l'environnement VBA (CTRL S, ou fichier/Enregistrer CompTest.XLA.

J'insuste bien sur ce point car quand on crée des macros dans des classeurs standards .XLS, on peut enregistrer son classeur indifféremment dans l'environnement VBA ou dans Excel, les modules, macros, feuilles et classeur seront tout enregistré en même temps, dans un même fichier.

Fonctions dans les compléments

Si vous écrivez des fonctions dans votre complément, par exemple :

Function MonNom()
  MonNom = "Michel Defawes"
End Function

Alors que les macros Sub n'apparaissent pas dans Outils/Macros/Macros, votre fonction apparaîtra dans le menu Insertion/Fonction (Et pour répondre à une interrogation que pourraient poser les connaisseurs : non, ça n'a rien à voir avec les éventuels Private ou Public qu'on pourrait ajouter avant)

Macros s'exécutant automatiquement dans les compléments

Il est possible de créer des macros qui s'exécutent automatiquement à chaque ouverture de CompTest.XLA. C'est à dire en fait à chaque ouverture d'Excel, pour autant qu'on ait coché la case correspondante dans Outils/Macros complémentaires.

1. Allez dans VBA
2. Cliquez deux fois sur VBAProject (CompTest.XLA) dans le but de l'ouvrir
3. Juste en dessous, cliquez deux fois sur Microsoft Excel Objets dans le but de l'ouvrir
4. Cliquez deux fois un peu plus bas sur ThisWorkBook dans le but d'ouvrir une page blanche à droite
5. Dans la liste déroulante de cette page de droite en haut, dans la liste de gauche, choisissez : "Workbook"
6. Ceci apparait :
   Private Sub Workbook_Open()
   End Sub
7. Mettez ceci :
   Private Sub Workbook_Open()
     MsgBox "J'ouvre"
   End Sub
8. Si vous désirez que du code VBA s'exécute à chaque fermeture d'Excel, pour autant que le complément soit chargé, évidemment :
9. Dans la liste de droite choisissez Before Close. Ca donne ceci :
   Private Sub Workbook_BeforeClose(Cancel As Boolean)
   End Sub
10. Mettez y : Msgbox "Je ferme"
11. Et si maintenant, vous désiriez exécuter du code VBA AU MOMENT ou on enlève de la mémoire CompTest.XLA, c'est à dire en fait quand on va dans Outils/Macros complémentaires, qu'on retire la coche de CompTest, et qu'on clique sur OK :
12. Dans la liste vde droite, choisssez Uninstall
13. Et, bien entendu, quand vous voulez que du code soit exécuté une seule fois lors du chargement du complément (quand on coche la case, et qu'on clique sur OK), c'est AddInstall

Arrangez vous pour avoir cette configuration :

Private Sub Workbook_AddinInstall()
  MsgBox "J'installe"
End Sub

Private Sub Workbook_AddinUninstall()
  MsgBox "Je désinstalle"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  MsgBox "Je ferme"
End Sub

Private Sub Workbook_Open()
  MsgBox "J'ouvre"
End Sub

Enregistrez avec CTRL-S DEPUIS VBA, PAS depuis Excel, donc !

Et faites quelques tests : Quittez Excel, rouvrez Excel, cochez et décochez la case de votre complément CompTest.XLA, et regardez si vous obtenez bien les messages correspondants.