Formules mathématiques dynamiques avec Microsoft ACCESS.

Tout le monde le sait, ACCESS n'a pas la réputation de flexibilité d'EXCEL pour tout ce qui touche aux calculs. En effet, rien ne semble prévu pour qu'un utilisateur d'application puisse employer des formules de calculs avec ACCESS. Généralement, c'est à la charge du programmeur de prévoir, concevoir et faire procéder à la résolution de celles-ci. L'objectif de cet article est de démontrer que nous pouvons laisser l'utilisateur maître de la création de formules de calculs complexes.

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Introduction

On reproche souvent à ACCESS de ne pas être souple en matière de formules mathématiques. En effet, le comparer à EXCEL est impossible, chaque produit a son utilité. C'est la raison pour laquelle l'automation permet des traitements qui sont difficiles, voire impossibles à reproduire avec ACCESS. Comme toute affirmation a son contraire, nous allons démontrer, avec certaines limites, que nous pouvons créer des formules mathématiques dynamiques avec ACCESS. Dynamique dans le sens où elles peuvent être entrées directement par l'utilisateur.

II. Un cas bien concret

Pour la démonstration, nous allons résoudre un problème souvent rencontré dans la gestion de contrats à moyen et long terme. Ce type de gestion est généralement assorti d'une clause de revalorisation contractuelle. En résumé, chaque année à date anniversaire du contrat, il est convenu d'une revalorisation indexée sur des indices officiels. Pour la France, vous pouvez consulter la majorité de ceux-ci sur le site INSEE. On utilise le ratio d'un indice constaté entre la date actuelle et la date année-1. Les contrats de location d'habitations en sont un parfait exemple.

II-A. Analyse rapide

Dans un premier temps, attachons-nous à analyser les différents composants de la solution.
Qu'avons-nous en entrée ?
Nous avons une ou plusieurs formules, les indices actuels et les indices précédents.
Que voulons-nous obtenir ?
Un coefficient de revalorisation ou directement un résultat.

II-B. Le pré-requis

Nous allons avoir besoin de plusieurs objets pour la démonstration. Commençons par les tables.

Table : tblFormules - Nom du champ Type du champ - Taille Description
N_Formule NuméroAuto identifie la formule
Formule Texte - 255 stocke la formule de calcul



Dans un cas pratique, il peut être utile de créer un champ supplémentaire pour stocker une description de la formule.

Table : tblIndices - Nom du champ Type du champ - Taille Description
TypeIndice Texte - 15 l'abréviation de l'indice dans la formule; souvent celui-ci correspond à sa dénomination officielle.
Mois Numérique - Entier Le numéro du mois (1 à 12).
Annee Numérique - Entier Le numéro de l'année sur 4 chiffres.
Indice Réel La valeur de l'indice avec 2 décimales maximum.



Dans TypeIndice, nous stockerons le nom de l'indice (BT48, CF, EBIQ…). Indice contiendra la valeur; le type réel simple est très largement suffisant puisque les indices comportent au maximum 2 décimales et la partie entière rarement 4 chiffres.

En règle générale, on utilise plusieurs indices qui peuvent être différents suivant la catégorie du métier. Il est évident que les métiers de la restauration n'ont que faire de l'indice du fil de cuivre ou de celui de la construction.

II-C. Les données

Voilà le décor planté; nous pouvons mettre quelques indices dans la table. Puis nous passerons au gros du travail, la création du moteur de calcul.

TypeIndice Mois Année Indice
EBIQ 6 2007 100
EBIQ 6 2008 105.5
TCH 6 2007 114.4
TCH 6 2008 115.1
ICHTTS 6 2007 135
ICHTTS 6 2008 142

Notez que les indices EBIQ, TCH et ICHTTS sont des indices INSEE bien réels, seules les dates et les valeurs ne le sont pas. Par exemple TCH est l'un des indices des prix à la consommation, hors tabac et alcool.

Dans la table tblFormules, nous allons saisir quelques formules. Certaines ne feront pas du tout appel aux indices, rassurez-vous ! c'est volontaire !

N_Formule Formule
1 (Sqr(4)*4)+1
2 _Montant*(0,15*(_EBIQ1/_EBIQ0)+0,2(_TCH1/_TCH0)+0,65*(_ICHTTS1/_ICHTTS0))

Il n'y a pas de lettre 0 mais le chiffre zéro dans la dernière formule.

La formule numéro 1 contient Sqr qui est une fonction mathématique native de VBA. Elle renvoie la racine carré du nombre passé en argument. Si on la calcule de tête, voici ce que nous obtenons:

  1. (sqr(4) *4)+1
  2. (2 * 4 )+ 1
  3. 8 + 1
  4. 9

