[E-03] Problème de référence circulaire

Version imprimable

Bonjour à tous,
Je débute en VBA et j'ai du mal ! :?
Voilà mon problème :
- Cellule B1 à Bn j'ai une formule du type "=F_Calcul(C1)" qui me renvoie une chaine formatée selon la valeur de la cellule C1 (Cn)
- Les Cellules C1 à Cn contiennent une valeur numérique correspondant à un niveau d'aplomb hiérarchique

Exemple :
col B col C
BMP1 1
BMP11 2
BMP12 2
BMP121 3
BMP122 3
BMP1221 4
BMP1222 4
BMP13 2
BMP14 2
BMP141 3
BMP2 1
BMP21 2
... ...

- la fonction F_Calcul (dev en VBA) pour un niveau donné (par ex) C4 nécessite de reprendre la valeur calculée en B3 pour l'incrémenter et l'afficher en B4. Mais là je bloque car j'ai un message de référence circulaire en essayant de lire la valeur calculée à la ligne précédente.

Comment faire pour résoudre ce problème ?

Merci d'avance de votre aide
Cordialement ;)
oracle7556

PS : mon code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
'---------------------------------------------------------------------------------------
    ' Déclarations Publiques / Privées
'---------------------------------------------------------------------------------------
'
    Dim rBigramme As Range    ' Plage contenant le bigramme
    Dim rLcn As Range    ' Plage contenant le LCN
    Dim rAplomb As Range    ' Plage contenant le niveau d'aplomb
    
    Dim lLigneCourante As Long  ' Numéro de la ligne courante
    Dim iAplomb As Integer  ' Valeur de l'aplomb courant
    Dim iAplombPrec As Integer  ' Valeur de l'aplomb précédant
    Dim iAplombSuiv As Integer  ' Valeur de l'aplomb suivant
    Dim sLcn As String    ' Chaine constituant le LCN
    Dim sLcnPrec As String    ' Valeur du LCN à la ligne précédente
    Dim lTailleFormat As Long ' Taille du format
    
    Dim stFormatLCN(0 To 8) As String    ' On définit la taille du tableau contenant les formats
                                         ' de chaque niveau d'aplomb

'---------------------------------------------------------------------------------------
    ' Initialisations
'---------------------------------------------------------------------------------------
'
    ' On récupère les plages nommées dans la feuille de calcul
    Set rBigramme = Worksheets("Feuil1").Range("Bigramme")
    Set rLcn = Worksheets("Feuil1").Range("Lcn")
    Set rAplomb = Worksheets("Feuil1").Range("Aplomb")
    
    iAplomb = 1     ' Vdp
    iAplombPrec = 0     ' Vdp
    iAplombSuiv = 0     ' Vdp
    sLcn = ""     ' VpD
    sLcnPrec = ""     ' VpD
    
    lLigneCourante = 0  ' VpD
    lTailleFormat = 0   ' VpD
    
    ' On initialise le tableau contenant les formats de chaque niveau d'aplomb
    ' Le numéro d'indice du tableau correspond au numéro d'indice de l'aplomb
    ' L'indice "0" n'est présent que pour gérer le code du ... ("B" pour ...)
    ' Séquences au format : 1er car = taille en Nb car, 2ème car = type de séquence
    stFormatLCN(0) = "1B"   ' "B" pour ...
    stFormatLCN(1) = "2$"   ' Bigramme de l'installation
    stFormatLCN(2) = "2X"   ' Commence à "01" et fini à "ZZ"
    stFormatLCN(3) = "2Y"   ' Commence à "AA" et fini à "99"
    stFormatLCN(4) = "2X"   ' Commence à "01" et fini à "ZZ"
    stFormatLCN(5) = "2Y"   ' Commence à "AA" et fini à "99"
    stFormatLCN(6) = "2X"   ' Commence à "01" et fini à "ZZ"
    stFormatLCN(7) = "2Y"   ' Commence à "AA" et fini à "99"
    stFormatLCN(8) = "1X"   ' Commence à "1" et fini à "Z"
    
'---------------------------------------------------------------------------------------
    ' Implémentation
