Besoin aide VBA

[Spinzi]

Bonjour à tous,

étant toujours en quête d'optimisation et de rapidité, mais cependant nul en macro/VBA, je viens vers vous pour la construction d'un fichier que j'avais réussi avec des formules, mais qui s'avère légèrement long à l'éxécution (3-4 minutes).

Je pense procéder par phases (j'ai différentes bases mais surtout un gros fichier à optimiser).

J'ai déjà commencé par tout mettre en tableaux excel car j'ai lu plusieurs fois que les tableaux accéléraient la vitesse des programmes VBA.

Dans un premier temps je souhaite donc modifier le code VBA présent dans le fichier ci dessous qui permet d'alimenter les colonnes A, B et C avec des informations sans doublon.
Ces colonnes sans doublons serviront plus tard pour alimenter plusieurs listes de validation.

J'aimerai donc une modification du code pour prendre en compte le tableau qui est dynamique et qui est donc ajusté pile poil à la taille du tableau et donc des données : pas besoin de vérifier des milliers de lignes pour rien.

Si vous avez des questions, n'hésitez pas et merci d'avance pour les améliorations que vous pourriez apporter à ce fichier.

 

[Spinzi]

Re : Besoin aide VBA

Bonjour Dranreb,

pas de langue de bois avec moi ! Si vous trouvez que c'est louche et pas optimisé, il faut me le dire ! Je ne prétends surement pas avoir les meilleures solutions, surtout quand je demande de l'aide.

J'avais mis les listes a gauche de ma table car j'avais adapté un code vba trouvé sur le net et c'était un des plus simples à modifier ! Cependant il y a juste ces trois colonnes que je peux bouger, le reste de la base (a partir de la colonne D donc) est un copié/collé d'une base existante (surement améliorable avec au lieu d'un copié/collé une importation directe, mais je préfererait d'abord une premiere version de la macro qui liste les résultats suivant les arguments des listes de validation).

Bien à vous

ps : si vous voulez je peux vous faire part d'un exemple qui résumerait exactement ce que je demande. Je ne sais pas si en terme de faisabilité c'est possible, mais cela vous permettrait d'avoir une vision globale de ce que je souhaite, car on pourrait dire que l'étape de lister les résultats est la 2eme sur 5 requete on va dire. J'espere m'etre fait comprendre =)
Et encore désolé de ne pouvoir vous apporter mon aide sur le fichier à éditer.

 

[Dranreb]

Re : Besoin aide VBA

Bonjour.
C'est certain, une vision globale de ce que vous souhaitez m'intéresse.
Et l'utilité d'un Userforme se précise. Une ListBox pourrait instantanément y montrer les lignes correspondant aux choix effectués dans 3 ComboBox, et plus besoin des plages de listes, ce qui règlerait la question du lieu où les mettre.

 

[Spinzi]

Re : Besoin aide VBA

Me revoila, avec une ébauche de ce que je souhaiterai au final.
Dur de tout détailler, mais ça passer par des boutons macro pour intégrer des fichiers, editer des onglets dasn des classeurs différents, des choses somme toute simple à faire en vba je pense, mais dont je n'ai aucune idée de fonctionnement.

J'ai séparé la feuille Onglet programme du reste du fichier car il dépassait la taille, mais cet onglet devrait être dans le même fichier, en tant que premier onglet.

Je reste à ton entière disposition et dis moi si cela te parait long (je pense que oui d'ailleurs).

Merci beaucoup

 

[Dranreb]

Re : Besoin aide VBA

C'est quoi ces formule Index equiv prévues ? Les dictionnaires arborescents à la base de mes modules de classes sont conçus pour permettre de rapporter facilement les informations associées aux choix. Vous n'allez par refaire des recherches d'informations à postériori, quand même !?

 

[Spinzi]

Re : Besoin aide VBA

Je me doutais de ça, ce pourquoi j'ai fais apparaitre ce que j'allais utiliser =)
De fait, ne pas prendre en compte le "index equiv". Si on peut alimenter tous les tableaux, pas de soucis.
D'ailleurs, les dictionnaires arborescents rapportent seulement les informations de la "base en cours" ou bien ils permettent de rechercher dans tous les onglets ?

M'enfin pour l'instant je pense que le plus important reste le fichier budget à alimenter (je suis surtout presser de voir la vitesse d'execution de la macro pour ramener les résultats.
Pour l'instant je vous laisse le choix qui pour vous et le plus optimisant quant à l'utilisation ou non d'une combobox. Et je vous ferai mon retour =)

 

[Dranreb]

Re : Besoin aide VBA

D'ailleurs, les dictionnaires arborescents rapportent seulement les informations de la "base en cours" ou bien ils permettent de rechercher dans tous les onglets ?

Les dictionnaires arborescents peuvent être fabriqué à partir de n'importe quoi d'un seul tenant spécifié à une fonction DictionnArbo qui vous le renvoie aussitôt pour que vous puissiez l'affecter par un Set à une variable déclarée As Dictionary. Mais aussi par un de mes 3 modules de classes, en leurs spécifiant d'abord à une méthode Plage l'ensemble des lignes couverte par un tableau où certaines colonnes y sont en corrélation, puis chacune des colonnes en question à une méthode Add. Ce sont les mêmes noms de méthodes pour ListesLiées, ComboBoxCasc et ComboBoxLiés. Sauf que pour les deux dernier il n'y a que 2 arguments: le ComboBox et la colonne vu q'un Combobox possède à la fois la List et une Text qui sert d'argument. Par ailleurs ils décrètent des évènements à l'intention de l'UserForm. Il le faut bien pour que la liste des lignes lui soit communiquée dès qu'un choix est effectué. Voulez vous un essai en envoyant les lignes en question dans une ListBox plutôt que dans une feuille ? Ne voudriez vous pas dessiner l'Userform vous même d'abord ?

En attendant je joins un dernier fichier avec les macros à jour (ce qui ne me semble pas toujours être le cas des classeur que vous me renvoyez) et un essai de récupération des lignes dans la feuille. Mais je crois qu'il faut quand même abandonner cette voie. Mes prochaines production s'appelleront probablement CbxLiésSpinzi. Mais vous verrez la programmation sera très voisine.

 

[Spinzi]

Re : Besoin aide VBA

Bonjour Dranreb,

je vais essayer d'éditer un userform (ce sera mon premier!) doncje ne vous promets rien.

Pour faire un suivi du fichier posté, c'est ce que je recherche ! J'aimerai juste qu'il fasse apparaitre :
_la colonne responsable est pour l'instant en 'L' or j'aimerai qu'elle se trouve en 'D'.
_que la macro renvoie les valeurs correspondantes aux libellés : si en 'F2' j'ai la mention "Libellés", alors il va récupérer cette valeur dans la base et pour toutes les lignes (les libellés de colonne seront strictement les mêmes entre la base de données et le fichier de récupération)
_dans tous les cas, pourriez vous m'indiquer dans quel module apporter ces améliorations pour que dans l'avenir j'essaie de modifier directement la macro (même si ce n'est pas forcément conseillé pour l'instant aux vus de mes capacités, je risque plus de détruire votre travail qu'autr chose mais cela me permettrai de comprendre)
_la base sera plus riche, et j'aimerai que la macro effectue un total puis un saut de ligne entre chaque centre de couts (voir test en pj pour le résultat attendu).

Merci pour le travail fourni !

 

[Dranreb]

Re : Besoin aide VBA

Bonjour.

_dans tous les cas, pourriez vous m'indiquer dans quel module apporter ces amélioration