La deuxième formule est un peu plus complexe. Vous remarquerez que nous avons des opérateurs simples (+, *, /), des nombres décimaux (0,15 ; 0,2…) et également des éléments précédés d'un underscore (_). Ces derniers représentent les variables de la formule.

Admettons que la revalorisation se fasse en juin 2008 (06/2008) pour l'année précédente, soit juin 2007. Si nous recherchons les indices Année et Année-1, voici ce que nous obtiendrons :

  1. _Montant*(0,15*(_EBIQ1/_EBIQ0)+0,2*(_TCH1/_TCH0)+0,65*(_ICHTTS1/_ICHTTS0))
  2. _Montant*(0,15*(105,5/100)+0,2*(114,4/116)+0,65*(135/126.7))

Montant sera remplacé par le montant du contrat ou par 100 pour indiquer un pourcentage de revalorisation.

Vous savez tout sur la revalorisation du moins sur la partie calcul.

III. De la formule au résultat

Deux solutions s'offrent à nous, la première est de passer par Excel, de lui laisser faire les calculs puis de réintégrer les résultats dans la base ACCESS; très simple quand il s'agit d'une formule avec une seule date. Mais que se passe-t-il lorsque vous avez 300 contrats, 10 formules différentes et presque autant de dates de révision ?
Tout faire passer dans une feuille Excel pré-programmée ?
Un peu lourd et surtout long et sujet à problèmes.

III-A. MS ACCESS sur un air d'EXCEL ou comment créer son propre moteur de calcul

Ouvrez un module et créez une fonction publique que vous appellerez Calcule() (pas le nom mais le verbe conjugué) avec un paramètre String en entrée et un type Variant en sortie.

 
Sélectionnez

Public Function Calcule(cFormule as String) as Variant

End Function

Nous avons choisi un Variant en sortie pour nous permettre de calculer n'importe quel type de données, du numérique au texte.

Dans un premier temps, nous allons renvoyer le résultat de la fonction mathématique Sqr(4). Ecrivez

 
Sélectionnez

Calcule = Sqr(4)

Passez dans la fenêtre d'exécution puis écrivez.

 
Sélectionnez

? calcule("")

Le raccourci pour accéder à la fenêtre d'éxécution dans VBE et ctrl+G.

Validez. Bravo ! Vous avez réussi à faire une fonction qui renvoie la racine carré de 4.

III-B. Alimentez le moteur de calcul

Pour bien comprendre comment va fonctionner notre fonction de calcul, nous allons passer par plusieurs étapes. Remplacez la ligne de la fonction Calcule

 
Sélectionnez

Calcule = sqr(4)

Par la ligne suivante.

 
Sélectionnez

Calcule = Eval("sqr(4)")

Passez dans la fenêtre d'exécution puis écrivez.

 
Sélectionnez

? Calcule("")

La fonction renvoie toujours 2 ? C'est que ça fonctionne.

III-C. Quelques mots sur la fonction EVAL

La fonction Eval() permet d'évaluer des expressions de type string (texte) valides. Inutile d'essayer d'exécuter tout et n'importe quoi, seules quelques fonctions natives et celles de l'utilisateur fonctionnent. Ce qui ne fonctionne pas :

 
Sélectionnez

Eval("msgbox ""bonjour""")

Ce qui fonctionne :

 
Sélectionnez

Eval("msgbox(""Bonjour"")")

Notez que le complément GenMessager disponible gratuitement sur ce site utilise cette fonction pour afficher la boîte de message composée par l'utilisateur.

III-D. Concrétisez le moteur de calcul

Après cet intermède ludique et vulgarisateur, revenons à notre moteur de calcul. Remplacez la ligne :

 
Sélectionnez

Calcule = Eval("Sqr(4)")

Par celle-ci.

 
Sélectionnez

Calcule = Eval(cFormule)

Et testons cette modification dans la fenêtre d'exécution en tapant ceci.

 
Sélectionnez

? calcule("sqr(4)")

Mais non ! Ne croyez pas que notre fonction ne sait renvoyer que 2. Essayez avec une autre valeur ou d'autres fonctions et formules !

 
Sélectionnez

? calcule("sqr(8)")
? calcule("Year(Date)-1")

Voilà notre moteur est terminé.

IV. Calcul des formules

Il existe plusieurs méthodes pour lire le contenu d'une table ; le dlookup ou le recordset. Utilisons le recordset. Allez dans le menu Outils/Références et sélectionnez Microsoft DAO 3.6 Object Library.
Créez une nouvelle fonction que nous appellerons LireFormules().

 
Sélectionnez

