Rappel

Dans la seconde partie de cet article, nous avons vu que avec Excel 2011, la fonction Evaluate de VBA est buguée, au point être inutilisable pour récupérer la valeur associée à un nom défini dans un classeur Excel. L’usage des noms a été montré de manière détaillée dans la première partie.

Nous avons vu également, qu’il était possible de contourner ce bug en utilisant pendant le traitement une ou plusieurs cellules pour y stocker provisoirement les noms qu’il est nécessaire d’évaluer. Toutefois, cette technique est inélégante, et nécessite de grandes précautions dans le cas où un même nom est défini sur plusieurs feuilles.

L’autre méthode est de recourir à la fonction Evaluate de l’ancien langage macro, celui qui a précédé VBA. On obtient alors un résultat correct.

 

Suite et fin

B - Utilisation de l’ancien langage macro

L’ancien langage macro est apparu avec la première version d’Excel, sortie en 1985. Il est toujours disponible dans les versions actuelles, principalement pour des raisons de compatibiité avec des développements anciens. VBA est apparu en 1993 avec Excel 5, mais la première implémentation n’était pas très enthousiasmante. Selon moi, ce n’est qu’avec Excel 97 (version pour Windows) et Excel 98 (son homologue pour Mac), que l’abandon de l’ancien langage pour VBA est devenu justifié. Pour différencier les macros écrites avec l’un ou l’autre des 2 langages qui se sont succédés, on parle dorénavant de macros Excel 4, XL4, ou XLM pour désigner celles qui ont été réalisées avec l’ancien langage macro.

Excel 4 a été la dernière version où seul était présent le langage originel. C’est à cela que XL4 fait allusion, tandis que XLM était l’extension des documents Excel contenant des macros. En effet, avec les premières versions, les classeurs n’existaient pas. Chaque feuille était alors un document Excel autonome. L’extension utilisée était « .XLS » pour les feuilles de calcul, et « .XLM » pour les feuilles macro.

Les instructions de l’ancien langage macro devaient être écrites dans des feuilles macro. Celles-ci ressemblent aux feuilles de calcul classiques, mais présentent plusieurs différences significatives :

- Par défaut, ce sont les formules, et non les valeurs qui sont affichées.
- Une cellule n’est calculée que lorsqu’une macro est lancée, et que son exécution passe par cette cellule.
- Le dialogue affiché lorsqu’on demande à insérer une fonction comporte toutes les fonctions accessibles depuis une feuille de calcul, mais également de nombreuses autres, spécifiques aux feuilles macro.

Il existe 2 types de feuilles macro, les feuilles standard, et les feuilles internationales. Les premières utilisent les fonctions dans la langue corrspondant à la version locale de l’application, et les formats définis dans les préférences système, en partiiculier le séparateur d’arguments, le point-virgule en principe en France, alors que les Américains utilisent la virgule. Les macros internationales quant à elles utilisent toujours l’anglais, et la virgule comme séparateur. Elles sont destinées à faciliter en particulier l’emploi d’une même macro par des gens travaillant dans des pays distincts.

Les feuilles internationales présentent un autre avantage : elles constituent un excellent outil de traduction. En effet, si on veut utiliser en VBA une fonction de feuille de calcul, comme EQUIV(), par exemple il faut en connaître le nom en anglais, car contrairement aux anciennes macros, VBA n’existe pas en français, italien, espagnol, …

Pour cela, il suffit d’écrire dans une feuille de calcul ou une feuille macro normale, une formule utilisant la fonction dont on souhaite connaître le nom en anglais, puis de faire un Copier / Coller sur une feuille macro internationale. On découvre alors que EQUIV() se traduit par MATCH(), ce qui n’est pas inné !

