Débuter avec VBA Excel et la programmation

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


VBA Excel - Programmation sous Excel

Cette formation a pour but de vous faire découvrir la programmation sous Excel en douceur et avec simplicité. Le VBA est un langage de programmation orienté objets. VBA signifie Visual Basic For Applications. Ici l'application est Excel. C'est pourquoi il existe aussi le VBA Word, Powerpoint ou encore Access. Le langage de programmation est le Visual Basic. Sa syntaxe est héritée de celle du VB.Net pour les plateformes DotNet. Les méthodes du VBA Excel et ses objets sont centrés autour des objets d'Excel à manipuler comme des cellules ou des plages ainsi que des lignes et des colonnes de tableau. Retenez bien ce mot Objet car c'est le fondement même d'un langage orienté Objets. Un Objet possède des propriétés et des méthodes. Une propriété permet d'attribuer une valeur ou une caractéristique à l'objet. La méthode permet de réaliser une action avec l'objet. Ainsi si on imagine un objet désignant une cellule dans VBA Excel, une de ses propriétés permettrait de modifier la police ou la couleur de la cellule, c'est une caractéristique. Une méthode quant à elle pourrait supprimer cette cellule, c'est une action. On appelle une propriété ou une méthode d'un objet VBA en séparant l'objet de la propriété ou de la méthode par un point dans le code : Objet.Propriété=valeur. Comme vous le savez, lorsque vous réalisez une macro automatique avec Excel, cette dernière enregistre vos actions afin de les reproduire par simple clic sur un bouton. Ces actions sont en fait enregistrées en VBA Excel. Le support sur les macros Excel vous enseigne comment créer et manipuler ces macros. Ce que nous proposons est de créer des macros triviales pour consulter et comprendre le code ainsi généré. Nous tenterons des modifications et nous constaterons l'incidence lors de l'exécution. Nous y découvrirons les objets fondamentaux et apprendrons les propriétés et méthodes essentielles pour débuter de la meilleure des façons qui soit. Nous devons avoir tous les outils de développement à disposition. Nous allons donc commencer par afficher le ruban Développeur.

• Démarrer avec un nouveau classeur vierge dans Excel,
• Cliquer sur Fichier en haut à gauche de la fenêtre puis sur Options dans la liste,
• Dans la boîte de dialogue, choisir la catégorie Personnaliser le ruban,
• Dans la liste de droite, cocher la case Développeur,
• Puis cliquer sur Ok pour valider,
• En passant par Fichier puis Enregistrer sous, enregistrer ce classeur au format .xlsm,

Ce format prend en charge les macros et leur code. Si vous l'enregistrez au format.xls classique, les macros sont perdues. Tout cela est fait pour la sécurité. Les macros étant codées dans un langage de programmation, vous pourriez très bien y trouver des codes malveillants venant de tierces personnes. VBA Excel est en effet très puissant.


Le ruban Développeur apparaît ainsi disponible en haut de la fenêtre Excel. Il offre les fonctionnalités permettant de manipuler les macros et d'accéder au code notamment.

Macro automatique
Nous allons créer une première macro nous enregistrant en train de passer en gras sur fond rouge une cellule. Nous attribuerons de même une couleur blanche au texte. L'objectif est de voir le code généré. Une cellule étant sélectionnée, normalement A1 par défaut :

• Dans le ruban Développeur, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue nommer la macro : test_mef par exemple,
• Dans la liste déroulante, conserver Ce classeur pour enregistrer la macro,
• Cliquer sur Ok pour démarrer l'enregistrement.

A partir de ce point, nous devons réaliser les actions strictement nécessaires car Excel nous enregistre.

• Cliquer sur le bouton G du ruban Accueil pour mettre en gras,
• Choisir une couleur de remplissage rouge toujours avec le ruban Accueil,
• Puis définir une couleur de police blanche,

C'est tout pour les actions. Nous devons immédiatement stopper l'enregistrement.

• Dans le ruban Développeur, cliquer sur le bouton Arrêter l'enregistrement,

La macro existe mais n'est pas matérialisée par un bouton. Pour l'instant la macro existe seulement sous forme de code VBA. Nous allons créer ce bouton.

