Besoin aide VBA

Spinzi

XLDnaute Impliqué
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.
 

Pièces jointes

  • TravauxHercule.xlsm
    450.8 KB · Affichages: 195
  • TravauxHercule.xlsm
    450.8 KB · Affichages: 199
  • TravauxHercule.xlsm
    450.8 KB · Affichages: 204
Dernière édition:

pedrag31

XLDnaute Occasionnel
Re : Besoin aide VBA

Bonjour Spinzi, le forum,

Il est très difficile de comprendre ce que tu veux vraiment faire dans la mesure où la macro que tu as dans ce fichier fait déjà (théoriquement) des listes "dept, "centre" et "resp" sans doublons grâce à l'utilisation de "scripting.dictionary"...

Qu'est ce que tu souhaites accélérer?
La création des listes sans doublons?

La création des listes avec cette méthode est rapide à priori, on pourrait essayer autre chose mais ce serait des temps équivalents je pense. Est-ce que les filtres auto d'Excel ne seraient plus adaptés?

Quelle est la finalité de ces listes sans doublons?
Que souhaites-tu en faire ensuite?

VB:
        For Each k In dept
            .Range("A" & Rows.Count).End(xlUp).Offset(1) = k
        Next k
        
        For Each k In centre
            .Range("B" & Rows.Count).End(xlUp).Offset(1) = k
        Next k
        
        For Each k In resp
            .Range("C" & Rows.Count).End(xlUp).Offset(1) = k
        Next k

Ces trois boucles séparées (et complexes) d'écriture ralentissent surement ton exécution mais il faudrait savoir où on peut économiser du temps de traitement en connaissant la finalité...

Également, la macro est sur un évènement Worksheet_Change donc elle se déclenche intempestivement à chaque changement de valeur dans B1, C1 et D1... Peut-être que tu pourrais éviter cela?

Enfin, pour que tu puisses vraiment optimiser, je pense qu'il est nécessaire que tu comprennes ce que les macros font (dans le détail) et donc que tu codes autant que possible la fonction que tu veux réaliser. Comme cela, tu verras vite où se trouvent les boucles de traitement inutiles et comment tu peux optimiser.

Sur le forum, les XLDnautes pourront toujours te filer un coup de main pour avancer sur tes blocages mais pas faire tout le travail pour toi, tu le comprends bien...;)

Bonne journée :)
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour pedrag,

tout d'abord merci pour ta réponse.
Cette macro fonctionne très bien effctivement, je souhaiterai juste qu'elle se base maintenant sur les données formatées sous forme de tableau. D'après ce que j'ai compris ça irait plus vite ...

Mon but final est de pouvoir utiliser une formule qui existe déjà sous forme de tableau car lorsque j'essaei de apsser en mode tableau, un message d'erreur apparait comme quoi il ne peut insérer une matricielle sur plusiurs cellules dans un tableau.

Je voulais juste commencer par le début pour pouvoir reprendre ma base de données qui n'était pas définie en tableau, sur de bonnes bases.

Merci d'avance
 

pedrag31

XLDnaute Occasionnel
Re : Besoin aide VBA

Re,

Je ne comprends toujours pas ce que tu veux faire au final...
Postes un fichier avec le résultat que tu veux obtenir, des exemples et des commentaires sur ce que tu imagines obtenir et les étapes par lesquelles tu veux passer.

Je ne te sens pas vraiment impliqué dans la démarche d'optimisation de la macro avec cette réponse...
Bonne journée :)
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Re,

je ne suis pas impliqué parce que je ne m'y connais pas en macro, tout simplement. J'ai essay de chercher sur la toile mais les seuls tableaux utilisés sont les arrays. Or dans Excel on peut définir un tableau "Insertion => Tableau". J'aimerai savoir si on peut utiliser les données stockées dans ce tableau pour faire la meme chose que ma macro précédente (enfin ma, la macro que j'ai essayé de modifier).

Je souhaite donc obtenir la même chose, seulement au lieu d'utiliser des donées en "vrac" me servir de celles que j'ai rangé en tableau (puisque les tableaux sont dynamiques, recopie des formules dans toute la colonne et bien d'autres avantages encore).

Le résultat est donc le même que celui fourni dans mon post 1, j'aimerai juste intégrer je ne sais qu'elle dimension pour que la macro se base mon tableau et non sur mes données non formatées.

Si ce n'est toujours pas clair, n'hésitez pas à me demander d'être plus exhaustif.

Merci d'avance et bonne journée =)
 

pedrag31

XLDnaute Occasionnel
Re : Besoin aide VBA

Bonjour Spinzi, le forum,

Je pense que ce que tu as lu sur la toile sur les tableaux (exemple les "arrays" que tu mentionnes) sont des tableaux "mémoire" générés et gérés par le code VBA de ta macro. Ces tableaux sont gardés en mémoire vive pendant le traitement des données par la macro donc si un traitement lourd est nécessaire, il se fera bcp plus rapidement car en mémoire vive.
Mais toutefois, ces tableaux n'existent que pendant l’exécution de la macro, il faut les créer quand tu lances ton code et les mettre à jour si les données à traiter viennent à évoluer.

Donc l'utilisation de tableau Excel "Insertion/Tableau" n'apportera pas d'amélioration sur la rapidité du traitement, je pense.

A voir donc ce que tu souhaites faire au final pour qu'on puisse t'aider à optimiser, quelle est la taille des tableaux de données que tu vas avoir à gérer, quelle est l'application que tu veux de ces tableaux sans doublons pour que la macro soit optimisée au mieux pour ton utilisation.

Je pense par exemple que si tu gardes les listes en mémoire au lieu de les écrire dans la feuille1, tu va s gagner du temps de traitement... Mais, encore ça dépend de ce que tu veux faire ensuite avec ces listes.

Bonne journée :)
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour tout le monde,

