Séance n°1 : Les macros enregistrées

Cours de M. Emmanuel DUBOIS

Vous disposez d’un classeur Excel contenant des cours récents sur l’indice CAC ALL SHARES et ses composants en vue d’une étude sur la constitution d’un portefeuille de valeurs cotées sur la place de Paris.

Il est téléchargeable à l’adresse suivante :

CACALLSHARES.zip

ou, ci-dessous en version décompressée.

CACALLSHARES.xlsx

Le problème est que les données présentes ne sont pas adaptées à un traitement immédiat :

1. Un certain nombre de données sont regroupées au sein de la première colonne

2. Les dates de séance ne sont pas reconnues

3. Les valeurs numérique, entières ou décimales, sont en fait des textes avec le point comme séparateur de décimales.

A priori, la fonction Ruban Données→ Convertir peut traiter tous ces problèmes, son seul inconvénient étant de ne pouvoir travailler que sur une colonne à la fois, or vous devez traiter 9 colonnes sur 495 pages…

Comme cela promet d’être fastidieux, vous décidez d’enregistrer une macro-commande pour accélérer votre travail.

A – Création d’une macro-commande

1 – Sélectionnez le second onglet du classeur nommé QS0010989141-XPAR.

2 – Lancez l’enregistrement (bouton en bas à gauche de l’écran ou Affichage / Macros / Enregistrer une macro ou Développeur / Enregistrer une macro)d’une macro nommée Correction, associée à la touche CTRL + MAJ + w à stocker dans le classeur courant.

3 – Sélectionnez la plage A1:A5 en vue de découper ses entrées, puis lancez la commande Données / Convertir

Dans un premier écran de l’assistant de conversion, choisissez la case d’option Délimité puis le bouton Suivant
Le séparateur choisi sera le symbole : , précisé dans la section Autre, puis vous pourrez choisir le bouton Terminer

4 – Sélectionnez la colonne B en vue de faire reconnaître les dates que contiennent certaines de ses cellules en faisant à nouveau Données / Convertir

On choisit à nouveau le type Délimité puis le bouton suivant
Le séparateur peut être quelconque (par exemple le caractère de tabulation), et peut tout être sauf un espace ou un tiret. Poursuivez par le bouton Suivant
Choisir comme format des données en colonne Date en sélectionnant l’option AMJ (année, mois, jours) dans la liste puis cliquez sur le bouton Terminer.

5 – Sélectionnez la colonne C en vue de faire reconnaître les valeurs numériques utilisant le point comme séparateur de décimales et réitérant la commande Données / Convertir

Choisissez encore Délimité comme type de données puis actionnez le bouton Suivant.
Le séparateur reste le caractère de Tabulation et vous enchainez à nouveau avec le bouton Suivant.
Le format des données reste Standard et vous cliquez sur le bouton Avancé pour choisir les séparateurs de décimales et de milliers
Inscrivez un point . comme séparateur de décimale puis cliquez sur OK.
Vous pouvez alors fermer l’assistant de conversion par un appui sur le bouton Terminer.

6 - Arrêtez l’enregistrement de la macro (bouton en bas à gauche de l’écran d’Excel ou Affichage / Macros / Arrêter l’enregistrement ou Développeur / Arrêter l’enregistrement)

B – Utilisation d’une macro commande

1 – Lancement par la séquence de touche initialement configurée

Sélectionnez la 3^e feuille nommée FR0000076887-XPAR puis lancez la séquence de touche CTRL + MAJ + W.

Vérifiez que les 3 premières colonnes de la feuille sont désormais instantanément corrigées.

2 – Lancement par menu

Sélectionnez la 4^e feuille nommée FR0010557264-XPAR puis lancez la commande Affichage / Macros / Afficher les macros ou Développeur / Macros, choisissez la macro Correction puis choisissez le bouton Exécuter.

Vérifiez que les 3 premières colonnes de la feuille sont désormais instantanément corrigées.

B – Modification d’une macro commande

Il apparaît désormais utile de corriger également les colonnes D à I des feuilles du classeur

Or toute macro, y compris une macro enregistrée, ne peut être modifiée que dans l’environnement de développement Visual basic mis à la disposition par les commandes Développeur / Visual Basic.

Pour cela lancez la commande Affichage / Macros / Afficher les macros ou Développeur / Macros, choisissez la macro Correction puis choisissez le bouton Modifier.

Vous pouvez alors prendre connaissance du code VBA enregistré pour votre macro et constatez que cela forme des commandes tout à fait compréhensibles pour peu que l’on ait des rudiments d’anglais.

En cas de difficulté éventuelle de compréhension, il est possible d’utiliser les commandes de mise au point, notamment les commandes du menu Débogage pour exécuter l’une après l’autre les différentes instructions.

1 / A la suite d’une lecture attentive de la macro-commande enregistrée, supprimez (ou commentez en cas de doute) les éventuelles commandes qui vous apparaissent avoir été enregistrées inutilement ou les actions inutilement enregistrées.

2 / Recopiez en 7 exemplaires les deux lignes qui précèdent le End Sub en actualisant Columns("C:C").Select en Columns("D:D").Select et en actualisant Selection.TextToColumns Destination:=Range("C1") en Selection.TextToColumns Destination:=Range("D1") et ainsi de suite pour les colonnes suivantes pour obtenir une macro correspondant à ce qui suit.

Sub Correction()
'
' Correction Macro
'
' Touche de raccourci du clavier: Ctrl+w
'
    Range("A1:A5").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 5), TrailingMinusNumbers:=True
    Range("B1").Select
    ActiveWindow.SmallScroll Down:=-12
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
    Columns("H:H").Select
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
    Columns("I:I").Select
    Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=":", FieldInfo:=Array(1, 1), DecimalSeparator:=".", TrailingMinusNumbers _
        :=True
End Sub

Vérifiez sur la 2^e feuille QS0010989141-XPAR que vous avez désormais une macro qui corrige la totalité de la page.

3 / Vous souhaiteriez désormais faire en sorte que les nombres utilisent un séparateur de milliers.

Pour cela enregistrez une macro-commande nommé Milliers dans laquelle vous sélectionnez les colonnes C à I, appliquez un format de nombre incluant un séparateur de milliers et 2 décimales, puis sélectionnez la cellule A1.

Vous obtiendrez alors une macro ressemblant à ce qui suit :

Sub Milliers()
'
' Milliers Macro
'
    Columns("C:I").Select
    Selection.NumberFormat = "#,##0.00"
    Range("A1").Select
End Sub

Vous pouvez alors compléter la macro Correction,

- soit en recopiant les commandes utiles ci-dessous juste avant son End Sub :

    Columns("C:I").Select
    Selection.NumberFormat = "#,##0.00"
    Range("A1").Select

- Soit par une simple saisie du nom de la macro juste avant son End Sub.

Milliers

Testez finalement le bon fonctionnement de la désormais complète macro Correction sur la 3^e feuille FR0000076887-XPAR.

C – Généralisation par programmation

Nous savons désormais que nous pourrions obtenir un classeur propre en réalisant 495 sélections de feuilles suivies de 495 appui sur CTRL + MAJ + W

Pour gagner du temps, nous pourrions enregistrer une macro FeuilleSuivante qui enregistrerait le passage à l’onglet suivant par séquence de touche Excel CTRL + Page vers le bas, ce qui donnerait une commande ActiveSheet.Next.Select à intégrer en fin de la macro correction qui nous ferait encore gagner du temps en supprimant la nécessité des 495 sélections de feuilles.

Toutefois, nous allons faire une incursion en mode programmé pour découvrir ce qu’apporte la programmation VBA en créant de toutes pièces, à la suite de la macro correction, la macro suivante :

Sub correctionGlobale()
For Each s In Worksheets
    If s.name <> "CAC ALL SHARES" Then
        s.Activate
        Correction
        End If
    Next
    MsgBox "Correction terminée"
End Sub

En exécutant la macro CorrectionGlobale, vous constaterez ainsi que l’avantage de savoir programmer permet, entre autres, de créer rapidement des actions dotées d’un raisonnement logique (ici éviter la feuille CAC ALL SHARES), itératives (ici la correction est itérée sur tous les onglets) et interactives (message de fin).

D – Enregistrement du fichier

Bien que le fichier soit corrigé, si vous souhaitez conserver vos macros pour l’avenir, il vous faudra enregistrer désormais votre classeur dans l’un des formats suivants :

- Classeur Excel (prenant en charge les macros) (*.xlsm) format recommandé

- Classeur Excel binaire (*.xlsb), format plus compact mais moins portable

- Classeur Excel 97-2003 (*.xls), non recommandé car obsolète

- Macro complémentaire Excel (*.xlam), peu pertinent dans le cas présent car les feuilles de calcul, lourdes et utiles dans le classeur présent, seraient désormais masquées pour ne donner accès qu’aux seules macros.