Discussion :

[idate]

Bonjour,

Je traite actuellement une base de trois tables : ProduitA, ProduitB et Relations.
Je souhaite déterminer les ProduitsA qui ont les mêmes relations avec des ProduitsB. Pour cela, j'ai concocté ce code VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub gamme()
 
Dim a, b, c As String
Dim k As Integer
Dim results As Worksheet
 
k = 1
 
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("rel")
    LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
 
Set results = Sheets.Add(After:=Sheets(Sheets.Count))
results.Name = "results"
 
With results
    .Cells(1, 1) = "pv_sku"
    .Cells(1, 2) = "concat"
 
End With
 
For i = 2 To LastLig
    With ThisWorkbook.Worksheets("rel")
        a = .Cells(i, 2)
        b = .Cells(i, 1)
        c = .Cells(i - 1, 2)
    End With
 
    With results
 
        If a = c Then
            .Cells(k, 2) = .Cells(k, 2) & b
        Else
            k = k + 1
            .Cells(k, 1) = a
            .Cells(k, 2) = b
 
        End If
    End With
Next i
 
End Sub

Mon problème : Ce code me permet de traiter des feuilles de relations d'environ 20 000 à 40 000 sans problèmes. Cependant, dès que je dépasse 40 000 cela devient très long ou excel plante.

Du coup, premièrement, avez-vous des méthodes pour permettre de diminuer le temps d'exécution d'une macro ? (une certaine mise en forme, l'utilisation de fonctions plutôt que d'autres, etc...)
Deuxièmement, pensez-vous qu'il est possible de faire le même traitement avec du SQL ? (en effet, j'utilise PostgreSQL pour les grosses tables)

Merci d'avance

Cordialement, idate.

[Marc-L]

Bonjour, bonjour,

il est question de rapidité par exemple ici …

[clementmarcotte]

Bonjour,

Bonjour, bonjour,

il est question de rapidité par exemple ici …

Il y a de ces gens qui ont la mémoire courte.

[clementmarcotte]

Bonjour,

Je traite actuellement une base de trois tables : ProduitA, ProduitB et Relations.

Mon problème : Ce code me permet de traiter des feuilles de relations d'environ 20 000 à 40 000 sans problèmes. Cependant, dès que je dépasse 40 000 cela devient très long ou excel plante.

C'est la preuve par l'absurde qu'Excel, n'est pas, n'a jamais été et ne sera jamais un Système de gestion de banques de données.

Du coup, premièrement, avez-vous des méthodes pour permettre de diminuer le temps d'exécution d'une macro ? (une certaine mise en forme, l'utilisation de fonctions plutôt que d'autres, etc...)

Prendre un programme capable de créer des composants COM et transformer ta macro en complément COM. À la différence de la macro qui est interprétée ligne après ligne, passage après passage, le programme COM est immédiatement disponible sous une forme compilée.

Deuxièmement, pensez-vous qu'il est possible de faire le même traitement avec du SQL ? (en effet, j'utilise PostgreSQL pour les grosses tables)

Je suppose que oui, mais je n'ai jamais essayé.

[idate]

Prendre un programme capable de créer des composants COM et transformer ta macro en complément COM. À la différence de la macro qui est interprétée ligne après ligne, passage après passage, le programme COM est immédiatement disponible sous une forme compilée.

J'ai fais quelques recherches à propos de ces fameux composants COM mais ça ne me parle pas du tout. Connaissez-vous un logiciel qui permettrait de transformer une macro en complément COM utilisable sous excel?

Merci d'avance

[el_slapper]

Il y a quand même moyen d'améliorer tout ça avec des tableaux.

Je remplacerais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
For i = 2 To LastLig
    With ThisWorkbook.Worksheets("rel")
        a = .Cells(i, 2)
        b = .Cells(i, 1)
        c = .Cells(i - 1, 2)
    End With
    With results
         If a = c Then
            .Cells(k, 2) = .Cells(k, 2) & b
        Else
            k = k + 1
            .Cells(k, 1) = a
            .Cells(k, 2) = b
        End If
    End With
Next i

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Dim TabO as Variant, TabD() As Variant
    TabO = Range("A1:B" & Cstr(LastLig))
    With results
    ReDim TabD(1 To 2, 1 To 1) 
    k = 1
    For i = 2 To LastLig
        If TabO (i, 2) = TabO (i - 1, 2) Then
            TabO (i, 1) = Val(TabO (i, 2)) + Val(TabO (i, 1))
        Else
            k = k + 1
            ReDim Preserve TabD(1 To 2, 1 To k) 
            TabD(k, 1) = TabO (i, 2)
            TabD(k, 2) = TabO (i, 1)
        End If
    Next i
    Range("A1:B" & Cstr(k)).Value = TabD
    End With

(non testé, j'ai pas les données - mais c'et la philosophie).

[Marc-L]

Il y a quand même moyen d'améliorer tout ça avec des tableaux.

