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.

[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.