Monsieur Excel
Pour tout savoir faire sur Excel !

26 février 2016

Créer un « zonage » des lignes

Dans l’informatique traditionnelle, on utilisait pour l’impression du papier « zoné », dans lequel la couleur des lignes était différente une ligne sur deux. Ce type de papier permettait de mieux suivre les valeurs affichées dans l’ensemble d’une ligne donnée.

Certes, on pourrait sélectionner le bloc à zoner et utiliser la commande « Mettre sous forme de tableau » de l’onglet « Accueil ». Mais, comme nous l’avons déjà commenté dans plusieurs articles, cela présente trois inconvénients :
  • primo, nous obtenons des menus déroulants que nous n’avons pas demandés ;
  • secundo, on perd l’indépendance précieuse des lignes et des colonnes s’il y a au moins deux tables partageant des lignes ou des colonnes ;
  • tertio, la dernière ligne ou la dernière colonne peut ne pas respecter ce formatage.
Il y a une solution bien plus simple : créer un format conditionnel. Nous voyons le résultat dans la copie d’écran ci-dessous avec, en insert, la définition de ce format conditionnel.




20 février 2016

Un bug de macro enregistrée...

Nous l’avons déjà dit à plusieurs reprises dans ce blog, il y a depuis toujours dans Excel des bugs liés aux macros enregistrées. Mais la situation empire version après version, car de nombreuses nouvelles ou récentes fonctionnalités d’Excel ne sont pas intégrées aux macros enregistrées.

Un très bel exemple de dysfonctionnement de l’enregistrement de macro vous a déjà été fourni dans l’article « ″Convertir en plage″ en VBA » du 26 octobre 2009. Dans ce cas, on voit que la macro enregistrée a totalement ignoré une commande pourtant enregistrée apparemment sans problème.

Par ailleurs, le code des macros enregistrées est très souvent abusivement verbeux. Nous en avons vu un exemple notable dans l’article du 12 juillet 2013 où la macro enregistrée faisait 53 lignes (et en plus ne marchait pas quand on la rejouait !) là où deux lignes auraient suffi.

En conclusion, il y a trois types de problèmes : certaines commandes ne s’enregistrent pas, le code est très verbeux, et enfin la macro – quand on l’exécute – ne fait pas la même chose que lors de son enregistrement…

Voici une série d’articles illustrant chacun les deux derniers problèmes :

7 juillet 2013 – Bug de la validation par liste : une validation par liste avec une liste d’options proposée en direct ne fonctionne plus quand on exécute la macro.

12 et 17 juillet 2013 – Bug dans la mise en page : la définition d’un pied de page ne marche plus quand on exécute la macro enregistrée.

27 juillet 2013 – Un bug dans la MAJ de dates : la conversion de dates ne fonctionne plus quand on exécute la macro enregistrée.

Vous trouverez une récapitualition d’un certain nombre de problèmes dans :
Les divers bugs des macros enregistrées – 2 août 2013

Je suis tombé récemment sur un nouveau bug de macro enregistrée. On enregistre une macro pour donner à une cellule un format conditionnel. Vous trouverez ci-dessous la macro enregistrée, ainsi la macro écrite à la main qui – elle – fonctionne parfaitement bien.


Quand on exécute la macro enregistrée, elle bloque sur l’instruction ExecuteExcel4Macro. On a bien du mal à comprendre ce que cette instruction vient faire ici, d’autant plus qu’elle « plante » la macro lors de l’exécution. 

15 février 2016

Deux livres sur le VBA Excel 2016

Aujourd'hui, nous vous présentons deux livres récents sur le VBA d'Excel 2016...


« VBA Excel 2016 » par Michèle Amelot – Editions ENI – 462 pages – 29,90 €
Sous-titre : « Programmer sous Excel : Macros et langage VBA »

Un ouvrage clair et assez exhaustif sur le VBA Excel 2016, en 14 chapitres bien clairs se terminant sur une mini-application pour la réalisation de devis. Selon la bonne habitude des Editions ENI, on peut accéder sur leur site à l’ensemble des exemples de ce livre. L’ouvrage est assez exhaustif : il y a par exemple un chapitre entier consacré à la communication avec les applications Office 2016, un chapitre sur Internet et un chapitre sur la programmation Windows.

La seule chose que je n’y ai pas trouvée, et que j’aimerais pourtant trouver – ce  qui est hélas très rarement le cas – dans la plupart des livres sur Excel, serait un préambule présentant en une page ou deux ce qui différencie la version 2016 du VBA des versions antérieures.

« VBA Excel 2016 » par Claude Duigou – Editions ENI – 379 pages – 27,00 €
Sous-titre : « Créez des applications professionnelles »

C’est une chose d’apprendre le VBA, c’en est une autre de le pratiquer… C’est à cela que Claude Duigou, un lecteur régulier de ce blog, qu’il enrichit d’ailleurs souvent de ses commentaires, s’attaque dans cet ouvrage. Il y a là 21 chapitres : Procédures – Variables, constantes et types de données – Opérateurs – Structures de contrôle – Tableaux – Introduction à la programmation objet – Classeurs – Feuilles de calcul – Cellules et plages – Graphiques – Echanges de fonctions entre Excel et VBA – Boîtes de dialogue standards – Formulaires – Contrôles – Ajout d’objets liés et incorporés – Collaboration avec les applications Microsoft – Programmation Web – Programmation du format XML – Programmation système Windows – Matrice et TCD – Outil Power Query.