Public Function LireFormules(idFormule As Integer) as string
' idFormule est le  de la formule dans la table
'Auteur  Fabrice CONSTANS (loufab) http://loufab.developpez.com 2009
	Dim strCrit As String
	Dim rst As Recordset                                ' déclaration de l'objet
	Set rst = CurrentDb.OpenRecordset("tblFormules", dbOpenSnapshot)   ' initialisation du recordset
	With rst
    	strCrit = .Fields(0).Name & " = " & idFormule		'composition du critère de recherche
    	.FindFirst strCrit					                ' recherche
    	If Not .NoMatch Then Debug.Print Calcule(.Fields(1))	' trouve et affiche le résultat
   		.close  						' fermeture
	End With
	Set rst = nothing					' libération
End Function

Dans la fenêtre d'exécution, testons cette nouvelle fonction. Tapez

 
Sélectionnez

? LireFormules(1)
? LireFormules(2)

Nous sommes maintenant capables d'utiliser une formule issue d'une table.

V. Fonction d'interprétation des formules.

Pour l'instant, nous évaluons une formule simple. Pour évaluer nos formules de revalorisation, il faut remplacer les indices par leur valeur respective. Nous avons besoin de 2 dates, la date de revalorisation et la date d'origine qui détermineront quels indices utiliser dans la période. L'intervalle étant fixé contractuellement, vous pourrez donc rencontrer des cas différents et de multiples formules.
On peut rencontrer plusieurs types d'indices mais toujours par paire, selon la formule suivante.

IndiceOrigine / indiceActuel

Voici la fonction :

 
Sélectionnez

Function Revalorise(Montant As Currency, DateOrigine As Date, DateActuelle As Date, iFormule As Integer) As Currency
'Montant est le montant à revaloriser
'DateOrigine date d'origine ou date la plus ancienne
'DateActuelle date de revalorisation
'iFormule est le  de la formule
'Auteur  Fabrice CONSTANS (loufab) http://loufab.developpez.com 2009

On Error GoTo Err_Revalorise

Dim fml As String           ' la formule
Dim FrstCar As Integer      ' Numero du caractère traité
Dim Indice As String		' abreviation de l'indice dans la formule
Dim valindice As Double	    ' 

fml = LireFormule(iFormule)            ' on recupère la formule dans la table

fml = Replace(fml, "_Montant", Montant) ' on remplace le _Montant par sa valeur

Do
    FrstCar = 1
    While Not Mid(fml, FrstCar, 1) = "_"         ' fait défiler les chiffres, signe jusqu'au "_"
        FrstCar = FrstCar + 1
    Wend
    
    FrstCar = FrstCar + 1                        ' on se positionne sur le nom de l'indice
    
    While Mid(fml, FrstCar, 1) Like "[A-Z]"      ' tant que c'est une lettre on traite le nom de l'indice
        Indice = Indice & Mid(fml, FrstCar, 1)   ' on récupère l'indice
        FrstCar = FrstCar + 1
    Wend
    Indice = Indice & Mid(fml, FrstCar, 1)       ' pour la terminaison
                                                        ' 0 = indice ancien -> contrat
                                                        ' 1 = indice actuel -> tbl_indice
   
    If Right(Indice, 1) = 1 Then                 ' chercher l'indice 1 étant le plus ancien
       valindice = DLookup("Indice", "tblIndices", "Mois = " & Month(DateActuelle) & " And Annee=" & Year(DateActuelle))
    Else                          ' Indice contrat
       valindice = DLookup("Indice", "tblIndices", "Mois = " & Month(DateOrigine) & " And Annee=" & Year(DateOrigine))
    End If
    ' remplace dans la formule
    fml = Replace(fml, "_" & Indice, valindice, , 1) ' remplace 1 seule fois l'indice et son caractère de répérage.
    
    Indice = ""

Loop While InStr(1, fml, "_") > 0    'Il n'y a plus d'indice à traduire

fml = Replace(fml, ",", ".")   'remplace chaque virgule par un point
Revalorise = Calcule(fml)      ' retourne le résultat de l'opération.

Exit Function

Err_Revalorise:
If Err = 94 Then
   Revalorise = 0
   Exit Function
ElseIf Err = 6 Then
   fml = Replace(fml, ",", ".")   'remplace chaque virgule par un point
   Revalorise = Calcule(fml)
Else
   MsgBox Err & " -- " & Err.Description
   Revalorise = 0
   Exit Function