• Cliquer sur le menu Fichier puis choisir Options dans la liste,
• Dans la boîte de dialogue, sélectionner la catégorie Barre d'outils Accès rapide,
• Au centre, choisir la catégorie Macros,
• Puis sélectionner la macro test_mef dans la liste du dessous et cliquer sur Ajouter,
• Cliquer sur Modifier en bas pour choisir une image de bouton,
• Cliquer deux fois sur Ok pour valider successivement les deux boîtes de dialogue,

Le bouton apparaît désormais dans la barre d'accès rapide, au-dessus du ruban, à côté des boutons pour enregistrer, annuler et rétablir.

• Saisir un texte dans une cellule et le valider par CTRL + Entrée pour la garder active,
• Cliquer sur ce nouveau bouton depuis la barre d'accès rapide,

Comme vous le remarquez, les propriétés de mise en forme définies par la macro s'appliquent instantanément à la cellule sélectionnée.

Code VBA et modules
Voyons à quoi ressemble cette macro côté code.

• Dans le ruban Développeur, cliquer sur le bouton Macros,
• Dans la boîte de dialogue, sélectionner la macro test_mef puis cliquer sur Modifier,

Vous basculez dans l'éditeur de code Visual Basic avec une nouvelle fenêtre. Comme l'indique le volet des projets sur la gauche, le code est écrit dans un module, le Module1, encerclé de rouge sur la capture. En dessous figure le volet des propriétés sur lequel nous reviendrons. Au centre, se trouve votre code, écrit entre les bornes de la procédure, comme toujours. Les bornes de la procédure sont le Sub suivi du nom de la fonction, de la macro ici et le End Sub. Ces bornes délimitent le champ d'action de la macro, son début et sa fin. Vous remarquez que le nom de la fonction est suivi d'une parenthèse ouvrante et d'une parenthèse fermante. C'est une norme, toute fonction possède des parenthèses pour attendre des paramètres à traiter, même si elle n'en a pas comme c'est le cas ici. Précédés d'une apostrophe, figurent les commentaires. C'est pour cela qu'ils apparaissent en vert. Et puis vient le code ! Examinons-le.

Selection.Font.Bold = True
Selection est l'objet qui désigne la sélection. Dans notre cas, la cellule qui était active au moment de débuter l'enregistrement. Il pourrait aussi s'agir d'une plage de cellules, d'un tableau. Ainsi toutes les cellules de la plage seraient concernées par les réglages de cette ligne. Font est une propriété de l'objet Selection qui désigne la police. Mais la police est trop générique pour être seulement définie ainsi car elle peut être mise en gras, soulignée, en couleur, de taille différente. Donc nous utilisons une propriété dérivée de Font, ici Bold pour définir le réglage que nous souhaitons réaliser. Bold pour gras. Une propriété s'affecte en programmation. Cela signifie qu'on lui attribue une valeur avec le symbole =. Gras peut être soit vrai soit faux. C'est pourquoi l'affectation est réglée à True (Vraie) pour passer la sélection en Gras. S'il était écrit Selection.Font.Bold = False et que vous exécutiez la macro sur une plage de cellules en gras, ces dernières repasseraient en normal.

With Selection.Interior
.Pattern= xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

Voilà une instruction très intéressante, With. Cette portion du code règle les propriétés de l'intérieur des cellules de la sélection : Selection.Interior, à l'aide de différentes propriétés dérivées. Mais plutôt que de répéter sur chaque ligne l'instruction Selection.Interior pour ensuite énoncer la propriété dérivée à régler, l'instruction With permet de lister ces propriétés sans répéter l'objet sur lequel elles s'appliquent. Il faut bien penser à fermer cette instruction par End With sans quoi une erreur de syntaxe est générée. Généralement on commence par ouvrir et fermer et on écrit à l'intérieur. Pattern est une propriété qui désigne le motif de remplissage de la cellule. La macro a déclenché ce réglage car avec une couleur de remplissage, nous pouvons associer un motif. Mais nous n'avons pas réglé le motif. Donc cette ligne et la suivante, pour la couleur du motif peuvent être supprimées.

