Fichier trop lourd, trop de codes

Version imprimable

08/02/2022, 16h10
laurentdx59

1 pièce(s) jointe(s)

Fichier trop lourd, trop de codes

Bonjour à tous,

Je vous expose mon problème: j'ai créé un sorte de formulaire pour la gestion de mes réserves qui, une fois saisie, vient incrémenter une base de donnée.

Les contraintes que j'ai rencontré et mon faible niveau en VBA font que j'ai un fichier avec plein de code dans tous les sens et une utilisation pas très "friendly".

En effet, lorsque je veux par exemple supprimer les filtres de mes segments via le bouton "Supprimer les filtres", c'est hyper long. Idem quand je veux cliquer sur le bouton Effacer. (Dans mon fichier les macros que j'utilise se situent dans le module 3)

De plus, je voudrais que les formules qui sont dans les zones grisées (Ex: de la cellule B26 à C66 ou toutes les formules du pavé "identifications dossiers") ne se lancent uniquement quand on clique sur le bouton "Rechercher".

Si quelqu'un a des idées, je suis preneur :)

Merci d'avance !
11/02/2022, 19h20
mjpmjp

le problème n'est pas forcement celui que l'on croit

bonjour, je n'est pas regardé le code ou les formules. j'ai vue la base de donnée et je pense que c'est là ou se trouve ton problème.
sans devenir un spécialiste , recherche de la doc sur l' ORGANISATION d'une DB ...

1) une Data Base est constituée de plusieurs Table (on vat dire que une Table est un onglet , et donc l'ensemble des onglets constituent la Data Base)
2) chacune des Table (onglet) est une liste de donnée (spécifique et complémentaire)
3) la liste principale doit avoir le moins de colonnes possible (pour plus de rapidité)

en fait le mécanisme est le même que les listes en cascade... une sélection (dans la table 1) vat déterminer un repaire / index qui permet de rechercher des data dans les autres table ...

@+JP
11/02/2022, 22h30
EricDgn

1 pièce(s) jointe(s)

Bonjour,

La lenteur vient à mon avis des formules dans la plage B26:C66. Une proposition en attaché pour réduire le nombre de recherches.

Cordialement.
12/02/2022, 16h26
mjpmjp

1 pièce(s) jointe(s)

définir les priorités

bonjour,
j'ai essayé de regrouper les data par centre d'intérêt , mais il ce peu que des data ne soit pas a leur place , car en effet je ne connait pas leur utilité.

Pièce jointe 613567

dans le fichier , cela ce trouve dans "analyse structure data" . dans une vraie base de donnée , pour chacune des "Table" (en jaune) il y aurait des colonnes en plus (pour les relier entre elles) reprenant par exemple le numéro de commande .
on peu voir ce genre de reprise dans les "Table" "Commande_Detail" et "Travaux_Detail".

avec cette organisation , il est aisé de piocher ici et là les info dont on a besoin :D

@+JP

hello :coucou: je reviens juste pour dire que je vais préparer un exemple de relation entre les "Table" ces mécanismes s'orientent plus vers une Data Base que vers les fonctions recherche / verticale de Excel.
12/02/2022, 18h46
mjpmjp

1 pièce(s) jointe(s)

bonjour,
pour continuer cette analyse , voila une option de travail 8O

Pièce jointe 613576

on peu accéder directement au info client , commande et travaux

Client
entrée par le client nous mène à la liste de ses commandes et de là le coté financier ou le coté travaux avec le détail des travaux et la gestion de ces travaux.

Commande
on entre par les commandes en cours par exemple.

Travaux
on entre par le suivi des travaux.

finalement çà devient très simple ... :roll:

@+JP
13/02/2022, 02h26
mjpmjp

1 pièce(s) jointe(s)

relation entre les tables ... fonction Excel

bonjour,
bon , j'avoue , j'ai eu du mal avec les fonctions Excel :furieux:

Pièce jointe 613584

le problème de la fonction RECHERCHEV c'est qu'elle n'est pas souple du tout du tout !!!

j'ai du m'adapter à son mode de fonctionnement , c'est à dire par exemple que la colonne de recherche doit être la première à gauche.
j'ai bataillé aussi avec le format des cellules , nombre et texte et autre ... la solution au format c'est la fonction VALEURNOMBRE()

VALEURNOMBRE(DROITE(H12;5)&"01")

il faut savoir qu'il y a une bizarre conception des numéro de commande , par exemple : 058833 ... pourquoi le "0" du début ??
parce que les numéro des ID de détail de commande n'ont pas ce "0" : 5883301 , 5883302 ... et il y a aussi des ID qui n'ont pas de "01" et qui débute à "02" ... oups !!

la fonction DROITE(H12;5) ne garde pas le "0" du début de "058833" ce qui donne "58833" , on ajoute le rang de l'ID avec &"01" et on reformate en nombre avec VALEURNOMBRE()

RECHERCHEV(H21;C21:E48;2;FAUX)

H21 c'est notre numéro d'ID "5883301"
C21:E48 c'est la zone "data" sans les entêtes du tableau "Commande_Detail"
2 c'est la colonne 2 du tableau (description)
FAUX c'est le mode de recherche , il est très important que ce soit FAUX et non VRAI !!

SIERREUR(RECHERCHEV(H21;C21:E48;2;FAUX);"")

si par exemple on recherche l'ID "5883311" qui n'existe pas , un message d'erreur s'affiche ... pour éviter ces messages , on utilise la fonction SIERREUR().
si il y a une erreur , déclenché par la fonction RECHERCHEV() alors on écrit rien : "" :D

mais si il n'y a pas d'erreur !!! et qu'on veut écrire quelque chose , comment utiliser la fonction SI() ??

SI(SIERREUR(RECHERCHEV(VALEURNOMBRE(DROITE(H12;5)&"01");C21:E48;2;FAUX);"err")="err";VALEURNOMBRE(DROITE(H12;5)&"02");VALEURNOMBRE(DROITE(H12;5)&"01"))

la plus grosse difficulté , ici , c'est de traiter le résultat de la fonction SIERREUR() .
on a vue plus haut que en cas d'erreur , on écrit rien : "" mais ce rien empêche le bon fonctionnement de SI() , alors on vat écrire quelque chose , par exemple "err" pour erreur (çà n'a pas d'importance) 8O .
donc on teste la première partie de SI() , SI( la fonction SIERREUR() a écrit "err" ; vrai ; faux) , si c'est vrai on écrit l'ID "5883302" et si c'est faux (donc pas d'erreur) on écrit l'ID "5883301"

il est à noter que la colonne E20:E48 sert uniquement à la fonction NB.SI(E21:E48;H12) qui indique le nombre d'ID de détail de la commande

"ID ("&NB.SI(E21:E48;H12)&")"

c'est le texte de l'entête : ID (9)
E21:E48 c'est la colonne de recherche
H12 c'est le numéro de commande "058833"

voila , pour la partie "fonction" des relations entre les tables ...

j'allais oublier , toutes les données proviennent du seul fait d'avoir écrit le nom du client en I3 !!

:coucou: @+JP
13/02/2022, 10h25
mjpmjp

2 pièce(s) jointe(s)

relation entre les tables ... orienté DB

bonjour,
d'accord la réponse est un peu longue , mais il s'agit d'un vaste sujet ou se croisent plusieurs méthodes :

1) les "fonctions" que nous avons vues , avec du "VBE" qui s'oriente vers les fonctions , usage "normal" de Excel

2) le "VBE" qui s'oriente vers les fichiers , usage "orienté DB" sans être un usage "Access" ,
il est question , ici , de gestion de fichier ; fichiers externes ou fichiers internes représentés par des tableaux qui ne doivent pas être triés

pour mener à bien ce dernier point , il faut peut être plus de rigueur mais cela reste abordable.

voila donc le schéma (sommaire :weird: ) des liens d'une donnée / data .

Pièce jointe 613592

