Comment évaluer et extraire des formules Excel avec des fonctions personnalisées ?

Dans ce tutoriel, je vais vous montrer comment créer deux fonctions VBA qui vont transformer votre façon de travailler avec les formules Excel.

La première fonction de ces fonctions va en effet nous permettre de convertir automatiquement du texte en formule fonctionnelle.

Et la seconde nous servira à extraire le contenu exact de n'importe quelle formule présente dans une cellule.

Ces deux outils vont vous faire gagner un temps précieux, particulièrement si vous manipulez régulièrement des données importées depuis des bases de données ou des fichiers CSV où les formules arrivent souvent sous forme de texte brut.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel nous retrouvons des données de vendeurs d'une boutique en ligne.

Ce tableau contient plusieurs colonnes, dont une qui va nous poser problème : la colonne « Formule ».

En effet, dans cette colonne, nous avons des formules qui sont stockées sous forme de texte simple, et non pas comme des formules Excel fonctionnelles.

Cela signifie qu'Excel ne les calcule pas automatiquement. Nous remarquons que certaines formules comportent même des espaces avant ou après le texte, ce qui complique encore la situation.

2. Comprendre le problème des formules en texte

Avant de créer nos fonctions, nous devons bien comprendre pourquoi Excel refuse parfois de reconnaître une formule.

• Le premier cas de figure, et le plus fréquent concernent l'absence du signe égal au début de la formule. Lorsque nous tapons une formule sans commencer par le symbole égal, Excel interprète cette saisie comme du texte ordinaire et ne tente pas de l'évaluer. C'est exactement ce qui se passe dans notre colonne D où toutes les formules sont dépourvues de ce fameux signe égal initial.
• Le deuxième problème provient des espaces parasites. Nous pouvons le constater avec la formule de Lucas Dubois en ligne 8, où nous voyons des espaces avant et après le texte « B8*C8 ». Même si nous ajoutons manuellement le signe égal devant cette formule, les espaces empêcheront Excel de la calculer correctement. Ces espaces invisibles sont particulièrement sournois, car ils ne se voient pas toujours à l'écran.
• Le troisième cas problématique survient lors de l'importation de données. Lorsque nous ouvrons un fichier CSV ou que nous importons des données depuis une base de données externe, les formules sont systématiquement converties en texte brut. Excel ne peut pas deviner que ces chaînes de caractères sont censées être des formules fonctionnelles et nous nous retrouvons alors avec des colonnes entières de formules inutilisables.
• Enfin, il existe un piège plus subtil : l'apostrophe invisible. Parfois, un caractère apostrophe se place automatiquement au début d'une cellule, forçant Excel à traiter le contenu comme du texte. Cette apostrophe n'apparaît pas dans la cellule, mais uniquement dans la barre de formule. C'est une source fréquente de confusion pour les utilisateurs qui ne comprennent pas pourquoi leur formule refuse de fonctionner.

3. Créer la fonction EVALUER_FORMULE

   3.1 Accéder à l'éditeur VBA et préparer l'environnement

Pour créer notre première fonction personnalisée, nous devons accéder à l'environnement de développement VBA d'Excel. Il s’agit d’un langage de programmation intégré dans les applications de la suite Office, et qui permet d’en décupler les fonctionnalités.

Dans le cadre de ce tutoriel, nous aborderons rapidement le fonctionnement de VBA, mais si vous souhaitez en savoir plus, vous pouvez découvrir mon livre « Apprendre le VBA pour les grands débutants ».

Pour créer du code VBA, il faut lancer « Visual Basic », et pour cela, la méthode la plus rapide consiste à utiliser le raccourci clavier [Alt]+[F11].

L'éditeur s'affiche dans une nouvelle fenêtre avec plusieurs panneaux.

Sur la gauche, nous voyons l'explorateur de projet qui liste tous les classeurs ouverts et leurs composants.

Pour créer notre fonction, nous avons besoin d'insérer un nouveau module.

Dans le menu « Insertion », nous cliquons alors sur « Module ».

Un nouveau module appelé « Module1 » apparaît dans l'arborescence, et une fenêtre de code vierge s'ouvre sur la droite.

C'est dans cette fenêtre que nous allons écrire le code de notre fonction.

   3.2 Écrire et comprendre le code de EVALUER_FORMULE

Nous allons maintenant saisir le code de notre fonction.

Nous cliquons dans la fenêtre de code pour la rendre active, puis nous copions exactement le code suivant :