'---------------------------------------------------------------------------------------
'
    If IsEmpty(prAplomb.Value) Then Exit Function   ' Terminé : pas de calcul à réaliser
    
    iAplomb = prAplomb.Value ' Récupère la valeur de l'aplomb courant
    
    If Not IsNumeric(iAplomb) Then
        MsgBox ("ERREUR : L'aplomb doit être numérique !")
        
    Else
        
        lLigneCourante = prAplomb.Row  ' Récupère le numéro de ligne courante
        
        If iAplomb <= UBound(stFormatLCN, 1) Then
            ' Le niveau d'aplomb est conforme pour le LCN
            
            If iAplomb = 1 Then
                If rBigramme.Cells(lLigneCourante, 1).Value <> "" Then
                    ' Le bigramme est renseigné
                    sLcn = Right(stFormatLCN(0), 1) & rBigramme.Cells(lLigneCourante, 1).Value    ' On initialise la chaine du LCN
                    F_Calcul_LCN = sLcn    ' On affecte la valeur au LCN
                Else
                    ' Le bigramme n'est pas renseigné --> on quitte la procédure
                    MsgBox "F_Calcul_LCN : Bigramme d'intallation non renseigné " & _
                        vbCrLf & " --> Calcul impossible !"
                End If
                
            Else
                ' On récupère la valeur de l'aplomb précédent
                iAplombPrec = rAplomb.Cells(lLigneCourante - 1, 1).Value
                If (iAplomb - iAplombPrec) <= 1 Then
                    ' On récupère la valeur du LCN précédent
' ET C'EST LA QUE L'ERREUR DE REFERENCE CIRCULAIRE INTERVIENT !
                    sLcnPrec = rLcn.Cells(lLigneCourante - 1, 1).Value
'---------------------------------------------------------------
                    ' On récupère la taille du format courant
                    lTailleFormat = CLng(Val(Left(stFormatLCN(iAplomb), 1)))

                    Select Case iAplombPrec
                        Case Is < iAplomb
                            ' On initialise la chaine du LCN pour le niveau à partir du LCN Précédent
                            sLcn = sLcnPrec & InitialiseNiveau(stFormatLCN(iAplomb))
                            ' On initialise la mémorisation de la partie de sequence d'incrémentation du
                            ' niveau d'aplomb courant
                            itPartieSeq(iAplomb) = 1

                        Case Is = iAplomb
                            ' On incrémente la valeur du LCN pour le niveau
                            sIndex = IncrementeNiveau(stFormatLCN(iAplomb), _
                                                      Right(sLcnPrec, lTailleFormat), _
                                                      itPartieSeq(iAplomb))
                            sLcn = Left(sLcnPrec, Len(sLcnPrec) - lTailleFormat) & sIndex

                        Case Is > iAplomb
                            ' On détermine la valeur du LCN pour le niveau courant à partir du LCN précédent
                            ' On calcule la taille du LCN pour le niveau courant
                            lTailleLcn = 0
                            For iInd = 0 To iAplomb
                                lTailleLcn = lTailleLcn + CLng(Val(Left(stFormatLCN(iInd), 1)))
                            Next iInd
                            ' On récupère la partie du LCN du niveau précédent pour constituer le LCN du niveau courant
                            sLcnCourant = Left(sLcnPrec, lTailleLcn)
                            ' On incrémente la valeur du LCN pour le niveau
                            sIndex = IncrementeNiveau(stFormatLCN(iAplomb), _
                                                      Right(sLcnCourant, lTailleFormat), _
                                                      itPartieSeq(iAplomb))
                            sLcn = Left(sLcnCourant, Len(sLcnCourant) - lTailleFormat) & sIndex

                    End Select
                    ' On initialise la chaine du LCN
                    sLcn = Right(stFormatLCN(0), 1) & rBigramme.Cells(lLigneCourante, 1).Value
                    ' On affecte la valeur au LCN
                    F_Calcul_LCN = sLcn


                    If InStr(1, rLcn.Cells(lLigneCourante, 1).Value, "?") > 0 Then
                        ' Le LCN comporte une erreur : on l'affiche en gras blanc sur fond rouge
                        rLcn.Cells(lLigneCourante, 1).Interior.Color = vbRed
                        rLcn.Cells(lLigneCourante, 1).Font.Color = vbWhite
                        rLcn.Cells(lLigneCourante, 1).Characters.Font.Bold = True
                    End If

                Else
                    MsgBox "F_Calcul_LCN : ligne n°" & lLigneCourante & _
                            " Ecart entre aplomb courant et aplomb précédent > 1 !!!"
                     L 'aplomb courant comporte une erreur : on l'affiche en gras blanc sur fond rouge
                    rAplomb.Cells(lLigneCourante, 1).Interior.Color = vbRed
                    rAplomb.Cells(lLigneCourante, 1).Font.Color = vbWhite
                    rAplomb.Cells(lLigneCourante, 1).Characters.Font.Bold = True

                End If

            End If  ' iAplomb = 1

        Else
            MsgBox "F_Calcul_LCN : Niveau d'aplomb supérieur au niveau d'aplomb maximum imposé par le LCN !!!"
            Exit Function

        End If
        
    End If

'    On Error GoTo F_Calcul_Lcn_Erreur
'
'    On Error GoTo 0
'    Exit Function
'
'F_Calcul_Lcn_Erreur:
'
'    MsgBox "Erreur " & Err.Number & " (" & Err.Description & ") dans la fonction F_Calcul_LCN du module F_Calcul_LCN"
'
'Fin:
    
