Validation des données (a)

Il me semble utile de consacrer quelques articles à la validation des données. Je constate en effet souvent que les cadres dans les entreprises ne les utilisent pas à la hauteur de leur immense potentiel. Dans ce premier article, nous verrons comment créer une validation à partir d’une liste déroulante dynamique.

Nous avons en colonne E une liste de continents. Nous voulons que cette liste soit proposée en B2 dans une liste déroulante. Mais nous voulons aller plus loin : il faudrait que, si l’on ajoute ultérieurement un continent – par exemple l’Antarctique – ce nouveau continent soit aussitôt proposé dans la liste déroulante en B2.

Pour créer une liste dynamique, il faut d’abord créer un nom dynamique. Créons donc le nom dynamique Continents en lui attribuant la définition :

=$E$1:decaler($E$1;nbval($E:$E)-1;0)

Il ne reste alors plus qu’à sélectionner la cellule B2 et à lui attribuer, via la commande « Validation des données » de l’onglet Données, comme dans la copie d’écran, une validation par liste à partir du nom dynamique.

Déroulez B2, et vous avez le choix entre les cinq continents actuels. Ajoutez Antarctique en E6, redéroulez B2, et vous avez à présent le choix entre six continents !

Voilà ! Vous venez de créer un menu déroulant dynamique !

L’erreur de traduction sur nb() et nbval()

Quand est sortie la première version d’Excel avec nb() et nbval(), j’ai aussitôt informé Microsoft France qu’il y avait un problème grave : la personne qui avait traduit le nom de ces deux fonctions n’avait rien compris à leur rôle !

En effet, en toute logique, nbval() devrait compter, dans une sélection, le nombre de cellules contenant des valeurs, et nb() le nombre de cellules non vides (valeurs ou texte), à l’instar des fonctions originales count() et counta() (pour Count All). Manque de bol, le traducteur original a mélangé les deux fonctions, et c’est l’inverse qui se passe.

Quand j’ai informé Microsoft France du problème, ils m’ont répondu que, pour des raisons de « compatibilité ascendante », il n’était pas possible de corriger cette erreur. C’est pour cela que, depuis près de 30 ans, et ce jusqu’à la fin d'Excel, ces deux fonctions sont nommées de travers dans la version française d’Excel. Triste, n’est-ce pas ?

VBA pour Excel (Editions ENI)

Nous venons de recevoir de la part des Editions ENI le livre VBA pour Excel (versions 2019 et Office 365) écrit par Claude Duigou, un lecteur assidu de ce blog. C’est un livre de 387 pages vendu à 27 €. On peut télécharger sur le site de l’éditeur près de 17 Mo de fichiers préparés par l’auteur.

Le livre comporte 172 QCM corrigés en fin d’ouvrage ainsi que 231 travaux pratiques avec leurs corrigés, ce qui devrait assurer 37 heures de mise en pratique. La table des matières à elle seule occupe 18 pages, autant dire qu’il y a du pain sur la planche. Mais, après une semaine de travail, vous maîtriserez enfin le VBA !

Pour ma part, j’ai particulièrement apprécié le fait qu’il ne s’agit pas d’une formation avec une lecture linéaire, mais que l’on y trouve une grande richesse d’information tout en résolvant – au pas à pas – un certain nombre de problèmes. Le va-et-vient entre les énoncés de problèmes à résoudre et les solutions proposées est intéressant, et plus motivant que de la lecture linéaire.

Le seul inconvénient de ce procédé pas à pas, construit dans un souci de clarté pédagogique, est que cela aboutit parfois à du code plus lourd. Ainsi, pour connaître le nombre de feuilles de calcul dans le classeur actif, la solution proposée est :

Sub Nombrefeuilles

Dim intNombre as Integer

intNombre=Sheets.Count

MsgBox ʺIl y a ʺ & intNombre & ʺ feuilles dans le classeur. ʺ

End Sub

… là où j’aurais tout simplement écrit :

Sub Nombrefeuilles