Méthode et tutoriel lui étant déjà indiqués depuis juillet dernier ! Cf lien dans le post #4 …

[idate]

(non testé, j'ai pas les données - mais c'et la philosophie).

Je viens de lire le tuto sur les tableaux et ton fameux code. Je ne trouve rien d'anormal, au contraire, ça a l'air de pouvoir faire exactement ce que je veux. J'ai testé avec les données en pièces jointes Pièce jointe 143315 mais aucun résultat n'est renvoyé dans la feuille "results" .... d'ou cela peut-il provenir d'après vous?

Merci d'avance

[el_slapper]

N'ayant que 2003 au boulot(et à la maison), j'aurais du mal à tester un *.xlsx (seulement utilisable à partir de 2007).

Il y a probablement un bug dans mon code(non testé), mais la méthode pour le rechercher est le pas-à-pas : tu mets un point d'arrêt au début(en cliquant dans le gris à gauche du code), et tu descends l'algorithme pas-à-pas en appuyant sur F8. Et tu vois si ça tourne comme ça devrait. En activant "Affichage - fenêtre variable globales", tu pourra voir en direct si le tableau amont est bien rempli, si le tableau aval se remplit bien petit à petit, et si le tableau aval s'insère bien dans l'onglet "results". L'erreur concerne probablement un des trois éléments.

En me relisant, je me dis que l'erreur est probablement dans la première ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TabO = Range("A1:B" & Cstr(LastLig))

. En effet, je ne précise pas l'onglet d'origine. A remplacer par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TabO = ThisWorkbook.Worksheets("rel").Range("A1:B" & Cstr(LastLig))

.
Et, à la fin rajouter un point devant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Range("A1:B" & Cstr(k)).Value = TabD

pour prendre en compte le With.


Si ça ne suffit pas, alors passer en pas-à-pas.

[idate]

Merci beaucoup pour la précision avec laquelle tu m'expliques comment débugguer ce code. Cela m'apporte beaucoup.

Concernant les modifications citées ci-dessus, je l'ai est bien prise en compte et en effet, la feuille "rel" est maintenant traitée.

Lorsque je lance le pas à pas détaillé, il m'indique directement cette ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TabD(k, 1) = TabO(i, 2)

Avec une erreur : "L'indice n'appartient pas à la selection"

J'ai utilisé l'affichage des variables locales pour pouvoir identifier le problème et il s'avère que d'une part, le tableau Tab0 a l'air d'être mal rempli.
Exemple:

Tab0(2, 1) = 5_pd_02486 (comme prévu)
Tab0(2, 2) = 5_pv_556751 (comme prévu)
Tab0(3, 1) = 10 (devrait être 5_pd_XXXXX)
Tab0(3, 2) = 5_pv_556751 (comme prévu)
Tab0(4, 1) = 10 (devrait être 5_pd_XXXXX)
Tab0(4, 2) = 5_pv_556751 (comme prévu)
etc...

Je ne vois pas d'ou vient le "10".

D'autre part, je ne comprend pas pourquoi j'ai l'erreur en question sur la ligne citée ci-dessus.

Avez-vous des idées? Je joins le fichier xls (format 2003)test.xls

Modif :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
For i = 2 To LastLig
        If TabO(i, 2) = TabO(i - 1, 2) Then
            TabO(i, 1) = Val(TabO(i, 2)) + Val(TabO(i, 1))
        Else
            k = k + 1
            ReDim Preserve TabD(1 To 2, 1 To k)
            TabD(k, 1) = TabO(i, 2)
            TabD(k, 2) = TabO(i, 1)
        End If
    Next i

Je pense que

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TabO(i, 1) = Val(TabO(i, 2)) + Val(TabO(i, 1))

doit devenir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TabD(i, 1) = Val(TabO(i, 2)) + Val(TabO(i, 1))

car sinon on réécrit dans Tab0.

[clementmarcotte]

J'ai fais quelques recherches à propos de ces fameux composants COM mais ça ne me parle pas du tout.

Contrôle ActiveX, Dll ActiveX, Microsoft Office, VisualBasic etc...

Connaissez-vous un logiciel qui permettrait de transformer une macro en complément COM utilisable sous excel?

Merci d'avance

Pas de conversion directe, mais avec VB5, VB6, C#, VB.net et probablement plusieurs autres.

[Marc-L]

N'ayant que 2003 au boulot(et à la maison), j'aurais du mal à tester un *.xlsx (seulement utilisable à partir de 2007).

J'ouvre les .xlsx sur 2003 : il y a un pack de compatibilité chez Microsoft à installer …

[idate]

Salut,

J'ai modifié ton code el_slapper de façon à réaliser exactement ce que je veux avoir comme résultat final. (Ce n'était pas exactement cela précédemment, je m'en suis rendu compte ce matin) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Sub gamme()
 
Dim k As Integer
Dim results As Worksheet
 
k = 1
 
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("rel")
    LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
 
