ma fonction retourne #VALEUR! lors de la suppression d'une ligne

[oracle7]

[Résolu] ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonjour à tous,

Voici mon problème :
Contexte
Dans chaque cellule de la plage A1:A100 j'ai une formule personnelle écrite en VBA du type "=MaFonction (Bx)".
Dans la plage B1:B100, je saisi une valeur numérique X qui sert de donnée d'entrée à "MaFonction".
En entête du tableau j'ai deux boutons. Un pour insérer une ligne n'importe où dans la plage A1:B100. L'autre pour supprimer une ligne quelconque dans cette même plage.
Voici le code de ces 2 boutons :
Bouton "Insérer ligne"

Private Sub CommandButton1_Click()
    Dim iNoLigne As Integer
    
    Application.ScreenUpdating = False
    iNoLigne = ActiveCell.Row ' On récupère le N° de la ligne courante
    With Range("A" & iNoLigne & ":C" & iNoLigne)
        .Resize(1).EntireRow.Insert ' On insert une ligne vide
        ' On recopie les formats et formules de la ligne précédente
        .EntireRow.Copy .Offset(-1).Resize(1).EntireRow
        On Error Resume Next ' Au cas où il n'y ait pas de constantes
        ' On efface les valeurs constantes préentes dans la ligne
        .Offset(-1).Resize(1).EntireRow.SpecialCells(xlConstants).ClearContents
    End With
    Application.ScreenUpdating = True
    ActiveCell.Select
End Sub

Bouton "Supprimer Ligne"

Private Sub CommandButton2_Click()
    Dim iNoLigne As Integer
    
    Application.ScreenUpdating = False
    iNoLigne = ActiveCell.Row ' On récupère le N° de la ligne courante
    With Range("A" & iNoLigne & ":C" & iNoLigne)
        ' On supprime la ligne courante
        .Resize(1).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
    ActiveCell.Select
End Sub

Mon Problème :
Lorsque je supprime une ligne, TOUTES les cellules contenant "MaFonction" perdent le résultat généré par "MaFonction" et affichent "#VALEUR!".
Pour mémoire, lorsque j'insére une ligne via le bouton "Insérer ligne", tout est OK, je ne perd pas l'affichage du résultat de calcul pour chaque ligne.

Quelqu'un peut-il me dire comment corriger ce problème.
D'avance MERCI.

Cordialement
oracle7

 

[jeanpierre]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonsoir oracle7, Bienvenue sur le forum,

Si ta formule, plutôt que d'être écrite en dur dans ta feuille Excel, était écrite dans une formule nommée par Insertion/Nom/Définir, le nom que tu veux et dans Fait référence à tu mets ta formule.

Dans ta feuille tu aurais donc : =maformule, si c'est le nom que tu lui a donné...

Normalement cela doit mieux fonctionner.

A te lire et bonne soirée.

Jean-Pierre

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonjoir jeanpierre,

1 - En premier lieu : MERCI pour ta réponse rapide !

2 - J'ai suivi ton conseil et donc si j'ai bien compris la manip voilà le détail de mes actions :
a) J'avais : en A1 la formule suivante : =F_Calcul_LCN(B1) et ainsi de suite pour les lignes suivantes jusque An : =F_Calcul_LCN(Bn)
b) J'ai défini un nom par Insertion/Nom/Définir : "Calcul_LCN" et j'ai inscrit dans "Fait référence à" ma fonction F_Calcul_LCN (soit =F_Calcul_LCN).
c) J'ai ensuite en A1 introduit la formule suivante : =Calcul_LCN(B1) et j'ai ensuite copier la cellule A1 dans la plage A2:An.
d) J'ai ensuite saisi des données dans la colonne B. J'ai inséré des lignes avec le bouton idoine. Jusque là pas de problèmes; la fonction fait son travail correctement.
e) Lorsque par contre je supprime une ligne au hasard avec le bouton "Supprimer ligne" j'obtiens toujours le même résultat à savoir : perte de toutes les données calculées par la fonction et affichage à la place de #VALEUR!

