Discussion :

[oracle7556]

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"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
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

[mercatog]

Il était judicieux de reporter ta fonction et non les sub d'ajout ou suppression

essaie de mettre au début de ta fonction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

application.volatile

[oracle7556]

bonsoir mercatog

J'ai placé "application.volatile" en début de ma fonction comme tu me l'a conseillé.
Eh bien c'est une catastrophe, ma fonction (en phase saisie de données) me renvoie systématiquement des #VALEUR!
Donc c'est pas la solution !
Je cherche d'autres pistes du coup.
Merci quand même pour ton aide.

[mercatog]

sans ta fonction, on ne peut rien deviner
si tu la mettais ici, ça serait mieux

[oracle7556]

Effectivement ce sera plus simple. Donc ci-joint le fichier en question.

[mercatog]

Tes fonctions ont pour argument des Ranges; quand tu supprime une ligne, le Range se trouve modifié,
Il faudrait recalculer la feuille (avec précaution)
je ne sais pas ce que font tes fonctions, mais c'était ma logique de ma compréhension des choses.

Application.Volatile: Cette méthode marque comme volatile une fonction personnalisée. Une fonction volatile doit être recalculée chaque fois qu'un calcul est effectué dans une cellule quelconque de la feuille de calcul. Une fonction non volatile n'est recalculée qu'en cas de changement des variables d'entrée. Cette méthode est sans effet si elle ne se trouve pas à l'intérieur d'une fonction définie par l'utilisateur utilisée pour calculer une cellule de feuille de calcul.

[oracle7556]

Bonjour mercatog,

Une fonction non volatile n'est recalculée qu'en cas de changement des variables d'entrée.

C'est bien pour cela que je n'ai pas rendu volatile ma fonction.
Mais alors comment expliquer que la fonction répond bien à mon besoin et qu'en plus lors d'une insertion de ligne tout fonctionne bien alors que lors de la suppression de ligne je perd mes données déjà calculées ? dans les deux cas les arguments Range sont modifiés même si les valeurs pures restes inchangées !
Désolé mais du coup je ne comprend trop; peux-tu STP être plus précis ?
Merci de ta réponse

[mercatog]

Voilà, pour être précis (ou proche de la précision); il est nécessaire de "déchiffrer" le fonctionnement des fonction.j'avais vaguement proposé le application.volatile au cas ou ça résoudra ton problème
bon courage

[Pierre Fauconnier]

Bonjour

J'ai regardé ta fonction...

En voici quelques lignes, nettoyées des commentaires

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Public Function F_Calcul_LCN(ByVal prAplomb As Range) As Variant
 
    ' ...
 
                If iAplomb = 1 Then
                    sLcn = Right(stFormatLCN(0), 1) & rBigramme.Cells(lLigneCourante, 1).Value
                    F_Calcul_LCN = sLcn
                Else
                    iAplombPrec = prAplomb.Offset(-1, 0).Value
    ' ...

La ligne après le ELSE est sujette à caution... Dans ton tableau, ta fonction sur une cellule de B reçoit comme paramètre la cellule de C sur la même ligne, qui utilise donc potentiellement la cellule C de la ligne au dessus (iAplombPrec = prAplomb.Offset(-1, 0).Value). Comme tu utilises cette formule en la tirant vers le bas, tu crées un "chainage" entre tes lignes qui amène à des problèmes de calcul dès lors que tu supprimes une ligne...

A mon avis, il faut revoir ta façon de procéder à ce stade.

De plus, tu as placé des MsgBox dans ta fonction. C'est loin d'être idéal. Si tu veux renseigner l'utilisateur sur un problème, renvoie une valeur d'erreur en retour de fonction. Mais pense, évidemment, que le chainage dont je parle plus haut joue en cascade lors du calcul, et qu'une valeur d'erreur en amont va inévitablement amener la même erreur en cascade.

N'oublie pas non plus qu'une fonction utilisée dans une feuille de calcul ne peut RIEN modifier au classeur, ni en direct, ni via des fonctions ou procédures qui seraient appelées par cette fonction.

[oracle7556]

@ Pierre Fauconnier

1 - Merci d'avoir pris le temps d'examiner ma fonction et de m'apporter une explication aussi rapide.