Pour commencer, nous allons déclarer notre fonction avec le mot-clé « Function » suivi du nom que nous avons choisi :

Function EVALUER_FORMULE()

Lorsque nous validons en appuyant sur la touche [Entrée], VBA nous ajoute les parenthèses qui vont nous permettre d’ajouter un argument.

Nous insérons le paramètre entre parenthèses, « formule_texte As String », qui indique que notre fonction attend de recevoir du texte en entrée.

Function EVALUER_FORMULE(formule_texte As String)

Ensuite, nous appelons la méthode « Application.Volatile », qui va forcer Excel à recalculer notre fonction à chaque fois que n'importe quelle cellule de la feuille change.

    Application.Volatile

Sans cette ligne, Excel ne recalculerait notre fonction que lorsque les cellules référencées directement changent.

C'est un comportement que nous voulons pour nous assurer que nos résultats restent toujours à jour.

Puis, nous allons nous assurer que toutes les espaces superflues au début et à la fin de la chaîne de caractères soient supprimées en utilisant la fonction « Trim » :

    formule_texte = Trim(formule_texte)

C'est notre première ligne de défense contre les espaces parasites que nous avons évoqués précédemment.

Maintenant, nous allons mettre en place un bloc « If » pour vérifier si le premier caractère de notre texte est un signe égal.

    If Left(formule_texte, 1) <>  "=" Then
        formule_texte =  "=" & formule_texte
    End If

La fonction « Left » extrait le premier caractère à gauche. Si ce caractère n'est pas un égal, nous utilisons l'opérateur « & » pour concaténer un signe égal devant notre texte.

La ligne suivante contient la magie de notre fonction : « Application.Evaluate ».

    EVALUER_FORMULE = Application.Evaluate(formule_texte) 

Cette méthode d'Excel prend une chaîne de caractères représentant une formule et l'évalue comme si nous l'avions tapée directement dans une cellule au sein d’une feuille Excel.

C'est exactement ce dont nous avons besoin pour transformer notre texte en formule fonctionnelle.

Le résultat de cette évaluation est stocké dans notre fonction via « EVALUER_FORMULE = ».

Maintenant que notre fonction est créée, nous retournons dans Excel pour retrouver notre tableau de données.

Nous y ajoutons un en-tête pour notre nouvelle colonne, par exemple « Commission Calculée ».

Puis, nous cliquons ensuite sur la première cellule de la colonne afin d’appeler notre nouvelle fonction.

Nous tapons le signe égal pour commencer notre formule, puis nous écrivons le nom de notre fonction : EVALUER_FORMULE.

Nous ouvrons une parenthèse et nous cliquons sur la cellule B7 qui contient notre première formule en texte, ensuite, nous fermons la parenthèse et nous appuyons sur [Entrée].

Si tout s'est bien passé, nous voyons apparaître le résultat : 2000.

C'est le produit de 25000 (le CA de Sophie) multiplié par 0,08 (son taux de commission).

Pour appliquer notre fonction aux autres lignes, nous double-cliquons simplement sur le petit carré situé en bas à droite de la cellule B7, afin qu’Excel copie automatiquement notre formule et l'adapte à chaque ligne.

4. Créer la fonction TEXTE_FORMULE

   4.1 Comprendre l'utilité de l'extraction de formules

Parfois, nous avons besoin de voir le contenu exact d'une formule sans pour autant voir son résultat.

Cette situation se présente par exemple lorsque nous documentons un classeur Excel pour nos collègues et que nous souhaitons extraire et afficher une formule directement dans une cellule adjacente.

Cela sera particulièrement utile lors de l'audit de classeurs complexes contenant des centaines de formules et que nous devions vérifier leur cohérence.

Plutôt que de cliquer sur chaque cellule individuellement et de noter manuellement les formules, notre fonction TEXTE_FORMULE nous permettra de créer automatiquement un inventaire complet de toutes les formules utilisées.

Cette fonction pourra également être utile dans un contexte pédagogique en permettant de montrer côte à côte le résultat d'une formule et la formule elle-même.

   4.2 Écrire le code de TEXTE_FORMULE

Nous retournons dans l'éditeur VBA en appuyant sur [Alt]+[F11].

Nous retrouvons notre Module1 avec la fonction EVALUER_FORMULE que nous avons créée précédemment.

