Les macros complémentaires avec Excel

Introduction du cours

Bonjour à tous !

Vous savez tous ce qu'est une macro. Il n'est donc pas nécessaire de vous expliquer comment utiliser cet outil. :-°
Bon, je dois bien admettre que certains ne sont pas forcément au courant de l'utilité de celle-ci et je vais devoir vous faire un petit résumé.

Qu'est-ce qu'une macro ? À quoi cela peut-il servir ?

Je pourrais m'attarder longuement sur la définition exacte des macros. Pour cela, nous avons divers sites comme www.gaboly.com.
Je me contenterai, ici, de répondre aux seules questions qui sont posées ci-dessus.

• La macro complémentaire, dans Excel, est un code écrit en VBA.

• Elle sert à exécuter automatiquement une tâche dans Excel.

Les macros sont souvent utilisées pour des actions répétitives et longues à faire.

Pour que vous ayez une bonne idée des possibilités offertes, je vais vous faire faire un petit TP.
Je vous fournis donc le fichier de base et nous travaillerons dessus.

Récupérez ce fichier : comptes.xls

Afin de colorer un peu plus le code, j'ai utilisé les balises-codes du VB.NET et non celles du VBA, qui sont inexistantes.
Je précise bien que le VB.NET n'a rien à voir avec le VBA.

L'écriture d'une macro

Je vais en premier lieu vous montrer la façon la plus compliquée de créer une macro.
Pour cela, il faut avoir certaines bases en VBA.
Le but de ce tutoriel n'est pas de vous apprendre le Visual Basic. Il s'agit simplement de vous en montrer quelques caractéristiques avant de poursuivre.
Cela vous sera utile au cas où vous seriez amenés à modifier une macro manuellement.

On va commencer par créer une macro qui nous permet de calculer la somme des dépenses.
Pour cela, ouvrez le fichier que vous avez téléchargé.

Écriture d'une macro

Maintenant, cliquez sur le menu Outils puis sur Macros et enfin sur Visual Basic Editor.
Nous pouvons maintenant créer notre première macro avec cet éditeur.

Pour créer notre première macro, vous devez cliquer sur le menu Outils puis Macros.
Une petite fenêtre apparaît avec toutes les macros du classeur. Bien sûr, celui-ci étant vierge, vous n'en voyez pas.

Dans le champ « nom de la macro », mettez « somme_depenses ».

Il faut éviter tous les caractères spéciaux, ils sont cause de problèmes.
Donc, pas d'espaces, pas d'accents, pas de « + - / * », etc.

Cliquez ensuite sur Créer.
La nouvelle fenêtre qui apparaît nous permet donc de taper notre code en Visual Basic.

La macro commence toujours par Sub nom de la macro() et se termine toujours par End Sub.
Dans notre cas, nous avons :

Sub somme_depenses() End Sub

Nous allons pouvoir commencer à taper notre code.
Nous devons donc définir la cellule où la somme des dépenses s'affichera.
Il s'agit de la case C21.
Celle-ci est fusionnée avec la C22.

Voici comment lui dire que nous sélectionnons la cellule C21 (qui contient aussi la cellule C22) :

Range("C21:C22").Select

Il nous faut ensuite lui faire calculer la somme des cellules C6 à C20 dans la cellule active.
Là où ça va faire réfléchir, c'est le positionnement dans les formules.

ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"

Humm, hummmm ?
C'est là que ça commence à chauffer derrière les yeux et entre les oreilles.

=Sum() représente la formule =Somme() qu'on peut facilement taper dans une cellule du tableau.
R[-15]C:R[-1]C représente la zone de sélection par rapport à la cellule actuelle.

Il faut remonter de 15 pour sélectionner la cellule 6 et remonter de 1 pour sélectionner la cellule 20 par rapport à la cellule active (C21:C22).
C'est en suivant cette logique que nous avons écrit cette ligne.
Les deux points dans la formule définissent la plage comprise entre les deux cellules.

Je vous affiche le code complet de la macro.