pour commencer par le commencement, ce fichier me sert (si tu reprends mes fichiers du premier post) a répertorier tous les ordres suivant et/ou le nom d'un département, le nom d'un centre de coût, un responsable.
Ces 3 conditions sont choisies suivant une liste de validation qui repertorie les départements, centres de couts et responsable sans doublon. Ces listes de validations fonctionnent en cascade, de telle sorte que le choix soit restrictif suivant les choix précédemment effectués (cf pj).

J'ai, pour extraire les ordres suivants, une formule matricielle lourde qui s'étend sur une plage de 1800 lignes (le nombre d'ordres par département est variable, et plus les listes de validation sont renseignées moins les ordres sont nombreux car la demande est plus précise) et qui est donc longue a exécutée, sachant que je copie colle ensuite ces données sur un autre fichiers en enlevant les lignes a 0, les colonnes ou le total est égal a 0 avant de l'envoyer au département en question.

Attention la feuille n'est pas en calculs automatiques
 

Pièces jointes

  • TravauxHercule1.xlsm
    365 KB · Affichages: 148

Dranreb

XLDnaute Barbatruc
Re : Besoin aide VBA

Bonjour.
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.
Non, vous avez mal compris. Ce qui accélère la vitesse c'est de passer par des tableaux VB de Variant, ce qu'est en soit la propriété Value d'un Range portant sur une plage rectangulaire. Vous pouvez presque partir du principe que la durée d'exécution n'est proportionnelle qu'au nombre de fois que vous invoquez Range (ou Cells) et nullement au nombre de valeurs de cellules transférées à chacune de ses utilisations.
Moins vous ferez de .Range mieux se sera. L'idéal c'est de ne n'en faire que 2. Un au début pour charger toute la UsedRange.Value de la feuille et un autre à la fin pour la décharger du tableau modifié.
 
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour Dranreb,

oui c'est ceque j'ai cru comprendre avec les explications de pedrag ... et comme je ne m'y connais pas j'ai pris ce que je lisais un peu partout comme argent comptant. De fait je me suis mis à modifier mes bases en table pour ... rien.
Mais j'ai cependant quand même besoin d'une optimisation et de passer par VBA pour baisser le temps d'execution de mes formules.

Je répète que je n'y connais strictement rien : j'ai essayé de m'y mettre mais il faut s'en servir tous les jours pour bien comprendre, ce que je ne peux pas faire.

Merci tout de même pour l'explication :D
 

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Voilà de quoi faire, avec deux extractions (1 par plage de données d'entrée) et 2 renvoi dans la feuille (un effacement, un remplissage)

Bien que j'ai collé à ta macro, je n'en ai pas compris la logique ...
En effet, ton "sans doublon" est problématique dans le sens où tu gère les doublons de manière indépendante sur chacune des listes ... je te prédis que ça sera un gros mic-mac quand tu aura effectivement des doublons, mais pas aux mêmes lignes ...

Il serait plus simple, dans ton cas, de remplir un tableau "mémoire" au fur et à mesure que tu fais tes multi tests "Si machin = "" and blablabla) et après, de partir à la recherche des doublons dans cette liste. (voir même, faire une recherche de doublons avant de rajouter une donnée aux résultats déjà obtenus)

Après, comme ça dépend de ton application finale, je te laisse y réfléchir et nous dire un peu ce que tu veux.
 

Pièces jointes

  • Copie de TravauxHercule1.xlsm
    368.2 KB · Affichages: 152

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Bonjour Tirou,

je dois avouer que je n'ai pas compris ce que tu as voulu faire ...
La macro (que je n'ai pas créée !) permets d'alimenter mes 3 premieres colonnes sans doublon, suivant le choix de mes listes de validation dans la feuille budget. Ensuite elle permet la mise en relation des listes de valdiation et donc des listes de validation en cascade.
Aprés je ne sais pas si c'est la macro la plus optimisée, toujours est-il qu'elle fonctionne (tout mon fichier fonctionne en fait, c'est juste qu'avec les formules en onglet budget, notamment en colonne E, le fichier prend trop de temps à s'éxécuter).
Mais si je suis là c'est pour avoir votre avis et savoir comment vous feriez.

Pour êre tout a fait transparent, ma base de données ne ressemble pas à ce que j'ai mis en exemple : les ordres sont des données uniques, une clé de répartition. Cependant les informations telles que "Département", "Noms de centre de couts", "Numéro de centre de couts", "Responsable" sont redondantes et correspondent à plusieurs ordres.

Ce fichier à pour but, grace au choix de la liste de validation, de remonter tous les ordres suivant un choix de département et/ou de nom de centre de couts et/ou de repsonsable. Ensuite j'aimerai avoir une mise en page un peu particulière ...
En fait c'est le travail d'un TCD, mais la forme n'est pas du tout adaptée (donc pas la peine de me le proposé, j'ai deja essayé et pas du tout convenable).

Merci a vous pour votre expertise et votre intérêt à mon pb.
 

Discussions similaires

Réponses
13
Affichages
261

Statistiques des forums

Discussions
311 720
Messages
2 081 924
Membres
101 841
dernier inscrit
ferid87