Comment automatiser la mise à jour des formules Excel ?

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

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

Excel formation - actualisation formules - 01

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 :

Excel formation - actualisation formules - 02

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 formation - actualisation formules - 03

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 :

Excel formation - actualisation formules - 04

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 :

Excel formation - actualisation formules - 05

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

Excel formation - actualisation formules - 06

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 :

Excel formation - actualisation formules - 07

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 :

Excel formation - actualisation formules - 08

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

Excel formation - actualisation formules - 09

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

 

 



Articles qui pourraient vous intéresser

Comment protéger efficacement un classeur Excel en utilisant une clé USB comme protection physique ?
Comment lire un fichier externe (txt, csv,…) avec une fonction Excel VBA ?
Comment créer un arbre généalogique en quelques minutes sur Excel ?
Comment lire un fichier externe depuis un classeur sans VBA Excel ?
Comment consolider plusieurs classeurs Excel dans un classeur unique ?
Comment automatiser la mise à jour des formules Excel ?
Comment vérifier qu’une adresse mail est valide sur Excel ?
Comment protéger efficacement le contenu de vos fichiers Excel ?
Comment afficher une image différente en fonction de la valeur d’une cellule Excel (sans VBA) ?
Comment faire clignoter une cellule Excel ? La mise en forme dynamique
COMMENT EFFACER ET PERSONNALISER LA LISTE DES DOCUMENTS RÉCENTS D’EXCEL (la vraie méthode)
Comment compter le nombre de cellules contenant un nombre de caractères sur Excel sans VBA ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2022 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.