Discussion :

[benlesage]

Bonjour,

Je pense que vous allez pouvoir m'aider.

Je cherche à optimiser la vitesse d'exécution d'une partie d'un programme VBA. Celui ci met presque 20 minutes!


La partie de programme en question :
------------------------
'A noter; nbl est le nombre de ligne dans tablcomplet (environ 2000), et lig le nombre de ligne dans tablecompil (environ 50 000). Pour chaque phase (p20, p30....) je dois retrouver dans tablcompil 4 valeurs. J'ai essayé avec des boucle for mais cela semble encore plus long

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
44
conca = ""
For a = 4 To nbl
 
        On Error Resume Next
        conca = Sheets("tablcomplet").Range("A" & a).Value & "p20"
 
    Sheets("tablcomplet").Range("AL" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AM" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AN" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("AO" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p30"
       Sheets("tablcomplet").Range("AP" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AQ" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AR" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("AS" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p40"
      Sheets("tablcomplet").Range("AT" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AU" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AV" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("AW" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p50"
      Sheets("tablcomplet").Range("AX" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("AY" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("AZ" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("BA" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p60"
      Sheets("tablcomplet").Range("BB" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("BC" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("BD" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("BE" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
      conca = Sheets("tablcomplet").Range("A" & a).Value & "p70"
      Sheets("tablcomplet").Range("BF" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 8, False)
    Sheets("tablcomplet").Range("BG" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 13, False)
     Sheets("tablcomplet").Range("BH" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 12, False)
      Sheets("tablcomplet").Range("BI" & a).Value = WorksheetFunction.VLookup(conca, Sheets("tablecompil").Range("S" & 2 & ":AF" & lig), 14, False)
 
    Next

-----

J'ai également essayé de transformer un peu tout ça en passant par un tableau mais c'est encore pire! :

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
Dim rg As Range
Dim h As Variant
 
Set rg = Sheets("tablecompil").Range("S" & 2 & ":AF" & lig)
h = rg
 
 
For a = 4 To nbl
 On Error Resume Next
h(a - 2, 2) = Sheets("tablcomplet").Range("A" & a).Value & "p20"
h(a - 2, 3) = Sheets("tablcomplet").Range("A" & a).Value & "p30"
h(a - 2, 4) = Sheets("tablcomplet").Range("A" & a).Value & "p40"
h(a - 2, 5) = Sheets("tablcomplet").Range("A" & a).Value & "p50"
h(a - 2, 6) = Sheets("tablcomplet").Range("A" & a).Value & "p60"
h(a - 2, 7) = Sheets("tablcomplet").Range("A" & a).Value & "p70"
 
Next
 
       For a = 4 To nbl
        On Error Resume Next
 
 
    Sheets("tablcomplet").Range("AL" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 8, False)
    Sheets("tablcomplet").Range("AM" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 13, False)
     Sheets("tablcomplet").Range("AN" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 12, False)
      Sheets("tablcomplet").Range("AO" & a).Value = WorksheetFunction.VLookup(h(a - 2, 2), h, 14, False)
 
 
       Sheets("tablcomplet").Range("AP" & a).Value = WorksheetFunction.VLookup(h(a - 2, 3), h, 8, False)
    Sheets("tablcomplet").Range("AQ" & a).Value = WorksheetFunction.VLookup(h(a - 2, 3), h, 13, False)
     Sheets("tablcomplet").Range("AR" & a).Value = WorksheetFunction.VLookup(h(a - 2, 3), h, 12, False)
 
etc..

Une idée?

Par avance merci

[joe.levrai]

Bonjour,

il me semble préférable d'utiliser la méthode FIND de l'objet Range

tu cherches la position de "conca" et ensuite tu récupères les valeurs décalées souhaitées (propriété Offset de l'objet Range)

nous avons répondu ces derniers jours sur plusieurs sujets traitant de la méthode FIND, je te laisse bûcher sur un début de code, reviens si ça coince

[benlesage]

Merci à toi je regarde ça! FIND est moins gourmand?

[joe.levrai]

y'a débat, enfin y'a déjà eu débat à plusieurs reprises

je dirais que oui, plus rapide, on verra ça

[Philippe Tulliez]

Bonjour,
Au lieu de faire une boucle (For a = 4 To nbl), insère en une seule instruction la fonction RECHERCHEV dans la plage de la colonne entière et ensuite si tu ne souhaites pas conserver la formule, tu remplaces, toujours en une seule instruction, la formule par son résultat.
Voir ces deux billets Ecrire une formule dans Excel à l'aide d'une procédure VBA et Comment remplacer des formules par leur résultat

[eriiic]

Bonjour,

sympas tes tutos Philippe, merci pour le travail.
J'ai une suggestion pour compléter le 2nd si tu veux bien.
En cas de formules nombreuses et/ou lourdes (sommeprod) il arrive qu'il y ait un déphasage et que l'on copie une partie non encore évaluée.
Il y a la propriété CalculationState (je ne sais pas depuis quelle version), on peut attendre son état correct pour copier :

Application.CalculationState, propriété
Cette propriété renvoie une constante XlCalculationState qui indique l'état de calcul de l'application, pour les calculs en cours dans Microsoft Excel. Type de données en lecture seule.

Code :

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

1
2
3
4
5
    If Application.CalculationState = xlDone Then
        MsgBox "Done"
    Else
        MsgBox "Not Done"
    End If

Énumération XlCalculationState
Indique l'état de calcul de l'application.
Nom Valeur Description
xlCalculating 1 Les calculs sont en cours.
xlDone 0 Calculs terminés.
xlPending 2 Des modifications déclenchant un calcul ont eu lieu, mais le recalcul n'a pas encore été effectué.

Je te laisse voir si tu veux tester et l'ajouter, ou si tu préfères que je le mette en commentaire du tuto.
eric