ce schéma offre des possibilités beaucoup plus grandes que nos besoins ...
c'est pour cela qu'il y a des cases qui ne seront pas utilisées (BG / BD)

on reprends les mêmes tables , avec le nouveau système , bien sur on vat voir çà en détail 8O

Pièce jointe 613595

un état des lieux s'impose !!!

1) les colonnes "ligne" , ces colonnes ne sont là que pour l'exemple car elle représentent les numéro de ligne d'Excel (en réalité elles n'existent pas , tout comme les noms des tables)

2) la table "Client" n'a pas les entêtes "S / G / D / BG / BD" , dans une table de "personne" elles gèrent des arbre généalogique par exemple ou des ventes multi-niveaux avec réversion en cascade...

les 2 colonnes "CmdG" et "CmdD" relient la table "Commande" à la table "Client"
CmdG , lien n1 vers "Commande" , les numéros représentent les lignes dans la table "Commande" , pour le client "CARLIER" (3) la vraie ligne est 14 :calim2:
CmdD , dernier lien vers "Commande" , dans le cas ou le client aurait plusieurs commande (actuellement 0 puisque 1 seule commande)

3) la table "Commande" est susceptible d'utiliser toutes les entêtes "S / G / D / BG / BD" même si pour le moment "G" et "D" sont à zéro .

S , pour Supérieur / Parent , à la ligne 3 on voit que S est à 3 , ici , S pointe vers "Client" , et donc dans "Client" à la ligne 3 qui on retrouve ? ... le client "CARLIER" :D

souvenez vous le "schémas des tables" , dans le cas d'un accès "direct" à la table "Commande" ,
on peu lister les clients grace à "S"
on peu lister toutes les commandes d'un client grace à "G" et "D"
on peu lister en détail tout les ID de la table "Commande_Detail" avec entre autre "BG" et "BD"

voila pour la présentation , je vais préparer le code "VBE"
:coucou: @+ JP

2 pièce(s) jointe(s)

le code VBE

bonjour,
suite et fin de cette aventure :D
voila le résultat en forme de liste de débogage ...

Pièce jointe 613654

cette liste est en rapport avec l' image précédente qui représentait les relations orientés DB entre les tables.

Le code VBE

il faut bien comprendre que c'est un code adapté à la configuration de la page / feuille de démo 8O et que en situation réelle , chacune des table est dans un onglet dédié avec la ligne d'entête en A1 !!!
je rappelle également que LES TABLES NE DOIVENT PAS ETRE TRIER ... ELLES DOIVENT RESTER EN L'ETAT