Au final, j'en suis toujours au même point. Dommage ...
Et cela commence à m'agacer de ne pas trouver l'erreur qui me génère ce problème. Là, je teste sur une centaine de lignes d'exemple mais au final ma fonction devra s'appliquer à plusieurs milliers. Alors si le simple fait de supprimer une ligne me fait perdre tout le travail de saisie précédent ce n'est pas acceptable. Voilà pourquoi j'appelle au secours ...
Donc je continue à chercher mais j'avoue ne plus trop savoir par quel bout prendre ce problème.

Cela dit Merci encore pour ton aide
Cordialement
oracle7

 

[jeanpierre]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Re,

Peut-être qu'un petit fichier exemple avec 50 ou 100 lignes (compressé avec Windows s'il dépasse les 48 Ko) pourra aider.

Jean-Pierre

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

jeanpierre,
Tu trouveras ci-joint mon fichier compressé RAR en 2 parties à réassembler.
Donc à réception il te faudra remplacer l'extension ".zip" de chacun des eux fichiers par ".rar" et reconstituer alors le fichier final en décompressant simplement la première partie.
Désolé je ne peux faire mieux, le fichier compressé pèse 68Ko et le site n'acceptant que des fichiers ".zip", c'est l'astuce que employée pour contourner la restriction. Comme il n'a pas de MP disponible (j'aurais préféré mais ...)
Merci encore de ton aide.
Cordialement
oracle7

Edit :
PJ supprimées

 

[jeanpierre]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Re,

Malgré l'utilisation de IZArc qui extrait le .Rar, ton premier fichier m'envoie plein de messages et ne s'ouvre pas et le second est vide d'apparence.

Au besoin et si tu ne peux vraiment pas descendre en dessous de la limite, dépose sur cijoint.fr (délai de conservation assez long).

Merci pour ton merci pour l'aide, mais pour l'instant je ne suis pas sûr de pouvoir t'aider...

Jean-Pierre

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Effectivement ce sera plus simple comme cela.
Donc le lien --> Cijoint.fr - Service gratuit de dépôt de fichiers
Merci encore.

 

[jeanpierre]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Re,

Y'a de la lecture.... pfiou.

A priori, il n'y a pas besoin de rajouter ou de supprimer une ligne pour obtenir le #VALEUR! (certes, une suppression l'engendre sur toute la colonne)

Le seul fait de valider une cellule, par exemple B3 et #VALEUR! apparaît. Ce doit être dans la fonction qu'il y a un problème, mais comme dit il va falloir s'y coller pour tout lire, pas gagné....

Jean-Pierre

 

[ROGER2327]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonsoir à tous

Pas de problème pour décompresser le fichier.
Ensuite, c'est touffu...
Pour l'instant, rien trouvé de mieux que cette modification du code du bouton de suppression :

[COLOR="DarkSlateGray"][B]Private Sub CommandButton2_Click()
    Dim iNoLigne As Integer
    Dim aplbVal
    Dim i As Long

    Application.ScreenUpdating = False
    iNoLigne = ActiveCell.Row ' On récupère le N° de la ligne courante
    With Range("A" & iNoLigne & ":C" & iNoLigne)
        ' On supprime la ligne courante
        .Resize(1).EntireRow.Delete
    End With
    With Range("Aplomb")
      aplbVal = .Value
      .ClearContents
      For i = 1 To .Cells.Count
        .Cells(i, 1).Activate
        ActiveCell.Value = aplbVal(i, 1)
      Next i
    End With
    Application.ScreenUpdating = True
End Sub[/B][/COLOR]

Est-ce quelque chose de ce genre qu'on cherche à obtenir ?​

ROGER2327
#2200

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonjour ROGER2327
Merci de ta réponse.
Je teste la solution que tu proposes et te tiens au courant du résultat ...
Cordialement
oracle7

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonsoir ROGER2327

Je viens de tester la modification du code du bouton Supprimerligne que tu m'as proposer.
SUPER CA MARCHE ! MERCI BEAUCOUP
C'est exactement ce que j'attendais mais pourrais-tu m'expliquer en quoi le fait de revalider (si j'ai bien compris le code) chaque valeur d'aplomb saisie ne provoque plus l'effacement de mes données calculées par ma fonction ?
Je suis curieux de comprendre.
Merci de ta réponse.
Cordialement
oracle7

 

[ROGER2327]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Re...

Votre fonction est complexe et je ne prétends pas l'avoir épluchée de A à Z. Il est toutefois évident que cette partie crée un problème :

