XL 2019 Insérer des lignes manuellement et modifier les codes vba

[gilboss]

Bonjour,

J'ai plusieurs pages d'un programme excel dont j'aimerai insérer des lignes. Concernant les formules écrites dans les pages, les décalages s'écrivent automatiquement et c'est parfait. Mais concernant les adresses de cellules dans les formules VBA, je suis obligé de les reprendres manuellement. C'est bien dommage, car il y en a beaucoup et cela crée des bugs dans mon programme excel.

Existe-il une méthode pour que le VBA suive les insertions ou suppressions de lignes ou colonnes automatiquement comme les formules qui sont dans les cellules des pages ?

En vous remerciant.

 

[Johnsdoeuf]

J'en utilise une :
je définis des noms aux cellules ou plages dont j'ai besoin (par exemple REF_Base)
Puis je l'appelle par une fonction FRef("base")

Les noms et plages évoluent avec la feuille.

voilà

 

[gilboss]

Vous auriez un exemple concret. J'avoue ne par avoir vraiment compris.
Cela veut dire que je suis quand même obligé de modifier toutes mes adresses de cellules VBA.
J'aurais aimé éviter ce travail fastidieux.

 

[TooFatBoy]

Bonjour,

Vous auriez un exemple concret.

Il suffit d'adapter ton exemple, soit avec des plages nommées, soit avec des tableaux structurés.

 

[gilboss]

Bonjour,


Il suffit d'adapter ton exemple, soit avec des plages nommées, soit avec des tableaux structurés.

Un travail titanesque quand même.

 

[TooFatBoy]

Un travail titanesque quand même.

Non. Du moins, pas vu d'ici.

 

[mapomme]

Vous auriez un exemple concret.

Bonjour @gilboss ,

Il me semble que c'est à vous de fournir un exemple concret pas aux répondeurs, n'est-ce pas ?

 

[Johnsdoeuf]

Voici du code et quelque explication :

Option Explicit

'FRef permet d'accéder à des cellules dont le nom (ausens excel) commence par un motif particulier.
'FRef(cle) recherche le nom "motifcle" et renvoit l'objet range correnpondant
'ceci permet d'adresser les cellules ou plage de manière souple(les ajouts de ligne ou colonnes sont possibles sans perdre les liens)

'motif :
Const MotifRef = "REF_"

'"Microsoft scripting runtime" doit être activé dans Outils-Références
Public Ref As New Scripting.Dictionary

'pour récupérer la feuille ou la fichier
'        Set ShList = FRef("ListNomISM").Parent
'        Set WBook = FRef("SynthBasTitre").Parent.Parent

'Initialisation automatique au premier appel de FRef. ne réinitialiser que si besoin (ajout de colonne par exemple)
'commande de réinitialisation
'   Set Ref = Nothing

Sub InitRef()
'
    Dim Nom

    
      'si pas renseigné
    If Ref.Count = 0 Then
        
        Dim LMotifRef
        Dim Cle As String
        Dim ComptRef As Long
        
        LMotifRef = Len(MotifRef)
        Set Ref = Nothing
        
            'balaie tous les "names" du fichier
        For Each Nom In ThisWorkbook.Names
                'Si il commence par MotifRef
            If Left(UCase(Nom.Name), LMotifRef) = MotifRef Then
                    'extrait la clé
                Cle = Mid(Nom.Name, LMotifRef + 1)
                    'ajoute la cellule au dictionnaire
                Ref.Add UCase(Cle), Nom.RefersToRange
    
            End If
                
        Next
        'ajoute un élément bidon si n'a rien trouvé pour différencier de l'absence d'initialisation
        If Ref.Count = 0 Then
            Ref.Add "xrgg5df9g8trdfhg2tr8", ""
        End If
        
    End If

    
End Sub

    
    ' passer par une fonction au lieu d'un appel direct au dictionnaire "Ref" permet de :
    ' - générer une erreur lorsque la clé n'existe pas au lieu de renvoyer vide
    ' - rendre le test insensible à la casse
Function FRef(Clef)
    Dim UClef As String
    Call InitRef
    
    UClef = UCase(Clef)
    If Ref.Exists(UClef) Then
        Set FRef = Ref(UClef)
    Else
        Err.Raise 5011, , "Erreur de clef de référence, La clef est erronée ou inexistante dans le classeur"
    End If
End Function

 

[Johnsdoeuf]

le fichier du sujet "La procédure s'arrête après un événement" contient un exemple d'utilisation du code ci dessus.
Bon test