Set results = Sheets.Add(After:=Sheets(Sheets.Count))
results.Name = "results"
 
Dim TabO As Variant, TabD() As Variant
    TabO = ThisWorkbook.Worksheets("rel").Range("A1:B" & CStr(LastLig))
    With results
    ReDim TabD(1 To 2, 1 To 2)
    TabD(1, 1) = "pv_sku"
    TabD(1, 2) = "concat"
    k = 1
    For i = 2 To LastLig
        If TabO(i, 2) = TabO(i - 1, 2) Then
            TabD(k, 2) = TabO(i, 1) + TabD(k, 2)
        Else
            k = k + 1
            ReDim Preserve TabD(1 To k, 1 To 2)
            TabD(k, 1) = TabO(i, 2)
            TabD(k, 2) = TabO(i, 1)
        End If
    Next i
    .Range("A1:B" & CStr(k)).Value = TabD
    End With
 
End Sub

Cependant, même avec le mode pas à pas détaillé, je n'arrive pas à comprendre pourquoi la macro bloque à la ligne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ReDim Preserve TabD(1 To k, 1 To 2)

En effet, avec cette ligne, je souhaite agrandir mon tableau en lui ajoutant une ligne tout en conservant les valeurs déjà stockées dedans. J'ai l'erreur habituelle : "L'indice n'appartient pas à la sélection". D'ou est-ce que ça peut provenir d'après vous?

Merci d'avance

[Marc-L]

La réponse se trouve pourtant en consultant l'aide de l'instruction ReDim …

[idate]

Autant pour moi... ReDim ne peut pas être étendu selon sa première dimension. D'ou :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ReDim Preserve TabD(1 To 2, 1 To k)

au lieu de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ReDim Preserve TabD(1 To k, 1 To 2)

.

Enfin, la ligne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 .Range("A1:B" & CStr(k)).Value = TabD

doit me permettre de copier mon tableau sur la nouvelle feuille. Cependant, le tableau TabD est un tableau a 2 lignes et k colonnes. Je voudrais afficher la transposé de celui-ci, existe-t-il une fonction adéquate?

Modif: Après quelques recherches, il me semble que la fonction Application.Transpose(table) devrait me donner le résultat escompté. Cependant, j'ai une incompatibilité de type quand j'utilise ce code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

.Range("A1:B" & CStr(k)).Value = Application.Transpose(TabD)

.

Sachant que TabD était un range, Application.Transpose(TabD) aussi non?

merci d'avance

[Marc-L]

TabD n'est pas un Range mais une variable tableau et cela ne change rien quant au souci de transposition …

La logique présentée est bonne, c'est l'une des deux solutions possibles et de mon côté je ne rencontre pas de souci …

L'autre solution est de définir TabD une fois pour toutes avec le maximum théorique de lignes,
ainsi pas de souci de transposition !

Donc vis à vis du code du post #15, après le calcul de LastLig, vers la ligne n°12 : Redim TabD(1 to LastLig, 1 to 2) …

Ligne n°28 à supprimer car plus besoin de redimensionner la variable tableau … Idem pour la ligne n°19.
Ligne n°6 à supprimer, en doublon avec la ligne n°22 …

__________________________________________________________________________________________

Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion …

[clementmarcotte]

Je suis donc le rabat-joie de service et je l'accepte.

Quand on pense que Visual Studio Express est gratuit et qu'il offre toute une panoplie d'outils pour la gestion de données (incluant la sauvegarde des données au format XML avec au maximum 2 lignes de code en VB, si l'on veut récupérer le schéma avant la sauvegarde) et qu'il vient avec SQL server Compact tout aussi gratuit.

Quand on pense que le même Visual Studio Express peut très facilement être interfacé avec un classeur Excel soit par automation, soit par OLEDB ou même avec OPEN XML, à partir d'Excel 2007.

Quand on pense que MySQL et SQL Server Express sont aussi offerts gratuitement et que leur moteur fait probablement automatiquement une grande partie du travail recréé par des macros.

Je ne comprend pas cet acharnement à utiliser des macros, puisque Excel doit probablement toujours garder la totalité des données en mémoire vive; alors que SQL Server Express peut facilement n'utiliser que les données dont il a besoin.

Mais bon, le "client" a supposément toujours raison.

[Marc-L]

Mais comme le demandeur a fait un effort de lecture et de recherche,
il est normal de lui répondre aussi quant à son code VBA …

[idate]

Bonjour bonjour !

J'ai donc opté pour la solution suivante : Dimensionner mon tableau TabD de manière fixe dès le début du code.

Ainsi, voici les résultats de l'utilisation de tableaux :

• Possibilité de traiter plus de 700 000 lignes au lieu de 20 000 auparavant
• La macro s'execute en moins d'une seconde sur ces 700 000 lignes alors qu'elle prenait plus de 30 sec pour 20 000 lignes auparavant

Un grand merci aux internautes qui me sont venus en aide !

[Marc-L]

Comme d'hab', merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion …