Excel - Power Query - Power Pivot - VBA

VBA10 | Automatiser le calcul des montants

Énoncé

  • Thème : premier lab complet dans Excel | Durée : 40 min | Niveau : débutant à intermédiaire.
  • Classeur de départ : VBA10_depart.xlsx, feuille Ventes (Réf, Désignation, Quantité, Prix HT pour 8 articles).
  • Macro à écrire : CalculerMontants.

Travail demandé

  • Afficher l'onglet Développeur (Fichier > Options > Personnaliser le ruban).
  • Ouvrir l'éditeur Visual Basic (Alt+F11) et insérer un module standard Module1.
  • Écrire la macro CalculerMontants : titre Montant HT en gras dans E1, puis Quantité × Prix HT sur chaque ligne jusqu'à la dernière ligne remplie, et un message final.
  • Exécuter la macro avec F5, puis la suivre pas à pas avec F8.
  • Surligner en jaune les montants supérieurs à 8 000 € (et effacer la couleur des autres).
  • Lancer la macro depuis Excel avec la boîte Macro (Alt+F8), puis depuis un bouton de formulaire.
  • Enregistrer le classeur sous VBA10_corrige.xlsm.

Corrigé

Sub CalculerMontants()
    Dim ligne As Long, derniere As Long
    derniere = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E1").Value = "Montant HT"
    Range("E1").Font.Bold = True
    For ligne = 2 To derniere
        Cells(ligne, 5).Value = Cells(ligne, 3).Value * Cells(ligne, 4).Value
        If Cells(ligne, 5).Value > 8000 Then
            Cells(ligne, 5).Interior.Color = vbYellow
        Else
            Cells(ligne, 5).Interior.ColorIndex = xlNone
        End If
    Next ligne
    MsgBox derniere - 1 & " montants calculés"
End Sub
  • Cells(Rows.Count, 1).End(xlUp).Row donne la dernière ligne remplie : la macro reste juste si des ventes sont ajoutées.
  • Le Else efface la couleur : relancer la macro après une correction de quantité donne toujours un résultat juste.
  • Quatre montants dépassent 8 000 € et sont surlignés : ART-0001 (8 767,68), ART-0004 (8 845,45), ART-0007 (9 604,20) et ART-0008 (10 953,90).
  • Un classeur .xlsx ne peut pas contenir de macros : il faut le format « Classeur Excel (prenant en charge les macros) » (.xlsm).

Pour aller plus loin