2 - OK à la vue de votre explication je comprend mieux le genre d'erreur que je fais. J'en déduit que le chainage ainsi introduit est préjudiciable et est certainement la source de mon problème lorsque je supprime une ligne et que, de fait, la fonction est ré-appelée pour recalcule avec le rafraichissement induit ce qui génère ces fameux #VALEUR!.

3 - Maintenant comment faire pour éviter ce chainage ?
C'est à dire si je vous ai bien suivi, il conviendrait de trouver une autre façon que d'appliquer : (iAplombPrec = prAplomb.Offset(-1, 0).Value) pour récupérer cette valeur. Mais voilà, dans le processus j'ai absolument besoin de connaitre la valeur calculée précédemment pour l'incrémenter et j'ai beau retourner le problème dans tous les sens, je vois pas comment faire autrement.
Par contre vous qui avez examiné ce code, vous avez peut être une piste à indiquer que je pourrait "creuser" (voire la solution ce qui serait alors un must !) ?

Merci d'avance de votre réponse.
Cordialement
oracle7556

[Pierre Fauconnier]

Bonsoir

Une possibilité est de passer les arguments en tant que valeur et non en tant que cellule, et d'en passer deux, celui de la ligne et celui de la ligne précédente.

Au vu du fichier, les aplombs sont des entiers, tu pourrais donc passer les paramètres as long. A toi de voir en fonction de tes besoins et des valeurs des aplombs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Public Function F_Calcul_LCN(ByVal prAplomb As long, Byval prAncienAplomb as long) As Variant

Tu peux aussi passer un/les paramètre(s) en optional

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Public Function F_Calcul_LCN(ByVal prAplomb As Long, Optional ByVal prAncienAplomb As Long) As Variant

au cas où tu ne saurais pas passer de valeur pour l'ancien aplomb, par exemple.

Note toutefois que dans ce cas, parce que déclaré as long, sa valeur sera automatiquement 0 si tu l'omets => A toi de voir si 0 est une valeur acceptable pour un aplomb et ce qu'il y a lieu de faire dans ce cas. Tu peux forcer une valeur par défaut en utilisant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Public Function F_Calcul_LCN(ByVal prAplomb As Long, Optional ByVal prAncienAplomb As Long = 1) As Variant

Si 0 est une valeur acceptable ou que tu ne peux pas déterminer une valeur par défaut, alors, tu peux passer tes valeurs as variant de façon à tester si un argument optionnel est manquant ou pas, en t'inspirant de ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Function F_Calcul_LCN(Aplomb As Variant, Optional AncienAplomb As Variant)
    If IsMissing(AncienAplomb) Then
        Debug.Print "manquant"
        Debug.Print "Présent"
    End If
End Function

Dans la feuille de calcul, tu utiliseras évidemment un pointage vers les cellules idoines pour passer tes arguments.

[oracle7556]

@ Pierre Fauconnier

Ok : Merci je vais tester ces solutions et vous tiens au courant du résultat.

Juste un petit truc, je sais cela parait idiot mais j'ai un doute sur la compréhension à propos :

Dans la feuille de calcul, tu utiliseras évidemment un pointage vers les cellules idoines pour passer tes arguments.

Pouvez vous SVP préciser "un pointage" ? Merci d'avance

Cordialement
oracle7556

[Pierre Fauconnier]

Je voulais simplement dire que, dans la feuille excel, il suffit de renseigner la référence de la cellule comme paramètre de la fonction, éventuellement par pointage (= avec la souris)...
Donc, la formule à utiliser sera par exemple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=F_Calcul_LCN(C3;C2)

[oracle7556]

Bonjour à tous,

Finalement j'ai résolu mon problème en changeant de stratégie. Je n'utilise plus de fonction directement dans la cellule à calculer. Je gère l'appel à ma fonction de calcul par une procédure évènementielle. Je n'ai donc plus de lien entre cellules et donc d'effets de cascade lors des recalculs automatiques sur la feuille.

Dans tous les cas, Merci à tous ceux qui m'ont consacré un peu de leur temps pour m'aider à résoudre mon problème.

Cordialement
oracle7556