Formule 1

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Access, c'est de la daube

C'est ce qui ressort de certains arguments trouvés dans le débat « Arguments pour ou contre Access », sur le  forum Access de Developpez.com. Notamment, en ce qui concerne la capacité de Jet, le moteur de base de données d'Access, de gérer des  tables de plus de quelques lignes :

je pense que sur une table avec un certain nombre de champs "standard" :p tu peux aller jusqu'a 30000 lignes,

ou alors

Ouf !!! même aux alentours de 1 Mo de données vaut mieux pas trop en demander Image non disponible

et le même, plus loin :

Au delà d'un certain nombre d'enregistrement le SGBDR a dû mal à effectuer des requêtes car il n'est pas fait pour des grosses bases de données mais plutôt pour une petite structure professionnelle ( PME ).
Et puis au-delà d'un certain nombre d'enregistrements est-ce que le SGBDR conserve-t-il son intégrité ?

tout comme des :

1) Access n'est pas un SGBD: il peut donc servir à papa pour classer ses vynils, mais pas pour des applis pro !

et encore :

- il ne permet pas de rechercher efficacement des données dans une grande masse d'informations (indexation des données assez lamentable)

I-A. Rien ne vaut un bon préjugé bien ancré

Papy Turbo (my old apple) dixit :

C'est sûr qu'avec 200 000 formules à évaluer comme ça, faudrait un Pentium VIII à 12 Ghz, ou un an de patience… pour afficher la requête. Image non disponible

Nous cherchions à ouvrir, sur cette table d'au moins 200 000 lignes, une requête capable d'évaluer le résultat d'une formule mathématique simple, la formule étant donnée sous forme de texte. Ma déclaration était basée sur des recommandations lues il y a longtemps dans la documentation d'Access, disant quelque chose comme « Évitez de mettre une fonction VBA dans une requête, pour des raisons de performance ».

Et je n'étais pas le seul à penser que les performances seraient catastrophiques, puisqu'on a pu lire :

Ce qui me perturbe dans tout ça, c'est le nombre de lignes possible : +200 000 Image non disponible

et plus loin,

Bon d'accord c'était 200 000. C'est encore bien pire.

Mais, les recherches continuent quand même, parce que le forum et l'équipe Access de Développez.com ne manquent pas de fous alliés, avec par ci par là, des

Du coup, ça va drôlement mouliner sur 200 000 lignes non ?

et

Mais quoi qu'on y fasse, malgré toute notre bonne volonté, sur 200 000 lignes (et non 20 000), ça va mouliner un max.

et puis encore

mais perso, ça ne m'intéresse pas de chercher car ce n'est pas raisonnable une requête calculée de plus de 200 000 enregistrements…

Plus loin, juste pour rigoler, surparis, auteur de la question de départ, rajoute encore une couche, histoire de remonter le moral général :

e) une formule n'utilise par forcement beaucoup de variables (quoique ..) mais le nombre de variables à disposition est très important (ex. la formule = Var1 + Var57 / Var1253)

Réponse immédiate :

Décidément, tu as décidé de nous faire péter les plombs ? Tu veux dire par là, que, pour un type donné, il peut y avoir + de 1000 variables ?

Bref, un seul moyen d'en avoir le cœur net : créer la table, lui ajouter 200 000 enregistrements, et essayer de lancer une requête qui s'ouvre en moins de 24 heures.

Pour plus de détails sur les débats qui ont mené l'équipe à créer ce banc d'essai, vous pouvez lire l'intégralité des échanges dans le sujet Calcul d'expression littérale, entre le 15 octobre et le 19 novembre 2004, en téléchargeant : ThreadForumFormule1.doc

II. Baby, you can drive my car

Un des plus fabuleux aspects de la programmation, c'est que, le jour où se pose une question complexe, nous avons sous la main tous les outils nécessaires pour fabriquer un autre outil qui, lui, va répondre à cette question.

Mieux encore, à vous de prendre le volant, de soulever le capot et d'aller où bon vous semble pour faire que ce banc d'essai réponde à vos questions.

II-A. Cahier des charges

En deux mots, une table intitulée [TableFormules], contient une variable par enregistrement et, dans un champ texte, des formules qui utilisent ces variables.
Les formules, incluant les 4 opérations de base seulement ainsi que des parenthèses, seront donc du type :

 
Sélectionnez
Var1 + Var6 * (Var53 + Var1704) / Var1

Comment faire une requête qui évalue, calcule et affiche le résultat de chaque formule ?

Enfin, si le champ [Formule] est vide, on affiche simplement la valeur de la variable (champ [ValeurVariable]).

La table concernée est simple, ne contenant que 4 champs : les formules et les variables qu'elles utilisent sont regroupées par Type de Formule.

Image non disponible

Vous trouverez, dans le banc d'essai, une table vide au format demandé, un générateur d'enregistrements qui créera les 200 000 enregistrements (ou plus), et divers essais de calcul de la formule.

