Changer une macro avec une autre macro

Question

Je viens juste de finir de coder une macro pour mon entreprise, et je me demandais s'il était possible de modifier le code d'une macro (ou plutôt d'un sub) par une autre macro ? Cordialement Maxime...

MFerrand · Accepted Answer

Pour les éléments suivants je procéderai à partir d'exemples de ton code...

Cela ne suivra donc pas forcément un ordre logique, mais de toute façon je ne saurais faire un exposé exhaustif de tous les points qui mériteraient discussion, mais cela permettra d'aborder quelques éléments plus concrètement et d'élargir la façon de les envisager.

BoEcran = Application.ScreenUpdating
BoBarre = Application.DisplayStatusBar
iCalcul = Application.Calculation
BoEvent = Application.EnableEvents
BoSaut = ActiveSheet.DisplayPageBreaks

On procède de cette façon lorsque l'on modifie l'interface utilisateur à l'ouverture et que l'on aura (c'est au moins une obligation déontologique) à la rétablir à la fermeture. On conserve donc l'état des éléments d'interface que l'on modifie temporairement dans des variables (de niveau module, pour les retrouver ensuite).

Seulement, ces éléments ne sont pas (sauf un) des éléments de l'interface utilisateur ! Et en outre ce n'est pas ton propos de modifier l'interface ! Tu ne cherches ce faisant qu'à gagner du temps d'exécution...

Ces variables et ce code sont donc purement et simplement inutiles dans ton contexte ! La suppression te fera gagner de la place et du temps...

Mais voyons de plus près : on peut abandonner sans remord la barre d'état et les sauts de page, sans aucun intérêt pour ce qui nous occupe, pour le mode de calcul il peut arriver qu'il devienne nécessaire de le passer en manuel, ce peut être le cas avec des fichiers ultra-lourds, bourrés de milliers de lignes de formules, pour lesquelles le recalcul est systématiquement long et perturbe en permanence l'activité (note qu'il perturbera alors plus l'utilisation manuelle que l'exécution des macros !). Mon conseil face à de tels classeurs serait d'abord d'éviter d'en créer... Dans un classeur normal avec des procédures normales, je n'ai jamais vu qu'il devienne nécessaire d'inhiber le recalcul, je ne l'ai jamais fait et au contraire à diverses reprises j'ai eu à forcer le recalcul dans le code pour garantir la mise à jour de valeurs calculées durant l'exécution. Je ne saurais exclure que cela puisse s'avérer nécessaire dans certains cas, mais cela me paraît tout à fait exceptionnel et tout cas pas de mise dans un classeur où le nombre de formules est tout à fait réduit.

Interrompre les évènements : oui, c'est nécessaire lorsque l'on a mis en place des procédures d'évènements qui peuvent être déclenchées hors des cas que l'on a prévu et provoquer des résultats inattendus qu'il vaut mieux éviter. C'est à proscrire sinon ! Or, on ne voit pas quels évènements intempestifs pourraient être déclenchés par ton code, d'autant moins que tu n'as aucune procédure d'évènement... !

Il reste l'interruption de la mise à jour de l'affichage. Cela a effectivement un impact sur la vitesse d'exécution, et on a à l'utiliser fréquemment... Il convient de le faire lorsque l'action en cours est susceptible de modifier l'affichage (on écrit sur la feuille active, ou on en modifie certains éléments). Mais inutile de le faire quand ce n'est pas le cas, et de le faire systématiquement en début de procédure (on le fait au moment où cela devient nécessaire). Dans ton cas, on peut considérer que l'effacement des données sur la feuille active au moment du lancement de la macro est un indicateur pour l'utilisateur que la proc. est bien lancée, et donc laisser cet effacement se mettre à jour, pour n'inhiber l'affichage qu'ensuite lorsque l'on aura à intervenir sur cette feuille demeurée active. Si l'on intervient sur une autre feuille cela n'aura aucun effet sur l'affichage. De même si on fait en sorte de limiter au maximum les interactions avec les composants d'Excel en travaillant avec des tableaux (hors Excel) pour n'y revenir qu'avec les résultats finaux.

La commande :

    Application.ScreenUpdating = False

suffit lorsque l'on procède à cette manoeuvre.

Il n'est nul besoin de la rétablir à True, c'est automatique dès que la macro s'est terminée.

On peut avoir besoin de la rétablir à True en cours d'exécution si l'on veut afficher avant la fin des données à jour à l'intention de l'utilisateur (quite éventuellement à réinhiber ensuite).

Noter que outre ScreenUpdating, DisplayAlerts (interruption des messages d'alertes, l'action se poursuit alors en général comme si l'utilisateur avait actionné le bouton par défaut de la boîte de dialogue, ne concerne pas les messages de sécurité qui ne peuvent être interrompus), est une propriété qui définie à False revient automatiquement à True en fin de procédure.

On peut donc se dispenser d'écrire des lignes de commande dont l'exécution sera assurée sans.

Autre élément :

Sheets("Gantt").Range("A4:XFD1048576").ClearContents
Sheets("Gantt").Range("A4:XFD1048576").Interior.ColorIndex = xlColorIndexNone
Sheets("Gantt").Range("A4:XFD1048576").ClearFormats

    Sheets("Gantt").Range("A4:XFD1048576").Clear

aurait été plus simple pour le même résultat.

Tu y viendras avec une meilleure connaissance des méthodes des divers objets...

Autre chose :

If Sheets("Asana").Cells(lg, 1) = Empty Then lg = lg + 1
If Sheets("Asana").Cells(lg, 1) = Empty Then lg = lg + 1
If Sheets("Asana").Cells(lg, 1) = Empty Then lg = lg + 1
If Sheets("Asana").Cells(lg, 1) = Empty Then lg = lg + 1
If Sheets("Asana").Cells(lg, 1) = Empty Then lg = lg + 1
If Sheets("Asana").Cells(lg, 1) = Empty Then lg = lg + 1

Ces énumérations font mal, c'est fastidieux et ça plombe un code...

Pour l'exemple (car j'ai quelques doutes sur l'utilité de ce que fait ce bout de code, ainsi que je l'ai déjà exprimé), une autre façon d'écrire cette action :

    With Sheets("Asana")
        For i = 1 To 6
            If IsEmpty(.Cells(lg, 1)) Then lg = lg + 1 Else Exit For
        Next i
    End With

Utilisation d'un bloc With : je conseille systématiquement chaque fois qu'on le peut car cela simplifie l'écriture en évitant des répétitions, et en même temps (et surtout) cela accélère l'exécution par mise en mémoire de la référence d'objet.

Utilisation de boucle : cela aère et réduit l'écriture, il ne faut surtout pas s'en priver (et cela peut parfois aller plus vite.

VBA nous fournit une fonction IsEmpty pour tester la valeur Empty ! Pourquoi ne pas l'utiliser... Il faut noter que seules les variables de type Variant peuvent prendre cette valeur Empty. Cela peut également s'appliquer aux cellules de feuilles de calcul qui peuvent accueillir tout type de donnée à l'instar des variables Variant.

Exit For pour sortir de la boucle dès lors que la condition n'est plus satisfaite, lg ne sera plus incrémentée, inutile de poursuivre la boucle pour rien (perte de temps...) A noter que l'instruction décisionnelle étant ici écrite sur une seule ligne cela dispense de l'instruction de fin : End If.

Il y a sûrement d'autres points à soulever, mais cela nécessiterait d'entrer plus avant dans la conception de ton projet, il me semble notamment déceler beaucoup d'actions répétées mais je ne peux dire comment on pourrait faire autrement sans maîtriser l'ensemble, ce qui réclamerait un travail de plus longue haleine...

Je m'en tiens donc là, mais reste ouvert à toute discussion sur un point ou un autre...

Cordialement.

thebenoit59 · Answer

Que souhaites-tu modifier dans le code ...

MFerrand · Answer

Tu peux commencer par reproduire ta macro, et expliquer les fonctionnalités qu'elle doit remplir selon tes souhaits et qui ne s'y trouvent pas ! Si la chose peut être faite sans se référer au fichier, tu auras des réponses utiles, sinon il faudra en passer par le fichier d'illustration afin que notre faculté de compréhension s'active.....

MFerrand · Answer

Elle m'a l'air quelque peu à rallonge !

Elle va donc attendre que j'ai le temps de la lire, d'autant plus qu'elle n'est pas indentée (et que le temps de lecture sera conséquemment plus long ! )

Si tu pouvais décrire ce qu'elle est censée faire, cela devrait faciliter l'analyse et aider à réduire le code...

Cordialement.

James007 · Answer

Bonjour à tous, D'un point de vue purement programmatique de VBE ... la réponse est affirmative ... Mais pour pouvoir implémenter ce genre de code ... il faut un certain degré de familiarité avec l'outil ... Et surtout ... avec de plonger dans cette zone ... il arrive très souvent qu'une solution plus simple et plus légère soit beaucoup plus facile à mettre en oeuvre ... Donc avant de préjuger de la solution adéquate ... tu as intérêt à très précisément expliciter ton objectif .....

ExcelCoreGame · Answer

Je ne savais même pas qu'on pouvait modifier une macro avec une autre macro J'ai ouvert le fichier mais je ne vois rien du tout sur tes due date mais clair que Mferrand a raison ! Après en terme de conception, tu t'es fait avoir en faisant une macro qui prends en compte un nombre fixe de Due Date je dirais , modifier un peu le code pour boucler sur toute les due date qui existent c'est possible? et si vraiment l'emplacement ou l'enregistrement des due date est compliqué au point de ne pas laisser n'importe qui le faire, tu fais un bouton pour ajouter des due date via un formulaire ou du genre. ECG...

James007 · Answer

@ ExcelCoreGame ...

Un des délires ...c'est quand tu écris une macro ... qui va écrire une macro ...

Mais ... faut être honnête ... ce chemin ne s'impose que dans de très très rares circonstances ...

MFerrand · Answer

Je partage pleinement cet avis ...

ExcelCoreGame · Answer

Pardon je suis d'accord avec la citation de James (et pas de Mferrand, même si je suis d'accord avec ce qu'il dit aussi mais je me suis trompé de personne) ^^ ECG...

MFerrand · Answer

Re,

On ne peut pas dire que tu aides beaucoup en étant aussi sybillin !

Je commence donc à lire ta procédure : il faut un certain temps déjà pour arriver au bout des déclarations de variables... Je suis tout à fait pour déclarer toutes des variables, mais quand j'en vois autant, je m'inquiète un peu de ce que va être la suite, surtout que quelques lignes de code exécutable flanquées au milieu sont inutiles, à une près, qui sera peut-être utile par la suite (mais au stade où j'en suis arrivé, son utilité n'étant pas encore effective, ça pouvait attendre...

Enfin, on va peut-être entrer dans le vif du sujet : effacement de Gantt... 3 lignes de code pour effacer les valeurs, les couleurs, les formats, une suffisait pour ce faire, mais continuons.

Initialisation de variable encore, et une étiquette de branchement : on y était pas encore.

On passe sur Asana : 6 lignes qui ajoutent 1 à la variable si la cellule en A est vide (on ne connait pas les boucles !), puis une question qui me demande si je colle au bon endroit, je réponds oui... et on recommence, ce qui peut durer un certain temps, jusqu'à ce que la variable (Integer) dépasse 32767 et j'aurai alors une erreur 6... Je réponds non, on sort. Je ne suis guère avancé après ça.

Vu la longueur du code, si des épisodes du même type se succèdent, je ne suis pas sûr du tout d'aller au bout... De toute façon comme au départ, si on actionne le bouton, il se passe ce que j'ai décrit, il manque visiblement quelque chose quelque part, et une signalisation pour m'informer que les conditions pour que mon appui sur le bouton ait une certaine efficacité ne sont pas réunies !

MFerrand · Answer

En 3 mois il n'y a pas de miracle, et on ne t'en voudra pas de ne pas produire un code optimal au premier jet, d'autant que le caractère optimal dépend de facteurs dont certains ne seront pas maîtrisables à l'avance et lorsque l'on se lance dans l'optimisation (dans les cas on l'on peut avoir des raisons de penser que le résultat pourrait être meilleur), seuls les tests en situation permettent d'établir si l'on peut considérer avoir atteint le but fixé... Il y a quelques règles à connaître, que tu assimileras au fur et à mesure, et l'enseignement résultant de la pratique... il faut toujours du temps pour s'améliorer... J'insisterai pour ma part d'abord sur la mise en forme du code, en précisant toutefois d'emblée que cela n'a rien à voir avec l'optimisation, cela ne concerne que le confort de travail du programmeur (mais j'ai la faiblesse de penser, lorsque c'est moi qui m'attaque à un code, que c'est un élément particulièrement important ! ) VBA s'en fout de la présentation du code : tant qu'il peut exécuter, il exécute, s'il ne peut pas il bloque : il convient d'ailleurs de distinguer les erreurs de compilation intervenant à l'écriture (de la syntaxe élémentaire en principe mais cependant pas toujours évidente) et celles survenant à l'exécution (qui peuvent être des erreurs de syntaxe également, mais aussi des erreurs liées aux données manipulées au moment de l'exécution), et ce n'est pas toujours VBA qui bloque, ce peut être Excel (typiquement les erreurs 1004 : c'est Excel qui refuse de laisser exécuter une commande, soit elle est inadéquate, soit Excel la refuse dans le contexte, soit Excel la refuse on ne sait pourquoi et on substitue une commande similaire qui sera acceptée...) En la matière, les erreurs sont utiles, elles permettent d'accroître ses connaissances et de s'améliorer... Pour revenir à la mise en forme, j'ai eu l'idée (bonne ou mauvaise ou saugrenue, c'est selon ) d'évaluer le temps passé à lire une procédure selon qu'elle était ou non indentée. Par lire, j'entends interpréter simultanément ce que fait le code en le lisant. Je suis parvenu à la conclusion qu'il fallait en moyenne 4 fois plus de temps pour lire du code non indenté, en raison des retours en arrière que l'on est sans cesse obligé d'opérer... (et pour du code particulièrement brouillon c'est à mon avis de l'ordre de 10 fois plus !) Ce qui m'a mis en rage ! Car perdre du temps, je le fais volontiers volontairement ! mais le perdre dans de la lecture de code c'est une perte sans aucun profit... D'où je suis devenu quelque peu maniaque (je le reconnais volontiers ! ) sur cette question. L'indentation est pourtant relativement simple : elle consiste à inscrire les deux lignes de déclarations de procédures (Sub... End Sub, Function... End Function, etc.) sans retrait (ce que VBA fait automatiquement quand on ne l'en empêche pas) et mettre tout le code à l'intérieur de la procédure en retrait d'une tabulation, sauf les étiquettes de branchements qui seront sans retrait (que VBA ramène automatiquement à la marge) et que l'on n'aura ainsi aucun mal à distinguer au coup d'oeil sans lire. Il est à la mode de placer les déclarations de variables sans retrait (et en liste plus ou moins longue). Ce n'était pas le cas lors de mon apprentissage, et j'avoue que cela me gêne un peu, je préfère les avoir en retrait et plus compactées (en utilisant les caractères de déclarations de types existants pour les variables numériques %, &amp;, !, #, @ et String $ , qui permettent de les raccourcir), mais je veux bien l'admettre comme variante dès lors que le code exécutable est lui systématiquement en retrait. Cela consiste ensuite, chaque fois que l'on a des instructions composées d'un mot-clé de début et d'un mot-clé de fin (et éventuellement de mots-clés intermédiaires), à placer le code interne à ces instructions en retrait, et lorsque l'on a des instructions imbriquées, on aura des retraits successifs : With... End With, Do... Loop, For... Next, If... ElseIf... Else... End If, Select Case... End Select etc. Cela permet généralement de détecter au coup d'oeil des erreurs potentielles dues notamment à l'omission de fins d'instructions. Ceci étant dit, j'en viens au code proprement dit : ce qui m'a d'abord frappé c'est l'abondance des variables... En la matière, il faut ce qu'il faut, mais l'optimisation commence par être économe sur tous les éléments utilisés dès le départ. Exemple, j'ai besoin de 6 variables de boucles (qui seront donc Integer ou Long selon le cas), mais je n'ai pas 6 boucles imbriquées nécessitant l'utilisation de 6 variables simultanées, mes boucles vont se dérouler tour à tour, une seule variable va suffire pour toutes les boucles en l'utilisant tour à tour pour chacune ! On est certes moins contraint par la capacité mémoire qu'il y a 25 ans, mais justement, ayant des ressources supplémentaires, elles seront d'autant mieux utilisées et avec plus d'efficacité qu'on ne les dilapidera pas à tour de bras. Il y a donc je pense lieu à avoir une réflexion plus approfondie sur l'utilisation des variables. J'insiste aussi sur la déclaration des variables systématiquement en tête de procédure (et non dispersée au milieu de code exécutable. Ce n'est pas qu'une convention d'écriture, les réservations de mémoire seront faites dans le même temps, cela pourra éventuellement faire gagner du temps mais en tout cas cela évitera d'en perdre. Le second élément qui m'a frappé ensuite c'est la longueur de la procédure, assortie d'un usage quelque peu immodéré de déplacement dans la procédure vers des étiquettes de branchements. C'est une façon quelque peu ancienne de programmer, d'avant la programmation orientée objet : code unique à l'intérieur duquel on délimite des sous-programmes... On y préfére un type de programmation modulaire, les sous-programmes faisant l'objet de procédures distinctes, appelées selon besoins à partir d'une procédure principale : on retrouve plus facilement la colonne vertébrale de l'application dans une proc. principale plus réduite, et on appréhende façon aisée des procédures secondaires accomplissant chacune des tâches plus réduites et homogènes, et selon l'articulation on pourra vraisemblablement avoir une meilleure utilisation de la mémoire. Pause avant de passer au 3e élément.....