Référence circulaire en VBA sur Excel

Question

Bonjour, J'ai fait une procédure qui appelle une fonction sous excel afin de calculer des pourcentages. En gros j'ai le résultat d'une enquete en ligne sous les yeux. Dans mon tableau il y a des lignes grises qui symbolisent les questions et en dessous de chaque ligne grise un ensemble de ligne (qui ne sont pas grises) remplies de 1 et de 0 et qui symbolisent le choix des différentes réponses possibles. Le but est de pouvoir faire une colonne a la fin de mon tableau et qui pour chaque ligne de réponse me disent le pourcentage de choix. Sachant que dans la ligne grise représentant une question donnée j'ai une case indiquant le nombre de répondants. voici un bout du code: (Je vous préviens, comme je viens de commencer le vba mon code risque de vous paraitre très sale) Private Sub CalculerPourcentage(LigneCourante, NbReponses, Autre) Dim i, j As Integer Dim NbReponsesPositives, NbRepondants As Long Dim LigneReponse As Range NbRepondants = LigneCourante.Cells(1, 2) For i = 1 To NbReponses + Autre Set LigneReponse = LigneCourante.Offset(i, 0) LigneReponse.Cells(1, IntColMax + 1).Activate LigneReponse.Cells(1, IntColMax + 1).Formula = "=Personal.xls!Pourcentage()" Next End Sub Public Function Pourcentage() As Long Dim NbReponsesPositives As Long, NbRepondants As Long, NbColMax As Integer, NbRowMax As Integer Dim LigneReponse As Range, CaseNbRepondant As Range, Cellule As Range Dim i As Integer, j As Integer Set Cellule = ActiveCell Set LigneReponse = ActiveSheet.UsedRange.Rows(Cellule.Row) NbColMax = ActiveSheet.UsedRange.Columns.Count NbRowMax = ActiveSheet.UsedRange.Rows.Count i = 0 While i < 20 Set CaseNbRepondant = Cellule.Offset(-i, -Cellule.Column + 2) If CaseNbRepondant.Interior.ColorIndex = 15 Then i = 20 NbRepondants = CaseNbRepondant.Value End If i = i + 1 Wend NbReponsesPositives = 0 For j = 3 To NbColMax - 1 If LigneReponse.Cells(1, j) <> 0 And LigneReponse.Cells(1, j) <> "" Then NbReponsesPositives = NbReponsesPositives + 1 End If Next Pourcentage = NbReponsesPositives * 100 / NbRepondants End Function La ligne en rouge est celle qui me pose problème. Quand je la laisse comme ça ma colonne de pourcentage se retrouve avec plein de #Name. Je suis obligé de cliquer sur chacune des cases et de rappuyer entrée pour que ça marche. En mode débug, je me suis rendu compte que ma fonction pourcentage() était appelée plusieurs fois pour une seule ligne et j'ai aussi quelques fois eu le message d'erreur "référence circulaire". Je ne comprend pas d'où ça vient. Help! Merci d'avance. Ps: Quand je remplace la ligne en rouge par LigneReponse.Cells(1, IntColMax + 1) = Pourcentage()" ça fonctionne mais après un changement dans mon tableau mes cellules ne sont pas mises à jour...

cs_lermite222 · Answer

Bonjour,


La référence circulaire provient de formule qui se recalcul en boucle, il est possible que c'est du fait que ta routine n'est pas terminée ?


Essaye en désactivant le calcul automatique et quand la routine est terminée faire un recalcul de la feuille. Si le pb persiste colle les formules qui interviennent dans la feuille, c'est de là que vient le problème.
A+
louis

nico971gwada · Answer

Super!

ça marche. En désactivant le calcul automatique j'ai bien ma formule calculée dans ma case et ce n'est même pas la peine de faire un recalcul de la feuille ça marche tt seul. 
Par contre si une case de ma feuille change, mon pourcentage n'est pas mis à jour (même avec l'option volatile). Et si je fais un recalcul de la feuille, étant donné que dans ma fonction je part de la cellule active, toutes les cases de la colonne pourcentage prennent la même valeur.
Que faire? Est-ce qu'il y a un moyen de partir de la cellule à partir de laquelle on appelle une fonction au lieu de la cellule active?

Merci d'avance.

cs_lermite222 · Answer

Evidemment, je ne vois pas l'entièreté de ton prog, mais comme ce sont des formules, il est évident que les autres cellules vont se recalculer.
Si les résultats peuvent êtres figés, tu peu changer les formules par leurs résultats au moyen des fonctions spéciale de copie.
A+
louis

cs_lermite222 · Answer

Une autre solution, ne pas mettre de formule dans la feuille et tout calculer par VBA, évidemment cela nécessiterrais une refonte complète de l'appli.
Après changement dans une cellule, faire appel à la fonction feuille Cell_change (je ne sais plus les termes exact) et recalculer ce qui est nécessaire en fonction de la ligne et de la colonne.
A+
louis

jmfmarques · Answer

Bonjour  408238  nico971gwada,

Imagine le mécanisme suivant :

une cellule additionnant toutes les valeurs d'une colonne dans laquelle elle se trouve elle-même.
Il est clair que si tu ajoutes ou modifie une des valeurs de la colonne, celà aura pour effet :
1) de modifier ton total
2) comme ce total est lui-même dans la colonne et inclus dans le calcul du total  ===>> de remodifier le total etc... etc...

C'est là un exemple simple de référence circulaire !!!
A toi de chercher sur ta feuille où se trouve une (ou plusieurs) cellule qui fait un calcul de l'espèce (sur une base incluant sa propre valeur) et de corriger...

Référence circulaire en VBA sur Excel

5 réponses

Votre réponse

Discussions similaires