Utiliser des références circulaires à notre avantage - Astuces Excel

Michelle demande,

Je pense que je suis sur la bonne voie avec une référence circulaire. Voici mon problème. J'ai deux cellules, A1=5et B1=5. Ce que je veux faire, c'est que B1 mémorise 5, puis je veux ajouter une nouvelle valeur à A1, disons que A1 est maintenant égal à 10. Puis-je avoir une formule B1 qui mémorise 5, mais ajoute également 10? Alors maintenant B1=15?

Habituellement, les références circulaires sont une mauvaise chose, mais parfois elles peuvent être utilisées à notre avantage. Voici la manière non macro de faire ce que vous voulez faire. Cela ne fonctionnera que dans certaines situations.

• Dans le menu Excel, sélectionnez Outils> Options.
• Accédez à l'onglet Calcul. Cochez la case Itérations. Remplacez le nombre maximal d'itérations par 1.
• Cliquez sur OK pour fermer la boîte de dialogue des options.
• Entrez 5 dans la cellule A1.
• Entrez 0 dans la cellule B1
• Entrez =A1+B1dans la cellule B1
• Maintenant, lorsque vous entrez de nouvelles valeurs dans A1, l'entrée dans B1 se souviendra de l'ancien total et ajoutera la valeur de A1.

Voici la limitation ÉNORME. Vous ne pouvez entrer aucune valeur ailleurs sur la feuille! Chaque fois que vous entrez une valeur ou que la feuille est recalculée, la valeur de A1 sera ajoutée à la valeur de B1. Ainsi, en appuyant plusieurs fois sur F9, vous verrez B1 augmenter de 5 pour chaque F9.

La manière la plus sûre de procéder consiste à utiliser une petite macro de gestionnaire d'événements. Vous devrez ajouter ce code au volet de code pour Sheet1 (en supposant que vous travaillez sur Sheet1). Le code du gestionnaire d'événements serait le suivant:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = “$A$1” Then Application.EnableEvents = False Range(“B1”).Value = Range(“B1”).Value + Target.Value Application.EnableEvents = True End If End Sub

Ce bit de code est exécuté à chaque fois qu'une cellule est modifiée sur la feuille. La cible est une variable d'objet spéciale qui indique quelle cellule a été modifiée. Le gestionnaire d'événements vérifie quelle cellule vient d'être modifiée. Si la cellule était A1, elle ajoutera la valeur de A1 à B1. Nous devons désactiver les gestionnaires d'événements lors de la modification de B1 afin que le gestionnaire d'événements ne se rappelle pas.