• Supprimer les deux lignes réglant les propriétés du motif dans l'instruction With,
• Sur la feuille Excel, tester la macro en cliquant sur son bouton,

Vous remarquez qu'elle fonctionne toujours à l'identique alors que nous avons épuré le code. Tant mieux, c'est plus clair.

.Color = 255
Comme son nom l'indique, la propriété Color permet de régler la couleur, ici selon une valeur peu explicite certes qui peut être comprise entre 1 et 255. Comme cette propriété dérivée est appliquée à la propriété Interior de l'objet Selection, elle désigne la couleur de remplissage de la cellule. Celle que nous avons réglée sur le rouge. Cet index de couleur (255 ici) peut être remplacé par la fonction RGB(), Red, Green, Blue, soit l'équivalent en français de RVB, Rouge, vert et bleu. Cette fonction RGB() attend donc trois valeurs entre ses parenthèses, les composantes de couleur. Nous souhaitons ainsi remplacer le fond rouge par un fond violet soutenu. Nous pouvons utiliser un logiciel comme Photoshop ou même Paint pour connaître les valeurs des composantes d'une telle couleur.



Paint nous indique, pour le violet choisi, que les composantes sont 202, 0 et 101.

• Dans le code remplacer la valeur 255 par RGB(202, 0, 101),

Au moment où vous saisissez les composantes dans les parenthèses de la fonction, vous notez l'apparition d'une info-bulle. Cette dernière vous guide en temps réel pour vous indiquer quel est le paramètre attendu.

• Enregistrer ces modifications (CTRL + S),
• Tester la macro avec le bouton sur la feuille Excel.

Vous remarquez en effet que le fond de la cellule passe en violet.

.TintAndShade
Il s'agit d'une propriété qui ne nous intéresse pas, nous ne l'avons pas réglée. Elle concerne la couleur, sa densité plus ou moins claire. Idem pour PatternTintAndShade qui concerne la couleur du motif. Pour savoir à quoi correspond un objet ou une propriété dans VBA Excel, vous pouvez sélectionner le mot clé en double cliquant dessus. Ensuite vous enfoncez la touche F1 du clavier et vous êtes redirigé sur l'aide contextuelle en ligne.

• Supprimer les deux dernières lignes de l'instruction With faisant référence à cette propriété,
• Tester la macro,

Le résultat est identique. Ces deux lignes n'étaient donc pas utiles. Le code devient de plus en plus simple. Intéressons-nous maintenant à la deuxième instruction With qui s'occupe des réglages de police : With Selection.Font.

.ThemeColor = xlThemeColorDark1
ThemeColor est une propriété qui définit le thème de la couleur, appliqué à la propriété Font (Police)de l'objet Selection ici. Or, nous avons simplement défini le blanc pour le texte. Donc nous allons utiliser la fonction RGB() pour régler la propriété Color à la place de ThemeColor.

• Remplacer l'instruction .ThemeColor = xlThemeColorDark1 par .Color = RGB(255, 255, 255),

Les composantes 255 définissent le blanc dans la function RGB(). La ligne du dessous, concernant la propriété TintAndShade ne nous concerne pas, comme toute à l'heure,

• Supprimer la ligne .TintAndShade = 0 de l'instruction With,
• Enregistrer les modifications et tester la macro,

Le résultat est toujours le même. Pourtant le code a nettement changé.


Du coup, le premier bloc With n'est plus utile puisque seule une propriété y figure. Alors que son intérêt est de pouvoir énumérer les propriétés à régler afin d'optimiser le code. Nous allons en revanche modifier le second bloc With de manière à pouvoir y inclure la première instruction de la procédure, qui se trouve isolée.

• Supprimer les lignes de commentaires car elles ne sont pas très utiles ici,
• Modifier le code de façon à obtenir le résultat de la capture ci-dessous,

Nous avons supprimé le premier bloc With inutile, pour écrire l'objet et ses propriétés qui règlent la couleur de fond, sur une seule ligne. Nous avons inclus le réglage du gras (Bold) pour l'objet Selection dans le bloc With restant. Le code est devenu trivial, très lisible. Il se contente désormais de réaliser ce que nous avions dicté à la souris.