End Function

21/03/2009, 09h35
Qwazerty
Salut
Je suis ton code, pas facile, il faut recréer l'entête de la function et désactiver les appelle a des function perso extérieur :s

j'ai repéré ca qui n'est pas très cohérent
Code:

1 2 3 4 5 6 iAplomb = prAplomb.Value ' Récupère la valeur de l'aplomb courant If Not IsNumeric(iAplomb) Then MsgBox ("ERREUR : L'aplomb doit être numérique !") Else
Il vaudrait mieux tester si le contenu de la cellule est numeric avant de vouloir mettre se contenu dans un integer, qui par definition lui sera forcement numeric ;)

Je regardes la suite:mur:

a++
Qwaz

Re
Je pense que ton probleme commence avec cette ligne, je sais pas comment je vais exliquer ca ....
Code:

1 2 iAplombPrec = rAplomb.Cells(lLigneCourante - 1, 1).Value
Ici tu ne selectionne pas ta ligne precedente!

Je vais essayr de m'expliquer clairement :aie:

Imaginons que tes données soit colonne A de la cellule A4 à A8.

Ta macro pointe sur la cellule A6 (prAplomb), ce que tu souhaites faire c'est regarder la cellule au dessus, donc A5, hors:

Code:

lLigneCourante = prAplomb.Row

te renvoie bien la ligne 6

Si rAplomb représente les cellules A4 à A8
Tu fais :

Code:

iAplombPrec = rAplomb.Cells(lLigneCourante - 1, 1).Value

Donc ici a l'interieur de la plage A4:A8, tu selectionnes la ligne 6-1 donc la ligne 5 MAIS de cette plage (donc la cellule A9 !!, qui ici ne fait mm pas partie de la plage!).

J'espère que tu comprend ton erreur.

Je pense que pour résoudre ton soucis il te faut utiliser quelque chose comme

Code:

valeurprecedente = prAplomb.Offset(-1,0).value

Attention a toi de faire une gestion avant cette ligne au cas ou tu te trouverais a la 1er ligne de ta colonne, ce code revenant a pointer sur l'entete de ta colonne ou pire aa l'exterieur de ton tableau su tes valeur debut a la 1er ligne de la feuille.

A++
Qwaz
22/03/2009, 12h59
oracle7556

Bonjour Quazerty,
D'abord Merci pour ta réponse aussi rapide et surtout aussi complète.
Efffectivement avec ces explications j'ai compris mes erreurs de référencement dans une plage.
Je te confirme qu'avec :

Citation:

Je pense que pour résoudre ton soucis il te faut utiliser quelque chose comme
Code :
valeurprecedente = prAplomb.Offset(-1,0).value

mon problème est résolu. Enfin resque ! dans la mesure où maintenant c'est la mise en couleur des cellules hors norme qui ne fonctionne pas (voir le code initial). Sachant que je ne souhaite pas (par commodité d'application de ma fonction dans différents fichiers d'avoir à gérer cela avec une mise en forme conditionnelle directement dans EXCEL. je préfèrerai gérer cela dans le code de ma fonction. Si tu as une idée je suis preneur !

Merci encore de ton aide sympathique.
Cordialement
oracle7556
:):):)
22/03/2009, 17h06
Qwazerty
Salut
Au vu du code et ne pouvant faire de test faute d'un fichier ayant la bonne architecture et toutes les procédures appelé par ton code, je dirais que le problème est toujours le même, tu utilises LignrCourante qui ne représente pas ta position dans ta feuille complète, mais juste dans le tableau que tu passe en paramètre a ta function (prAplomb).

Pour ce qui est de coloré une autre cellule, je ne suis pas sur que cela marche si facilement que ca, je vais regarder.

[Edit]
C'est bien ce que je craignais, il ne semble pas possible de faire cela dans une Function perso.
Regardes cet exemple que j'ai fais vite fait
Code:

1 2 3 4 5 Function ColorACote(TheCell As Range) If TheCell.Value = 1 Then TheCell.Interior.ColorIndex = 54 TheCell.Value = 2 End Function
Dans la cellule C1 par exemple tu mets

Code:

=ColorAcote("B1")

On se rend vite compte que 1/ la cellule B2 ne passe jamais en couleur et ce même si son contenu passe a 1. 2/son contenue ne passe pas a 2, cette ligne n'est jamais exécuté, la macro plante sur la ligne de changement de couleur.

Il me semblais bien avoir rencontré ce problème, il ne semble pas possible d'interagir comme ça sur les cellule depuis une function perso, mais peut être quelqu'un aura une explication pour justifier cela et mieux peut être une solution pour y parvenir ;)

[/Edit]
a++
Qwaz