End If
End Function

Cas particulier ! L'abréviation de certains indices est composée de chiffres. C'est notamment le cas du BT48 (indice de la construction). Vous devez remplacer les chiffres par autre chose; en effet, la fonction ci-dessus ne reconnait pas les chiffres dans les abréviations.

V-A. Virgule ou point, le séparateur décimal

Le séparateur décimal pose un réel problème pour notre moteur de calcul. En effet, lorsque l'on saisit un nombre décimal dans ACCESS, c'est la virgule qui est utilisée, du moins pour ceux qui ont ce réglage dans la configuration régionale de Windows. Pour VBA, il en va autrement ! Lui n'accepte que le point, mais la virgule est utilisée pour la séparation de tout paramètre.

Ceci peut poser un problème lors de la composition de vos formules; dans ce cas utilisez les fonctions de conversions suivantes :

Fonctions Syntaxe avec le moteur Description
CCur() Calcule("CCur(""10,2"")....") Converti l'expression en format monétaire
CDate() Calcule("cdate(""10/2/2009"")") Converti la date en double
CDbl() Calcule("cdbl(""10,25"")") Converti l'expression au format double



La régle est donc simple; il faut doubler la double-cote dans la chaîne. Comme pour toutes chaînes de caractères.

V-B. Quelques tests

Après cette importante parenthèse, nous allons procéder à des tests de notre formule. Dans la fenêtre d'exécution, essayez les syntaxes suivantes.

 
Sélectionnez

? revalorise(100,"1/6/2007","1/6/2008",4)
105,5
? calcule("year(cdate(""2/8/2008""))-1")
2007
? calcule("replace(""c_est pas bon"",""_"",chr(149))")

Voilà nous pouvons évaluer à peu prés toutes les fonctions, même celles que vous développerez.

Notez que si vous entrez une date avec les séparateurs de date #, celles-ci seront interprétées comme des dates au format anglo-saxon (mois / jour /année), ce qui est très gênant dans notre cas.

VI. Exploitation

il n'y a pas de secret, une fonction reste une fonction et à ce titre elle peut évidement être utilisée dans une requête. Ce qui pour une revalorisation en masse est un sérieux avantage.

Commençons par créer une petite table de "contrat" avec juste l'essentiel.

Table : tblContrat Nom du champ Type du champ - Taille Description
TypeFormule Numérique - Entier Numéro de la formule utilisée pour ce contrat. Correspond au numéro identifiant la formule dans la table tblformule
Montant Monétaire - Euro Le montant du contrat.
DerniereReval Date/Heure La date de la dernière revalorisation.



Remplissez le contrat en utilisant des numéros de formules existants (2) et dont les dates correspondent à celles saisies dans les indices (1/6/2007).

Créez ensuite la requête comme suit.

 
Sélectionnez

SELECT tblContrat.typeFormule, tblContrat.Montant, tblContrat.DernierReval, 
Revalorise([Montant],[DerniereReval],DateAdd("yyyy",1,[DerniereReval]),[TypeFormule])+tblContrat.Montant AS NouveauTarif
FROM tblContrat;

Vous avez noté que nous utilisons la formule Revalorisation() directement dans la requête et que ses paramètres sont les champs de la table.

La seule chose que nous pouvons commenter c'est la fonction native DateAdd() que vous pouvez retrouver dans le tutoriel, Les fonctions Date / Heure de Maxence Hubiche . Elle permet de rajouter 1 an à la date de dernière revalorisation. Pour calculer le nouveau prix, nous ajoutons l'ancien à la revalorisation. Vous n'avez plus qu'à transformer cette requête en requête Modification (Update) pour avoir une revalorisation automatisée.

VII. Conclusion

Il n'y a aucune limite à la création des formules. Vous devez simplement respecter les règles des opérateurs et prioriser vos calculs par des parenthèses. Vous pouvez également utiliser des fonctions mathématiques ou non d'ACCESS, ou créer vos propres fonctions. Au travers de ce tutoriel, nous venons non seulement de repousser plus loin les limites d'ACCESS mais également de nous affranchir dans une moindre mesure de l'intervention d'EXCEL tout en laissant l'utilisateur maître de rédiger ses propres formules de calculs.

VIII. Remerciements

A Jeannot45 alias Jean BALLAT pour ses corrections et critiques toujours constructives ainsi qu'à toutes l'équipe de Developpez.

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

  

Copyright © 2009 Fabrice CONSTANS. Aucune reproduction, même partielle, ne peut être faite de ce site et 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. Droits de diffusion permanents accordés à Developpez LLC.