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

gilboss

XLDnaute Nouveau
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

XLDnaute Nouveau
Voici du code et quelque explication :
VB:
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
 

Discussions similaires

Statistiques des forums

Discussions
314 493
Messages
2 110 197
Membres
110 703
dernier inscrit
papysurf