Sub somme_depenses() Range("C21:C22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" End Sub

Qu'avons-nous fait exactement ?

Vous allez rire !
Cette macro permet d'écrire =somme(C6:C20) dans la cellule C21:C22.

Vous n'êtes pas obligés de fermer l'éditeur pour pouvoir revenir à Excel, « ALT + TAB » est amplement suffisant.

Pour l'exécuter, vous retournez dans Excel et vous cliquez sur Outils → Macro → Macros, choisissez la macro puis cliquez sur Exécuter.

Là, vous allez me dire que c'est se casser la tête pour pas grand-chose et je ne vous donne pas tort.
Nous allons donc passer ensuite à la méthode la plus simple pour créer une macro.
Il fallait juste que vous sachiez ce qui se passe à l'écriture d'une macro avant de poursuivre ce tutoriel.

Je vous donne quand même un petit supplément à cette macro.

Range("G21:G22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"

Ce qui nous donne donc :

Sub somme_depenses() Range("C21:C22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("G21:G22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" End Sub

Allez sur la cellule C21:C22 et supprimez son contenu.
Exécutez ensuite la macro et regardez ce qui a changé.

J'espère que ce premier changement vous incite à poursuivre ce tutoriel…

L'assistant création de macros

Après avoir aperçu l'écriture d'une macro, nous allons passer à l'outil qui nous sera le plus utile.

L'assistant enregistrera chaque chose que l'on fera. Le moindre clic sera inscrit en VBA.

Nous risquons d'augmenter considérablement la taille de la macro en effectuant des tâches inutiles.
Il nous faut faire uniquement le strict minimum.

Enregistrer une macro complémentaire

Cliquez donc sur Outils → Macro → Nouvelle_macro.
Vous voyez qu'on peut attribuer un raccourci pour son exécution ou même l'enregistrer ailleurs que dans le classeur actuel.
Nous n'allons pas nous attarder sur ces options et rester sur ce classeur.

Mettez le nom que vous voulez à votre macro (pas de caractères spéciaux) et cliquez sur « OK ».
J'ai mis test comme nom de macro et « essai pour le SDZ » en description.

Vous voyez la barre d'outils s'afficher.

Elle nous servira à la fin.

C'est à partir de là qu'Excel enregistre tout ce qu'on fait, il nous faut donc éviter les clics superflux.

• Sélectionnez la cellule D28 ;

• appuyez sur la touche +=} pour faire le signe égal ;

• sélectionnez la cellule G21 ;

• appuyez sur 6-| pour faire le signe moins ;

• sélectionnez la cellule C21 ;

• validez avec la touche Entrée ;

• sélectionnez, à nouveau, la cellule D28.

Lors de l'enregistrement, vous devez avoir en G28 :

Citation : Excel

=G21-C21

Si tout s'est bien passé, nous avons nos deux totaux de renseignés et la somme restante.

Appuyer sur le bouton « Arrêter l'enregistrement » de la barre d'outils macros.

Allez voir l'éditeur, Microsoft Visual Basic, qui contient nos deux macros.

Vous pouvez remarquer une grande différence entre celles-ci.

Sub somme_depenses() Range("C21:C22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("G21:G22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" End Sub Sub test() ' ' test Macro ' essai pour le SDZ ' ' ActiveCell.Offset(-2, -6).Range("A1:B3").Select ActiveCell.FormulaR1C1 = "=R[-7]C[3]-R[-7]C[-1]" ActiveCell.Range("A1:B3").Select End Sub L'arrangement manuel d'une macro enregistrée

C'est dans des moments comme celui-ci qu'il est possible de simplifier la macro, et c'est ce que nous allons faire en fusionnant les deux précédentes dans une seule et même macro.

Nous gardons donc la première macro :

Sub somme_depenses() Range("C21:C22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("G21:G22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" End Sub

Nous allons l'arranger avec ce qu'on a dans la seconde.

Nous devons garder la formule utilisée.

ActiveCell.FormulaR1C1 = "=R[-7]C[3]-R[-7]C[-1]"

Mais juste avant celle-ci, nous allons donner la cellule cible.

Range("D28:E30").Select

Ce qui donne :

Range("D28:E30").Select ActiveCell.FormulaR1C1 = "=R[-7]C[3]-R[-7]C[-1]"

Notre macro complète sera donc :

Sub somme_depenses() Range("C21:C22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("G21:G22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("D28:E30").Select ActiveCell.FormulaR1C1 = "=R[-7]C[3]-R[-7]C[-1]" End Sub

Supprimez tout le reste (l'autre macro).
Il ne doit vous rester que celle-ci.

Cette seule macro permet donc d'inscrire les 3 formules dans les trois cellules.
1 macro pour 3 résultats en un seul coup.
Je vous accorde que de poser une macro de ce genre n'est guère très intéressant, mais passer par là n'est certainement pas une mauvaise chose. Nous allons désormais pouvoir poursuivre avec d'autres macros complémentaires et d'autres façons de les utiliser.

Le nettoyage des cellules

Il n'y a pas de grosses questions à se poser là-dessus, on va juste faire le vide dans toutes les cellules où se trouvent nos chiffres.

Cliquez sur Outils → Macro → Nouvelle_macro et nommez celle-ci « effacer ».
Cliquez sur OK.

• Sélectionnez les cellules de C6 à C22 ;

• appuyez sur CTRL ou MAJ (selon la version d'Excel) pour ajouter une sélection ;

• restez appuyés et sélectionnez les cellules de G6 à G22 ;

• et la cellule D28 ;

• appuyer sur la touche Suppr de votre clavier.

Arrêtez l'enregistrement de la macro.

Retournez voir l'éditeur.
Ce dernier doit afficher :

Sub somme_depenses() Range("C21:C22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("G21:G22").Select ActiveCell.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)" Range("D28:E30").Select ActiveCell.FormulaR1C1 = "=R[-7]C[3]-R[-7]C[-1]" End Sub Sub effacer() ' ' effacer Macro ' essai pour le SDZ ' ' ActiveCell.Offset(-22, -1).Range("A1:A17,E1:E17,B23:C25").Select ActiveCell.Activate Selection.ClearContents End Sub

Oups !

Nous avons commis une erreur.
Nous voulons juste effacer les résultats et non la totalité, ce qui implique d'effacer juste C21, G21 et D28.
Appuyez sur « CTRL + Z » pour annuler cette dernière opération.

Modifions notre macro.

Sub effacer() ' ' effacer Macro ' essai pour le SDZ ' ' ActiveCell.Offset(-22, -1).Range("A1:A17,E1:E17,B23:C25").Select ActiveCell.Activate Selection.ClearContents End Sub

Nous gardons donc l'activation de cellule et l'effacement.
Nous ne devons modifier que la première ligne qui ne cible pas les bonnes cellules.
Mettons donc :

Sub effacer() ' ' effacer Macro ' essai pour le SDZ ' ' Range("C21,G21,D28").Select ActiveCell.Activate Selection.ClearContents End Sub

Et, au passage, on va enlever l'inutile.
Ce qui nous donnera :

Sub effacer() Range("C21,G21,D28").Select ActiveCell.Activate Selection.ClearContents End Sub

Voilà qui est bien mieux.

Nous pouvons effacer simplement le contenu des trois cellules de résultats à l'aide de cette macro.

Peut-on faire plus simple que d'aller cliquer sur « Outils → Macro → Macros » et de sélectionner la macro « supprimer » puis de cliquer sur « OK » ?

Vous vous dites : « je vais devoir supprimer les chiffres au moins 200 fois dans ma journée car je veux calculer le budget de chacun de mes clients et je dois avouer qu'un simple clic ça m'aiderait bien ».

Je vais alors vous répondre qu'on va arranger ça.
Ce n'est pas très compliqué.

Lier une macro à un objet

Pour aider à la tâche, il serait bien de pouvoir lier cette macro à un objet comme un bouton ou une image.
Excel nous offre quelques possibilités sur ce point-là, que nous allons aborder dans cette partie du tutoriel.