• Enregistrer les modifications et tester la macro.

Pas de souci, le résultat est toujours identique.

• Sélectionner une plage de cellules, soit plusieurs cellules à la fois,
• Et, cliquer sur le bouton de la macro,

Tous les réglages du code de la macro s'appliquent instantanément à l'ensemble des cellules. Ceci est dû à l'utilisation de l'objet Selection dans le code qui, rappelez-vous, ne désigne pas une cellule en particulier mais les cellules sélectionnées au moment de commencer le traitement.

Créer une macro par le code VBA
Maintenant que nous connaissons quelques petites ficelles, nous allons écrire la macro en lui attribuant un nouveau nom. A ce stade, son traitement sera similaire à celui de la macro précédente, nous changerons seulement certaines valeurs. Puis nous verrons qu'elle sera disponible dans la liste des macros de manière à l'associer à un bouton. Il s'agit donc d'une démarche différente pour un résultat identique.

• Dans l'éditeur de code, copier intégralement la macro (du Sub au End Sub),
• La coller en dessous, dans la page du module,
• Changer son nom après le Sub en : mef_bleue,

Nous avons une nouvelle macro avec un nouveau nom mais qui pour l'instant réalise exactement les mêmes actions que la précédente. Notez la présence de la ligne continue de séparation pour bien démarquer les deux macros. Nous souhaitons que ce nouveau code mette la cellule en bleu assez clair avec un texte gris foncé dans un premier temps. Il nous suffit donc de jouer uniquement sur les valeurs des composantes de la fonction RGB(). Dans un deuxième temps, nous souhaiterions que cette macro soit capable d'appliquer une bordure à la sélection. L'outil de Paint pour personnaliser les couleurs nous indique qu'un bleu clair peut s'obtenir avec les composantes : 40,180,255. De même, un gris foncé peut s'obtenir avec les composantes : 76,76,76.

• Modifier les composantes de couleur des fonctions RGB() de la sorte dans le code de cette nouvelle macro,

Nous n'avons pas encore de bouton pour la tester. Ce n'est pas un problème, l'éditeur de code nous permet de le faire.

• Sélectionner tout d'abord une cellule contenant du texte sur la feuille,
• Revenir dans l'éditeur de code et cliquer n'importe où entre les bornes de la procédure de la deuxième macro afin d'y placer le point d'insertion,

C'est ainsi que VBA Excel saura quel code exécuter au moment de lui demander.

• Puis cliquer sur le bouton matérialisé par une flèche verte dans la barre d'outils de l'éditeur de code,

En apparence rien ne se produit. En fait ça va très vite.

• Afficher la feuille du classeur,

Vous remarquez que la cellule qui était présélectionnée a bien été affectée. Nous venons d'exécuter une macro, construite manuellement, par le code et donc sans l'aide d'un bouton. Nous souhaitons maintenant définir une bordure épaisse, pour la sélection par le code. Mais nous ne connaissons pas les propriétés de l'objet Selection qui permettent d'y parvenir. Nous pourrions passer par l'explorateur d'objets et la fenêtre des propriétés de l'éditeur de code. Tous deux sont accessibles par le menu Affichage. Mais il est encore plus simple de créer une macro automatique et de récupérer le code.

• Sélectionner une cellule dans la feuille du classeur Excel,
• Dans le ruban Développeur, cliquer sur le bouton Enregistrer une macro,
• La nommer : bordure,
• Cliquer sur Ok pour débuter l'enregistrement,
• Avec le bouton Bordures du ruban Accueil, définir une bordure extérieure épaisse,
• Puis, cliquer sur le bouton Arrêter l'enregistrement du ruban Développeur,
• Revenir dans l'éditeur de code,

Sous la macro mef_bleue() vous constatez la présence d'une nouvelle procédure. Il s'agit de la macro bordure() que nous venons de créer. Son code paraît bien compliqué. En fait il s'agit d'une répétition systématique des mêmes propriétés réglées de la même façon. Excel traite les bordures indépendamment. D'abord la bordure de gauche : Selection.Borders(xlEdgeLeft), puis la bordure du dessus : Selection.Borders(xlEdgeTop) etc... Alors qu'il suffit de ne pas indiquer de paramètres à la propriété Borders de l'objet Selection pour que VBA Excel comprenne qu'on parle de la bordure extérieure.

