Formules mathématiques dynamiques avec Microsoft ACCESS

Fabrice Constans

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 calcul 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.

- Titre : Formules mathématiques dynamiques avec Microsoft ACCESS

- Auteur : Fabrice Constans

- Parution : 16 janvier 2009

- Mise à jour : 16 janvier 2009

- Licence : 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.

Sommaire

- I. Introduction

- II. Un cas bien concret

- II-A. Analyse rapide

- II-B. Le prérequis

- II-C. Les données

- III. De la formule au résultat

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

- III-B. Alimentez le moteur de calcul

- III-C. Quelques mots sur la fonction EVAL

- III-D. Concrétisez le moteur de calcul

- IV. Calcul des formules

- V. Fonction d'interprétation des formules

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

- V-B. Quelques tests

- VI. Exploitation

- VII. Conclusion

- VIII. Remerciements

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. Dynamiques 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 de l’INSEE. On utilise le ratio d'un indice constaté entre la date actuelle et la même date un an plus tôt. 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 de la colonne

Type de colonne - 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 une colonne supplémentaire pour stocker une description de la formule.

Table : tblIndices - Nom de la colonne

Type de colonne- 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 au 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ée 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 pour 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.

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). Écrivez.

Calcule = Sqr(4)

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

? calcule("")

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

Validez. Bravo ! Vous avez réussi à faire une fonction qui renvoie la racine carrée 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

Calcule = sqr(4)

par la ligne suivante :

Calcule = Eval("sqr(4)")

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

? 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 :

Eval("msgbox ""bonjour""")

Ce qui fonctionne :

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

Calcule = Eval("Sqr(4)")

par celle-ci :

Calcule = Eval(cFormule)

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

? 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 !

? 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().

Public Function LireFormules(idFormule As Integer) as string
' idFormule est le n° 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 &amp; " = " &amp; 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 :

? 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 :

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 N° 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      ' Numéro du caractère traité
Dim Indice As String        ' abréviation de l'indice dans la formule
Dim valindice As Double        '

fml = LireFormule(iFormule)            ' on récupè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 &amp; Mid(fml, FrstCar, 1)   ' on récupère l'indice
FrstCar = FrstCar + 1
Wend
Indice = Indice &amp; 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 = " &amp; Month(DateActuelle) &amp; " And Annee=" &amp; Year(DateActuelle))
Else                          ' Indice contrat
valindice = DLookup("Indice", "tblIndices", "Mois = " &amp; Month(DateOrigine) &amp; " And Annee=" &amp; Year(DateOrigine))
End If
' remplace dans la formule
fml = Replace(fml, "_" &amp; Indice, valindice, , 1) ' remplace 1 seule fois l'indice et son caractère de repé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 &amp; " -- " &amp; 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 reconnaît 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, lorsqu'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"")....")

Convertit l'expression en format monétaire.

CDate()

Calcule("cdate(""10/2/2009"")")

Convertit la date en double.

CDbl()

Calcule("cdbl(""10,25"")")

Convertit l'expression au format double.

La règle est donc simple ; il faut doubler la double-quote 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 :

? 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 des dates 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 évidemment ê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 « contrats » avec juste l'essentiel.

Table : tblContrat Nom de la colonne

Type de colonne - 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.

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 colonnes 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 un 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

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