Ça se passe dans la procédure Worksheet_Change de Feuil5 (Budget) essentiellement au niveau de la boucle For C = 1 To UBound(Te, 2): Ts(Ls, C) = Te(Le, C): Next C qu'il y a sans doute lieu de remplacer par trois affectations de certaines colonnes spécifiques de Te vers Ts, ce dernier étant préalablement redimensionné à 3 colonnes. Mais ce sera à l'avenir dans une ListBox, plus dans la feuille.
Alors le rapport par département et par centre de coûts c'est un autre problème. Un DictionnArbo([BasDéptmt],[BasCCoûts]) pourrait bien vous aider. Il serait même possible de le filtrer selon la table des lignes retenues (Function DicoFiltré). Travaillez toujours sur des tableaux de Variant, jamais directement sur les cellules. Tour à la fin seulement, ça, en une seule fois.

 

[Spinzi]

Re : Besoin aide VBA

Bonjour,

impossible pour moi de créer cet userfrom tant espéré : j'arrive bien à ouvrir la fenêtre, mais pour y intégrer par exemple une liste avec des données qui apparaissent, je n'y arrive pas, donc pas grand intérêt.
De plus j'ai repensé à la solution avec affichage d'une liste de résultat dans une combobox (c'est bie nde ça dont vous parliez ?) le problème est de pouvoir par la suite réutiliser les résultats obtenus : je vois pas l'intêret de les avoir dans une fenêtre à part alors que je vais retraiter les informations après notamment pour savoir si je fais apparaitre cette colonne ou non.
J'ai peur de ne pas vous comprendre : pourriez vous illustrer vos propos avec des imprim ecran d'autre fichier n'importe, juste que je puise voir ce à quoi vous pensez car je n'arrive pas à faire le lien.

Merci

 

[Dranreb]

Re : Besoin aide VBA

Bonjour.
Ce n'est pas parce que vous ne savez pas encore faire que ça n'a pas grand intérêt.
Non je ne parlais pas d'afficher des résultats dans une ComboBox, mais les listes de choix possibles.
Il suffit pour cela d'affecter à sa propriété List la propriété Keys d'un Dictionary convenablement élaboré.
Ah mais moi non plus je ne peux pas voir l'intérêt d'afficher les listes résultantes.
Je ne comprends pas par exemple pourquoi vous ne feriez pas tout simplement une liste avec totaux de tout ce qu'il y a, quitte à en transférer par la suite certains chapitres dans des feuilles séparées.

 

[Spinzi]

Re : Besoin aide VBA

Bonjour,

en fait je disais "pas grand intêret" mais pour le mettre en pj, pas pour ce que ça apporte !
Ce fichier doit me permettre d'afficher des informations issues de la base de données suivant les choix des listes de validation. Je vais en faire 1 par service (Finances et gestion, mkt, etc). Ces fichiers seront ensuite envoyés aux managers qui devront les compléter.

Le choix par liste de validation directement sur la feuille n'a donc rien d'handicapant. Je dois juste etre en mesure, apres modification de la mise en page, de transmettre ces rapports finalisés.
Dans l'idée p-e créer aussi directement 1 onglet par département en appuyant sur un bouton macro, mais je perds en autonomie car si je veux modifier des parametres je serai obligé de toucher directement à la macro, m'voyez ?

Donc pour l'affichage autant pour moi, j'avais mal interprété ce que vous aviez dit ! Encore à être sur la même longueur d'onde pour la forme du résultat j'ai l'impression : qu'entendez vous par totaux d etout ce qu'il y a et le transférer ? La bse fournie n'est pas complète, il manque une bonne centaine de colonne.
Ma base récapitule toutes les données des précedents exercices, compilées pour les années qui ne servent plus (2011, 2012) mais avec le détail des différentes révisions budgétaires trimestrielles pour les plus récentes (2013 V0, 2013 V1, 2013 V2, etc).
Dans le fichier budget, je souhaite faire apparaitre les colonnes de mon choix, généralement Année N-1 puis révision N V0 et révision N V-1. Exemple si je suis en train de faire la révision 3 : 2012 + 2013 V0 + 2013 V2.
Il faut ensuite que je mette à disposition des colonnes vides correspondants au différentes natures comptables qui vont être imputées pour la révision 2013 V3, ex : Frais de déplacement, Prestations externalisées, Telecom, le budget et alors réparti suivant ces natures. Cependant il ne faut pas que je fasse apparaitre les mêmes natures pour chaque départements ... il me faut donc garder bcp d'autonomie sur ce fichier et non pas qu'il soit trop automatisé, car le jour ou les criteres changeront, je serai bien embêté.

J'espere vous avoir un tantinet éclairé.

 

[Dranreb]

Re : Besoin aide VBA

Pas vraiment.
Mais c'est sûr que si vous devez établir un rapport selon ce qui est sélectionné dans une ou plusieurs ComboBox, il n'est pas utile d'afficher en plus une liste des lignes retenues, ni dans une ListBox ni dans la feuille.
À vous de voir pour l'établissement d'un rapport pur et simple de tout ce qu'il y a, et son éclatement automatique par département à raison d'un onglet, voir d'un classeur pour chaque.
J'ai écrit tout récemment un code qui devrait permettre d'imbriquer des For Each Argn In Argp.Contenu dans un global For Each Arg1 In GroupOrg(Range, Liste des colonnes)
Il n'utilise pas de Dictionary, mais il n'a jamais été testé.

 

[Spinzi]

Re : Besoin aide VBA

J'ai écrit tout récemment un code qui devrait permettre d'imbriquer des For Each Argn In Argp.Contenu dans un global For Each Arg1 In GroupOrg(Range, Liste des colonnes)
 Il n'utilise pas de Dictionary, mais il n'a jamais été testé.

^^

Je vais essayer de me pencher un peu plus sur la macro pour faire apparaitre au moins les colonnes que je désire pour l'instant (pour l'instant ce sont, dans l'ordre, les colonnes Département, nom de centre de cout, Code budgétaire/Ordre = la même chose, libellé, destination1 et destination 2).
Cela me permettrai d'avoir un début de base sur le quel travailler, au mois pour la mise en page.

Merci

 

[Dranreb]

Re : Besoin aide VBA

Soit.
Ne vous préoccupez pas de l'ordre, classer provisoirement les données de façon adéquate, mais copiez de tableau à tableau. Lorsque vous voudrez que je mette au point mon système sur un nombre suffisant de départements et de centres de couts pour pouvoir tester, vous n'aurez qu'a me joindre votre classeur.
Pour l'instant la liste des colonnes à spécifier à ma fonction Function GroupOrg(Plg As Range, ParamArray ColOrd() As Variant) As Collection sont en fait seulement les colonnes des arguments à la fois structurels et de classement. La collection du dernier niveau reproduirait dans chacun de ses membres l'intégralité des valeurs de colonnes d'une ligne de la plage d'entrée. Chaque membre des collections de niveaux précédents, y compris celle de tout l'ensemble, aura une propriété Id, valeur de la colonne d'argument correspondante, qui pourra par exemple être reproduite dans un sous-titre et une propriété Contenu sur laquelle portera le For Each In suivant.
Mais si vous estimez qu'il serait plus pratique de pouvoir spécifier, en plus, des colonnes supplémentaires de classement, à rappatrier en premier mais ne faisant pas partie de l'organisation structurelle, puis encore, derrière, les numéros des colonnes des autres données associées à rapatrier dans les membres de la dernière collection, dites le moi. Un zéro ou une spécification non numérique pourrait servir de séparateur entre les trois catégories de spécifications de colonnes.

 

[Spinzi]

Re : Besoin aide VBA

Parce qu'en fait, je ne peux rien vous apporter en termes d'avancement sur le VBA vu que je ne comprends rien car je n'ai aucune connaissance. C'est donc génant pour moi de demander sans cesse des modifications.
Je modifie ma base avec un peu plus d'informations pour que vous ayez une ébauche à la fois de la base de données et de ce que je souhaiterai.

ps : j'a rajouté une base de données plus complète et se rapprochant le plus possible de la réalité en tenant compte des problématiques de taille de fichier ainsi que de confidentialité.

Merci