Execution Macro VBA lente /Excel ne répond plus

Question

J'ai une macro qui fonctionne lorsque je la déroule pas à pas. Lorsque je la lance en execution automatique, Excel se fige et ne répond pas pendant 15 minutes. Ensuite le calcul est fait. Mais cela est trop long. Actuellement, le code VBA est dans la feuille ou le résultat doit apparaitre (j'ai également essayé de la mettre dans un module, mais cela a le même effet à priori). Je pense que l'erreur vient du paramétrage des différentes données, mais je n'en suis pas sure. Voici le code Sub RECHERCHE_DES_BESOINS_NOMENCLATURE() Dim ligne As Long Dim De As Integer Dim colonne As Integer ligne = 2 colonne = 56 De = Worksheets("QAIFNL").Range("A" & Rows.Count).End(xlUp).Row + 1 While Cells(1, colonne).Value &lt; 53 ligne = 2 While ligne &lt; De Besoin Cells(ligne, (colonne)) = WorksheetFunction.CountIfs(Worksheets("PLANNING DE PRODUCTION").Range("A:A"), Cells(ligne, 49), Worksheets("PLANNING DE PRODUCTION").Range("D:D"), Cells(1, colonne)) * Cells(ligne, 10) ligne = ligne + 1 Wend colonne = colonne + 1 Wend End Sub Merci de votre aide...

jmd · Answer

Joins le fichier de départ un descriptif des actions de la macro et une copie du résultat 9 chances sur 10 qu'on se passe de VBA...

jmd · Answer

Re je ne vois pas le traitement ??? 5000 ou 100 000 lignes comptent peu ! note : VBA ne résout jamais un problème, il en ajoute un autre...

Famewarz · Answer

J'ai un peut de mal à comprendre ton programme, pourrais-tu rapidement expliquer en quoi ce sub consiste...

MFerrand · Answer

Il convient autant que possible de réserver les modules de feuilles aux évènementielles, tu les encombres inutilement (et ces modules ne sont pas publics). La fonction NO.SEMAINE est fausse (utilisée ainsi). Sous 2010 elle donne le bon résultat avec le paramètre 21, sous les versions antérieures pas de possibilité, sous les versions postérieures une fonction dédiée renvoie le numéro de semaine ISO. Peux-tu indiquer en quelques mots ce que tu calcules ? (lire du code non indenté devient vraiment très fatiguant ! ) Cordialement...

Pedro22 · Answer

D'où sort la valeur en Cells(1, colonne).Value ? Je ne vois ni formule Excel ni instruction dans ta macro qui impacte sa valeur...

MFerrand · Answer

Tu fais une double-boucle, là où il n'en faut qu'une avec 2 conditions... ça doit ramer ! Et que signifie Besoin ? Je ne vois pas de procédure portant ce nom...

Pedro22 · Answer

J'allais poser la même question...

Voilà ma proposition :

Sub RECHERCHE_DES_BESOINS_NOMENCLATURE()

Application.ScreenUpdating = False 'Désactive l'affichage (exécution + rapide)
Application.Calculation = xlCalculationManual 'Désactive le calcul auto des fonctions à chaque modification (exécution + rapide)

Dim ligne As Long, De As Long, colonne As Integer, Lmax As Long

De = Worksheets("QAIFNL").Range("A" & Rows.Count).End(xlUp).Row + 1
Lmax = Worksheets("PLANNING DE PRODUCTION").Range("A" & Rows.Count).End(xlUp).Row + 1

For colonne = 56 To 77 'Privilégier une boucle "For" en cas d'intervalle connu
    For ligne = 2 To De
        With Worksheets("PLANNING DE PRODUCTION")
            Cells(ligne, (colonne)) = WorksheetFunction.CountIfs(.Range("A1:A" & Lmax), Cells(ligne, 49), .Range("D1:D" & Lmax), Cells(1, colonne)) * Cells(ligne, 10) 'privilégier les plages finies (et pas "A:A") dans les fonctions type ".SI.ENS()"
        End With
    Next ligne
Next colonne

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

MFerrand · Answer

Et quelque chose m'échappe ! Une seule ligne dans ton planning, donc il ne devrait y avoir que 1 ou 0 dans la colonne 56 ...

Pedro22 · Answer

As-tu essayé le code que je t'ai envoyé ...

MFerrand · Answer

Bon, J'ai trouvé la multiplication ! Oui on peut faire mieux ! Je regarde plus tard, je dois m'absenter, mais vois toujours ce que donne le code de Pedro (que je n'ai pas lu, Salut !). @+...

Pedro22 · Answer

Essaye de mettre la première et la dernière ligne en commentaire (=ajouter un apostrophe devant le "Application.ScreenUpdating = False") pour voir ce que ça donne sans désactiver l'affichage...

MFerrand · Answer

27 secondes ! Je garde des chances de faire mieux ! Je profite pour des petites précisions : tu as 22 colonnes à partir de BD où tu flanques des numéros de semaines. Et tu numérotes à partir de la semaine en cours. Est-ce que ce nombre de colonnes est fixe ? Où est-ce que tu numérotes jusqu'à 53 systématiquement ? &#91;l'année ? en fait ,il serait mieux de prendre la dernière semaine, facilement identifiable, c'est celle du 28 décembre (comme la 1re est celle du 4 janvier)&#93; Parce qu'en fait c'est en quelque sorte l'horizon de planification, et 22 semaines c'est déjà pas mal... Et si on en numérotes 22, pourquoi ne pas continuer sur l'année suivante en repartant à 1 après la dernière ? Là il n'y a qu'une ligne servie en production, mais si j'ai bien compris, il peut y en avoir plusieurs, et plusieurs concernant le même produit ? Ce que tu veux obtenir en colonne semaine, c'est le nombre de produits correspondants, figurant en production la même semaine, multiplié par la valeur en J ? Si j'ai bien compris la chose, je pense que je vais changer radicalement de méthode ! Et on pourra voir ce que cela donne. @+...

MFerrand · Answer

J'ai réécrit tes deux macros : pour la semaine, en retenant l'option 22 colonnes, elle te met donc 1 en BY au lieu de 53 puisque 2018 n'a que 52 semaines. Si pas ça ! me dire, je réécris !!

(Pour info : les années à 53 semaines sont celles dont le 1er janvier est un jeudi, ou bien un mercredi si elles sont bissextiles.)

Pour la seconde macro, objet du sujet, réécrite aussi : sur le modèle le 1er essai donnait un temps d'exécution de 46,875 millisecondes. J'ai refait 5 autres essais, car toujours des fluctuations... ils se situent dans une fourchette 31,25 à 70 millisecondes.

On tourne donc autour de la demi-seconde. Vu le contenu du modèle je ne trouve pas que ce soit particulièrement optimal et n'en suis donc que moyennement satisfait.

Je cherche donc une idée pour gagner en nombre d'opérations élémentaires... Peut-être une possibilité en triant mais je ne l'ai pas entreprise, ne sachant si l'ordre des lignes dans ton tableau à une importance, et je n'ai pas vu d'élément permettant de rétablir l'ordre initial.

Sinon, une possibilité à tenter serait de prédéfinir les cellules devant être servies dans la zone BD:BY en recensant les valeurs présentes en AW et les semaines susceptibles d'être impactées selon ces valeurs, en escomptant que le temps perdu lors de cette préparation supplémentaire sera regagné et plus largement ensuite...

En attendant, je vais voir ce que fait ta 3e macro...

Cordialement.