Macros du tableur : enregistrement, modèle d'objets, procédures, fonctions et variables (langage VBA)

04Variables, types et affectation

4.1 Déclarer une variable

Une variable est un emplacement mémoire nommé qui contient une valeur le temps de l'exécution. On la déclare avec Dim, en précisant son type :

La ligne Option Explicit, placée en tête du module, oblige à déclarer toutes les variables : une faute de frappe dans un nom est alors signalée au lieu de créer silencieusement une nouvelle variable vide. Une constante se déclare avec Const : Const TVA As Double = 0.2.

Règles de nommage : une lettre au début, pas d'espace ni d'accent dans les noms de variables par prudence, des noms parlants (totalHT plutôt que x).

4.2 L'affectation

L'affectation range une valeur dans une variable ou une propriété. Le signe = y joue le rôle d'une flèche : on calcule le membre de droite, puis on le range dans celui de gauche.

Attention à la double lecture du signe = : en total = total + 1 il s'agit d'une affectation (la nouvelle valeur est l'ancienne plus 1) ; dans un test (If total = 10 Then), c'est une comparaison (cours 33).

4.3 Conventions d'écriture du code

Le code est écrit avec les noms anglais des instructions et des objets, quelle que soit la langue du tableur.

05Les familles d'instructions

5.1 Entrée

Le texte renvoyé par InputBox se convertit pour les calculs : CDbl(saisie) (décimal), CLng(saisie) (entier long). Avec la configuration française, CDbl("49,9") donne 49,9. L'instruction Val ne reconnaît que le point : Val("49,9") donne 49 ; on évite Val pour les nombres saisis à la française. Si l'utilisateur annule ou saisit du texte, la conversion provoque une erreur d'exécution : on la prévient par un test (IsNumeric, cours 33).

5.2 Calcul et cumul

Opérateurs : +, -, *, / (division), ^ (puissance), \ (division entière), Mod (reste de la division entière), & (concaténation de textes).

Un cumul est une affectation de la forme total = total + valeur, précédée d'une initialisation (total = 0) : une variable numérique déclarée vaut 0 au départ, mais on l'écrit explicitement pour que le programme se lise sans ambiguïté.

Attention à l'arrondi : la fonction Round du langage de macros arrondit « au pair » lorsque la décimale est exactement 5 : Round(2.5, 0) donne 2, Round(3.5, 0) donne 4, alors que ARRONDI(2,5;0) du tableur donne 3. Pour les montants, on s'assure du comportement voulu avant de choisir (le principe d'arrondi doit être précisé par l'énoncé).

5.3 Sortie

Dans MsgBox, l'opérateur & assemble du texte et des variables ; un nombre est converti en texte avec la virgule décimale si la configuration est française.

Exemples corrigés

Cas 1 : lire et améliorer une macro enregistrée

Le code de la section 1.3 (MiseEnFormeEntetes) est demandé en lecture. Réponses :

1. Rôle : mettre en gras, centrer et griser la ligne 1 des colonnes A à E, ajuster la largeur de ces colonnes, puis placer le curseur en A2.
2. Effet sur une autre feuille : la macro agit sur la feuille active au moment de l'exécution, puisque les adresses ne précisent aucune feuille.
3. Défaut : si la liste comptait une colonne F, elle ne serait pas traitée.

Version simplifiée équivalente, sans sélection :

Sub MiseEnFormeEntetes()
    With Range("A1:E1")             ' tout ce qui suit s'applique à la plage A1:E1
        .Font.Bold = True           ' gras
        .Interior.Color = 14277081  ' fond gris clair
        .HorizontalAlignment = xlCenter
    End With
    Columns("A:E").AutoFit          ' ajuste la largeur des colonnes
End Sub

À retenir

Conclusion à destination du responsable administratif : la macro enregistrée automatise bien la mise en forme, mais elle ne s'adapte pas à un tableau plus large. Pour un usage durable, on garde l'enregistreur comme point de départ, puis on lit et on nettoie le code ; pour un tableau de longueur variable, on passe à un programme avec boucle (cours 34).

Cas 2 : une procédure avec entrée, calcul et sortie

La cellule B2 de la feuille active contient le taux de TVA (20 %, soit la valeur 0,2).

Option Explicit

Sub CalculerTTC()
    ' Calcule le prix TTC d'un article à partir d'un prix HT saisi
    Dim prixHT As Double        ' prix hors taxe saisi
    Dim tauxTVA As Double       ' taux de TVA lu dans la feuille
    Dim montantTVA As Double    ' montant de la TVA
    Dim prixTTC As Double       ' prix toutes taxes comprises

    tauxTVA = Range("B2").Value                        ' entrée : lecture de la cellule B2
    prixHT = CDbl(InputBox("Prix HT de l'article ?"))  ' entrée : saisie, convertie en nombre
    montantTVA = prixHT * tauxTVA                      ' calcul de la TVA
    prixTTC = prixHT + montantTVA                      ' calcul du TTC
    Range("B4").Value = prixTTC                        ' sortie : écriture dans B4
    MsgBox "TVA : " & montantTVA & " ; TTC : " & prixTTC   ' sortie : message
End Sub

Trace d'exécution (état des variables après chaque instruction ; l'utilisateur saisit 250) :

