Amélioration de notre macro

Il y a quatre jours, je vous ai présenté une macro dont l’objet était de sélectionner, à l’intérieur de la sélection active, les seules cellules non vides.

La macro identifiait d’un côté les cellules contenant des formules et de l’autre celles contenant des constantes puis, si les deux ensembles étaient non vides, les fusionnaient grâce à la commande Union.

Pour cet exemple – comme très souvent avec le VBA – non seulement il y a de multiples façons d’atteindre ce même objectif, mais certaines sont nettement plus compactes que les autres. Voici ce que l’on peut obtenir quand on a activé la macro après avoir sélectionné le bloc A1:D5.

Dans ce cas précis, nous pouvons remplacer les 18 instructions de la macro originale par seulement deux instructions ayant exactement le même effet :

On Error Resume Next
Union(Selection.SpecialCells(xlCellTypeFormulas, 23), _
Selection.SpecialCells(xlCellTypeConstants, 23)).Select

Quand vous rendez ainsi un code nettement plus compact, vous êtes en situation de win-win : d’un côté, vous économisez de la place mémoire et du temps de calcul ; de l’autre, vous rendez la macro nettement plus lisible, et donc plus économique du point de vue de la maintenance.

Sélection des cellules non vides

La macro ci-dessous m’a été demandée par un lecteur qui souhaitait sélectionner, à l’intérieur d’un bloc, toutes les cellules non vides, donc contenant du texte, une valeur ou une formule. Le but qu’il poursuivait était de donner à cette sélection un format et/ou un motif particulier.

Cette macro n’est pas bien compliquée. Il faut toutefois ne pas oublier le On Error... au début car, quand un ensemble de cellules sélectionnées via le SpecialCells est vide, cela engendre une erreur.

Notez aussi l’utilisation de la commande Union, bien utile dans les macros, tout comme sa copine la commande Intersect.

Remarque – A la place du test If Formules.Cells.Count > 0, nous aurions pu utiliser un test tel que : If Not Formules Is Nothing.

Trois nouveaux livres à l'essai

Préparation à l’examen MCAS Excel 2007
Examen n°77-602 – Editions ENI
309 pages – 24,90 €.

Ce livre est découpé en huit chapitres à l’intérieur desquels en moyenne une quinzaine de sujets est traitée. Chaque sujet répond à une question du type
« Comment faire… ».

Il s’agit donc d’un ensemble de conseils & modes d’emploi et non d’un ouvrage avec une progression pédagogique. Chaque chapitre se termine par une série d’exercices avec leur corrigé.

Microsoft- Office Excel 2007
Collection Sésame – Editions ENI
152 pages – 5,79 €.

Comme l’indique le nom de la collection, il s’agit d’un ouvrage d’introduction. On y trouve de nombreux renseignements généraux pour l’utilisation d’Excel en tant que super-calculette. N’attendez pas d’y trouver la moindre explication sur des fonctions « compliquées » comme Recherche, Index ou Equiv. C’est à peine si les fonctions Max, Min et Si sont abordées. En revanche, vous saurez modifier la mise en page, jouer avec les bordures et les motifs, insérer un objet graphique…

Pour ces deux ouvrages des Editions ENI, vous pourrez en savoir plus en consultant directement le site de la société, grâce au pointeur placé dans le bandeau droit de ce blog.

Analyse marketing et reporting avec Excel
Ivana Taylor et Bill Jelen – Eyrolles
234 pages – 29,00 €

Voici un ouvrage qui se distingue des ouvrages usuels sur Excel : il s’intéresse tout particulièrement à l’analyse marketing et au reporting. On y trouve donc des explications sur l’importation de données, l’utilisation de la fonction RechercheV, les filtres, les extractions, les tableaux croisés dynamiques. Un certain nombre de pages sont aussi consacrées à des conseils plus généraux sur la façon de gérer des fichiers clients, sur l’étude de votre marché…

Une autre façon de progresser...

La façon la plus rapide de progresser dans votre maîtrise d'Excel est de vous inscrire à la formation "Modélisation avec Excel" que j'anime à Paris le jeudi 6 et le jeudi 13 mars (cf. bandeau à droite du blog). En deux jours, un utilisateur "lambda" d'Excel y devient un "power user".

Un message dans la barre d’état

Quand on effectue par macro un traitement assez long, il est parfois nécessaire de rassurer l’utilisateur sur le bon fonctionnement de la macro.

Supposons par exemple que l’on effectue un traitement ligne par ligne, que le numéro de la ligne courante porte dans la macro le nom Lig et le nombre de lignes à traiter le nom nb_Lig. Il serait utile de pouvoir afficher dans la barre d’état un message du type : « Traitement de la ligne 23 sur 1450 lignes ».

Cela s’obtient grâce au code suivant :
Application.Statusbar = "Traitement de la ligne " & Lig & " sur " & nb_Lig & " lignes"

Une fois le message de la barre d’état ainsi personnalisé, il prend le pas sur les messages qu’Excel y affiche normalement.

