XL 2021 Une aide pour optimiser du code VBA

[jeff1494]

Bonsoir à toutes et tous;

Pour des besoins personnels j'ai écris 3 petites procédures qui me permettent de lister le nom de tous les contrôle des formulaires présents dans le classeur, et une pour lister tous les noms utilisés dans le classeur (cellules nommées), et enfin une qui me liste tous les formulaires du classeur.

Alors soyons clairs je n'ai qu'un niveau d'amateur débutant en VBA, et donc il y a surement mieux à faire que ce que j'ai pondu.
Je recherche donc une bonne volonté qui aurait la gentillesse de regarder mon code et de l'améliorer (Et pourquoi pas en faire un petit complément si cela est possible)

Par exemple dans le 'rapport' des contrôles des formulaires, j'aimerais gérer plus proprement ce que l'on appelle les 'Groupes (Frames)'. L'idée serait d'avoir une sorte de treeview avec une colonne supplémentaire pour gérer les 'Groupes'. Ce qui donnerait par exemple :

• Colonne A = Nom du Formulaire.
• Colonne B = Nom du Groupe, si existe.
• Colonne C = Nom du Contrôle.
• Colonnes D = Type de Contrôle.
• Colonne E = Libellé ou Caption.

Un peu comme sur cette capture que j'ai réalisé à la main. A noter que la liste est triée dans l'ordre de la disposition des contrôles dans le formulaire, ce qui me va très bien.

Je joins donc un petit classeur contenant les éléments nécessaires pour illustrer mes propos.

D'avance je remercie toute personne qui prendrait de son temps pour m'aider à progresser.
Bonne soirée à toutes et tous.

 

[mapomme]

Bonsoir @jeff1494🙂,

J'avais commencé optimiser mais j'ai dû m'interrompre, désolé🫤. Juste le début avec un peu moins de lignes😉:

Public Sub List_Noms()
'======================================================================
' Procédure pour lister les noms et les Tableaux Structurés du classeur
'======================================================================
Const NomFeuille = "LSTNOMS"
Dim nomP As Name
   On Error Resume Next: Application.DisplayAlerts = False
   Sheets(NomFeuille).Delete: On Error GoTo 0: Application.DisplayAlerts = True  ' supprimer NomFeuille
   Worksheets.Add(after:=Sheets(Sheets.Count)).Name = NomFeuille                 ' recréer NomFeuille
   With Sheets(NomFeuille)
      .Tab.Color = RGB(204, 255, 255)                                            ' couleur de l'onglet
      .Range("a1:c1") = Split("Nom cellule/Emplacement/Adresse Cellule", "/")    ' les titres colonnes A à C
      .Range("e1:g1") = Split("Nom Feuille/Nom Tableau/Adresse Tableau", "/")    ' les titres colonnes E à G
   '       For Each nomP In ThisWorkbook.Names..... suite du code
   End With

 

[patricktoulon]

Bonjour @jeff1494
ma fois pour moi tes codes sont propres et bien commentés
les simplifier reviendrait a réduire les étapes qui sont ma fois bien commentées
un code simplifié le serait bien moins évidement

deux petits détails cependant
1° tu joue avec les vbcomponents de thisworkbook.vbproject et par exemple tu texte feuille existe sur activeworkbook
je ne sais pas si c'est normal mais attention on est pas forcement sur le classeur actif

2° les selectpartout là !!!! burk!!!
tiens pour les formulaires sans select

Public Sub List_All_Userforms2()
    Dim tbl(), vbcomp, count, sh As Worksheet
    ReDim Preserve tbl(1 To 1)
    'compilation dans une variable tableau les noms des formulaires
    
    For Each vbcomp In ThisWorkbook.VBProject.VBComponents
        If vbcomp.Type = 3 Then
            count = count + 1
            ReDim Preserve tbl(1 To count): tbl(count) = vbcomp.Name ' Nom du formulaire
        End If
    Next vbcomp
    
    If UBound(tbl) > 1 Then 'alors il y a des formulaire
        
        'comme un sauvage on brule la feuille
        'car je me suis appercu ( ajouter/ suprimmer  et rebelote)  ajoutait du poids inutilement dans le classeur
        'donc guillotine la feuille
        Application.DisplayAlerts = False
        For Each sh In ThisWorkbook.Worksheets
            If sh.Name = "TabFormulaires" Then sh.Delete
        Next
        
        'on repart donc avec une feuille vierge
        Set Feuille = Worksheets.Add(After:=Sheets(Sheets.count))
        DoEvents
        Feuille.Name = "TabFormulaires"
        With Feuille
            'transfert sur feuille
            .Cells(1) = "Nom Formulaire"
            .Cells(2, 1).Resize(UBound(tbl), 1).Value = Application.Transpose(tbl)
            DoEvents
            
            'conversion en Listobject de la plage occupé par le resultat
            With .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes)
                .Name = "Tbl_Formulaires"
                .TableStyle = "TableStyleMedium7"
                .ShowAutoFilterDropDown = False
            End With
            
            'un peu de design
            With .Cells(1)
                .VerticalAlignment = xlCenter
                .RowHeight = 35.4
                .WrapText = False
                .Font.Size = 14
                .Font.Bold = True
                .Font.Name = "Tw Cen MT"
                .Font.Color = vbBlack
            End With
            
            With .Cells(2, 1).Resize(UBound(tbl) - 1)
                .HorizontalAlignment = xlLeft
                .VerticalAlignment = xlCenter
                '.Font.Color = vbBlack
                .Font.Size = 11
                '.Font.Bold = False
                .Font.Name = "Tw Cen MT"
            End With
            
            .Columns(1).AutoFit
            
        End With
        
    End If
      
End Sub

 

[jeff1494]

Bonjour à toutes et tous;

@mapomme :
Merci pour ton aide. L'instruction Split est en effet très pratique, mais je n'y pense jamais car j'arrive à faire autrement. En tous cas je vais essayer d'y penser plus souvent quand je dois faire ce genre de choses.

@patricktoulon :
Merci pour tes remarques sur mon code. J'arrive à m'améliorer tout doucement, donc je garde l'espoir de pouvoir un jour faire des choses plus propres, et plus structurées.
Ta remarque sur les "Select" est très juste mais quand on ne maitrise pas tout, et c'est mon cas, cela ressemble à une sorte d'assurance de savoir où on est.
J'ai d'énormes lacunes concernant l'environnement d'une procédure, de la portée de certains éléments donc de leur disponibilité et de mon positionnement dans tout cela.
Donc je vais devoir repartir depuis le début, et revoir tout le sujet pour comprendre tout cela.
Je vais voir de plus près ta recommandation concernant les "Select", et adapter les autres bouts de code pour m'approcher de cette idée de travailler sans "Select".

Dans tous les cas je vous remercie d'avoir pris le temps de jeter un œil sur ma demande, et je vais bien sûr essayer de tirer des enseignements de vos propositions.
Je vous souhaite une bonne journée.
Encore merci pour votre temps.

 

[patricktoulon]

Bonjour @jeff1494
perso même avec les selects c'est du code très propre et très bien commenté
d'autres pourraient prendre exemple sur toi

tu sais quoi?!!!
laisse comme ça si les select te rassurent et permettent de te situer psychologiquement dans la chronologie des étapes, quand tu est dans le VBE et ben c'est très bien

et même moi d'ailleurs je ne factorise pas aussi carré les sections de code par exès de confiance

juste en terme d’amélioration pour le workbook
utilise une variable globale comme ça tu l'aura par tout et tu n'aura plus le doute null part si thisworkbook ou activeworkbook

et au diables les puristes

 

[jeff1494]

@patricktoulon :

Je vais en effet voir cette histoire de variable globale, peut-être qu'en réfléchissant il y a d'autres variables à gérer de la même manière.
Par contre ce sont trois petites procédures que j'aimerai bien utiliser à chaque fois que j'entreprends un développement. Alors quel serait ton conseil pour inclure systématiquement cela dans un classeur.
D'avance merci pour ton aide.
Bonne journée à toi.

 

[patricktoulon]

a non dans ce cas c'est dans un xlam qui faut mettre tout ça
et les feuile/tableaux devraient aller dans le classeur?
parce que autant je peux comprendre ce travail mais autant je comprends pas l'intérêt d'avoir ces feuilles dans le projet examiné lui m$
moi je crerais un classeur pour garder un etat du fichier examiné
mais bon c'est toi qui vois

 

[jeff1494]

Bonjour @patricktoulon , je pense que je vais garder le classeur sous le coude et utiliser les macros quand j'en aurais besoin.
En fait lorsque je commence un développement j'essaie de définir la cinématique de l'ensemble, puis de voir les entrées et les sorties (si il y en a).
La définition des données à gérer (type, ...), puis de voir comment les gérer. Là l'utilisation des tableaux structurés (découverte récente pour moi, mais je ne pourrais plus m'en passer) devient évidente.
Donc la définition des formulaires intervient assez rapidement dans mon process. De même que les tableaux structurés qui vont avec les données que je dois gérer.

Et étant de la vieille école pour qui le papier reste une valeur sûre, j'imprime tout ce que je peux pour avoir des références facilement accessibles.
Une espèce de référentiel de ce que j'ai imaginé.

Bref je te remercie pour tes conseils toujours pertinents, justes.

Bonne journée à toi.

 

[patricktoulon]

Donc si je comprends bien
Ton truc va servir à imprimer tes tableaux représentant la hiérarchie des éléments dans les userforms et les listes de module.
C’est bien ça ?

 

[jeff1494]

Exact.
Le but est pour moi d'avoir une liste des différents éléments constitutifs de l'environnement direct du projet VBA.
Je suis plus habitué à avoir sous les yeux un document papier plutôt que de chercher sur l'ordi.

Vieille école, avec son lot d'habitudes.

 

[patricktoulon]

Je pourrais développer ton complément en partant de cette idée. Avec XLAM, vous pourrez l'utiliser sur tous vos projets sans avoir à ajouter les modules à chaque fois.

 

[jeff1494]

Alors là Cela serait Noël avant Noël 🎁🎉🍾

Une offre si gentille ne peut pas se refuser. Et de plus cela m'initiera à la création de XLAM.
D'autant plus que je pourrais avoir d'autres envies, comme celle de pouvoir sélectionner certaines feuilles contenant le résultat de mes bricolages et de pouvoir les exporter au format PDF (1 Feuille par tableau structuré) par exemple.
Je suis entrain de voir comment faire.
Je part du principe d'avoir un formulaire avec une ListBox à choix multiples, puis après clic sur un bouton "Exporter" lancer une boucle qui ferait l'export pour chaque sélection de la ListBox.
Je vais réfléchir à créer cette possibilité puis de l'inclure dans ton XLAM par exemple.

Dans tous les cas un très grand merci pour ton aide.
Bonne journée à toi.

 

[patricktoulon]

@jeff1494
re:
Avant tout regarde si le résultat obtenu correspond ca que tu souhaites
J’ai ajouté provisoirement sur un exemplaire de ton fichier 2 boutons enregistrés ou imprimer les feuilles.
J’ai fait un peu de mises en page, ajouter des sauts de pages par-ci par-là ,adapter le zoom pour que ça ne soit pas trop petit.
etc..etc..

 

[jeff1494]

Merci pour ton effort.
Je vais regarder tout cela ce soir, et je te tiens au courant dès demain matin.
Bonne soirée à toi.