Les premières versions de VBA offraient également le choix entre l’anglais et la langue locale, mais cela a rapidement été abandonné. Certains le regrettent probablement, mais c’est plutôt une bonne chose. En effet, nous avons vu que VBA est apparu en 1993, il y a près de 20 ans. Hors du milieu universitaire, personne ou presque ne connaissait Internet, et le web était à peine balbutiant. 

Avec le développement rapide de l’accès à Internet, la situation a été bouleversée : abandonner les différents variantes linguistiques de VBA permet de faciliter grandement le partage de connaissances. Selon qu’existent pour un même élément 30 mots-clefs distincts ou 1 seul, la situation n’est pas la même. D’autant plus que l’emploi de termes anglais pour VBA, n’empêche nullement de rédiger des articles, comme cette humeur, dans une autre langue.

Là encore, malheureusement, des difficultés nous attendent. 

VBA comporte un mot-clef, ExecuteExcel4Macro, qui permet d’intégrer une instruction isolée écrite dans l’ancien langage macro ou d’appeler une macro (plusieurs instructions), toujours écrite avec le langage macro Excel 4.

Voici comment obtenir la valeur associée au nom « TrancheA » dans la feuille de calciul nommée « Moreau »

Sub TrancheAEnMacroExcel4()
    MsgBox Application.ExecuteExcel4Macro("EVALUATE(""Moreau!TrancheA"")")
End Sub 

La syntaxe est relativement délicate. Dès lors qu’on a besoin d’avoir un élément variable, il vaut mieux appeler une macro Excel 4 (plusieurs instructions). Imaginons un classeur comportant plusieurs bulletins de salaire (plusieurs salariés, ou plusieurs mois d’un même salarié).

Voici une macro Excel 4 permettant d’obtenir la valeur du Brut pour une feuille particulière :

Fig. 34 — RécupNom est une fonction écrite dans l’ancien langage macro d’Excel, qui renvoie la valeur correspondant au nom « Brut » dans la feuille passée en argument. Test est une macro permettant de s’assurer que la fonction renvoie le résultat attendu. NB - La zone de nom montre bien que la cellule A1 est nommée « RécupNom ».

 

Si on exécute la macro « Test », le résultat est s’affiche ainsi :

Fig. 35 — Le message affiché par la macro Excel 4 « Test »

 

Voici maintenant comment appeler en VBA la fonction « RécupNom », écrite dans l’ancien langage :

