Référence circulaire en VBA sur Excel

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...

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

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.

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

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

Bonjour  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...