Les déclarations des variables

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
Private LigneInfo As Double 'numéro de ligne de l'affichage des info
'----------------------------------------------------------------------------------- contenu
Private ClientNom As String 'le nom contenu dans la cellule nommé ClientNom
Private CelData As Variant 'le contenu d'une cellule
'----------------------------------------------------------------------------------- ligne
'----- Client
Const ClientTableLigneEntete As Double = 2 'ligne des entetes de la table "Client"
Private ClientLigneDansExcel As Double 'ligne Excel du nom dans table "Client"
Private ClientLigneDansTable As Double 'ligne Table du nom dans table "Client"
Private ClientLigneCmdG As Double 'premiere ligne des commande du client dans table "Commande"
Private ClientLigneCmdD As Double 'derniere ligne des commande du client dans table "Commande"
'----- Commande
Const CommandeTableLigneEntete As Double = 11 'ligne des entetes de la table "Commande"
Private CommandeLigneDansExcel As Double 'ligne Excel de la commande dans table "Commande"
Private CommandeLigneDansTable As Double 'ligne Table de la commande dans table "Commande"
Private CommandeLigneBG As Double 'premiere ligne des détail de la commande dans table "Commande_Detail"
Private CommandeLigneBD As Double 'derniere ligne des détail de la commande dans table "Commande_Detail"
'----- Commande_Detail
Const CommandeDetailTableLigneEntete As Double = 20 'ligne des entetes de la table "Commande_Detail"
Private CommandeDetailLigneDansExcel As Double 'ligne Excel des détail de la commande dans table "Commande_Detail"
Private CommandeDetailLigneDansTable As Double 'ligne Table des détail de la commande dans table "Commande_Detail"
Private CommandeDetailLigneG As Double 'ligne précédente des détail de la commande dans table "Commande_Detail"
Private CommandeDetailLigneD As Double 'ligne suivante des détail de la commande dans table "Commande_Detail"
'----------------------------------------------------------------------------------- systeme
'----- Client
Const ClientColCmdGStr As String = "L" 'la lettre de la colonne systeme CmdG
Const ClientColCmdDStr As String = "N" 'la lettre de la colonne systeme CmdG
'----- Commande
Const CommandeColBGStr As String = "R" 'la lettre de la colonne systeme BG
Const CommandeColBDStr As String = "T" 'la lettre de la colonne systeme BD
'----- Commande_Detail
Const CommandeDetailColGStr As String = "N" 'la lettre de la colonne systeme G
Const CommandeDetailColDStr As String = "P" 'la lettre de la colonne systeme D
'----------------------------------------------------------------------------------- data
'----- Client
Const ClientColNomStr As String = "P" 'la lettre de la colonne Nom
Const ClientColPrenomStr As String = "V" 'la lettre de la colonne Prénom
Const ClientColCodeMarqueStr As String = "AB" 'la lettre de la colonne CodeMarque
'----- Commande
Const CommandeColNumStr As String = "V" 'la lettre de la colonne Num (numéro de commande)
Const CommandeColCaTtcStr As String = "AB" 'la lettre de la colonne CA TTC
Const CommandeColEtatReStr As String = "AH" 'la lettre de la colonne Etat RE
'----- Commande_Detail
Const CommandeDetailColIDStr As String = "V" 'la lettre de la colonne ID
Const CommandeDetailColDescriptionStr As String = "AB" 'la lettre de la colonne Description

