Problème de référence circulaire dans une fonction

Pierre1981

XLDnaute Nouveau
Bonjour,

je travaille actuellement sur ce fichier que j'ai récupéré de la personne en place avant moi.
(je ne peux malheureusement pas le poster car trop volumineux, mais si quelqu'un peut m'aider qu'il me MP)

il s'agit d'un fichier avec une 60aine d'onglets qui va finir par être mis en ligne sur l'intranet de l'entreprise afin d'être nourri par les différents établissements qui la compose.

Le problème (on y vient) est que la personne avant moi avait tout fait en formule (et certaine sont assez monstrueuses) et la partie concernant le code VBA est un peu b*****ique.

j'ai donc repris certaines cellules et il me semble que les onglets concernant les établissements fonctionnent maintenant de la bonne façon.
Cependant quand j'en arrive au dernier onglet qui sert à consolider je suis obligé de créer une fonction puisque les seuls formules excels ne suffisent pas.

voici les deux fonctions que j'ai créées et qui posent problème.


'donne le nombre de participants total à toutes les formations d'une catégorie sur l'ensemble de l'association
Function nbparticipassocsp(colonne As String, ligne As Integer) As Variant
Dim h As Integer
Dim i As Integer
Dim rang As Integer
Dim resultat As Variant
Dim nbpers As Integer
Dim feuillenom As String
Dim cell As String
resultat = 0
rang = 17
For h = 1 To 63
Sheets(h).Select
feuillenom = Right(Sheets(h).Name, 3)
If feuillenom Like ("(1)") Then
If Not IsEmpty(Sheets(h).Range("A15")) Then
For i = 1 To 177
cell = colonne & rang
If Not IsEmpty(Sheets(h).Range(cell)) Then
If Sheets(h).Range(cell).HasFormula = False Then
resultat = resultat + Sheets(h).Range("N" & rang).Value * Sheets(h).Range(cell).Value
nbpers = nbpers + Sheets(h).Range("N" & rang).Value
End If
End If
rang = rang + 1
Next i
rang = 17
End If
End If
Next h
nbparticipassocsp = resultat / nbpers
End Function


'permet de trouver le nombre de participant aux formations collectives
Function nbparticipassocspcoll(colonne As String, ligne As Integer) As Variant
Dim h As Integer
Dim i As Integer
Dim rang2 As Integer
Dim resultat As Variant
Dim nbpers As Integer
Dim feuillenom As String
Dim cell As String
Dim test As String
resultat = 0
For h = 1 To 63
Sheets(h).Select
feuillenom = Right(Sheets(h).Name, 3)
If feuillenom Like ("(1)") Then
For i = ligne To 177 Step 16
cell = colonne & i
test = Sheets(h).Range("A" & i).Value
If test Like ("Intitulé des actions collectives (1 ligne par groupe)") Then
rang2 = i
For j = 1 To 10
If Not IsEmpty(Sheets(h).Range(cell)) Then
If Sheets(h).Range(cell).HasFormula = False Then
resultat = resultat + Sheets(h).Range("N" & rang2).Value * Sheets(h).Range(cell).Value
nbpers = nbpers + Sheets(h).Range("N" & rang2).Value
End If
End If
rang2 = rang2 + 1
cell = (colonne & rang2)
Next j
i = rang2
rang2 = 0
End If
Next i
End If
Next h
nbparticipassocspcoll = resultat / nbpers
End Function

Le code est bon je pense (enfin la méthode de calcul implémenté en tout cas) car lorsque j'arrive à les faire fonctionner le résultat est ok. Le problème est que j'ai sytématiquement une référence circulaire à l'ouverture du fichier et cela empêche donc le calcul de s'effectuer (il ne se fait qu'en mode debugage).
Quelqu'un pourrait-il me dire si quelque chose dans mon code génère cette erreur ?

Merci d'avance. :D
 
C

Compte Supprimé 979

Guest
Re : Problème de référence circulaire dans une fonction

Bonjour Pierre1981,

Pour moi ton problème de référence circulaire ne vient pas de tes fonctions VBA :confused:
Puisqu'une référence circulaire est une cellule qui est dans son propre résultat ...

En ce qui concerne le dépôt de fichier, tu peux le faire via : dl.free.fr
(1Go pour un fichier en mode Web, ça devrait suffire :rolleyes:;))
Ensuite tu nous donnes juste le lien pour le récupérer

A+
 

Pierre1981

XLDnaute Nouveau
Re : Problème de référence circulaire dans une fonction

Bonjour,

Merci pour la réponse. voici donc le lien pour récupérer le fichier.
Free - Envoyez vos documents

Je rajoute quelques précisions.
Tout d'abord le fichier est très verrouillé (j'ai mis des protections partout car il est impératif que la trame initiale ne bouge pas. Et dans les associations vous seriez étonnés de voir à quel point on ne respecte pas les consignes).
Le mot de passe pour toutes les protections (feuille et code VBA) est mnsecret
En ce qui concerne le fichier en lui même. Chaque feuille est accessible avec un mot de passe uniquement (chaque établissement n'a pas à connaitre ce qu'ont rempli les autres).
Je ne donne que le mot de passe du Siège qui est "Chef" (la casse est importante).