• Sélectionner et copier le premier bloc With et le coller dans la macro mef_bleue() sous l'instruction Selection.Interior.Color= RGB(40, 180, 255),
• Supprimer le paramètre dans les parenthèses de la propriété Borders ainsi que ses parenthèses,

Ainsi nous désignons l'ensemble de la bordure de la sélection et pas seulement l'un de ses côtés.

• Supprimer la ligne .TintAndShade = 0, inutile comme nous l'avons vu précédemment,
• Remplacer la ligne .ColorIndex = 0 par .Color = RGB(76, 76, 76),

.ColorIndex = 0 permet de régler la couleur de bordure sur le noir. Nous lui préférons comme précédemment la propriété .Color qui permet de définir une couleur précise à l'aide de la fonction RGB(). En l'occurrence ici nous définissons une couleur de bordure identique à celle du texte.

• Sélectionner une cellule sur la feuille du classeur,
• Revenir dans l'éditeur de code et placer le point d'insertion dans les bornes de la procédure mef_bleue(),
• Cliquer sur la flèche de lecture pour exécuter le code,

La cellule apparaît bien avec les attributs de mise en forme précédents avec en plus la bordure. C'est parfait et nous avons appris avec Excel en lui demandant d'enregistrer une macro qu'il a transcrite en code. Nous n'avions plus qu'à nous en inspirer.

With Selection.Borders
Comme toute à l'heure, l'instruction With permet de regrouper toutes les propriétés à définir pour la ligne Selection.Borders. Propriété Borders de l'objet Selection, soit la bordure des cellules sélectionnées au moment de l'exécution.

.LineStyle = xlContinuous
Il s'agit de la propriété pour définir le type de trait de la bordure. Elle pourrait être en pointillés ou tirets mais la valeur xlContinuous définit un trait plein et continu. N'oubliez pas que la touche F1 sur la propriété vous conduit sur l'aide en ligne qui vous renseignera sur ces différentes valeurs.

.Color = RGB(76, 76, 76)
Comme nous l'avons dit plus haut, la propriété dérivée Color de la propriété Borders de l'objet Selection permet de définir la couleur de la bordure des cellules sélectionnées, à l'aide notamment de la fonction RGB().

.Weight = xlMedium
Weight est la propriété dérivée de la propriété Borders pour définir l'épaisseur de trait de la bordure. La valeur xlMedium permet d'obtenir un trait assez épais. Nous allons maintenant voir qu'il est aussi simple d'associer un bouton à une macro automatique qu'à une macro créée par le code. Dans les deux cas en effet, la création conduit à une procédure de code écrite dans un module Visual Basic.

• Retourner sur la feuille du classeur,
• Cliquer sur le ruban Fichier puis choisir Options dans la liste,
• Dans la boîte de dialogue, sélectionner la rubrique Barre d'outils Accès rapide,
• Au centre, sélectionner la catégorie Macros,
• Puis, cliquer sur la macro mef_bleue en dessous et cliquer sur Ajouter,
• Cliquer sur Modifier en dessous pour choisir une image,
• Et valider deux fois de suite par Ok pour les deux boîtes de dialogue,

De retour sur la feuille, le bouton apparaît à côté du précédent. Si vous sélectionnez des cellules et que vous cliquez dessus, vous exécutez la macro de la même façon que la précédente. Nous allons maintenant nettoyer le travail précédent.

• Revenir dans l'éditeur de code,
• Sélectionner toutes les lignes de code de la procédure bordure(), du Sub au End Sub,
• Les supprimer en enfonçant la touche Suppr du clavier,

Son code étant supprimé, la macro bordure ne sera plus proposée dans la liste des macros, notamment pour la création de boutons.