Function ValeurBrut(f As Worksheet)
    ValeurBrut = Application.ExecuteExcel4Macro(ThisWorkbook.Name _
    & "!RécupNom(""" & f.Name & """)")
End Function 

La fonction ci-dessus ne comporte qu’une seule instruction. Elle est écrite ici sur 2 lignes pour tenir compte de l’étroitesse de la zone de lecture de Cuk. Le « ThisWorkbook.Name » découle de ce qu’il est indispensable de fournir le nom du classeur contenant la macro appelée, même si celle-ci est située dans une feuille macro du même classeur.

Voici l’équivalent en VBA de la macro « Test » écrite en macro XL4, illustrée en figure 34

Sub Test(f As Worksheet)
    MsgBox ValeurBrut(Sheets("Feuil2"))
End Sub

Et voici le dialogue affiché :

Fig. 36 — Le message affiché par la macro VBA « Test »

 

Le dialogue est identique, à l’absence près de l’icône d’Excel, présente par défaut, avec l’ancien langage.

Devoir fournir le nom du classeur contenant la macro XL4 appelée, même si elle est dans le même classeur que le code VBA, présente un inconvénienr inattendu. Si le classeur contenant la macro XL4 se nomme par exemple « VBAetmacrosXL4pourCuk2011.xlsm », tout se passe bien. Mais il suffit de renommer « VBAetmacrosXL4pourCuk 2011.xlsm » ou « VBA et macros XL4 pour Cuk 2011.xlsm » pour que la situation se détériore : la présence d’une ou plusieurs espaces dans le nom du document contenant une macro XL4 appelée depuis VBA suffit à tout dérégler.

Selon les cas, Excel s’emmêle les pinceaux, et réclame un fichier dont le nom est tronqué et commence après la dernière espace¹ faisant partie du nom :

Fig. 37 — Le message réclamant un fichier au nom erroné, car la présence d’espaces
dans le nom d’un document contenant une macro XL4 appelée depuis VBA,
perturbe Excel, qui ne prend en compte que la dernière partie du nom du fichier.

 

C’est le cas le moins grave. Il suffit de cliquer sur le bouton Annuler, et tout rendre dans l’ordre ; alors même qu’Excel semblait réclamer un fichier au nom incorrect, il y renonce volontiers, et affiche tout de même le dialogue de la figure 36. Il suffit alors de rajouer une instruction bloquant les messages d’Excel pendant le déroulement du traitement, et tout rendre dans l’ordre :

Sub Test(f As Worksheet)
    Application.DisplayAlerts = False
    MsgBox ValeurBrut(Sheets("Feuil2"))
    Application.DisplayAlerts = True
End Sub

L’instruction « Application.DisplayAlerts = False » inhibe alors l’affichage du dialogue réclamant un fichier. Il est impératif ensuite de réautoriser immédiatement, l’affichage de messages de la part d’Excel. Le bloquage doit en effet être ponctuel, pour résoudre un point précis, mais ne pas entraîner de conséquences pour la suite. C’est ce que permet l’instruction « Application.DisplayAlerts = True ».

Cette anomalie n’est pas spécifique à Excel 2011. On peut la reproduire avec Excel 2007 pour Windows :

Fig. 38 — L’équivalent du dialogue reproduit en figure 37, avec Excel 2007 sous Windows. La même macro
fonctionne parfaitement avec un classeur nommé « MTestXLM2007.xlsm », mais entraîne l’affichage du
dialogue ci-dessus, si le classeur est renommé « M TestXLM2007.xlsm » (ajout d’une espace après le « M ».

 

Malheureusement, l’adjonction d’espaces dans le nom du document a parfois des conséquences plus graves. Il arrive qu’au lieu de simplement réclamer un document au nom erroné, le traitement s’interrompe avec un message d’erreur bloquant :

Fig. 39 — La présence d’espaces dans le nom du fichier contenant les macros XL4 provoque parfois l’affichage de ce message, qui fournit une information trompeuse.

 

Le message d’erreur affiché indique une erreru de type. Cela se produit typiquement dans VBA quand un nombre est attendu et que l’information fournie est du texte, par exemple. Cela n’a rien à voir avec la réalité, puisque renommer le fichier sans espace résoud le problème, ce qui est sans rapport avec la notion de type. Ce n’est pas la seule occasion où VBA fournit un message d’erreur trompeur. Voici un autre cas.

Il ne semble pas y avoir de règle claire, expliquant la survenue de l’une ou l’autre des 2 variantes. De toute façon, l’introduction d’espace(s) dans un nom de fichier ne devrait entraîner aucune conséquence, dès lors qu’il est fourni correctement à l’instruction VBA le réclamant. Il n’y a aucune ambiguïté sur ce dernier point, dans la mesure où dans tous les fichiers que j’ai utilisés pour les tests, le Code VBA et les macros XL4 cohabitaient dans un même classeur. Par conséquent, l’instruction « ThisWorkbook.Name » devrait être capable de fournir une information valide. Il s’agit en effet de renvoyer le nom du classeur contenant cette instruction VBA. Aucune confusion ne devrait donc être possible.

Dans tous les cas, la suppression des espaces à l’intérieur du nom du document contenant les macros XL4 résoud les problèmes, qu’il s’agisse du dialogue récllamant inutilement un fichier au nom fantaisiste, ou du message d’erreur de la figure 39.

En résumé, la fonction Evaluate de VBA est gravement buguée et ne permet en aucun cas de récupérer la valeur associé à un nom défini dans le classeur. On peut résoudre le problème en faisant appel à la fonction Evaluate de l’ancien langage macro, qui fonctionne parfaitement. Dans les cas les plus simples, inclure une instruction macro XL4 dans une ligne de VBA suffit. C’est ce qui est fait au sein de la procédure « TrancheAEnMacroExcel4 » (un peu avant la figure 34). Pour gérer des cas plus compleces, avec des éléments variables, il vaut mieux faire appel à une macro XL4, c’est-à-dire un ensemble de plusieurs instructions, écrites dans dans une feuille macro. Malheureusement, on est alors confronté à un autre bug : la présence d’espace(s) dans le nom du fichier contenant cette feuille macro perturbe VBA qui selon les cas réclame à tort un fichier, ce qu’il est aisé de contourner ou aboutit à une situation bloquante. Il est donc impératif de nommer le document concerné sans utiliser d’espace.

Nous en avons enfin terminé avec les difficultés provoquées par la fonction Evaluate de VBA dans Excel 2011. Nous allons voir qu’il existe d’autre bugs et faiblesses dans cette version, mais heureusement, aucun n’a de conséquence aussi lourde. Certains sont liés à l’adaptation à la langue française, ou concernent les racourcis clavier.

 

3 - Autres anomalies liées aux anciennes macros

A - Difficulté à obtenir une feuille macro internationale

Nous avons déjà évoquées ces feuilles. Elles avaient 2 usages très différents. Le premier était de faciliter l’emploi de macros XL4, dans un environnement international. Cet aspect est devenu secondaire. En effet, depuis maintenant de nombreuses années, pratiquement plus personne ne fait de développement avec les macros XL4. Il n’y a que pour des applications très anciennes que des besoins de maintenance peuvent encore se faire sentir.

L’autre intérêt est que lorsqu’on réalise un développement en VBA, comme dans de nombreux domaines, il n’est pas nécessaire de réinventer la roue. De nombreuses fonctions existent dans Excel. Il est possible d’y faire appel à partir de VBA, en utilsant WorksheetFunction, suivi du nom de la fonction. Malheureusement, il s’agit du nom en anglais, qui n’est pas forcément toujours connu d’un utilisateur non anglophone.

Il suffisait alors d’écrire dans une feuille de calcul (ou dans une feuille macro classique) une formule quelconque utilisant la fonction à utiliser dans VBA, puis de faire un Copier/Coller dans une feuille macro internationale, pour avoir la traduction, de manière extrêment simple.

Malheureusement, avec Excel 2011, il n’est plus possible d’obtenir simplement une feuille macro internationale.

Auparavant, un clic droit sur u onglet de feuille permettait d’ajouter une nouvelle feuille à un classeur, et d’en choisir le type, y compris une feuille macro internationale. Il était également possible d’n créer à partir du menu « Insertion ». Seule cette seconde option existe encore, mais les macros internationales ne sont plus proposées :

Fig. 40 — L’insertion d’une feuille n’offre plus le choix « feuille macro internationale ».

 

On pourrait estimer que c’est un choix légitime de la part de Microsoft, qui 18 ans après l’apparition de VBA dans Excel 5, commence à retirer certines fonctionnalités liées à l’ancien langage. Toutefois, il est toujours possible d’en créer par macro VBA ou XL4 :

Sub NouvelleFeuilleMacroInternationale()
    ValeurBrut = ThisWorkbook.Sheets.Add , , , xlExcel4IntlMacroSheet
End Sub 

ou

Fig. 41 — Création d’un classeur ne contenant qu’une « feuille macro internationale », par une macro XL4.

Il est donc toujours possible, par programmation d’obtenir une feuille macro internationale. En ce qui concerne l’usage de ces feuilles comme dictionnaire, cela fonctionne toujours : voici le résultat, lorsiuqu’après avoir créé une feuille macro internationale, on fait un Copier/Coller des cellules illustrées en figure 41 :

Fig. 42 — Les macros internationales peuvent être utilisées aisémenrt pour traduire les fonctions.

Ce n’est plus très grave, mais dans la version française d’Excel, les macro internationales ne peuvent plus guère servir qu’à la traduction des fonctions en effet, si on souhaite les utiliser pour y écrire directement du code, il faut tout avoir en tête, car si l’on essaie d’insérer une fonction, ce sont les fonctions en français qui sont proposées :

Fig. 43 — Fonctions en français, proposées à tort au sein d’une feuille macros internationale.

 

Ces fonctions ne sont bien évidemment pas reconnues dans une feuille macro internationale. Il s’agit ici d’une erreur dans l’adaptation en français d’Excel, ce que l’on appelle la « localisation ».

B - Présence de macros XL4 et de VBA dans un même document

Lorsqu’un document contient à la fois du code VBA et d’anciennes macro, typiquement pour contourner le bug de la fonction Evaluate, que nous avons étudié en détail, le message de mise en garde contre le risque que représentent les macros² :

Fig. 44 — Message de mise en garde, affiché 2 fois, à l’identique lorsqu’un document contient du code VBA et XL4.

 

est affiché 2 fois à la suite, sans aucun élément permettant à l’utilsateur que chacun des messages est déclenché par l’un des 2 types de macros. Dans ces conditions, une seule mise en garde suffirait. C’est d’ailleurs ce qui se passe avec les autres versions d’Excel

4 - Excel proprement dit

A - Saisien de texte

Il m’arrive régulièrement de vouloir entrer du texte ou une formule danns une cellule, et de me rendre compteb que seul le premier caractère a été pris en compte. Il faut alors cliquer à l’intérieur de la barre de formule, et recommencer à partir du second caractère. C’est assez agaçant, et j’ignore pour le moment dans quelles circonstances particulières cela se produit.

B - Menus grisés

À l’issue d’un traitement macro VBA, il arrive assez fréquemment qu’on se retrouve avec un environnement dégradé dans Excel, où toutes les commandes des menus s(y compris les menus contextuels) sont grisées. Les raccourcis clavier fonctionnent :on peut par exemple quitter Excel ou enregistrer un document, mais c’est tout de même désorientant. Il y a heureusement une solution extrêmement simple : il suffit de sélectionner une cellule, puis d’utiliser le raccourci clavier, « ⌘ + 1 » permettant d’en modifier le format :

Fig. 45 — « ⌘ + 1 », le raccourci pour modifier le format d’une cellule.

 

Selon que l’on passe par le menu « Format », ou par le menu contextuel en faisant un clic droit (ou Ctrl + clic) dans une cellule, le raccourci indiqué n’est pas le même :

Fig. 46 — « Maj + ⌘ + & », le raccourci pour modifier le format d’une cellule.

 

En fait la différence n’est qu’apparente : pour obtenir « ⌘ + 1 », si on ne dispose pas d’un clavier numérique, typiquement un utilisateur de Mac portable, il faut bien faire « Maj + ⌘ + & », car sur un clavier français de Mac, « & » et « 1 » sont sur la même touche. Ce qui est tout de même surprenant, c’est l’incohérence, au moins apparente, entre les 2 menus.

Dès lors que le dialogue permettant de modifier le formatage de la cellule s’affiche, cliquer sur le bouton « Annuler » suffit à provoquer le retour à la normale des commandes des différents menus.

C - Raccourcis clavier

Nous venons d’aborder le sujet avec la manière de récupérer les menus lorsqu’ils sont grisés. 

Outre l’incohérence déjà signalée, la figure 46 fait apparaître plusieurs bizarreries : Les raccourcis associés aux classiques Couper, Copier et Coller font apparaître un « x », un  « c »  et un « v » minuscules. Cela n’a pas d’incidence pratique, mais c’est inesthétique et contrire aux usages. Plus grace, le raccoiurci clavier associé au collage spécial commence par « ^ ». J’ai beaucoup de mal à imaginer comment un tel raccourci est possible. Je serais bien incapable d’obtenirau clavier « ^ + ⌘ + V ». Malheureusement, ce n’est pas un cas isolé. Plusieurs raccourcis, dans Excel, comme dans l’environnement VBA, commencent par « ^ ». Je suppose qu’ils ont été conçus par des gens utilisant un clavier autre que français

Il y a également d’autres anomalies; Par exemple, selon la liste des raccourcis Excel, indiqués sur une page de Microsoft, on peut basculer entre l’affichage des formules et celui des valeurs avec « Ctrl = accent grave ». Selon moi, ce n’est pas non plus réalisable avec un clavier français.

Quant aux classiques, pendant des années, « ⌘ + B » permettait de recopier vers le bas et « ⌘ + D » vers la droite. Dorénavant, « ⌘ + B » efface, tandis que « ⌘ + D » semble ne plus avoir aucun effet ! Pour recopier vers le bas, ce n’est pas non plus « Ctrl + B », qui met en gras (« B » comme Bold) il faut maintenant utiliser « Ctrl + D ». La lettre « D » n’est plus associée à Droite, mais à Down. On recopie dorénavant vers la droite avec « Ctrl + R », ce qui est cohérent avec ce que nous venons de voir (« R » comme Right). 

Il y a tout de même, outre toutes ces évolutions agaçantes, une curiosité :  À l’époque d’Excel 1, les raccourcis clavier étaient traduits. « ⌘ + E » permettait d’enregistrer, et « ⌘ + S » supprimait. Depuis la normalisation des raccourcis, en principe, ⌘ + S est réservé à l’enregistrement (« S » comme Save). Hé bien, dans Excel 2011, ⌘ + S quand on sélectionne un élément dans la liste des noms, « ⌘ + S » permet toujours de le supprimer comme à l’époque d’Excel 1. C’est une sorte de « bug positif », car l’usage de ⌘ + S dans ce contexte est une anomalie, mais c’est en même temps bien pratique

J’aurais aimé illustrer cela avec une copie d’écran des menus d’Excel 1. Malheureusement, à défaut d’Excel 1.0, j’ai bien retrouvé une disquette avec Excel 1.51, mais rien que me permette d’essayer de la lire.

5 - L’environnement de programmation Excel

Il y aurait encore un certain nombre de choses à dire, y compris pour une fois, quelques élément positifs, mais cela me paraît trop technique pour des lecteurs dont l’immense majorité n’a jamais vu cet environnement. Je suggère de faire dans un avenir relativement proche une introduction à VBA. Il sera alors plus raisonnable d’examiner les forces et les faiblesses de cet environnement en ayant au préalable acquis quelques bases.

Je vais donc me contenter de vos donner un avant-goût de ce qui nous attend : pour se déplacer à l’intérieur d’un dialogue, le plus efficace est la touche tabulation. Pour les dialogues créés en VBA, il existe une interface qui permet de déplacer dans une liste les différents contrôles (boutons options, boutons radio, zones de saisie de texte, listes, …), afin de déterminer dans quel ordre la touche tabulation va paermettre d’accéder à ces différenys éléments.

Il y a ici encore, une erreur manifeste de localisation :

Fig. 47 — Il y a 2 boutons pour déplacer un élément de la liste vers le haut !

Heureusement, « Déplacer vers le haut » n’est qu’une bourde de traduction. Celui situé le plus en bas dans le dialogue provoque bien un déplacement vers le bas !

______________________________________________________________

1 - Espace est féminin en typographie.

2 - Pour quelqu’un animé de mauvaises intentions, il est facile par exemple d’effacer de nombreux fichiers d’un disque dur ou d’un réseau avec des macros VBA (ou XL4).