Vérification : 250 × 0,2 = 50 ; 250 + 50 = 300.

Second jeu d'essai (saisie 49,9) : prixHT = 49,9 ; montantTVA = 49,9 × 0,2 = 9,98 ; prixTTC = 59,88 ; message : « TVA : 9,98 ; TTC : 59,88 » (la configuration française affiche une virgule décimale).

Points de lecture : Option Explicit oblige à déclarer les variables ; CDbl convertit le texte saisi ; si l'utilisateur annule la boîte de saisie, InputBox renvoie un texte vide et CDbl("") provoque une erreur d'exécution (à traiter au cours 33).

À retenir

Conclusion à destination du responsable des devis : la procédure automatise le calcul et affiche le résultat sans toucher à la formule du tableur, et le taux de TVA reste dans une cellule de paramètre, lu à chaque exécution (aucune valeur en dur dans le code, comme dans une formule). Elle ne contrôle pas encore la saisie : c'est l'objet du cours suivant.

Cas 3 : une fonction personnalisée utilisée dans une cellule et dans le code

Function PrixTTC(prixHT As Double, taux As Double) As Double
    ' Renvoie le prix TTC pour un prix HT et un taux de TVA donnés
    PrixTTC = prixHT * (1 + taux)
End Function

Sub AfficherPrix()
    ' Appelle la fonction PrixTTC depuis le code
    Dim ht As Double
    ht = 250
    MsgBox "Prix TTC : " & PrixTTC(ht, 0.2)
End Sub

Dans la feuille : B5 contient 250, Taux_TVA vaut 0,2 ; =PrixTTC(B5;Taux_TVA) renvoie 300. Avec B5 égal à 49,9, le résultat est 59,88 (49,9 × 1,2).

Trace de AfficherPrix : ht = 250 ; l'appel PrixTTC(ht, 0.2) transmet 250 au paramètre prixHT et 0,2 au paramètre taux ; la fonction calcule 250 × (1 + 0,2) = 300 et le range dans PrixTTC ; le message affiche « Prix TTC : 300 ».

À retenir

Conclusion à destination du service comptable : la fonction se comporte comme une fonction du tableur et se recopie dans toute une colonne. Elle centralise la règle de calcul : si la règle change, on la modifie à un seul endroit. Elle ne contient pas de taux en dur, qui est transmis en paramètre.

Cas 4 : affecter un objet et cumuler

La feuille Ventes contient les trois premières ventes en D2, D3 et D4 : 4 200, 1 350 et 6 800.

Option Explicit

Sub CumulerTroisVentes()
    ' Cumule les trois premières ventes de la feuille Ventes
    Dim ws As Worksheet          ' variable de type objet : une feuille
    Dim cumul As Double          ' cumul du chiffre d'affaires

    Set ws = Worksheets("Ventes")            ' affectation d'un objet : Set obligatoire
    cumul = 0                                ' initialisation du cumul
    cumul = cumul + ws.Range("D2").Value     ' 1re vente
    cumul = cumul + ws.Range("D3").Value     ' 2e vente
    cumul = cumul + ws.Range("D4").Value     ' 3e vente
    ws.Range("F2").Value = cumul             ' sortie : écriture dans F2
    MsgBox "Cumul des trois premières ventes : " & cumul
