Validation avec formule matricielle

Bill Jelen, un expert d’Excel,est tombé récemment sur un bug original que vous pouvez aisément reproduire.

En A1, activez la commande « Validations des données », onglet « Données », déroulez la commande « Autoriser » jusqu’à « Personnalisé » et entrez la formule : =nbcar(A1)-somme(--non(estnum(--stxt(A1;ligne(indirect(1&":"& nbcar(A1)));1))))<=0

Cette formule, nous verrons plus loin de quelle façon, aboutit au résultat « FAUX » si la cellule contient au moins un chiffre. S’il ne s’agit que de texte, le résultat est « VRAI » et est donc accepté par la validation.

Entrons « ABC » en A1. C'est accepté ! Entrons maintenant « A23 » et c’est refusé. Tout va donc bien.

Enregistrons le fichier et fermons-le. Ensuite, nous le rouvrons et essayons de nouveau d’entrer « ABC » en A1. Hélas, à présent, Excel refuse la saisie !

La seule façon de faire remarcher la validation est de la redéfinir, tout simplement en la revalidant…

Après diverses expérience, il semble donc que ce problème apparaît quand la formule utilisée pour la validation est une formule de type matriciel, même si elle n’a pas été saisie en tant que telle – donc avec avec [Ctrl]-[Maj]-[Entrée] – lors de la définition de la formule de validation. Ce qui ne l’avait pas empêché de fonctionner avant la sauvegarde initiale du fichier.

Comment fonctionne donc la formule de validation ?

Supposons que A1 contienne le texte « ABCD ».

LIGNE(INDIRECT(1&":"&NBCAR(A1))) donne {1;2;3;4}

STXT(A1;{1;2;3;4};1) donne {"A";"B";"C";"D"}

ESTNUM(--{"A";"B";"C";"D"}) donne {FAUX;FAUX;FAUX;FAUX}

--NON({FAUX;FAUX;FAUX;FAUX}) donne {1;1;1;1}

NBCAR(A1)-SOMME({1;1;1;1}) donne 0

Et =0<=0, enfin, donne VRAI

Si l’une des lettres était remplacée par un chiffre, il y aurait un VRAI dans la série des FAUX, et l’on obtiendrait au final 1, qui n’est pas inférieur ou égal à 0, donc un résultat FAUX pour la validation.