MsgBox ʺIl y a ʺ & Sheets.Count & ʺ feuilles dans le classeur. ʺ

End Sub

Mais c’est le prix à payer pour la clarté pédagogique…

En conclusion, un livre que je vous conseille !

Sélection d’une image à afficher (c)

Nous avons eu trois commentaires sur cette série d’articles.

Le premier est dû à Philippe qui nous dit : « Bonjour, il y a moyen d'éviter la macro en passant par une image définie par une plage nommée alimentée par un décaler : https://www.cjoint.com/c/IClu5CcLYTd

Sur cet article, vous trouvez un bouton pour télécharger le fichier IClu5CcLYTd_rech-image.xlsx dans lequel vous pouvez voir la solution mise en œuvre.

Cela fonctionne très bien, avec un inconvénient : vous devez avoir les différentes images visibles dans le classeur et vous ne voyez pas, comme dans ma solution, uniquement l’image demandée.

Une solution du même type est proposée par Arnaud Cottin, avec le même inconvénient mais aussi, à titre de compensation, le fait qu’il n’est plus nécessaire de passer par le VBA.

Enfin, il y a une proposition d’un habitué de notre blog, cduigou, qui passe aussi par le VBA mais en allégeant la solution que je proposais. Voici ce qu’il suggère :

Une autre solution consiste à nommer les images (Zone des noms) dans la feuille de calcul en utilisant les libellés exacts présents dans la liste de choix de la cellule C1. On se passe ainsi de l'index, des fonctions EQUIV, DECALER, etc... pour ne garder que les noms. Le choix dans la collection Pictures se fait ainsi par le nom au lieu de l'index.

La procédure événementielle devient :

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$C$1" Then
      Me.Pictures.Visible = False
      Me.Pictures(ActiveCell.Value).Visible = True
   End If
End Sub

Sélection d’une image à afficher (b)

Certes, si vous avez essayé de résoudre le problème que nous vous avons posé dans le dernier article sans passer par le VBA, il est normal que vous n’ayez pas trouvé de solution !

Dans la cellule E1, nous avons entré la formule suivante, qui calcule le numéro de l’image sélectionnée via le menu déroulant en C1 : =equiv(C1;F:F;0).

Remarque 1 – Comme nous avons pris toute la colonne F en argument, nous ne serons pas limités dans le nombre d’images utilisables.

Remarque 2 – Attention ! Le numéro de l’image ne correspond pas à celui affiché dans la zone « Nom » de la barre d’édition, mais à l’ordre d’apparition parmi les images actuelles de la feuille de calcul.

La solution à notre problème revient donc à créer une macro événementielle, liée à l’onglet « Image », celui qui nous intéresse, et qui se déclenche chaque fois que la valeur de la cellule C1 (le menu déroulant) est modifiée.

Il y a dans la liste du menu déroulant en haut à droite du VBA 17 événements auxquels on peut associer une macro, l’option Change que nous utilisons ici n’étant qu’une des possibilités pour la feuille de calcul active (Worksheet).

Sélection d’une image à afficher (a)

Nous reprenons aujourd’hui le principe d’un article publié dans ce blog il y a plus de 12 ans.

De façon surprenante, malgré les années qui sont passées depuis, je pense qu'il n’y a probablement pas un utilisateur d’Excel sur 50 qui saurait faire cela tout seul…

Le but est d’afficher dans un tableau Excel l’image correspondant à ce qui a été sélectionné dans une liste déroulante. 

Comme nous le voyons dans la copie d’écran ci-dessous, vous obtenez l’image de la grenouille quand vous la sélectionnez dans la liste déroulante de la cellule C1 et celle du cheval quand vous prenez la troisième option de la liste.

Voyez l’intérêt de cette fonctionnalité. Vous pouvez ainsi afficher à volonté la photo d’une personne dans une liste de connaissances, ou parmi les salariés de votre département ou même de toute votre entreprise.

Saurez-vous faire cela avant la parution de mon prochain article ?