[COLOR="DarkSlateGray"]        [COLOR="SeaGreen"]' Récupère la valeur de l'aplomb courant[/COLOR]
        iAplomb = [B]ActiveCell[/B].Value
[COLOR="SeaGreen"]'         Récupère le numéro de ligne courante[/COLOR]
        lLigneCourante = [B]ActiveCell[/B].Row - 2 [COLOR="SeaGreen"]' -2 = décalage entre le numéro de la première ligne de la plage _
                                            de l'aplomb et le numéro réel de ligne affiché par EXCEL[/COLOR][/COLOR]

Lorsque vous appelez la fonction en validant une valeur dans la plage C3:C..., ActiveCell désigne un argument possible de la fonction. Mais si la fonction est exécutée alors que la cellule active n'est pas dans cette plage, les choses se gâtent... C'est le cas par exemple lorsque vous supprimez une ligne. Cette suppression entraîne l'actualisation de la feuille et l'appel répété de la fonction. Mais ActiveCell est une constante sans rapport avec les arguments successifs que doit traiter la fonction.
Il faudrait avoir quelque chose de ce genre à la place du recours à la cellule active :

[COLOR="DarkSlateGray"][B]        iAplomb = Range(prAplomb.Address).Value '*****
        lLigneCourante = Range(prAplomb.Address).Row - 2 '*****[/B][/COLOR]

Je ne suis pas certain que cela règle tout (risque, peut-être, de référence circulaire lors de la suppression de lignes), mais ça peut être une piste de travail. Ce que je crois, c'est que la structure de la fonction est à revoir.
Le bricolage que j'ai proposé hier contourne cette erreur de programmation en réintroduisant une à une les valeurs d'aplomb (je ne sais pas ce que c'est, mais là n'est pas la question). Ce faisant, on assure la coïncidence de la valeur de l'argument de la fonction avec celle de la cellule active. C'est un pis-aller sans doute, mais ça fonctionne.
La procédure que j'ai introduite peut d'ailleurs servir au recalcul de la feuille, sous la forme autonome suivante :

[COLOR="DarkSlateGray"][B]Sub Recalcul()
   Dim aplbVal
   Dim i As Long
   Application.ScreenUpdating = False
   With Range("Aplomb")
      aplbVal = .Value
      .ClearContents
      For i = 1 To .Cells.Count
         .Cells(i, 1).Activate
         ActiveCell.Value = aplbVal(i, 1)
      Next i
   End With
   Application.ScreenUpdating = True
End Sub[/B][/COLOR]

En espérant n'avoir pas dit trop de bêtises,​

Cordialement,
ROGER2327
#2202

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Bonsoir ROGER2327

1 - Merci pour ta réponse et ton analyse de mon problème.

2 - J'ai donc testé ta proposition de code :

        iAplomb = Range(prAplomb.Address).Value '*****
        lLigneCourante = Range(prAplomb.Address).Row - 2 '*****

Cela marche bien lors de la première exécution (saisie de valeurs d'aplomb, insertion de lignes). Par contre et comme tu le pressentais après une suppression de ligne je récolte bien une référence circulaire et les choses empirent car le code se "perd" ensuite et fait du n'importe quoi !

3 - Maintenant si j'ai bien compris ce que tu dis :

Mais si la fonction est exécutée alors que la cellule active n'est pas dans cette plage, les choses se gâtent... C'est le cas par exemple lorsque vous supprimez une ligne. Cette suppression entraine l'actualisation de la feuille et l'appel répété de la fonction.

J'en déduit que la solution est peut être d'empêcher l'exécution du code de ma fonction pour tout appel à la fonction (lors d'une actualisation du classeur) si la cellule active n'est pas dans la plage d'aplomb + 1 ligne (après la dernière) ce qui peut être effectivement le cas lors de la suppression d'une ligne pour laquelle l'aplomb est renseigné.

Pour préciser mon propos : "cellule active n'est pas dans la plage d'aplomb + 1 ligne (après la dernière)" --> ceci afin d'une part de permettre l'insertion d'une ligne lorsque la cellule active est dans la plage de valeurs d'aplomb déjà saisies et d'autre part pour permettre l'ajout d'une ligne (donc la saise d'un aplomb déclenchant le calcul du LCN) lorsque la cellule active est exactement située après la dernière cellule renseignée de la plage d'aplomb (i.e. en bas de cette plage).

