Optimisation code VBA

Question

Je souhaiterai avoir vos avis et retours d'expérience sur la façon dont organiser au mieux une macro de gestion de données. Je vous mets en PJ mon fichier épuré. Dans l'idée, j'ai créé une base de données avec plusieurs listes sur ma feuille "DONNEES" (clients, machines, opérateurs, et bien d'autres...). Cette base de données est utilisée sur d'autres classeurs. Lorsque j'accès à mon UserForm "UF_Parametres", mes ListBox se mettent à jour. Puis, via les boutons "Ajouter" ou "Supprimer", je peux modifier mes listes, et ainsi modifier ma base de données. _-_-_-_-_ Comme vous pouvez le voir dans ma macro, j'ai défini des variables Clients/Machines/Opérateurs Dim iCli As Integer, iCliMax As Integer, NouvCli As String Dim iMac As Integer, iMacMax As Integer, NouvMac As String Dim iOpe As Integer, iOpeMax As Integer, NouvOpe As String Si je veux ajouter un nouveau client, j'ai déjà écrit ça : Private Sub Bt_Ajouter_Client_Click() 'Saisie d'un nouveau client NouvCli = InputBox("Saisissez le nouveau client à ajouter dans la base de données (vérifiez l'orthographe avant de valider) :", "Ajout nouveau client") 'Demande de confirmation avant ajout If NouvCli &lt;&gt; "" Then 'Ecrire en majuscule NouvCli = UCase(NouvCli) If MsgBox("Vous êtes sur le point d'ajouter """ & NouvCli & """ comme nouveau client dans la base de données. Voulez-vous continuer ?", vbYesNo, "Confirmation ajout") = vbYes Then 'Test si le client existe déjà For iCli = 1 To iCliMax If ParamDonnees.Cells(iCli, 8) = NouvCli Then 'Avertir client déjà dans la liste MsgBox "Le client que vous voulez ajouter existe déjà dans la base de données !" 'Sortir de la procédure Exit Sub End If Next 'Ajout du client dans la base de données ParamDonnees.Cells(iCliMax + 1, 8) = NouvCli 'Mettre à jour iCliMax iCliMax = ParamDonnees.Cells(2, 7) 'Mise à jour de la liste dans l'ordre alphabétique ParamDonnees.Sort.SortFields.Clear ParamDonnees.Sort.SortFields.Add2 Key:=Range(Cells(1, 8), Cells(iCliMax, 8) _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ParamDonnees.Sort .SetRange Range(Cells(1, 8), Cells(iCliMax, 8)) .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With 'Vider la liste clients LB_Clients.Clear 'Et mettre à jour la liste For iCli = 1 To iCliMax LB_Clients.AddItem ParamDonnees.Cells(iCli, 8) Next End If End If End Sub Et pour en supprimer, ça : Private Sub Bt_Supprimer_Clients_Click() 'Vérif' si client sélectionné If IsNull(LB_Clients.Value) = True Then Exit Sub 'Sortir de la procédure 'Demande de confirmation avant suppression If MsgBox("Voulez-vous vraiment supprimer """ & LB_Clients.Value & """ de la liste des clients ?", vbYesNo, "Confirmation suppression") = vbYes Then iCli = 1 'Chercher le client à supprimer dans la base de données clients Do While LB_Clients.Value &lt;&gt; ParamDonnees.Cells(iCli, 8) iCli = iCli + 1 Loop 'Supprimer la cellule du client choisi dans la base de données ParamDonnees.Cells(iCli, 8).Delete Shift:=xlUp 'Mettre à jour iCliMax iCliMax = ParamDonnees.Cells(2, 7) 'Vider la liste clients LB_Clients.Clear 'Et mettre à jour la liste For iCli = 1 To iCliMax LB_Clients.AddItem ParamDonnees.Cells(iCli, 8) Next End If End Sub _-_-_-_-_ Ma question : Si je veux faire la même chose pour les machines, est-ce qu'il vaut mieux copier/coller les 2 procédures (en modifiant les variables iCli --&gt; iMac, iCliMax --&gt; iMacMax, etc... Ainsi que les textes des Msgbox) ? C'est-à-dire recopier autant de fois le code, que j'ai de liste à traiter ? Ou alors, serait-ce plus "intelligent" de faire un module en écrivant une seule fois ces 2 procédures , mais en paramétrant encore plus de variables ? _-_-_-_-_ J'étais parti pour tout recopier. un peu fastidieux, certes, mais plus simple. Mais pour éviter d'avoir 15 000 lignes de code, je sais que je peux optimiser tout ça. MAIS ça implique de faire des ajustements... Par exemple, rien que pour les Msgbox, cela veut dire que je devrai créer de nouvelles variables pour que le texte affiché soit cohérent. Exemple : If MsgBox("Voulez-vous vraiment supprimer """ & LB_Clients.Value & """ de la liste des clients ?", vbYesNo, "Confirmation suppression") = vbYes Then Il faudrait que j'écrive : Dim InfoMsgbox as string InfoMsgbox = "Clients" If MsgBox("Voulez-vous vraiment supprimer """ & LB_Clients.Value & """ de la liste des " & InfoMsgbox & " ?", vbYesNo, "Confirmation suppression") = vbYes Then ... Vous voyez où je veux en venir ? Pour vous, c'est mieux (vos arguments) de paramétrer à fond, et donc de réduire la taille de la macro (rapidité d'exécution ? sources d'erreur ? ...) ? Ou cela ne vaut pas le coup de se casser la tête ? PS : le forum a modifié le nom de mon fichier ... Créant une erreur à l'ouverture. Je l'ai remis à jour ! Je vous remercie pour votre aide ! Bonne journée, Matthieu...

LouReeD · Accepted Answer

Voici ce que j'en pense : Si dans un code il se trouve qu'une routine se répète plusieurs fois alors il est de bon ton d'essayer de la rendre fonctionnelle par des variables afin d'éviter une répétition de code qui lors d'un changement va entrainer de multiple modifications. Dans votre cas, si les clients, les machines et opérateurs seront toujours "gérer" de la même façon, alors pourquoi pas : Un tableau "String" comportant les différent morceaux de texte du MsgBox et en paramètre de la fonction "affichage" les différentes valeur d'appel : Function Affichage( AjoutQuoi as String, AjoutOù as Integer, AjoutType as Integer, CtrlAjout as String) As Boolean où AjoutQuoi = le nom du client ou de la machine ou de l'opérateur où AjoutOù = numéro de colonne de la liste ( la colonne qui sert à stoker le nombre de donnée sera égale à AjoutOù -1 où AjoutType= liste des données du tableau texte avec les différents morceaux (0 = texte pour clients, 1 = texte pour les machines, 2 = texte pour les opérateurs) où CtrlAjout = nom du contrôle de la liste ( LB_Clients pour la liste clients et pour pouvoir travailler dessus en "variable" =&gt; Controls("LB_Clients").Clear donc en code VBA =&gt; Controls(CtrlAjout).Clear La fonction est booléenne afin de retourner "Faux" si la moindre décision d'annulation et VRAI si tu se passe bien. Le Tableau String pourrait être rempli comme ceci : Tableau(0,0) ="Vous êtes sur le point d'ajouter " Tableau(0,1) =" comme nouveau client dans la base de données. Voulez-vous continuer ?" Tableau(0,2) ="Le client que vous voulez ajouter existe déjà dans la base de données !" puis : Tableau(1,0) ="Vous êtes sur le point d'ajouter " Tableau(1,1) =" comme nouvelle machine dans la base de données. Voulez-vous continuer ?" Tableau(1,2) ="La machine que vous voulez ajouter existe déjà dans la base de données !" Mais comme dit plus haut il ne faut pas que la gestion des trois types de données évolue, sinon l'adaptation deviendra vite (si ce n'est déjà le cas) complexe. Ensuite pour y faire appel, exemple sur le bouton Client : Private Sub Bt_Ajouter_Client_Click() Dim Résultat 'Saisie d'un nouveau client NouvCli = InputBox("Saisissez le nouveau client à ajouter dans la base de données (vérifiez l'orthographe avant de valider) :", "Ajout nouveau client") 'Demande de confirmation avant ajout If NouvCli &lt;&gt; "" Then Résultat = Affichage(UCase(NouvCli),8,0,"LB_Clients") If Résultat Then MsgBox("Ajout effectué") Else MsgBox("Processus d'ajout abandonné") End If End Sub Après galopin01 a raison quant à la structure de votre fichier, mais il est vrai aussi qu'il parait plus simple (c'est à voir...) de garder de "vieille habitudes" plutôt que de tout reprendre correctement... @ bientôt LouReeD...

thev · Accepted Answer

Pour optimiser votre code et le rendre le plus invariant possible, commencez par utiliser des plages nommées et bornées. 1- dans votre feuille Données, nommez via le Gestionnaire de noms (menu Formule), vos 3 listes avec un bornage dynamique : client, machine, opérateur 2- dans votre formulaire, ne garder qu'une seule ListBox et ajouter une Combobox permettant de sélectionner la liste à modifier ci-joint exemple avec simplification de votre code...

galopin01 · Answer

Perso, il ne viendrai pas à l'idée d'organiser ma feuille de paramètres comme ça. Inutile de chercher à réinventer l'eau chaude : On trouve des quantités de modèle de gestion de base de données. Pour les paramètres de simples listes suffisent : Pas besoin de usf pour les paramètres. A+...

galopin01 · Answer

CRUD est un acronyme qui recouvre un concept général à tous les langages.

La philosophie générale étant de regrouper dans le même usf toutes les opérations de gestion d'une base de donnée.

Celui que tu cites en est une minuscule variante adaptés à certains cas (gestion d'un tableau structuré utilisée comme une table de BD)

Les bases de données pouvant couvrir de nombreuses réalités il en existe de nombreuses déclinaisons dans tous les langages et dans des domaines très variés.

En guise d'exemple je te renvoie plutôt cela à cette explication.

Après je ne sais pas par quel bout prendre la suite de tes explications.

qu'est-ce qui vous motive à créer par exemple un module, auquel on ferait appel plusieurs fois ?

Un module étant un ramassis bien souvent disparate de plusieurs macros sans nécessairement de lien entre elles, je ne commenterait pas plus cette question.

Pour ma part mes modules sont souvent créé par destination : Leur appellation est sans équivoque et leur utilité très transversale par rapport aux diverses feuilles potentielles...

Ils peuvent être : module main, prg, function, impress, util, graph, report, calend, ribbon...

Après les macros elles même, correspondent à un concept de généralisation.

Une macro read suffit à couvrir tous les besoins de lecture de données que l'on soit en mode recherche, consultation, navigation.

Une macro write suffit à couvrir tous les besoins d'écriture création ou modification.

Après une macro InitVar, InitData ou InitCombo doit pouvoir être exécuté aux divers moment opportun du programme sans inconvénient. Et pas uniquement au moment du Initialize.

De même une fonction ValidSaisie me semble pouvoir être interrogée sur tous les contrôles avant le transfert définitif, sans qu'il soit nécessaire de pondre un paragraphe à la sortie de chaque contrôle... Quand tu vas sur internet faire un achat, si tu as oublié de remplir une case ou si elle est pal remplie, tu as rarement un msgbox à chaque fois qu'il y a un problème : Le plus souvent c'est à la validation qu'on te renvoie sur le contrôle problématique avec un msg général...

Enfin bref...

Sur ton fichier, ben je ne n'y trouve pas mon compte donc je ne commenterai pas plus. Déjà on ne parle pas le même langage : base de donnée en Excel, me parait un peu nébuleux donc j'ai surtout retenu le mot liste, rattaché à ton UF_Paramètres qui me parlait un peu plus. C'est vrai que tes listes me paraissait un peu zarbi, mébon je n'ai pas approfondi plus que ça...

Vu l'aspect de cette feuille, sur la conception d'un tableau ou d'une tablz je te renverrai bien ici...

http://www.xlerateur.com/divers/2010/05/14/les-13-regles-d%E2%80%99or-pour-utiliser-excel-comme-gest...

Bon je ne vais pas te faire un roman hein... Je sais que la plupart de mes interlocuteurs prennent souvent fort mal mes observations. Alors je me limite...

Mais après sur les concept d'optimisation, gain de temps il existe déjà de très bons auteurs mais je ne refuse pas d'en rediscuter éventuellement...

EDIT : Parce que en me relisant, bien sur que la rapidité d'exécution (mais pas que...) est essentielle. La longueur du code aussi est importante : Trop souvent on voit des gars qui se lancent sur 2000 ou 3000 lignes de code et qui se retrouvent avec un message du compilateur leur demandant d'arrêter les frais...

Donc oui, il y a des règles qu'on ne rabâche pas à tout bout de champ, oui il y a des array, des dico, des modules de classe...

A+

AMIR · Answer

Salut galopin01, LouReeD et Matthieu.H Alors c'est vrais que tu peux faire ça avec les plus courtes et complexes procédures possibles mais des fois il serait plus facile et compréhensible de le faire avec plusieurs petites procédures simples mais bien lisibles a tous le monde Bien que cet exemple est une procédure plus court mais il devient plus complexe : Private Sub ComboBox1_Change() FrClients.Caption = ComboBox1.Value LB_Clients === liste de FrClients.Caption If MsgBox("Voulez-vous vraiment supprimer """ _ & LB_Clients.Value & """ de la liste des " & FrClients.Caption & "?", vbYesNo, "Confirmation suppression") = vbYes Then ............................... End Sub Private Sub UserForm_Initialize() ComboBox1.AddItem "Client" 'ListIndex = 0 ComboBox1.AddItem "Machine" ...............................

eriiic · Answer

Je n'ai pas osé ouvrir ton fichier Pour info, tu peux passer des paramètres à un Sub, qui deviennent des variables utilisables comme tu veux. Ex : Sub appel() maProc 8, "client" End Sub Sub maProc(param1 As Long, param2 As String) MsgBox param1 & "/" & param2 End Sub eric...