Question optimisation macro

Bonjour, pas de problème, seulement une question.

J'utilise dans mon code une structure de boucle do.

i = 2
Do
    a = Cells(i, 4).Value
    Cells(i, 4).Value = Year(a)
    i = i + 1
Loop Until IsEmpty(Cells(i, 4))

Je l'utilise en plusieurs endroit, pas pour faire la même chose, mais l’idée est la même (prendre valeur de cette cellule et faire action en fonction)

Mais je ne comprend pas pourquoi c'elle ci est plus rapide.

i = 2
Cells(i, 4).Activate
Do
    a = ActiveCell.Value
    Cells(i, 4).Value = Year(a)
    i = i + 1
    Cells(i, 4).Activate
Loop Until IsEmpty(ActiveCell)

Avec la première structure mon programme met 37s tandis que avec la deuxieme, il met 25s.

Quelqu’un aurait une idée ? Etant donné que j'en suis à l'étape d'optimisation j'aimerais comprendre.

Hooodini

bonjour,

chez moi, la première structure est 30 x plus rapide que la seconde, et cela correspond à ce à quoi je m'attendais (activate et select ont une influence négative sur le temps d'exécution).

J'ai réessayer après avoir redémarrer, je gagne effectivement du temps, mais pas autant que j’espérais.

Bonjour acide sulfurique,

Je ne comprend pas du tout comment cela marche, j'ai relancer encore avec la nouvelle structure, sauf que la j'ai exactement le même temps que avec l'ancienne structure.

Est ce que les performance d'excel varie avec le temps?

J'ai deux macro chronométrée que je lance a la suite, la première le temps varie et la deuxième reste fixe.

La première est celle que je souhaite optimiser, mais le temps varie de 22 a 37 seconde d’exécution sans toucher au code.

Je doit avouer que le temps n'est pas forcement un problème, mais je n'aimerais pas avoir de problème d'exécution qui arrivent aléatoirement.

Cordialement.

Bonjour, salut h2so4

Une piste d'optimisation à tester :

Sub MaMacro()

Dim i As Long 'Déclaration de variables
Application.Calculation = xlCalculationManual 'Désactive le recalcul auto des formule Excel
For i = 2 To Range("D" & Rows.Count).End(xlUp).Row 'Boucle sur les lignes, de la 2ème à la dernière
    If IsDate(Range("D" & i)) Then Range("D" & i) = Year(Range("D" & i)) 'Pas besoin d'une variable intermédiaire
Next i
Application.Calculation = xlCalculationAutomatic

End Sub

Merci beaucoup,

Effectivement ça marche aussi. en changeant quelque bloc de mon code j'ai gagner un seconde. Je suis en train de regarder ce que je pourrais améliorer d'autre.

Sur ce, je vais clore le sujet.

hoooodini a écrit :

Effectivement ça marche aussi.

C'est un premier pas, mais est-ce plus rapide ??

oui mais ça m'as seulement permis de gagner une seconde.

J'ai réussi a descendre encore plus avec :

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

Application.ScreenUpdating = True
Application.Calculation = Application.Calculation
Application.EnableEvents = ActivationEvents

Maintenant je sais pas ce que je peut faire de plus.

hoooodini a écrit :

Maintenant je sais pas ce que je peut faire de plus.

bonjour,

travailler avec des arrays VBA.

exemple si date en colonne A

    dl = Cells(Rows.Count, 1).End(xlUp).Row 'nombre de lignes
    ad = Range("A1:A" & dl) 'on charge les dates dans un tableau
    For i = 1 To dl
        ad(i, 1) = Year(ad(i, 1)) 'on ne garde que l'année
    Next i
    Range ("A1:A") & dl = ad 'on remet le tableau dans la feuille

Je ne comprend pas ton code, c'est quoi "ad" ?

Tu luis attribue un range et tu fait parcourir chaque cellule pour rentrer = Year(ad(i, 1)).

En quoi est-ce différent de moi ?

La différence que je voit c'est que tu utilise "for-next" alors que moi j'utilise "do-loop". Est ce que un est plus rapide que l'autre?

Bonjour,

le fait d'utiliser une array VBA est environ 20 fois plus rapide que d'interagir avec la feuille de calcul.

Oui mais pourquoi ?

Je veut dire c'est les même opération qui sont réalisée. Les calculs doivent tous de mêmes être fait quelque part.