Comme vous pouvez le constater, c’est assez exhaustif ! En tout, 172 QCM, 226 travaux pratiques avec leurs corrigés en fin d’ouvrage, soit au total 36 heures de travaux pratiques. Avec bien entendu, les fichiers disponibles sur le site ENI.

08 février 2016

Quelques raccourcis précieux…

Quand je travaille chez mes clients, je suis toujours surpris de constater que des cadres utilisant Excel de façon quotidienne – et ce souvent depuis des années – ignorent encore certains raccourcis pourtant particulièrement utiles. Voici une récapitulation (non exhaustive, forcément) de raccourcis précieux.

Couper, Copier et Coller

Notre première série de raccourcis est un grand fondamental, et pas seulement pour Excel : [Ctrl]-x pour Couper, [Ctrl]-c pour Copier et [Ctrl]-v pour Coller. Là, heureusement, rares sont ceux qui ne les connaissent pas !                      

Déplacement dans un bloc

A l’opposé, le raccourci suivant est inconnu de la majorité des utilisateurs : [Ctrl]-[Maj]-flèche, quand la cellule active est à l’intérieur d’un bloc, sélectionne toutes les cellules de la cellule active à celle placée à la fin du bloc dans le sens de la flèche.

Sélection de tout un bloc

Le raccourci suivant (au moins l’un des deux) est connu de la plupart des utilisateurs : [Ctrl]-* ou [Ctrl]-a pour sélectionner tout le bloc auquel appartient la cellule active.

Remarque – Il y a là un encouragement à concevoir votre modèle avec des blocs bien séparés les uns des autres. Si certains de vos blocs se touchent, ce raccourci ne pourra plus sélectionner un bloc sans l’autre !


Par défaut, quand on construit une formule, les références sont relatives (sans « $ »). Rappelez-vous que les américains nous ont imposé (dans Excel) que le « $ » soit le symbole d’une valeur immuable… Lors de la construction d’une formule, la touche [F4] transforme les coordonnées actives en – successivement – ligne bloquée, colonne bloquée, tout bloqué, tout relatif.

Remarque – Dans l’édition d’une formule, la touche [F4] vous permet, si nécessaire, de « dollariser » d’un coup plusieurs arguments ou même la formule entière.

Sommation rapide

Encore un raccourci utile mais peu connu : [Alt]-= joue exactement le même rôle que la touche S. L’intérêt est que, quand on développe un modèle rapidement avec les deux mains sur le clavier, ce raccourci est plus rapide que de devoir quitter le clavier, prendre la souris et cliquer sur la touche S.

Ce que peu d’utilisateurs en revanche savent, à ce sujet, est que l’on peut d’un coup d’un seul, avec le raccourci-clavier ou la touche, faire à la fois les totaux à droite et les totaux en bas pour un bloc, à condition bien entendu d’avoir aussi auparavant sélectionné la colonne vide à droite et la ligne vide en bas.

Collage spécial en accès direct

Encore un raccourci peu connu ! Quand vous avez copié un bloc, vous pouvez ensuite sélectionner une destination puis appeler directement la commande « Collage spécial » en faisant  [Ctrl]-[Alt]-v.

Affichage/Fermeture des filtres en direct

Encore un raccourci peu connu ! Pour afficher ou faire disparaître les filtres, il suffit de faire [Ctrl]-L.

Notez que le L est en majuscules, il vous faut donc trois doigts, ou alors un doigt à cheval sur [Ctrl] et [Maj]…   

Edition rapide d’une cellule

Tout le monde connaît le couble clic sur une cellule pour en afficher la formule et éventuellement l’éditer. Mais il est possible d’obtenir le même résultat avec [F2], ce quoi est plus pratique si vous avez déjà les deux mains sur le clavier.

Remplissage automatique

Vous avez sélectionné un bloc vertical ou un bloc horizontal dont la première cellule contient la formule ou valeur originale. Pour recopier cette cellule vers le bas, faites [Ctrl]-b. Pour recopier cette cellule vers la droite, faites [Ctrl]-d.

Si maintenant deux cellules au début du bloc contiennent des valeurs, la recopie continuera la série avec des valeurs de même écart…

Saisie multiple

Vous avez sélectionné un vecteur horizontal ou vertical, ou même une matrice. Vous entrez une valeur, ou une formule. Si vous validez avec [Ctrl]-[Entrée], la formule est entrée dans l’ensemble du bloc. Cela revient au même résultat qu’un copier suivi d’un collage spécial des seules formules…


02 février 2016

Liste des onglets avec liens

Nous découvrons aujourd’hui un petit utilitaire bien pratique proposé par Jeff Robson, d’Access Analytics, et un peu modifié par mes soins.

Le but est de disposer dans un classeur d’une liste des onglets du classeur, chaque nom d’onglet ayant un lien hypertexte vous permettant d’y aller directement.

Commençons par ajouter un onglet baptisé « Plan » et entrons « Onglets du classeur » en A1. C’est plus logique de placer cet onglet en tête du classeur, mais rien ne vous y oblige.

Faites un clic droit dans le nom de cet onglet et activez la commande « Visualiser le code ». Il ne vous reste plus qu’à entrer le code ci-dessous pour que tout soit prêt. Quittez alors VBA et, dans le classeur, cliquez sur le nom d’un autre onglet, puis sur celui de l’onglet « Plan ».

Et le tour est joué !

Vous avez maintenant la liste des onglets du classeur, avec des liens hypertexte…