Optimisation code (boucles if)

Version imprimable

Bonjour,

J'ai un problème de boucle...

j'ai une feuille excel qui sert à rédiger des factures et à mettre à jour une base de donnée. Il y a une template sur cette feuille. J'ai créé un code qui permet d'importer les anciennes factures dans cette template à partir d'une base de donnée dite analytique. Les attributs de cette template sont : Description service, nombre d'unité de temps, unité de temps, tarif horaire, total htva.

La logique que j'ai suivi pour importer une ancienne facture est la suivante:

2 étapes:

1) injecter une formule INDEX dans chaque cellule de chaque ligne qui va aller chercher le bon attribut dans la base de donnée analytique à partir d'un identifiant unique "catégorie tâche + numéro tâche"

2) si une cellule tache est vide, cela veut dire que ce service n'a pas été utilisé pour cette facture bien précise. Je veux néanmoins compléter cette cellule avec un service provenant de la template au cas ou il s'agit d'une facture ou un devis qui doit être modifiée

Le problème est qu'il y a deux boucles if pour traiter ces deux requètes et cela prend plus ou moins 15 secondes pour les traiter.

Comment faire pour optimiser le code dans ce cas la ?

la condition If Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 3).Value > 0 me dit si une ligne de la feuille input correspond bien à un service/tache

Code:

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
 
Sub import_document()
 
Dim lastrow As Long
Dim i As Long
 
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With
 
lastrow = Workbooks("template_facture-devis.xlsm").Worksheets("input").Range("C65536").End(xlUp).Row
 
'd'abord on copie les valeurs des colonnes B et colonnes C (pour éviter la référence circulaire)
 
Sheets("INPUT").Range("B25:C" & lastrow).Value = Sheets("INPUT").Range("B25:C" & lastrow).Value
 
'ensuite on applique une formule index aux colonnes D, G, H, I si et seulement si la colonne C est plus grande que 0 (ligne de tâche)
 
' on importe ici les lignes contenus dans la facture voulue.
 
For i = 28 To lastrow
 
If Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 3).Value > 0 Then
 
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),8)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),8))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("G" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-5]&RC[-4],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),9)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-5]&RC[-4],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),9))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("H" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),10)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),10))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("I" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),11)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),11))"
 
End If
 
Next i
 
'on complète ensuite les lignes vides par les services de la template
 
For i = 28 To lastrow
 
If Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 4).Value = "" And Workbooks("template_facture-devis.xlsm").Worksheets("input").Cells(i, 3).Value > 0 Then
 
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),4)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),4))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("G" & i).Value = "0"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("H" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),6)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),5))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("I" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),7)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C15,MATCH(RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_template.xlsx]template'!R2C1:R9995C1,0),6))"
 
 
End If
 
Next i
 
 
'ensuite on copie colonnes toutes les cellules des colonnes D à I
 
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D25:I" & lastrow).Value = Sheets("INPUT").Range("D25:I" & lastrow).Value
 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
 
End Sub

Je ne sais pas si c'est très clair. Merci d'avance pour vos conseils.

Bonjour,

et bien enlève :

Code:

1
2
3
4
5
Next i
 
'on complète ensuite les lignes vides par les services de la template
 
For i = 28 To lastrow

et tu n'aura que 1 boucle

Peut aider..

Code:

1
2
3
Application.ScreenUpdating = False
'code
Application.ScreenUpdating = True

28/06/2013, 15h51
Kormondre

j'ai mis le code complet au cas ou
28/06/2013, 15h53
Kormondre
Citation:
Envoyé par rdurupt

et bien enlève :

Code:

1 2 3 4 5 Next i 'on complète ensuite les lignes vides par les services de la template For i = 28 To lastrow

et tu n'aura que 1 boucle
j'ai essayé cela ne réduit pas le temps de calcul

Le code est propre. Tu ne peux pas optimiser en nombre d'opérations par contre tu peux sûrement faire des opérations moins coûteuses en temps marchine.. Que font ces lignes

Code:

1
2
3
4
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("D" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),8)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-2]&RC[-1],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),8))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("G" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-5]&RC[-4],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),9)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-5]&RC[-4],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),9))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("H" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),10)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-6]&RC[-5],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),10))"
Workbooks("template_facture-devis.xlsm").Sheets("INPUT").Range("I" & i).FormulaR1C1 = "=IF(ISERROR(INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),11)),"""",INDEX('\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C15,MATCH(R7C12&RC[-7]&RC[-6],'\\Diskstation-bbw\templates\db_bbw\[db_analytics.xlsx]analytics'!R2C1:R9995C1,0),11))"

28/06/2013, 16h00
Kormondre

Ces lignes vont chercher dans la db analytique la ligne portant la clé de la tâche lié à la facture (type_document + numéro document + catégorie tache + numéro tache) et renvoient le contenu d'une certaine colonne lié à cet clé unique (un recherche v en fait)
28/06/2013, 16h04
EngueEngue
Et bien c'est de ce coté là qu'il faut regarder pour optimiser, il sera plus rapide de faire autre chose que des formules.
Code:

1 2 3 Application.Calculation = xlCalculationManual 'code Application.Calculation = xlCalculationAutomatic
Si tu veux que je jette un oeuil à tes formules et que les datas ne sont pas confidentielles poste les
28/06/2013, 16h24
Kormondre

Merci c'est gentil. Malheureusement c'est assez confidentiel.

je vois deux autres solutions pour éviter les formules:

1) Copier coller la template à partir d'un modèle (sans boucle) et changer les valeurs qui nous intéresse avec un application.match dans la db_analytique.
2) utiliser les filtres (mais est-ce que 400 filtres sur deux fichiers externes sont moins laborieux qu'injecter des formules?)
28/06/2013, 16h31
Invité

Citation:

Merci c'est gentil. Malheureusement c'est assez confidentiel.

rien ne t'emperche de donner des fichiers bidons sans rapport avec la réalité.
28/06/2013, 17h01
Kormondre

1 pièce(s) jointe(s)

vous avez raison, voici le fichier rar contenant les fichiers simplifiés
28/06/2013, 20h12
Invité
Citation:
Envoyé par rdurupt

et bien enlève :

Code:

1 2 3 4 5 Next i 'on complète ensuite les lignes vides par les services de la template For i = 28 To lastrow

et tu n'aura que 1 boucle
Bonjour,

Je ne comprends pas bien ce fichier, mais je pense qu'il s'agit d'une recherche faite entre la ligne 28 et la dernière ligne (lastrow = 65536).

Il semble que les 2 boucles soient différentes:
Boucle 1

Code:

' on importe ici les lignes contenus dans la facture voulue.

Boucle 2:

Code:

'on complète ensuite les lignes vides par les services de la template

En attendant, je proposerai de mettre un timer entre chaques étapes pour déterminer dans un 1er temps quels sont les étapes qui prennent un temps négligeable (sur lesquels il sera inutile d'agir) et non négligeable (celles sur lesquels il faudra agir).

t = Timer au début,

Après la 1ère boucle:
[A1] = Timer - t

Après la 2ème boucle:
[A2] = Timer - t

A la fin de la Macro:
[A3] = Timer - t

La cellule A1:A3 doivent être libre.

Ça permettra déjà d'aller dans la bonne direction pour la modife du code.

A+

EDIT:
Tu écris des formules enfaite.

- Soit il faut travailler avec des tableaux pour écrire en mémoire vive, puis ensuite balancer tout le tableau contenu en mémoire sur une plage de cellule en 1 seule fois.

- Soit, tu utilise la méthode Autofill, pour faire glisser les formules d'une cellule à une autre (méthode efficace). Regarde un exemple ici http://www.developpez.net/forums/d13...a/#post7368176
29/06/2013, 14h45
Kormondre

merci pour ta réponse. Est-ce que tu aurais un lien pour apprendre comment utiliser les tableaux en mémoire car je n'ai jamais appris cette technique. Pour la deuxième méthode je ne peux pas utiliser autofill car je dois appliquer la formule seulement sur certaines lignes.
29/06/2013, 18h52
EngueEngue
Un tableau est un array de variables stockées en mémoire:
Code:

1 2 3 Dim Tableau(28 to 65536) as integer Tableau(28)=1
Pour un tableau d'entiers d'une dimension.
29/06/2013, 21h41
Invité

Salut,

Juste pour dire, chez moi, le GetTickCount64 indique 0.063 s après l'étape de l'application autofill. Les formules sont enfaite glissé de haut en bas dans un temps totalement négligeable.
Seul l'opération de calcul prendra du temps, mais tu ne pourras pas agir sur ce temps de calcul. Enfaite si, je me suis apperçu que la fonction Range().Calculate ne donnait pas le même temps de calcul que la fonction Calculate tout court.
Une grosse partie de l'explication provient du fait qu'avec Application.Calculate tout court, C'est Excel qui gère le calcul et peut utiliser les différent coeurs d'execution du processeur. Avec Un CPU 4 coeurs, les 4 coeurs sont chargé à 100%. Avec un range.calculate, le multitreading n'est pas permis et seul 25% d'utilisation CPU sont utilisé. Il y a aussi le gèle de l'affichage et d'autres options qui sont inséré avec l'option Application.Calculate tel la possibilité d'arréter le calcul en court de route ou l'affichage du pourcentage en temps réel des calculs effectués depuis le début etc...
Mais globalement, la technique du Autofill + Calculate est la plus rapide de toutes les solutions confondu (y compris le travail en mémoire en travaillant avec les tableaux).
02/07/2013, 18h34
Kormondre

je vais vérifier la méthode du autofill :-)