Les objets Range et Cells
Quoi de mieux qu'une macro automatique pour connaître l'objet qui permet de désigner et manipuler une cellule en particulier ? Car jusqu'alors l'objet Selection impliquait que nous agissions sur une plage présélectionnée. Et si nous voulons agir sur des cellules précises, référencées par rapport à leurs coordonnées, comment fait-on ? C'est ce à quoi nous allons répondre en demandant à une macro de nous enregistrer pendant que nous sélectionnons une cellule en particulier.

• Activer le ruban Développeur depuis la feuille du classeur Excel,
• Cliquer sur le bouton Enregistrer une macro,
• La nommer cellules par exemple et cliquer sur Ok pour démarrer,
• Sélectionner par exemple la cellule G10 avec la souris,
• Cliquer sur le bouton Arrêter l'enregistrement du ruban Développeur,
• Afficher l'éditeur de code en cliquant sur le bouton Visual Basic duruban Développeur,

Si vous ne voyez pas votre nouvelle macro dans l'éditeur de code, c'est qu'elle a été écrite dans un nouveau module.

• Double cliquer alors sur la rubrique Module2 depuis la fenêtre Projet sur la gauche,

Le code excessivement simple de la macro apparaît. Vous notez l'utilisation de l'objet Range avec en paramètre et entre guillemets, les références de la cellule telles qu'elles sont identifiées sous Excel. Range à l'origine désigne une plage de cellules mais si une seule cellule est indiquée, c'est la cellule seule qui est pointée. Associée à l'objet Range, nous avons la méthode Select. Souvenez-vous, une méthode réalise une action et n'affecte pas de valeur à une propriété. Ici l'action est donc de sélectionner la cellule ou la plage désignée. L'objet Range possède donc des propriétés et méthodes simples à découvrir permettant de personnaliser et paramétrer les cellules.

• Dans l'éditeur, sous la première ligne de code, saisir Range('G10').,

Lorsque vous tapez le point (.) après l'objet Range, vous appelez une propriété ou une méthode de l'objet. C'est pourquoi une petite liste déroulante s'affiche avec la saisie. Elle vous propose toutes les méthodes de l'objet (précédées d'une icône verte) ainsi que toutes ses propriétés(précédées d'une icône grise). Si vous tapez les premières lettres de la propriété, par exemple fon, la liste vous place sur les propriétés commençant de la sorte.

• Taper ou sélectionner ainsi la propriété Font puis taper un nouveau point (.),

Vous appelez ainsi les propriétés dérivées de la propriété Font.

• Taper ou sélectionner ainsi la propriété Size pour la taille de la police,
• Lui affecter la valeur 12 (=12),

Si nous appliquons ce que nous avons appris toute à l'heure, nous pouvons aussi paramétrer la couleur de police ainsi que la couleur de fond. Les propriétés sont en effet les mêmes. Qu'il s'agisse d'une sélection ou d'une plage, les deux objets désignent bien des cellules.

• Reproduire le code de la capture ci-dessus,
• L'exécuter en cliquant sur la flèche ou en enfonçant la touche F5 du clavier,

Si vous affichez la feuille Excel, vous remarquez que la cellule a bien subi la mise en forme dictée par notre code. Pour mettre en forme une ou des cellules, il n'est pas nécessaire de la sélectionner préalablement.

• Supprimer la ligne de code Range('G10').Select,

Et pour appliquer ce que nous avons appris précédemment, nous allons optimiser le code.

• Regrouper les instructions de l'objetRange dans un bloc With,

Si vous testez le code, tout fonctionne bien et c'est plus propre.


Comme nous le disions, l'objet Range sert initialement à désigner une plage de cellules. Pour ce faire, tout comme dans les fonctions Excel, il suffit d'indiquer les références séparées du symbole deux points (:) pour une plage de cellules contigües.

• Modifier le code de manière à ce que les réglages s'appliquent sur la plage de cellules G10:H12,

Sachez enfin qu'il existe l'objet Cells qui permet de désigner une cellule en fonction de son numéro de ligne et de son numéro de colonne. Cet objet attend deux paramètres, le numéro de la ligne et le numéro de la colonne. Ainsi pour la cellule C10, nous lui transmettrons les valeurs 10 pour la ligne et 3 pour la colonne. Comme cet objet manipule des cellules, il dispose des mêmes propriétés et méthodes que les objets Selection et Range.