Si on bloque le rafraîchissement écran et la mise a jour auto des calculs, ça ne devrait pas se sentir non?

Je suis désolé de poser autant de question bêtes j'essaie juste de comprendre.

Bonjour,

je peux bien imaginer tout un tas de raisons pour lesquelles l'un est plus lent et l'autre plus rapide, mais le mieux est que tu poses la question à Microsoft. Tu trouveras ici (doc en anglais), les conseils d'optimisation émanant de Microsoft, mais tu n'as pas toujours l'explication de la raison pour laquelle une solution est plus rapide qu'une autre (désolé).

https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

Je comprend merci.

J'avais trouver un document en français pour réaliser quelque optimisation simple, ça m'as bien aider.

Je vais éplucher ton document et je verrais ce que je peut faire.

cordialement.

hoooodini a écrit :

Oui mais pourquoi ?

Je veut dire c'est les même opération qui sont réalisée. Les calculs doivent tous de mêmes être fait quelque part.

Si on bloque le rafraîchissement écran et la mise a jour auto des calculs, ça ne devrait pas se sentir non?

Je suis désolé de poser autant de question bêtes j'essaie juste de comprendre.

Bonjour, salut h2so4,

La raison est assez simple, une plage excel ne permet pas uniquement de stocker une valeur, mais a tout un tas d'autres propriétés qui la rendent plus lourde à manipuler : formule, format, couleur de fond, couleur de police, taille de police, bordure...

Un tableau (ou array), c'est juste une série de valeurs, donc beaucoup plus "light".

Pedro22 a écrit :

Bonjour, salut h2so4,

La raison est assez simple, une plage excel ne permet pas uniquement de stocker une valeur, mais a tout un tas d'autres propriétés qui la rendent plus lourde à manipuler : formule, format, couleur de fond, couleur de police, taille de police, bordure...

Un tableau (ou array), c'est juste une série de valeurs, donc beaucoup plus "light".

Bonjour à tous,

cela fait partie des raisons que je pouvais imaginer.

C'est beaucoup plus clair.

Je ne pensais pas que les propriétés étaient toujours active, je pensais qu'il fallait les appeler pour les activer.

Mais donc du coup il n'y a pas que pour les range que ça marche.

Dans beaucoup d'endroit je copie des données pour ensuite les trier, par exemple les doubles ou alors exclure certaine valeur.

i = 2

Do
    a = Cells(i, 1).Value
    If a = b Then
        Rows(i).Delete Shift:=xlUp
        i = i - 1
    End If
    b = a
    i = i + 1
Loop Until IsEmpty(Cells(i, 1))

Ici je supprime les lignes en double qui se suivent. Dans mon document ça correspond au titre du projet qui est répété pour les action projet, mais je ne veut garder que les titres. La fonction fonctionne très bien, mais est ce que je peut le passer dans un array pour accélérer ?

Bonjour,

dans ce cas-ci, il faut essayer de supprimer les lignes en bloc.

1) ajouter une colonne avec une formule qui permet de distinguer les lignes à supprimer des autres

exemple de formule en B2 et à tirer vers le bas,=SI(A2=A1;1;2)

2) trier les lignes sur la cette colonne

3) filtrer les lignes sur cette colonne, valeur 1

4) supprimer toutes les lignes visibles.

5) supprimer le filtre

exemple

    With ActiveSheet
        dl = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range("A1:B" & dl).Sort key1:=.Range("B1"), order1:=xlAscending, Header:=xlYes
        .Range("A1:B" & dl).AutoFilter field:=2, Criteria1:=1
        .Range("A2:B" & dl).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .ShowAllData
    End With

ps tu devrais peut-être aussi jeter un oeil du coté de powerquery.

J'ai pas encore regardé ton code je suis sur le document que tu m'as fourni plus haut.

https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions

J'en suis a la partie 3: Allow for extra data plus précisement sur les dynamic range.

J'en utilise à la pelle, avec la fonction offset et si j'ai bien compris je pourrais optimiser en mettant index a la place, mais en regardant sur la fiche EXCEL-PRATIQUE de index on ne peut que renvoyer une cellule dans une autre et non des plages.

https://www.excel-pratique.com/fr/fonctions/fonction_index.php

Je me dit que si je suis les conseils sur les plage dynamique je devrait gagner pas mal de temps, mais je ne sais pas comment appliquer.