Il est donc nécessaire, dès que la macro est terminée, de rétablir le fonctionnement normal de la barre d’état. Cela s’obtient avec l’instruction :
Application.Statusbar =False

Remarque – Ces messages personnalisés dans la barre d’état sont d’autant plus utiles que, dans un souci esthétique et de rapidité, on bloque fréquemment l’actualisation de l’écran durant l’exécution d’une macro. Heureusement, l’affichage de la barre d’état n’est pas concerné par ce blocage.

Encore un bug : 6 + 0 = 7 !

Dans le menu contextuel de la barre d’état, que nous avons vu il y a quatre jours, il y a un bug dans l’option « Compteur ». Comme le montrent les figures suivantes :
■ en A, on a sélectionné une colonne de six éléments ;
■ en B, on a sélectionné une colonne avec un élément ;
■ en C, on a sélectionné un bloc contenant sept éléments.

Et pourtant, le compteur, dans la barre d'état, n’affiche rien dans le cas de la figure B.

Alors que, en figure C, nous constatons que le septième élément apporté par la colonne I est bien reconnu !

Doit-on en déduire que Bill Gates a parfois du mal à reconnaître l’existence du Macintosh ?

Non, c’est tout simplement un bug dans le compteur qui ne compte rien quand il y a un élément et un seul dans la sélection, que cet élément soit numérique ou textuel.

C’est très dangereux car – pour ma part – j’utilise ce compteur pour savoir quand une colonne ne contient rien, auquel cas je sais que je peux la supprimer.

La parade consiste donc à faire ce que j’ai fait entre les figures A et C : sélectionner un bloc contenant des éléments puis voir – quand on ajoute une (ou plusieurs) colonne(s) à la sélection, si cela modifie le compteur. Si le compteur n’est pas modifié, ces colonnes sont vraiment vides et on peut les détruire…

Barre d’état : menu contextuel

La barre d’état d’Excel est la partie inférieure de la fenêtre qui – si tout va bien – affiche à gauche « Prêt » pour vous dire qu’Excel est à vos ordres. Dans sa partie droite, elle n’affiche rien de particulier (si ce n’est NUM ou MAJ selon les réglages de votre clavier) mais, si vous faites un clic droit n’importe où dans cette barre d’outils, vous obtenez le menu contextuel reproduit dans l’écran ci-dessous.

Ce menu contextuel vous permet, pour les cellules sélectionnées lors de l’appel du menu, de ne rien afficher (Aucun), d’afficher la moyenne des valeurs concernées, ou le compteur – la fonction NbVal() –, ou les chiffres – la fonction Nb() –, ou le maximum, ou le minimum, ou enfin la somme.

Remarque 1 – Nous rappelons au passage que les premiers traducteurs français d’Excel se sont complètement mélangés les pinceaux en baptisant Nb() la fonction qui compte le nombre de valeurs et NbVal() celle qui compte le nombre de cellules occupées (valeur ou texte). Depuis plus de 20 ans – comptabilité ascendante oblige – nous en subissons les conséquences…

Remarque 2 – La somme de 83.436 affichée dans la barre d’état vous semble peut-être un peu forte au vu des valeurs des cellules sélectionnées. Rassurez-vous, ce sont tout simplement les cellules C2:D2 qui provoquent ce problème puisque les dates – dans Excel – sont des nombres, ainsi que vous le rappellera mon savoureux message « La grand-mère de Bill Gates » du 14 décembre 2005.

Récupérez le nom de la feuille

Nous avons découvert la fonction cellule() le 10 décembre 2006 et, quatre jours plus tard, nous avons vu comment – grâce à cette fonction – récupérer en une formule unique l’un quelconque des renseignements suivants : nom du classeur, nom du chemin ou nom de la feuille.

Aujourd’hui, nous nous concentrons sur la seule récupération du nom de la feuille. Nous avons en colonne A les formules qui ont été reproduites en colonne B sous la forme de texte :

La feuille actuelle est la feuille « Noms », la feuille « Données » étant une autre feuille dans laquelle nous avons fait une modification juste avant de revenir dans la feuille « Noms ».

Pour information, le résultat sur mon ordinateur de la formule =cellule("nomfichier";$A$1) est : D:\Excel\Blog\Blog 2008 1-2\[Nom feuille.xls]Noms

Nous constatons – comme nous l’avions vu en fin 2006 – que si la fonction cellule() ne possède pas de second argument, ce n’est pas le nom de la feuille active que l’on retrouve, mais plutôt celle dans laquelle la dernière modification a eu lieu.

Remarque 1 – Nous voyons aussi (A1 et A2, ou bien A3 et A4) que l’on peut indifféremment utiliser « filename » ou « nomfichier » comme argument, l’avantage du terme en anglais étant que la fonction marchera dans toute version d’Excel, et non dans la seule version française.

Remarque 2 – La formule que nous proposons aujourd’hui (en A1) est nettement plus légère que celle proposée le 14 décembre 2006.