J’ai eu des difficultés plus grandes que prévues pour la reproduction du code. Il est 5h50. Je mets tout de même quelque chose en ligne, que j’essaeirai de compléter dans la matinée. Si c’est irréaliste, je finirai une partie pour avoir quelque chose de cohérent, et la fin sera dans une prochaine humeur, dans les jours qui viennent. C’est à voir avec François.
Rappel
Dans la première partie de cet article, signée du pseudo que j’ai longtemps utilisé ici, j’ai essayé de montrer à quel point l’usage des « noms » pouvait être utile dans Excel.
De nombreux utilisateurs d’Excel, la majorité sans doute, ne font jamais appel aux noms. Parmi ceux qui s’en servent, la plupart ne connaissent que la possibilité de nommer une cellule ou une plage.
Et pourtant ! C’est la possibilité d’associer une formule à un nom, qui confère toute sa puissance au concept.
Reprenons l’exemple d’un des bulletins de salaire, précédemment illustré en figure 18 (de la première partie) :
Fig. 23 — La plage de cellules sélectionnées a été nommée « Base ». Des formules simples ont permis d’obtenir les différentes valeurs. Les cotisations elles-mêmes n’ont pas encore été calculées. NB - il s’agit de la reprise de la figure 18 de la première partie.
Par défaut, Excel affiche les valeurs des cellules. Cependant, il peut être pratique, pour vérifier qu’un tableau est bien conçu, en comprendre le fonctionnement, ou simplement se rafraîchir les idées, de pouvoir afficher les formules. Pour basculer de l’affichage des valeurs à celui des formules (et inversement), Il faut passer par le ruban « Formules » :
Fig. 24 — Le menu permettant de basculer entre l’affichage des valeurs et celui des formules.
Le ruban existe depuis Excel 2007 pour Windows, mais en ce qui concerne le Mac, Excel 2011 est la première version à l’offrir. Dans les versions précédentes, il fallait passer par les préférences, ce qui est toujours possible avec la version actuelle, mais la procédure est plus longue. Le plus pratique est de recourir à un raccourci clavier, mais malheureusement, il semble ne pas en exister pour la version française d’Excel 2011. Il y en avait pourtant un dans les versions précédentes (Mac et Windows), mais il a varié au fil des ans. Le raccourci a longtemps été « ⌘ + " » sur Mac et « Crtl + " » sous Windows.
Une fois l’affichage des formules sélectionnées, la largeur de toutes les colonnes de la feuille est automatiquement doublée, pour faciliter la lecture des formules. En effet, celles-ci nécessitent souvent plus d’espace pour être intégralement visibles.
Voici les formules utilisées pour calculer les différentes bases de cotisations :
Fig. 25 — Les cellules sélectionnées sont les mêmes qu’en figure 23, mais ici, le sont les formules qui sont affichées.
L’affichage des formules est un bon moyen, nous venons de le voir, pour analyser la structure d’une feuille Excel. Il existe également un outil comparable pour les noms. On y accède par le même sous-menu que pour créer un nom (figure 2 de la première partie), à la nuance près qu’il faut choisir Coller… au lieu de Définir… :
Fig. 26 — L’accès à la zone de dialogue Coller un nom
Le choix Coller… entraîne l’apparition d’une fenêtre listant l’ensemble des noms ayant été définis1 :
Fig. 27 — La liste des noms ayant été définis
L’usage le plus classique consiste à cliquer successivement sur un nom dans la liste, puis sur le bouton OK, afin d’insérer le nom dans une formule. Mais ce qui nous intéresse ici est le bouton Coller une liste2. Il a pour effet d’insérer à partir de la cellule active, la liste de tous les noms1, avec leurs définitions dans la colonne voisine :
Fig. 28 — La liste des noms, et leurs définitions
NB - Avant de coller la liste des noms dans une feuille de calcul, il faut s’assurer d’avoir suffisamment de cellules vides disponibles à partir de la cellule active : C’est en effet dans celle-ci qu’est reporté le premier nom ; les suivants, et les définitions exploitent les lignes suivantes, et la colonne voisine. Si parmi les cellules utilisées pour le reportées
Lorsqu’il est nécessaire de modifier un document complexe, auquel on n’a pas touché pendant plusieurs mois, l’existence de noms associés à certaines cellules, et plus encore à des formules, facilite grandement les choses. C’est encore plus flagrant, si la personne devant effectuer les modifications n’est pas le concepteur du document originel. La figure 25 illustre parfaitement la situation : si aucun nom n’avait été défini, la cellule B11, à la place de :
=BaseCSG
devrait contenir la formule suivante
=(MIN($E$6;Param!$C$2) + MIN(Param!$C$3 - Param!$C$2;MAX(0;$E$6 - MIN($E$6;Param!$C$2)))) * Param!$C$6 + MIN(Param!$C$4 - Param!$C$3;MAX(0;$E$6 - MIN($E$6;Param!$C$2) - MIN(Param!$C$3 - Param!$C$2;MAX(0;$E$6 - MIN($E$6;Param!$C$2)))))
Cette formule a été obtenue en remplaçant chaque nom de la définition de « BaseCSG » par la définition correspondante de la figure 28, avec un effet de cascade : par exemple, la tranche B repose en partie sur la tranche A : « = MIN(…;MAX(0;Brut - TrancheA)) ». Dans la formule ci-dessus, c’est MIN($E$6;Param!$C$2) et non simplement MIN(Brut;PlafondTrA) qui se substitue au « TrancheA » inclus dans la définition de « TrancheB ». En effet, chaque nom présent au sein d’une définition a lui-même été lui-même été remplacé par sa propre définition.
J’espère qu’un simple examen visuel des deux variantes devrait convaincre les plus réticents que l’usage de noms associés à des formules peut être extrêmement efficace. On aurait pu optimiser la très longue formule ci-dessus , afin d’aboutir à quelque chose d’un peu plus court (plus concis serait très exagéré), mais cela serait tout de même resté difficilement compréhensible.
Si par extraordinaire, un argument était encore nécessaire, la version de la formule reposant sur les noms, peut être reprise sans crainte pour des calculs annexes, y compris par quelqu’un ne connaissant pas suffisamment Excel et/ou les mécanismes de la paye pour comprendre la version longue.
VBA
Réussir à expliquer en quelques mots ce qu’est VBA (Visual Basic for Applications) à quelqu’un qui n’a jamais programmé, est une tache ardue, et c’est un euphémisme. Il faudrait pour cela au moins une humeur entière de Cuk.ch.
À défaut, je vous propose une courte annexe, située à la fin de l’humeur. Par ailleurs, j’ai réalisé il y a une dizaine d’années un site consacré à Excel et VBA. Il n’a pas été mis à jour depuis longtemps, et aurait besoin d’être profondément remanié pour être au goût du jour. Toutefois, il contient beaucoup de matière, et le parcourir vous donnera une assez bonne idée de VBA. J’oserai même prétendre qu’il contient quelques perles, en particulier :
- La fonction Controle, commentée de manière détaillée, est un outil universel pour les contrôles de saisie dans les dialogues réalisés en VBA, les UserForms. Avant d’accepter une validation, elle permet de s’assurer que toutes les zones obligatoires ont été renseignées, qu’elles sont cohérentes, et respectent le format attendu si nécessaire
- Une macro, ConversionVBA, développée avec VBA pour Word, qui permet d’imprimer du code VBA en couleurs, ce qui n’est pas proposé par l’environnement VBA lui-même. Ce même outil permet également de générer le code HTML pour reproduire sur une page Web ce smêmes couleurs.
Il éxiste également, bien sûr, de nombreuses autres ressources sur le Web.
Excel 2011 - Bugs et nouveautés
Commençons par les bugs ; cela nous permettra de finir sur une note plus optimiste. Il en existe de plus ou moins graves, et certains ne concernent que la version française.
1 - VBA - Fonction Evaluate, présentation du bug.
Le plus grave à mes yeux concerne la fonction Evaluate de VBA. Elle permet en principe de récupérer la valeur associée à un nom. Voici un exemple de code :
Sub BugEvaluate()
MsgBox Sheets("Moreau").Evaluate("Brut")
MsgBox Sheets("Moreau").Evaluate("BaseCSG")
End Sub
MsgBox permet d’afficher une alerte ou une information. Sheets("Moreau") désigne une feuille de classeur nommée « Moreau ». Pour que ce petit programme fonctionne, il faut que le classeur au premier plan contienne une feuille « Moreau », et que les 2 noms « Brut » et « BaseCSG ». Tous ces préalables sont remplis dans notre bulletin de salaire.
Lançons maintenant le programme. En principe, les 2 instructions MsgBox devraient chacune afficher les valeurs associées aux 2 noms indiqués, soit 2 800 pour « Brut » et 2 716 pour « BaseCSG » (cf. les cellules B12, B23 et B24 du bulletin illustré en figure 23).
Le premier message est correct :
Fig. 29 — L’instruction MsgBox Sheets("Moreau").Evaluate("Brut")
affiche correctement le montant du salaire brut de Moreau.
Le second au contraire est aberrant : au lieu d’afficher la valeur associée au nom « BaseCSG », c’est le nom lui-même qui apparaît. La fonction Evaluate se comporte ici telle un perroquet :
Fig. 30 — L’instruction MsgBox Sheets("Moreau").Evaluate("BaseCSG")
renvoie le nom passé en argument, « BaseCSG », au lieu de la valeur attendue, 2 716 !
Nous avons testé La fonction Evaluate avec 2 noms respectivement associés à une cellule, et à une formule. Le résultat attendu n’est obtenu que dans le premier cas. Voyons maintenant ce qui se passe avec un nom associé à une simple valeur :
Sub BugEvaluateSuite()
ActiveWorkbook.Names.Add "PlafondURSSAF", 2946
MsgBox ActiveSheet.Evaluate("PlafondURSSAF")
End Sub
Ce second programme, également très simple est indépendant d’un classeur particulier :
ActiveWorkbook représente le classeur actif. Names est une collection de noms. Add est une méthode générique, permettant d’ajouter 1 élément à une collection. La première instruction a donc pour effet de définir un nouveau nom dans le classeur, associé au nombre 2 946, qui correspond au plafond de la tranche A pour l’année 2011.
Appliquée à un modèle de bulletin de salaire réalisé pour l’année 2010, cette simple instruction entraîne la mise à jour du plafond.
Voyons maintenant le résultat obtenu :
Fig. 31 — L’instruction MsgBox ActiveSheet.Evaluate("PlafondURSSAF")
renvoie le nom passé en argument, « PlafondURSSAF », au lieu du plafond, 2 946 !
Ces 3 exemples, et de nombreux autres tests, réalisés sur plusieurs machines, équipées de versions différentes de Mac OS X, montrent que sous Excel 2011, Evaluate est incapable de fournir le résultat attendu si le nom qui lui est passé en argument correspond à autre chose qu’à un nom associé à une cellule. Malheureusement, dans ce cas il est inutile de faire appel à la fonction Evaluate, car l’un des objets de base utilisés par VBA Excel est Range, qui correspond à une plage de cellules. Une cellule particulière n’est qu’un cas particulier de Range.
Par conséquent, parmi les 3 instructions utilisant MsgBox que nous avons testées, la seule qui fonctionne,
MsgBox Sheets("Moreau").Evaluate("Brut")
peut être remplacée par
MsgBox Sheets("Moreau").Range("Brut")
qui renvoie la bonne valeur.
Autrement dit, Evaluate ne fonctionne que dans le cas où l’appel de cette fonction n’est pas nécessaire !
Ce bug est d’autant plus incompréhensible, que cette fonction existe depuis de nombreuses années. Je l’ai utilisée de nombreuses fois avec toutes les versions depuis Excel 98 (Mac), et Excel 97 (Windows). Vous avez compris, que pour moi qui suis un inconditionnel de l’association des noms et des formules, ce bug est particulièrement gênant.
J’ai un client pour lequel j’ai développé avec l’ancien langage macro d’Excel, au milieu des années 90 une application qui tourne toujours. Ce premier langage est apparu en 1985 avec Excel 1.0, et mêlait les fonctions standard d’Excel avec des outils de programmation plus classiques. Il a été l’unique langage macro jusqu’à la version 4 incluse. On parle maintenant de macros Excel 4 (ou de macros XLM, l’extension de fichier correspondante) pour les macros développées avec cet outil. L’ancien langage avait déjà une fonction Evaluate, qui fonctionne toujours sous Excel 2011 ! Voici donc une piste intéressante pour contouner le bug de la fonction éponyme de VBA.
VBA est apparu avec la version 5, mais n’a atteint sa maturité, selon moi, qu’avec les versions 97 et 98. Pendant un certain temps, les 2 langages ont cohabité, et si on voulait enregistrer une macro, on avait le choix entre les 2.
Pour ma part, je n’ai commencé à utiliser VBA sérieusement qu’avec Excel 97, et pendant encore un certain temps, j’ai continué à développer avec les macros XL4, qui sont toujours présentes dans les versions actuelles, mais ne sont plus documentées.
En ce qui concerne l’application évoquée, je l’ai ensuite portée sous VBA au début des années 2000. Des adaptations ont été faites depuis lors, pour prendre en compte l’évolution des besoins, et l’application est encore utilisée chaque semaine. Elle tourne encore sous Excel X, la première version sortie pour Mac OS X. En effet, en ce qui concerne VBA, Excel 2004 était bien moins stable que la version précédente, et faisait parfois quitter Excel « inopinément », lors de traitements ne posant aucun problème avec la version précédente, ou avec une version PC d’Excel.
Je ne suis pas le seul à penser du mal d’Excel 2004, et pas seulement au niveau de l’implémentation de VBA. Une page d’un développeur américain liste de nombreux problèmes de cette version, dont beaucoup n’ont jamais été corrigés. Parmi ceux-ci, à part les problèmes d’instabilité, ce que je trouvais le plus gênant était l’impossibilité, de se déplacer avec la touche Tabulation. Voici un dialogue réalisé initialement avec Excel 98 :
Fig. 32 — Dialogue initialement réalisé avec Excel 98. Excel 2004 est la seule version avec laquelle il est impossible de se déplacer d’une zone de saisie à la suivante en utilisant la touche Tabulation. Il faut alors recourir à la souris, ce qui pour de la saisie est parfaitement inadapté.
Quant à Excel 2008, qui a été la première version développée pour les Mac équipés de processeurs Intel, VBA en avait disparu, pour des raisons que j’ignore. Il y a plusieurs hypothèses, parmi lesquelles : le portage de VBA aurait provoqué un délai excessif et/ou compte tenu du fible nombre de Macs en entreprise, cela n’en aurait pas valu la peine.
Les développeurs étaient censés faire appel à Applescript, à la place. Cela avait 3 inconvénients majeurs :
- Une perte totale d’interopérabilité entre les versions Mac et Windows,
- L’obligation d’investir pas mal de temps pour être capable de réaliser les mêmes choses avec AppleScript qu’avec VBA, à supposer que ce fût possible,
- L’impossibilité de réaliser directement les outils que chacun avait pu développer en VBA.
Il y a eu de nombreuses réclamations, et Microsoft a rapidement annoncé que VBA serait à nouveau présent dans Excel 2011.
Par conséquent, cette version était très attendue, car depuis Excel X, sorti il y a une dizaine d’années, était la dernière version d’Excel pour Mac utilisable sans problème majeur en ce qui concerne VBA.
Il y a un an environ, alors qu’Excel 2011 était disponible depuis peu, le client m’a appelé pour me demander conseil. Il voulait savoir s’il pouvait installer Office 2011 ou si cela risquait de poser des problèmes. J’étais alors en mission relativement longue ailleurs, et n’avais pas encore eu le temps de tester cette version. Je lui ai répondu que j’avais lu des tests dans la presse et sur Internet, qui étaient tous favorables, et selon lesquels les macros écrites pour des versions antérieures fonctionnaient bien avec Excel 2011, mais que je n’avais pas encore fait d’essai moi-même. Ils ont acheté un nouveau Mac sur lequel Excel 2011 a été installé.
L’application que j’ai développée pour eux a planté presque immédiatement, à cause du bug de la fonction Evaluate. En effet lors du premier traitement à effectuer chaque semaine, la validation commence ainsi :
Private Sub ReportDonnees()
If CDate(TDate) - FParam.Evaluate("DateEnCours") >= 4 Then
ThisWorkbook.Names.Add "DatePrécédente", FParam.Evaluate("DateEnCours")
ThisWorkbook.Names.Add "DateEnCours", CDate(TDate)
End If
…
Pour simplifier, on compare une date saisie avec une date de référence stockée sous le nom « DateEnCours ». En fonction du résultat, on met ou non à jour 2 noms, « DatePrécédente » et « DateEnCours ». Sauf que, sous Excel 2011, cela ne fonctionne pas à cause du bug de la fonction Evaluate.
2 - VBA - Comment contourner le bug de la Fonction Evaluate.
A - Passage par une cellule
La première façon consiste, puisque les noms sont parfaitement utilisables dans les feuilles de calcul, à utiliser une instruction VBA pour entrer dans une cellule le signe « = » suivi d’un nom. Il suffira de lire la valeur qu’affichera alors la cellule. Ce sera celle associé au nom entré.
Ce n’est pas très élégant comme façon de faire, et cela implique plusieurs contraintes : il faut être sûr de ne pas écraser un contenu préalable. Ceci n’est pas très difficile : il suffit d’utiliser une colonne vide, ou une feuille masquée.
Cependant, si la feuille concernée n’est pas protégée, un utilisateur risque de supprimer la feuille ou la colonne, ou saisir quelques chose dans la plage prévue pour déterminer la valeur associée à un nom. Dans ce cas, la fois suivante qu’on entrra un nom dans la cellule, on écrasera ce qui aura été saisi par l’utilisateur.
Si la feuille est protégée, il faudra la déprotéger et la reprotéger à chaque fois, ce qui complique les choses.
D’autre part, utiliser ainsi une cellule va modifier le classeur. Si l’utilsateur se voit demander s’il veut enregistrer les modifications, et accepte, le classeur semblera avoir été modifié le jour même alors que ce n’est pas forcément le cas, à part bien sûr l’utilisation provisoire d’une cellule pour contourner un bug.
Fig. 33 — La création d’un second bulletin de paye dans le classeur.
on va se retrouver avec plusieurs occurrences des mêmes mots ; dans chacune des feuilles, « TrancheA » désignera la tranche A correspondante. Dans ces conditions, les contraintes évoquées en passant par une cellule pour récupérer la valeur associée à un nom augmentent donc encore, car il faut que la cellule appartienne à la feuille concernée.
B - Utilisation de l’ancien langage macro
Nous avons vu plus haut que Evaluate existait également dans les macros Excel 4, et qu’on obtenait alors un résultat correct. 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, VBA est apparu en 1993, il y a près de 20 ans. Hors du milieu universitire, 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.
Sub TrancheAEnMacroExcel4() MsgBox Application.ExecuteExcel4Macro("EVALUATE(""Moreau!TrancheA"")")End Sub
Présentation de VBA, et interaction avec Excel
VBA (Visual Basic for Applications) est un langage informatique, une évolution des Basic d’antan, qui est intégré à Office et permet d’automatiser des tâches dans Excel, mais aussi dans Word ou Powerpoint. Il est également possible, pour les utilisateurs de Windows, de manipuler Access, et même Internet Explorer !
Après cette longue présentation de l’usage possible des noms dans Excel, nous allons essayer de voir ce qu’est , et à quoi cela peut être utile. Nous pourrons ensuite entrer enfin dans le vif du sujet, les nouveautés d’Excel 2011.
VBA est ce qu’on appelle un langage orienté objet. Un classeur, une feuille de calcul ou une cellule sont des objets.
Chaque objet a des propriétés : la valeur, la formule, la largeur de colonne et la hauteur de ligne, le format pour les nombres, la police, … sont quelques unes des propriétés d’une cellule. Certaines propriétés renvoient elles-mêmes un objet : par exemple la propriété Font (police) renvoie un objet Font, qui a lui-même de nombreuses propriétés (nom, taille, graisse, soulignement, couleur, …). Les propriétés de la plupart des objets peuvent être lues ou modifiées. Un petit nombre d’entre elles sont accessibles uniquement en lecture : si on a besoin de savoir si une macro tourne sur un Mac ou un PC, on ira lire la propriété OperatingSystem de l’objet Application. Il paraît normal de ne pas pouvoir modifier la valeur d’OperatingSystem !
On peut appliquer à un objet des méthodes, par exemple Open (ouvrir), ou Save (enregistrer) pour un classeur.
Enfin, on peut déclencher un traitement lorsque survient un évènement rattaché à un objet, par exemple le passage au premier plan d’une feuille, un clic droit ou un double clic dans une feuille, …
VB (Visual Basic) existe, uniquement dans le monde Windows, et permet de réaliser des applications double cliquables. VBA en découle ; il ne permet pas d’obtenir une application au sens classique du terme, mais peut en revanche interagir avec les objets d’Excel ou ceux de Word. C’est de là (Application) que vient le « A » de VBA. En effet, les objets diffèrent en grande partie (classeur, feuille de calcul et cellules pour Excel, document, paragraphe pour Word). Il existe donc un dialecte de VBA pour chaque logiciel concerné : VBA Excel et VBA Word ont un important noyau commun, hérité de VB, mais diffèrent quant aux objets qu’ils vont être capables de manipuler.
Excel est un tableur, une famille de logiciels destinés à réaliser assez facilement des tableaux et des graphiques. Le concept de tableur repose sur une idée simple : au sein d’un tableau, si on modifie une cellule, toutes celles dont le contenu est lié, même indirectement à la cellule modifiée sont recalculées automatiquement.
Cela suffit à faire des tableurs un outil irremplaçable pour les financiers, les comptables, les statisticiens, et bien d’autres encore. Depuis une vingtaine d’années, Excel domine largement le marché. Les utilisateurs du produit dans le monde se comptent par dizaines de millions, voire plus.
La très grande majorité des utilisateurs n’utilise qu’une infime fraction des fonctionnalités d’Excel : ils font appel aux 4 opérations et à quelques fonctions, en particulier SOMME() et SI(), alors même qu’il en existe plusieurs centaines. Toutefois, même une exploitation aussi limitée du logiciel permet déjà de faire beaucoup de choses, car le recalcul automatique des tableurs est plus fondamental que la connaissance de nombreuses fonctions.
Pour aller plus loin, les facteurs limitants sont de nature diverse :
- Certains salariés reçoivent une formation, mais ne sont absolument pas motivés, ou n’ont l’occasion de mettre en œuvre ce qui leur a été montré qu’après un délai trop long ; tout a pratiquement été oublié avant même d’avoir servi. Il y a également le cas où la personne suivant la formation a un besoin spécifique auquel la réponse apportée ne convient pas, car celle-ci est trop généraliste.
- Il est possible de progresser tout seul en expérimentant, mais cela concerne peu de monde : il manque à la plupart des gens l’envie ou la curiosité intellectuelle nécessaire à ce type de pratique.
- Et bien sûr, il y a tous ceux que l’informatique effraie.
Puisque les capacités d’Excel sont à ce point sous-exploitées, on pourrait se demander pourquoi s’intéresser à VBA, qui en accroît encore considérablement les possibilités. Estimer qu’il vaudrait mieux que les gens acquièrent une meilleure maîtrise d’Excel avant de se préoccuper de VBA peut sembler raisonnable.
Mais les choses ne sont pas aussi simples. VBA peut en effet être employé de multiples façons, impliquant différents niveaux d’exigence :
1 - Le plus simple consiste à enregistrer une succession d’actions dans Excel, et le code VBA est alors généré automatiquement. Selon ce que réalise la macro ainsi obtenue, celle-ci peut ensuite être réutilisée sans avoir la moindre connaissance de VBA. Toutefois, il vaut mieux être conscient de certaines contraintes : si on reçoit régulièrement un type de document (issu d’une base de données, du web, reçu en pièce jointe d’un email, …) dont la structure est constante, et nécessite une mise en forme et quelques aménagements qui sont toujours les mêmes, une macro obtenue par enregistrement permettra de traiter parfaitement les documents à venir. Si par contre le nombre de lignes et/ou de colonnes est variable, le résultat nécessitera un ajustement.
2 - L’étape suivante consiste à entrer dans l’environnement VBA, à essayer de comprendre ce que réalisent les différentes lignes de codes et à oser faire quelques modifications
3 - Le stade ultérieur consiste à écrire directement du code VBA, sans passer par l’enregistrement automatique. C’est aussi la découverte de tous les outils offerts par VBA et la phase
4 - Enfin, le développeur doit posséder la maîtrise de l’environnement VBA, la connaissance du modèle Excel (la hiérarchie des objets Excel), la capacité de développer des dialogues ayant une bonne ergonomie, de mettre en place des menus supplémentaires ou des boutons offrant une interface à l’utilisateur. Tout cela n’est pas encore suffisant : il faut également être capable de comprendre ce que souhaite un client, savoir s’adapter à un cahier des charges imprécis ou instable, et évaluer correctement le temps nécessaire pour finaliser un projet. En outre, savoir rédiger une documentation claire n’est pas un luxe.
Il est donc possible de mettre en forme automatiquement certains documents, simplement en lançant une macro obtenue par enregistrement automatique, sans avoir la moindre connaissance de VBA, comme on peut faire appel à iun développeur extérieur pour réaliser une application verticale, absolument. spécifique à l’entreprise.
L’intérêt d’utiliser VBA pour une application, n’est pas un réflexe naturel pour la plupart des gens, car le plus souvent, ils n’imaginent même pas que c’est possible. Cela offre pourtant au moins 2 avantages :
- Les documents générés ou manipulés par l’application sont des classeurs Excel. Cela permet par un simple Copier / Coller vers d’autres classeurs, y compris vides, de faire aisément des traitements complémentaires, ne serait-ce que pouvoir trier les clients d’une zone géographique par exemple.
- L’association Excel-VBA évite de devoir réinventer la roue : toutes les fonctions d’Excel sont disponibles et peuvent être utilisées via VBA. D’autres outils impliqueraient en général de redévelopper certaines de ces fonctions.
______________________________________________________________
1 - Certains noms peuvent avoir une validité limitée à une seule feuille du classeur, ce que nous n’avons pas eu l’occasion de voir jusqu’à présent. S’il en existe, chacun d’eux n’apparaîtra dans la fenêtre reproduite en figure 27 que si la feuille active est celle où il est valide.
2 - Le texte du bouton, Coller une liste est une traduction malheureuse, car il sous-entend un choix possible entre plusieurs listes, ce qui est trompeur. Il me semble que dans les premières versions d’Excel, c’était Coller la liste, qui était plus explicite.
, le 02.11.2011 à 08:17
V*raiment *Balaise, cet Article !
(bon, là ya un bug Textile…)
Quand j’ai découvert Excel, en 97, avec Win 3.11 (hihihi), j’ai tout de suite eu envie de macroter, mais je n’ai trouvé personne pour m’expliquer, à l’époque…
z (depuis, je fait d’autres choses, je répêêêêêêêêêêêêête : et puis, je suis passé sur OO)
, le 02.11.2011 à 08:50
Tres bel article. Cela donne envie d’aller encore plus loin avec Excel. Pour ma part, il y a une chose que m’énerve, avec la version 2011 numéroté 14.0.1. Si j’ouvre une feuille de calcul, uniquement pour la consulter, un tarif par exemple. Lorsque je la referme, Excel me demande si je veux l’enregistrer. Alors que je n’ai fait que regarder, rien d’autre. C’est bénin, par rapport à ce que nous a expliqué Michel Galoby, mais ça énerve.
Une raison à cela, et peut être une solution ?
Bonne journée à tous.
, le 02.11.2011 à 09:22
Bonjour. Humeur très instructive, merci.
Je n’utilise plus Excel depuis que nous avons de bons tableurs basiques qui lisent ou importent et exportent des fichiers Excel ce qui me permet de communiquer avec mon environnement Exceliste. Actuellement sous les tableurs que j’utilise je ne programme pas, je n’utilise que les fonctions de base. Et je regrette l’absence de noms pour les plages de cellules, que l’on ne trouve que sous les tableurs qui miment Excel, dommage.
J’ai arrêté de programmer dans les tableurs depuis la disparition, dans le milieu des années 90, de Wingz « le tableur de rêve » publié par Informix. Écrit en Hyperscript, un langage lui-même écrit en Hyperscript, un très beau langage de programmation. On pouvait tout, tout, reprogrammer à sa convenance et dans mes souvenirs un langage « bug free ». Par ailleurs des tableaux « infinis » et des performances décoiffantes sur le traitement des très gros tableaux. Ces performances je ne les ai jamais retrouvées sous aucun des autres tableurs, même avec nos machines actuelles.
Trop d’expériences malheureuses avec Microsoft, c’est une société dans laquelle je ne peux plus avoir confiance, depuis fort longtemps.
, le 02.11.2011 à 10:55
La première chose à faire est d’installer la dernière version d’Excel. Mon humeur est basée sur la version 14.1.2. En principe, un outil est fourni qui recherche les mises à jour, mais la recherche automatique est peut-être désactivée.
Tu peux lancer la recherche manuellement :
Ensuite, est-ce que cela concerne tous tes fichiers, ou seulement certes d’entre eux ? Pour certains fichiers, c’est normal, car certaines fonctions dites volatiles, impliquent un recalcul permanent. C’est le cas par exemple de MAINTENANT ou AUJOURDHUI. Ce recalcul provoque le comportement que tu décris.
, le 02.11.2011 à 14:48
Très bon ces articles, merci pour le partage de connaissances. Pour avoir “programmé” pendant pas mal d’années sous Excel (d’abord en macros Excel 4 puis en VBA), je connais un peu la puissance d’Excel et tout son potentiel. Je pense qu’avec cette “formation” de Michel Gaboly, pas mal de gens vont vouloir essayer de s’y mettre. Bravo pour la rédaction, c’est sympa et agréable à lire. Et aussi merci pour le temps passé à réaliser ces articles.
, le 02.11.2011 à 16:23
Merci Michel pour cet article d’autant que pour avoir déjà réalisé des exemples illustrés, cela demande un temps considérable et énormément d’énergie (à tel point que je ne suis pas prêt de refaire un article comme celui-là par exemple ;) )
Parmi les ressources que je vous conseille :
, le 02.11.2011 à 19:07
Il était bien ton article, pourtant.
Il date de 2006, une mise à jour ne serait pas mal ! Mais, je commence à être conscient de ce que cela représente !
, le 03.11.2011 à 14:25
+1 C’est vrai qu’il était excellent cet article sur Cocoa. Même qu’il m’a poussé à mettre pour de vrai mon nez dans ce langage. Et j’adore.
, le 04.11.2011 à 10:23
Michel,
J’ai fais quelques essais de nommage avec Excel 2003 PC :
A peine deux tentatives et déjà deux échecs de nommage… Cela me laisse sceptique sur l’efficacité du nommage. En tout cas, merci pour l’astuce du “Copier Liste” que je ne connaissais pas ;)
, le 04.11.2011 à 11:17
Guillôme, l’intérêt essentiel des noms, si on se limite à les employer pour des cellules, repose sur la facilité qu’ils apportent pour mieux s’y retrouver, grâce à la plus grande lisibilité des formules que confère leur présence.
L’emploi de lettres uniques n’offre absolument pas cette plus grande lisibilité ; si tu dois modifier un document au bout de plusieurs mois ou années, une formule utilisant des noms comme « A »ou « B » ne présentera aucun avantage par rapport à l’utilisation des classiques références de cellules.
Par conséquent, ce genre de nom me paraît peu compatible avec l’avantage qu’il est censé apporter. Dans ces conditions, que telle ou telle lettre ne puisse être utilisée comme nom me semble sans importance !
Ce n’est pas vraiment une astuce, plutôt une fonctionnalité de base méconnue ;-((
, le 04.11.2011 à 11:35
Raah, on croirait l’assistance Microsoft qui à l’époque m’expliquait que les bugs que je rencontrais sur Office (dans le cadre support entreprise, bugs reproductibles et documentés) ne devaient pas me poser de problème si j’utilisais autrement le produit (et qu’en gros j’étais un idiot si je ne l’utilisai pas comme ça). Et que de toute façon, rien n’était prévu pour corriger ces bugs (circulez y’a rien à voir).
L’intérêt des noms, c’est de pouvoir donner des noms. Si on ne peut pas dans tels ou tels cas, cela limite l’intérêt et c’est important à mon avis de le signaler (c’est le cas pour “Prix (HT)”, “Antoine & Marc”, “Blé, sucre, …”, “Total Net”,…).
En bref, le nom est limité aux caractères A-Z, aux chiffres et à quelques caractères spéciaux comme ”_”. A cela s’ajoute des contraintes que je n’ai pas toutes identifiées comme certaines lettres orphelines interdites (ex : “C”)
L’intérêt des noms, comme tu dis, c’est pour mieux s’y retrouver. Et quand justement, tu as des formules mathématiques (A^2 + B^2 = C^2), des contrats qui stipulent que le prix (A) sera augmenté de 1% en fonction de l’indice insee (B) et de la taille du capitaine (C)… sur lesquels reposent tes feuilles Excel, avoir des noms A, B, C… permet justement de faire le lien directement avec le contrat, les mathématiques, les documents annexés exprimés uniquement en A,B,C…
, le 04.11.2011 à 12:57
Tu es dur, là !
Les formules et les noms d’Excel, ce n’est pas du texte libre, mais les éléments d’un langage de programmation atypique. Comme dans tout langage, il y a des mots interdits et des contraintes à respecter : dans la plupart des langages, tu as des contraintes pour nommer une variable. Les noms en sont l’équivalent.
Les espaces par exemple sont interdits, mais il y a une excellente raison pour cela : l’espace est en effet l’opérateur d’intersection, ce qui n’est pas très connu, je te le concède volontiers !
Ainsi si une plage est nommée « CA », comme Chiffre d’affaires, et une autre « Suisse »,
=SOMME(CA Suisse)renverra la part de CA en Suisse.
, le 04.11.2011 à 13:11
La fin de mon commentaire précédent est une bonne illustration des contraintes des langages informatiques : les commentaires de Cuk.ch sont écrits en Textile, un langage de la même famille que le HTML.
Certains caractères servent à la mise en forme et donnent un résultat inattendu quand on ignore les règles ; j’avais cherché à écrire :
« Ainsi si une plage est nommée « CA », comme Chiffre d’affaires, et une autre « Suisse »,
=SOMME(CA Suisse)renverra la part de CA en »
et ma formule a initialement été transformée ainsi :
=<acronym title= »CA Suisse »>SOMME</acronym>
Pour obtenir que la formule soit affichée, j’ai dû aller consulter la documentation de Textile, et modifier mon commentaire précédent, en faisant précéder et suivre la formule d’un « @ ».
, le 04.11.2011 à 14:51
Complément de ma réponse 12
Je suis allé faire quelques tests. Comme je l’ai indiqué dans mon article du mois dernier (figure 12), la zone de noms permet de se déplacer dans la feuille en sélectionnant un des noms définis.
Cette fonctionnalité n’est pas limitée aux noms ; elle fonctionne aussi avec les références ; entrer « BC1728 » dans la zone de noms et valider, est le moyen le plus rapide d’atteindre la cellule correspondante (si elle n’est pas nommée, bien sûr).
La référence ne doit pas nécessairement désigner une cellule unique, on peut entrer par exemple « BC1728:BD2000 », ou « CA Suisse » pour sélectionner l’intersection de 2 plages nommées« CA » et « Suisse », comme nous l’avons vu dans le commentaire 12.
Il existe encore d’autres possibilités :
« R » n’est pas non plus utilisable pour nommer une plage ; je suppose, mais je n’ai que des versions françaises d’Excel, qu’avec une version en anglais, « R » dans la zone de noms permet de sélectionner la ligne (« Row » en anglais).
Voici pourquoi, guillôme, tu ne peux pas utiliser la lettre C (ou L ou R) pour nommer une plage.