J'espère ne pas avoir été trop confus dans mon explication !

Par contre maintenant, pour appliquer cette solution si elle te parait cohérente, comment faire pour vérifier que la cellule active est bien dans la plage d'aplomb idoine et ainsi autoriser l'exécution du reste du code (donc effectuer le calcul de LCN) lors d'un appel de la fonction ou bien à l'inverse comment interdire le calcul lorsque la cellule active n'est pas dans la plage d'aplomb ?
Je vois bien code du genre :

IF ActiveCell.address  "[I]n'appartient pas à[/I]" Range(prAplomb) Then Exit Function

à placer au tout début de ma fonction de calcul.
Mais malheureusement je ne vois pas comment écrire en VBA cette condition de test. J'en appelle encore à ton aimable aide si tu le veux bien.

4 - Tu dis par ailleurs aussi :

Ce que je crois, c'est que la structure de la fonction est à revoir.

Ok pourquoi pas mais alors peux-tu STP préciser ta pensée et bien vouloir me donner les éventuelles pistes à suivre pour effectuer ce travail (une trame quoi ...). D'avance Merci.

Donc MERCI encore de ton aide, en attendant de te lire ...

Cordialement
oracle7

 

[ROGER2327]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

Re...

Votre fonction est compliquée et, même bien commentée comme vous l'avez fait, je n'en ai pas encore compris tous les éléments. Ce n'est pas quelque chose qu'on peut faire en deux minutes... Il me semble toutefois que son exécution dans une cellule fait appel aux résultats qu'elle donne dans d'autres cellules. C'est toujours risqué. Il est difficile (pour moi) de savoir exactement ce qui se passe lors de l'actualisation d'une fonction en chaîne. Une modification du résultat d'une occurrence de la fonction provoque généralement le re-calcul d'autres occurrences dans un ordre que je ne maîtrise pas bien.
Je me demande s'il ne serait pas plus judicieux d'abandonner l'idée d'une formule pour envisager une procédure évènementielle qui traite l'ensemble de la colonne lorsque la valeur d'un paramètre change. Le contrôle de l'ordre dans lequel les cellules doivent être recalculées en serait plus sûr.
Le travail déjà effectué ne serait pas perdu : on pourrait vraisemblablement récupérer une bonne partie de la fonction dans la construction d'une procédure évènementielle.
Je mets beaucoup de conditionnel parce que je n'ai pas entièrement saisi ce que vous voulez faire. Il se peut que je n'ai pas compris le fond de l'affaire...
Est-il possible de décrire le fonctionnement global de la fonction en quelques lignes ? Je veux dire décrire la fonction indépendamment du code actuel. En sachant que telle cellule doit prendre telle valeur en fonction de celles de telles autres cellules, la construction d'une procédure globale est peut-être une solution.
Ce qui est certain, c'est qu'en l'état actuel des choses, je n'ai pas d'autre idée. Je vais encore y réfléchir. Mais, comme vous le remarquiez dans un message privé, peu de monde est intervenu dans cette discussion. Le forum étant riche de talents, ne désespérez pas de voir un spécialiste du code trouver la bonne idée en passant par ici.​

ROGER2327
#2231

 

[oracle7]

Re : ma fonction retourne #VALEUR! lors de la suppression d'une ligne

@ ROGER2327

Bonsoir,

1 - Merci de bien vouloir continuer d'examiner mon problème. C'est vraiement sympa !

2 -

Est-il possible de décrire le fonctionnement global de la fonction en quelques lignes ?

Absolument, j'espère toutefois être suffisamment clair dans mon propos pour décrire la fonction en question.

Le contexte :
A la base on cherche à identifier de façon unique tous les composants constituant un système donné. L'ensemble des identifiants ainsi créés constitue ce qu'on appelle une arborescence fonctionnelle.
Chaque niveau de l'arborescence est caractérisé par une valeur numérique que l'on incrémente de 1 à chaque fois que l'on passe à niveau de décomposition supplémentaire avec un maximum de 8 niveaux de décomposition.

A chaque niveau il peut exister de 1 à n éléments.