End Sub

Trace d'exécution :

Sortie : F2 contient 12 350 et le message affiche « Cumul des trois premières ventes : 12350 ».

Si le mot Set est oublié (ws = Worksheets("Ventes")), l'instruction provoque une erreur d'exécution : une feuille est un objet, il faut Set. À l'inverse, on n'écrit pas Set pour une valeur simple (cumul = 0).

À retenir

Conclusion à destination du responsable du contrôle de gestion : le programme est correct mais ne traite que trois lignes ; pour cumuler toute la liste, quel qu'en soit le nombre, on remplace les trois lignes de cumul par une boucle (cours 34). Le résultat est conforme à la somme manuelle 4 200 + 1 350 + 6 800 = 12 350.

Vocabulaire essentiel

Points clés à retenir

1. L'enregistreur produit un code verbeux (Select puis Selection) et figé sur des adresses : on le lit et on le nettoie.
2. Le modèle d'objets s'écrit du général au particulier : classeur, feuille, plage ; Cells(ligne, colonne) donne la ligne d'abord.
3. Une propriété se lit ou se modifie ; une méthode exécute une action.
4. Une procédure agit ; une fonction renvoie une valeur en affectant son nom.
5. On déclare les variables avec Dim et un type ; Option Explicit rend la déclaration obligatoire.
6. Valeur : x = ... ; objet : Set x = ....
7. Dans le code : virgule entre arguments, point décimal ; dans une cellule : point-virgule et virgule décimale.
8. InputBox renvoie du texte à convertir ; Round du langage arrondit au pair (Round(2.5, 0) donne 2).

Pièges fréquents

1. Oublier Set pour affecter un objet (ou le mettre pour une valeur simple).
2. Confondre Cells(2, 4) et D2/B4 : la ligne vient en premier.
3. Écrire 0,2 dans le code : le séparateur décimal du code est le point.
4. Séparer par un point-virgule les arguments dans le code ou par une virgule dans une cellule.
5. Oublier de convertir le texte de InputBox avant un calcul.
6. Utiliser Val pour un nombre saisi avec une virgule : Val("49,9") donne 49.
7. Lire = comme une égalité dans une affectation : total = total + 1 n'est pas une équation.
8. Supposer que Round arrondit comme ARRONDI : le langage arrondit « au pair ».

Q&R pour le tuteur IA

Q : Quelle est la différence entre une procédure (Sub) et une fonction (Function) ? R : Une procédure exécute des actions et ne renvoie rien. Une fonction renvoie une valeur, obtenue en affectant le résultat au nom de la fonction ; elle s'utilise dans une cellule comme une fonction du tableur.

Q : Que désigne Cells(3, 2) ? R : La cellule située à la ligne 3, colonne 2, c'est-à-dire B3. La ligne est toujours donnée en premier.

Q : Pourquoi écrit-on Set ws = Worksheets("Ventes") et non ws = ... ? R : Parce que ws est une variable d'objet : l'affectation d'un objet exige le mot Set. Pour une valeur simple (nombre, texte), on écrit l'affectation sans Set.

Q : Que fait total = total + montant ? R : C'est un cumul : on calcule la somme de la valeur actuelle de total et de montant, puis on range le résultat dans total. La variable doit avoir été initialisée (total = 0) avant le premier cumul.

Q : Comment lire le prix saisi par l'utilisateur et l'utiliser dans un calcul ? R : Avec InputBox, qui renvoie du texte, converti par CDbl : prix = CDbl(InputBox("Prix HT ?")). On prévoit le cas d'une saisie vide ou non numérique, qui provoquerait une erreur.

Q : Pourquoi Round(2.5, 0) ne donne-t-il pas 3 ? R : Parce que la fonction Round du langage arrondit au nombre pair le plus proche quand la décimale est exactement 5 : 2,5 donne 2 et 3,5 donne 4. Le tableur, avec ARRONDI, arrondit 2,5 à 3. L'énoncé doit préciser la règle à appliquer.