Comment automatiser la mise à jour des formules Excel ?

Dans ce tutoriel, je vais vous montrer comment il est possible d’automatiser la mise à jour des résultats retournés par des formules Excel.

Nous verrons ainsi comment mettre en place un décompte automatique du temps restant avant une échéance donnée.

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. Mise à jour automatique du décompte

Comme nous venons de le voir dans l’introduction de ce tutoriel, je vais vous montrer comment automatiser la mise à jour des formules d’une feuille de calcul de manière régulière en suivant un intervalle donné.

Pour illustrer ce tutoriel, nous allons repartir du fichier de travail créé précédemment dans le cours sur la création d’un décompte du temps séparant deux dates.

Nous avions alors vu que si nous voulons modifier automatiquement la valeur de ce décompte afin de tenir compte du temps qui passe, nous allons devoir mettre en place une macrocommandes en VBA.

Ici, cette macro sera très simple à mettre en place, mais si vous souhaitez aller plus loin dans la découverte de ce langage, qui permet de décupler les possibilités offertes par Excel et les autres applications de la suite Office, vous trouverez mon livre consacré à l’apprentissage de VBA en cliquant ici.

Aucune notion préalable de développement n’est nécessaire pour le suivre.

Pour commencer, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.

Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :

VBE s’ouvre alors et nous allons commencer par créer un nouveau module, qui est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.

Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :

Une fois le module inséré, nous allons pouvoir créer la macro-commande en saisissant le mot-clé Sub, suivi du nom que nous souhaitons lui donner à la fonction, à savoir decompteAutomatique :

Excel ajoute alors automatiquement la ligne End Sub, et tout ce que nous allons saisir entre ces deux lignes sera exécuté automatiquement dès que nous appellerons la macro-commande.

Pour actualiser le résultat retourné par la fonction Excel AUJOURDHUI(), il suffit d’utiliser la commande Application.Calculate :

Sub decompteAutomatique()
    Application.Calculate
End Sub

2. Répéter l’actualisation de manière automatique dès l’ouverture du classeur

Maintenant, chaque fois que nous lancerons la macro, nous pourrons constater que les valeurs du décompte vont s’actualiser automatiquement.

Mais évidemment, ici, nous ne sommes pas beaucoup plus avancés, nous pourrions même dire que lancer une macro et encore plus chronophage que d’appuyer sur la touche [F9].

Le but du jeu sera ici de relancer automatiquement la macro toutes les secondes.

Pour cela, nous allons pouvoir utiliser l’instruction Application.OnTime, laquelle permet de planifier une nouvelle exécution de la macro à une heure souhaitée, ici dans une seconde.

    Application.OnTime Now +  TimeSerial(0, 0, 1), "decompteAutomatique"

Cela nous permet de relancer la macro decompteAutomatique, au bout d’une seconde, et cela indéfiniment.

Ensuite, pour être certains que cette macro se lance automatiquement au démarrage de la feuille du calcul, nous allons pouvoir utiliser un évènement.

Comme nous l’avions vu dans le tutoriel dédié, un évènement permet de lancer une macro lorsqu’une action va se produire au niveau de la feuille de calcul, ou du classeur.

Pour créer cet évènement qui va se lancer à l’ouverture, nous allons double-cliquer sur la feuille ThisWorkbook, au niveau du navigateur de projet :

Ensuite, nous allons aller chercher la bibliothèque d’évènements Workbook en sélectionnant la sélectionnant depuis le premier menu déroulant que nous retrouvons au-dessus de la feuille de code :

Ici, Excel ajoute automatiquement l’évènement Workbook_Open :

Mais si nous souhaitons utiliser un autre évènement, il suffirait de le choisir dans le second menu déroulant, comment nous le verrons un peu plus tard.

Nous pouvons maintenant enregistrer le classeur, puis le relancer pour constater qu’effectivement le décompte va bien s’actualiser automatiquement.

Au passage, étant donné que le classeur contient maintenant des macros, il est important de bien l’enregistrer sous un format de fichier qui permet de les préserver (le format *.xlsm).

3. Arrêter le décompte à la fermeture du classeur

Le problème de l’instruction que nous venons de mettre en place, c’est que si nous fermons le classeur et qu’Excel reste ouvert (car d’autres classeurs sont encore ouverts), alors celui-ci va se rouvrir automatiquement au bout d’une seconde.

Pour éviter cela, nous allons mettre en place une seconde macro qui va permettre d’annuler la planification lorsque nous fermons le classeur.

Pour cela, nous allons enregistrer l’heure du prochain lancement dans une variable que nous pourrons manipuler depuis n’importe quel endroit du projet VBA.

Celle-ci doit donc être déclarée tout en haut de la feuille, en dehors de la macro :

Puis, nous allons y stocker l’heure du prochain lancement :

prochainLancement = Now + TimeSerial(0, 0, 1)

De cette manière, c’est la valeur de cette variable que nous allons utiliser sur la ligne Application.OnTime :

    prochainLancement = Now + TimeSerial(0, 0, 1)
    Application.OnTime prochainLancement,  "decompteAutomatique"

Cela va maintenant nous permettre de créer une nouvelle macro qui va pouvoir annuler le prochain lancement :

Sub arreterDecompte()
    Application.OnTime prochainLancement,  "decompteAutomatique", , False
End Sub

Il ne reste plus qu’à appeler cette macro depuis l’évènement Workbook_BeforeClose lequel se lancera automatiquement à la fermeture du classeur :

• Nous sélectionnons l’évènement BeforeClose depuis le second menu déroulant de la feuille ThisWorkbook :

• Une fois celui-ci inséré, nous appelons la nouvelle macro :

Il ne reste plus qu’à tester pour constater le bon fonctionnement des macros et de l’actualisation des cellules.