L'identifiant de chaque élément est codé en fonction du niveau de décomposition auquel se trouve l'élément en question. Au final l'identifiant de l'élément est la concaténation des codes (codex) de tous les niveaux d'arborescence rencontrés pour l'élément. Par ex si l'élément est au 8éme niveau de décomposition du système on aura : code1code2code3code4code5code6code7code8
Avec :

code1 = $$$ (3 car identifiant de l'appareil)
code2 = Commence à "01" et fini à "ZZ"
code3 = Commence à "AAA" et fini à "999"
code4 = Commence à "001" et fini à "ZZZ"
code5 = Commence à "AA" et fini à "99"
code6 = Commence à "01" et fini à "ZZ"
code7 = Commence à "AA" et fini à "99"
code8 = Commence à "1" et fini à "Z"
​

avec la codification ci-dessus on voit bien qu'en fait le nombre "n" maxi d'éléments d'un niveau de décomposition est fini et donc limité par le nombre de combinaisons possibles de caractères. En pratique voila à quoi cela ressemble :

Identifiant Niveau
SMP 1
SMP01 2
SMP02 2
SMP02AAA 3
SMP02AAB 3
SMP02AAB001 4
SMP02AAB002 4
SMP02AAC 3
SMP02AAD 3
SMP03 2
SMP03AAA 3
SMP03AAA001 4
SMP03AAA001AA 5
SMP03AAA001AA01 6
SMP03AAA001AA01AA 7
SMP03AAA001AA01AA1 8
SMP03AAA002 4
SMP03AAB 3
SMP03AAC 3
SMP04 2
...
Edit : Désolé la mise en forme n'est pas conservée et ce qui était aligné initialement, ne le reste pas !

En pratique :
Chaque ligne de l'arborescence ci-dessus correspond à une ligne de mon tableau Excel. La colonne B comporte les valeurs d'identifiant et la colonne C les valeurs de niveau. Chaque cellule de la colonne B contient la formule de calcul de l'identifiant.
L'opérateur ajoute successivement des lignes au tableau en saisissant une valeur de niveau. Bien évidemment les règles métier font qu'il ne saisi que des valeurs qui sont soit égales à la valeur de la ligne précédente soit supérieure ou inférieure mais dans ce cas avec un écart maxi de 1 ( valeur précédente + 1 ou valeur précédente - 1).
La valeur saisie est alors passée en paramètre à ma fonction de calcul de l'identifiant soit par ex la cellule B6 contient la formule "=Calcul(C6)".

Ce que fait en gros aujourd'hui ma fonction de calcul :
1 - elle récupère la cellule active pour déterminer sur quelle ligne elle est.
2 - à partie de là elle recherche la valeur maxi de l'identifiant correspondant au même niveau d'aplomb que celui qui a été saisi pour l'incrémenter. Je passe sur ce processus d'incrémentation lui même qui est complexe et pas l'objet de mon problème.
Bien évidemment tous les cas de figure sont traités selon la valeur saisie du niveau (niveau nouveau, niveau existant, niveau >, niveau <) par rapport à la valeur précédente. Rappel au final l'identifiant doit être unique !

Au final tout cela marche bien tant que je suis en saisie de valeur de niveau, même lorsque j'insère une ligne dans le tableau.
Où cela se gâte c'est quand je supprime une ligne. Alors là toutes les valeurs d'identifiant sont remplacées par "#VALEUR!". Ce qui n'est pas acceptable car il faut reprendre toute la saisie !

Voilà donc le pourquoi de mon appel à l'aide ici même car je retourne le problème dans tous les sens sans trouver de solution. De plus mes connaissances en VBA sont rudimentaires alors ...

Notre ami ROGER2327 m'a proposé un début de solution mais comme il le dit lui même c'est un "pis aller". Cela marche certes, mais au final je ne sais pas ce que cela donnera sur mon fichier final de plusieurs dizaines de milliers de lignes du point de vue temps de réponse.

Donc voilà, si qq'un a un peu de son temps à me consacrer sur ce problème je lui en serai reconnaissant. Il y a là je crois un bel exercice de style à relever !
D'avance MERCI.

Cordialement
oracle7

PS : le fichier VBA/XLS correspondant est un peu plus haut en PJ.