Introduction

L’objectif de cette activité est de s’initier au VBA pour manipuler une feuille de calcul.

L’exemple est celui d’une simulation de remboursement d’emprunt bancaire selon la méthodes de l’annuité constante.

Les exercices portent sur l’amortissement comptable.

Le code est compatible avec Calc (LibreOffice) et Excel grâce à la bibliothèque de compatibilité (cf fonctions GetValue, SetValue et EraseValue).

Ressources

  • Mémento du langage Basic.
  • Tutoriel facultatif.
  • Bibliothèque de compatibilité.

Initiation

Mise en place de la feuille de calcul

A B
1 Montant 150 000 €
2 Durée 15
3 Taux 3%

…

A B C D E
6 Année Capital dû Annuité Remb. Intérêts
7 …
31
32 Coût =somme(E6:E31)

Partie VBA

  • Créer un module en langage “Basic” pour ce fichier, le nommer Compat et y copier le code de la bibliothèque de compatibilité. Cf menu Outils → Macros.
  • Créer un autre module et y copier les fonctions (et procédures) suivantes :
Function Arrondir(valeur As Double) As Double
    Arrondir = Int(valeur * 100) / 100
End Function

Sub RemboursementAnnuiteConstante(feuille As Integer)
    Const LIGNE as Integer = 6
    Dim montant, duree, taux as Double
    Dim annuite, annee, interets, remboursement, capitalDu as Double

    montant = GetValue(feuille, "B1")
    duree = GetValue(feuille, "B2")
    taux = GetValue(feuille,"B3")
    annuite = montant * taux / (1 - (1 + taux)^-duree)
    capitalDu = montant

    For annee = 1 To duree
        interets = capitalDu * taux
        remboursement = annuite - interets
        SetValue feuille, "A" & CStr(LIGNE + annee), annee
        SetValue feuille, "B" & CStr(LIGNE + annee), Arrondir(capitalDu)
        SetValue feuille, "C" & CStr(LIGNE + annee), Arrondir(annuite)
        SetValue feuille, "D" & CStr(LIGNE + annee), Arrondir(remboursement)
        SetValue feuille, "E" & CStr(LIGNE + annee), Arrondir(interets)
        capitalDu = capitalDu - remboursement
    Next
End Sub

sub EffacerEmprunt(feuille As Integer)
    Const LIGNE as Integer = 6
    Const DUREE_MAX as Integer = 25
    Dim annee as Integer
    For annee = 1 To DUREE_MAX
        EraseValue feuille, "A" & CStr(LIGNE + annee)
        EraseValue feuille, "B" & CStr(LIGNE + annee)
        EraseValue feuille, "C" & CStr(LIGNE + annee)
        EraseValue feuille, "D" & CStr(LIGNE + annee)
        EraseValue feuille, "E" & CStr(LIGNE + annee)
    Next
end sub

Partie IHM

Ajouter 2 boutons sur la feuille de calcul :

  • pour lancer le calcul (exécute la macro RemboursementAnnuiteConstante) ;
  • pour effacer le tableau de remboursement (macro EffacerEmprunt)

Bibliothèque de compatibilité

L’API Excel diffère de celle de LibreOffice pour notamment accéder aux cellules. Afin de permettre la compatibilité entre les deux suites bureautiques, une bibliothèque de compatibilité est utilisée ; elle comprend La bibliothèque de compatibilité offre (entre autres) les fonctions suivantes :

  • procédure SetValue (no_feuille: entier, cellule: chaîne, valeur) ; exemple : SetValue 0, "F1", "Test" ; contrairement aux fonctions, pour utiliser une procédure, il ne faut pas mettre de parenthèses autour des paramètres ;
  • procédure EraseValue (no_feuille: entier, cellule: chaîne) ; exemple : EraseValue 0, "F1", "Test" ;
  • fonction GetValue (no_feuille: entier, cellule: chaîne): valeur ; exemple : v = GetValue(0, "F1") ;

Exercices

Présentation

Lorsqu’une organisation acquiert un bien utile à l’exercice de son activité (comme un véhicule, une machine ou un serveur informatique par exemple), elle doit l’amortir, c’est à dire en calculer la perte de valeur annuelle.

Par exemple, un véhicule acheté 10000 €, amorti sur 5 ans, perdra chaque année 2000 € de valeur (en linéaire).

Il existe 2 principales méthodes d’amortissement :

  • la méthode linéaire : l’amortissement annuel = valeur initiale / durée de vie ;
  • l’amortissement dégressif ou l’amortissement est plus important les premières années (cf algorithme).

L’objectif de cette activité est de créer un classeur permettant de calculer l’amortissement d’un bien ; l’utilisateur saisit :

  • la valeur initiale,
  • la durée de conservation ;

puis lance le calcul en cliquant sur un bouton.

Code de l’amortissement linéaire

Sub AmortissementLineaire()
    Const FEUILLE as Integer = 1
    Const LIGNE as Integer = 4
    Dim montant, amort, cumul As Single
    Dim duree, n As Integer

    montant = getvalue(FEUILLE, "B1")
    duree = getvalue(FEUILLE, "B2")
    amort = montant / duree
    cumul = 0
    For n = 1 To duree
        cumul = cumul + amort
        setvalue FEUILLE, "A" & CStr(LIGNE + n), n
        setvalue FEUILLE, "B" & CStr(LIGNE + n), montant
        setvalue FEUILLE, "C" & CStr(LIGNE + n), amort
        setvalue FEUILLE, "D" & CStr(LIGNE + n), cumul
        montant = montant - amort
    Next
End Sub

Algorithme de l’amortissement dégressif

procédure AmortissementDegressif()
  montant = lire_cellule (B3)
  duree = lire_cellule (B4)

  si (duree < 5)
  alors coef = 1.25
  sinon si (duree < 7)
    alors coef = 1.75
    sinon coef = 2.25
    finsi
  finsi

  taux_deg = coef / duree
  cumul = 0
  pour n variant de 1 à duree
    taux_lin = 1 / (duree - n + 1)
    si (taux_lin > taux_deg)
    alors amort = montant * taux_lin
    sinon amort = montant * taux_deg
    finsi
    cumul = cumul + amort

    afficher (montant)    #préciser feuille, ligne et colonne
    afficher (amort)
    afficher (cumul)
    montant = montant - amort
  finpour
fin