la sub ClienInfoCommande

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
Private Sub ClientInfoCommande()
    'efface la zone info
    Range("ZoneInfo").Value = ""
    'indique la ligne 1 de la zone data de la table Client
    ClientLigneDansTable = 1
    '----- trouver le nom dans la colonne
    'remplir la variable du nom avec la cellule nommé
    ClientNom = Range("ClientNom").Value
    'recherche
    Do
        CelData = Range(ClientColNomStr & ClientTableLigneEntete + ClientLigneDansTable).Value
        'sortie
        If CelData = "" Then
            ClientLigneDansExcel = 0
            Exit Do
        End If
        If CelData = ClientNom Then
            ClientLigneDansExcel = ClientTableLigneEntete + ClientLigneDansTable
            Exit Do
        End If
        ClientLigneDansTable = ClientLigneDansTable + 1
    Loop
    'ligne affichage 1
    LigneInfo = 1
    If ClientLigneDansExcel = 0 Then
        Range("ClientNom").Offset(LigneInfo, 0).Value = " le Client n'existe pas"
        LigneInfo = LigneInfo + 1
    Else
        Range("ClientNom").Offset(LigneInfo, 0).Value = " le Client se trouve à la ligne :  " & ClientLigneDansExcel & "  de la feuille Excel"
        LigneInfo = LigneInfo + 1
        Range("ClientNom").Offset(LigneInfo, 0).Value = " le Client se trouve à la ligne :  " & ClientLigneDansTable & "  de la table Client"
        LigneInfo = LigneInfo + 1
        Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- Nom du client :  " & Range(ClientColNomStr & ClientLigneDansExcel).Value & " " & Range(ClientColPrenomStr & ClientLigneDansExcel).Value
        LigneInfo = LigneInfo + 1
        Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- Code Marque :  " & Range(ClientColCodeMarqueStr & ClientLigneDansExcel).Value
        LigneInfo = LigneInfo + 1
        'ligne premiere commande dans la table "Commande"
        ClientLigneCmdG = Range(ClientColCmdGStr & ClientLigneDansExcel).Value
        'ligne derniere commande dans la table "Commande"
        ClientLigneCmdD = Range(ClientColCmdDStr & ClientLigneDansExcel).Value
        'si > 0 il y a une commande
        If ClientLigneCmdG > 0 Then
            'ligne excel de la premiere commande
            CommandeLigneDansExcel = CommandeTableLigneEntete + ClientLigneCmdG
            If ClientLigneCmdD > 0 Then
                Range("ClientNom").Offset(LigneInfo, 0).Value = " il y a plusieurs commandes :  "
                LigneInfo = LigneInfo + 1
            Else
                Range("ClientNom").Offset(LigneInfo, 0).Value = " il n'y a qu'une commande ..."
                LigneInfo = LigneInfo + 1
                Range("ClientNom").Offset(LigneInfo, 0).Value = "ligne :  " & CommandeLigneDansExcel & "  de la feuille Excel"
                LigneInfo = LigneInfo + 1
                Range("ClientNom").Offset(LigneInfo, 0).Value = "ligne :  " & ClientLigneCmdG & "  de la table Commande"
                LigneInfo = LigneInfo + 1
                Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- Numéro de commande :  " & Range(CommandeColNumStr & CommandeLigneDansExcel).Value
                LigneInfo = LigneInfo + 1
                Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- CA TTC :  " & Range(CommandeColCaTtcStr & CommandeLigneDansExcel).Value
                LigneInfo = LigneInfo + 1
                Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- Etat RE :  " & Range(CommandeColEtatReStr & CommandeLigneDansExcel).Value
                LigneInfo = LigneInfo + 1
                'premiere ligne des détail de la commande dans table "Commande_Detail"
                CommandeLigneBG = Range(CommandeColBGStr & CommandeLigneDansExcel).Value
                'derniere ligne des détail de la commande dans table "Commande_Detail"
                CommandeLigneBD = Range(CommandeColBDStr & CommandeLigneDansExcel).Value
                'si > 0 il y a des détail
                If CommandeLigneBG > 0 Then
                    Range("ClientNom").Offset(LigneInfo, 0).Value = " des détail de la commande sont disponibles..."
                    LigneInfo = LigneInfo + 1
                    'ligne excel de la premiere ligne des détail de la commande
                    CommandeDetailLigneDansExcel = CommandeDetailTableLigneEntete + CommandeLigneBG
                    Range("ClientNom").Offset(LigneInfo, 0).Value = "ligne 1 :  " & CommandeDetailLigneDansExcel & "  de la feuille Excel"
                    LigneInfo = LigneInfo + 1
                    Range("ClientNom").Offset(LigneInfo, 0).Value = "ligne 1 :  " & CommandeLigneBG & "  de la table Commande_Detail"
                    LigneInfo = LigneInfo + 1
                    Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- ID :  " & Range(CommandeDetailColIDStr & CommandeDetailLigneDansExcel).Value
                    LigneInfo = LigneInfo + 1
                    Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- Description :  " & Range(CommandeDetailColDescriptionStr & CommandeDetailLigneDansExcel).Value
                    LigneInfo = LigneInfo + 1
                    'boucle de lecture de tous les ID
                    Do
                        'sortie
                        CommandeDetailLigneD = Range(CommandeDetailColDStr & CommandeDetailLigneDansExcel).Value
                        If CommandeDetailLigneD = 0 Then
                            Exit Do
                        End If
                        'calcul de la ligne suivante
                        CommandeDetailLigneDansExcel = CommandeDetailTableLigneEntete + CommandeDetailLigneD
                        Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- ID :  " & Range(CommandeDetailColIDStr & CommandeDetailLigneDansExcel).Value
                        LigneInfo = LigneInfo + 1
                        Range("ClientNom").Offset(LigneInfo, 0).Value = " ----- Description :  " & Range(CommandeDetailColDescriptionStr & CommandeDetailLigneDansExcel).Value
                        LigneInfo = LigneInfo + 1
                    Loop
                End If
            End If
        End If
    End If
End Sub

il y a pas mal de lignes , mais c'est une démo et de plus, les explications sont en commentaire dans le code

:coucou: @+ JP