Excel - éditeur Visual Basic
éditeur Visual Basic
Vous avez pour le moment vu comment enregistrer une macro (ou plusieurs), mais n'avez guère d'idée de ce qui se cache derrière. Il est toujours intéressant d'examiner les macros enregistrées, pour éventuellement les modifier. Plusieurs raisons peuvent vous pousser à vouloir modifier une macro :
*
- corriger un texte qui a été mal saisi pendant l'enregistrement (un titre, par exemple) ;
- supprimer une commande qui a été enregistrée accidentellement sans être utile à cette macro (un défilement de fenêtre accidentel, par exemple) ;
- modifier une valeur enregistrée, comme une largeur de marge ou de colonne ;
- supprimer une commande devenue inutile.
Une autre raison peut vous inciter à examiner le code : simplement souhaiter pouvoir reproduire telle ou telle commande dans une macro que vous souhaitez rédiger intégralement.
L'éditeur de macros qui permet d'afficher la programmation et d'écrire dans le langage pertinent se nomme éditeur Visual pour Excel (et la suite Microsoft Office). Vous l'affichez en cliquant simplement dans l'onglet Développeur sur Visual Basic, ou en appuyant sur les touches Alt+F11.
Au premier examen de l'Éditeur Visual Basic, vous pourriez vous sentir intimidé. Il n'y a toutefois aucune crainte à avoir : même si sa fenêtre montre un logiciel multifenêtres sophistiqué, vous n'avez pas à le connaître en profondeur pour éditer une simple macro.
La fenêtre principale est la fenêtre Code, qui affiche la programmation associée au module sélectionné. Comme toutes les procédures y apparaissent, c'est cette fenêtre que vous utiliserez le plus souvent dans l'éditeur.
Vous pouvez savoir où vous vous trouvez (dans quel module) en examinant l'explorateur de projet : une fenêtre dédiée qui affiche tous les projets ouverts et leurs objets associés.
C'est toujours la fenêtre principale Code qui présente le code de la macro. Pour comprendre celui-ci, vous devez comprendre quelque peu le langage de programmation VBA.
Toute macro est constituée d'instructions. Vous pouvez les éditer comme vous le faites avec un traitement de texte. Si vous connaissez un peu l'anglais, vous pouvez déchiffrer le sens de la plupart des instructions. Voici par exemple quelques instructions VBA qui nécessitent d'être éditées, suivies par une description des éventuelles corrections à apporter :
.LeftHeader = "Coment ça marche"
Il manque un m à Comment.
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Le nombre de copies à imprimer devrait être 2.
Range("A1").Select
C'est A4 qui devrait être sélectionné.
Attention
Les chaînes de caractères (ou, si vous préférez, le texte) apparaissent toujours entre guillemets (""). En l'absence d'un seul de ces guillemets, la macro génère une erreur.
Ne supprimez jamais une instruction si vous n'êtes pas certain de savoir ce qu'elle accomplit. La macro risquerait de ne plus s'exécuter correctement.
Édition d'une macro
1.Si la macro que vous comptez éditer est stockée dans le Classeur de macros personnelles, vous devrez afficher le dossier avant de pouvoir l'éditer. Pour cela, choisissez l'option Afficher du groupe Fenêtres de l'onglet Affichage, choisissez Personal.xlsb dans la boîte de dialogue Afficher, puis cliquez sur OK. Si elle se trouve dans le classeur actif, passez directement à l'étape 2.
2.Choisissez dans le groupe Code de l'onglet Développeur l'option Macros, ou appuyez sur les touches Alt+F8.
3.Sélectionnez la macro à éditer dans la liste Noms des macros.
4.Cliquez sur Modifier. La macro sélectionnée s'ouvre dans l'Éditeur Visual Basic.
5.Éditez la macro dans la fenêtre Code.
6.Fermez l'Éditeur Visual Basic, puis enregistrez le classeur.
A savoir
Après avoir édité une ou plusieurs macros du Classeur de macros personnelles, masquez à nouveau ce classeur en activant sa fenêtre et en choisissant l'option Masquer du groupe Fenêtres de l'onglet Affichage. Sinon, le Classeur de macros personnelles apparaîtra systématiquement le premier à chaque lancement d'Excel.
Rédaction d'une macro
Vous pourriez toutefois légitimement vous demander pourquoi donc devoir envisager de rédiger des macros vous-même, au lieu de vous contenter d'employer l'enregistreur de macros. La raison en tient à ce que les macros enregistrées connaissent quelques limitations, dont celles qui suivent :
*
- Interaction : l'utilisateur doit lui-même procéder aux entrées qui commanderont la macro. Si un classeur possède des graphiques montrant les ventes de 20 produits, plutôt que d'obliger l'utilisateur à imprimer un graphique pour chacun de ces 20 produits, il serait préférable qu'il puisse sélectionner celui à imprimer.
- Décisions : vous voulez que la macro prenne des décisions lors de son exécution en fonction de certains événements ou données. Par exemple, supprimer certaines lignes si une certaine valeur est nulle ou égale à zéro.
- Fonctions personnalisées : vous exécutez régulièrement de longs calculs identiques dans vos feuilles de calcul. Créer une fonction personnalisée évitera de devoir saisir à chaque fois une formule complexe.
- L'enregistreur de macros peut être inefficace : il est fréquent de ne pas avoir à sélectionner une plage de cellules pour effectuer une action. L'enregistreur ne pouvant mémoriser autre chose que des touches de clavier ou des clics de souris, vous n'avez aucun moyen de faire référence à la plage concernée. C'est en revanche possible en écrivant vous-même une macro.
Le langage de programmation VBA fournit les outils permettant de résoudre les scénarios qui ne peuvent pas être enregistrés. En raison de la puissance et des possibilités offertes par VBA, il est exclu de dépasser dans ce livre le stade de l'aperçu des possibilités de programmation. Pour aller plus loin, vous devrez vous reporter à d'autres sources.
Nous commencerons par examiner certains termes et concepts propres aux langages de programmation orientés objet, puis verrons comment employer les outils d'édition proposés par Excel pour écrire des macros.
Introduction à la programmation orientée objet
Tout tableur se compose d'un ensemble d'objets, qui peuvent être manipulés par programmation. Le classeur, les feuilles de calcul et les lignes sont autant d'objets. Lors du travail avec un tableur, vous manipulez ses objets. La liste complète des objets est impressionnante : vous en découvrirez un certain nombre peu à peu dans ce chapitre. VBA, langage de programmation orienté objet, est capable de les manipuler.
Certains objets appartiennent à une collection. Une collection est un ensemble d'objets similaires pouvant être manipulés simultanément : une collection est elle-même un objet. La collection des classeurs est un exemple de collection. Il est constitué de chacun des objets classeur que vous avez à votre disposition. Vous vous référez généralement à un élément d'une collection en mettant son nom entre parenthèses, comme dans l'exemple suivant :
Collection("Nom_élément")
Les principales collections d'objets du tableur Excel sont WorkBooks (classeur), Sheets (feuille de calcul) et Range (cellules). Elles sont utilisables selon une hiérarchie descendante :
*
- Workbooks("Ventes 2009").Sheets("Janvier").Range("B2") désigne la cellule B2 de la feuille Janvier du classeur Ventes 2009 ;
- Sheets("Janvier").Range("B2") désigne la cellule B2 de la feuille Janvier du classeur actif (ouvert et affiché) ;
- Range("B2") désigne la cellule B2 de la feuille active.
Astuce
Veillez à ne pas confondre collection et objet membre d'une collection. Le nom d'une collection se termine par un « s ». Ainsi, Sheets est la collection de toutes les feuilles d'un classeur, Sheet est une feuille.
Une méthode est une action qui peut être effectuée par un objet. Un objet de tableur possède des méthodes. L'objet Workbook (classeur) possède par exemple une méthode Open (ouvrir) et une méthode Close (fermer). Dans une instruction VBA, vous employez la méthode d'un objet en faisant suivre son nom de celui de la méthode, séparé par un point :
Collection("Nom_élément").Nom_méthode
Les propriétés décrivent un objet. Certaines sont en lecture seule, d'autres en lecture/écriture. Une propriété en lecture seule ne peut pas être modifiée. Ainsi, un objet classeur possède une propriété Path (chemin), qui décrit son chemin d'accès complet. Elle est en lecture seule, car vous ne pouvez pas modifier le chemin d'un classeur sans l'enregistrer dans un autre dossier.
Une propriété possède donc une valeur. Vous modifiez la valeur d'une propriété en lecture/écriture en lui affectant une nouvelle valeur. Vous spécifiez une propriété comme une méthode :
Collection("Nom_élément").Propriété = Valeur
Si vous n'affectez pas de valeur, l'instruction indique (ou renvoie) la valeur courante. Parmi les propriétés classiques figurent Visible (à utiliser avec True et False pour afficher ou masquer), Value (valeur d'une cellule), Count (nombre de cellules d'une page, de feuilles de classeur, etc.).
Les propriétés et méthodes d'un objet portent le nom de membres. L'éditeur VBA propose d'ailleurs deux dispositifs très intéressants, la liste des membres et l'Information rapide automatique, qui simplifient considérablement le travail de rédaction de code. Nous y reviendrons par la suite.
Une procédure est un ensemble d'instructions de programmation qui effectue une tâche spécifique ou renvoie un résultat. Il en existe de deux sortes :
*
- Une sous-routine effectue une tâche spécifique. Les macros enregistrées directement sont des sous-routines.
- Une fonction est conçue pour renvoyer un résultat.
Lorsque vous écrivez du code, vous modifiez ou créez une procédure. Le processus est identique qu'il s'agisse d'une sous-routine ou d'une fonction.
Un bon point de départ : une macro enregistrée
Cela peut paraître curieux, mais le meilleur moyen de créer des macros est souvent d'en enregistrer. Les macros enregistrées présentent souvent des informations redondantes (même si cela s'améliore au fil de l'évolution des produits) qui les rendent un peu lourdes : vous pouvez commencer par vous « faire la main » en copiant et en essayant d'optimiser de telles procédures.
Ensuite, c'est un excellent moyen de déterminer rapidement comment effectuer telle ou telle opération par programmation : le code enregistré vous donne immédiatement la solution, que vous n'avez plus qu'à recopier dans la procédure qui vous préoccupe.
Il n'y a aucune honte à procéder ainsi, bien au contraire : c'est souvent un des seuls moyens de découvrir des astuces auxquelles vous n'auriez pas pensé. Renoncer à employer des méthodes rapides et efficaces serait faire preuve d'un entêtement dommageable...
Partir d'une procédure enregistrée pour chercher à l'optimiser et à l'adapter est un des moyens les plus efficaces que je connaisse pour maîtriser rapidement ce type de programmation.
Écriture d'une procédure
2.Ouvrez le classeur auquel vous voulez ajouter la ou les nouvelles procédures.
7.Choisissez dans l'onglet Développeur (ou dans l'onglet Affichage) l'icône Macros, ou appuyez sur Alt+F8.
8.Nommez cette procédure (nom de sous-routine ou de fonction) dans la zone Nom de la macro.
à savoir
Les noms de procédure doivent commencer par une lettre et peuvent contenir des lettres, des chiffres, ainsi que le caractère de soulignement. Les espaces ne sont pas permis. Excel autorise les caractères accentués.
9.Cliquez sur le bouton Créer. L'éditeur de macro s'ouvre, affichant la nouvelle procédure. L'instruction Sub est suivie du nom de la procédure et de l'instruction End Sub.
Astuce
Si vous êtes déjà dans l'éditeur VBA, il suffit pour créer une nouvelle procédure de saisir en dehors d'une procédure existante Sub ou Function directement dans la fenêtre Code, suivi par le nom de la procédure (et des éventuels arguments). Après avoir appuyé sur la touche Entrée, l'éditeur ajoute automatiquement les instructions End Sub ou End Function à la fin de la nouvelle procédure.
10.Comme nous voulons créer une fonction, remplacez le mot Sub par Function, dans la première ligne de la nouvelle procédure. Dans la dernière ligne, End Sub devient automatiquement End Function.
11.Entrez les instructions de la nouvelle procédure fonction VBA VOL(a,b,c), qui calcule le volume d'un parallélépipède rectangle dont les côtés ont pour longueur a, b et c :
Function VOL(a,b,c)
VOL = a*b*c
End Function
Astuce
Saisissez toutes les instructions en minuscules. Ce faisant, vous pourrez détecter les erreurs au cours de la frappe. Quand vous passerez à la ligne suivante, l'éditeur mettra en majuscule une lettre au moins de chacun des mots de la ligne, pour peu que vous les ayez tapés correctement. Il mettra aussi en majuscules les noms des variables et des constantes. Si rien de tel ne se produit, vérifiez le mot en question. Est-il correctement orthographié ? S'agit-il véritablement d'un objet, d'une méthode, d'une propriété ou d'une fonction du langage ?
D'accord, cette fonction ne présente pas un grand intérêt : il est plus long de saisir dans une cellule =VOL(a,b,c), avec ses onze caractères, que de saisir les six caractères de =a*b*c. Que vous remplaciez a, b et c par des références ou des variables n'y changera rien.
Imaginez toutefois une fonction nettement plus complexe, que vous seriez amené à saisir régulièrement dans vos feuilles de calcul. Par exemple, le calcul du nombre de jours d'un mois donné, à partir d'une date. La formule est la suivante :
=JOUR(DATE(ANNEE(A2);SI(MOIS(A2)=12;1;MOIS(A2)+1);1)-1)
Nous supposons ici que la cellule A2 contient la date concernée.
Il n'est pas facile de mémoriser cette formule, et surtout fastidieux de la saisir dans chaque nouvelle feuille de calcul. Vous pourriez bien sûr recourir au copier-coller déjà examiné, mais vous pourriez également envisager de créer une fonction personnalisée. Il suffira alors de saisir le nom de la fonction, comme avec n'importe quelle fonction intégrée.
La plupart des fonctions nécessitent au moins un argument : une information indispensable pour que la fonction puisse effectuer correctement le calcul. Il s'agit ici d'une date, à partir de laquelle la fonction va déterminer le nombre de jours du mois auquel se rapporte la date.
Voici comment créer cette fonction. Nous allons la créer dans le Classeur de macros personnelles, pour qu'elle soit par la suite accessible dans n'importe quel classeur :
3.Dans l'explorateur de projet de l'éditeur VBA, naviguez jusqu'à un module du Classeur de macros personnelles ou insérez-en un nouveau.
12.Saisissez ensuite ce qui suit dans le module :
Function NbJours(DateComplete)
NbJours = Day(DateSerial(Year(DateComplete), IIf(Month(DateComplete) = 12, 1, Month(DateComplete) + 1), 1) - 1)
End Function
Comme vous le remarquez certainement, cette formule diffère largement de celle qui serait saisie dans une cellule de feuille de calcul. Les noms de toutes les fonctions intégrées ont été transformés en anglais (JOUR devient DAY et MOIS, MONTH), certains étant même plus profondément modifiés : DATE devient DATESERIAL et SI devient IIf.
VBA est fondé sur l'anglais et n'est pas localisé, contrairement aux noms des fonctions intégrées dans une feuille de calcul. Vous constaterez la même chose avec la plupart des propriétés et des objets.
13.Une fois la saisie terminée, enregistrez votre travail en cliquant sur le bouton Enregistrer de la barre d'outils Standard de l'éditeur pour enregistrer le classeur de macros personnelles.
Le texte original de cette fiche pratique est extrait de
«Tout pour bien utiliser Excel 2010» (Fabrice LEMAINQUE, Collection CommentCaMarche.net, Dunod, 2010)