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.
 

Fichiers joints

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
 

Fichiers joints

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.
 

Fichiers joints

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.
 

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

J'aurai plusieurs questions :

1) est-ce que le fichier que je t'ai proposé fonctionne chez toi avec ta base de donnée plus importante?

2) Si j'ai bien compris, ta macro fait (et ne fait que) dresser la liste sans doublon de 3 champs, histoire de les réutiliser dans de la validation de donnée ? Bon, après avoir relu tes posts, oui c'est bien ça ... bon, on oublie le VBA, et on va creuser du coté des formules.
Je reviens quand j'ai avancé

Bon, ça me dépasse en formules, à cause du fait qu'il y ait 3 champ de même niveau (au lieu d'un champ maitre et un champ esclave)
 
Dernière édition:

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

1)En fait je vois que tu as modifié la macro pour les doublons. Elle fonctionne aussi (sauf pour la colonne responsable) mais si une macro peut directement alimenter les listes de validation en cascade sans passer par un stockage en feuille "base de données", cela m'arrange !

2) Oui elle ne fait que ça ... mais comme énoncé plus haut, si tu peux faire un fichier sans lister les sans doublon, ça m'importe peu en fait, je voudrais juste le plus optimisé à savoir le plus fiable et le plus rapide (et le ce qui est le plus facile à mettre en oeuvre pour l'instant). Donc si une macro/formule pouvait alimenter directement les listes de validation en cascade, ce serait le top. l'inconvénient des formules c'est que j'ai quelques 3000 lignes à traiter ...

Merci de votre aide !

edit : le format tableau associé à la base de donnée peut tout a fait être changé et il en est de même pour l'onglet budget.
 

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Ok, merci d'avoir confirmé qu'elle fonctionne. Mais est-ce que tu y a vu un gain de temps dans ton application?

Sinon, à tout hasard, je te propose de retirer le calcul automatique et de le forcer à la fin de ta macro :
Code:
Option Base 1

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.Calculation = xlCalculationManual
    
    If Target.Count > 1 Then Exit Sub
    tempAddress = Target.Address
    If tempAddress = "$B$1" Or tempAddress = "$C$1" Or tempAddress = "$D$1" Then
        Set dept = CreateObject("scripting.dictionary")
        Set centre = CreateObject("scripting.dictionary")
        Set resp = CreateObject("scripting.dictionary")
        
        With Sheets("Base en cours") 'Extraction données sources
            derLigne = .Range("E1048000").End(xlUp).Row
            derCol = .Range("AAA1").End(xlToLeft).Column
            tempTableau = .Range(.Cells(1, 1), .Cells(derLigne, derCol))
            End With
        tempTableau2 = Sheets("Budget").Range("B1:D1").Value
            
            
            For i = 2 To UBound(tempTableau, 1)
                If (tempTableau2(1, 1) = "" Or tempTableau(i, 5) = tempTableau2(1, 1)) And _
                    (tempTableau2(1, 2) = "" Or tempTableau(i, 6) = tempTableau2(1, 2)) And _
                    (tempTableau2(1, 3) = "" Or tempTableau(i, 7) = tempTableau2(1, 3)) Then
                    
                    dept(Trim(tempTableau(i, 5))) = 1: centre(Trim(tempTableau(i, 6))) = 1: resp(Trim(tempTableau(i, 7))) = 1
                    End If
                Next i
            
            tailleTableau = Application.Max(dept.Count, centre.Count, resp.Count)
            ReDim tempTableau3(tailleTableau, 3)
            ligne = 1
            For Each k In dept: tempTableau3(ligne, 1) = k: ligne = ligne + 1: Next k: ligne = 1
            For Each k In centre: tempTableau3(ligne, 2) = k: ligne = ligne + 1: Next k: ligne = 1
            For Each k In resp: tempTableau3(ligne, 3) = k: ligne = ligne + 1: Next k: ligne = 1
            
            With Sheets("Base en cours")
                .Range("A2").Resize(2 ^ 10, 3).ClearContents
                .Range("A2:C" & 1 + UBound(tempTableau3, 1)).Value = tempTableau3
                End With
            Application.Calculate
        End If
End Sub
 

Spinzi

XLDnaute Impliqué
Re : Besoin aide VBA

Oui par contre avant j'étais obligé d'attendre que la liste "cherche" les résultats ou qu'elle les vide. Ici il y a un gain de temps. par contre il y a un décalage pour la colonne sans doublon responsable, où les données commencent à apparaitre en C3 et non en C2 de la feuille base.

Je test ton nouveau code.

Super début

edit d'après reflexion : ok j'ai compris pourquoi il y avait un décalage, simplement parce qu'il y a des cellules vides dans responsable et que pour lui c'est une occurence. Mais c'est trés bien comme ça.
Par contre avec ton nouveau code c'est un peu plus long qu'avec le premier ...
 
Dernière édition:

Tirou

XLDnaute Occasionnel
Re : Besoin aide VBA

Mmm, je pense que la lenteur viens du Application.calculate. Après, je ne connais pas bien le fonctionnement automatique d'excel à ce niveau: est-ce qu'il repasse en calcul automatique à la fin des macros (et auquel cas, la ligne est inutile)? je n'en sais rien.

Pour t'éviter les cellules vides, je te propose de vérifier que les données sont bien "pleines" avant de les entrer dans le dico.

Code:
if Trim(tempTableau(i, 5)) <> "" then dept(Trim(tempTableau(i, 5))) = 1
if Trim(tempTableau(i, 6)) <> "" then centre(Trim(tempTableau(i, 6))) = 1
if Trim(tempTableau(i, 7)) <> "" then resp(Trim(tempTableau(i, 7))) = 1
 

Discussions similaires


Haut Bas