Torturez le, martyrisez le, massacrez le code et faites le tourner jour et nuit, tout le week-end ou plus, jusqu'à lui faire rendre l'âme, il est fait pour cela (le banc d'essai, pas votre PC).

Par contre, en tant que [banc d'essai], soyez conscient qu'il s'adresse à des programmeurs et n'a donc rien de ce qu'on peut attendre d'une application professionnelle à distribuer à des utilisateurs qui ne seraient pas des développeurs eux-mêmes. En particulier et à titre exceptionnel,

  • le banc d'essai est « monobloc ». Les tables sont dans le même fichier que les requêtes, les formulaires et états.
  • certaines routines sont utilisées par 2 ou 3 méthodes différentes. Dans chaque cas, la routine a été recopiée pour que chaque module contienne tout ce dont il a besoin.
  • il n'y a aucun contrôle d'erreur. Même pour certaines erreurs prévues ou prévisibles, nous avons laissé s'afficher le message d'erreur, sans aucun traitement. C'est volontaire, bien sûr.
  • de nombreuses procédures, telles que le générateur d'enregistrement, vous seront certainement utiles pour vos propres tests et applications : elles sont livrées « en l'état », sans aucune garantie de fonctionnement particulier elles sont adaptées au problème abordé ici et non pas pour résoudre tous les cas possibles ; à vous de les adapter à vos cas particuliers.
  • vue la taille de la base avec plusieurs centaines de milliers d'enregistrement, l'option de Compactage automatique (Access 2000, menu Outils — Options, onglet Général, Compactage lors de la fermeture) n'a pas été activée : n'oubliez pas de compacter régulièrement, sinon, la base atteindra plusieurs centaines de Mo très rapidement. Le compactage peut prendre plusieurs minutes.

II-B. [Banc d'essai]

Pour télécharger le banc d'essai, cliquez sur :

Le formulaire, intitulé « 001_Test_Calculs », inclut 3 onglets :

Image non disponible

II-B-1. Onglet générateur

La table principale, [TableFormules], est vide au départ, pour des raisons évidentes de téléchargement.

Vous devez y ajouter des enregistrements conformes aux règles édictées sur le forum au cours de la discussion, pour mesurer les temps de réponse des diverses solutions.

Vider la table ?  Par la suite, pour recommencer vos tests avec une table propre, ou pour diminuer le nombre d'enregistrements, cochez cette case avant de générer d'autres lignes.

La table doit contenir…  Pour les premiers tests, vous pouvez accepter la valeur par défaut (200 000 enregistrements). Par la suite, il sera recommandé de réaliser certains tests avec moins de lignes.

J'ai testé les calculs non limités (versions VBA, DAO…) de ce banc d'essai avec plus d'1 million d'enregistrements. Mais rien ne vous empêche d'aller (beaucoup) plus loin…

Appliquer un modèle  Si vous ne cochez pas cette case, des types de formules seront créés, chacun avec un nombre aléatoire de variables pouvant aller jusqu'à plusieurs milliers de variables par type.

Cochez la case pour limiter le nombre de variables par type de formule ou créer des configurations particulières :

Image non disponible

L'exemple ci-contre propose de remplir la table avec une série de types de formules :

  • 1 type contenant 20 variables,
  • 5 types de 50 variables,
  • 3 types de 120 variables.

Ces 9 types, comportant en tout (20 + (5*50) + (3*120)) variables, seront répétés jusqu'à ce que le nombre total d'enregistrements (contrôle « La table doit contenir ») soient créés.

N'hésitez pas à créer des types plus complexes, avec plusieurs (dizaines de) milliers de variables par type : les résultats de chaque méthode de calcul seront affectés différemment, le tout dépendant bien sûr de la quantité de mémoire disponible sur votre poste.

Générer les enregistrements cliquez sur ce bouton pour lancer la génération des enregistrements. Elle prendra plusieurs minutes, voir une demi-heure ou plus, selon le nombre de lignes demandées, la puissance de votre poste, le nombre d'autres applications actives au même instant, etc.

La génération se déroule en 2 phases :

Image non disponible Pendant la 1ère phase, les enregistrements sont créés avec une variable par ligne. La progression s'affiche sur la barre d'état sous forme de chiffres : le type (de Type1 à TypeX), le nombre de variables par type et le nombre total d'enregistrements dans la table.

Image non disponible Pendant la 2e phase, les formules sont ajoutées aux enregistrements, en utilisant les variables créées en phase 1. La progression s'affiche sous forme d'une barre de progression typique d'Access.

Vous pourrez constater que, si la barre de progression est plus esthétique, ou fait plus « professionnelle » que le simple défilement de chiffres, elle est beaucoup moins « parlante » lors de processus aussi longs que celui-ci…

Pour examiner le code, ouvrez le module 9_Generateur, et voyez le chapitre Le Générateur.

Lorsque la génération se termine, l'en-tête du formulaire affichera le nombre d'enregistrements contenus dans la table [TableFormules].

Afficher la structure des données générées Ce bouton est actif lorsque la table n'est pas vide. Notamment si vous avez utilisé un modèle, cliquez-le pour afficher un état indiquant le nombre réel de types générés et le nombre de variables dans chaque type.

Image non disponible

Il est normal qu'un des types ne corresponde pas au modèle puisque la génération d'enregistrements s'arrête dès qu'on a atteint le nombre demandé exact : ici 200 000, donc le dernier type généré ne contient que 60 enregistrements — 60 variables.

Ouvrir le formulaire : Le bouton reste grisé tant que la table est vide. Après avoir lancé le générateur, sortez votre chronomètre et cliquez sur ce bouton pour afficher vos 200 000 (ou plus) enregistrements, avec le résultat de chaque formule.

II-B-1-a. Le formulaire

Si un de vos clients/utilisateurs est affolé à l'idée que ses 10 000 lignes de facture ou autres enregistrements mettront des heures à s'afficher, n'hésitez pas à

  • générer le double du nombre d'enregistrements dont il a besoin, pour tenir compte d'une possible montée en charge,
  • le laisser ouvrir ce formulaire, avec ou sans le calcul des résultats de formule (qui va ralentir le défilement des enregistrements).

Sur le formulaire,

  • appuyez sur Ctrl + flèche bas, pour atteindre le dernier enregistrement,
  • cliquez sur Activer le filtre par Type, pour créer une liste déroulante de tous les types contenus dans la table de Formules, et voyez le temps nécessaire pour créer la liste. Utilisez ensuite ce filtre pour sélectionner un groupe d'enregistrements.
  • cliquez sur Activer la recherche par nom de variable, pour créer une liste déroulante contenant tous les noms de variables de la table. Si vous avez activé un filtre sur un type, seules les variables de ce type apparaîtront dans la liste.
  • après avoir activé la liste de recherche, saisissez ou copiez le nom d'une variable dans ce contrôle, pour atteindre l'enregistrement qui contient sa valeur.

Lorsque la liste des noms est créée, elle ne contient que les premières variables. Pour la remplir complètement, vous devez la faire défiler jusqu'au nom de la dernière variable, ce qui oblige Access à la compléter.

Si vous avez créé plus de 65 000 enregistrements, vous constaterez une limite des listes déroulantes dans Access : toutes les variables n'y apparaissent pas.

Pas d'information sur ce sujet, dans l'aide (rubrique « Spécifications de Microsoft Access »).

La commande Outils > Options > onglet Modifier/Rechercher, spécifie par défaut que les listes de plus de 1 000 lignes « ne seront pas affichées ». Quel que soit ce nombre (valeur max. acceptée : 32 766), ma liste affiche 65536 variables !?!

C'est tellement frustrant ! Mais constatez que, si vous saisissez « Varz » dans le contrôle, Access trouve la première variable dont le nom commence par « Varz », et le complète pour vous. Appuyez sur Entrée pour l'atteindre, dans le formulaire. Seule la partie déroulante de la liste est limitée, par le jeu d'enregistrements sous-jacent.

Vous pouvez aussi :

  • Trier les enregistrements par noms de variables (champ indexé),
  • Trier par résultat : patience ! Il faudra peut être 5 à 10 minutes pour calculer et trier 200 000 résultats, mais ça marche.
  • etc.

Attention : si vous activez un tri, surtout par résultats, pensez à l'effacer de la propriété du sous-formulaire, sinon, la prochaine ouverture se fera avec le même tri !

  • fenêtre base de données > formulaires > formulaire « 011-Résultats Formules RemplaceSélection » > propriétés > onglet Données > propriété « Tri par » doit être vide.

II-B-2. onglet VBA + DAO : 6 méthodes

Image non disponible

Conseil : avant de lancer les tests, fermez tous autres logiciels, et notamment tout logiciel en tâche de fond, tel qu'un anti-virus en plein scan complet du disque dur, une sauvegarde automatique…

Ouvrir :  sélectionnez requête pour répondre à la question initiale du forum : combien de temps faut-il pour ouvrir une requête permettant de calculer les formules, dans une table de 200 000 enregistrements ?

Nombre d'itérations : Faites plusieurs séries de tests avec une seule ou plusieurs (centaines d')itérations. Vous pourrez constater des résultats surprenants, notamment dus aux diverses optimisations réalisées par Jet et Access.

Méthode :  6 méthodes de calcul différentes, pour comparer les performances.

  • Calibration(la « tare ») :  exécutez ce test pour mesurer le temps nécessaire à l'ouverture de la requête ou à la mise en forme de l'état, sans effectuer le moindre calcul. Notez ce temps, qui représente la « tare » à déduire des autres tests.

Les trois premières méthodes utilisent toutes la fonction Replace() pour remplacer, dans chaque formule, le nom de chaque variable par sa valeur. Puis la méthode Eval() pour calculer le résultat des opérations entre ces nombres. Vous trouverez les détails de chaque méthode dans la seconde partie de ce document.

  • Replace all :  la 1ère méthode évoquée sur le forum lit toutes les variables propres à un type de formule, puis remplace tous les noms de variables par leur valeur, et effectue le calcul.
  • DAO :  cette méthode élimine le besoin de tableaux statiques pour stocker les valeurs des variables, en utilisant une requête DAO. La requête extrait, pour chaque formule, seulement les variables nécessaires au calcul de la formule.
  • Replace selected :  similaire à la méthode « Replace all », celle-ci optimise les performances en reprenant l'extraction des variables concernées, comme dans la méthode DAO. Elle utilise un algorithme de recherche rapide pour trouver la valeur correspondant à chaque variable dans le tableau statique.

Les trois méthodes suivantes sont toutes basées sur un « parsing » (en français « évaluation »). Méthode utilisée par les compilateurs pour analyser chaque ligne de code. Entre autres, la fonction Eval(), qui permet de calculer les expressions dans les 3 premiers essais, réalise son propre « parsing ».

Chaque formule est décortiquée en ses divers éléments, chaque nom de variable est remplacé par sa valeur, chaque opération est effectuée en respectant l'ordre des priorités (parenthèses d'abord, produits (*, /) ensuite et finalement les sommes (+, -).

  • Evaluate1 :  Première méthode, la + simple : effectue les calculs dans l'ordre où elle les rencontre. Fait « marche arrière » si l'ordre des priorités n'a pas été respecté.
  • Evaluate2 :  Seconde méthode similaire. Utilise des appels récursifs (en clair : s'appelle elle-même) pour exécuter des éléments de formule, en fonction des priorités.
  • Evaluate3 :  Troisième du lot. Cette méthode n'est pas la plus rapide, mais sera plus intéressante, sur le plan « scolaire », pour des étudiants qui souhaitent créer leur propre EVALuateur() : elle calcule chaque élément de la formule dans l'ordre dicté par les priorités et peut donc être facilement étendue avec d'autres opérateurs et autant de niveaux de priorité que nécessaire.

II-B-3. Résultats d'ouverture de la requête

Quelques secondes ?  Même avec un vieux bouzin à 3 ou 400 Mhz… mais je refuse de publier ici les résultats chiffrés : à vous de lancer le test, sur votre propre machine, et d'apprécier les résultats.

J'espère quand même que vous aurez remarqué quelques surprises :

II-B-3-a. 1ère ouverture

Si, immédiatement après avoir ouvert le banc d'essai, vous demandez une seule itération et vous ouvrez d'abord la requête sans aucun calcul (option Calibration ("Tare")), puis, ensuite, vous ouvrez la requête suivante (Replace all) par exemple, vous constaterez que la première requête, sans aucun calcul, peut prendre plus longtemps que la seconde, avec calculs !

Eh oui, on a souvent évoqué les optimisations d'Access et de Jet : la 1ère fois qu'Access ouvre une table, il lit les enregistrements en mémoire. S'il a de la place, et s'ils n'ont pas été modifiés, il les conserve et s'en ressert ensuite, tout en adaptant le calcul de la formule…

La 2e ouverture, malgré les temps de calcul supplémentaires, est plus rapide !

Si vous n'y avez pas fait attention, fermez Access, fermez votre session Windows en cours pour bien nettoyer la mémoire, relancez le banc d'essai avec une seule itération, notez le 1er résultat et comparez aux suivants.

II-B-3-b. Combien de calculs réellement effectués ?

Il devient surtout vite évident qu'Access ne peut pas évaluer 200 000 formules en des temps aussi courts.

Pour savoir précisément combien de fois chaque fonction est évaluée, entrez dans le code VBA, ouvrez le module 2_ReplaceAll, entrez dans la fonction ReplaceAll. C'est celle qui est appelée par la requête 001-Résultats Formules RemplaceTout.

Sur la 4e ligne, qui a été mise en commentaires, se trouve la variable statique Counter (Compteur). Enlevez les commentaires qui la bloquent, ainsi que les 2 lignes plus bas :

 
Sélectionnez
Public Function ReplaceAll(ByVal TypeFormule As String, ByVal Formule As String) As Variant
Dim Pointeur        As Integer
Dim Curseur         As DAO.Recordset
'ralentit l'exécution, mais affiche le nombre d'enregistrements réellement calculés
Static Counter     As Long
    On Error GoTo Catch
   Counter = Counter + 1
   Debug.Print Counter
    If mCurrentType <> TypeFormule Then
        'récupère les variables et les valeurs:

Pour lire le nombre d'exécutions qui s'accumulent dans la variable Counter, partagez votre écran en 2 :

  • à ma droite, le module 2_ReplaceAll, avec, en dessous, la fenêtre d'exécution (Ctrl+G),
  • à ma gauche, la fenêtre d'Access dans laquelle vous ouvrez, en plein écran, la requête 001-Résultats Formules RemplaceTout.
Image non disponible

Lors du 1er affichage, la variable Counter affiche, dans la fenêtre d'exécution, exactement le nombre de lignes visibles à l'écran. Dans la fenêtre de gauche, faites défiler les enregistrements, sautez à la fin de la requête, et suivez la progression du compteur…

Ce qui veut dire qu'Access ne se casse tout simplement pas la tête à calculer ce qui n'est pas affiché ou requis par du code.

Vous êtes déçus ?  Vous préféreriez qu'il calcule tout, quitte à attendre une ½ heure avant qu'il n'affiche les 1ères lignes de la requête ?  D'accord, allons y…

II-C. État de siège

Après avoir fait tous vos essais comparatifs avec les requêtes, revenez sur le banc d'essai, onglet VBA/DAO, et sélectionnez Ouvrir — état.

Pour savoir combien de temps il faut à Access pour calculer nos 200 000 formules, l'astuce est simple :

  • afficher les résultats dans un état,
  • ajouter le nombre de pages (balise [Pages]) dans le pied de page de l'état,
  • ouvrir l'état.

Pour calculer le nombre de pages, Access est obligé de calculer la place prise sur l'état par chacun des 200 000 enregistrements, donc, de formater toutes les pages jusqu'à la dernière.

Si vous avez des états très importants, souvenez vous de cela, mais dans l'autre sens : supprimez la balise 'nombre de [Pages]' pour accélérer l'ouverture d'un état en mode aperçu, ou l'impression de la 1ère page en mode impression directe.

Image non disponible
Image non disponible

Les résultats du test ci-dessus :

  • La « tare » nécessaire pour afficher l'état sans aucun calcul, indique moins de 30 secondes. C'est le temps nécessaire pour lire et formater les 200 000 enregistrements, en appelant une fonction qui n'effectue aucun calcul.

Tester : La requête à la base de l'état appelle une fonction qui ne fait que renvoyer la valeur qu'elle a reçu en paramètre (voir module 6_Calibration). À vous de remplacer, dans la requête, l'appel de fonction GetVariable()par la valeur de la variable, pour voir s'il y a une différence notable : avec et sans aucun appel de fonction ?

À vérifier : le nombre d'appels de la fonction, en activant le compteur, soit dans GetVariable(), soit dans ReplaceAll(). Vous pourriez aussi constater, en créant un évènement 'Au formatage' pour la section Détail et en vérifiant le paramètre FormatCount, qu'Access passe environ 2 fois sur chaque ligne de l'état. Donc, pour 200 000 enregistrements, la formule est exécutée environ 400 000 fois.

À tester absolument : fonction GetVariable(), avec ou sans Debug.Print Counter. En situation réelle, ne laissez pas traîner des Debug.Print partout dans vos codes, surtout dans une boucle ou une requête, ça coûte très cher en temps d'exécution…

  • Moins de 2 minutes environ pour afficher l'état avec les méthodes les plus rapides, sur le bouzin à roulettes (Pentium 4 à 3 Ghz, 512 Mo RAM).

Jusqu'à plus de 45 minutes (3000 secondes) pour les pires. Soyez conscients qu'un état avec 2000 enregistrements occupe déjà plus de 200 pages. Un état basé sur 200 000 enregistrements en remplit plus de 20 000 !  Je ne vous conseille pas de l'envoyer à l'imprimante.

  • ce qui, dans le meilleur des cas, nous laisse de l'ordre de 90 secondes pour appeler et calculer près de 400 000 formules

II-D. Client/Serveur, où es tu ? que fais tu ?

Je serais très curieux de comparer ces résultats avec un test équivalent sur une base Client/Serveur ou tout autre Système de Gestion de Bases de Données, même de type fichier comme Jet ou MySQL, toutes conditions de réseau mises à part afin de ne pas pénaliser les bases Client/Serveur.
Par exemple, une bonne petite base MSDE, c'est à dire SQL Server, mais portable sur chaque poste, comme celle-ci.

Il est possible, ou même probable, que certaines méthodes détaillées ci après soient aussi rapides ou plus rapides, si elles sont entièrement réécrites dans une « procstock » (Procédure stockée).

Déjà, est-ce que c'est possible ?  qui peut traduire une des méthodes proposées en SQL, ou en créer une équivalente ? et combien de temps pour développer chaque méthode dans le langage des procédures stockées, pour les mettre au point… ?

La mise au point de ce banc d'essai, y compris, pour certaines méthodes, jusqu'à 5 refontes complètes, y compris les tables, les requêtes, la création de l'interface, l'ajout de la « Tare », les cadeaux Bonux, bref, l'ensemble de la programmation, représente moins de 60 heures de ma part, soit de l'ordre d'une centaine d'heures pour l'ensemble des participants.

III. Sous le capot

Pour ceux que cela intéresse et qui ont les notions de base du VBA (Visual Basic for Applications), entrons dans le détail de chaque méthode et des divers codes fournis avec le banc d'essai.

Pour les débutants qui veulent s'accrocher et découvrir le VBA par l'exemple, certaines indications de raccourcis sont incluses au début seulement, mais attention : il n'y aura aucune explication concernant les méthodes de base du langage. Dans ce cas, cliquez sur le nom de la fonction ou de la commande et appuyez sur F1 pour lire l'aide d'Access, faites quelques essais pour mieux comprendre…

Bon courage, vous ne le regretterez pas.

Vous trouverez d'autres commentaires dans le code lui-même.

Les outils utilisés pendant la mise au point incluent notamment : mzTools et Smart Indenter, que vous trouverez tous deux sur la page Outils SGBD de Développez.com. Si vous ne l'avez pas déjà fait, je ne saurais trop vous encourager à les installer et tester de suite ces deux outils gratuits et indispensables.

Passons rapidement sur le module 0_Declarations, qui contient juste 2 variables globales

Je sais ! Je suis le premier à hurler contre les variables globales dans les applications, mais ceci est un banc d'essai, pas une application appelée à évoluer…)

et deux enum, utilisés pour le plaisir de remplacer les valeurs chiffrées par des constantes plus lisibles, dans la version Access 2000.

III-A. L'interface

Un seul formulaire, qui a été désigné dans le menu Outils > Démarrage… comme formulaire de démarrage.

Notez que les procédures ont été triées dans un ordre logique et non dans l'ordre plus ou moins alphabétique qu'utilise Access lors de l'insertion des évènements. La commande « Trier les procédures » de mzTools s'avère indispensable pour cela, ce qui facilite beaucoup le suivi et les évolutions du code à long terme.

  • au démarrage, Form_Load() affiche le nombre d'enregistrements présents dans la table et masque le sous formulaire (sfrm) de modèle.

Clavier : Cliquez sur DisplayRecordCount, et appuyez sur Maj+F2 pour entrer dans la procédure et examiner son contenu.

Appuyez sur Ctrl+Maj+F2 pour revenir en arrière.

À noter : lorsque vous modifiez du code, Ctrl+Maj+F2 revient en arrière sur les dernières lignes que vous avez modifié.

  • Onglet_Change : cet évènement se déclenche lorsqu'on clique sur un titre d'onglet, pour afficher la page correspondante. C'est l'emplacement idéal pour mettre du code à faire tourner en fonction de la page sélectionnée.

La valeur de l'onglet est le n° de page. Si vous ouvrez la page '3', la valeur du contrôle nommé 'Onglet' = 3.

Ici, plutôt que d'utiliser les n°s de pages (de 0 à 4, soit 5 pages), on utilise un enum : cliquez sur tbPageGenerator et appuyez sur Maj+F2 pour atteindre la déclaration de cette constante. Rappel : Ctrl+Maj+F2 pour revenir dans la procédure.

L'avantage est clair : le code est nettement plus lisible si chaque page est désignée par son nom, plutôt que par un n°.

  • chkModèle_AfterUpdate : la seule action liée à la case à cocher est d'afficher ou masquer le sous formulaire (sfrm) contenant le modèle.
  • cmdGenerate_Click : contrôles de validité, avant d'appeler le générateur d'enregistrements. Détails du Générateur dans la section suivante.

La section mise en commentaire pourra vous être utile, si vous en profitez pour fabriquer votre propre générateur : si le nouveau nombre d'enregistrements est inférieur au nombre de lignes existantes, ce code supprime les lignes en trop. Ce qui va plus vite que de vider la table et de recommencer.

Mais nous ne pouvons pas faire cela ici : si nous supprimons des lignes, nous supprimons des variables. Ces variables peuvent être utilisées dans certaines formules à calculer, ce qui renverrait des #Erreurs.

Si le modèle est activé, on sauvegarde également l'enregistrement en cours si nécessaire : DoCmd.RunCommand acCmdSaveRecord.
Après la génération d'enregistrements, on réaffiche le nouveau nombre d'enregistrements de la table TableFormules : DisplayRecordCount, et on envoie un Beep pour réveiller l'utilisateur qui s'était endormi…

  • frmOpen_Click : « frm » est le préfixe que j'utilise pour les « frames » ou groupes d'option. La routine met simplement une valeur par défaut pour le nombre d'itérations, avec message d'avertissement.
  • cmdTestVBA_Click : contrôle de validité des divers paramètres et appel de la fonction ChronoCalculs() (détails dans la section Exécution et chronométrage) qui renverra les résultats sous forme d'une chaîne de caractères.

Les deux procédures suivantes font la même chose sur l'onglet suivant, pour la méthode « tout SQL ».

III-B. Le Générateur

Revenez dans la Sub cmdGenerate_Click (Ctrl+haut), cliquez sur GenereEnregistrements et appuyez sur F2.

Le module 9_Generateur contient toutes procédures utilisées pendant la génération. Il a été créé par fred.g, ancien responsable du forum Access de Developpez.com.

Dans la mesure ou les Types de formules (champ TypeFormule de la table TableFormules) sont incrémentés de 1 à X, on récupère le dernier type, pour pouvoir continuer au-delà : GetMaxCompteurType() va lire le dernier enregistrement de la table.

2 parties :

  • génération des enregistrements avec chacun une variable,
  • ajout des formules, dans les enregistrements créés en phase 1, en utilisant toutes les variables du type.

III-B-1. Phase 1 : génération des enregistrements

Deux sub routines, selon qu'on utilise un modèle ou pas : la première (GenereSansModele()) génère aléatoirement le nombre d'enregistrements (CompteNouveaux) par type, alors que la seconde (GenereAvecModele()) va boucler sur la table contenant vos définitions de modèle. Noter que la table elle-même a été passée en paramètre : nous utilisons directement le RecordsetClone du sous formulaire.

En utilisant le Clone (= la copie) et non pas le Recordset, nous sommes assurés de ne pas perturber l'affichage du sous formulaire.

III-B-1-a. Nombre aléatoire

Pour chaque type, la sub GenereSansModele() prend un nombre aléatoire de variables, donc d'enregistrements par Type de formule.

La fonction Rnd génère un nombre de type Single, dont la valeur est comprise entre 0 et 1. Chaque génération aléatoire part du nombre généré précédemment pour calculer le suivant.

L'utilisation la plus courante est donnée dans l'aide d'Access (cliquez sur Rnd, appuyez sur F1), pour générer un nombre entier entre une valeur minimale et une valeur maximale.

Donc, Int(Rnd * 4000) renverra des nombres entre 0 et 4000.
Int(Rnd * 4000 - 999) renverra des nombres entre -999 et 3001.
Abs(Int(Rnd * 4000 - 999)) renverra des nombres entre 0 et 3001, mais nous aurons deux fois plus de valeurs entre 0 et 1000 qu'entre 1000 et 2000, ou au-delà.

Si vous ne connaissez pas les fonctions Int() et Abs(), cliquez sur le nom de la fonction dans la fenêtre de code, et appuyez sur F1. Je ne le répéterai pas !

Testez la fonction Rnd : ouvrez le module 99_Outils, la sub TestRandom() se trouve à la fin. Cliquez dedans et appuyez sur F5 pour voir une série de nombres aléatoires défiler dans la fenêtre d'exécution (Ctrl+G).

Pour ne pas obtenir systématiquement la même série de nombres, vous pouvez soit appeler la fonction avec un argument numérique, soit, plus simplement appeler Randomize, sans argument. Randomize utilise l'horloge système comme point de départ. L'appel de Randomize est fait dans la sub GenereEnregistrements().

Dans la quasi-totalité des cas, il est donc conseillé d'exécuter Randomize une fois, avant le premier Rnd. À moins de l'exécuter tous les jours à la même heure, au millième de seconde près, vous n'obtiendrez pas deux fois les mêmes séries de nombres.

Enlevez certains commentaires (le 'guillemet simple' en tête de ligne) pour activer Rnd(-1), Rnd(1), Rnd(0) et comparez les résultats. Notamment entre les 2 premières lignes. Vérifiez dans l'aide en ligne les résultats par rapport au signe de l'argument numérique.

Enfin, enlevez les 2 commentaires des 1ères lignes et vérifiez que vous obtenez toujours la même série de nombres après un Rnd(ArgumentNégatif) suivi d'un Randomize(ArgumentNonNull).

III-B-1-b. Ajout des enregistrements

Les deux subs GenereSansModele() et GenereAvecModele() appellent la même fonction AddRecords().

Notez que les noms des variables dont la portée est le Module (Maj+F2 pour lire la déclaration) commencent tous par un 'm' minuscule.

Pour chaque Type de formule, la fonction

  • ajoute chaque enregistrement avec

    • Le n° de Type (« Type1 », puis « Type2 »…) dans le champ TypeFormule,
    • une chaîne de caractères aléatoires dans le nom de la variable (champ NomVariable),
    • une valeur simple (Single) dans le champValeurVariable.

    Rappel : Les formules seront ajoutées lors de la deuxième passe. Chaque formule a besoin de toute la liste des variables.

  • se termine lorsqu'elle a créé le nombre d'enregistrements voulus pour le type (soit un nombre aléatoire, soit le nombre que vous avez indiqué dans le modèle) ou bien lorsque le nombre total d'enregistrements voulus pour la table est atteint. Dans le dernier cas, elle renvoie Vrai  (True = -1), pour que le générateur passe à la phase suivante.

III-B-1-c. Création aléatoire des noms de variables (chaînes)

Les noms de variables, créés par la fonction GenereString() comportent

  • un nombre maximal de caractères limités par la taille du champ NomVariable, passée dans le paramètre LongueurMax,
  • il commence par un nombre variable de lettres aléatoires,

Rappel : Un nom de variable ne peut pas commencer par un chiffre.

  • suivi d'un nombre unique pour le type : on utilise le compteur de variables (paramètre ValeurNombre, correspondant à la variable PointeurNouveaux de la fonction AddRecords()), de 1 au nombre voulu de variables pour le type. Ce qui fait que nous sommes sûrs de ne jamais obtenir 2 fois le même nom de variable dans un type donné.
  • suivi ou non d'une lettre.

Je vous laisse analyser et tester le détail de la fonction GenereAlpha() qui va renvoyer

  • soit un caractère entre « a » et « z »,
  • soit rien (un caractère vide "" ), en fonction du pourcentage passé en paramètre.

III-B-1-d. Création aléatoire des valeurs numériques des variables

La fonction GenereSingle() va renvoyer, pour 10 nombres demandés

  • environ 4 nombres entiers positifs,
  • environ 3 nombres entiers négatifs,
  • environ 2 nombres décimaux positifs,
  • et, généralement, 1 nombre décimal négatif.

Ces nombres seront compris entre 0 et ± 1000 : (Rnd * 1000)

À vous de remplacer '1000' par une constante ou un paramètre, pour générer des nombres avec une autre limite supérieure.

III-B-1-e. Affichage de l'avancement

SysCmd acSysCmdSetStatus, … permet d'afficher un texte dans la barre d'état.

Lors de processus aussi longs que la génération de plusieurs centaines de milliers de lignes, l'affichage du nombre d'enregistrements est plus « parlant » qu'une simple barre de défilement (trop lente). À vous de juger.

Noter le DoEvents qui donne à la commande précédente le temps nécessaire pour s'afficher (mettez-le en commentaire, et relancez une génération courte, juste pour voir, ou plutôt, pour ne pas voir…) et qui fait que votre programme ne bloque pas l'ordinateur pendant toute la durée de la génération : chaqueDoEvents permet à Windows de capturer et traiter les clics de souris, les évènements clavier, de rafraîchir les affichages et autres « évènements système ».

Même si ce phénomène est beaucoup moins contraignant sous Windows 2000 et versions suivantes, il reste important de prévoir des DoEvents quelque part dans chaque boucle qui risque de s'avérer longue, pour ne pas bloquer le fonctionnement de votre application.

A fortiori, une requête basée sur une table importante, est une boucle (du 1er au dernier enregistrement).

III-B-2. Phase 2 : Ajout des formules

Remontons dans la Sub GenereEnregistrements (avec Ctrl+Haut), dont la deuxième partie

  • boucle sur l'ensemble des enregistrements, du dernier au premier,
  • affiche une barre de progression dans la barre d'état,
  • laisse environ un enregistrement sur 5 sans formule,
  • crée une formule pour les 4 autres, et la remplit avec des variables et des opérateurs,
  • à la fin, efface les tableaux de variables de la mémoire.

III-B-2-a. Barre de progression

Cliquez sur SysCmd et appuyez sur F1 pour une documentation complète.

En bref, l'affichage d'une barre de progression (ou jauge d'avancement) dans Access utilise 3 fois cette commande :

  • SysCmd acSysCmdInitMeter… initialise la jauge, avant d'entrer dans la boucle,
  • SysCmd acSysCmdUpdateMeter… affiche l'avancement, dans la boucle,
  • SysCmd acSysCmdRemoveMeter rétablit la barre d'état standard, avec ses messages, après la fin de la boucle.

La création des formules commence par un choix simple parmi divers modèles, dans FormatFormules(). Vous êtes maintenant devenus experts en nombres aléatoires, donc les commentaires dans le code devraient être suffisants pour comprendre le déroulement.

TraduitFormules() va ensuite remplacer chaque caractère du modèle par des noms de variables et des opérateurs :

  • si le Type de Formule a changé, on lit l'ensemble des variables du type dans un tableau statique, (voir ci-dessous)
  • on crée ensuite une chaîne ou chaque caractère du modèle sert à indiquer ce qu'il faut ajouter. Le seul cas un peu particulier est le « # », qui est remplacé par un nom de variable :
  • dans 1 cas sur 5 environ, on réutilise la même variable déjà sélectionnée auparavant,
  • dans 1 autre cas, on utilise la variable qui est déclarée sur le même enregistrement que la formule en cours de traitement,
  • dans les 3 autres cas, on prend au hasard un variable quelconque, parmi toutes celles du Type.

Noter que, lors de l'appel de la fonction, la variable Formule est, à la fois,

  • passée en paramètre pour envoyer la précédente valeur créée par FormatFormules(), et
  • elle reçoit ensuite la version définitive modifiée par TraduitFormules().

À l'intérieur de la fonction, le nom de la fonction elle-même (TraduitFormules) est utilisée comme une variable pour stocker tous résultats temporaires, jusqu'à la construction de la formule finale, à renvoyer. Certains puristes n'aiment pas cela, certains l'aiment chaud… dont je fais partie (je ne vois aucune raison de s'en priver).

III-B-2-b. Une portée de Variables

Nous avons eu, pendant le développement, de grandes discussions sur l'usage ou non de variables statiques (clic sur Static + F1). Elles doivent être évitées dans la mesure où elles occupent de l'espace en mémoire, et ne peuvent pas être supprimées.

Ceci dit, l'alternative, plutôt que de lire une fois les variables dans un tableau, est de relire le jeu d'enregistrement (recordset) à chaque fois, lors de la création de chaque formule. Ce qui est nettement plus long, comme vous pouvez le constater en comparant la méthode DAO (recordset) avec la méthode Replace Selected (tableau statique).

D'autre part, un tableau peut être complètement supprimé en fin de parcours, en utilisant la commande VBA Erase NomTableau, comme le font les diverses subs ResetArray…().

Rappelons enfin que les variables de niveau module, déclarées en tête de module et donc visibles par toutes les procédures du module, sont exactement équivalentes à des variables statiques, de ce point de vue : on ne peut pas les supprimer de la mémoire. A fortiori, les variables globales, visibles dans l'ensemble du projet et à éviter surtout parce qu'elles rendent la maintenance du code très cafouilleuse, sont pareilles.

Exception : les variables de niveau module ou les variables statiques, dans le module d'un formulaire, d'un état ou autre module de classe, sont détruites avec le formulaire, l'état ou l'objet, lorsque celui-ci est fermé.

Seules les variables locales, propres à une seule procédure, sont détruites dès que la procédure est terminée. On n'insistera jamais assez pour

  • toujours utiliser des variables locales, quand c'est possible, visibles à l'intérieur d'une seule procédure,

À noter : un pas de plus est franchi en .Net avec les variables dont la portée peut être réduite à une seule boucle ou condition, donc à une partie seulement d'une procédure !

À quand la variable visible seulement sur la ligne de sa déclaration ?  Non, peut être pas quand même ?

  • aussi bien à l'intérieur d'un module que d'un module à l'autre, toujours passer toutes valeurs d'une procédure à l'autre, en tant que paramètres, dans l'appel,
  • conséquence des 2 points précédents : si vous avez le choix entre une variable statique, et une variable de module, préférez la variable statique : plus la portée d'une variable est réduite, moins il y a de chances d'introduire des bugs.
  • fuir au maximum les variables globales. Dès qu'il y en a un peu trop, on ne s'y retrouve plus pour savoir qui ?, Quoi ?, Quand ? Où ? Pourquoi ? Modifie la valeur de cette variable. Et bug, et bogue et re-debug…

    Le pire cas que l'on puisse rencontrer, lorsqu'on reprend des logiciels existants : une variable globale est passée en paramètre d'une routine à une autre !!!  La routine qui reçoit ce paramètre y a donc accès de deux manières : soit en modifiant le paramètre, soit en modifiant directement la variable globale !  Aaaaaarghh !

  • toujours bien marquer les variables de module avec un préfixe (« m » ou « m_ ») et les (quelques, rares) variables globales (« g » ou « g_ »), pour les identifier immédiatement.
  • autant que possible, si on a besoin de passer des valeurs entre plusieurs modules, ou entre formulaires (qui sont déjà des objets, avec module de classe !), créer, si nécessaire, un objet (module de classe) et structurer les données en propriétés, sous-objets, collections…

Une structure d'objets avec leurs propriétés, souvent assez proche de la structure des données dans les tables (avec leurs champs), est beaucoup plus facile (plus logique) à gérer qu'une foultitude de variables individuelles, globales ou non.

III-C. Exécution et chronométrage

Lorsque vous cliquez sur le bouton cmdTestVBA, l'évènement cmdTestVBA_Click est déclenché et exécute la Subroutine du même nom.

Celle-ci se contente, après divers contrôles des paramètres, de passer ces paramètres à la fonction ChronoCalculs() du module 1_Chronos. Celle-ci

  • boucle pour exécuter chaque opération le nombre de fois que vous avez spécifié dans le contrôle Nombre d'itérations,
  • utilise deux Select Case imbriqués pour déterminer

    • s'il s'agit d'ouvrir une requête ou un état,
    • laquelle des 6 méthodes proposées est sélectionnée.
  • pour certaines méthodes, réinitialise toutes variables statiques avant l'appel, par sécurité,

En cours de débogage, il est fréquent que la réinitialisation, exécutée à la fin de la procédure, ne soit pas exécutée si le fonctionnement normal a été interrompu. Cette commande n'a été insérée ici que pour être utilisée en débogage. Elle peut être mise en commentaire ultérieurement.

  • ouvre la requête ou l'état concerné,
  • additionne, dans la variable Duree1, les temps d'exécution mesurés entre l'heure de début stockée avant l'appel, et l'heure présente, après,
  • pour certaines méthodes, réinitialise les tableaux (arrays) statiques après l'appel, pour récupérer la mémoire utilisée, comme évoqué ci-dessus,
  • affiche l'avancement sur la barre d'état, avec la même méthode qu'en première partie de génération des enregistrements,
  • après la boucle, met en forme le message final, afin d'afficher les résultats dans une étiquette,
  • réveille l'utilisateur avec un beep et nettoie la barre d'état.

Chacune des méthodes concernées va donc ouvrir une requête ou un état portant le nom de la méthode.

Je vous laisse le soin d'ouvrir chaque requête et d'examiner le champ calculé intitulé Resultat pour y lire le nom de la fonction utilisée par chaque méthode.

 
Sélectionnez
Resultat: VraiFaux(EstNull([Formule]); [ValeurVariable]; ReplaceAll(TypeFormule];[Formule]))
  • Dans chaque cas, si aucune formule n'est indiquée (EstNull([Formule]) renvoie True), l'expression renvoie directement la valeur de la variable ([ValeurVariable]).
  • Sinon, elle appelle la fonction destinée à calculer la valeur de la formule. Dans l'exemple ci-dessus, méthode 'Replace all', requête « 001-Résultats Formules RemplaceTout », fonction ReplaceAll().

Ce fonctionnement est conforme au « cahier des charges » défini sur le forum.

Est-ce plus rapide d'exécuter ces tests (VraiFaux… + EstNull…)

  • ici, dans la requête ?
  • dans la formule, en VBA ?

à vous de tester…

IV. Travail à la chaîne (Replace + Eval)

2 types d'approche pour évaluer la valeur de la formule, qui est contenue dans une chaîne de caractères :

  • manipulation de chaînes, méthodes dites Replace :

    • le nom de chaque variable est remplacé par sa valeur,
    • la chaîne complète est passée à la fonction Eval() qui effectue le calcul et renvoie le résultat.
  • évaluation : tout comme le fait la fonction Eval(), nous allons décomposer la chaîne en opérations simples, chaque opération étant constituée d'un opérateur et de deux opérandes, exécuter chaque opération et renvoyer le résultat final. Ces méthodes sont détaillées dans la section suivante : Ébauche d'un évaluateur d'expressions numériques.

IV-A. 1- fonction ReplaceAll()

La première méthode, dans le module 2_ReplaceAll, est simple et brute :

  • à chaque nouveau Type, on parcourt toutes les variables de ce type pour lire leurs noms dans un tableau (array), avec leurs valeurs,
  • puis on évalue la formule, en essayant de remplacer chaque variable par sa valeur, au cas où cette variable serait incluse dans la formule.

Curieusement, la partie la plus importante, en nombre de lignes de code, est la moins utilisée. Étant donné que nous allons traiter parfois plusieurs milliers de fonctions pour un même Type, il paraît judicieux d'aller lire une seule fois les noms des variables et leurs valeurs dans l'ensemble du jeu d'enregistrements. C'est ce que fait la boucle, à l'intérieur de la condition :

 
Sélectionnez
If mCurrentType <> TypeFormule Then … End If

mCurrentType est une variable de niveau module, puisque nous avons préfixé son nom avec un m.
TypeFormule est passé en paramètre : c'est le type de l'enregistrement en cours de traitement.
Au départ, lors du premier appel depuis le premier enregistrement, mCurrentType est vide (""). La condition est donc vraie puisque TypeFormule, lui, ne peut pas l'être. Tout le code entre If et End If est donc exécuté, y compris la dernière ligne :

 
Sélectionnez
mCurrentType = TypeFormule

qui va permettre de stocker le nom du Type en cours.

Lors de ce premier appel, ainsi qu'à chaque fois que nous allons aborder un nouveau type, nous allons :

  • créer un jeu d'enregistrement Curseur, dont le code SQL sera, pour le premier Type :

     
    Sélectionnez
    SELECT NomVariable, ValeurVariable FROM TableFormules WHERE TypeFormule="Type1"

    autrement dit : toutes les variables du type concerné.
    Le type de jeu d'enregistrement est dbOpenSnapshot : un instantané nous permet de nous déplacer en avant ou en arrière sur tous les enregistrements, mais en lecture seule, ce qui lui permet d'être plus rapide qu'un classique dbOpenDynaset.

  • sauter au dernier enregistrement avec le .MoveLast. Si vous consultez la documentation de la propriété .RecordCount du jeu d'enregistrement, vous constaterez que le nombre d'enregistrements ne sera pas fiable tant que le moteur Jet n'a pas atteint au moins une fois le dernier enregistrement.
  • ReDimensionner nos tableaux de noms et de valeurs de variables au nombre d'enregistrements - 1. Pourquoi -1 ?  Simplement parce que le premier élément d'un tableau, à moins que l'on ne le spécifie autrement, est le n° 0. Donc, si nous avons 100 enregistrements, ils seront numérotés de 0 à 99.

Pourquoi deux tableaux à une dimension chacun ? alors que nous aurions pu utiliser un seul tableau à 2 dimensions ?

Avec 2 tableaux séparés, nous accédons à chaque élément du tableau avec un seul index, tel que mTableau_var(Pointeur)

Nous aurions pu créer un seul tableau à 2 'colonnes' avec

ReDim mTableau_var(.RecordCount - 1, 1)

puis y stocker les noms et les valeurs de variables, grâce au deuxième index :

 
Sélectionnez
mTableau_var(Pointeur, 0)= !NomVariable
mTableau_var(Pointeur, 1)= !ValeurVariable

Chipotons, chipotons gaiement : le fait d'utiliser un double index oblige VBA à faire des calculs plus complexes pour trouver l'emplacement de chaque élément, que s'il n'y a qu'une seule dimension. Donc, un tableau simple est plus rapide (pas beaucoup, d'accord, mais bon, les petits ruisseaux…)

Surtout, le fait d'utiliser deux tableaux, chacun avec son nom, permet de savoir tout de suite ce qui est quoi : …var pour les noms de variables, …val pour leurs valeurs.

Et ça, ça peut nous éviter bon nombre de bugs, donc pas d'hésitation.

  • initialiser le Pointeur, qui va nous servir à stocker les variables dans chaque élément des tableaux,
  • Revenir en tête du jeu d'enregistrements avec le .MoveFirst,
  • Boucler jusqu'à ce que nous ayons dépassé le dernier enregistrement, en atteignant la fin du fichier (EOF = End Of File),
  • Remplir les tableaux.

Ensuite, sachant que nous disposons des variables et de leurs valeurs,

  • Une deuxième boucle avec ForNext va ensuite remplacer chaque nom de variable par sa valeur. Pourquoi parcourir les variables de la dernière (UBound(mTableau_var) pointe sur le dernier élément du tableau) à la première et non pas l'inverse ?  Notez déjà qu'il est possible de boucler à l'envers avec l'instruction Step et un argument négatif, mais surtout, cette inversion devait nous permettre d'éviter les erreurs de calcul.

Les boucles ForNext sont elles aussi rapides à l'envers qu'à l'endroit ?  À tester…

IV-A-1. Erreurs de calcul

Vous trouverez sur l'extrait du forum de longues discussions concernant les erreurs trouvées lors des premiers essais : ThreadForumFormule1.doc

Le principal problème est le suivant :

  • prenons 2 variables intitulées respectivement Var1 et Var123, par exemple,

Même si ce cas n'est pas possible avec notre générateur d'enregistrements et de variables, il reste très probable, en situation réelle. Il nous appartient, en tant que développeurs, d'anticiper de tels problèmes.

  • prenons Var1 = 12.34 et Var123 = 56.78, par exemple
  • prenons une formule qui utilise la 2e variable Var123 :
    Formule = Var4 * Var123 + (Var6 - Var7)
  • Lorsque nous essayons de remplacer chaque variable par sa valeur, nous risquons de traiter la variable Var1 avant de traiter la variable Var123.
    L'exécution de la commande Replace, en remplaçant le texte Var1 par sa valeur, soit le texte '12,34' donnerait pour résultat :

     
    Sélectionnez
    Formule = Var4 * 12,3423 + (Var6 - Var7)

    alors que le résultat attendu voudrait que nous ne remplacions que la variable Var123 par sa valeur, soit '56,78' :

     
    Sélectionnez
    Formule = Var4 * 56,78 + (Var6 - Var7)
  • Le résultat renvoyé par la formule sera donc faux, et nous aurons beaucoup de mal à détecter l'erreur, dans un cas comme celui-ci…

Solutions :

Nous avons commencé par utiliser les variables à l'envers. Partant du principe qu'elles sont triées alphabétiquement, il n'est plus possible, théoriquement, de traiter une variable (disons Var1) dont le nom est inclus dans celui d'une autre (disons Var123) avant celle-ci. Var123 sera donc remplacée par sa valeur avant que la boucle n'atteigne Var1.

C'est pourquoi vous constatez que les boucles vont du dernier élément vers le premier :

 
Sélectionnez
 For Pointeur = UBound(mTableau_var) To 0 Step -1

Encore faut il que les variables soient bien triées par ordre alphabétique, ce qui n'était pas le cas dans la fonction ReplaceAll(), par exemple.

Mais je me doute bien que vous aviez déjà rajouté le « ORDER BY NomVariable » manquant, comme vous l'avez remarqué dans les méthodes suivantes.

Et cela n'empêche pas d'autres cas d'erreur, tels que deux variables nommées W1 et AW1 !

Plus efficace car elle n'est pas sujette à l'ordre de tri, est la méthode préconisée par Tofalu et utilisée dans ReplaceSelected() (3e méthode, ci-dessous) :

  • dans le texte de la formule, on insère un espace derrière le nom de chaque variable,
  • on remplace [le nom de la variable et l'espace qui le suit] par [la valeur].

Comme les noms de variables ne peuvent pas contenir d'espace, cette méthode nous garantit que le nom remplacé est bien le bon : "Var1 " n'est plus contenu dans "Var123 ".

En réalité, on a encore "W1 " inclus dans "AW1 ", donc, pour prévoir tous les cas possibles, il faut ajouter (au moins) un espace avant et un autre après chaque nom de variable.

IV-A-1-a. Version française de Windows : virgule décimale

Du fait que nos tests sont réalisés sous une version française de Windows et qu'Access utilise comme séparateur décimal celui qui est désigné dans le panneau de configuration — options régionales et linguistiques, les valeurs transformées en chaînes de caractères pour la fonction Replace incluront une virgule.

Vous avez probablement noté dans l'exemple ci-dessus concernant les erreurs, que j'ai utilisé le point décimal en ce qui concerne les valeurs, telles que VBA les stocke en mémoire et les utilise pour les calculs, mais une virgule, lorsque ces mêmes valeurs sont transformées en chaînes de caractères.

Lors de l'appel de la fonction Eval, nous devons remplacer ces virgules par des points décimaux (paramètre US), seul paramètre acceptable par VBA en général et la fonction Eval en particulier :

 
Sélectionnez
ReplaceAll = Eval(Replace(Formule, ",", "."))

IV-A-1-b. Sortie et contrôle d'erreur

Comme nous travaillons proprement, nous fermons le jeu d'enregistrement, même après une erreur. La commande

 
Sélectionnez
Resume Finally

qui s'exécute à la suite de toute erreur, renvoie toujours l'exécution vers l'étiquette Finally:, ce qui nous assure que le jeu d'enregistrement sera bien fermé.

Normalement, nous n'utilisons jamais d'instruction du type

 
Sélectionnez
On Error Resume Next

Si une erreur se produit parmi les instructions qui suivent celle ci, nous n'en serons jamais avertis ! Ce qui nous empêche d'intervenir et de corriger l'erreur en question.

Voici donc une des rares exceptions : dans la mesure où nous avons fini le travail, nous ne voulons plus que fermer proprement la routine et, si une erreur se produit, tant pis, on continue quand même…

La dernière instruction, Resume,n'est pas là pour faire joli !  Elle est utile pendant la mise au point du code et le débogage. Pendant cette période, vous pouvez mettre un point d'arrêt, soit en appuyant sur F9 (la ligne de code devient marron), soit en insérant l'instruction Stop, juste après le Catch:, de manière à ce que l'exécution s'arrête en cas d'erreur.
Dans ce cas, après avoir analysé le code et le message d'erreur (Err.Description), vous sauterez directement (avec Ctrl+F9) à la commande Resume pour retourner (avec F8) sur la ligne où l'erreur s'est produite et corriger ce qui a pu causer l'erreur.

Enfin, la fonction CVErr() (Conversion en Erreur) renvoie un objet de type 'Erreur', ce qui permettra, dans le champ [Resultat] de la requête, d'afficher la chaîne de caractères « #Erreur », pour signaler le hic.

IV-A-2. 2- fonction Conversion(), en DAO

La seconde méthode, dans le module 3_DAOConversions, mise au point par Maxence Hubiche, utilise une approche plus subtile :

  • elle n'utilise aucune variable statique, en allant relire chacune des variables nécessaires directement dans la table,
  • elle transcrit la formule en une liste des noms de variables, dans la chaîne sCrit. Cette chaîne contiendra juste les noms des variables séparés par des virgules et délimités par de simples guillemets.
  • un jeu d'enregistrements extrait de la table les variables dont le nom apparaît dans la formule, en utilisant l'opérateur SQL IN. L'instruction SQL complète ressemblera à quelque chose comme :

     
    Sélectionnez
    SELECT NomVariable, ValeurVariable
    From TableFormules
    WHERE TypeFormule = 'Type1'
    AND NomVariable In('Var1','Var6','Var123','Var45')
    ORDER BY TypeFormule
  • une boucle sur ce jeu d'enregistrements permet de ne remplacer que les noms des variables présentes dans la formule par leurs valeurs.

Cette méthode gagne du temps sur le nombre d'appels de la méthode Replace, lorsque le nombre de variables est élevé, et on a vu, sur le forum, qu'il pouvait y avoir plusieurs milliers de variables par Type.

Elle se révèle moins rapide, par contre, du fait qu'elle lit ces valeurs directement dans le jeu d'enregistrements plutôt qu'en mémoire.

IV-A-3. 3- fonction ReplaceSelected()

La troisième méthode, dans le module 2_ReplaceSelect, combine les avantages des 2 précédentes :

  • utilisation de tableaux statiques pour stocker les valeurs des variables, à ne remplir qu'une seule fois pour toutes les formules de chaque type,
  • extraction de la liste des variables concernées par la formule en cours, pour ne remplacer que ces valeurs là dans le corps de la formule,
  • de plus, elle introduit une routine très classique de recherche rapide, pour trouver le nom et la valeur de chaque variable dans ces tableaux,
  • enfin, des 3 méthodes ci-dessus, c'est la seule qui est à l'abri des erreurs évoquées plus haut ("W1 " inclus dans "AW1 "…)

IV-A-3-a. Recherche rapide dans un tableau

La fonction SearchArray(), utilisée par la 3e méthode seulement, est basée sur le principe suivant :

Prenons un tableau de 100 noms, avec des indices de 0 à 99, triés alphabétiquement,

La requête créée pour lire les noms de variables les a triées par ordre croissant.

Une méthode « brute » consisterait à parcourir tous ces noms un par un, jusqu'à trouver le bon. Statistiquement,

  • si nous admettons que chacun des noms a autant de chance que les autres d'être recherché,
  • sur 100 éléments,
  • il faudra en moyenne en examiner 50 par recherche.

L'idée générale de la recherche rapide est la suivante :  si, au lieu de commencer au 1er élément et d'examiner jusqu'au dernier (100e), nous commençons au milieu (50e) et nous regardons s'il faut continuer la recherche vers le haut ou vers le bas, il ne reste déjà plus que 50 éléments à examiner au lieu de 100. Le même principe sera alors appliqué aux 50 éléments restants : continuer parmi les 25 premiers ou les 25 derniers ?…

Concrètement,

  • le paramètre Variable contient le nom de la variable que nous cherchons,
  • au départ, nous fixons les bornes basses (variable Lowbound) et hautes (variable Highbound) entre lesquelles nous devons chercher la valeur, soit 0 (le plus petit élément) et 99 (le plus grand),
  • le pointeur va désigner l'élément du milieu : 50, au départ,
  • on examine cet élément, et on détermine :

    • soit, c'est le bon, on s'arrête,
    • soit la valeur cherchée est plus petite : on met HighBound= 49, de manière à chercher entre les éléments 0 et 49, et on rappelle récursivement SearchArray() pour chercher entre ces 2 nouvelles bornes,
    • soit la valeur cherchée est plus grande : on met LowBound = 51, de manière à continuer la recherche au dessus, toujours récursivement.
  • Les appels récursifs continuent, jusqu'à ce qu'on trouve l'élément cherché.

Prenons le pire scénario, toujours avec 100 éléments, et en considérant que l'élément est toujours plus petit que la valeur examinée. Cela donnerait le même résultat s'il était plus grand, puisqu'à chaque étape, nous divisons par 2 le nombre d'éléments à examiner.

Premier essai : entre 0 et 99, test du n°50,

2e essai : 0-49, test élément n° 25 La division par 2 est arrondie à l'entier le + proche et j'arrondis au dessus exprès, étant pessimiste…

3e : 0-24, test n°12
4e : 0-11, test n°6
5e : 0-5, test n°3
6e : 0-2, test n°1

7ème :0-0 Théoriquement, même pas besoin de l'examiner c'est obligatoirement le bon. À tout hasard, nous avons quand même prévu de vérifier et renvoyer une erreur si l'élément recherché n'est pas trouvé.

En conclusion, dans le pire des cas, il nous aura fallu examiner 7 valeurs avant de trouver la bonne, soit, en moyenne, 3½ tests. Au lieu des 50 tests, toujours en moyenne, pour le trouver avec une recherche séquentielle. Les appels récursifs prennent du temps aussi, bien sûr, mais le gain global est encore très important.

V. Ébauche d'un évaluateur d'expressions numériques

L'exercice, qui consiste à ne pas utiliser la fonction Eval() pour calculer le résultat, ne présente strictement aucun intérêt pour les applications que vous aurez à développer pour des clients.

D'autant plus que ces méthodes se révèlent bien sûr moins rapides que la fonction de VBA. Ce qui, si vous me permettez de commencer par la conclusion, nous confirme simplement ce dont on se serait douté : s'il existe une méthode, dans le langage lui-même (VBA, DAO, ADO, SQL ou tout autre composant), cette méthode « interne » sera toujours beaucoup plus efficace que celle que nous reproduirons en plusieurs lignes de code VBA à compiler.

C'est une évidence, mais on est là pour s'amuser et, entre autres multiples questions auxquelles j'aimerais que ce banc d'essai réponde, figure celle-ci : de quel ordre est la différence entre une méthode interne, donc probablement compilée en C++ ou équivalent, et une fonction équivalente, écrite en VBA ?  10 fois plus, 100 fois, 2 fois seulement ?

Nous n'irons pas jusqu'à créer un compilateur en VBA !  Mais les méthodes d'analyse de chaque ligne de code par les interpréteurs et les compilateurs sont du même ordre que ce que nous faisons ici : extraire chaque variable et chaque instruction, utiliser les adresses ou les valeurs des variables pour exécuter l'instruction avec les bons paramètres…

Sachant aussi que cette approche bénéficie quand même de quelques avantages :

  • nous passons directement à l'analyse (évaluation) de la fonction, sans appeler les innombrables Replace() nécessités par les méthodes précédentes,
  • la fonction Eval() de VBA est capable d'effectuer de nombreuses opérations mathématiques complexes, alors que la nôtre se contente des 4 opérateurs de base, avec parenthèses…

Et les résultats sont encore une fois assez étonnants, par leur vitesse d'exécution.

Mais vous avez déjà fait les tests…

V-A. Principes généraux de l'évaluation

Avant de nous plonger dans l'examen du code, établissons un petit cahier des charges de ce qu'il faudra faire pour évaluer chaque formule et renvoyer le résultat.

Prenons un exemple simple, avec quelques variables et leurs valeurs :

 
Sélectionnez
Formule = Var1 +(Var2 - Var3 * (Var4 + Var2) + Var5) - Var3

avec, inutile de se compliquer la vie, :

 
Sélectionnez
Var1 = 1
Var2 = 2
Var3 = 3
Var4 = 4
Var5 = 5

Nous allons devoir :

  • extraire, caractère par caractère, le nom de chaque variable dans une chaîne temporaire. Une fois trouvé le nom, nous pouvons trouver et utiliser la valeur pour les calculs.
    Ce qui donnerait : Formule = 1 + (2 - 3 * (4 + 2) + 5) - 3
  • déterminer l'ordre des priorités entre opérations,

Les niveaux de priorité sont une des notions les plus importantes pour l'exactitude de nos calculs. Il ne s'agit ici heureusement que de mathématiques très primaires donc 3 niveaux de priorité seulement :

  • parenthèses d'abord,
  • fonctions ensuite (*, /)
  • et finalement les sommes (+, -).

Une approche simple consisterait à insérer des parenthèses autour de chaque opération, pour marquer ces priorités.
Inutile pour le 1er niveau : il s'agit des parenthèses réelles, qui sont déjà là.
Pour le niveau 2, les fonctions, cela donnerait, en utilisant des <> pour indiquer les parenthèses virtuelles :

 
Sélectionnez
Formule=Var1+ (Var2 - <Var3 * (Var4 + Var2)> + Var5)- Var3

soit, en valeurs numériques :

 
Sélectionnez
Formule=1 + (2 - <3 * (4 + 2) > + 5)- 3

soit :

 
Sélectionnez
Formule=1 + (2 - <3 * 6> + 5) - 3

et finalement :

 
Sélectionnez
Formule=1 + 2 - 18 + 5 - 3

Non, je ne vous donnerai pas le résultat final. Ce serait trop facile.

que nous pourrons exécuter dans n'importe quel ordre.

À chaque étape, nous devons

  • détecter l'opérateur, en isolant

    • le premier opérande, qui précède l'opérateur,
    • le second opérande, qui le suit.
  • exécuter chaque opération,
  • renvoyer le résultat final.

La première idée, assez évidente dès qu'on parle de parenthèses, est que, du moment que notre routine est capable d'évaluer une formule plus ou moins complexe, elle est a fortiori capable d'en évaluer une partie.

Donc, si nous isolons une partie de la formule située entre 2 parenthèses (réelles ou virtuelles), nous pourrons appeler récursivement la routine principale, qui renverra juste la valeur du contenu des parenthèses.

V-B. 1er essai : Evaluate1()

Cette fonction, créée par Tofalu, est dans le module 4_Evaluate_1.

Je passe sur la lecture des noms et valeurs de variables dans les tableaux statiques : identique aux méthodes précédentes.

Seule nous intéresse donc la deuxième partie, la fonction Compute().

Nous n'utiliserons pas la notion de parenthèses virtuelles.

Nous exécuterons les opérations dans l'ordre, de gauche à droite et nous ferons « marche arrière » en cas de non respect des niveaux de priorité.

Nous allons exécuter les opérations en déterminant

  • le premier opérande (valeur numérique), ou opérande de gauche,
  • l'opérateur (+, -, *, /), stocké dans une chaîne d'un seul caractère,
  • le second opérande (numérique) ou opérande de droite.

La première opération ne sera donc exécutée qu'après avoir isolé le second opérande, soit au moment où le pointeur CharPointer arrive sur le deuxième opérateur (détails ci-dessous).

Après avoir exécuté la première opération, son résultat devient le premier opérande de la suivante, et, si tout se passe bien, on continue ainsi jusqu'au bout de la formule.

À cause de cette disposition, nous commençons par ajouter en fin de formule un opérateur « bidon » ("§"), qui servira juste à déclencher la toute dernière opération :

 
Sélectionnez
Formule = Formule & "§"

La fonction va donc examiner un par un les caractères qui composent la chaîne de la formule,

  • CharPointer, le pointeur, va indiquer la position de chacun de ces caractères, du premier au dernier (Len(Formule)),
  • CharBuffer va stocker temporairement le caractère en question,
  • NextOperand va stocker temporairement une chaîne qui sera le nom d'une variable, ou, dans le cas d'une expression entre parenthèses, directement la valeur calculée de cette expression,

C'est très pratique d'utiliser une chaîne de caractères aussi bien pour stocker un texte comme un nom de variable, que pour stocker un résultat numérique. Mais soyons conscients que nous perdons du temps, à cause de toutes les opérations de conversion implicites que VBA doit accomplir.

Lorsqu'on affecte la valeur d'OpenRealBracket(), qui est un double à NextOperand, qui est une chaîne, VBA doit la « traduire » en une chaîne.

Lorsqu'on utilise ensuite cette valeur numérique pour une opération, VBA doit, à l'inverse, retraduire la chaîne en une variable numérique.

De plus, chaque conversion, y compris les conversions de numérique Single à Double, peut induire des erreurs d'arrondi que vous constaterez si vous ouvrez les diverses requêtes avec la table de vérification (voir Contrôle des résultats), et comparez les diverses méthodes.

Quelqu'un veut s'amuser à voir si on peut accélérer cette méthode en utilisant soit un variant, soit 2 variables :

  • une de type chaîne pour les noms de variables,
  • une autre, numérique, pour les valeurs.

Le gain de temps est-il significatif, négligeable ou négatif, à cause des tests en plus ?  Pouvez-vous faire disparaître les erreurs d'arrondi ?

  • Operand1 va stocker temporairement le premier opérande, celui de gauche, de chaque opération.
    Dans un premier temps, il récupère juste la valeur numérique (double) de NextOperand.
  • Plus tard, après avoir exécuté une opération, le résultat temporaire sera encore stocké dans Operand1, d'où il servira comme premier opérateur (de gauche) pour la prochaine opération, tant qu'il y en a.
  • Finalement, après la dernière opération, il contiendra le résultat final, à renvoyer par la fonction Compute().

Nous ne pouvons pas faire comme dans les méthodes précédentes avec Replace(), en remplaçant par exemple le nom de chaque variable par sa valeur, directement dans la chaîne de caractères qui constitue la Formule. Nous ne pouvons rien modifier dans la formule elle-même, sinon le pointeur CharPointer perdrait la boussole.

Nous allons distinguer les trois cas qui nous intéressent :
Soit CharBuffer

  • est une parenthèse : nous allons évaluer en priorité le contenu des parenthèses, et remplacer ce bloc de texte par la valeur calculée,

Évidemment, il ne peut s'agir que d'une parenthèse ouvrante.

Si nous rencontrions une parenthèse fermante avant d'avoir trouvé sa parenthèse ouvrante, nous devrions lever (Err.Raise) une erreur de syntaxe.

Mais, vous avez bien compris que nous ne voulions pas vous embrouiller avec de tels détails…

  • soit il s'agit d'une lettre qui fait partie du nom d'une variable, et nous allons extraire le nom de cette variable, caractère par caractère,
  • soit il s'agit d'un opérateur et nous allons exécuter une des 4 opérations de base (+, -, *, /) en respectant les priorités.

Les espaces sont simplement ignorés. Ils sont toujours suivis d'un nom de variable, d'une parenthèse, d'un opérateur ou de la fin de la formule.

Par contre, nous avons ajouté un caractère spécial ("§") à la fin de la formule. Ce caractère ne nécessite aucun traitement particulier, mais nous permet simplement, au niveau du select case, de brancher sur l'exécution de la dernière opération, celle dont le 2e opérande est la dernière variable ou le dernier jeu de parenthèses.

V-B-1. Silence, Action !

Plongeons dans le code. Concrètement,

  • s'il s'agit d'un espace, nous ne faisons rien.

Ouf ! Jusque là, ça va ?

  • s'il s'agit d'une parenthèse ouvrante, on appelle la fonction OpenRealBracket() (Ouvre Parenthèse Réelle).

    Celle-ci va calculer la valeur de tout ce qui se trouve entre les parenthèses et renvoyer cette valeur dans NextOperand :

    • d'abord, il faut trouver la parenthèse fermante qui correspond à la parenthèse ouvrante que nous avons repérée. Dans notre exemple,

       
      Sélectionnez
      (Var2 - Var3 * (Var4 + Var2) + Var5)…

      On voit bien qu'il ne suffit pas de faire un InStr(). Un Instr() qui rechercherait une parenthèse fermante (")") au-delà de la première parenthèse ouvrante détectée, trouverait celle qui suit + Var2), alors que celle qui correspond est située après + Var5).
      On parcourt donc le reste de la formule en comptant les « niveaux de parenthèses » qui augmentent à chaque "(" trouvée, et diminuent avec chaque ")".
      Et on continue jusqu'à ce qu'on trouve une ")", alors que le niveau est à 0, le même niveau que la parenthèse de départ.

    • Ensuite, c'est tout le contenu des parenthèses qui est stocké dans SubFormula, sans les parenthèses, soit :

       
      Sélectionnez
      Var2 - Var3 * (Var4 + Var2) + Var5
    • et on rappelle la fonction Compute() qui renverra la valeur de cette « sous-formule ».

      Il s'agit bien ici d'un appel récursif, c'est-à-dire d'une fonction qui s'appelle elle-même, sauf que l'appel est sur 2 niveaux :

      • Compute() appelle d'abord OpenRealBracket()

        • C'est OpenRealBracket() qui va appeler récursivement Compute() avec une partie de la formule comme argument.

      Notez avec l'exemple ci-dessus, que l'appel récursif à Compute() va trouver une autre parenthèse ouvrante avant Var4, donc appeler à nouveau OpenRealBracket(), lequel va extraire la sous formule « Var4 + Var2 », rappeler une 3e fois Compute() avec cet argument, etc.

    • avant de sortir, elle positionne le pointeur d'avancement (CharPointer) au-delà de la parenthèse fermante, pour que la fonction principale (Compute()) continue son examen à partir de ce point.
  • Gardons pour le dessert le cas où il s'agit d'un opérateur et examinons d'abord le cas simple ou il s'agit d'un caractère alphanumérique (Case Else, juste avant la fin de la fonction Compute()). Ce caractère ne peut faire partie que du nom d'une variable, et nous reconstruisons ce nom dans NextOperand, en bouclant jusqu'à pointer sur un autre opérateur.

Après un nom de variable, on ne peut trouver qu'un opérateur. Ou alors, il y a une erreur de syntaxe dans la formule, que nous devrions détecter et lever (Err.Raise)

V-B-2. Sweeeet Operator

D'abord, c'est pas « Sweet operator », tu confonds avec « Sweet dreams are made of this… » de Eurythmics.

C'est « Smooth Operator », et c'est Shade.

Ah, ouais, merci.

Attention, c'est là que ça se complique, donc, concentration.

  • Finalement, il peut s'agir d'un opérateur,

    Remarque sur les priorités :

    Nous ne sommes plus concernés ici par les parenthèses réelles. S'il y en a eu avant, leur contenu a été traité el le résultat stocké dans Operand1. S'il y en a d'autres plus loin, nous les traiterons de la même manière.

    Nous n'avons donc plus à traiter que 2 niveaux :

    • chaque produit (*, /), à traiter immédiatement,
    • chaque somme, à traiter après les produits qui la suivent directement, s'il y en a.
    • Lorsque nous rencontrons le premier opérateur, If NotFirstOp = False

      Si « Pas Première Opération » est faux, autrement dit, si « Première Opération » est vrai (je vous avais dit de vous accrocher…), la valeur contenue dans NextOperand peut être soit une valeur numérique renvoyée par OpenRealBrackets(), soit une chaîne : le nom de la première variable en tête de la formule.
      Nous allons juste stocker sa valeur dans Operand1, qui servira de 1er opérande pour la première opération.

      Si NextOperand est une chaîne, il contient le nom d'une variable et la fonction SearchArray() va renvoyer la valeur de cette variable. C'est la même que ci-dessus, dans la section Recherche rapide dans un tableau.

      Le code continue au-delà de l'End If, on stocke l'opérateur dans la variable Operator, et on efface NextOperand, pour pouvoir y stocker le prochain opérande.

    • La boucle va continuer de la même manière : au-delà du 1er opérateur, nous ne pouvons trouver qu'une parenthèse, ou un nom de variable. Dans les 2 cas, NextOperand va servir à lire ce 2e opérande (de droite) de la première opération.
    • Au-delà du premier opérateur de la formule, et du 2e opérande, lorsque nous rencontrons le 2e opérateur, il est temps d'effectuer la 1ère opération entre Operand1 (1er opérande), Operator (l'opérateur) et NextOperand, le second opérande.

      Nous stockerons ensuite le résultat dans Operand1, qui deviendra alors le 1er opérande de la 2e opération, et nous continuerons jusqu'au bout de la formule.

    • Oui, mais, si la 1ère opération est une somme (Operator = "+" ou "-"), et la 2e opération est un produit (CharBuffer = "*" ou "/"), ben ça va plus.

      Il faudrait d'abord exécuter le produit (2e opération), puis seulement ensuite, la somme (1ère opération).

    • Donc, si l'opération (la première) est une somme,

      • on met tous ses éléments en mémoire, dans PreviousOperand1, PreviousOperator et PreviousOperand2,
      • on l'exécute quand même,
      • et on stocke le résultat dans Operand1, comme prévu.

        Pourquoi l'exécuter maintenant alors qu'on a peut être un produit juste derrière ?

        Ben, justement, à cause du « peut être ». Si la prochaine opération n'est pas un produit, la première opération sera OK, et on continuera…

        Et alors, y aurait pas un petit test à faire, là, qui pourrait encore accélérer ça un tout petit poil ? Moi ??? Jamais parlé de CharBuffer… Et c'est pas du tout sûr que ce soit plus rapide…

      • Si l'opération est un produit, par contre, 2 cas se présentent, en fonction de l'opération précédente, correspondant à PreviousOperator, enregistré ci-dessus en cas de somme et juste ici, une ligne plus bas, en cas de produit  :

        1. c'était déjà un produit, ou bien il s'agit de la première (aucune opération précédente) (Case Else) : rien de spécial, on exécute le produit normalement, en stockant le résultat dans Operand1.
        2. l'opération précédente était une somme (Case "+" ou Case "-") : il faut la défaire, pour exécuter d'abord le produit (opération 2) puis la somme seulement après. La ligne

           
          Sélectionnez
          Operand1 = PreviousOperand1 + PreviousOperand2 * Operand2

          fait juste cela : elle ignore le résultat précédent qui était stocké dans Operand1,  elle recalcule dans l'ordre les 2 opérations, en faisant confiance à VBA. VBA va d'abord calculer PreviousOperand2 * Operand2 avant d'ajouter le tout à PreviousOperand1 et de stocker le résultat à nouveau dans Operand1.

          Nous avons maintenant, dans Operand1, le résultat des 2 opérations, lequel résultat respecte bien l'ordre des priorités.

          Oui, mais ! Que se passe t'il, si, derrière ces deux opérations, la troisième est encore un produit ?

          Rien ne va plus. Ou plutôt, nous sommes obligés de résoudre le produit (la deuxième opération) et stocker son résultat :

           
          Sélectionnez
          PreviousOperand2 = PreviousOperand2 * Operand2

          Ainsi,

          • soit on trouvera une somme ensuite, et le résultat de toutes opérations précédentes étant dans Operand1, no problemos,
          • soit on trouvera encore un produit et on pourra recommencer comme ci-dessus en ré exécutant la même opération :

             
            Sélectionnez
            Operand1 = PreviousOperand1 + PreviousOperand2 * Operand2

            PreviousOperand1 est toujours le 1er opérande de la 1ère somme,

            PreviousOperand2 est le résultat du premier produit (2e opération),

            que l'on multiplie/divise par Operand2 pour réaliser le 3e produit.

V-B-3. Exemple de somme suivie de deux produits

Pour illustrer ce cas le plus complexe (somme suivie de deux produits), suivons l'évolution des calculs en prenant directement des valeurs numériques simples :

 
Sélectionnez
1 + 2 * 3 / 4 + 5

La première opération nous donne :

  • Operand1 = 1
  • Operator = "+"
  • Operand2 = 2

Lors de la détection du deuxième opérateur, CharBuffer = "*".

Operator = "+", donc, on met de côté les opérandes et on calcule la 1ère somme :

  • PreviousOperand1 = 1
  • PreviousOperator = "+"
  • PreviousOperand2 = 2
  • Operand1 = 1 + 2 = 3
  • Operator = "*" (voir plus bas, sous le End Select)

Lors de la détection du troisième opérateur, CharBuffer = "/" et Operand2 = 3.

Operator = "*", et PreviousOperator = "+", donc on calcule

  • Operand1 = 1 + 2 * 3 = 1 + 6 = 7 ce qui, jusque là, est conforme aux règles de priorité.
  • PreviousOperand1 = 1  ' reste inchangé
  • PreviousOperator ="+"  ' reste inchangé
  • PreviousOperand2 = 2 * 3 = 6

Cette opération étant un produit n'aura jamais besoin d'être défaite ou refaite, quelle que soit la prochaine opération.

  • Operator = "/"

Lors de la détection du quatrième opérateur, CharBuffer = "+" et Operand2 = 4.

Operator = "/", et PreviousOperator = "+", donc on calcule

  • Operand1 = 1 + 6 / 4 = 1 + 1.5 = 2.5

ce qui, jusque là, est conforme aux règles de priorité.

  • PreviousOperand2 = 6 / 4 = 1.5
  • Operator = "+"

Lors de la dernière opération, on n'aura plus qu'une somme à effectuer, et

  • Operand1 = 2.5 + 5 = 7.5

ce qui est correct.

En bref, dès que nous avons détecté une somme,

  • nous gardons son premier opérande et son opérateur en mémoire,
  • tant que nous trouvons des produits à sa suite,
  • nous recalculons la somme du 1er opérande avec le résultat du ou des produits qui la suivent,
  • nous stockons le résultat du ou des produits successifs dans PreviousOperand2,

Allez, il y a encore quelques millièmes de secondes à gagner, sur ces 2 lignes. Vous avez trouvé ?

  • dès que nous trouvons une autre somme, nous l'exécutons simplement dans Operand1.

V-C. Contrôle des résultats

Non seulement, vous n'êtes pas obligés de me faire confiance, de croire que les résultats sont justes, que personne ne s'est trompé…

Mais vous souhaiterez peut être créer votre propre méthode de calcul avec son module, sa ou ses fonctions, sa requête, etc.

Pour vérifier si une méthode, quelle que soit sa rapidité, donne des résultats justes, dans la fenêtre Base de données, onglet Tables,

  • renommez la table TableFormules en TableFormules-200000, ou autre nom qui vous permette de retrouver ensuite vos enregistrements complets, sans avoir besoin de relancer le générateur,
  • renommez la table TableFormules_CheckResults en TableFormules,
  • ouvrez cette table. Vous constaterez qu'elle ne contient que 10 formules.
    Un champ supplémentaire ([ExpectedResults]) indique le résultat attendu et quelques remarques à vérifier : que "Var1" ne soit pas inclus dans "Var123", pour la 2e formule, quelques erreurs de calcul qui doivent apparaître comme #Erreur

Rien ne vous empêche d'ajouter vos propres « formules type » pour tester de manière encore plus précise des résultats que vous aurez vérifié manuellement, ou ajouter et contrôler des formules avec des erreurs de syntaxe (trop de parenthèses ouvrantes, ou fermantes, ou inversées, caractères non conformes…)

Ah, vous croyiez que ça serait simple, de créer un compilateur ?

  • ouvrez la requête qui utilise la fonction que vous voulez tester,
  • placez les fenêtres en mosaïque horizontale, au dessus l'une de l'autre, sans recouvrement, de manière à comparer les résultats de votre requête avec ceux qui étaient « attendus », dans la table de test.

Après vos tests, n'oubliez pas de renommer inversement :

  • TableFormules en TableFormules_CheckResults,
  • TableFormules-200000 en TableFormules.

V-D. 2ème essai : Evaluate2()

Forts de notre expérience avec la 1ère méthode, nous allons essayer de simplifier l'évaluation, ce qui, dans la plupart des cas, amène aussi une amélioration des performances.

Le point faible de la fonction Evaluate1() est la gestion des parenthèses virtuelles, c'est à dire des niveaux de priorité entre les sommes et les produits. L'idée de calculer une opération pour ensuite la jeter à la poubelle si les priorités n'étaient pas respectées manque d'élégance.

Essayons d'analyser ce cas précis, et d'en tirer un nouveau « cahier des charges » plus efficace :

  • niveau 1 : il n'y a rien à changer dans notre gestion des « sous formules » incluses à l'intérieur de parenthèses réelles. Elles sont immédiatement calculées et remplacées par leur valeur.
  • niveau 2 : les produits peuvent toujours être évalués comme ci-dessus, et le résultat du produit peut être stocké dans la variable qui va, au final, renvoyer la valeur de la formule. Jusque là, tout va bien.
  • niveau 3 : puisque les sommes, comme toute opération,

    • sont composées de 2 opérandes (et d'un opérateur),
    • que nous ne savons pas encore si le 2e opérande, qui est égal à tout le reste de la formule, à droite de l'opérateur, contient ou non des parenthèses, des produits, bref, quelque chose dont le niveau de priorité serait supérieur au niveau de cette somme,
    • pourquoi ne pas tout simplement traiter ce 2e opérande comme une sous expression, comme s'il était inclus entre des parenthèses ?

En fait, tout ce que nous allons faire, c'est, à chaque fois qu'on rencontre une somme, ajouter un couple de parenthèses virtuelles autour du 2e opérande.

Si nous reprenons l'exemple cité plus haut :

 
Sélectionnez
Formule=Var1 + (Var2 - Var3 * (Var4 + Var2) + Var5) - Var3

dès que nous rencontrons le premier opérateur de somme (Var1 +…), nous allons ajouter des parenthèses virtuelles autour de tout ce qu'il reste :

 
Sélectionnez
Formule=Var1 + ((Var2 - Var3 * (Var4 + Var2) + Var5) - Var3)

exécuter cette partie de la formule d'abord, puis l'ajouter à Var1.

V-D-1. Le code d'Evaluate2()

Encore une fois, passons sur la lecture des variables dans un tableau. Elle a été « sortie » de la fonction principale et stockée dans la sub GetVariables(), mais c'est exactement le même code que précédemment.

Nous n'utiliserons plus de pointeur pour parcourir le texte de la formule, puisque, pour chaque opération, nous allons évaluer chaque opérande séparément.

Nous allons extraire

  • le 1er opérande, stocké dans la variable FirstOperand,
  • l'opérateur,  dans NextOperator,
  • le 2nd opérande (soit un bloc entre parenthèses réelles, soit un nom de variable), n'est pas stocké, mais extrait au fur et à mesure par la fonction NextOperand(),
  • le reste : la variable Remainder va garder la trace de ce qu'il nous reste encore à évaluer, au-delà du 2nd opérande, pour compléter le calcul de la formule.

Nous en profiterons pour n'utiliser que des valeurs numériques (jamais de chaîne) pour chaque opérande. Toutes les fonctions utilisées renverrons donc également un résultat, sous forme d'un nombre double.

Et, tant qu'à faire, plutôt qu'un caractère, nous enregistrerons l'opérateur (NextOperator) dans une variable numérique, plus rapide à manipuler. Le code ASCII des signes « + - * / » étant entre 42 et 47 tient très bien dans une variable numérique Byte (de 0 à 255).

FirstOperand = GetNextOperand(…)

Vous vous souvenez, dans l'étude d'Evaluate1(), que nous devions traiter spécialement le cas du premier opérande de la formule, en utilisant un indicateur (flag) booléen (NotFirstOp).

Ici, la recherche du premier opérande est dans une subroutine séparée, ce qui nous permet de commencer directement, sans aucun indicateur, d'où un code plus lisible, plus facile à maintenir plus tard.

Rappelons qu'en dehors des logiciels dits « jetables », qui ne servent que quelques jours, les coûts les plus importants dans la durée de vie d'un logiciel concernent la maintenance : débogage, support technique, améliorations selon les besoins des utilisateurs, mises à jour avec l'évolution des systèmes…

En ce sens, et en dehors de toute considération de performance, une routine facile à lire coûte beaucoup moins cher à long terme qu'un code obscur.

De plus, elle s'avère généralement plus performante ! À contrôler au cas par cas, bien sûr, mais statistiquement très valable.

V-D-2. La fonction « Va chercher le prochain opérande »

GetNextOperand() fait ce que nous faisions déjà la 1ère fois, simplement de manière un peu plus structurée (lisibilité, lisibilité, lisibilité) :

  • soit extraire des parenthèses réelles, les exécuter récursivement, et renvoyer la valeur de la sous expression entre parenthèses,

Dans ce cas, il y aura un « reste » : toute la partie de la formule située au-delà de la parenthèse fermante. Ce reste sera renvoyé dans le paramètre Remainder.

  • soit lire un nom de variable, et renvoyer sa valeur.

Dans ce cas, le « reste » sera toute la partie de la formule située au-delà du nom de la variable.

Notez que, dans l'appel de la fonction, le paramètre Expression est passé avec un Trim(). On supprime tout espace en tête (et en fin) de l'expression à évaluer, ce qui nous permet d'utiliser directement la fonction Asc(Expression) pour connaître le code du premier caractère qui n'est pas un espace. Asc() nous renvoie le code du premier caractère d'Expression. Comme précédemment c'est, soit  une parenthèse ouvrante (code ASCII = 40), soit le début d'un nom de variable.

V-D-3. La fonction « Ouvre Parenthèses »

OpenBrackets() n'est qu'une variante de la précédente OpenRealBrackets() du module 4_Evaluate_1.

À part quelques petites variations sur la manière de conduire la boucle : « Tant que le caractère examiné n'est pas une parenthèse fermante au niveau 0 de parenthèses », et du fait que nous ignorons l'absence de parenthèse fermante, alors qu'un compilateur digne de ce nom devrait renvoyer une erreur de syntaxe, la fonction

  • renvoie toujours, par un appel récursif à la fonction principale Evaluate2(), le résultat de ce qui se trouve entre les parenthèses,
  • extrait la suite dans le paramètre Remainder,
  • et en profite tout de suite pour également détecter le prochain opérateur.

Comme nous l'avons évoqué avec la 1ère méthode d'évaluation, à la suite d'un bloc entre parenthèses réelles, ou à la suite d'un nom de variable, on ne peut trouver qu'un autre opérateur.

Toutes ces opérations font appel à la fonction Trim() de VBA pour éliminer les espaces.

V-D-4. La fonction « Va chercher la prochaine variable »

GetNextVar() est ultra simple, puisqu'elle se contente de chercher dans une boucle simple la fin du nom de la variable.

  • si elle trouve un espace, elle extrait le prochain opérateur du texte qui suit,
  • si elle trouve un opérateur, elle le stocke directement dans NextOperator,
  • dans les 2 cas ci-dessus, on garde « ce qu'il reste encore au-delà » dans Remainder,
  • après la sortie de la boucle, au cas où on n'aurait rien trouvé, on vide Remainder, puisqu'il ne reste rien au-delà du dernier nom de variable,
  • Mid(Expression, 1,TempCharPointer - 1) va extraire juste le nom de la variable en question, et
  • SearchArray(), comme dans les méthodes précédentes, va nous renvoyer sa valeur.

Y a-t-il quelqu'un que ça intéresse de voir si on ne pourrait pas grappiller encore quelques précieux (dix- ou cent-)millièmes de secondes ?

Notre boucle, avec TempCharPointer, est écrite en VBA et il est certain qu'un Instr() trouverait beaucoup plus vite la position du 1er espace contenu dans Expression.

Oui, mais, ce qu'il nous faut, c'est

  • trouver le 1er espace,
  • trouver la 1ère occurrence de chacun des 4 opérateurs,
  • parmi les réponses valides (si Instr() a trouvé, donc n'a pas renvoyé zéro), ne garder que la plus petite.

V-D-5. La boucle principale

n'a plus grand-chose à faire…

À chaque passage dans la boucle, nous disposons

  • du 1er opérande FirstOperand, lequel constitue le résultat des calculs précédents déjà effectués,
  • de l'opérateur NextOperator extrait par GetNextOperand() au passage précédent,
  • du second opérande dont la valeur est renvoyée par GetNextOperand().

Il n'y a plus qu'à

  • boucler jusqu'à ce qu'il n'y ait plus aucun reste dans Remainder,
  • exécuter directement les produits,
  • résoudre toute la partie de la formule située à droite d'une somme avant d'exécuter la somme elle-même. Ici, un simple appel récursif à Evaluate2() résout le problème.

En tout cas, une chose dont je suis certain, encore une fois en dehors de toute considération de performance : il était indispensable de créer une première méthode d'évaluation qui marche, mais la deuxième est nettement plus facile à expliquer par écrit que la première.

Ceci n'est qu'un exemple type de ré-ingénierie. Concernant tout code sur lequel vous serez appelé à travailler, et qui est, soit ancien, soit a été écrit par quelqu'un d'autre, n'hésitez jamais à

  • changer (Ctrl+H)  les noms des subs, fonctions et variables. De préférence remplacer par un nom plus explicite pour vous, mais il est important de « s'approprier » le code.
  • réécrire, et tester pour vérifier qu'on y gagne, chaque partie de routine qui n'est pas claire.

Ce qui implique : à chaque fois que la position d'une ligne de code est importante (exécuter cette instruction avanttelle autre, sinon erreur n°…), toujours marquer cela très clairement dans les commentaires du code. Pour éviter de reproduire les mêmes erreurs 6 mois plus tard.

N'hésitez pas à vous approprier le code qui est dans ce banc d'essai. Pas pour le revendre sous votre nom, merci, mais simplement pour qu'il devienne limpide à vos yeux.

Le temps passé à de telles (ré-)appropriations est du temps gagné, pas du temps perdu.

V-E. 3ème essai : Evaluate3()

Encore ?  Ben, oui. Quand on est maniaque, on pourrait fort bien ne jamais s'arrêter.

Ce qui représente sûrement un des pires dangers pour les développeurs passionnés : à force de vouloir faire mieux (et on peut toujours faire mieux), on ne termine jamais rien !!!

Après, il faut encore apprendre à terminer « quelque chose qui marche », et au-delà, les génies du marketing vous expliqueront qu'il vaut beaucoup mieux vendre une première version lente et ultra simple, ne serait-ce que pour pouvoir vendre une version 2 plus tard, puis une version 3, …

La seule chose qui reste à clarifier c'est qu'on n'a jamais établi clairement les niveaux de priorité, simplement parce qu'on n'en a que 3.

  • On traite le niveau le plus haut d'abord, avec les parenthèses réelles,
  • on traite spécialement le plus bas, avec les sommes.

Mais on ne traite en aucune manière le, ou, dans un cadre plus général, les multiples niveaux qui pourraient s'imbriquer entre le premier et le dernier, si nous avions des expressions plus complexes à traiter.

Cette fois-ci, nous allons,

  • chercher et résoudre toutes les opérations au 1er niveau : tous les blocs entre parenthèses,
  • chercher et résoudre chaque type d'opération, dans l'ordre des niveaux d'intégrité. En ce qui nous concerne, pas bien compliqué : tous les produits, puis toutes les sommes.
  • à chaque étape, l'opération ou le bloc de code entre parenthèses sera remplacé, dans la chaîne de caractères de la formule, par son résultat. Jusqu'à ce que la formule ne contienne plus que son propre résultat.

Il est donc clair que vous pourrez, ce qui serait déjà un excellent exercice d'école, ajouter tous les niveaux d'opérations souhaités pour évaluer des types d'expressions beaucoup plus complexes que nos seules 4 opérations de base.

V-E-1. Nettoyage préalable

Aaargh ! On était censés ne jamais utiliser la fonction Replace() !

Pas vraiment quand même : on ne voulait plus l'utiliser pour remplacer, dans la chaîne de caractères qui constitue la formule, un nom de variable par sa valeur. Ici, on se contente de supprimer d'un coup tous les espaces, histoire d'éviter les nombreux Trim() utilisés dans la fonction Evaluate2() et ses subroutines.

Ensuite, on passe le tout en minuscules. La raison sera évidente lors de la recherche des signes « - », pour les soustractions. Étant donné que nous stockons les résultats intermédiaires dans la formule elle-même, celle-ci pourrait contenir des valeurs en notation scientifique telles que : … + 1.345 E-2 * …. Dans un tel cas, avec exposant négatif, nous ne devons pas interpréter le signe « - » comme étant une opération de soustraction. Nous détecterons pour cela le 'E' majuscule qui le précède, sachant qu'aucun nom de variable ne peut plus contenir de 'E' majuscule.

V-E-2. Niveau 1 : (blocs entre parenthèses)

Nous cherchons directement la première parenthèse ouvrante, et nous la remplaçons par sa valeur.

Il faudra donc que la fonction OpenBracket() renvoie une chaîne de caractères, et que cette chaîne contienne la valeur du bloc suivi du reste de la formule.

V-E-2-a. OpenBrackets()

La recherche de la parenthèse fermante est similaire aux recherches des méthodes précédentes.

La valeur renvoyée par OpenBracket va, en une ligne,

  • extraire le bloc entre parenthèses : Mid(Expression, 1, TempCharPointer - 1)
  • calculer sa valeur par un appel récursif à Evaluate3 : Evaluate3(TypeFormule, Mid(Expression, 1, TempCharPointer - 1))
  • concaténer le « reste » (qui était stocké dans Remainder, pour la méthode Evaluate2()),
  • renvoyer le tout.

V-E-3. Niveaux suivants

En dehors de l'opération elle-même (+, - , * ou /), toutes les fonctions qui évaluent un résultat fonctionnent exactement de la même manière.

On aurait donc pu se contenter d'une seule fonction avec un paramètre désignant l'opération à exécuter. On aurait alors, dans cette fonction « universelle », un Select Case pour déterminer l'opération à effectuer.

Il est certain qu'une seule routine de code à maintenir au lieu de 4, va revenir beaucoup moins cher, à long terme, en heures de maintenance : toute mise au point ne devra être réalisée qu'une seule fois au lieu de 4 fois.

Oui, mais là, le Select Case nous imposerait un temps de réponse supplémentaire. Étant donné notre impératif de performances, voici une exception qui confirme la règle : dans ce cas, 4 routines séparées seront plus rapides qu'une seule.

Si nous partons d'un exemple comme

 
Sélectionnez
Formule = Var1+Var2*Var3-Var4

avec l'intention de résoudre la multiplication, nous avons comme paramètre

  • cette formule complète dans Expression,
  • la position du "*" dans le paramètre Pos, soit 10.

La collecte du 1er opérande (GetFirstOperand) détermine également la position de départ du 1er caractère de l'opération, soit

  • FirstOperand = valeur de Var2
  • StartPos = 6 (le début de "Var2*Var3").

On cherchera de l'arrière vers l'avant le premier opérateur qui précède.

Cette recherche ressemble beaucoup à celle que nous avions, pour la méthode Evaluate2(), dans GetNextVar(). Rappelons, pour les fous qui chercheraient à accélérer encore tout cela, qu'il existe, depuis la version 2000 d'Access, une fonction InstrRev() qui fait une recherche rapide en arrière.

N'importe quel opérande pourra être soit un nom de variable, soit le résultat d'une opération précédente.

En ce qui concerne le symbole "-" qui peut être soit un opérateur de soustraction, soit un indicateur de signe négatif, nous devrons traiter spécialement

  • le cas d'un signe en tête de l'expression. Expression = -5 +Var2*Var3-Var4
  • les exposants négatifs, dans le cas d'un résultat intermédiaire exprimé en notation scientifique (E‑03).

De même, le calcul du 2e opérande (GetNextOperand) déterminera

  • NextOperand = valeur de Var3
  • EndPos = 14 (position du dernier caractère de "Var2*Var3").

En dehors du fait qu'il recherche vers l'avant et non vers l'arrière, GetNextOperand() est similaire à GetFirstOperand().

V-F. Evaluate4() ?

On voit bien qu'en dehors de son plan d'action plus simple que les précédentes, la dernière méthode, Evaluate3(), est loin d'être parfaite : elle enregistre tous les résultats intermédiaires dans la chaîne de caractères de la formule, alors que nous avions évoqué, à propos de la première (Evaluate1()) les lourdes et néfastes opérations de conversion entre chaîne de caractères et valeurs numériques que VBA doit accomplir en plus.

Alors ?  Peut-on se passer de ces conversions et garder le meilleur des 2 mondes en n'effectuant que des opérations numériques comme Evaluate2(), mais avec les possibilités d'extension d'Evaluate3() ?

Peut-on surtout partir dans une tout autre direction pour aller encore plus vite ?

À vous de jouer…

VI. Les cadeaux Bonux 

Une solution très originale avait été suggérée par Sasmira, pour l'évaluation de la formule.

Elle consistait à

  • ouvrir un tableau Excel par automation,
  • créer des Noms (au sens d'Excel) à partir de chaque variable,
  • coller les formules dans des cellules,
  • chaque cellule affiche le résultat.

Joli, non ?  Cette méthode a été testée par fred.g, mais pas mise en œuvre dans la mesure où il fallait compter de l'ordre de 20 minutes pour un petit type, et les limitations sont très sévères : nombre de noms maximum, etc.

Dommage.

VI-A. SQL pur

Elle marche pas. Enfin, si vous essayez d'ouvrir directement la requête 002-qrySQLFormule1, avec une table de 200 000 enregistrements ou plus, outre le temps passé à essayer d'évaluer chaque formule, vous trouverez pas mal d'#Erreurs !

Image non disponible

Access n'est même pas capable de créer une requête avec 200 000 colonnes !  Nul !

Recréez une table de 254 variables au plus : c'est hélas, le nombre maximum de colonnes qu'Access permet dans une requête telle que l'analyse croisée (002-qrySQLgetVariables).

Mais admirez la beauté de l'interprétation des variables !

VI-A-1. + de 254 lignes en SQL ?

En conservant un maximum de 254 variables par Type de formule, ce qui peut se faire en utilisant un modèle avec le générateur d'enregistrements, serait-il possible d'avoir plusieurs types dans la table : à chaque type correspondrait une seule analyse croisée de 254 colonnes, ce qui, théoriquement, devrait être possible ?

Voir divers essais infructueux dans le module 5_SQL.

VI-B. La fonction crée la fonction

Il n'y a pas de VBE sous Access 97 : cette méthode n'apparaît pas dans la base formule1-97.mdb

D'accord, l'idée est débile, mais elle reste une des plus simple de toutes :

La fonction appelée depuis la requête pourrait-elle, en utilisant les commandes de l'objet VBE (Visual Basic Environment),

  • ouvrir un module,
  • y créer une fonction,
  • pour chaque variable, déclarer une constante avec nom = valeur
  • ajouter la formule tout en bas,
  • renvoyer le résultat de la formule qui ne contient que des constantes et des opérations simples ?

Oui, ça marche. Du moins la création d'une fonction. Et pas à partir d'une requête, ni même à partir du code VBA de l'application en cours.

Les commandes VBE qui créent du code dans un module ne peuvent s'exécuter qu'en mode « Arrêt », lorsque le VBA de l'application est arrêté. Si ces commandes VBE sont situées dans un module de l'application, dès qu'elles essayent de modifier une ligne d'un module, une erreur est déclenchée. Il faut donc qu'elles soient situées dans une librairie séparée, ou dans un Complément (Add-In).

Juste pour rire :

  • Vérifiez, dans l'environnement VBA, menu Outils, Références, que vous avez une référence vers le projet Access CreateFunction.mdb.
    Sinon, ajoutez la référence en cliquant sur Parcourir…, sélectionnez le type « Bases de données (*mdb) », …
  • Ouvrez le module 7_CreateFunction,
  • Vérifiez qu'il n'y a, dans ce module, aucune ligne de code en dessous de

     
    Sélectionnez
    Option Compare Database
    Option Explicit
  • S'il y en a, effacez les toutes,
  • dans la fenêtre d'exécution (Ctrl + G), tapez CreateFormulaFunction("Type1") et appuyez sur Entrée,
  • Vous devez voir apparaître une nouvelle fonction FormulaType1() As Variant contenant toutes les variables du Type 1 et la 1ère formule de votre table TableFormules,
  • Toujours dans la fenêtre d'exécution, pour afficher le résultat, tapez plus bas :

     
    Sélectionnez
    ? FormulaType1()

    et appuyez sur Entrée,

  • Vous devez lire la valeur calculée de la 1ère fonction, que vous pouvez vérifier en ouvrant n'importe quelle requête de type Evaluate_1 à 3 ou Remplace…

Ça vous plairait de taper votre code aussi vite que VBE ?

Ça n'est pas très compliqué : un clic sur le nom CreateFormulaFunction, appuyez sur F2. À peine 23 lignes de code…

Amusez vous bien avec, je l'espère, plus de beetles que de bugs à l'arrivée :o)

VII. Remerciements

Merci à chacun des participants pour leurs apports, leurs idées, leur enthousiasme, à tofalu (christophe warin) et maxence hubiche, pour leur participation active aux solutions codées.

Un spécial à fred.g (frédéric grasa) pour son générateur d'enregistrements, d'une part, pour sa relecture avec coup de pied au c.. dynamique et efficace, d'autre part.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2017 Etienne Pailleret. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.