Monsieur Excel
Pour tout savoir faire sur Excel !

28 juin 2012

Pop-up : une seule exécution...

Dans l’article publié il y a quatre jours, nous avons constaté que la macro appelée par la commande choisie dans le menu pop-up s’exécutait deux fois au lieu d’une…

Mon collègue Excel-omane et ami François Sermier s’est penché sur ce problème et en a tiré les conclusions suivantes, que j’ai enrichies de mes propres commentaires…

On trouve une réponse sur le forum :

Apparemment, quand on définit une fonction « OnAction » avec paramètres, on ne peut pas utiliser la notation classique Macro(param) : il faut obligatoirement utiliser le passage d'arguments sans parenthèses Macro param. Et, comme cela doit être inclus dans des guillemets de texte, cela ne marche pas avec le code proposé dans l’article précédent.

Il faut donc utiliser une kyrielle de guillemets simples et doubles, comme indiqué dans le forum.

Voici dont les cinq lignes qui doivent remplacer, dans la macro publiée il y a quatre jours, les cinq commandes  « OnAction » :
            .OnAction = "'Macro """ & 1 & """'"
.OnAction = "'Macro 2'"
.OnAction = "'Macro 3'"
.OnAction = "'Macro 4'"
.OnAction = "'Macro 5'"

Pour la première ligne, la formule est plus lourde, mais elle permet de remplacer le « 1 » par une formule afin de rendre le paramètre réellement modifiable. Pour les macros 2 à 5, l’argument numérique est saisi en dur, mais – du coup – la formule est plus légère.

Pour améliorer la lisibilité de la première formule, voici la décomposition, caractère par caractère, de l’argument utilisé dans la première commande :

Avec ces changements, le menu pop-up fonctionne parfaitement bien, les macros ne s’exécutant plus qu’une seule fois chacune.

Pourquoi Excel doublait-il l’affichage du dialogue ?

On peut imaginer que c’était dû à la façon dont l'analyseur syntaxique d'Excel (le parser) analyse la chaîne de caractères passée en argument de la commande « OnAction ». Le mélange des quotes simples et quotes doubles tendrait à prouver qu'il s'agit d'un bricolage monté en catastrophe à l’origine du Visual Basic et que tout ça est en fait assez salopé dans le code original de Microsoft !

Ou, autre option, à la façon dont la boucle d’événement gère l’interception du déclenchement de l’événement. En navigant dans les interrogations des forums, on voit qu’il y a une autre occasion où l’affichage du dialogue a lieu deux fois, c'est quand on utilise la propriété Tag du CommandBarButton. Il est possible que Microsoft utilise ce mécanisme du Tag pour déclencher le OnAction. Mais allez savoir…

Nous sommes preneurs de commentaires par des lecteurs ou lectrices qui pourraient enrichir ce débat !


24 juin 2012

Création d’un menu pop-up

Un menu pop-up est un menu qui apparaît dans une fenêtre qui s’affiche à l’écran. Il est assez facile de créer un menu pop-up avec, le cas échéant des sous-menus.


Dans l’exemple ci-dessous, on lance le test de ce menu pop-up en cliquant dans le bouton macro éponyme placé dans le coin supérieur gauche de la feuille. On pourrait bien entendu le lancer aussi via un raccourci comme par exemple [Ctrl]-p

Voici le code qui permet de définir ce menu pop-up et de gérer ses appels de macros liés au choix de l’utilisateur.

Option Explicit
Public Const NomMenu As String = "MonPopUp"
Sub DeletePopUpMenu()
    ' Efface le pop-up s'il est encore présent
    On Error Resume Next
    Application.CommandBars(NomMenu).Delete
    On Error GoTo 0
End Sub
Sub CreateDisplayPopUpMenu()
    ' Efface tout menu pop-up
    Call DeletePopUpMenu

    ' Crée le menu popup
    Call Custom_PopUpMenu

    ' Affiche le menu popup
    On Error Resume Next
    Application.CommandBars(NomMenu).ShowPopup
    On Error GoTo 0
End Sub
Sub Custom_PopUpMenu()
    Dim MenuItem As CommandBarPopup
    ' Ajoute le menu pop-up
    With Application.CommandBars.Add(Name:=NomMenu, Position:=msoBarPopup, _
         MenuBar:=False, Temporary:=True)

        ' Ajoute les deux premiers boutons
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Bouton 1"
            .FaceId = 71
            .OnAction = "Macro(1)"
        End With

        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Bouton 2"
            .FaceId = 72
            .OnAction = "Macro(2)"
        End With

        ' Ajout du sous-menu
        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
        With MenuItem
            .Caption = "Mon sous-menu à moi"

            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Bouton a dans le sous-menu"
                .FaceId = 71
                .OnAction = "Macro(3)"
            End With

            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Bouton b dans le sous-menu"
                .FaceId = 72
                .OnAction = "Macro(4)"
            End With
        End With

        ' Ajout du dernier bouton
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Bouton 3"
            .FaceId = 73
            .OnAction = "Macro(5)"
        End With

    End With
End Sub
Sub Macro(i As Integer)
    Dim Mess(5) As String
    Mess(1) = "Bouton 1": Mess(2) = "Bouton 2": Mess(3) = "Bouton 2a"
    Mess(4) = "Bouton 2b": Mess(5) = "Bouton 3"
    MsgBox "Votre sélection : " & Mess(i)
End Sub

Remarque – Le seul petit problème que j’ai encore est que le dialogue affiché par la macro apparaît deux fois de suite… Nous étudierons ce problème dans le prochain article.

Les commandes “.FaceId = …” ne servent qu’à afficher les numéros à gauche des noms des commandes. Vous pouvez les éliminer sans problème.

L'auteur de la version originale de cette macro, que j'ai ensuite francisée et simplifiée, est Ron de Bruin, un collègue MVP Excel.

20 juin 2012

Un vecteur de valeurs disjointes

Vous avez déjà probablement remarqué que l’on peut, dans une cellule, utiliser comme formule l’adresse d’un vecteur, ou celle d’une matrice.

Dans l’exemple ci-dessous, nous avons entré en C5:C7 des formules dont le texte a été reproduit en colonne D.

Nous constatons que la cellule C5, qui fait référence à C1:C4, renvoie l’erreur #VALEUR !

Ce résultat est tout à fait normal. Si cette formule était utilisée dans une cellule des lignes 1 à 4, elle renverrait la valeur du vecteur C1:C4 qui se trouve dans la même ligne. Quand on utilise C1:C4 dans une formule, en dehors des lignes 1 à 4, on ne peut – sans erreur – l’utiliser que de façon collective comme par exemple dans les formules =moyenne(C1:C4) ou =index(C1:C4;B1).

Quand on évalue la cellule C5, on obtient le résultat affiché en E5. Le résultat de la formule est donc bien le vecteur des quatre valeurs de C1:C4.

Si l’on essaye en revanche d’adresser – de la même façon – un ensemble de cellules non contiguës, comme nous l’avons fait en C6, on obtient le même résultat #VALEUR ! dans l’affichage de la cellule C6. Mais – en revanche – l’évaluation n’affiche plus un résultat vectoriel mais encore le même message d’erreur !

Autrement dit, on ne peut pas, avec la formule =A1;A3;A5 créer un vecteur de trois valeurs.

Question : Est-il possible de créer un vecteur à partir de cellules non contiguës ?
  
Réponse : Oui, c’est possible, à condition comme nous l’avons fait dans la cellule C7, de passer par la fonction indirect(). Nous constatons en E6 que le résultat désiré a été bien obtenu !

16 juin 2012

Un test pour les génies d'Excel !

Après l’article « Etes-vous dans le top 1% d'Excel »  du 9 mai 2012, voici encore un test pour les petits génies en Excel !

Le problème que je vous pose aujourd’hui était un challenge publié dans le site MrExcel (mon cousin en quelque sorte, mais un peu plus âgé – si c’est possible !). Voici l’adresse du site :

Vous disposez en B2:C4 d’une liste de services dans votre entreprise avec, pour chacun d’entre eux, votre contact.

En A7:A8, vous avez une liste de projets en cours. Pour chacun d’entre eux, vous devez afficher en colonne B le contact correspondant, celui-ci étant identifié par la présence dans le nom du projet du département auquel il appartient.

Trouvez la formule la plus courte possible en B7 pour obtenir le bon résultat.

Essayez vraiment avant de lire la suite de cet article…

Pour vous écœurer un peu, je vous informe que la formule proposée plus bas tient en 48 caractères d’imprimerie et ne requiert même pas de validation matricielle !

Cette solution a été proposée par Barry Houdini qui, comme par hasard, porte un nom de magicien…

Voici la formule de B7 : =recherche(2^10;cherche($B$2:$B$4;A7);$C$2:$C$4)

Cette formule ne tient pas compte de la syntaxe officielle de la fonction cherche(), dans laquelle le premier argument est normalement un simple texte à rechercher, donc tout sauf un vecteur...

Quand on évalue cherche($B$2:$B$4;A7), on obtient {#VALEUR!;6;#VALEUR!}. Ce qui explique pourquoi le bon résultat est finalement obtenu…

Avec 2^10 comme premier argument de la fonction recherche(), on se donne la possibilité d’utiliser une table dans laquelle on recherche avec des cellules comportant 1.023 caractères au maximum. Si ces cellules devaient être plus longues, il suffirait d’utiliser une puissance de 2 supérieure à 10…

Cette brillante solution prouve – une fois de plus – qu’une parfaite connaissance des algorithmes sous-jacents aux fonctions permet de découvrir des utilisations inédites de ces fonctions. C’est le cas, dans cet exemple, à la fois pour la fonction cherche() – avec son premier argument vectoriel – et pour la fonction recherche() qui – dans sa syntaxe à deux vecteurs – suppose en général que le premier vecteur soit classé en valeurs croissantes.

12 juin 2012

Utilisez la fonction Trouve()

Nous avons vu, il y a quatre jours, comment fonctionnait la fonction cherche(), en notant d’ailleurs qu’elle ne faisait aucunement la distinction entre les majuscules et les minuscules.

En fait, Excel dispose d’une fonction tout à fait similaire qui – elle – fait très bien la différence entre les majuscules et les minuscules. Nous pouvons le voir dans les lignes 6 et 7 du tableau suivant, qui complète celui de mon dernier message :


La syntaxe de la fonction trouve() est exactement la même que celle de la fonction cherche(), avec un troisième argument facultatif identifiant la position à partir de laquelle on souhaite débuter la recherche.
Cette fonction est vraiment mal nommée : trouve(). Il est en effet totalement ridicule d’appeler cherche() et trouve() deux fonctions qui – à un détail près – jouent le même rôle !

Ceci dit, pour une fois, ce n’est pas la traduction en français de la fonction qui est en cause, car en anglais on utilise Search() et Find(), qui soulèvent exactement le même problème.

M’enfin, comme dirait Gaston Lagaffe, on ne peut pas refaire le monde...

Remarque – L’article précédent et cet article sont des actualisations légères des articles publiés le 20 et le 24 août 2006. Tout ce qui a été dit à ce sujet il y a 6 ans reste donc valable… 

Comme quoi, dans le blog « Monsieur Excel », il n’y a pas grand-chose à jeter :)

08 juin 2012

Utilisez la fonction Cherche()

Nous avons déjà, dans ce blog, utilisé plusieurs fois la fonction cherche(). Les deux premières fois, c'était même durant le premier trimestre de ce blog, dans les messages du 2 et du 18 décembre 2005.

La fonction cherche() est une fonction intéressante et peu connue d'Excel.

La syntaxe de la fonction est la suivante : =cherche(texte_1;texte_2[;position])

La fonction cherche() trouve la position du premier endroit où l’on trouve la chaîne de caractères texte_1 dans la chaîne texte_2. Quand le troisième argument est présent, il indique à partir de quelle position dans texte_2 on démarre la recherche de texte_1.

Remarque 1 – Nous utilisons ici la convention informatique usuelle selon laquelle les arguments facultatifs sont présentés entre crochets.

Ne croyez surtout pas Microsoft, pour qui la syntaxe devrait être :
=cherche(texte_1;texte_2;[position])

En effet, si vous poussez l'audace jusqu'à mettre le dernier ";" sans rien derrière, cela engendre une erreur.

Remarque 2 – Cette erreur, consistant à mal placer les ";" par rapport aux crochets, Microsoft la fait dans toutes les fonctions comportant des arguments facultatifs !


Nous voyons ci-dessus comment cette fonction nous sert à identifier dans le texte de la cellule A1 la position du premier « e » - en cellule A3 – puis du second – en cellule A5. En A4, nous trouvons la position du premier « e » à partir de la onzième position. Les formules de A3:A5 ont été reproduites en B3:B5.

Remarque 3 – Notons au passage que la fonction cherche() ne fait pas la différence entre les majuscules et les minuscules. En revanche, elle est tout à fait sensible aux lettres accentuées, comme c’est aussi le cas pour les fonctions recherche() et equiv().

03 juin 2012

Les fichiers d’un répertoire

La macro listée dans le lien référencé ci-dessous est très pratique : elle liste dans un onglet appelé « Catalogue » tous les fichiers d’un classeur identifié par le chemin indiqué dans la cellule portant le nom « Classeur ». Voici le lien, qui provient de la société Erlandsen Data Consulting :

Dans l’exemple ci-dessous, la cellule nommée « Classeur » contenait le texte « D:\Excel\_Enigmes\ ». N’oubiez pas le « \ » final, sinon la macro ne marchera pas.

Cette macro appelle la macro « ListFilesInFolder Classeur », non listée ci-dessous, mais que vous trouverez dans le lien .

Je n’ai modifié que la partie de la macro avant le curseur dans la copie d’écran ci-dessous :

  
Remarque – Pour que cette macro fonctionne, il faut que vous ayez activé, dans l’éditeur Visual Basic, le menu Outils, la commande « Références » et que vous ayez sélectionné « Microsoft Scripting Runtime ».

Voici le début du résultat obtenu pour le sous-dossier « _Enigmes » du dossier « Excel » de mon disque D. Pour mieux voir cette copie d'écran, cliquez dessus.