Cette fonction sera encore plus courte et plus simple que la précédente, mais elle n'en est pas moins puissante.

Nous déclarons donc une nouvelle fonction que nous appelons TEXTE_FORMULE et dans laquelle nous insérons le paramètre s'appelle « plage » que nous typons en tant que « Range », ce qui signifie qu'il s'agit d'une référence de cellule ou d'un groupe de cellules.

Function TEXTE_FORMULE(plage As Range)
End Function

Comme pour la première fonction, nous utilisons « Application.Volatile » pour forcer le recalcul automatique.

    Application.Volatile

Ensuite, nous utilisons la propriété « HasFormula » de notre plage.

    If plage.HasFormula Then

Cette propriété est un booléen, c’est-à-dire une variable qui peut prendre comme valeur « True » ou « False », « Vrai » ou « Faux ».

Sa valeur sera égale à « True » si la cellule contient une formule et « False » si elle contient uniquement une valeur ou du texte.

C'est une vérification simple, mais efficace.

Si la cellule contient effectivement une formule, nous utilisons la propriété « Formula » pour récupérer son contenu complet, y compris le signe égal initial.

    If plage.HasFormula Then
        TEXTE_FORMULE = plage.Formula

Cette propriété retourne la formule exactement telle qu'elle apparaît dans la barre de formule d'Excel.

À l’inverse, si la cellule ne contient pas de formule, nous retournons simplement le texte « Pas de formule » pour informer l'utilisateur.

    Else
        TEXTE_FORMULE = "Pas de formule"
    End If

   4.3 Tester notre fonction d'extraction

Nous retournons dans Excel et nous ajoutons encore une nouvelle colonne, que nous nommons « Formule en claire » et qui va nous permettre de retrouver les formules utilisées dans la colonne précédente.

Pour terminer notre test, nous allons voir ce qui se passe quand une cellule ne contient pas de formule.

Ainsi, dans une cellule en dehors du tableau, nous tapons simplement « 12345 » comme valeur fixe, sans formule.

Et dans la cellule située juste à côté, nous utilisons la fonction « TEXTE_FORMULE() ».

Cette fois, le résultat affiché est « Pas de formule », exactement le message que nous avons défini dans notre code pour les cellules qui ne contiennent pas de formule.

   4.4 Comparer avec la fonction native FORMULETEXTE

Il faut savoir qu’Excel dispose depuis la version 2013 d'une fonction native appelée FORMULETEXTE qui remplit un rôle similaire.

Nous pouvons comparer les deux pour comprendre pour nous rendre compte que le résultat est identique à celui de notre fonction.

En effet, les deux fonctions extraient correctement la formule.

Cependant, il existe des différences importantes.

• Notre fonction TEXTE_FORMULE fonctionne sur toutes les versions d'Excel, y compris Excel 2010 et les versions antérieures, alors que FORMULETEXTE n'est disponible que depuis 2013. Si nous devons partager notre fichier avec des utilisateurs utilisant d'anciennes versions d'Excel, notre fonction personnalisée garantit la compatibilité.
• De plus, notre fonction est entièrement personnalisable. Si nous voulons modifier le message « Pas de formule » pour afficher quelque chose de différent, nous pouvons facilement le faire en modifiant le code VBA. Avec FORMULETEXTE, nous sommes limités au message d'erreur standard d'Excel. Nous pourrions également enrichir notre fonction pour, par exemple, extraire uniquement les références de cellules utilisées dans une formule, ou compter le nombre de fonctions imbriquées. Ces possibilités d'évolution rendent notre solution VBA beaucoup plus flexible.

Pour terminer, si nous devons utiliser régulièrement ces fonctions personnelles, plutôt que de les recréer dans chaque nouveau classeur Excel, nous pouvons les sauvegarder une fois pour toutes dans un fichier spécial appelé classeur de macros personnelles, lequel se charge automatiquement au démarrage d'Excel et rend nos fonctions disponibles dans tous nos fichiers.

Cette approche présente un avantage considérable : nous n'avons plus besoin d'activer les macros dans chaque classeur pour utiliser nos fonctions.

En effet, le classeur de macros personnelles est un fichier de confiance sur notre ordinateur, et Excel les accepte automatiquement.

Cependant, si nous partageons un fichier utilisant ces fonctions avec un collègue, ce dernier devra également disposer des fonctions dans son propre classeur de macros personnelles, ou nous devrons inclure le code VBA directement dans le classeur partagé.