XL 2016 Optimisation macro

[Astrolab]

Bonjour,

je dispose d'un fichier qui a vocation à déterminer quelle est la meilleure quantité à choisir en fonction de tranches de prix.

Pour cela je rentre en premier lieu mon besoin exprimé( colonne C ) et ensuite ça fait une recherche du besoin minimum à commander (besoin réel) et du besoin optimisé par rapport aux coûts.

Exemple en ligne 9 du fichier

besoin exprimé de 6
acquisition possible à l'unité donc le besoin réel est de 6
Mais finalement moins onéreux d'en commander 500

Pour réaliser les calculs on a fait 2 macros fonctions

Function prix(cel As Range) ' cel étant la quantité réelle
For i = (Asc("J") - 64) To Cells(cel.Row, Columns.Count).End(xlToLeft).Column Step 3
    If Cells(cel.Row, i) > cel Or Cells(cel.Row, i) = "" Then Exit For
    prix = Cells(cel.Row, i + 1)
Next
End Function

Function optimum(cel As Range) ' cel étant la quantité réelle
For i = (Asc("J") - 64) To Cells(cel.Row, Columns.Count).End(xlToLeft).Column Step 3
    If Cells(cel.Row, i + 2) < cel.Offset(0, 2) And Cells(cel.Row, i + 2) <> 0 Then optimum = Cells(cel.Row, i)
Next
optimum = Application.Max(optimum, cel.Value)
End Function

Le problème est que le fichier source contient 671000 lignes et que lorsque l'on rentre notre besoin exprimé ça prend trois plombes

Si vous avez une astuce pour améliorer la vitesse de traitement, je suis preneur

Merci

 

[sylvanu]

Bonjour Astrolab,
Avec 671000 ligne, de toute façon ce sera long.
Plusieurs pistes :
1- Le plus rapide serait à l'ouverture du fichier de passer le tableau en array, et ensuite de ne travailler que dans celui ci. Il faudrait déclarer cet array en public pour être en permanence accessible. Mais ça ferait un gros array. Sur mon petit PC, le transfert cellules array prend 2 secondes. Il y a un rapport entre 5 et 10 antre la lecture d'une cellule et la lecture d'un élément d'array.

Sub test()
T0 = Timer
tablo = Range("J1:BQ671000")
MsgBox ("Remplissage array : " & Timer - T0 & "s")
End Sub

2- On peut gagner un peu de temps sur les IF car beaucoup de cellules sont vides sur les tranches de prix :

Remplacer
If Cells(cel.Row, i) > cel Or Cells(cel.Row, i) = "" Then Exit For
par ça
If Cells(cel.Row, i) = "" Then Exit For
If Cells(cel.Row, i) > cel  Then Exit For

De cette façon pour toutes les cellules vides, il n'y a pas d'évaluation du OU.

et ensuite :

If Cells(cel.Row, i + 2) < cel.Offset(0, 2) And Cells(cel.Row, i + 2) <> 0 Then optimum = Cells(cel.Row, i)
par :
If Cells(cel.Row, i + 2) = 0 Then Exit For
If Cells(cel.Row, i + 2) < cel.Offset(0, 2) Then optimum = Cells(cel.Row, i)

Mais le meilleur gain reste la première solution.
A noter que la solution 2 est compatible de la solution 1.

 

[Astrolab]

Bonjour Sylvanu,

merci pour ta réponse.

Concernant la solution 2, j'ai compris

Concernant la solution 1 , je te livre la façon dont j'ai modifié dans le fichier mais en réalité je ne comprends pas vraiment ce que je fais.

Il s'agit de transformer toute ma feuille en un tableau type array ?
A quoi sert le timer ? Pas vu de message box s'ouvrir ?

j'ai insérer

Sub test()
T0 = Timer
tablo = Range("J1:BQ671000")
MsgBox ("Remplissage array : " & Timer - T0 & "s")
End Sub

Sub Worksheet_Activate()
    Application.CalculateFull
End Sub

Si tu peux me faire une petite explication à la façon pour les "nuls"

Bonne journée

 

[sylvanu]

Bonjour,

C'était juste un exemple. Il faut lancer la macro Test. C'est pour mesurer le temps de transfert de "J1:BQ671000". Le Msgbox donne le temps de transfert. En effet inutile de partir sur une solution qui serait plus longue que la méthode actuelle.

Si vous l'adoptée, cette solution entraînera de très grosse modifs dans vos macros, il est sain, je pense, de vérifier que le gain de temps sera au rendez vous.
En PJ deux boutons de test :
1- Temps transfert cellule/array
2- Temps de lecture de 671000 cellules et arrays.
A vous d'apprécier le gain potentiel.

 

[Astrolab]

Re,

merci. Je viens de tester les temps de transferts et lecture, j'arrive sur les mêmes gains que vous.

"Si vous l'adoptée, cette solution entraînera de très grosse modifs dans vos macros"

de quel type ?

Je vais essayer déjà demain avec la solution 2 et me rendre compte du temps avant d'aller plus avant

Encore merci et bonne soirée

 

[sylvanu]

Re,
Il vous faudra ré écrire vos deux fonctions avec comme pointeurs des index sur l'array.
Pas compliqué mais il faut bien comprendre l'organisation de l'array.
En faisant tablo = Range("J1:BQ671000"), on obtient :
Array(x,y) avec y N° de colonne de 1 à 60 et x numéro de lignes;
c'est juste un exercice intellectuel de transfert de pointeurs.
Mais comme vous allez y passer du temps, je voulais juste vérifier que je ne vous emmenais pas dans une impasse stérile.