Voila une fois dans l'onglet siège ce sont les données des feuilles concernant l'association complète qui posent problème.
Parfois l'erreur circulaire apparaît, parfois non.

Je suis perdu (d'autant que le code n'est vraiment pas propre, celui antérieur et peut être le mien aussi ^^)

Merci d'avance à tout ceux qui pourront m'aider à résoudre ce problème.

Pierre
 
C

Compte Supprimé 979

Guest
Re : Problème de référence circulaire dans une fonction

Bonjour Pierre1981,

Sais-tu dans quelle feuille tu as cette fameuse référence circulaire !?
A l'ouverture je n'ai aucun problème chez moi ...

A+
 

Pierre1981

XLDnaute Nouveau
Re : Problème de référence circulaire dans une fonction

oui elle apparait dans la feuille association (1) (je n'ai pas fait la 2 vu que la formule fait ces fameuses références)
j'ai testé tout l'après midi et je ne comprends pas le pourquoi de la chose.

A noter que vu que les références se font sur des formules que j'ai créées. Elles n'apparaissent du coup que lorsque je relance les calculs.
 
C

Compte Supprimé 979

Guest
Re : Problème de référence circulaire dans une fonction

Bonsoir Pierre1981,

Après tests, pour moi une seule fonction pose problème : Function nbparticipassocspcoll()

Ce qui ce passe apparemment, c'est que tu boucles jusqu'à la ligne 177,
or la cellule appelant la fonction se trouve ligne 44 donc "référence circulaire"

Je pense avoir trouver une solution pour empêcher le problème de se créer ;)
VB:
'permet de trouver le nombre de participant aux formations collectives et de calculer le temps moyen de formation pour 1 stagiaire
Function nbparticipassocspcoll(colonne As String, ligne As Integer) As Variant
  Dim h As Integer
  Dim i As Integer
  Dim resultat As Variant
  Dim nbpers As Integer
  Dim feuillenom As String
  Dim cell As String
  Dim LigFunc As Long
  ' Récupérer le numéro de ligne de la cellule appelant la fonction
  LigFunc = Application.Caller.Row
  '
  resultat = 0
  For h = 1 To 63
    Sheets(h).Select
    feuillenom = Right(Sheets(h).Name, 3)
    If feuillenom Like ("(1)") Then
      For i = ligne To 177
        ' Si la boucle arrive sur la ligne, on sort ICI
        If i = LigFunc Then Exit For
        ' ****************************
        cell = colonne & i
        If Not IsEmpty(Sheets(h).Range(cell)) Then
          If Sheets(h).Range(cell).HasFormula = False Then
            resultat = resultat + Sheets(h).Range("N" & i).Value * Sheets(h).Range(cell).Value
            nbpers = nbpers + Sheets(h).Range("N" & i).Value
          End If
        End If
      Next i
    End If
  Next h
  If nbpers = 0 Then
    nbparicipassocspcoll = 0
  Else
    nbparticipassocspcoll = resultat / nbpers
  End If
End Function

A+
 
Dernière modification par un modérateur:

Pierre1981

XLDnaute Nouveau
Re : Problème de référence circulaire dans une fonction

Bonjour,

Merci pour ta réponse.
J'ai testé avec tes modifications (effectivement il semblerait qu'il n'y ait que la dernière formule qui pose problème)
mais la référence circulaire existe toujours.

Je pense que ça doit venir du fait que cette fonction scan des lignes utilisées dans d'autres fonction à l'intérieur des tableaux de chaque établissement. Car si ton raisonnement est juste sur le fait que la ligne 44 est balayée par mon tableau, elle ne l'est que sur les feuilles des établissements, ma première boucle est censé empêcher le passage sur la feuille association. La référence circulaire ne vient donc pas de là.

J'avoue mon découragement face à ce problème. J'ai testé tellement de solution que je n'arrive plus vraiment à savoir quoi faire.

Merci encore pour ton aide.

Pierre
 
C

Compte Supprimé 979

Guest
Re : Problème de référence circulaire dans une fonction

Re,

Navré, mais chez moi ou au bureau, je n'ai plus de référence circulaire !?
Tu l'as toujours sur ta feuille Association (1) ?
As-tu bien remplacé ton code par celui que je t'ai donné ?

A+
 

Pierre1981

XLDnaute Nouveau
Re : Problème de référence circulaire dans une fonction

J'ai adapté ta solution en fait. Le but n'est pas qu'après le test on sorte du for. Je veux que le balayage des différentes feuilles soit complet sinon cela fausse mes calculs.
A la place de "Exit For" j'ai donc mis un i = i +1. Ce qui logiquement doit faire passer à la ligne suivante (je préfère perdre une ligne que le reste de mon tableau dans le calcul ^^) et donc éviter la référence circulaire.

Me trompe-je ?
 
C

Compte Supprimé 979

Guest
Re : Problème de référence circulaire dans une fonction

Re,

J'ai adapté ta solution en fait. Le but n'est pas qu'après le test on sorte du for. Je veux que le balayage des différentes feuilles soit complet sinon cela fausse mes calculs.
Effectivement, si tu t'en sers pour d'autres feuilles

Pierre1981 à dit:
A la place de "Exit For" j'ai donc mis un i = i +1. Ce qui logiquement doit faire passer à la ligne suivante (je préfère perdre une ligne que le reste de mon tableau dans le calcul ^^) et donc éviter la référence circulaire.
Me trompe-je ?
Mieux vaut mettre un test; sinon tu auras 2 fois le même calcul

VB:
'permet de trouver le nombre de participant aux formations collectives et de calculer le temps moyen de formation pour 1 stagiaire
Function nbparticipassocspcoll(colonne As String, ligne As Integer) As Variant
  Dim h As Integer
  Dim i As Integer
  Dim resultat As Variant
  Dim nbpers As Integer
  Dim feuillenom As String
  Dim cell As String
  Dim LigFunc As Long
  ' Récupérer le numéro de ligne de la cellule appelant la fonction
  LigFunc = Application.Caller.Row
  '
  resultat = 0
  For h = 1 To 63
    Sheets(h).Select
    feuillenom = Right(Sheets(h).Name, 3)
    If feuillenom Like ("(1)") Then
      For i = ligne To 177
        ' Si la ligne est différente de la ligne de la cellule appelante
        If i <> LigFunc Then
          ' On effectue le calcul
          cell = colonne & i
          If Not IsEmpty(Sheets(h).Range(cell)) Then
            If Sheets(h).Range(cell).HasFormula = False Then
              resultat = resultat + Sheets(h).Range("N" & i).Value * Sheets(h).Range(cell).Value
              nbpers = nbpers + Sheets(h).Range("N" & i).Value
            End If
          End If
        End If
      Next i
    End If
  Next h
  If nbpers = 0 Then
    nbparicipassocspcoll = 0
  Else
    nbparticipassocspcoll = resultat / nbpers
  End If
End Function

A+
 
Dernière modification par un modérateur:

Pierre1981

XLDnaute Nouveau
Re : Problème de référence circulaire dans une fonction

Merci encore, mais rien n'y fait. Cette référence circulaire apparaît toujours.
Je pense que je vais supprimer cette dernière ligne et que ce calcul se fera de façon manuelle. Ca sera plus long mais peut être plus simple :)

Merci encore de ton aide en tout cas.
 

Discussions similaires

Réponses
6
Affichages
202

Statistiques des forums

Discussions
311 720
Messages
2 081 904
Membres
101 834
dernier inscrit
Jeremy06510