CHAPITRE 1 – LA CONCEPTION

D’UNE FEUILLE DE CALCUL EXCEL

Exercice 1 : fonctions avancées ••

1. Complétez avec des formules la maquette fournie en annexe 1.

Chiffre d’affaires semestriel des commerciaux

2. Complétez le tableau des formules.

Cellule Nom Formule

F6 =[Link](A7:A24;E6;C7:C24) recopie jusqu’à F8.

H6 =INDEX($E$6:$E$8;EQUIV([Link]($F$6:$F$8;G6);$F$6:$F$8;0))
recopie jusqu’à H8.
I6 =SI(F6>50000;F6*$B$3;0) recopie jusqu’à I8.

J6 =$B$4*6 recopie jusqu’à J8.

K6 =J6+I6 recopie jusqu’à K8.

G10 =MOYENNE(C7:C24)

G11 {=MOYENNE(SI(C7:C24<>0;C7:C24;FAUX))}

G12 {=MIN(SI(C7:C24<>0;C7:C24;FAUX)) }
G13 {=MAX(SI(C7:C24<>0;C7:C24;FAUX)) }

G14 {=MOYENNE(SI(MOIS(B7:B24)=1;C7:C24)) }

G15 {=SOMME(SI(MOIS(B7:B24)=1;C7:C24)) }
G16 =EQUIV(MIN(C7:C24);C7:C24;0)+LIGNE(C7)-1

G17 =EQUIV(MAX(C7:C24);C7:C24;0)+LIGNE(C7)-1
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/2 SOLUTIONS CHAPITRE 1

 PARTIE 1 – LES BASES DU TABLEUR EXCEL, LA LOGIQUE ALGORITHMIQUE ET LE LANGAGE DE PROGRAMMATION SOUS VBA

Exercice 2 : fonctions matricielles ••

Quelques techniques de calcul matriciel peuvent être mises à profit pour résoudre des problèmes mathématiques. La résolu-
tion de systèmes de n équations à n inconnues est un problème de produit matriciel, qui peut être résolu à l’aide des fonctions
DETERMAT, PRODUITMAT et INVERSEMAT.

Système d’équation à trois inconnues

2. Complétez le tableau des formules.

Cellule Nom Formule

H10 :H12 {=SI(DETERMAT(C10:E12);PRODUITMAT(INVERSE-

MAT(C10:E12);F10:F12);SI(ABS(D10*F11-D11*F10)+ABS(C10*F11-
C11*F10);"Pas de solution";"Système indéterminé"))}
I10 =SI(ESTNUM(H10);"x="&H10;"")
I11 =SI(ESTNUM(H11);"y=";"")&H11
I12 =SI(ESTNUM(H12);"z="&H12;"")

Nous avons utilisé l’opérateur de concaténation pour lier les deux éléments. Une cellule appartenant à un bloc de cellules
contenant une formule matricielle ne peut pas se désolidariser. Si vous souhaitez entreprendre une modification de détail sur
une matrice, il faut en figer les données en sélectionnant la matrice, en la copiant, puis en utilisant Collage spécial du menu
« Édition », option « Valeurs ».
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/2 SOLUTIONS CHAPITRE 1

 CHAPITRE 2 – LE LANGAGE VBA SOUS EXCEL

Exercice 1 : script VBA : les dépréciations (Sujet BAC STG CFE) ••

Sub Calcul()
Dim Créances_dues_TTC As Single
Dim Créances_dues_HT As Single
Dim DépréciationN_1 As Single ' Dépréciation au 31/12/N-1
Dim Taux_irrécouvrabilité As Single
Dim DépréciationN As Single ' Dépréciation au 31/12/N
Dim Dotation As Single
Dim Reprise As Single
Dim Taux_TVA As Single
Dim Solvabilité As Boolean

Taux_TVA = 5.5
'Saisie de la créance due TTC
Créances_dues_TTC = InputBox("Veuillez saisir la créance due TTC au 31/12/N du client concerné.")
'Calcul de la créance due HT

Créances_dues_HT = Créances_dues_TTC / (1 + Taux_TVA / 100)

'Saisie de la dépréciation au 31/12/N-1

DépréciationN1 = InputBox("Veuillez saisir la dépréciation au 31/12/N-1 du client concerné")
'Test de solvabilité
Solvabilité = InputBox("Veuillez dire si le client reste solvable : Vrai ou Faux")
If Solvabilité = True Then
'Saisie du taux d'irrécouvrabilité
Taux_irrécouvrabilité = InputBox("Veuillez saisir le Taux d'irrécouvrabilité au 31/12/N du client
concerné.")
'Calcul de la dépréciation au 31/12/N

DépréciationN = Créances_dues_HT * (Taux_irrécouvrabilité / 100)

If DépréciationN > DépréciationN_1 Then

Dotation = DépréciationN - DépréciationN - 1

Reprise = 0
Else
Dotation = 0

Reprise = DépréciationN - 1 - DépréciationN

End If
Else
Dotation = 0
Reprise = DépréciationN - 1
End If
'Affichage des informations demandées
© Groupe Eyrolles

MsgBox ("La créance due HT est de : " & Créances_dues_HT)

MsgBox ("La dépréciation au 31/12/N est de : " & DépréciationN)
MsgBox ("La reprise est de : " & Reprise)
MsgBox ("La dotation est de : " & Dotation)
End Sub

LA GESTION SOUS EXCEL ET VBA 1/4 SOLUTIONS CHAPITRE 2

 PARTIE 1 – LES BASES DU TABLEUR EXCEL, LA LOGIQUE ALGORITHMIQUE ET LE LANGAGE DE PROGRAMMATION SOUS VBA

Exercice 2 : fonction personnalisée : calcul d’une commission ••

Rémunérations des vendeurs en fonction du chiffre d’affaires et de l’ancienneté

Code VBA
Function Commission(Ventes As Currency, Anciennete As Integer) As Currency
'Déclaration des constantes
Const TauxCom1 As Double = 0.06
Const TauxCom2 As Double = 0.08
Const TauxCom3 As Double = 0.1
Const TauxCom4 As Double = 0.12
'Traitement des données
Select Case Ventes
Case 0 To 10000
Commission = Ventes * TauxCom1
Case 10000 To 20000
Commission = Ventes * TauxCom2
Case 20000 To 30000
Commission = Ventes * TauxCom3
Case Is >= 30000
Commission = Ventes * TauxCom4
End Select
'Affichage des données
Commission = Commission + (Commission * Anciennete / 100)
End Function

Exercice 3 : équation du second degré et VBA ••

La fonction InputBox
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/4 SOLUTIONS CHAPITRE 2

 CHAPITRE 2 – LE LANGAGE VBA SOUS EXCEL

La fonction MsgBox

Code VBA
Option Explicit
Sub EquationSecondDegre_Click()

'Déclaration des variables

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim delta As Integer
Dim x0 As Integer
Dim x1 As Integer
Dim x2 As Integer
'Saisie des données
a = InputBox("Saisir la valeur du coefficient a : ")
b = InputBox("Saisir la valeur du coefficient b : ")
c = InputBox("Saisir la valeur du coefficient c : ")
'Traitement des données
delta = (b * b) - 4 * a * c
x0 = ((-b) / (2 * a))
x1 = ((-b + ((delta) ^ (1 / 2))) / (2 * a))
x2 = ((-b - ((delta) ^ (1 / 2))) / (2 * a))
If delta < 0 Then
MsgBox ("Pas de solution")
ElseIf delta = 0 Then
© Groupe Eyrolles

'Résultats des données

MsgBox ("Il existe une unique solution qui est " & x0)
Else: MsgBox ("Il existe deux solutions qui sont " & x1 & " et " & x2)
End If
End Sub

LA GESTION SOUS EXCEL ET VBA 3/4 SOLUTIONS CHAPITRE 2

 PARTIE 1 – LES BASES DU TABLEUR EXCEL, LA LOGIQUE ALGORITHMIQUE ET LE LANGAGE DE PROGRAMMATION SOUS VBA
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/4 SOLUTIONS CHAPITRE 2

 CHAPITRE 3 – BOÎTE DE DIALOGUE PERSONNALISÉE
(USERFORM)

Exercice 1 : ma première boîte de dialogue personnalisée •

La création des boîtes de dialogue s'effectue dans Visual Basic Editor. Choisissez dans le menu Outils l'option Macro, puis
cliquez sur Visual Basic Editor.
Le moyen le plus rapide de lancer VBE à partir d'Excel est d'appuyer sur la touche Alt+F11. Pour revenir à Excel, appuyez une
nouvelle fois sur Alt +F11.
Créez la boîte de dialogue, pour cela, choisissez dans le menu Insertion l’option UserForm (pour Excel 2007/2010 : onglet
Développeur/Visual Basic/Insertion UserForm).
Une boîte vierge s’affiche à droite de l’écran. Renommez cette boîte en tapant dans la zone (Name), à la place de UserForm1,
saisissez RAY.
Dans la fenêtre des propriétés, modifiez la propriété Caption en tapant à la place de UserForm1 « RAY CHARLES ».
Dans la barre de titre de la UserForm, vous devez voir s’écrire le mot « RAY CHARLES » comme dans l’exemple ci-dessous.
Dans une UserForm (boîte de dialogue), nous placerons :
Q un bouton de commande OK : CommandButton :
Q une image : Picture
Q un code VBA

Private btDOK_Click()
[Link]
End Sub
Une fois réalisée, l’interface ressemblera à l’illustration ci-dessous.
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/3 SOLUTIONS CHAPITRE 3

 PARTIE 1 – LES BASES DU TABLEUR EXCEL, LA LOGIQUE ALGORITHMIQUE ET LE LANGAGE DE PROGRAMMATION SOUS VBA

Exercice 2 : salaire brut du personnel ••

Code VBA
Option Explicit
'Déclaration des variables
Dim Sh As Double
Dim Nh As Double
Dim Sb As Currency
Private Sub Com_Calculer_Click()
'Traitement des données
Sheets("Salaire").Select
Range("B4").Select
[Link] = Scro_Horaire.Value
Range("B5").Select
[Link] = Text_NHeure.Value
Sh = Scro_Horaire.Value
Nh = Text_NHeure.Text
Sb = Sh * Nh
'Affichage des données
Lab_Sbrut.Caption = Format(Sb, "currency")
End Sub
Private Sub Com_Quitter_Click()
End
End Sub

Sub salaire1()
B_Salaire.Show
End Sub

ANNEXE 2

Tableau des contrôles dans la boîte de dialogue personnalisée et leurs propriétés

Contrôle Outils Propriété Name Propriété Caption Propriété FONT

Police Times New

Formulaire (UserForm) B_Salaire Salaire du personnel
Roman taille 11

Barre de défilement (ScrollBar) Scro_Horaire

Zone de texte (TextBox) Text_NHeure

Cadre (Frame) Fram_Salaire Salaire

Intitulé (Label)) Lab_Sbrut

Bouton de commande (CommandButton) Com_Calculer Calculer

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/3 SOLUTIONS CHAPITRE 3

 CHAPITRE 3 – BOÎTE DE DIALOGUE PERSONNALISÉE (USERFORM)

Exercice 3 : UserForm et structure itérative ••

Private Sub Cmd_Inserer_Click()
'Déclaration de variable
Dim i As Integer
i = 0
'Boucle de traitement des données
Do
i = i + 1
Loop Until IsEmpty([Link](i, 1))
'Insérer un nouvel élève
Range("A" & i) = Txt_Nom.Value
Range("B" & i) = Txt_Prenom.Value
Range("C" & i) = Txt_Age.Value
Range("D" & i) = Txt_Classe.Value
End Sub
'Quitter le formulaire
Private Sub Cmd_Quitter_Click()
End
End Sub

Module 1
'Ouverture du formulaire
Sub ELEVEstg()
Fml_Eleve.Show
End Sub

ANNEXE 2

Tableau des contrôles dans la boîte de dialogue personnalisée et leurs propriétés

Contrôle Outils Propriété Name Propriété Caption

Formulaire (UserForm) Fml_Eleve Formulaire Elève de 1STG GESTION

Zone de texte (TextBox) Txt_Nom

Zone de texte (TextBox) Txt_Prenom

Zone de texte (TextBox) Txt_Age

Zone de texte (TextBox) Txt_Classe

Bouton de commande (CommandButton) Cmd_Inserer Insérer

Bouton de commande (CommandButton) Cmd_Quitter Quitter

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/3 SOLUTIONS CHAPITRE 3

 CHAPITRE 4 – ALGORITHME ET VBA

Exercice 1 : TVA à décaisser : structure séquentielle •

Concevez un algorithme qui affichera le montant de la TVA à décaisser.

Algorithme TVA_A_DECAISSER
Variable TVACollectée : Réel
Variable TVADéductibleABS : Réel
Variable TVADéductibleImmob : Réel
Variable TVADécaisser : Réel

Début
| Afficher ("Saisir la TVA collectée sur les ventes :")
| Saisir (TVACollectée)
| Afficher ("Saisir la TVA déductible sur les achats de biens et services :")
| Saisir (TVADéductibleABS)
| Afficher ("Saisir la TVA déductible sur les immobilisations :")
| Saisir (TVADéductibleImmob)
| TVADécaisser
TVACollectée – (TVADéductibleABS + TVADéductibleImmob)
| Afficher ("La TVA à décaisser est de : ",TVADécaisser, " € ")
Fin

Exercice 2 : seuil de rentabilité et point mort : structure conditionnelle ••

Proposez un algorithme qui affichera le seuil de rentabilité et le point mort.

AlgoSeuilRentabilite
Variable CAffaires : Réel
Variable CVariable : Réel
Variable CFixe : Réel
Variable MCV : Réel
Variable TMCV : Réel
Variable RESULTAT: Réel
Variable SR : Réel
Variable PM : Réel

Début
| Afficher ("Saisir le montant du Chiffre d'affaires HT : ")
| Saisir (CAffaires)
| Afficher ("Saisir le montant des Charges variables : ")
| Saisir (CVariable)
| Afficher ("Saisir le montant des Charges fixes : ")
| Saisir (CFixe)
| MCV
CAffaires - CVariable
| TMCV
MCV / CAffaires
| RESULTAT
MCV - CF
| SR
CF / TMCV
| Afficher ("La seuil de rentabilité est de :", SR, "euros")
|SI CAffaires > SR
|Alors PM
SR/ CAffaires * 360
© Groupe Eyrolles

| Afficher ("Le point mort est de :", PM, "jours.")

|Sinon PM
0
| Afficher ("Le point mort n’est pas atteint :")
|Fin Si
Fin

LA GESTION SOUS EXCEL ET VBA 1/2 SOLUTIONS CHAPITRE 4

 PARTIE 1 – LES BASES DU TABLEUR EXCEL, LA LOGIQUE ALGORITHMIQUE ET LE LANGAGE DE PROGRAMMATION SOUS VBA

Exercice 3 : calculer le montant d’une commission : structure itérative ••

Concevez un algorithme qui affichera le nom du représentant et le montant de la commission en fonction du chiffre d’affaires
réalisé. Pour un chiffre d’affaires supérieur ou égal à 30 000 € mensuel, la commission est égale à 12 % des ventes, sinon elle
est égale à 10 % des ventes. Réalisez un algorithme avec la structure TantQue… FinTantQue

La structure TantQue… FinTantQue

AlgoMontantCommission
Variable CAffaires : Réel
Variable Com : Réel
Variable NomRep : Chaîne de caractères
Variable AutreRep : Chaîne de caractères
Constante TauxCom <- 0.10
Constante TauxCom1 <- 0.12
Constante Objectif <- 30 000
Début
| AutreRep = "OUI"
| TantQue AutreRep = "OUI"
| Afficher ("Saisir le nom du représentant : ")
| Saisir (NomRep )
| Afficher (" Saisir le montant du chiffre d’affaires : ")
| Saisir (CAffaires)
| Si CAffaires > Objectif Alors
| Com
CAffaires * TauxCom1
| Sinon
| Com
CAffaires * TauxCom
| Fin si
| Afficher ("Le nom du représentant :",NomRep,"le montant de la commission :",Com," € ")
| Afficher ("Avez-vous un autre traitement à effectuer ? OUI/NON : ")
| Saisir (AutreRep)
| FinTantQue
Fin
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/2 SOLUTIONS CHAPITRE 4

 CHAPITRE 5 – LA GESTION DE LA FACTURATION

Exercice 1 : facture UserForm avec Listview ••••

Les codes de la boîte de dialogue personnalisée

Private Sub Cmd_Calculer_Click()
'Traitement dans la facture
MontantHT = Format(CDbl([Link]) * CDbl(Quantité), "0.00")
End Sub

Private Sub Cmd_Quitter_Click()

End
End Sub

Private Sub Reference_Change()

'Déclaration des variables
Dim Plage1 As Variant
Dim Ligne As Integer
Dim Cellule As Variant
Dim RechercheArticle As String
'Recherche de la désignation et du prix unitaire à l'aide du code référence
'dans la table Produits
With Sheets("Facture")
Set Plage1 = .Range("I2:I" & .Range("I65536").End(xlUp).Row)
End With
RechercheArticle = [Link]
Set Cellule = [Link](What:=RechercheArticle, LookIn:=xlValues)
If Not Cellule Is Nothing Then
Ligne = [Link]
With Sheets("Facture")
[Link] = .Range("J" & Ligne).Value
[Link] = .Range("K" & Ligne).Value
End With
End If
Quantité.SetFocus
End Sub

Private Sub Cmd_Ajouter_Click()

'Déclaration des variables
Dim Ligne As Variant
Dim I As Integer
Dim j As Integer
Dim Total As Double
Dim Valeur As Variant
'Ajouter données dans la facture
Ligne = Array("Reference", "Designation", "Quantité", "PUHT", "MontantHT")
With Me.ListView1
j = .[Link] + 1
' Boucle
For I = 0 To UBound(Ligne)
'Structure de decision
Select Case I
Case 0: .[Link] , , Controls(Ligne(I)).Value ': j = j + 1
Case 1 To 2: .ListItems(j).[Link] , , Controls(Ligne(I)).Value
Case 3 To 4: .ListItems(j).[Link] , , Format(Controls(Ligne(I)).Value, "#,##0.00
€")
End Select
Next I

For I = 1 To .[Link]
Total = Total + Numerique(.ListItems(I).ListSubItems(4))

Next I
© Groupe Eyrolles

End With
'Traitement du total
Lab_Total.Caption = Format(Total, "#,##0.00 €") ' Total TTC
MontantTVA = Total * 19.6 / 100
MontantTTC = Total + MontantTVA
Lab_TVA.Caption = Format(MontantTVA, "#,##0.00 €")

LA GESTION SOUS EXCEL ET VBA 1/6 SOLUTIONS CHAPITRE 5

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Lab_TTC.Caption = Format(MontantTTC, "#,##0.00 €")

'Réinitialisation
Reference = ""
Designation = ""
Quantité = ""
PUHT = ""
MontantHT = ""
End Sub

Private Sub UserForm_Initialize()

'Déclaration des variables
Dim Plg As Variant
With Sheets("Facture")
Plg = .Range("I2:I" & .Range("J65536").End(xlUp).Row)
End With
[Link] = Plg
With Me.ListView1
'Ajoute 5 colonnes en spécifiant le nom de l'entête
'et la largeur des colonnes
With .ColumnHeaders
.Add , , "Référence", 60
.Add , , "Désignation", 160
.Add , , "Quantité", 60, lvwColumnCenter 'centrer
.Add , , "Prix Unit HT", 100, lvwColumnCenter
.Add , , "Montant HT", 100, lvwColumnCenter
End With
End With
End Sub
Public Function Numerique(Valeur As String)
'Les montants au format monétaire
If InStr(1, Valeur, " €") > 0 Then
Numerique = CDbl(Replace(Valeur, " €", ""))
Else
Numerique = Valeur
End If
End Function

Programme dans le module1

Sub facture()
Fm_Facture.Show
End Sub

Le tableau de synthèse des contrôles de l’interface graphique utilisateur

Contrôle Outils Propriété Name Propriété Caption

Formulaire (UserForm) Fm_Facture FACTURE DE DOIT

Zone de liste modifiable (ComboBox) Reference

Zone de texte (TextBox) Designation

Zone de texte (TextBox) Quantité

Zone de texte (TextBox) PUHT

Zone de texte (TextBox) MontantHT

Listview ListView1
© Groupe Eyrolles

Intitulé (Label)) Lab_Total

.../...

LA GESTION SOUS EXCEL ET VBA 2/6 SOLUTIONS CHAPITRE 5

 CHAPITRE 5 – LA GESTION DE LA FACTURATION

Contrôle Outils Propriété Name Propriété Caption

Intitulé (Label)) Lab_TVA

Intitulé (Label)) Lab_TTC

Bouton de commande (CommandButton) Cmd_Calculer Calculer

Bouton de commande (CommandButton) Cmd_Ajouter Ajouter

Bouton de commande (CommandButton) Cmd_Quitter Quitter

Exercice 2 : TVA à décaisser en VBA ••

Calculez la TVA à décaisser au titre du mois de juin N.

Montant

TVA collectée 150 000 × 19,6% = 29 400

TVA déductible sur ABS 60 00 × 19,6% = 11 760

TVA déductible sur immobilisations 45 000 × 19,6% = 8 820

Crédit de TVA 3 000

TVA à décaisser 5 820,00 €

La saisie des données : la fonction InputBox

Ventes de marchandises HT Achats de marchandises HT

Achats d’immobilisations HT Crédit de TVA mai N

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/6 SOLUTIONS CHAPITRE 5

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

L’affichage du résultat : la fonction MsgBox

Code VBA
Sub TVA()
'Déclaration des variables
Dim Vente As Single, Achat As Single, Immob As Single
Dim report_credit As Single, TVACollectee As Single, TVADeductibleABS As Single, TVADeductibleImmob As
Single, TVADecaisser As Single
'Saisie des variables
Vente = InputBox("Saisir le montant des ventes HT du mois :", "Ventes HT")
Achat = InputBox("Saisir le montant des achats HT du mois :", "Achats HT")
Immob = InputBox("Saisir le montant des acquisitions d'immobilisations HT du mois :", "Immobilisations
HT")
report_credit = InputBox("Saisir le crédit de TVA du mois précédent :", "Report crédit de TVA")
'Traitement des variables
Vente = Round(Vente, 0)
Achat = Round(Achat, 0)
Immob = Round(Immob, 0)
TVACollectee = Round(Vente * 0.196, 0)
TVADeductibleABS = Round(Achat * 0.196, 0)
TVADeductibleImmob = Round(Immob * 0.196, 0)
TVADecaisser = TVACollectee - TVADeductibleABS - TVADeductibleImmob - report_credit
'Affichage des résultats
If TVADecaisser > 0 Then
MsgBox ("La TVA à décaisser est de " & TVADecaisser & " € ")
Else
MsgBox ("La crédit de TVA est de " & -(TVADecaisser) & " € ")
End If
End Sub

Exercice 3 : cas meuble Decor ••••

Tableau des formules sous Excel

Cellule Formule
F26 =SOMME(F17:F25)
F27 =F26*E27
F28 =F26+F27

Le tableau de synthèse des contrôles de l’interface graphique utilisateur

Propriété Propriété
Contrôle Outils
Name Caption

Formulaire (UserForm) BD_FACTURE SOCIETE MEUBLE DECOR

Zone de liste modifiable (ComboBox) Nom

© Groupe Eyrolles

Zone de texte (TextBox) Adresse

.../...

LA GESTION SOUS EXCEL ET VBA 4/6 SOLUTIONS CHAPITRE 5

 CHAPITRE 5 – LA GESTION DE LA FACTURATION

Contrôle Outils Propriété Name Propriété Caption

Zone de texte (TextBox) CPostal

Zone de texte (TextBox) ListBox1

Intitulé (Label)) LabelCode

Intitulé (Label)) LabelPrixUnit

Zone de texte (TextBox) TextBoxQuantite

Intitulé (Label)) LabelPrixTotal

Bouton de commande (CommandButton) Com_Ajouter Ajouter

Bouton de commande (CommandButton) Com_Quitter Quitter

PROPOSITION DE CODE EN VBA

Code VBA
Option Explicit
'Déclaration des variables
Dim ListProduit As Integer
Dim DerLigne As Integer
Dim PlageList As String
Dim Ligne As Integer
Private Sub Com_Ajouter_Click()
'Ajouter ligne suivante
Ligne = Sheets("Facture").Range("B24").End(xlUp).Row + 1
With Sheets("Facture")
.Range("B" & Ligne) = LabelCode
.Range("C" & Ligne) = ListBox1
.Range("D" & Ligne) = TextBoxQuantite
.Range("E" & Ligne) = Format(LabelPrixUnit, 0#)
.Range("F" & Ligne) = Format(LabelPrixTotal, 0#)
End With
'Récupération des données de la boîte de dialogue
Nom = BD_FACTURE.Nom
Adresse = BD_FACTURE.Adresse
CPostal = BD_FACTURE.CPostal
'Entrée des données du client dans la facture
Sheets("Facture").Select
Range("D7").Value = Nom
Range("D8").Value = Adresse
Range("D9").Value = CPostal
[Link]
Sheets("Facture").Range("E12") = "le " & Format(Now, "DD/MM/YYYY")
End Sub

Private Sub Com_Quitter_Click()

End
End Sub

Private Sub ListBox1_Click()

'Table des produits
ListProduit = BD_FACTURE.[Link] + 2
LabelCode = Sheets("Produits").Range("A" & ListProduit).Value
© Groupe Eyrolles

LabelPrixUnit = Format(Sheets("Produits").Range("C" & ListProduit).Value, "#,##0.00")

LabelPrixTotal = ""
TextBoxQuantite = ""
[Link]
End Sub

LA GESTION SOUS EXCEL ET VBA 5/6 SOLUTIONS CHAPITRE 5

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Private Sub Nom_Change()

'Table des clients
Me("Adresse") = [Link](Me("Nom"), [BDClient], 2, False)
Me("CPostal") = [Link](Me("Nom"), [BDClient], 3, False)
End Sub

Private Sub TextBoxQuantite_Change()

If TextBoxQuantite = "" Then Exit Sub
On Error GoTo Sortie
LabelPrixTotal = Format(LabelPrixUnit * TextBoxQuantite, 0#)
Com_Ajouter.Visible = True
Exit Sub
Sortie:
MsgBox "Saisir Uniquement un entier Numérique"
End Sub

Private Sub UserForm_Initialize()

Sheets("Facture").Activate
DerLigne = Sheets("Produits").Range("A65536").End(xlUp).Row
PlageList = Sheets("Produits").Range("B2:B" & DerLigne).Address
[Link] = "Produits!" & PlageList
Me("Nom").List = (Range([H2], [H2].End(xlDown)))
End Sub

Module 1
Sub bdfact()
BD_FACTURE.Show
End Sub

Sub Efface()
Sheets("Facture").Range("B17:F25").ClearContents
Sheets("Facture").Range("D7:D9").ClearContents
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 6/6 SOLUTIONS CHAPITRE 5

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

Exercice 1 : programmation d’un amortissement linéaire (sans prorata temporis) •••

La fonction InputBox permettant la saisie des données :

Code VBA
Sub AmortL()
'Variables des données
Dim Annee, Durée As Integer
Dim Taux As Single
Dim VO, AMORT, VNC, MontantAmort As Currency
Dim Ligne As String 'Cette variable va afficher le plan d'amortissement
VO = InputBox("Saisissez la base d'amortissement", "Plan d'amortissement linéaire")
Durée = InputBox("Saisissez la durée d'amortissement", "Plan d'amortissement linéaire")
'Affiche les titres du plan d’amortissement
Ligne = "Année" & vbTab & " Base " & vbTab & vbTab & " Annuité " & vbTab & vbTab & " Valeur NC " &
vbCrLf

'Boucle et traitement du plan d’amortissement

For i = 1 To Durée
VO = Format(VO, "currency")
AMORT = Format(VO / Durée, "currency")
MontantAmort = MontantAmort + AMORT
VNC = Format(VO - MontantAmort, "currency")
Ligne = Ligne & vbCrLf & i & vbTab & VO & vbTab & AMORT & vbTab & VNC
Annee = Annee + 1
Next i
MsgBox Ligne 'Affichage du plan d'amortissement
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 2 : programmation en VBA d’un amortissement non linéaire ••••

Code VBA
Option Explicit
Private Sub Cmd_Calculer_Click()
'Déclaration des variables
Dim tabConso(10) As Double
Dim tabAnnuite(10) As Double
Dim tabCumul(10) As Double
Dim nbreMaxLigne, duree As Integer
Const nbreMaxColonne As Integer = 6
Dim baseAmortissable, totalConso As Double
Dim annee As Variant
Dim indiceLigne, indiceColonne, indice As Integer
'Nombre de colonnes du tableau d’amortissement non linéaire
[Link] = nbreMaxColonne
[Link] = "1,5 cm; 2 cm ; 3,5 cm ; 3,5 cm ; 4 cm"
[Link]
nbreMaxLigne = GetDureeUtilisation()
'Date système
annee = Year(Date)
'Boucle de traitement
For indiceLigne = 0 To nbreMaxLigne
If indiceLigne = nbreMaxLigne Then
[Link] "TOTAL"
Else
[Link] (annee)
End If
annee = annee + 1
Next indiceLigne

baseAmortissable = GetValeurTextBox("tbBaseAmortissable")

indiceColonne = 2
For indiceLigne = 0 To (nbreMaxLigne - 1)
[Link](indiceLigne, indiceColonne) = baseAmortissable
Next indiceLigne

For indice = 0 To (nbreMaxLigne - 1)

tabConso(indice) = GetValeurTextBox("tbConso" & indice + 1)
Next indice

totalConso = GetValeurTextBox("tbTotalConso")
indice = 0
For indiceLigne = 0 To (nbreMaxLigne - 1)
tabAnnuite(indice) = CalculerAnnuite(tabConso(indice), totalConso, baseAmortissable)
indice = indice + 1
Next indiceLigne

indice = 0
indiceColonne = 3
For indiceLigne = 0 To nbreMaxLigne
If indiceLigne = nbreMaxLigne Then
[Link](indiceLigne, indiceColonne) = Format(baseAmortissable, "####0.00")
Else
[Link](indiceLigne, indiceColonne) = Format(tabAnnuite(indice),
"####0.00")
indice = indice + 1
End If
Next indiceLigne

indice = 0
For indiceLigne = 0 To (nbreMaxLigne - 1)
If indice = 0 Then
tabCumul(indice) = tabAnnuite(indice)
© Groupe Eyrolles

Else: tabCumul(indice) = tabCumul(indice - 1) + tabAnnuite(indice)

End If
indice = indice + 1
Next indiceLigne

LA GESTION SOUS EXCEL ET VBA 2/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

indiceColonne = 4
indice = 0
For indiceLigne = 0 To (nbreMaxLigne - 1)
[Link](indiceLigne, indiceColonne) = Format(tabCumul(indice), "####0.00")
indice = indice + 1
Next indiceLigne

indiceColonne = 5
indice = 0
For indiceLigne = 0 To (nbreMaxLigne - 1)
[Link](indiceLigne, indiceColonne) = Format(baseAmortissable -
tabCumul(indice), "####0.00")
indice = indice + 1
Next indiceLigne

End Sub

'Quitter l’application
Private Sub CmdQuitter_Click()
End
End Sub

Private Sub CmdReset_Click()

'Réinitialiser
Dim nbreMaxTB As Integer
Const valeurInitialisation As Integer = 0

Dim indice As Integer

indice = 1

[Link]
nbreMaxTB = GetDureeUtilisation()

Do While indice <= nbreMaxTB

Call InitialiserTextBox("tbConso" & indice, valeurInitialisation)
indice = indice + 1
Loop

Call InitialiserTextBox("tbCoutAcquisition", valeurInitialisation)

Call InitialiserTextBox("tbValeurResiduelle", valeurInitialisation)
Call InitialiserTextBox("tbDureeUtilisation", valeurInitialisation)

End Sub

'Tableau des consommations des unités d’œuvre

Private Sub tbConso1_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso1_KeyPress(ByVal KeyAscii As [Link])

Select Case KeyAscii

© Groupe Eyrolles

Case Is < 48, Is > 57

KeyAscii = 0

End Select
End Sub

LA GESTION SOUS EXCEL ET VBA 3/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Private Sub tbConso10_Change()

Dim nbreMaxTB As Integer
Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso2_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso3_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso4_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso5_Change()

© Groupe Eyrolles

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

LA GESTION SOUS EXCEL ET VBA 4/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso6_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop
End Sub

Private Sub tbConso7_Change()

Dim nbreMaxTB As Integer
Dim nbreIteration As Integer
nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso8_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]
nbreIteration = nbreIteration + 1
Loop

End Sub

Private Sub tbConso9_Change()

Dim nbreMaxTB As Integer

Dim nbreIteration As Integer

nbreMaxTB = GetDureeUtilisation()

nbreIteration = 1
[Link] = 0
Do While nbreIteration <= nbreMaxTB
© Groupe Eyrolles

[Link] = GetValeurTextBox("tbConso" & nbreIteration) + [Link]

nbreIteration = nbreIteration + 1
Loop

End Sub

LA GESTION SOUS EXCEL ET VBA 5/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Private Sub tbCoutAcquisition_Change()

If [Link] < 0 Or [Link] = "" Then [Link] = 0

[Link] = [Link] - [Link]

If [Link] < 0 Then [Link] = 0

End Sub

Private Sub tbCoutAcquisition_KeyPress(ByVal KeyAscii As [Link])

Select Case KeyAscii

Case Is < 48, Is > 57

KeyAscii = 0

End Select

End Sub

Private Sub tbDureeUtilisation_AfterUpdate()

‘Mise à jour des données
Const nbreMaxTB As Integer = 10
Dim duree, indice As Integer

duree = GetDureeUtilisation()

If duree < 2 Or duree > 10 Or duree = "" Then

[Link] = ""
Exit Sub
Else
For indice = (duree + 1) To nbreMaxTB
[Link]("tbConso" & indice) = 0
[Link]("tbConso" & indice).Locked = True
Next indice
End If

End Sub

Private Sub tbValeurResiduelle_Change()

If [Link] < 0 Or [Link] = "" Then [Link] = 0

[Link] = [Link] - [Link]

If [Link] < 0 Then [Link] = 0

End Sub
Private Sub tbValeurResiduelle_KeyPress(ByVal KeyAscii As [Link])

Select Case KeyAscii

Case Is < 48, Is > 57

KeyAscii = 0

End Select

End Sub

Private Function GetValeurTextBox(ByVal NomTextBox As String) As Double

If Controls(NomTextBox) < 0 Or Controls(NomTextBox) = "" Then Controls(NomTextBox) = 0

GetValeurTextBox = Controls(NomTextBox)

End Function
© Groupe Eyrolles

Private Sub InitialiserTextBox(ByRef NomTextBox As String, ByVal valeurInitialisation As Integer)

Controls(NomTextBox) = valeurInitialisation

End Sub

LA GESTION SOUS EXCEL ET VBA 6/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

Private Function CalculerAnnuite(ByVal valeurTextBox As Double, ByVal Total As Double, ByVal BaseAmort
As Double) As Double

If Total <= 0 Then Exit Function

CalculerAnnuite = (valeurTextBox / Total) * BaseAmort

End Function

Private Function GetDureeUtilisation() As Integer

If [Link] = "" Or [Link] <= 0 Then

Exit Function
Else
GetDureeUtilisation = [Link]
End If

End Function

Module 1
'Ouverture de la boîte
Sub AmortNonLineaire()
[Link]
End Sub

ANNEXE 2

Le tableau de synthèse des contrôles de l’interface graphique

Contrôle Outils Propriété Name Propriété Caption

MODE D'AMORTISSEMENT LINEAIRE

Formulaire (UserForm) FmAmort
(Unité d'oeuvre)

Zone de texte (TextBox) tbCoutAcquisition

Zone de texte (TextBox) tbValeurResiduelle

Zone de texte (TextBox) tbDureeUtilisation

Zone de texte (TextBox) tbBaseAmortissable

Zone de liste (ListBox) lbTabAmortissement

Zone de texte (TextBox) tbConso1 jusqu’à tbConso10

Zone de texte (TextBox) tbTotalConso

Bouton de commande (CommandButton) Cmd_Calculer Calculer

© Groupe Eyrolles

Bouton de commande (CommandButton) CmdReset Réinitialisation

Bouton de commande (CommandButton) CmdQuitter Quitter

LA GESTION SOUS EXCEL ET VBA 7/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 3 : l’automatisation d’un plan d’amortissement linéaire en VBA •••

ANNEXE 1

Maquette

ANNEXE 3

Le tableau de synthèse des contrôles de l’interface graphique

Contrôle Outils Propriété Name Propriété Caption

Formulaire (UserForm) Form_Linéaire Amortissement linéaire

Zone de texte (TextBox) Txt_CoutAcquisition

Zone de texte (TextBox) Txt_Date

Zone de texte (TextBox) Txt_Duree

Zone de texte (TextBox) Txt_Immobilisation

Listview ListView1

Intitulé (Label) Lab_TxtLinéaire

Intitulé (Label) Lab_Annuite

Bouton de commande (CommandButton) CB_Calculer Calculer

© Groupe Eyrolles

Bouton de commande (CommandButton) CB_Reset Réinitialisation

Bouton de commande (CommandButton) CB_Quitter Quitter

LA GESTION SOUS EXCEL ET VBA 8/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

Code VBA
Private Sub CB_Calculer_Click()
'Déclaration des variables
Dim VO As Double
Dim Duree As Integer
Dim mois As Integer
Dim jour As Integer
Dim Prorata As Double
Dim TauxLinéaire As Double
Dim CoefProrata_Temporis As Double
Dim Amort As Double

'Les valeurs par défaut dans la boîte de dialogue

Txt_CoutAcquisition = Form_Linéaire.Txt_CoutAcquisition
Txt_Date = Form_Linéaire.Txt_Date
Txt_Duree = Form_Linéaire.Txt_Duree
Txt_Immobilisation = Form_Linéaire.Txt_Immobilisation

'Entrée des données dans le plan d'amortissement linéaire

Sheets("Linéaire").Select
Range("B3").Value = Txt_CoutAcquisition
Range("B4").Value = Txt_Date
Range("D3").Value = Txt_Duree
Range("D5").Value = Txt_Immobilisation

'Traitement des variables

VO = Range("B3")
Duree = Range("D3")
mois = Month(Range("B4"))
jour = 30 - Day(Range("B4"))

'Calcul prorata temporis et du taux linéaire

Prorata = ((12 - mois) * 30 + jour) / 360
TauxLinéaire = 1 / Duree
CoefProrata_Temporis = TauxLinéaire * Prorata
Amort = VO / Duree

'Traitement de la première ligne du plan d'amortissement linéaire

Cells(8, 1) = Year(Range("B4")) ' Année
Cells(8, 2) = VO ' Valeur d'origine
Cells(8, 3) = VO * CoefProrata_Temporis 'Amortissement linéaire avec prorata temporis
Cells(8, 4) = VO * CoefProrata_Temporis ' Cumul
Cells(8, 5) = VO - Cells(8, 4) ' VNC
Range("B5") = TauxLinéaire
Range("D4") = Amort

'Traitement des autres lignes du plan d'amortissement linéaire

i = 2
If Duree > 2 Then
For i = 2 To Duree
Cells(7 + i, 1) = Year(Range("B4")) + i - 1
Cells(7 + i, 2) = VO
Cells(7 + i, 3) = Amort
Cells(7 + i, 4) = Cells(7 + i - 1, 4) + Cells(7 + i, 3)
Cells(7 + i, 5) = VO - Cells(7 + i, 4)
Next i
Cells(7 + i, 1) = Year(Range("B4")) + i - 1
Cells(7 + i, 2) = VO
Cells(7 + i, 3) = Amort - Cells(8, 3)
Cells(7 + i, 4) = Cells(7 + i - 1, 4) + Cells(7 + i, 3)
Cells(7 + i, 5) = VO - Cells(7 + i, 4)
Else
MsgBox (" La durée doit être supérieure à 2 ans")
End If
Lab_TxtLiné[Link] = Format(TauxLinéaire, "#,##0.00" & "%")
Lab_Annuite.Caption = Format(Amort, "#,##0.00")
'Affichage des données dans la listView1
With Me.ListView1
.[Link]
© Groupe Eyrolles

With .ColumnHeaders
.Clear
.Add , , "Année", 80
.Add , , "Base HT", 80
.Add , , "Amortissement", 80

LA GESTION SOUS EXCEL ET VBA 9/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

.Add , , "Cumul", 80
.Add , , "VNC", 80
End With
For i = 8 To Sheets("Linéaire").Range("A65536").End(xlUp).Row
.[Link] , , Sheets("Linéaire").Cells(i, 1)
For k = 2 To 5
.ListItems(.[Link]).[Link] , , Format(Sheets("Linéaire").Cells(i, k), "# ### ##0.00")
Next
Next
End With
End Sub

Private Sub CB_Quitter_Click()

End
End Sub

Private Sub CB_Reset_Click()

Sheets("Linéaire").Range("B3:B4").ClearContents
Sheets("Linéaire").Range("D3,D5").ClearContents
Sheets("Linéaire").Range("B5,D4").ClearContents
Sheets("Linéaire").Range("A8:E21").ClearContents
[Link]
Lab_TxtLinéaire = ""
Lab_Annuite = ""
Txt_CoutAcquisition = ""
Txt_Date = ""
Txt_Duree = ""
Txt_Immobilisation = ""
End Sub

Private Sub UserForm_initialize()

'----- Remplissage ListView------------------------
With Me.ListView1
'Définit le nombre de colonnes et Entêtes
With .ColumnHeaders
'Supprime les anciennes entêtes
'.Clear
'Ajoute 5 colonnes en spécifiant le nom de l'entête
'et la largeur des colonnes
.Add , , "Année", 80
.Add , , "Base HT", 80, lvwColumnCenter ' centrer
.Add , , "Amortissement", 80, lvwColumnCenter
.Add , , "Cumul", 80, lvwColumnCenter
.Add , , "VNC", 80, lvwColumnCenter
End With
'ligne = 1
.Gridlines = True 'Affichage d'un quadrillage
[Link] = lvwReport 'Spécifie l'affichage en mode Rapport
.FullRowSelect = True
End With
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 10/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

Exercice 4 : l’automatisation d’un plan d’amortissement dégressif en VBA •••

ANNEXE 1
Maquette

ANNEXE 3
Le tableau de synthèse des contrôles de l’interface graphique

Contrôle Outils Propriété Name Propriété Caption

PLAN D'AMORTISSEMENT
Formulaire (UserForm) FM_Degressif
DEGRESSIF

Zone de texte (TextBox) Txt_BaseHT

Zone de texte (TextBox) Txt_Date

Zone de texte (TextBox) Txt_Duree

Bouton d'option (OptionButton) OptionButton1 Coefficient : 1,25

Bouton d'option (OptionButton) OptionButton2 Coefficient : 1,75

Bouton d'option (OptionButton) OptionButton3 Coefficient : 2,25

Listview ListView1

Intitulé (Label) Lab_TauxLinéaire

Intitulé (Label) Lab_TauxLDégressif

Bouton de commande (CommandButton) CB_Calculer Calculer

© Groupe Eyrolles

Bouton de commande (CommandButton) CB_Reset Réinitialisation

Bouton de commande (CommandButton) CB_Quitter Quitter

LA GESTION SOUS EXCEL ET VBA 11/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Code VBA
Private Sub CB_Calculer_Click()
'Déclaration des variables
Dim VO As Double
Dim CoefDegressif As Double
Dim Duree As Integer
Dim Mois As Integer
Dim Prorata As Double
Dim TauxDegressif As Double
Dim CoefProrata_Temporis As Double
Dim TauxLineaire As Double
Dim Amort As Double
Dim Tauxlineaire1 As Double

'Les valeurs par défaut dans la boîte de dialogue

Txt_BaseHT = FM_Degressif.Txt_BaseHT
Txt_Date = FM_Degressif.Txt_Date
Txt_Duree = FM_Degressif.Txt_Duree

'Entrée des données dans le tableau d'amortissement

Sheets("Degressif").Select
Range("B4").Value = Val(Txt_BaseHT)
Range("B5").Value = Txt_Date
Range("D4").Value = Val(Txt_Duree)

'Traitement des variables

VO = Range("B4")
CoefDegressif = Range("D5")
Duree = Range("D4")
Mois = Month(Range("B5"))
'Calcul du prorata temporis et du taux dégressif
Prorata = (12 - Mois + 1) / 12
TauxDegressif = CoefDegressif / Duree
CoefProrata_Temporis = TauxDegressif * Prorata
TauxLineaire = 1 / Duree

'Traitement de la première ligne du plan d'amortissement dégressif

Cells(8, 1) = Year(Range("B5"))
Cells(8, 2) = VO
Cells(8, 3) = VO * CoefProrata_Temporis
Cells(8, 4) = VO * CoefProrata_Temporis
Cells(8, 5) = VO - Cells(8, 4)
Range("B6") = TauxLineaire
Range("D6") = TauxDegressif

'Traitement des autres lignes du plan d'amortissement dégressif

If Duree > 2 Then
Do
Cells(7 + i, 1) = Year(Range("B5")) + i - 1
Cells(7 + i, 2) = Cells(7 + i - 1, 5)
Cells(7 + i, 3) = Cells(7 + i, 2) * TauxDegressif
Cells(7 + i, 4) = Cells(7 + i - 1, 4) + Cells(7 + i, 3)
Cells(7 + i, 5) = VO - Cells(7 + i, 4)
Tauxlineaire1 = 1 / (Duree - i)
i = i + 1
Loop Until Tauxlineaire1 > TauxDegressif

Amort = Cells(7 + i - 1, 5) * Tauxlineaire1

For j = i To Duree
Cells(7 + j, 1) = Year(Range("B5")) + j - 1
Cells(7 + j, 2) = Cells(7 + j - 1, 5)
Cells(7 + j, 3) = Amort
Cells(7 + j, 4) = Cells(7 + j - 1, 4) + Cells(7 + j, 3)
Cells(7 + j, 5) = VO - Cells(7 + j, 4)
© Groupe Eyrolles

Next j
Else
rep = MsgBox("La durée doit être supérieure à 2 ans et inférieure ou égale à 15 ans", vbOKOnly)
End If
Lab_TauxLDé[Link] = Format(TauxDegressif, "#,##0.00" & "%")

LA GESTION SOUS EXCEL ET VBA 12/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 6 – LA GESTION DES AMORTISSEMENTS

Lab_TauxLiné[Link] = Format(TauxLineaire, "#,##0.00 " & "%")

'Affichage des données dans la listView1
With Me.ListView1
.[Link]
With .ColumnHeaders
.Clear
.Add , , "Année", 80
.Add , , "Base HT", 80
.Add , , "Amortissement", 100
.Add , , "Cumul", 80
.Add , , "VNC", 80
End With
For i = 8 To Sheets("Degressif").Range("A65536").End(xlUp).Row
.[Link] , , Sheets("Degressif").Cells(i, 1)
For k = 2 To 5
.ListItems(.[Link]).[Link] , , Format(Sheets("Degressif").Cells(i, k), "# ###
##0.00")
Next
Next
End With
End Sub

Private Sub CB_Quitter_Click()

End
End Sub

Private Sub CB_Reset_Click()

Sheets("Degressif").Range("B4:B5").ClearContents
Sheets("Degressif").Range("D4:D5").ClearContents
Sheets("Degressif").Range("A8:E23").ClearContents
Sheets("Degressif").Range("B6,D6").ClearContents
[Link]
End Sub

Private Sub OptionButton1_Click()

Range("d5").Select
ActiveCell.FormulaR1C1 = "1,25"
Range("d6").Select
End Sub

Private Sub OptionButton2_Click()

Range("d5").Select
ActiveCell.FormulaR1C1 = "1,75"
Range("d6").Select
End Sub

Private Sub OptionButton3_Click()

Range("d5").Select
ActiveCell.FormulaR1C1 = "2,25"
Range("d6").Select
End Sub

Private Sub UserForm_initialize()

'----- remplissage ListView------------------------
With Me.ListView1
'Définit le nombre de colonnes et en-têtes
With .ColumnHeaders
'Supprime les anciennes entêtes
'.Clear
'Ajoute 5 colonnes en spécifiant le nom de l'en-tête
'et la largeur des colonnes
.Add , , "Année", 80
.Add , , "Base HT", 80, lvwColumnCenter ' centrer
© Groupe Eyrolles

.Add , , "Amortissement", 100, lvwColumnCenter

.Add , , "Cumul", 80, lvwColumnCenter
.Add , , "VNC", 80, lvwColumnCenter
End With

LA GESTION SOUS EXCEL ET VBA 13/14 SOLUTIONS CHAPITRE 6

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

.Gridlines = True 'Affichage d'un quadrillage

[Link] = lvwReport 'Spécifie l'affichage en mode Rapport
.FullRowSelect = True
End With
End Sub

Module 1
Sub Degressif()
FM_Degressif.Show
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 14/14 SOLUTIONS CHAPITRE 6

 CHAPITRE 7 – L’IMPÔT SUR LES SOCIÉTÉS

Exercice 1 : impôt sur les sociétés •

1. Calcul de l’IS en N.
IS (N) = Résultat fiscal (N) × Taux d’IS en vigueur, Taux d’IS en vigueur = 33,1/3 %.
IS (N) = 500 000 × 33,1/3 %. = 166 667 €
2. Calcul des acomptes et solde.

Acompte Date Montant des acomptes

d’exigibilité limite de paiement
1 20 février N 15 mars N 430 000 * 8 1/3 % = 35 833
2 20 mai N 15 juin N 480 000 * 16 2/3 % - 35 833 = 44 167
3 20 août N 15 septembre N 480 000 * 8 1/3 % = 40 000
4 20 novembre N 15 décembre N 480 000 * 8 1/3 % = 40 000
Total des acomptes versés : 35 833 + 44 167+ 40 000+ 40 000= 160 000

Calcul du montant du solde IS N = RF N × 33 1/3 % – total des 4 acomptes versés.

Solde IS N = 166 667 – 160 000 = 6 667 €.
Le solde de l’IS, à verser au plus tard le 15/04/N+1, sera de 6 667 €.
3. Automatisez à l’aide de formule sur tableur Excel les acomptes et le solde de l’impôt sur les sociétés.

CELLULE FORMULE
B13 =B3*1/12
C13 =(B4*1/6)-B13
D13 =B4*1/12
E13 =B4*1/12
F13 =B5*1/3-SOMME(B13:E13)
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/5 SOLUTIONS CHAPITRE 7

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 2 : programmation de l’impôt sur les sociétés : InputBox, MsgBox ••

LA SAISIE DES DONNÉES : LA FONCTION INPUTBOX

Résultat fiscal N – 2

Résultat fiscal N – 1

Résultat fiscal N

L’AFFICHAGE DES DONNÉES : LA FONCTION MSGBOX

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/5 SOLUTIONS CHAPITRE 7

 CHAPITRE 7 – L’IMPÔT SUR LES SOCIÉTÉS

Code VBA
Sub IMPOT()
Dim RFN2 As Single, RFN1 As Single, RFN As Single
Dim Acompte1 As Single, Acompte2 As Single, Acompte3 As Single, Acompte4 As Single, Solde As Single

RFN2 = InputBox("Saisir le résultat fiscal N-2 :", " Résultat N-2")

RFN1 = InputBox("Saisir le résultat fiscal N-1 :", " Résultat N-1")
RFN = InputBox("Saisir le résultat fiscal N :", " Résultat N")

RFN2 = Round(RFN2, 0)
RFN1 = Round(RFN1, 0)
RFN = Round(RFN, 0)
Acompte1 = Round(RFN2 * 1 / 12, 0)
Acompte2 = Round(RFN1 * 1 / 6 - Acompte1, 0)
Acompte3 = Round(RFN1 * 1 / 12, 0)
Acompte4 = Round(RFN1 * 1 / 12, 0)
Solde = Round(((RFN * 1 / 3) - (Acompte1 + Acompte2 + Acompte3 + Acompte4)), 0)
MsgBox ("L'Acompte 1 est de " & Acompte1 & " € ")
MsgBox ("L'Acompte 2 est de " & Acompte2 & " € ")
MsgBox ("L'Acompte 3 est de " & Acompte3 & " € ")
MsgBox ("L'Acompte 4 est de " & Acompte4 & " € ")
MsgBox ("La Solde est de " & Solde & " € ")
End Sub

Exercice 3 : impôt sur les sociétés – interface graphique utilisateur •••

Tableau des contrôles de la boîte de dialogue

Propriété Propriété
Contrôle Outils Propriété Font
Name Caption

Impot_Societe Calcul de l'impôt sur les Police Tahoma

Formulaire (UserForm) sociétés taille 10 Gras

Zone de texte (TextBox) TextBoxRFN2

Zone de texte (TextBox) TextBoxRFN1

Zone de texte (TextBox) TextBoxRFN

Intitulé (Label) Label_Acompte1

Intitulé (Label) Label_Acompte2

Intitulé (Label) Label_Acompte3

Intitulé (Label) Label_Acompte4

Intitulé (Label) Label_Solde

Bouton de commande (CommandButton) Cmd_Calculer Calculer

Bouton de commande (CommandButton) Cmd_Quitter Quitter

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/5 SOLUTIONS CHAPITRE 7

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Code VBA
Private Sub Cmd_Calculer_Click()
Dim RFN2 As Single
Dim RFN1 As Single
Dim RFN As Single
Dim Acompte1 As Single
Dim Acompte2 As Single
Dim Acompte3 As Single
Dim Acompte4 As Single
Dim Solde As Single

RFN2 = [Link]
RFN1 = [Link]
RFN = [Link]
Acompte1 = RFN2 * 1 / 12
Acompte2 = RFN1 * 1 / 6 - Acompte1
Acompte3 = RFN1 * 1 / 12
Acompte4 = RFN1 * 1 / 12
Solde = ((RFN * 1 / 3) - (Acompte1 + Acompte2 + Acompte3 + Acompte4))
Label_Acompte1.Caption = [Link](Acompte1, 0) & " € "
Label_Acompte2.Caption = [Link](Acompte2, 0) & " € "
Label_Acompte3.Caption = [Link](Acompte3, 0) & " € "
Label_Acompte4.Caption = [Link](Acompte4, 0) & " € "
Label_Solde.Caption = [Link](Solde, 0) & " € "
Range("B3") = FormatNumber(RFN2, 0)
Range("B4") = FormatNumber(RFN1, 0)
Range("B5") = FormatNumber(RFN, 0)
Range("B13") = FormatNumber(Acompte1, 0)
Range("C13") = FormatNumber(Acompte2, 0)
Range("D13") = FormatNumber(Acompte3, 0)
Range("E13") = FormatNumber(Acompte4, 0)
Range("F13") = FormatNumber(Solde, 0)
End Sub

Private Sub Cmd_Quitter_Click()

End
End Sub

Programme VBA : Module1

Sub Impôts()
Impot_Societe.Show
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/5 SOLUTIONS CHAPITRE 7

 CHAPITRE 7 – L’IMPÔT SUR LES SOCIÉTÉS
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 5/5 SOLUTIONS CHAPITRE 7

 CHAPITRE 8 – L’AFFECTATION DU RÉSULTAT

Exercice 1 : automatisation de la répartition du bénéfice sous Excel ••

ANNEXE 1

Maquette

ANNEXE 2

Tableau des formules

Cellules Formules Explications

E8 =B11 Le bénéfice à répartir

E9 =SI(B12+0,05*B11>B9*0,1;B9*0,1-B12;B11*0,05) Montant de la dotation à la réserve légale = Si réserve légale + 5 % du résultat de l’exercice N

> Capital x 10 % alors Capital x 10 % – réserve légale N-1 sinon résultat de l’exercice N x
5 %.
E10 =B14 Montant de la dotation réserve statutaire

E11 =SI(B13>0;B13;0) Si le report à nouveau N-1 >0 alors report à nouveau N-1 ; sinon rien.

E12 =E8-E9-E10+E11 Bénéfice distribuable = résultat de l’exercice N – dotation à la réserve légale – dotation
réserve statutaire + report à nouveau N-1 .
E13 =B9*B16 Intérêt statutaire = capital x 6 %

E14 =B15 Montant de la dotation réserve facultative

E15 =E12-E13-E14 Superdividende = bénéfice distribuable – intérêt statutaire – dotation réserve facultative

E16 =E12-E14-E21 Report à nouveau N= bénéfice distribuable – dotation réserve facultative – dividende total.
E19 =(E13+E15)/B10 Dividende théorique /Nombre actions = (intérêt statutaire + superdividende)/ nombre d'actions

E20 =[Link](E19;0) Dividende /Nombre actions = arrondi à l’euro inférieur dividende théorique

E21 =E20*B10 Dividende total = dividende unitaire x Nombre actions

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/3 SOLUTIONS CHAPITRE 8

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 2 : automatisation de la répartition du bénéfice sous Excel VBA •••

ANNEXE 2

Tableau des contrôles de la boîte de dialogue

Propriété Propriété
Contrôle Outils
Name Caption

Formulaire (UserForm) Fm_AffectationResultat Affectation du résultat

Zone de texte (TextBox) Txt_Capital

Zone de texte (TextBox) Txt_Resultat

Zone de texte (TextBox) Txt_RLegale

Zone de texte (TextBox) Txt_ReportDebiteur

Zone de texte (TextBox) Txt_ReportCrediteur

Zone de texte (TextBox) Txt_RFacultative

Zone de texte (TextBox) Txt_Dividendes

Intitulé (Label) Lab_Resultat

Intitulé (Label) Lab_ReportDebiteur

Intitulé (Label) Lab_RédultatARepartir

Intitulé (Label) Lab_DotationRLegale

Intitulé (Label) Lab_ReportCcrediteur

Intitulé (Label) Lab_ResultatDistribuable

Intitulé (Label) Lab_DotationRFacultative

Intitulé (Label) Lab_Dividende

Intitulé (Label) Lab_ReportANouveau

Bouton de commande (CommandButton) Cb_Calculer Calculer

Bouton de commande (CommandButton) Cb_Effacer Reset

Bouton de commande (CommandButton) Cb_Quitter Quitter

Code VBA
Private Sub Cb_Calculer_Click()
'Variables de travail
Const tauxMaxReserve As Double = 0.1
Const tauxRLegale As Double = 0.05
© Groupe Eyrolles

Dim capital As Currency

Dim resultat As Currency
Dim reserveLegale As Currency
Dim reportDebiteurN_1 As Currency
Dim reportCrediteurN_1 As Currency

LA GESTION SOUS EXCEL ET VBA 2/3 SOLUTIONS CHAPITRE 8

 CHAPITRE 8 – L’AFFECTATION DU RÉSULTAT

Dim dotationRFacultative As Currency

Dim dotationRLegale As Currency
Dim Dividende As Currency
Dim ReportANouveauN As Currency
Dim resultatRepartir As Currency
Dim resultatDistribuable As Currency

'Récupération de la valeur des textes box et affectation des valeurs aux variables de travail
capital = Txt_Capital.Value
resultat = Txt_Resultat.Value
reserveLegale = Txt_RLegale.Value
reportDebiteurN_1 = Txt_ReportDebiteur.Value
reportCrediteurN_1 = Txt_ReportCrediteur.Value
Dividende = Txt_Dividendes.Value
dotationRFacultative = Txt_RFacultative.Value

'Traitement des résultats

resultatRepartir = resultat - reportDebiteurN_1
If reserveLegale = capital * tauxMaxReserve Then
dotationRLegale = 0
Else
If reserveLegale + resultat * tauxRLegale > capital * tauxMaxReserve Then
dotationRLegale = Format(capital * tauxMaxReserve - reserveLegale, "0,00")
Else
dotationRLegale = Format(resultat * tauxRLegale, "0,00")
End If
resultatDistribuable = resultatRepartir - dotationRLegale + reportCrediteurN_1
ReportANouveauN = resultatDistribuable - dotationRFacultative - Dividende
'Afficher les résultats
Lab_Resultat.Caption = Format(resultat, "currency")
Lab_ReportDebiteur.Caption = Format(reportDebiteurN_1, "currency")
Lab_Ré[Link] = Format(resultatRepartir, "currency")
Lab_DotationRLegale.Caption = Format(dotationRLegale, "currency")
Lab_ReportCcrediteur.Caption = Format(reportCrediteurN_1, "currency")
Lab_ResultatDistribuable.Caption = Format(resultatDistribuable, "currency")
Lab_DotationRFacultative.Caption = Format(dotationRFacultative, "currency")
Lab_Dividende.Caption = Format(Dividende, "currency")
Lab_ReportANouveau.Caption = Format(ReportANouveauN, "currency")
End If
End Sub

Private Sub Cb_Effacer_Click()

'Reset
Txt_Capital = ""
Txt_Resultat = ""
Txt_RLegale = ""
Txt_ReportDebiteur = ""
Txt_ReportCrediteur = ""
Txt_RFacultative = ""
Txt_Dividendes = ""
Lab_Resultat = ""
Lab_ReportDebiteur = ""
Lab_RédultatARepartir = ""
Lab_DotationRLegale = ""
Lab_ReportCcrediteur = ""
Lab_ResultatDistribuable = ""
Lab_DotationRFacultative = ""
Lab_Dividende = ""
Lab_ReportANouveau = ""
Txt_Capital.SetFocus
End Sub

Private Sub Cb_Quitter_Click()

End
End Sub
© Groupe Eyrolles

Module 1
Sub AffectBenefice()
Fm_AffectationResultat.Show
End Sub

LA GESTION SOUS EXCEL ET VBA 3/3 SOLUTIONS CHAPITRE 8

 CHAPITRE 9 – L'ANALYSE
DE LA RELATION COÛT-VOLUME-PROFIT

Exercice 1 : reclassement des charges et tableur ••

La société Paranati doit réaliser au moins un chiffre d’affaires critique de 1 405 509 €, soit vendre 56 220 produits au
minimum pour réaliser un résultat égal à zéro, au-delà de ce chiffre d’affaires critique, elle devient bénéficiaire. La date du
seuil de rentabilité sera atteinte le 23 juillet N. La société Paranati peut perdre 43,78 % de son chiffre d’affaires sans devenir
déficitaire. Cette marge de sécurité est satisfaisante.
Le levier d'exploitation indique la réactivité du résultat face à une variation du chiffre d’affaires. Le levier d'exploitation
mesure la sensibilité du résultat à la variation du chiffre d’affaires. Dans notre application, une variation de 1 % du chiffre
d’affaires provoque une variation de 2,28 % du résultat. On peut considérer que cette variation est modérée et que le risque
d’exploitation est modéré.

Tableau des formules

Cellule Formule
D5 =C5*E5 Recopie vers le bas jusqu’à D11
F5 =C5*G5 Recopie vers le bas jusqu’à F11
C12 =SOMME(C5:C11) Recopie vers la droite jusqu’à D12 et F12
C16 =G17*G18
C17 =D12
C18 =C16-C17
C19 =F12
C20 =C18-C19
C24 =C19/D18
C25 =C24/G18
C26 =(12*C24)/C16
© Groupe Eyrolles

C27 =(C16-C24)/C16
C28 =1/C27

LA GESTION SOUS EXCEL ET VBA 1/3 SOLUTIONS CHAPITRE 9

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 2 : boîte de dialogue personnalisée et variabilité des charges •••

Code VBA
'==== Tableau de variabilité des charges et seuil de rentabilité ====
Option Explicit
'Déclaration des variables
Dim Qte As Integer
Dim PVU As Double
Dim CAHT As Double
Dim CV As Double
Dim CF As Double
Dim MCV As Double
Dim TxMCV As Double
Dim TxResultat As Double
Dim SRV As Double
Dim SRQ As Double
Dim RESULTAT As Double
Dim PM As Double
Dim MS As Double
Dim IndiceS As Double
Dim LevierE As Double

Private Sub Cmd_Calculer_Click()

'Saisie des données
CV = Txt_CV.Value
CF = Txt_CF.Value
PVU = Txt_PVH.Value
Qte = Txt_Qte.Value
'Traitement des données
CAHT = PVU * Qte
MCV = CAHT - CV
RESULTAT = MCV - CF
TxMCV = MCV / CAHT * 100
TxResultat = RESULTAT / CAHT * 100
SRV = CF / TxMCV * 100
SRQ = SRV / PVU
PM = (12 * SRV) / CAHT
MS = CAHT - SRV
IndiceS = MS / CAHT * 100
LevierE = 1 / IndiceS * 100
'Affichage des données
Lab_CAHT.Caption = Format(CAHT, "### ##0.00") & " € "
Lab_MSCV.Caption = Format(MCV, "### ##0.00") & " € "
Lab_CoutVariable.Caption = Format(CV, "### ##0.00") & " € "
Lab_CoutFixe.Caption = Format(CF, "### ##0.00") & " € "
Lab_Resultat.Caption = Format(RESULTAT, "### ##0.00") & " € "
Lab_TauxMSCV.Caption = Format(TxMCV, "### ##0.00") & " % "
Lab_TauxResultat.Caption = Format(TxResultat, "### ##0.00") & " % "
Lab_SR.Caption = Format(SRV, "### ##0.00") & " € "
Lab_SRQte.Caption = Format(SRQ, "### ##0.00") & " "
Lab_PM.Caption = Format(PM, "### ##0.00") & " mois "
Lab_MS.Caption = Format(MS, "### ##0.00") & " € "
Lab_IS.Caption = Format(IndiceS, "### ##0.00") & " % "
Lab_LevierExploitation.Caption = Format(LevierE, "### ##0.00") & " "
End Sub

Private Sub Cmd_Quitter_Click()

© Groupe Eyrolles

End
End Sub
Sub SeuilRentabilite()
Fml_VARIABILITE.Show
End Sub

LA GESTION SOUS EXCEL ET VBA 2/3 SOLUTIONS CHAPITRE 9

 CHAPITRE 9 – L'ANALYSE DE LA RELATION COÛT-VOLUME-PROFIT

Tableau des contrôles de la boîte de dialogue

Propriété Propriété
Contrôle Outils
Name Caption

Formulaire (UserForm) Fml_Variabilite Tableau de variabilité des charges

Zone de texte (TextBox) Txt_PVH

Zone de texte (TextBox)) Txt_Qte

Zone de texte (TextBox) Txt_CV

Zone de texte (TextBox) Txt_CF

Intitulé (Label) Lab_CAHT

Intitulé (Label) Lab_CoutVariable

Intitulé (Label) Lab_MSCV

Intitulé (Label) Lab_CoutFixe

Intitulé (Label) Lab_Resultat

Intitulé (Label) Lab_SR

Intitulé (Label) Lab_SRQte

Intitulé (Label) Lab_PM

Intitulé (Label) Lab_MS

Intitulé (Label) Lab_IS

Intitulé (Label) Lab_LevierExploitation

Intitulé (Label) Lab_TauxMSCV

Intitulé (Label) Lab_TauxResultat

Bouton de commande (CommandButton) Cmd_Calculer Calculer

Bouton de commande (CommandButton) Cmd_Quitter Quitter

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/3 SOLUTIONS CHAPITRE 9

 CHAPITRE 10 – LA VALORISATION DES STOCKS

Exercice 1 : résolution sur tableur EXCEL d’un CUMP après chaque entrée ••

Maquette : fiche de stock CUMP après chaque entrée

Tableau des formules

Cellule Formule

E5 =SI(C5>0;C5*D5;"") recopie jusqu’à E15

G6 =SI(F6="";"";J5) recopie jusqu’à G15
H6 =SI(F6="";"";F6*G6) recopie jusqu’à H15
I5 =C5
I6 =SI(ESTVIDE(C6);SI(ESTVIDE(F6);"";I5-F6);I5+C6) recopie jusqu’à I15
J5 =D5
J6 =SI(I6="";"";K6/I6) recopie jusqu’à J15
K5 =I5*J5
K6 =SI(C6="";SI(F6="";"";K5-H6);K5+E6) recopie jusqu’à K15

Exercice 2 : résolution sur tableur Excel d’un CUMP en fin de période••

Maquette : fiche de stock CUMP en fin de période

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/9 SOLUTIONS CHAPITRE 10

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Tableau des formules

Cellule Formule
E5 =SI(C5>0;C5*D5;"") recopie jusqu’à E15
G6 =SI(F6>0;$D$16;"") recopie jusqu’à G15
H6 =SI(F6="";"";F6*G6) recopie jusqu’à H15
I5 =C5
I6 =SI(ESTVIDE(C6);SI(ESTVIDE(F6);"";I5-F6);I5+C6) Recopie jusqu’à I15
J5 =D5
J6 =SI(A6="";"";$D$16) recopie jusqu’à J15
K5 =I5*J5
K6 =SI(A6="";"";I6*J6) recopie jusqu’à K15

Exercice 3 : programmation du premier entré – premier sorti en langage VBA ••••

Maquette : fiche de stock PEPS (FIFO)

Tableau des formules

Cellule Formule
E3 =SI(ESTVIDE(C3);0;C3*D3) recopie vers le bas jusqu’à E50

Code VBA
MODULE 1
Sub Calcul_PEPS_Clic()
'Déclaration des variables
Dim Tabstocks() As Long
Dim Qte As Integer
Dim i As Integer
Dim j As Integer
Dim Indice As Integer
Dim Montant As Long
'Traitement des données
© Groupe Eyrolles

Indice = 1
ReDim Tabstocks(1 To Calcul_lot(), 1 To 3)
For i = 1 To Calcul_lot()
Tabstocks(i, 1) = -1
Tabstocks(i, 2) = -1

LA GESTION SOUS EXCEL ET VBA 2/9 SOLUTIONS CHAPITRE 10

 CHAPITRE 10 – LA VALORISATION DES STOCKS

Tabstocks(i, 3) = -1
Next i
Tabstocks(Indice, 1) = Range("C" & 3).Value
Tabstocks(Indice, 2) = Range("D" & 3).Value
Tabstocks(Indice, 3) = Range("E" & 3).Value
Range("I" & 3).Value = Range("C" & 3).Value
Range("J" & 3).Value = Range("D" & 3).Value
Range("K" & 3).Value = Range("E" & 3).Value
i = 4
Range("A3:K3").Borders(xlEdgeTop).LineStyle = xlContinuous
Range("A3:K3").Borders(xlEdgeTop).ColorIndex = 0
Range("A3:K3").Borders(xlEdgeTop).Weight = xlThin
While Range("A" & i).Value
Range("A" & i & ":K" & i).Borders(xlEdgeTop).LineStyle = xlContinuous
Range("A" & i & ":K" & i).Borders(xlEdgeTop).ColorIndex = 0
Range("A" & i & ":K" & i).Borders(xlEdgeTop).Weight = xlThin
If Range("C" & i).Value Then
Indice = Indice + 1
Tabstocks(Indice, 1) = Range("C" & i).Value
Tabstocks(Indice, 2) = Range("D" & i).Value
Tabstocks(Indice, 3) = Range("E" & i).Value
End If
If Range("F" & i).Value Then
Qte = Range("F" & i).Value
Montant = 0
For j = 1 To Indice
If Tabstocks(j, 1) > Qte Then
Tabstocks(j, 1) = Tabstocks(j, 1) - Qte
Tabstocks(j, 3) = Tabstocks(j, 3) - Qte * Tabstocks(j, 2)
Montant = Montant + Qte * Tabstocks(j, 2)
Range("H" & i).Value = Montant
Range("G" & i).Value = Montant / Range("F" & i).Value
Qte = 0
Else
Qte = Qte - Tabstocks(j, 1)
Montant = Montant + Tabstocks(j, 1) * Tabstocks(j, 2)
Tabstocks(j, 1) = 0
Tabstocks(j, 3) = 0
End If
Next j
End If
For j = 1 To Indice
If j > 1 Then
i = i + 1
Range("A" & i).[Link]
End If
Range("I" & i).Value = Tabstocks(j, 1)
Range("J" & i).Value = Tabstocks(j, 2)
Range("K" & i).Value = Tabstocks(j, 3)
Next j
i = i + 1
Wend
End Sub
Private Function Calcul_lot()
Dim Lot As Integer
Dim i As Integer
Lot = 0
For i = 3 To 100
If Range("C" & i).Value Then
Lot = Lot + 1
End If
Next i
Calcul_lot = Lot
End Function

MODULE 2
Sub Reset_Clic()
'Déclaration de la variable
Dim i As Long
© Groupe Eyrolles

'Réinitialisation
i = 3
While Range("J" & i).Value
Range("A" & i & ":K" & i).Borders(xlEdgeTop).LineStyle = xlNone
If Range("A" & i).Value Then
Range("G" & i).Value = ""

LA GESTION SOUS EXCEL ET VBA 3/9 SOLUTIONS CHAPITRE 10

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Range("H" & i).Value = ""

Range("I" & i).Value = ""
Range("J" & i).Value = ""
Range("K" & i).Value = ""
i = i + 1
Else
Range("A" & i).[Link]
End If
Wend
End Sub

Exercice 4 : CUMP après chaque entrée et VBA ••••

Le tableau de synthèse des contrôles de l’interface graphique

Propriété Propriété
Contrôle Outils
Name Caption

Fiche de stock : CUMP après

Formulaire (UserForm) Fm_CUMP
chaque entrée

Zone de texte (TextBox) tbNumLigne

Zone de texte (TextBox) tbStockQuantiteFinale

Zone de texte (TextBox) tbCMUPStock

Zone de texte (TextBox) tbStockValeurFinale

Zone de texte (TextBox) Txt_Date

Zone de texte (TextBox) Txt_Libelle

Zone de texte (TextBox) Txt_Qte

Zone de texte (TextBox) Txt_CoutUnitaire

Zone de texte (TextBox) Txt_Montant

Zone de texte (TextBox) Txt_Qte1

Zone de texte (TextBox) Txt_CoutUnitaire1

Zone de texte (TextBox) Txt_Montant1

Zone de texte (TextBox) Txt_Qte2

Zone de texte (TextBox) Txt_CoutUnitaire2

Zone de texte (TextBox) Txt_Montant2

Listview ListView1
© Groupe Eyrolles

Bouton de commande (CommandButton) Cmd_Inserer Insérer

.../...

LA GESTION SOUS EXCEL ET VBA 4/9 SOLUTIONS CHAPITRE 10

 CHAPITRE 10 – LA VALORISATION DES STOCKS

Propriété Propriété
Contrôle Outils
Name Caption

Bouton de commande (CommandButton) Cmd_Reset

Bouton de commande (CommandButton) Cmd_Quitter Quitter

Bouton de commande (CommandButton) Cmd_Supprimer Supprimer

Code VBA
Option Explicit

Private Sub Txt_CoutUnitaire_AfterUpdate()

'Exécution du code après mise à jour
Dim list As ListItem
Dim indice As Integer

If Txt_Qte.Value = "" Then

Exit Sub
End If

indice = 3

Set list = [Link]()

If Txt_CoutUnitaire.Value <> "" And Txt_Qte.Value <> "" Then

Call MouvementerStock(list, Txt_Date.Value, Txt_Libelle.Value, Txt_Qte.Value,
Txt_CoutUnitaire.Value, indice)
Call CalculerCMUP(Txt_Qte.Value, Txt_CoutUnitaire.Value)
indice = 9
Call MouvementerStock(list, Txt_Date.Value, Txt_Libelle.Value, [Link],
[Link], indice)
Call MiseABlanc
Else: Exit Sub
End If

End Sub

Private Sub Txt_CoutUnitaire_Change()

'Exécution de cet ensemble de codes après toute action
If Txt_CoutUnitaire.Value = "" Then
Txt_Montant.Value = ""
Exit Sub
Else
Txt_Montant.Value = Txt_Qte.Value * Txt_CoutUnitaire.Value
End If

End Sub

Private Sub Txt_CoutUnitaire_KeyPress(ByVal KeyAscii As [Link])

'Sélection des chiffres, supprimer, retour arrière, activer uniquement les touches chiffres,
Select Case KeyAscii
Case 0 To 7
KeyAscii = 0
Case 9 To 43
KeyAscii = 0
Case 45 To 47
KeyAscii = 0
© Groupe Eyrolles

Case 58 To 126
KeyAscii = 0
End Select

End Sub

LA GESTION SOUS EXCEL ET VBA 5/9 SOLUTIONS CHAPITRE 10

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Private Sub Txt_CoutUnitaire2_AfterUpdate()

'Exécution du code après mise à jour je quitte
Dim list As ListItem
Dim indice As Integer
If Txt_Qte2.Value = "" Then
Exit Sub
End If
indice = 9
Set list = [Link]()
If Txt_CoutUnitaire2.Value <> "" And Txt_Qte2.Value <> "" Then
Call MouvementerStock(list, Txt_Date.Value, Txt_Libelle.Value, Txt_Qte2.Value,
Txt_CoutUnitaire2.Value, indice)
[Link] = Txt_Qte2.Value
[Link] = Txt_CoutUnitaire2.Value
[Link] = [Link] * Txt_CoutUnitaire2.Value
Call MiseABlanc
Else: Exit Sub
End If
End Sub

Private Sub Txt_CoutUnitaire2_Change()

'Changement dans le texbox du coût unitaire
If Txt_CoutUnitaire2.Value = "" Then
Txt_Montant2.Value = ""
Exit Sub
End If
If Txt_CoutUnitaire2.Value > 0 And Txt_Qte2.Value > 0 Then
Txt_Montant2.Value = Txt_Qte2.Value * Txt_CoutUnitaire2.Value
Else: Exit Sub
End If
End Sub

Private Sub Txt_Date_KeyPress(ByVal KeyAscii As [Link])

'Uniquement les touches actif slas et les chiffres
Select Case KeyAscii
Case Is < 47, Is > 57
KeyAscii = 0
End Select
End Sub

Private Sub Txt_qte_Change()

If Txt_Qte2.Value = "" Then
Txt_Montant.Value = ""
Exit Sub
End If
End Sub

Private Sub Txt_Qte_KeyPress(ByVal KeyAscii As [Link])

'Uniquement des chiffres entiers et virgules
Select Case KeyAscii
Case 0 To 7
KeyAscii = 0
Case 9 To 43
KeyAscii = 0
Case 45 To 47
KeyAscii = 0
Case 58 To 126
© Groupe Eyrolles

KeyAscii = 0
End Select
End Sub

LA GESTION SOUS EXCEL ET VBA 6/9 SOLUTIONS CHAPITRE 10

 CHAPITRE 10 – LA VALORISATION DES STOCKS

Private Sub Txt_Qte1_AfterUpdate()

Dim list As ListItem

Dim indice As Integer

If Txt_Date.Value = "" Then

Exit Sub
End If

If Txt_Libelle.Value = "" Then

Exit Sub
End If

If Txt_Qte1.Value = "" Then

Exit Sub
Else
If Txt_Qte1.Value > [Link] Then
MsgBox ("Quantité disponible insuffisante.")
Exit Sub
End If
End If

indice = 6

Set list = [Link]()

If Txt_Qte1.Value > 0 Or Txt_Qte1.Value <> "" Then

Txt_CoutUnitaire1.Value = [Link]
Call MouvementerStock(list, Txt_Date.Value, Txt_Libelle.Value, Txt_Qte1.Value,
Txt_CoutUnitaire1.Value, indice)
indice = 9
[Link] = [Link] - Txt_Qte1.Value
[Link] = [Link] - [Link] * Txt_Qte1.Value
Call MouvementerStock(list, Txt_Date.Value, Txt_Libelle.Value, [Link],
Txt_CoutUnitaire1.Value, indice)
Call MiseABlanc
Else: Exit Sub
End If

End Sub

Private Sub Txt_Qte1_Change()

If Txt_Qte1.Value = "" Or Txt_Qte1.Value > [Link] Then

Txt_Montant.Value = ""
Exit Sub
Else
Txt_CoutUnitaire1.Value = [Link]
Txt_Montant1.Value = Txt_Qte1.Value * [Link]
End If

End Sub

Private Sub Txt_Qte1_KeyPress(ByVal KeyAscii As [Link])

Select Case KeyAscii

Case 0 To 7
KeyAscii = 0
Case 9 To 43
KeyAscii = 0
Case 45 To 47
KeyAscii = 0
Case 58 To 126
© Groupe Eyrolles

KeyAscii = 0
End Select

End Sub

LA GESTION SOUS EXCEL ET VBA 7/9 SOLUTIONS CHAPITRE 10

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Private Sub Txt_Qte2_KeyPress(ByVal KeyAscii As [Link])

Select Case KeyAscii

Case 0 To 7
KeyAscii = 0
Case 9 To 43
KeyAscii = 0
Case 45 To 47
KeyAscii = 0
Case 58 To 126
KeyAscii = 0
End Select

End Sub

Private Sub UserForm_initialize()

'Ajoute 12 colonnes en spécifiant le nom de l'en-tête
'et la largeur des colonnes

With [Link]
.Clear
.Add , , "NumLigne", 0
.Add , , "Date", 55
.Add , , "Libellé", 85, lvwColumnLeft
.Add , , "Quantité", 60, lvwColumnCenter
.Add , , "Coût unitaire", 60, lvwColumnCenter
.Add , , "Montant", 60, lvwColumnCenter

.Add , , "Quantité", 60, lvwColumnCenter

.Add , , "Coût unitaire", 60, lvwColumnCenter
.Add , , "Montant", 60, lvwColumnCenter

.Add , , "Quantité", 60, lvwColumnCenter

.Add , , "Coût unitaire", 60, lvwColumnCenter
.Add , , "Montant", 60, lvwColumnCenter
End With

[Link] = True
[Link] = lvwReport
[Link] = True
[Link] = 1
End Sub

Private Sub Cmd_Quitter_Click()

End
End Sub

Private Sub Cmd_Reset_Click()

Txt_Date = ""
Txt_Libelle = ""
Txt_Qte = ""
Txt_CoutUnitaire = ""
Txt_Montant = ""
Txt_Qte1 = ""
Txt_CoutUnitaire1 = ""
Txt_Montant1 = ""
Txt_Qte2 = ""
Txt_CoutUnitaire2 = ""
Txt_Montant2 = ""
[Link] = ""
[Link] = ""

Call SupprimerListeItems
End Sub

Private Sub SupprimerListeItems()

© Groupe Eyrolles

Dim i, nbItem As Integer

nbItem = [Link]

LA GESTION SOUS EXCEL ET VBA 8/9 SOLUTIONS CHAPITRE 10

 CHAPITRE 10 – LA VALORISATION DES STOCKS

For i = 1 To nbItem

[Link] 1

Next i

End Sub

Private Sub MiseABlanc()

Txt_Date = ""
Txt_Libelle = ""
Txt_Qte = ""
Txt_CoutUnitaire = ""
Txt_Montant = ""
Txt_Qte1 = ""

Txt_CoutUnitaire1 = ""
Txt_Montant1 = ""
Txt_Qte2 = ""
Txt_CoutUnitaire2 = ""
Txt_Montant2 = ""
End Sub

Private Sub MouvementerStock(ByRef ListView1 As ListItem, ByRef DateMvt As Date, ByRef LibelleMvt As
String, ByRef QuantiteMvt As Double, ByRef puMvt As Double, ByVal indice As Integer)

'Traitement de la date et du libellé

[Link](1) = DateMvt
[Link](2) = LibelleMvt

'Traitement des stock initial/entrée/sortie

[Link](indice) = QuantiteMvt
indice = indice + 1
[Link](indice) = Format(puMvt, "##,##00.00")
indice = indice + 1
[Link](indice) = Format(QuantiteMvt * puMvt, "##,##00.00")

End Sub

Private Sub CalculerCMUP(ByRef QuantiteEntree As Double, ByRef PUEntree As Double)

If [Link] = "" Then

[Link] = 0
End If

[Link] = [Link] + QuantiteEntree

[Link] = [Link] + (QuantiteEntree * PUEntree)
[Link] = Format([Link] / [Link], "##,##00.00")

End Sub
Private Sub Cmd_Supprimer_Click()
[Link] ([Link])
End Sub
© Groupe Eyrolles

Module 1
Sub StocksCUMP()
Fm_CUMP.Show
End Sub

LA GESTION SOUS EXCEL ET VBA 9/9 SOLUTIONS CHAPITRE 10

 CHAPITRE 11 – LA GESTION DES COÛTS

Exercice 1 : charges indirectes sur tableur Excel •

ANNEXE 1

Maquette

ANNEXE 2

Tableau des formules

Cellule Formule

B12 =SOMME(C12:I12)

C12 :I12 =C4 recopie jusqu’à I4

C13 =C12
E13 =$C$13*E5 recopie jusqu’à I13

D14 =D12

E14 =$D$14*E6 recopie jusqu’à I14

B15 =SOMME(C15:I15)

C15 =C12-C13

D15 =D12-D14
E15 =SOMME(E12:E14) recopie jusqu’à I15
© Groupe Eyrolles

E19 =E15/E18 recopie jusqu’à I19

LA GESTION SOUS EXCEL ET VBA 1/4 SOLUTIONS CHAPITRE 11

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 2 : simulation de calcul de coûts sur tableur EXCEL ••

1. Concevez la maquette relative au calcul de coûts en annexe 1.
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/4 SOLUTIONS CHAPITRE 11

 CHAPITRE 11 – LA GESTION DES COÛTS

2. Implantez des formules.

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/4 SOLUTIONS CHAPITRE 11

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/4 SOLUTIONS CHAPITRE 11

 CHAPITRE 12 – LES SOLDES INTERMÉDIAIRES
DE GESTION

Exercice 1 : les SIG et la CAF ••

DÉTERMINATION DE LA CAPACITÉ D'AUTOFINANCEMENT N

EXCÉDENT BRUT D'EXPLOITATION 929 320

+ Autres produits (d'exploitation) 1 650

– Autres charges (d'exploitation) 0

+ Produits financiers (sauf reprises sur dépréciations) 11 062

– Charges financières (sauf dotations aux amortissements et dépréciations) – 45 000

+ Produits exceptionnels 6 000

(Sauf : produits des cessions d'immobilisations ;

reprises sur dépréciations exceptionnelles)

– Charges exceptionnelles – 8 000

(sauf : valeur comptable des immobilisations cédées ;

dotations aux amortissements et aux dépréciations

exceptionnels)

– Impôts sur les bénéfices – 185 000

CAPACITÉ D'AUTOFINANCEMENT 710 032

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/4 SOLUTIONS CHAPITRE 12

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

DÉTERMINATION DE LA CAPACITÉ D'AUTOFINANCEMENT N

RÉSULTAT DE L'EXERCICE 234 776

+ Dotations aux amortissements et aux dépréciations :
d'exploitation 189 000
financiers 12 000
exceptionnels 350 000
– Reprises sur amortissements et dépréciations :
d'exploitation – 50 000
financiers – 744
exceptionnels 0
+ Valeurs comptables des éléments d'actif cédés 90 000
– Produits de cession des éléments d'actif – 115 000
CAPACITÉ D'AUTOFINANCEMENT 710 032

Exercice 2 : mise en œuvre sur tableur : SIG et CAF ••

Tableau des SIG

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/4 SOLUTIONS CHAPITRE 12

 CHAPITRE 12 – LES SOLDES INTERMÉDIAIRES DE GESTION

Formule des SIG

CAF selon des deux méthodes

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/4 SOLUTIONS CHAPITRE 12

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Formule de la CAF
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/4 SOLUTIONS CHAPITRE 12

 CHAPITRE 13 – LE BILAN FONCTIONNEL

Exercice 1 : bilan fonctionnel et ratios ••

Commentez.
La trésorerie nette est négative car le FRNG est inférieur au BFR. Cela signifie que l’entreprise ne dispose pas de disponibilités
suffisantes. Elle doit donc avoir recours aux découverts bancaires pour assurer son équilibre financier. Le FRNG est positif
mais insuffisant pour couvrir le BFR. Les ressources stables sont donc trop faibles. Le BFR a augmenté ce qui peut provenir
soit de l’augmentation des créances clients, soit des stocks. Il faudrait envisager de réduire le délai de paiement accordé à
certains clients (durée moyenne 52 jours), réduire aussi le niveau des stocks (durée de stockage 80 jours), soit augmenter le
délai fournisseurs (durée moyenne 46 jours). Le ratio de financement des immobilisations (1,36) est supérieur à 1, l'entreprise
finance ses investissements par des ressources stables. L'endettement est assez faible, le ratio d'endettement est de 0,19, ceci
signifie que les emprunts représentent 19 % des ressources propres. L’entreprise peut s’endetter à long terme pour augmenter
ses ressources stables, car elles sont moins coûteuses que les découverts bancaires.
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/4 SOLUTIONS CHAPITRE 13

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION

Exercice 2 : mise en œuvre sur tableur d’un bilan fonctionnel ••

Bilan fonctionnel
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/4 SOLUTIONS CHAPITRE 13

 CHAPITRE 13 – LE BILAN FONCTIONNEL

Formules des bilans

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/4 SOLUTIONS CHAPITRE 13

 PARTIE 2 – LES OUTILS COMPTABLES DE GESTION
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/4 SOLUTIONS CHAPITRE 13

 CHAPITRE 14 – LES CALCULS FINANCIERS

Exercice 1 : la valeur actuelle en VBA ••

Programme VBA
Option Explicit
Sub VALEUR_ACTUELLE()
Dim Capital As Single
Dim Taux As Single
Dim Duree As Integer
Dim VA As Single
Capital = InputBox("Saisir le Capital:")
Taux = InputBox("Saisir le taux:")
Duree = InputBox("Saisir la durée:")
VA = round((Capital * (1 + Taux) ^ -Duree),2)
MsgBox ("La Valeur actuelle est de :" & VA & " € ")
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/4 SOLUTIONS CHAPITRE 14

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Exercice 2 : UserForm et calculs financiers ***

Ce projet met en œuvre une boîte de dialogue personnalisée relative à des calculs financiers. L’utilisateur entre des informa-
tions sur une boîte de dialogue, le capital, la durée, le taux d’intérêt, puis valide ces données.
Ces informations sont alors passées dans un programme qui les exploite et calcule automatiquement la valeur acquise et la
valeur actuelle.
L’UserForm est réalisé en deux étapes :
Q création des contrôles (intitulés, barre de défilement, toupie, boutons de commande et zone de texte…) dans une feuille
UserForm ;
Q rédaction du code et de la macro permettant l’exploitation des données fournies par l’utilisateur.

Module 1 VBA
Sub CALCUL()
Form_Financier.Show
End Sub

Code VBA
Private Sub Cmd_Fermer_Click()
Form_Financier.Hide
Unload Form_Financier
End Sub

Private Sub Scrob_Taux_Change()

Txt_Taux.Text = Scrob_Taux.Value / 100
End Sub
Private Sub Spinb_Durée_spinDown()
Txt_Duré[Link] = Txt_Durée - 1
End Sub

Private Sub SpinbCapital_spinDown()

Txt_Capital.Value = Txt_Capital.Value - 1
End Sub

Private Sub SpinbCapital_SpinUp()

Txt_Capital.Value = Txt_Capital.Value + 1
End Sub

Private Sub Spinb_Durée_SpinUp()

Txt_Duré[Link] = Txt_Durée + 1
End Sub

Private Sub Txt_Capital_Change()

Txt_Capital.Value = Val(Txt_Capital.Text) * 1
Lab_Valeur_Actuelle.Caption = Format(Txt_Capital.Value * ((1 + (Txt_Taux.Value / 100)) ^ -
Txt_Duré[Link]), "Currency")
Lab_Valeur_Acquise.Caption = Format(Txt_Capital.Value * ((1 + (Txt_Taux.Value / 100)) ^
Txt_Duré[Link]), "Currency")
End Sub

Private Sub Txt_Durée_Change()

Lab_Valeur_Actuelle.Caption = Format(Txt_Capital.Value * ((1 + (Txt_Taux.Value / 100)) ^ -
Txt_Duré[Link]), "Currency")
Lab_Valeur_Acquise.Caption = Format(Txt_Capital.Value * ((1 + (Txt_Taux.Value / 100)) ^
Txt_Duré[Link]), "Currency")
End Sub

Private Sub Txt_Taux_Change()

© Groupe Eyrolles

Lab_Valeur_Actuelle.Caption = Format(Txt_Capital.Value * ((1 + (Txt_Taux.Value / 100)) ^ -

Txt_Duré[Link]), "Currency")
Lab_Valeur_Acquise.Caption = Format(Txt_Capital.Value * ((1 + (Txt_Taux.Value / 100)) ^
Txt_Duré[Link]), "Currency")
End Sub

LA GESTION SOUS EXCEL ET VBA 2/4 SOLUTIONS CHAPITRE 14

 CHAPITRE 14 – LES CALCULS FINANCIERS

ANNEXE 2
Le tableau de synthèse des contrôles

Propriété Propriété
Contrôle Outils
Name Caption

Formulaire (UserForm) Form_Financier Calculs financiers à intérêts composés

Toupie (SpinButton) SpinbCapital

Zone de texte (TEXTBOX) Txt_Capital

Zone de texte (TEXTBOX) Txt_Durée

Toupie (SpinButton) Spinb_Durée

Zone de texte (TEXTBOX) Txt_Taux

Barre de défilement (ScrollBar) Scrob_Taux

Intitulé (LABEL) Lab_Valeur_Acquise

Intitulé (LABEL) Lab_Valeur_Actuelle

Bouton de commande (CommandButton) Cmd_Fermer Fermer

Exercice 3 : UserForm et coût total d’emprunt indivis ••

Private Sub Cmd_Calculer_Click()
'Déclaration des variables
Dim Capital As Double
Dim Duree As Integer
Dim ANNUITE As Double
Dim Taux As Double
Dim CoûtEmprunt As Double
'Saisie des données
Capital = Format(Txt_Capital.Value, "currency")
Duree = Txt_Duree.Value
Taux = Txt_Taux.Value
'Traitement des données
ANNUITE = (Capital * Taux * (1 + Taux) ^ Duree / ((1 + Taux) ^ Duree - 1)) 'calcul de l'annuité
CoûtEmprunt = (ANNUITE * Duree)
'Affichage des résultats
Lab_Annuite.Caption = Format(ANNUITE, "currency")
Lab_CoutEmprunt.Caption = Format(CoûtEmprunt, "currency")
Lab_Commentaire.Caption = "Le coût de l'emprunt ( amortissement plus intérêt ) est de :"
Lab_Cout.Caption = Format(Lab_CoutEmprunt, "currency")
End Sub

Private Sub Cmd_Quitter_Click()

End
End Sub
© Groupe Eyrolles

Module VBA
Sub ANNUITES()
Formulaire_Annuité.Show
End Sub

LA GESTION SOUS EXCEL ET VBA 3/4 SOLUTIONS CHAPITRE 14

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

ANNEXE 2

Le tableau de synthèse des contrôles

Propriété
Contrôle Outils Propriété Caption
Name
Formulaire (UserForm) Formulaire_Annuité Coût total d'un emprunt indivis

Zone de texte (TEXTBOX) Txt_Capital

Zone de texte (TEXTBOX) Txt_Duree

Zone de texte (TEXTBOX) Txt_Taux

Intitulé (LABEL) Lab_Annuite

Intitulé (LABEL) Lab_CoutEmprunt

Intitulé (LABEL) Lab_Commentaire

Intitulé (LABEL) Lab_Cout

Bouton de commande (CommandButton) Cmd_Calculer Calculer

Bouton de commande (CommandButton) Cmd_Quitter Quitter

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/4 SOLUTIONS CHAPITRE 14

 CHAPITRE 15 – L’EMPRUNT INDIVIS

Exercice 1 : emprunt à annuités constantes •

Formule mathématique financière :
1) Calculez l’annuité constante.
i
a = C × ----------------------------
–n
-
1 – (1 + i)
0,05
a = 50 000 × ----------------------------
–5
= 11 548,74
1 – ( 1,05 )
2) Complétez le tableau de remboursement à annuités constantes.

3) Enregistrez au journal les opérations comptables relatives à l’emprunt en 2010.

Lors de l’obtention de l’emprunt

01/03/2010
512 Banque 50 000
164 Emprunt 50 000
Selon le tableau d’emprunt

Le remboursement de l’emprunt

01/03/2010
164 Emprunt 9 048,74
661 Charges d’intérêts 2 500,00
512 Banque 11 548,74
Selon le tableau d’emprunt
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/7 SOLUTIONS CHAPITRE 15

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

La régularisation de l’emprunt

31/12/2010
661 Charges d’intérêts 2 083,33
1688 Intérêts courus 2 083,33
Selon le tableau d’emprunt

10
2 500 × --------- = 2 083,33
212

Exercice 2 : programmation d’un emprunt à annuités constantes en VBA ••

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/7 SOLUTIONS CHAPITRE 15

 CHAPITRE 15 – L’EMPRUNT INDIVIS

Code VBA
Sub Emprunt()
'Déclaration des variables
Dim indice, Annee, Durée As Integer
Dim Taux As Single
Dim CapitalRestant, Interet, Amort, Capital, annuite As Currency
Dim Ligne As Variant
'Traitement des données
Capital = InputBox("Saisissez le montant de l'emprunt", "Emprunt indivis")
Durée = InputBox("Saisissez la durée de l'emprunt", "Emprunt indivis")
Taux = InputBox("Saisissez le taux d'intérêt 0,07 pour 7%", "Emprunt indivis")
annuite = Format(Capital * Taux / (1 - (1 + Taux) ^ (-Durée)), "Currency")
CapitalRestant = Format(Capital, "Currency")
Ligne = "Année" & vbTab & "Capital restant dû" & vbTab & " Intérêt " & vbTab & vbTab &
"Amortissement" & vbTab & "Annuité" & vbCrLf
'Boucle des données
For indice = 1 To Durée
Interet = Format((CapitalRestant * Taux), "Currency")
Amort = Format((annuite - Interet), "Currency")
Ligne = Ligne & vbCrLf & indice & vbTab & CapitalRestant & vbTab & Interet & vbTab & Amort & vbTab
& annuite
CapitalRestant = Format((CapitalRestant - Amort), "Currency")
Annee = Annee + 1
Next indice
'Affichage du tableau de remboursement
reponse = MsgBox(Ligne, , "Emprunt indivis")
End Sub

Exercice 3 : amortissement à annuités constantes et UserForm •••

ANNEXE 1

Interface graphique utilisateur

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/7 SOLUTIONS CHAPITRE 15

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

ANNEXE 2

Tableau des contrôles dans la boîte de dialogue personnalisée et leurs propriétés

Propriété
Contrôle Outils Propriété Caption
Name

Tableau d'amortissement d'un emprunt

Formulaire (UserForm) FmlEmpruntC
indivis

Zone de texte (TextBox) MontEmprunt

Zone de texte (TextBox) Taux

Zone de texte (TextBox) Duree

Zone de liste (ListBox) ListBox1

Intitulé (Label) LabAnnuite

Intitulé (Label) LabCoutInteret

Intitulé (Label) LabCoutCredit

Boutons de commande (CommandButton) CmdCalculer Calculer

Boutons de commande (CommandButton) CmdReset Remise à zéro

Boutons de commande (CommandButton) CmdQuitter Quitter

Code vba
Private Sub CmdCalculer_Click()
'Variables de travail
Dim indice, numAnnee As Integer
Dim amortissement, annuite, CapitalDu, interet, cumulInteret As Double
Dim capitalAmorti, cumulCapitalAmorti As Double
Dim ligneEmprunt As Variant

'Calcul de l'annuité constante

annuite = [Link] * ([Link] / 100) / (1 - (1 + [Link] / 100) ^ -[Link])

'Récupération de l'année système

numAnnee = Year(Date)

'Boucle finie qui calcule et affiche les données de l'emprunt

For indice = 1 To [Link]

'Traitement de la première ligne du tableau d'amortissement

If indice = 1 Then
interet = MontEmprunt * [Link] / 100
amortissement = annuite - interet
CapitalDu = [Link] - amortissement
ligneEmprunt = Str(numAnnee) + vbTab + vbTab + Format(MontEmprunt, "## ### ##0.00") + vbTab + vbTab + _
© Groupe Eyrolles

Format(interet, "# ### ##0.00") + vbTab + vbTab + vbTab + _

Format(amortissement, "### ##0.00") + vbTab + vbTab + Format(annuite, "## ### ##0.00") + _
vbTab + vbTab + Format(CapitalDu, "## ### ##0.00")
[Link] (ligneEmprunt)

LA GESTION SOUS EXCEL ET VBA 4/7 SOLUTIONS CHAPITRE 15

 CHAPITRE 15 – L’EMPRUNT INDIVIS

'Traitement des autres lignes du tableau

Else
interet = CapitalDu * Taux / 100
amortissement = annuite - interet
ligneEmprunt = Str(numAnnee) + vbTab + vbTab + _
Format(CapitalDu, "## ### ##0.00") + vbTab + vbTab + _
Format(interet, "# ### ##0.00") + vbTab + vbTab + vbTab + _
Format(amortissement, "### ##0.00") + vbTab + vbTab + _
Format(annuite, "### ##0.00") + vbTab + vbTab + Format(CapitalDu - amortissement, "## ###
##0.00")
CapitalDu = CapitalDu - amortissement
[Link] (ligneEmprunt)
End If

'Ajout d'une année

numAnnee = numAnnee + 1

'Incrémentation des cumuls

cumulInteret = cumulInteret + interet
cumulCapitalAmorti = cumulCapitalAmorti + amortissement

Next indice

'Affichage du général
ligneEmprunt =
"======================================================================================================
============="
[Link] (ligneEmprunt)
ligneEmprunt = "TOTAL GENERAL" + vbTab + vbTab + vbTab + Format(cumulInteret, "### ##0.00") + vbTab
+ vbTab + vbTab + Format(cumulCapitalAmorti, "## ### ##0.00")
[Link] (ligneEmprunt)
ligneEmprunt =
"======================================================================================================
============="
[Link] (ligneEmprunt)
'Affichage des résultats
[Link] = Format(annuite, "#### ##0.00 €")
[Link] = Format(cumulInteret, "#### ##0.00 €")
[Link] = Format(cumulInteret + cumulCapitalAmorti, "### #### ##0.00 €")
End Sub

Private Sub CmdQuitter_Click()

Unload Me
End Sub

Private Sub CmdReset_Click()

'Mise à zéro des variables de l'emprunt
[Link]
MontEmprunt = ""
Taux = ""
Duree = ""
LabAnnuite = ""
LabCoutInteret = ""
LabCoutCredit = ""
End Sub
© Groupe Eyrolles

Module 1
Sub EmpruntC()
[Link]
End Sub

LA GESTION SOUS EXCEL ET VBA 5/7 SOLUTIONS CHAPITRE 15

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Exercice 4 : emprunt à amortissements constants et UserForm •••

ANNEXE 1
Interface graphique utilisateur

ANNEXE 2
Tableau des contrôles et leurs propriétés

Propriété
Contrôle Outils Propriété Caption
Name

Tableau d'amortissement d'un emprunt

Formulaire (UserForm) FmlEmpruntC
indivis

Zone de texte (TextBox) MontEmprunt

Zone de texte (TextBox) Taux

Zone de texte (TextBox) Duree

Zone de liste (ListBox) ListBox1

Intitulé (Label) LabRemboursement

Intitulé (Label) LabCoutInteret

Intitulé (Label) LabCoutCredit

Boutons de commande (CommandButton) CmdCalculer Calculer

Boutons de commande (CommandButton) CmdReset Remise à zéro

© Groupe Eyrolles

Boutons de commande (CommandButton) CmdQuitter Quitter

LA GESTION SOUS EXCEL ET VBA 6/7 SOLUTIONS CHAPITRE 15

 CHAPITRE 15 – L’EMPRUNT INDIVIS

Code VBA
Private Sub CmdCalculer_Click()

'Variables de travail
Dim indice, numAnnee As Integer
Dim amortissement, annuite, CapitalDu, interet, cumulInteret As Double
Dim capitalAmorti, cumulCapitalAmorti, cumulPaye As Double
Dim ligneEmprunt As Variant

'Calcul de l'amortissement constant

amortissement = [Link] / [Link]

'Récupération de l'année système

numAnnee = Year(Date)

'Boucle finie qui calcule et affiche les données de l'emprunt

For indice = 1 To [Link]

'Traitement de la première ligne du tableau d'amortissement

If indice = 1 Then
interet = MontEmprunt * [Link] / 100
ligneEmprunt = Str(numAnnee) + vbTab + vbTab + Format(MontEmprunt, "## ### ##0.00") + vbTab
+ vbTab + _
Format(interet, "# ### ##0.00") + vbTab + vbTab + _
Format(amortissement, "### ##0.00") + vbTab + vbTab + Format(amortissement + interet, "## ###
##0.00") + _
vbTab + vbTab + Format(MontEmprunt - amortissement * indice, "## ### ##0.00")
[Link] (ligneEmprunt)

'Traitement des autres lignes du tableau d'amortissement

Else
interet = (MontEmprunt - amortissement * (indice - 1)) * Taux / 100
ligneEmprunt = Str(numAnnee) + vbTab + vbTab + _
Format(MontEmprunt - amortissement * (indice - 1), "## ### ##0.00") + vbTab + vbTab + _
Format(interet, "# ### ##0.00") + vbTab + vbTab + _
Format(amortissement, "### ##0.00") + vbTab + vbTab + _
Format(amortissement + interet, "### ##0.00") + vbTab + vbTab + Format(MontEmprunt -
(amortissement * indice), "## ### ##0.00")
[Link] (ligneEmprunt)
End If

'Ajout d'une année

numAnnee = numAnnee + 1

'Incrémentation des cumuls

cumulInteret = cumulInteret + interet
cumulCapitalAmorti = amortissement * indice

Next indice

'Affichage du général
ligneEmprunt =
"======================================================================================================
============="
[Link] (ligneEmprunt)
ligneEmprunt = "TOTAL " + vbTab + vbTab + vbTab + vbTab + vbTab + Format(cumulInteret, "### ##0.00")
+ vbTab + vbTab + Format(cumulCapitalAmorti, "## ### ##0.00")
[Link] (ligneEmprunt)
ligneEmprunt =
"======================================================================================================
============="
[Link] (ligneEmprunt)
'Affichage des résultats
[Link] = Format(amortissement, "#### ##0.00 €")
[Link] = Format(cumulInteret, "#### ##0.00 €")
[Link] = Format(cumulInteret + cumulCapitalAmorti, "### #### ##0.00 €")
© Groupe Eyrolles

End Sub

Private Sub CmdQuitter_Click()

Unload Me
End Sub

LA GESTION SOUS EXCEL ET VBA 7/7 SOLUTIONS CHAPITRE 15

 CHAPITRE 16 – LA RENTABILITÉ
DES INVESTISSEMENTS

Exercice 1 : projet d’investissement et Excel VBA ••

Tableau des contrôles dans la boîte de dialogue personnalisée et leurs propriétés

Propriété
Contrôle Outils Propriété Caption
Name

Formulaire (UserForm) Form_Investissement La rentabilité des investissements

Zone de texte (TextBox) Txt_Invest

Zone de texte (TextBox) Txt_CAF1

Zone de texte (TextBox) Txt_CAF2

Zone de texte (TextBox) Txt_CAF3

Zone de texte (TextBox) Txt_CAF4

Zone de texte (TextBox) Txt_CAF5

Zone de texte (TextBox) Txt_VR

Intitulé (Label) Lab_VAN

Intitulé (Label) Lab_IP

Boutons de commande (CommandButton) CB_Calculer Calculer

Boutons de commande (CommandButton) CB_Quitter Quitter

Code VBA
Option Explicit
'Déclaration des variables
Const Taux = 0.1
Dim MI As Single
Dim RNE1 As Single
Dim RNE2 As Single
Dim RNE3 As Single
Dim RNE4 As Single
Dim RNE5 As Single
Dim VResid As Single
Dim VAN As Single
Dim IProfit As Single

Private Sub CB_Calculer_Click()

MI = Txt_Invest.Value
RNE1 = Txt_CAF1.Value
RNE2 = Txt_CAF2.Value
RNE3 = Txt_CAF3.Value
RNE4 = Txt_CAF4.Value
© Groupe Eyrolles

RNE5 = Txt_CAF5.Value
VResid = Txt_VR.Value
VAN = -MI + (RNE1 * (1 + Taux) ^ -1 + RNE2 * (1 + Taux) ^ -2 + RNE3 * (1 + Taux) ^ -3 + RNE4 * (1 + Taux)
^ -4 + RNE5 * (1 + Taux) ^ -5 + VResid * (1 + Taux) ^ -5)
IProfit = (VAN + MI) / MI

LA GESTION SOUS EXCEL ET VBA 1/7 SOLUTIONS CHAPITRE 16

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Lab_VAN.Caption = Format(VAN, "currency")

Lab_IP.Caption = Format(IProfit, "### ##0.00")
End Sub

Private Sub CB_Quitter_Click()

End
End Sub

Programme VBA MODULE 1

Sub MONT_INVEST()
Form_Investissement.Show
End Sub

Exercice 2 : taux interne de rentabilité et Excel VBA •••

ANNEXE 2

Tableau des contrôles et leurs propriétés

Propriété
Contrôle Outils Propriété Caption Propriété FONT
Name

Police Tahoma Gras

Taux interne de rentabilité d'un
Formulaire (UserForm) CalculerTIR
investissement corporel
taille 10

Zone de texte (TextBox) TB_MtInvestissement

Zone de texte (TextBox) TB_Somme1

Zone de texte (TextBox) TB_Somme2

Zone de texte (TextBox) TB_Somme3

Zone de texte (TextBox) TB_Somme4

Zone de texte (TextBox) TB_Somme5

Zone de texte (TextBox) TB_Tir

Intitulé (Label) Lab_Résultat

Boutons de commande (CommandButton) CB_Calculer Calculer

Boutons de commande (CommandButton) CB_Quitter Quitter

Code VBA
Private Function ValActualiser(ByVal taux As Double, ByVal Somme1 As Double, ByVal Somme2 As Double,
© Groupe Eyrolles

ByVal Somme3, ByVal Somme4 As Double, ByVal Somme5 As Double) As Double

ValActualiser = Somme1 * (1 + taux / 100) ^ -1 + Somme2 * (1 + taux / 100) ^ -2 + Somme3 * (1 + taux

/ 100) ^ -3 + Somme4 * (1 + taux / 100) ^ -4 + Somme5 * (1 + taux / 100) ^ -5
End Function

LA GESTION SOUS EXCEL ET VBA 2/7 SOLUTIONS CHAPITRE 16

 CHAPITRE 16 – LA RENTABILITÉ DES INVESTISSEMENTS

Private Sub CB_Calculer_Click()

Const Precision As Double = 0.001

Dim rCalculerTir As Double

rCalculerTir = CalculerTIR(TB_MtInvestissement.Value, Precision)

Call AfficherTir(rCalculerTir)
End Sub

Private Function CalculerTIR(ByVal Montant As Double, ByVal Precision As Double) As Double

Dim taux, taux1, pente, taux2, rValActualiser, ecart, delta1, delta2, tir As Double
For taux = 1 To 20 Step Precision
rValActualiser = Abs(ValActualiser(taux, TB_Somme1.Value, TB_Somme2.Value, TB_Somme3.Value,
TB_Somme4.Value, TB_Somme5.Value))
ecart = rValActualiser - Montant
If ecart > 0 Then
delta1 = ecart
taux1 = taux
Else
delta2 = -ecart
taux2 = taux
pente = ((delta2 - delta1) / (taux2 - taux1))
tir = taux1 + (delta1 / pente)
CalculerTIR = tir
Exit Function
End If
Next taux
End Function

Private Sub AfficherTir(ByVal tir As Double)

TB_Tir.Value = Format(tir, "##0.000")
Lab_Ré[Link] = "Le taux interne de rentabilité de ce projet d'investissement est de" &
Str(Round(tir, 2)) & " %"
End Sub

Private Sub CB_Quitter_Click()

End
End Sub

Programme VBA MODULE 1

Sub LancerCalcul_Click()
[Link]
End Sub

Exercice 3 : valeur actuelle nette (VAN) et Excel VBA ••••

Tableau des contrôles et leurs propriétés

Contrôle Outils Propriété Name Propriété Caption

Formulaire (UserForm) Form_Investissement La rentabilité des investisse-
ments

Zone de liste modifiable (Combobox) CB_DureeInvest

Zone de texte (TextBox) TB_MtInvestissement

Zone de texte (TextBox) TB_TxtActualisation

Zone de texte (TextBox) TB_Van

© Groupe Eyrolles

Boutons de commande (CommandButton) CB_Calculer Calculer

Boutons de commande (CommandButton) CB_Quitter Quitter

LA GESTION SOUS EXCEL ET VBA 3/7 SOLUTIONS CHAPITRE 16

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Code VBA
Option Explicit
Private Sub CB_Calculer_Click()
'Déclaration des variables
Dim nombreTB, indice As Integer
Dim tabTB(20), tabFNA(20) As Variant
Dim NomTB As String
Dim crCalculerVAN, crTailleTabFNA, crTailleTabTB As Double
Dim cr As Boolean
If TB_MtInvestissement.Value <= 0 Or TB_MtInvestissement.Value = "" Then
MsgBox ("Le Montant de l'Investissement est obligatoire.")
Exit Sub
End If
If TB_TxtActualisation.Value <= 0 Or TB_TxtActualisation.Value = "" Then
MsgBox ("Le Taux d'Actualisation est obligatoire.")
Exit Sub
End If
nombreTB = NombreVariable()

Do While nombreTB > 0

NomTB = "CAF" & indice + 1
tabTB(indice) = GetValeurTextBox(FZoneSaisie, NomTB)
indice = indice + 1
nombreTB = nombreTB - 1
Loop

nombreTB = NombreVariable()
indice = 0
Do While nombreTB > 0
tabFNA(indice) = CalculerFluxActualises(tabTB, TB_TxtActualisation.Value, indice)
Call SetValeurTextBox(FZoneSaisie, "FNA" & (indice + 1), Format(tabFNA(indice), "## ###0.00"))
nombreTB = nombreTB - 1
indice = indice + 1
Loop

'Affichage des flux nets actualisés

nombreTB = NombreVariable()
indice = 0
Do While nombreTB > 0
Call SetValeurTextBox(FZoneSaisie, "FNA" & (indice + 1), Format(tabFNA(indice), "## ###0.00"))
nombreTB = nombreTB - 1
indice = indice + 1
Loop

'Calcul et affichage de la valeur actualisée nette

crTailleTabFNA = TailleTableau(tabFNA())
crCalculerVAN = CalculerVAN(TB_MtInvestissement.Value, tabFNA, crTailleTabFNA)
Call AfficherDansTextBox(TB_Van, Format(crCalculerVAN, "### ###0.00"))
End Sub

Private Sub CB_Quitter_Click()

End
End Sub

Private Sub UserForm_Initialize()

Dim NbSerie As Single
For NbSerie = 3 To 6 Step 1
CB_DureeInvest.AddItem (NbSerie)
© Groupe Eyrolles

CB_DureeInvest.ListIndex = 0
Next NbSerie
End Sub

LA GESTION SOUS EXCEL ET VBA 4/7 SOLUTIONS CHAPITRE 16

 CHAPITRE 16 – LA RENTABILITÉ DES INVESTISSEMENTS

Private Sub CB_DureeInvest_Change()

'Déclaration des variables de travail
Const nbTBMax As Integer = 30
Const width As Integer = 70
Const height As Integer = 20
Const size As Integer = 10
Const ecartLeft As Integer = 72
Const top As Integer = 60
Dim dureeProjet As Single
Dim left, indiceNom, annee, nbTextbox As Integer
Dim locked As Boolean
'Destruction des Text_Box CAF et FNA avant la création de nouveaux
indiceNom = 1
nbTextbox = nbTBMax
Do While nbTextbox >= 0
Call SupprimerControle(FZoneSaisie, "CAF" & indiceNom)
Call SupprimerControle(FZoneCalcul, "FNA" & indiceNom)
nbTextbox = nbTextbox - 1
indiceNom = indiceNom + 1
Loop
TB_MtInvestissement.Value = ""
TB_TxtActualisation.Value = ""
TB_Van.Value = ""
left = 7
'Récupération de la durée du projet et Ajout/concaténation de CAF, FNA et de l'année+1 dans la
fenêtre (Frame) FZoneSaisie
annee = GetAnnee() + 1
dureeProjet = NombreVariable()
Do While dureeProjet > 0
Call AjouterLabel(FZoneSaisie, "C.A.F " & annee, width, height, size, top - 20, left)
Call AjouterLabel(FZoneCalcul, "F.N.A " & annee, width, height, size, top - 20, left)
annee = annee + 1
dureeProjet = dureeProjet - 1
left = left + ecartLeft
Loop
locked = False
left = 7
indiceNom = 1
'Récupération de la durée du projet et Ajout d'autant de Text_Box CAF, FNA dans les fenêtres (Frame)
FZoneSaisie, FZoneCalcul
dureeProjet = NombreVariable()
Do While dureeProjet > 0
Call AjouterTextBox(FZoneSaisie, "CAF" & indiceNom, width, height, size, top, left, locked)
locked = True
Call AjouterTextBox(FZoneCalcul, "FNA" & indiceNom, width, height, size, top, left, locked)
locked = False
left = left + ecartLeft
dureeProjet = dureeProjet - 1
indiceNom = indiceNom + 1
Loop
End Sub

Private Function CalculerFluxActualises(ByRef TableauTB(), ByVal Taux As Double, ByVal indice As

Integer) As Double
Taux = 1 + (Taux / 100)
CalculerFluxActualises = TableauTB(indice) * Taux ^ -(indice + 1)
End Function

Private Function CalculerSommeFNA(ByRef TableauTB(), ByVal Taux As Double, ByVal TailleTab As Integer)
As Double
Dim indice As Integer
Dim cumulFlux As Double
Taux = 1 + (Taux / 100)
Do While indice <= TailleTab
cumulFlux = cumulFlux + TableauTB(indice) * Taux ^ -(indice + 1)
indice = indice + 1
© Groupe Eyrolles

Loop
CalculerSommeFNA = cumulFlux

End Function

LA GESTION SOUS EXCEL ET VBA 5/7 SOLUTIONS CHAPITRE 16

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Private Function CalculerVAN(ByVal Montant As Double, ByRef Tableau(), ByVal TailleTab As Integer) As
Double
Dim indice As Integer
Dim cumul As Double
cumul = -Montant
Do While indice <= TailleTab
cumul = cumul + Tableau(indice)
indice = indice + 1
Loop
CalculerVAN = cumul
End Function

Private Sub AjouterTextBox(ByRef SrceControle As Control, ByVal NomTextBox As String, ByVal width As
Integer, ByVal height As Integer, ByVal size As Integer, ByVal top As Integer, ByVal left As Integer,
ByVal locked As Boolean)
Dim objetTextBox As Object
Set objetTextBox = [Link]("[Link].1")
With objetTextBox
.Name = NomTextBox 'Définit le nom du Text_Box
.top = top 'Définit l'écart entre deux Text_Box dans le sens de la hauteur
.left = left 'Définit la distance entre le bord gauche de la fenêtre et le bord du conteneur
.width = width 'Définit la largeur de l'objet
.height = height 'Définit la hauteur de l'objet
.[Link] = size 'Définit la taille de la police des valeurs saisies
.Visible = True
.locked = locked
End With
End Sub

Private Sub AjouterLabel(ByRef SrceControle As Control, ByVal NomLabel As String, ByVal width As
Integer, ByVal height As Integer, ByVal size As Integer, ByVal top As Integer, ByVal left As Integer)
Dim objetLabel As Object
Set objetLabel = [Link]("[Link].1", True)
With objetLabel
.Caption = NomLabel
.top = top
.left = left
.width = width
.height = height
.[Link] = size
.Visible = True
.ForeColor = RGB(200, 255, 200)
End With

End Sub

Private Sub SupprimerControle(ByRef SrceControle As Control, ByVal NomControle As String)

If RechercherControle(SrceControle, NomControle) = True Then
Form_Investissement.[Link] NomControle
End If
End Sub

Private Function RechercherControle(ByRef SrceControle As Control, ByVal NomControle As String) As

Boolean
For Each SrceControle In Form_Investissement.Controls
If [Link] = NomControle Then
RechercherControle = True
Exit Function
End If
Next SrceControle
RechercherControle = False
End Function
© Groupe Eyrolles

Private Function NombreVariable() As Integer

NombreVariable = CB_DureeInvest.Value
End Function

LA GESTION SOUS EXCEL ET VBA 6/7 SOLUTIONS CHAPITRE 16

 CHAPITRE 16 – LA RENTABILITÉ DES INVESTISSEMENTS

Private Function GetValeurTextBox(ByVal SrceControle As Control, ByVal NomTextBox As String) As Double

If RechercherControle(SrceControle, NomTextBox) = True Then
If [Link] = "" Then
GetValeurTextBox = 0
Exit Function
Else
GetValeurTextBox = [Link]
Exit Function
End If
End If
End Function

Private Sub SetValeurTextBox(ByVal SrceControle As Control, ByVal NomTextBox As String, ByVal Valeur As
Double)
If RechercherControle(SrceControle, NomTextBox) = True Then
Controls(NomTextBox) = Valeur
End If
End Sub

Private Sub AfficherDansTextBox(ByVal OuAfficher As Object, ByVal QuoiAfficher As Variant)

[Link] = QuoiAfficher
End Sub

Module 1
Sub MONT_INVEST()
Form_Investissement.Show
End Sub

Public Function GetAnnee() As Integer

GetAnnee = Year(Date)
End Function

Public Function TailleTableau(ByRef Tableau()) As Integer

Dim taille, indice As Integer
Do While Tableau(indice) <> ""
taille = taille + 1
indice = indice + 1
Loop
TailleTableau = taille - 1
End Function
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 7/7 SOLUTIONS CHAPITRE 16

 CHAPITRE 17 – STATISTIQUE DESCRIPTIVE

Exercice 1 : cas d’une variable continue ••

ANNEXE 1

Maquette

∑ ni xi
Moyenne = -----------------
9 450
x = ------------- = 94,50
100
∑ ni
2

Variance =
∑ ni xi
------------------ – x
2 464 475 2
V ( x ) = ------------------ – 94,50 = 4 644,75
100
∑ ni
Écart type = V(x) σ = 4 644,75 = 68,15

Tableau des formules

Cellule Formule
C6 =D5 recopie vers le bas jusqu’à D8
G5 =(C5+D5)/2 recopie vers le bas jusqu’à G9
H5 =F5*G5 recopie vers le bas jusqu’à H9
I5 =G5-$I$15 recopie vers le bas jusqu’à I9
J5 =I5^2 recopie vers le bas jusqu’à J9
K5 =F5*J5 recopie vers le bas jusqu’à K9
F10 =SOMME(F5:F9) recopie vers le bas jusqu’à K10
I15 =H10/F10
I17 =K10/F10
© Groupe Eyrolles

I19 =RACINE(I17)

LA GESTION SOUS EXCEL ET VBA 1/12 SOLUTIONS CHAPITRE 17

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Exercice 2 : cas d’une variable continue sur tableur •••

ANNEXE 1
Maquette

La classe modale se trouve dans la classe [1000,1200[ car 220 est le plus grand effectif de cette série statistique. La médiane se
situe dans la classe [1200,1400[.
Me = 1 200 + [(1 400 – 1 200) × (50 – 44) / (70 – 44)] = 1 246,15. La médiane est de 1 246 €.

∑ ni xi
Moyenne = -----------------
698 500
x = ------------------ = 1 397
500
∑ ni
La moyenne est de 1 397 €.
2

Variance =
∑ ni xi
------------------ – x
2 1 063 525 000 2
V ( x ) = --------------------------------- – 1 397 = 175 441
500
∑ ni
Écart type = V(x) σ = 175 441 = 418,86
L’étendue est de 3 000 – 1 000 = 2 000.

Tableau des formules

Cellule Formule
A4 =CONCATENER("[";B4;" ; ";C4;"[") recopie vers le bas jusqu’à A9.
D10 =SOMME(D4:D9)
E4 =(B4+C4)/2 recopie vers le bas jusqu’à E9.
F4 =D4*E4 recopie vers le bas jusqu’à F9.
F10 =SOMME(F4:F9)
G4 =D4/$D$10 recopie vers le bas jusqu’à G9.
G10 =SOMME(G4:G9).
H4 =G4 recopie vers le bas jusqu’à H9.
I4 =D4*E4^2 recopie vers le bas jusqu’à I9.
I10 =SOMME(I4:I9)
© Groupe Eyrolles

B13 =MAX(D4:D9)
B14 =G17+(G18-G17)/(G16-G15)*(H9/2-G15)
B15 =F10/D10
.../...

LA GESTION SOUS EXCEL ET VBA 2/12 SOLUTIONS CHAPITRE 17

 CHAPITRE 17 – STATISTIQUE DESCRIPTIVE

Cellule Formule
B16 =(I10/D10)-B15^2
B17 =RACINE(B16)
B18 =C9-B4
G14 =EQUIV(H9/2;H4:H9;H4:H9)
G15 =INDEX(H4:H9;G14)
G16 =INDEX(H4:H9;G14+1)
G17 =INDEX(C4:C9;G14)
G18 =INDEX(C4:C9;G14+1)

Exercice 3 : cas d’une variable continue sur tableur et VBA •••

1. Créez la maquette sur tableur conforme à l’annexe 1.

ANNEXE 1

Maquette

La classe modale se trouve dans l’intervalle [600,700[ car 200 est le plus grand effectif de cette série.
Moyenne : 506 000/800 = 632,5
Variance : V(x) = 341 600 000 – 632,5²/800 = 26 943,75
L’écart type : σ (x) = 164,15
L’étendue est de 1 000 – 300 = 700
2. Concevez une fonction personnalisée en VBA pour chaque indicateur statistique : le mode, la moyenne, la variance,
l’écart type et l’étendue.

Code VBA dans un module de VBE

Mode
Function Max(ValeurNi As Variant) As Variant
Max = [Link](ValeurNi)
© Groupe Eyrolles

End Function

LA GESTION SOUS EXCEL ET VBA 3/12 SOLUTIONS CHAPITRE 17

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Moyenne
Function MOYPOND(Centre_Classe As Variant, Effectifs As Variant) As Variant
s1 = [Link](Centre_Classe, Effectifs)
s2 = [Link](Effectifs)
MOYPOND = s1 / s2
End Function
Variance
Function VARPOND(Serie_xi As Variant, Serie_ni As Variant) As Double
With WorksheetFunction
s1 = [Link](Serie_xi, Serie_ni)
s2 = [Link](Serie_ni)
M = s1 / s2
Var = .SumProduct(Serie_xi, Serie_xi, Serie_ni) / .SumIf(Serie_xi, "<>", Serie_ni)
VARPOND = Var - M ^ 2
End With
End Function

Écart type
Function EcartType(Serie_xi As Variant, Serie_ni As Variant) As Variant
With WorksheetFunction
s1 = [Link](Serie_xi, Serie_ni)
s2 = [Link](Serie_ni)
Moy = s1 / s2
Var = .SumProduct(Serie_xi, Serie_xi, Serie_ni) / .SumIf(Serie_xi, "<>", Serie_ni)
VarP = Var - Moy ^ 2
'Affiche le résultat de l'écart-type
EcartType = Sqr(VarP)
End With
End Function

Étendue
Function Etendue(Classe As Variant) As Variant
Etendue = [Link](Classe) - [Link](Classe)
End Function
3. Établissez un tableau des formules.

Cellule Formule
G4 =(C4+D4)/2 Recopie vers le bas jusqu’à G10
H4 =F4*G4 Recopie vers le bas jusqu’à H10
I4 =F4/$F$11 Recopie vers le bas jusqu’à I10
J4 =I4
J5 =J4+I5 Recopie vers le bas jusqu’à J10
K4 =F4*G4^2 Recopie vers le bas jusqu’à K10
F11 =SOMME(F4:F10)
H11 =SOMME(H4:H10)
K11 =SOMME(K4:K10)
F14 =MAX(F4:F10)
F16 =MOYPOND(G4:G10;F4:F10)
F18 =VARPOND(G4:G10;F4:F10)
F20 =EcartType(G4:G10;F4:F10)
F22 =Etendue(C4:D10)
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 4/12 SOLUTIONS CHAPITRE 17

 CHAPITRE 17 – STATISTIQUE DESCRIPTIVE

Exercice 4 : cas d’une variable continue et VBA •••

1. Créez la maquette sur tableur conforme à l’annexe 1.

2. Calculez le mode, la médiane, la moyenne, la variance, l’écart type et l’étendue.

La classe modale se trouve dans la classe [2000,2500[car 24 est le plus grand effectif de cette série statistique. La médiane
se situe dans la classe [2000,2500[
Me = 2000 + [(2 500 – 2 000) × (50 – 38) / (62 – 38)] = 2 250. La médiane est de 2 250 €.
La moyenne est de 2 220 €.

∑ ni xi
Moyenne = -----------------
222 000
x = ------------------ = 2 220
100
∑ ni
2

Variance =
∑ ni xi
------------------ – x
2 555 250 000 2
V ( x ) = ----------------------------- – 2 220 = 624 100
100
∑ ni
Écart type = V(x) σ = 624 100 = 790

L’étendue est de 4 000 – 500 = 3 500

3. Concevez un programme en langage VBA pour chaque indicateur statistique : le mode, la médiane, la moyenne, la
variance, l’écart type et l’étendue.

Code VBA
Sub Clic()
'Déclaration des variables
total
centreclasse
nixi
frequence
nixi²
classemodale
mediane
moyenne
© Groupe Eyrolles

variance
ecarttype
etendue
End Sub

LA GESTION SOUS EXCEL ET VBA 5/12 SOLUTIONS CHAPITRE 17

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Sub centreclasse()
For i = 3 To 9
Range("G" & i).Value = (Range("C" & i) + Range("D" & i)) / 2
Next i
End Sub

Sub nixi()
Dim count As Long
For i = 3 To 9
Range("H" & i).Value = Range("G" & i) * Range("F" & i)
Next i
count = 0
For i = 3 To 9
count = count + Range("H" & i).Value
Next i
Range("H10").Value = count
End Sub

Sub total()
Dim count As Integer
count = 0
For i = 3 To 9
count = count + Range("F" & i).Value
Next i
Range("F10").Value = count
End Sub

Sub frequence()
Dim count As Long
For i = 3 To 9
Range("I" & i).Value = Round(Round(Range("F" & i) * 10000 / Range("F10"), 2) / 100, 4)
Next i
count = 0
For i = 3 To 9
count = count + Range("I" & i).Value * 100
Range("J" & i).Value = count / 100
Next i
End Sub

Sub nixi²()
Dim count As Long
For i = 3 To 9
Range("K" & i).Value = Range("G" & i) * Range("G" & i) * Range("F" & i)
Next i
count = 0
For i = 3 To 9
count = count + Range("K" & i).Value
Next i
Range("K10").Value = count
End Sub

Sub classemodale()
Dim count As Integer
count = 0
For i = 3 To 9
If count < Range("F" & i).Value Then
count = Range("F" & i).Value
End If
Next i
Range("F13").Value = count
End Sub

Sub mediane()
Dim count As Integer
Dim i As Integer
© Groupe Eyrolles

i = 3
count = 0
Do While count <= (Range("F10").Value / 2)
count = count + Range("F" & i).Value
i = i + 1

LA GESTION SOUS EXCEL ET VBA 6/12 SOLUTIONS CHAPITRE 17

 CHAPITRE 17 – STATISTIQUE DESCRIPTIVE

Loop
Range("F15").Value = Range("G" & i - 1).Value
End Sub
Sub moyenne()
Range("F17").Value = Round(Range("H10") / Range("F10"), 2)
End Sub

Sub variance()
Dim count As Long
count = 0
For i = 3 To 9
count = count + Range("F" & i) * ((Range("G" & i) - Range("F17")) * (Range("G" & i) -
Range("F17")))
Next i
count = count / Range("F10").Value
Range("F19").Value = count
End Sub

Sub ecarttype()
Range("F21").Value = Round(Sqr(Range("F19").Value), 2)
End Sub
Sub etendue()
Range("F23").Value = Range("D9").Value - Range("C3").Value
End Sub

Exercice 5 : statistique descriptive sous Excel VBA – cas d’une variable continue ••••
1. Complétez le tableau statistique en annexe 1 et calculer le mode, la médiane, la moyenne, la variance, l’écart type et
l’étendue.

Centre des
Nombre de salariés Fréquences cumulées
Années d'ancienneté xi classes [Link] Fréquences en % [Link]²
ni en %
xi
[ 0 3 [ 30 1,5 45 30,00% 30,00% 68
[ 3 6 [ 28 4,5 126 28,00% 58,00% 567
[ 6 9 [ 12 7,5 90 12,00% 70,00% 675
[ 9 12 [ 8 10,5 84 8,00% 78,00% 882
[ 12 15 [ 7 13,5 95 7,00% 85,00% 1 276
[ 15 18 [ 6 16,5 99 6,00% 91,00% 1 634
[ 18 21 [ 5 19,5 98 5,00% 96,00% 1 901
[ 21 24 [ 4 22,5 90 4,00% 100,00% 2 025
Total 100 726 9 027

La classe modale se trouve dans la classe [0,3[ car 30 est le plus grand effectif de cette série statistique. La médiane se situe
dans la classe [3,6[.
Me = 3 + [(6 – 3) × (50 – 30) / (58 – 30)] = 5,14. La médiane est de 5,14 années.

∑ ni xi
Moyenne = -----------------
726
x = -------- = 7,26
100
∑ ni
La moyenne est de 7,26 années d’ancienneté.
2

Variance =
∑ ni xi
------------------ – x
2 9 027 2
V ( x ) = ------------- – 7,26 = 37,56
100
∑ ni
© Groupe Eyrolles

Écart type = V(x) σ = 37,56 = 6,13

L’étendue est de 24 – 0 = 24.
2. Réalisez la boîte de dialogue personnalisée conforme à celle proposée en annexe 2. Voir annexe 3, la boîte de dialogue
personnalisée en mode création.

LA GESTION SOUS EXCEL ET VBA 7/12 SOLUTIONS CHAPITRE 17

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Testez votre modèle à l’aide de l’exemple ci-dessus.

3. Proposez un programme en VBA pour rendre fonctionnel votre interface utilisateur.

Private Sub UserForm_Initialize()
Dim NbSerie As Single
For NbSerie = 4 To 12 Step 1
CB_NbreSerie.AddItem (NbSerie)
CB_NbreSerie.ListIndex = 0
Next NbSerie
End Sub

Private Sub CB_NbreSerie_Change()

'Déclaration des variables de travail
Const width As Integer = 46
Const height As Integer = 18
Const Size As Integer = 10
Const ecartLeft As Integer = 55
Const ecartTop As Integer = 24
Const nbColonne As Integer = 6
Const nbMaxLigne As Integer = 12
Dim top, left, nbLigne As Integer
Dim indiceNom, nbIteration1, nbIteration2, nbTextBox As Integer
Dim locked As Boolean
'Effacement des données précédentes avant la création de nouveaux Text_Box
indiceNom = 1
nbIteration1 = 1
nbTextBox = (nbMaxLigne * nbColonne) + NombreVariable()
Do While nbIteration1 <= nbTextBox
Call SupprimerControle("TB_Variable" & indiceNom)
nbIteration1 = nbIteration1 + 1
indiceNom = indiceNom + 1
Loop
'Création des objets Text_Box
locked = False
indiceNom = 1
left = ecartLeft
top = 30
nbIteration1 = 1
© Groupe Eyrolles

nbIteration2 = 1
nbLigne = NombreVariable()
Do While nbIteration1 <= nbColonne
Do While nbIteration2 <= nbLigne
top = top + ecartTop

LA GESTION SOUS EXCEL ET VBA 8/12 SOLUTIONS CHAPITRE 17

 CHAPITRE 17 – STATISTIQUE DESCRIPTIVE

Call AjouterTextBox("TB_Variable" & indiceNom, width, height, Size, top, left, nbTextBox,
locked)
indiceNom = indiceNom + 1
nbIteration2 = nbIteration2 + 1
If nbIteration2 > nbLigne Then
top = 30
left = left + ecartLeft
End If
Loop
nbIteration2 = 1
nbIteration1 = nbIteration1 + 1
Select Case nbIteration1
Case 1 To 2
locked = False
Case 3 To 6
locked = True
End Select
Loop
End Sub

Private Sub AjouterTextBox(ByVal NomControle As String, ByVal width As Integer, ByVal height As Integer,
ByVal Size As Integer, ByVal top As Integer, ByVal left As Integer, ByVal nbTextBox As Integer, ByVal
locked As Boolean)
Dim objetTextBox As Control
Set objetTextBox = Fm_Statistique.[Link]("[Link].1")
With objetTextBox
.name = NomControle 'Définit le nom du Text_Box
.top = top 'Définit l'écart entre deux Text_Box dans le sens de la hauteur
.left = left 'Définit la distance entre le bord gauche de la fenêtre et le bord du conteneur
.width = width 'Définit la largeur de l'objet
.height = height 'Définit la hauteur de l'objet
.[Link] = Size 'Définit la taille de la police des valeurs saisies
.Visible = True
.locked = locked
End With
End Sub

Private Sub SupprimerControle(ByVal NomControle As String)

For Each Control In Fm_Statistique.Controls
If [Link] = NomControle Then
Fm_Statistique.[Link] NomControle
Exit Sub
End If
Next Control
End Sub

Private Sub CB_Calculer_Click()

Dim tabValeurX(16), tabValeurN(16), tabValeurXXN(16), tabValeurXN(16), tabBorneInf(16),
tabBorneSup(16) As Double
Dim nomTextBox, bInfAlph, bSupAlph, tabInterval(16) As String
Dim valeurTextBoxX, valeurTextboxN, valeurTextboxXN, bInfNum, bSupNum, moyenne, crMediane As Double
Dim sommeN, sommeNX, moyenneNX, sommeNXX, variance, ecartType, valeurTextboxXXN, cumul, BInf, BSup As
Double
Dim indice, i, j, nbVariable, taille As Integer
nbVariable = NombreVariable()

'Récupération des classes et stockage dans un tableau

i = 0
For indice = 1 To nbVariable
tabInterval(i) = GetValeurTextBox("TB_Variable" & indice)
i = i + 1
Next indice
'Récupération des valeurs Ni et stockage dans un tableau et calcul de la somme des Ni
i = 0
For indice = indice To nbVariable * 2
nomTextBox = "TB_Variable" & indice
valeurTextboxN = GetValeurTextBox(nomTextBox)
tabValeurN(i) = valeurTextboxN
sommeN = sommeN + valeurTextboxN
© Groupe Eyrolles

i = i + 1
Next indice
'Calcul du centre de classe
i = 0
For indice = indice To nbVariable * 3

LA GESTION SOUS EXCEL ET VBA 9/12 SOLUTIONS CHAPITRE 17

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

j = PositionCaractere(tabInterval(i), "-")
If j = 0 Then
Exit Sub
Else
bInfAlph = ExtraireGauche(tabInterval(i), j - 1)
taille = Len(tabInterval(i))
bSupAlph = ExtraireDroit(tabInterval(i), taille - j)
bInfNum = ConvertirDouble(bInfAlph)
tabBorneInf(i) = bInfNum
bSupNum = ConvertirDouble(bSupAlph)
tabBorneSup(i) = bSupNum
moyenne = Format((bInfNum + bSupNum) / 2, "### ##0.00")
tabValeurX(i) = moyenne
Call AfficherResultat("TB_Variable" & indice, moyenne)
End If
i = i + 1
Next indice
'X*Ni
i = 0
For indice = indice To nbVariable * 4
valeurTextboxXN = tabValeurX(i) * tabValeurN(i)
tabValeurXN(i) = valeurTextboxXN
Call AfficherResultat("TB_Variable" & indice, valeurTextboxXN)
sommeNX = sommeNX + valeurTextboxXN
i = i + 1
Next indice
'X^2*Ni
i = 0
For indice = indice To nbVariable * 5
valeurTextboxXXN = tabValeurX(i) ^ 2 * tabValeurN(i)
tabValeurXXN(i) = valeurTextboxXXN
Call AfficherResultat("TB_Variable" & indice, valeurTextboxXXN)
sommeNXX = sommeNXX + tabValeurXXN(i)
i = i + 1
Next indice
'Affichage du cumul de Ni
i = 0
sommeN = 0
For indice = indice To nbVariable * 6
sommeN = sommeN + tabValeurN(i)
nomTextBox = "TB_Variable" & indice
Call AfficherResultat("TB_Variable" & indice, sommeN)
i = i + 1
Next indice
'Affichage de la somme de Ni
Call AfficherResultat("TB_TotalNi", sommeN)

'Affichage de la somme de NiX

Call AfficherResultat("TB_TotalNiX", sommeNX)

'Affichage de la somme NiX^2

Call AfficherResultat("TB_NiXX", sommeNXX)

'Calcul et affichage de la moyenne de NiX

moyenneNX = Format(sommeNX / sommeN, "### ##0.00")
Call AfficherResultat("TB_MoyenneXN", moyenneNX)

'Calcul et affichage de la variance

variance = Format((sommeNXX / sommeN) - moyenneNX ^ 2, "### ##0.00")
Call AfficherResultat("TB_Variance", variance)

'Calcul et affichage de l'écart type

ecartType = Format(Sqr(variance), "### ##0.00")
Call AfficherResultat("TB_EcartType", ecartType)

'Calcul et affichage de la médiane

i = 0
cumul = 0
While cumul < sommeN / 2
cumul = cumul + tabValeurN(i)
© Groupe Eyrolles

i = i + 1
Wend
BInf = tabBorneInf(i - 1)
BSup = tabBorneSup(i - 1)
cumulInf = cumul - tabValeurN(i - 1)

LA GESTION SOUS EXCEL ET VBA 10/12 SOLUTIONS CHAPITRE 17

 CHAPITRE 17 – STATISTIQUE DESCRIPTIVE

crMediane = Format(Mediane(BInf, BSup, cumulInf, cumul, sommeN / 2), "### ##0.00")

Call AfficherResultat("TBMediane", crMediane)
End Sub

Private Function GetValeurTextBox(ByVal nomTextBox As String) As Variant

For Each Control In Fm_Statistique.Controls
If [Link] = nomTextBox Then
GetValeurTextBox = [Link]
Exit Function
End If
Next Control
End Function

Private Sub AfficherResultat(ByVal nomTextBox As String, ByVal ValeurTextBox As Double)

Const dimTab As Integer = 165
Dim i As Integer
Dim tableau(dimTab) As String
For Each Control In Fm_Statistique.Controls
If [Link] = nomTextBox Then
tableau(i) = [Link]
Controls(tableau(i)).Value = ValeurTextBox
Exit Sub
End If
i = i + 1
Next Control
End Sub

Private Function NombreVariable() As Integer

NombreVariable = CB_NbreSerie.Value
End Function

Private Function GetNbreControl() As Double

Dim i As Integer
i = 1
For Each Control In [Link]
i = i + 1
Next Control
GetNbreControl = i
End Function

Private Function PositionCaractere(ByVal Chaine As String, ByVal Caractere As String) As Integer

Dim PosCaractere As Integer
PosCaractere = InStr(Chaine, Caractere)
If PosCaractere = 0 Then
PositionCaractere = 0
Else
PositionCaractere = PosCaractere
End If
End Function
Private Function ExtraireGauche(ByVal Chaine As String, ByVal NbCaractere As Integer) As String
ExtraireGauche = left(Chaine, NbCaractere)
End Function

Private Function ExtraireDroit(ByVal Chaine As String, ByVal NbCaractere As Integer) As String

ExtraireDroit = Right(Chaine, NbCaractere)
End Function

Private Function ConvertirDouble(ByVal Chaine As String) As Double

ConvertirDouble = CDbl(Chaine)
End Function

Private Function Mediane(ByVal BInf As Double, ByVal BSup As Double, ByVal FInf As Double, ByVal FSup As
Double, ByVal moyenne As Double)
Mediane = (moyenne * (BSup - BInf) + FInf * (BInf - BSup) + BInf * (FSup - FInf)) / (FSup - FInf)
End Function
© Groupe Eyrolles

Private Sub CB_Quitter_Click()

End
End Sub

LA GESTION SOUS EXCEL ET VBA 11/12 SOLUTIONS CHAPITRE 17

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Private Sub CB_Statistique_Click()

Fm_Statistique.Show
End Sub

4. Complétez le tableau des contrôles en annexe 4.

Le tableau de synthèse des contrôles de l’interface graphique utilisateur

Propriété
Contrôle Outils Propriété Caption
Name

Statistique descriptive à
Formulaire (UserForm) Fm_Statistique
une variable

Zone de liste modifiable (Combobox) CB_NbreSerie

Zone de texte (TextBox) TB_TotalNi

Zone de texte (TextBox) TB_TotalNiX

Zone de texte (TextBox) TB_NiXX

Zone de texte (TextBox) TBMediane

Zone de texte (TextBox) TB_MoyenneXN

Zone de texte (TextBox) TB_Variance

Zone de texte (TextBox) TB_EcartType

Boutons de commande (CommandButton) CB_Calculer Calculer

Boutons de commande (CommandButton) CB_Quitter Quitter

Remarque : les zones de texte du tableau doivent apparaître dynamiquement à l’aide

de code VBA.
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 12/12 SOLUTIONS CHAPITRE 17

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS
ET LA CORRÉLATION LINÉAIRE

Exercice 1 : méthode des moindres carrés et UserForm ••••

Tableau des contrôles de la boîte de dialogue

Propriété
Contrôle Outils Propriété Caption
Name

Formulaire (UserForm) Fm_MoindresCarres Statistique à deux variables

Zone de liste modifiable (ComboBox) CB_NbreSerie

Zone de texte (TextBox) RangPrev1

Zone de texte (TextBox) tbPrevBrute1

Bouton de commande (CommandButton) CB_Calculer Calculer

Bouton de commande (CommandButton) Cmd_Quitter Quitter

Remarque : les autres contrôles apparaissent dynamiquement à l’aide d’un programme

en langage VBA.

Code VBA
Private Sub UserForm_Initialize()
Dim NbSerie As Single
For NbSerie = 4 To 16 Step 1
CB_NbreSerie.AddItem (NbSerie)
CB_NbreSerie.ListIndex = 0
Next NbSerie
End Sub

Private Sub CB_NbreSerie_Change()

'Déclaration des variables de travail
Const width As Integer = 60
Const height As Integer = 20
Const Size As Integer = 10
Const ecartLeft As Integer = 55
Const ecartTop As Integer = 24
Const nbColonne As Integer = 6
Const nbMaxLigne As Integer = 16
Dim top, left, nbLigne, cumulTop, nbVariable As Integer
Dim locked As Boolean
Dim indiceNom, nbIteration, nbIteration1, nbIteration2, nbTextBox As Integer
nbTextBox = (nbMaxLigne * nbColonne) + NombreVariable() + 3
Do While nbIteration <= nbTextBox
Call SupprimerControle("TB_Variable" & indiceNom)
nbIteration = nbIteration + 1
indiceNom = indiceNom + 1
Loop
© Groupe Eyrolles

'Création des objets Text_Box des variables X et Y

locked = False
indiceNom = 1
left = ecartLeft
top = 60

LA GESTION SOUS EXCEL ET VBA 1/13 SOLUTIONS CHAPITRE 18

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

nbIteration1 = 1
nbIteration2 = 1
nbLigne = NombreVariable()
Do While nbIteration1 <= nbColonne
Do While nbIteration2 <= nbLigne
top = top + ecartTop
Call AjouterTextBox("TB_Variable" & indiceNom, width, height, Size, top, left, nbTextBox,
locked)
indiceNom = indiceNom + 1
nbIteration2 = nbIteration2 + 1
If nbIteration2 > nbLigne Then
top = 60
left = left + ecartLeft
End If
Loop
nbIteration2 = 1
nbIteration1 = nbIteration1 + 1
If nbIteration1 > 2 Then
locked = True
End If
Loop

'Création des objets Text_Box des Totaux X et Y

nbIteration1 = 1
left = ecartLeft
locked = True
If NombreVariable() = 4 Then
top = 185 ' l'écart depuis le haut
Else
If NombreVariable() = 8 Then
top = 285
Else
If NombreVariable() = 12 Then
top = 385
Else
top = 480
End If
End If
End If

Do While nbIteration1 <= nbColonne

Call AjouterTextBox("TB_Variable" & indiceNom, width, height, Size, top, left, nbTextBox, locked)
left = left + ecartLeft
indiceNom = indiceNom + 1
nbIteration1 = nbIteration1 + 1
Loop

'Remplissage de la première colonne de Text_Box par pas de 1

nbIteration1 = NombreVariable()
indiceNom = 0
Do While nbIteration1 > 0
Call SetValeurTextBox("TB_Variable" & (indiceNom + 1), indiceNom + 1)
nbIteration1 = nbIteration1 - 1
indiceNom = indiceNom + 1
Loop

End Sub

Private Sub AjouterTextBox(ByVal nomControle As String, ByVal width As Integer, ByVal height As Integer,
ByVal Size As Integer, ByVal top As Integer, ByVal left As Integer, ByVal nbTextBox As Integer, ByVal
locked As Boolean)

Dim objetTextBox As Control

Set objetTextBox = Fm_MoindresCarres.[Link]("[Link].1")

With objetTextBox
.name = nomControle 'Définit le nom du Text_Box
.top = top 'Définit l'écart entre deux Text_Box dans le sens de la hauteur
© Groupe Eyrolles

.left = left 'Definit la distance entre le bord gauche de la fenetre et le bord du conteneur
.width = width 'Définit la largeur de l'objet
.height = height 'Définit la hauteur de l'objet
.[Link] = Size 'Définit la taille de la police des valeurs saisies
.Visible = True

LA GESTION SOUS EXCEL ET VBA 2/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS ET LA CORRÉLATION LINÉAIRE

.locked = locked
End With
End Sub

Private Sub SupprimerControle(ByVal nomControle As String)

Dim nbIteration As Integer
For Each Control In Fm_MoindresCarres.Controls
If [Link] = nomControle Then
Fm_MoindresCarres.[Link] nomControle
Exit Sub
End If
Next Control
End Sub

Private Sub CB_Calculer_Click()

Const nbColonne As Integer = 6

Dim tabValeurX(16), cumulX, moyenneX, indiceX As Double
Dim tabValeurY(16), tabValeur(16), cumulY, moyenneY, indiceY As Double
Dim cumulXX, cumulXY, penteD, rang, y As Double
Dim NomTextBox, nomLabel As String
Dim rProduit, rDifference, ValeurTextBoxX, ValeurTextboxY As Double
Dim objetTxtBox As Control
nbVariable = NombreVariable()

'Récupération des valeurs X et stockage dans un tableau

For indice = 1 To nbVariable
NomTextBox = "TB_Variable" & indice
ValeurTextBoxX = GetValeurTextBox(NomTextBox)
tabValeurX(indice - 1) = ValeurTextBoxX
cumulX = cumulX + ValeurTextBoxX
Next indice
moyenneX = cumulX / nbVariable
indice = nbVariable * nbColonne + 1
Call AfficherResultat("TB_Variable" & indice, moyenneX)
'Calcul et affichage du changement de variable xi (X)
For indice = 1 To nbVariable
ValeurTextBoxX = tabValeurX(indice - 1)
rDifference = Difference(ValeurTextBoxX, moyenneX)
tabValeurX(indiceX) = rDifference
Call AfficherResultat("TB_Variable" & (nbVariable * 2 + indice), rDifference)
indiceX = indiceX + 1
Next indice
'Calcul et affichage de X*X
For indice = 1 To nbVariable
rProduit = Produit(tabValeurX(indice - 1), tabValeurX(indice - 1))
Call AfficherResultat("TB_Variable" & (nbVariable * 5 + indice), rProduit)
cumulXX = cumulXX + rProduit
Next indice
indice = nbVariable * nbColonne + 6
Call AfficherResultat("TB_Variable" & indice, cumulXX)

'Récupération des valeurs Y et stockage dans un tableau

For indice = nbVariable + 1 To nbVariable * 2
NomTextBox = "TB_Variable" & indice
ValeurTextboxY = GetValeurTextBox(NomTextBox)
tabValeurY(i) = ValeurTextboxY
tabValeur(i) = ValeurTextboxY
i = i + 1
cumulY = cumulY + ValeurTextboxY
Next indice

moyenneY = cumulY / nbVariable

indice = nbVariable * nbColonne + 2
Call AfficherResultat("TB_Variable" & indice, moyenneY)

'Calcul et affichage du changement de variable yi (Y)

For indice = 1 To nbVariable
ValeurTextboxY = tabValeurY(indice - 1)
© Groupe Eyrolles

rDifference = Difference(ValeurTextboxY, moyenneY)

tabValeurY(indiceY) = rDifference
Call AfficherResultat("TB_Variable" & (nbVariable * 3 + indice), rDifference)
indiceY = indiceY + 1
Next indice

LA GESTION SOUS EXCEL ET VBA 3/13 SOLUTIONS CHAPITRE 18

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

'Affichage de la somme, produit, moyenne des XY

indiceX = 0
indiceY = 0
For indice = 1 To nbVariable
ValeurTextBoxX = tabValeurX(indiceX)
ValeurTextboxY = tabValeurY(indiceY)
rProduit = Produit(ValeurTextBoxX, ValeurTextboxY)
cumulXY = cumulXY + rProduit
Call AfficherResultat("TB_Variable" & (nbVariable * 4 + indice), rProduit)
indiceX = indiceX + 1
indiceY = indiceY + 1
Next indice

indice = nbVariable * nbColonne + 5

Call AfficherResultat("TB_Variable" & indice, cumulXY)

penteD = cumulXY / cumulXX

constanteB = moyenneY - (penteD * moyenneX)

[Link] = nbVariable + 1
[Link] = Format((penteD * [Link]) + constanteB, "###,##0.00")

'Afficher la droite Y
penteD = Format(penteD, "###,##0.00")
constanteB = Format(constanteB, "###,##0.00")
If constanteB >= 0 Then
nomLabel = "Y=" & penteD & "x+" & constanteB
Call AjouterLabel(nomLabel, 150, 15, 12, 20, 45)
Else
nomLabel = "Y=" & penteD & "x-" & constanteB
Call AjouterLabel(nomLabel, 150, 40, 12, 20, 45)
End If
End Sub

Private Function GetValeurTextBox(ByVal NomTextBox As String) As Double

Const dimTab As Integer = 165

Dim i As Integer
Dim valeur As Double
Dim tableau(dimTab) As String

For Each Control In Fm_MoindresCarres.Controls

If [Link] = NomTextBox Then
tableau(i) = [Link]
tableau(i) = [Link]
valeur = tableau(i)
GetValeurTextBox = valeur
Exit Function
End If
i = i + 1
Next Control

End Function

Private Sub AjouterLabel(ByVal nomLabel As String, ByVal width As Integer, ByVal height As Integer,
ByVal Size As Integer, ByVal top As Integer, ByVal left As Integer)

Dim objetLabel As Control

Set objetLabel = [Link]("[Link].1", True)

With objetLabel
.Caption = nomLabel
.top = top
.left = left
.width = width
.height = height
.[Link] = Size
.Visible = True
© Groupe Eyrolles

.ForeColor = RGB(250, 0, 0)
End With

End Sub

LA GESTION SOUS EXCEL ET VBA 4/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS ET LA CORRÉLATION LINÉAIRE

Private Sub AfficherResultat(ByVal NomTextBox As String, ByVal ValeurTextBox As Double)

Const dimTab As Integer = 165
Dim i As Integer
Dim tableau(dimTab) As String

For Each Control In Fm_MoindresCarres.Controls

If [Link] = NomTextBox Then
tableau(i) = [Link]
Controls(tableau(i)).Value = ValeurTextBox
Exit Sub
End If
i = i + 1
Next Control

End Sub
Private Function NombreVariable() As Integer

NombreVariable = CB_NbreSerie.Value

End Function

Private Function Difference(ByVal ValeurTextBox1 As Double, ByVal ValeurTextBox2 As Double) As Double

Difference = ValeurTextBox1 - ValeurTextBox2

End Function

Private Function Produit(ByVal ValeurTextBoxX As Double, ByVal ValeurTextboxY As Double) As Double

Produit = ValeurTextBoxX * ValeurTextboxY

End Function

Private Function GetNbreControl() As Double

Dim i As Integer
i = 1

For Each Control In [Link]

i = i + 1
Next Control

GetNbreControl = i

End Function

Private Sub SetValeurTextBox(ByVal NomTextBox As String, ByVal valeur As Double)

Controls(NomTextBox) = valeur

End Sub

Private Sub Cmd_Quitter_Click()

End
End Sub

MODULE 1
Private Sub CB_Coefficient_Saisonnier_Click()
© Groupe Eyrolles

Fm_MoindresCarres.Show

End Sub
Testez votre modèle à l’aide de l’exemple ci-dessous.

LA GESTION SOUS EXCEL ET VBA 5/13 SOLUTIONS CHAPITRE 18

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

ANNEXE 4

Évolution des ventes en dizaine de milliers d’euros au cours des douze dernières années

Années 1 2 3 4 5 6 7 8 9 10 11 12
Chiffre d’affaires 84 123 165 108 103 137 200 124 100 167 196 140
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 6/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS ET LA CORRÉLATION LINÉAIRE

Exercice 2 : coefficient de corrélation et UserForm ••••

Code VBA
Private Sub BC_Calculer_Click()
'Déclaration des variables
Dim tabValeurX(16), tabValeurY(16) As Double
Dim NomTextBox, nomLabel As String
Dim rMoyenneX, rMoyenneY, rProduit, rCarre, ValeurTextBoxX, ValeurTextboxY As Double
Dim sommeXX, sommeYY, sommeXY, rVarianceX, rVarianceY, rSommeX, rSommeY, rCoefficientXY,
rCovarianceXY As Double
Dim indice, nbVariable, indiceAffich As Integer
Dim rDeterminerCoeffDroite, rDeterminerDroite As Double
Dim objetTxtBox As Control

nbVariable = NombreVariable()

'Récupération des valeurs X et stockage dans un tableau

For indice = 1 To nbVariable
NomTextBox = "TB_Variable" & indice
ValeurTextBoxX = GetValeurTextBox(NomTextBox)
tabValeurX(indice - 1) = ValeurTextBoxX
Next indice

'Affichage de la somme, carré, moyenne des X

rSommeX = Somme(tabValeurX)
indiceAffich = (nbVariable * 5) + 1
Call AfficherResultat("TB_Variable" & indiceAffich, rSommeX)
For indice = 1 To nbVariable
ValeurTextBoxX = tabValeurX(indice - 1)
rCarre = Carre(ValeurTextBoxX)
sommeXX = sommeXX + rCarre
Call AfficherResultat("TB_Variable" & (nbVariable * 2 + indice), rCarre)
Next indice
rMoyenneX = Format(moyenne(rSommeX), "### ### ##0.00")
Call AfficherResultat("TB_MoyenneX", rMoyenneX)
indiceAffich = (nbVariable * 5) + 3
Call AfficherResultat("TB_Variable" & indiceAffich, sommeXX)

'Récupération des valeurs Y et stockage dans un tableau

For indice = nbVariable + 1 To nbVariable * 2
NomTextBox = "TB_Variable" & indice
ValeurTextboxY = GetValeurTextBox(NomTextBox)
tabValeurY(i) = ValeurTextboxY
i = i + 1
Next indice

'Affichage de la somme, carré, moyenne des Y

rSommeY = Somme(tabValeurY)
rMoyenneY = Format(moyenne(rSommeY), "### ### ##0.00")
For indice = 1 To nbVariable
ValeurTextboxY = tabValeurY(indice - 1)
rCarre = Carre(ValeurTextboxY)
sommeYY = sommeYY + rCarre
Call AfficherResultat("TB_Variable" & (nbVariable * 3 + indice), rCarre)
Next indice
indiceAffich = (nbVariable * 5) + 2
Call AfficherResultat("TB_Variable" & indiceAffich, rSommeY)
Call AfficherResultat("TB_MoyenneY", rMoyenneY)
indiceAffich = (nbVariable * 5) + 4
Call AfficherResultat("TB_Variable" & indiceAffich, sommeYY)

'Affichage de la somme, produit, moyenne des XY

For indice = 1 To nbVariable
ValeurTextBoxX = tabValeurX(indice - 1)
ValeurTextboxY = tabValeurY(indice - 1)
rProduit = Produit(ValeurTextBoxX, ValeurTextboxY)
sommeXY = sommeXY + rProduit
© Groupe Eyrolles

Call AfficherResultat("TB_Variable" & (nbVariable * 4 + indice), rProduit)

Next indice
indiceAffich = (nbVariable * 5) + 5
Call AfficherResultat("TB_Variable" & indiceAffich, sommeXY)

LA GESTION SOUS EXCEL ET VBA 7/13 SOLUTIONS CHAPITRE 18

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

'Calcul et affichage des variances X et Y

rVarianceX = Format(Variance(sommeXX, rMoyenneX), "### ### ##0.00")
rVarianceY = Format(Variance(sommeYY, rMoyenneY), "### ### ##0.00")
Call AfficherResultat("TB_VarianceX", rVarianceX)
Call AfficherResultat("TB_VarianceY", rVarianceY)

'Calcul et affichage des écarts-types X et Y

rEcartTypeX = Format(EcartType(rVarianceX), "### ### ##0.00")
rEcartTypeY = Format(EcartType(rVarianceY), "### ### ##0.00")
Call AfficherResultat("TB_EcartTypeX", rEcartTypeX)
Call AfficherResultat("TB_EcartTypeY", rEcartTypeY)

'Calcul de la covariance XY
rCovarianceXY = Format(covariance(sommeXY, rMoyenneX, rMoyenneY), "### ### ##0.000")
Call AfficherResultat("TB_CovarianceXY", rCovarianceXY)

'Calcul du coefficient de corrélation XY

rCoefficientXY = Format(CoefficientCorrelation(rCovarianceXY, rEcartTypeX, rEcartTypeY), "##
##0.00000")
Call AfficherResultat("TB_Correlation", rCoefficientXY)

'Calculer le coefficient directeur de la droite X

rDeterminerCoeffDroite = Format(DeterminerCoeffDroite(rCovarianceXY, rVarianceY), "### ##0.000")
'Déterminer la droite X
rDeterminerDroite = Format(DeterminerDroite("DroiteX", rDeterminerCoeffDroite, rMoyenneX,
rMoyenneY), "### ##0.000")
'Afficher la droite X
If rDeterminerDroite < 0 Then
nomLabel = "X=" & rDeterminerCoeffDroite & "y" & rDeterminerDroite
Else
nomLabel = "X=" & rDeterminerCoeffDroite & "y+" & rDeterminerDroite
End If
Call AjouterLabel(nomLabel, 100, 15, 8, 135, 60) ' taille police 8 pour l'équation

'Calculer le coefficient directeur de la droite Y

rDeterminerCoeffDroite = Format(DeterminerCoeffDroite(rCovarianceXY, rVarianceX), "### ##0.000")
'Determiner la droite Y
rDeterminerDroite = Format(DeterminerDroite("DroiteY", rDeterminerCoeffDroite, rMoyenneX,
rMoyenneY), "### ##0.000")
'Afficher la droite Y
If rDeterminerDroite < 0 Then
nomLabel = "Y=" & rDeterminerCoeffDroite & "x" & rDeterminerDroite
Else
nomLabel = "Y=" & rDeterminerCoeffDroite & "x+" & rDeterminerDroite
End If
Call AjouterLabel(nomLabel, 100, 15, 8, 155, 60)

End Sub

Private Sub AfficherResultat(ByVal NomTextBox As String, ByVal ValeurTextBox As Double)

Const dimTab As Integer = 165

Dim i As Integer
Dim tableau(dimTab) As String

For Each Control In [Link]

If [Link] = NomTextBox Then
tableau(i) = [Link]
Controls(tableau(i)).Value = ValeurTextBox
Exit Sub
End If
© Groupe Eyrolles

i = i + 1
Next Control

End Sub

LA GESTION SOUS EXCEL ET VBA 8/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS ET LA CORRÉLATION LINÉAIRE

Private Function GetValeurTextBox(ByVal NomTextBox As String) As Double

Const dimTab As Integer = 165
Dim i As Integer
Dim valeur As Double
Dim tableau(dimTab) As String

For Each Control In [Link]

If [Link] = NomTextBox Then
tableau(i) = [Link]
tableau(i) = [Link]
valeur = tableau(i)
GetValeurTextBox = valeur
Exit Function
End If
i = i + 1
Next Control

End Function

Private Sub NbreVariable()

'Déclaration des variables de travail

Const Width As Integer = 60
Const Height As Integer = 20
Const Size As Integer = 10
Const ecartObjet As Integer = 60

Dim Top, Left, nbTextBox As Integer

Dim locked As Boolean

'Récupération de la valeur sélectionnée dans le Combo_Box

nbTextBox = NombreVariable()

'Création des objets Text_Box des variables X

Top = 24
Left = ecartObjet
locked = False
Call DessinerText_Box(Width, Height, Size, Top, Left, nbTextBox, locked)

'Création des objets Text_Box des variables Y

Left = Left + ecartObjet
Call DessinerText_Box(Width, Height, Size, Top, Left, nbTextBox, locked)

'Création des objets Text_Box des variables X*X

Top = 24
Left = Left + ecartObjet
locked = True
Call DessinerText_Box(Width, Height, Size, Top, Left, nbTextBox, locked)

'Création des objets Text_Box des variables Y*Y

Left = Left + ecartObjet
Call DessinerText_Box(Width, Height, Size, Top, Left, nbTextBox, locked)

'Création des objets Text_Box des variables X*Y

Left = Left + ecartObjet
Call DessinerText_Box(Width, Height, Size, Top, Left, nbTextBox, locked)

'Affichage des objets Text_Box des totaux

Top = Top * nbTextBox + 24
Left = ecartObjet
nbTextBox = 1
For i = 1 To 5
Left = ecartObjet * i
© Groupe Eyrolles

Call DessinerText_Box(Width, Height, Size, Top, Left, nbTextBox, locked)

Next i

End Sub

LA GESTION SOUS EXCEL ET VBA 9/13 SOLUTIONS CHAPITRE 18

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Private Sub BC_Quitter_Click()

End
End Sub

Private Sub UserForm_Initialize()

Const NbreVarDefaut As Integer = 12 ' changement du nombre de séries maxi 12 mois

TB_NbreSerie.Value = NbreVarDefaut
Call NbreVariable

End Sub

Private Sub DessinerText_Box(ByVal Width As Integer, ByVal Height As Integer, ByVal Size As Integer,
ByVal Top As Integer, ByVal Left As Integer, ByVal nbTextBox As Integer, ByVal locked As Boolean)

Const dimTab As Integer = 165

Static i As Integer

Dim indice As Integer

Dim objetTxtBox As Control
Dim tabNomTextBox(dimTab) As String

For indice = 1 To nbTextBox

Set objetTxtBox = [Link]("[Link].1", "TB_Variable" & i + 1, True)

With objetTxtBox
.Tag = "TB_Variable" & indice 'Définit le nom du Text_Box
.Top = (Top * indice) + 40 'Définit l'écart entre deux Text_Box dans le sens de la hauteur
.Left = Left 'Definit la distance entre le bord gauche de la fenetre et le bord du conteneur
.Width = Width 'Definit la largeur de l'objet
.Height = Height 'Definit la hauteur de l'objet
.[Link] = Size 'Définit la taille de la police des valeurs saisies
.Visible = True
.locked = locked
End With

i = i + 1

Next indice

End Sub

Private Sub AjouterLabel(ByVal nomLabel As String, ByVal Width As Integer, ByVal Height As Integer,
ByVal Size As Integer, ByVal Top As Integer, ByVal Left As Integer)

Dim objetLabel As Control

Set objetLabel = [Link]("[Link].1", True)

With objetLabel
.Caption = nomLabel
.Top = Top
.Left = Left
.Width = Width
.Height = Height
.[Link] = Size
.Visible = True
© Groupe Eyrolles

.ForeColor = RGB(255, 0, 0)
End With
End Sub

LA GESTION SOUS EXCEL ET VBA 10/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS ET LA CORRÉLATION LINÉAIRE

Private Function NombreVariable() As Integer

NombreVariable = TB_NbreSerie.Value
End Function

Private Function Somme(ByVal TabValeurTextBox) As Double

Dim cumul As Double

Dim indice As Integer
For indice = 0 To NombreVariable() - 1
cumul = cumul + TabValeurTextBox(indice)
Next indice
Somme = cumul

End Function

Private Function moyenne(ByVal Somme As Double) As Double

moyenne = Somme / NombreVariable()

End Function

Private Function Carre(ByVal ValeurTextBox As Double) As Double

Carre = ValeurTextBox ^ 2

End Function

Private Function Produit(ByVal ValeurTextBoxX As Double, ByVal ValeurTextboxY As Double) As Double

Produit = ValeurTextBoxX * ValeurTextboxY

End Function

Private Function Variance(ByVal sommeProduit As Double, ByVal moyenne As Double) As Double

Variance = (sommeProduit / NombreVariable()) - (moyenne ^ 2)

End Function

Private Function EcartType(ByVal Variance As Double) As Double

If Variance > 0 Then

EcartType = Sqr(Variance)
End If

End Function

Private Function covariance(ByVal sommeXY As Double, ByVal rMoyenneX As Double, ByVal rMoyenneY As
Double) As Double

covariance = (sommeXY / NombreVariable()) - (rMoyenneX * rMoyenneY)

End Function

Private Function CoefficientCorrelation(ByVal covariance As Double, ByVal EcartTypeX As Double, ByVal

EcartTypeY As Double) As Double
CoefficientCorrelation = covariance / (EcartTypeX * EcartTypeY)
End Function
© Groupe Eyrolles

Private Function DeterminerCoeffDroite(ByVal CovarianceXY As Double, ByVal Variance As Double) As Double

DeterminerCoeffDroite = CovarianceXY / Variance
End Function

LA GESTION SOUS EXCEL ET VBA 11/13 SOLUTIONS CHAPITRE 18

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Private Function DeterminerDroite(ByVal NomDroite As String, ByVal CoeffDirecteur As Double, ByVal

MoyenneX As Double, ByVal MoyenneY As Double) As Double
Const nomDroiteY As String = "DroiteY"

If NomDroite = "DroiteY" Then

DeterminerDroite = MoyenneY - (CoeffDirecteur * MoyenneX)
Else
DeterminerDroite = MoyenneX - (CoeffDirecteur * MoyenneY)
End If
End Function

ANNEXE 3

Tableau des contrôles de la boîte de dialogue

Propriété
Contrôle Outils Propriété Caption
Name

Formulaire (UserForm) UserForm1 Coefficient de correlation

Zone de liste modifiable (Combobox) TB_NbreSerie

Zone de texte (TextBox) TB_MoyenneX

Zone de texte (TextBox) TB_VarianceX

Zone de texte (TextBox) TB_MoyenneY

Zone de texte (TextBox) TB_VarianceY

Zone de texte (TextBox) TB_EcartTypeX

Zone de texte (TextBox) TB_EcartTypeY

Zone de texte (TextBox) TB_CovarianceXY

Zone de texte (TextBox) TB_Correlation

Bouton de commande (CommandButton) BC_Calculer Calculer

Bouton de commande (CommandButton) BC_Quitter Quitter

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 12/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 18 – MÉTHODE DES MOINDRES CARRÉS ET LA CORRÉLATION LINÉAIRE

Testez votre modèle à l’aide de l’annexe 4.

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 13/13 SOLUTIONS CHAPITRE 18

 CHAPITRE 19 – L’ANALYSE
DES SÉRIES CHRONOLOGIQUES

Exercice 1 : coefficient saisonnier par rapport au trend ••

Année Trimestre xi yi xi*yi xi² y = axi + b Coef. saisonnier

1T 1 36 36 1 54,13 0,67
2T 2 40 80 4 56,81 0,7
N–4
3T 3 38 114 9 59,50 0,64
4T 4 36 144 16 62,18 0,58
1T 5 70 350 25 64,86 1,08
2T 6 63 378 36 67,54 0,93
N–3
3T 7 75 525 49 70,23 1,07
4T 8 69 552 64 72,91 0,95
1T 9 125 1 125 81 75,59 1,65
2T 10 140 1 400 100 78,27 1,79
N–2
3T 11 145 1 595 121 80,96 1,79
4T 12 160 1 920 144 83,64 1,91
1T 13 45 585 169 86,32 0,52
2T 14 40 560 196 89,00 0,45
N–1
3T 15 50 750 225 91,69 0,55
4T 16 56 896 256 94,37 0,59
Total 136 1188 11010 1496

136 1 188
x = -------- = 8,5 et y = ------------- = 74,25
16 16

∑ xi yi – nxy-
a = -------------------------------
2 2
∑ xi – n x
b = y –ax
11 010 – 16 × 8,5 × 74,25
a = ------------------------------------------------------------
2
= 2,68
1 496 – 16x ( 8,5 )
b = 74,25 – 2,68 × 8,5 = 51,45

D’où la droite d’équation : y = 2,68x + 51,45.

Les coefficients saisonniers retenus sont calculés par la moyenne arithmétique des valeurs de même rang :

Trimestres Coefficients saisonniers

© Groupe Eyrolles

1er trimestre (0,67+ 1,08 + 1,65 + 0,52) /4 = 0,98

2e trimestre (0,7+ 0,93 + 1,79 + 0,45) /4 = 0,97
3e trimestre (0,58+ 1,07+ 1,79 + 0,55) /4 = 1,01
4e trimestre (0,58+ 0,95+ 1,91 + 0,59) /4 = 1,01

LA GESTION SOUS EXCEL ET VBA 1/3 SOLUTIONS CHAPITRE 19

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Prévisions N en milliers d’euros

Périodes Indices Prévisions brutes Prévisions saisonnalisées

1er trimestre 17 y = 2,68x (17) + 51,45 = 97,05 97,05 × 0,98 = 95,09
2e trimestre 18 y = 2,68x (18) + 51,45 = 99,73 99,73 × 0,97 = 96,61
3e trimestre 19 y = 2,68x (19) + 51,45 = 102,41 102,41 × 1,01= 103,52
4e trimestre 20 y = 2,68x (20) + 51,45 = 105,10 105,10 × 1,01= 105,93

Exercice 2 : coefficient saisonnier et tableur ••

Cellule Formule
C21 =SOMME(C5:C20) recopie vers la droite jusqu’à E21
D5 SI(A5="";"";B5*C5) recopie vers le bas jusqu’à D20
E5 =SI(A5="";"";B5^2) recopie vers le bas jusqu’à E20
F5 =SI(A5="";"";$B$25*B5+$B$26) recopie vers le bas jusqu’à F20
G5 =SI(A5="";"";C5/F5) recopie vers le bas jusqu’à G20
B23 =MOYENNE(B5:B20)
B24 =MOYENNE(C5:C20)
B25 =INDEX(DROITEREG(C5:C20;B5:B20);1)
B26 =INDEX(DROITEREG(C5:C20;B5:B20);2)
B28 =(G5+G9+G13+G17)/4 recopie vers le bas jusqu’à B31
E25 =$B$25*D25+$B$26 recopie vers le bas jusqu’à E28
F25 =E25*B28 recopie vers le bas jusqu’à F28

Exercice 3 : coefficient saisonnier (trend) ••

Année Trimestre xi yi xi*yi xi² y = axi + b Coef. saisonnier

1T 1 6 000 6 000 1 4 942,65 1,21
2T 2 4 500 9 000 4 4 942,79 0,91
N–4
3T 3 1 500 4 500 9 4 942,94 0,3
4T 4 5 000 20 000 16 4 943,09 1,01
1T 5 7 500 37 500 25 4 943,24 1,52
2T 6 4 800 28 800 36 4 943,38 0,97
N–3
3T 7 2 000 14 000 49 4 943,53 0,4
4T 8 5 500 44 000 64 4 943,68 1,11
1T 9 9 000 81 000 81 4 943,82 1,82
2T 10 6 200 62 000 100 4 943,97 1,25
N–2
3T 11 1 900 20 900 121 4 944,12 0,38
4T 12 4 800 57 600 144 4 944,26 0,97
1T 13 8 800 114 400 169 4 944,41 1,78
2T 14 5 600 78 400 196 4 944,56 1,13
N–1
3T 15 1 700 25 500 225 4 944,71 0,34
4T 16 4 300 68 800 256 4 944,85 0,87
© Groupe Eyrolles

Total 136 79100 672400 1496

LA GESTION SOUS EXCEL ET VBA 2/3 SOLUTIONS CHAPITRE 19

 CHAPITRE 19 – L’ANALYSE DES SÉRIES CHRONOLOGIQUES

136 79 100
x = -------- = 8,5 et y = ---------------- = 4 943,75
16 16

a =
∑ x i y i – nxy
-------------------------------- b = y –ax
2 2
∑ xi – n x
675 400 – 16 × 8,5 × 4 943,75-
a = ----------------------------------------------------------------------
2
= 0,15
1 496 – 16x ( 8,5 )
b = 4 943,75 – 0,15 × 8,5 = 4 920,50

D’où la droite d’équation : y = 0,15x + 4942,50

Les coefficients saisonniers retenus sont calculés par la moyenne arithmétique des valeurs de même rang :

Coefficients saisonniers trimestriels

Trimestre 1 1,58
Trimestre 2 1,07
Trimestre 3 0,36
Trimestre 4 0,99

Prévisions N

Périodes Indices Prévisions brutes Prévisions saisonnalisées

Trimestre 1 17 4 945,00 7 827,18

Trimestre 2 18 4 945,15 5 276,50
Trimestre 3 19 4 945,29 1 775,52
Trimestre 4 20 4 945,44 4 901,50
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/3 SOLUTIONS CHAPITRE 19

 CHAPITRE 20 – LA GESTION DES STOCKS :
LA MÉTHODE WILSON

Exercice 1 : cadence d’approvisionnement •

Le calcul s'effectue avec la formule suivante :

55 000 × 2 × 12
N = ------------------------------------- = 8
200 × 100
Lot économique en quantité = 55 000/8 = 6 875 unités.
Lot économique en valeur = 110 000/8 = 13 750 €.
Il faut donc passer 8 commandes d’une valeur de 13 750 € par commande. Soit 6 875 unités par commande.

Exercice 2 : optimisation des stocks et UserForm •••

ANNEXE 1

Interface graphique utilisateur

© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/7 SOLUTIONS CHAPITRE 20

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

ANNEXE 2

Tableau des contrôles de la boîte de dialogue

Propriété
Contrôle Outils Propriété Caption
Name

Formulaire (UserForm) fm_Wilson MÉTHODE DE WILSON

Zone de texte (TextBox) Txt_PrixU

Zone de texte (TextBox) Txt_Quantite

Zone de texte (TextBox) Txt_CPassation

Zone de texte (TextBox) Txt_TPossession

Intitulé (Label) Lab_NbreCommande

Intitulé (Label) Lab_QtOpti

Intitulé (Label) Lab_LotEconomique

Zone de liste (ListBox) ListBox1

Bouton de commande (CommandButton) CB_Calculer Calculer

Bouton de commande (CommandButton) CB_Reset Réinitialisation

Bouton de commande (CommandButton) CB_Quitter Quitter

Code VBA
Private Sub CB_Calculer_Click()
'Déclaration des variables
Dim Consommation As Single
Dim PU As Single
Dim Qt As Single
Dim CoutPassation As Single
Dim TxPossession As Single
Dim Ca As Single ' cout de passation
Dim Cp As Single ' cout de possession
Dim Ct As Single ' cout total
Dim indice As Integer
Dim ligneCadence1 As Variant

'Calcul de la cadence
PU = Txt_PrixU.Value
Qt = Txt_Quantite.Value
CoutPassation = Txt_CPassation.Value
TxPossession = Txt_TPossession.Value
Consommation = PU * Qt
'boucle For
For indice = 1 To 12
'Traitement de la première ligne
If indice = 0 Then
© Groupe Eyrolles

ligneCadence1 = Str(Cadence) + vbTab + vbTab + Format(Consommation, "## ### ##0.00") + vbTab

+ vbTab + _
Format(CoutPassation, "# ### ##0.00") + vbTab + vbTab + vbTab + _
Format(TxPossession, "### ##0.00")
[Link] (ligneCadence1)

LA GESTION SOUS EXCEL ET VBA 2/7 SOLUTIONS CHAPITRE 20

 CHAPITRE 20 – LA GESTION DES STOCKS : LA MÉTHODE WILSON

'Traitement des autres lignes du tableau d’approvisionnement

Else
Ca = indice * CoutPassation
Cp = Format((Consommation * TxPossession) / (200 * indice), "## ###0.00")
Ct = Ca + Cp
ligneCadence1 = Str(indice) + vbTab + vbTab + _
Format(Ca, "## ### ##0.00") + vbTab + vbTab + _
Format(Cp, "# ### ##0.00") + vbTab + vbTab + _
Format(Ct, "### ##0.00")
[Link] (ligneCadence1)
End If

Next indice
'Affichage du total général

ligneCadence1 =
"======================================================================================================
============="
[Link] (ligneCadence1)
'Traitement du nombre de commande et du lot économique en valeur
NbreCommande = Sqr((Consommation * TxPossession) / (200 * CoutPassation))
LotEconomique = Consommation / NbreCommande
QtOptimale = Qt / NbreCommande
'Affichage des résultats
Lab_NbreCommande.Caption = Format(NbreCommande, "### ##0")
Lab_LotEconomique.Caption = Format(LotEconomique, "### ##0")
Lab_QtOpti.Caption = Format(QtOptimale, "### ##0")
End Sub

Private Sub CB_Quitter_Click()

End
End Sub

Private Sub CB_Reset_Click()

'Mise à zéro des variables
[Link]
Txt_PrixU.Value = ""
Txt_Quantite.Value = ""
Txt_CPassation.Value = ""
Txt_TPossession.Value = ""
Lab_NbreCommande = ""
Lab_LotEconomique = ""
Lab_QtOpti = ""
End Sub

MODULE 1
‘ Ouverture de la boîte de dialogue
Sub stocks()
fm_Wilson.Show
End Sub
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 3/7 SOLUTIONS CHAPITRE 20

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Exercice 3 : gestion des stocks en avenir certain ••••

ANNEXE 2

Interface graphique utilisateur

ANNEXE 3

Tableau des contrôles de la boîte de dialogue

Propriété
Contrôle Outils Propriété Caption
Name

Formulaire (UserForm) Fm_Wilson GESTION DES STOCKS EN AVENIR CERTAIN

Zone de liste modifiable (Combobox) CB_NbreSerie

Zone de texte (TextBox) TB_Prix_Unitaire

Zone de texte (TextBox) TB_Quantite

Zone de texte (TextBox) TB_CoutPassation

Zone de texte (TextBox) TB_TauxPossession

Zone de texte (TextBox) TB_NbreCdeAn

Zone de texte (TextBox) TB_LotEco

© Groupe Eyrolles

Zone de texte (TextBox) TB_Qt_Optimale

.../...

LA GESTION SOUS EXCEL ET VBA 4/7 SOLUTIONS CHAPITRE 20

 CHAPITRE 20 – LA GESTION DES STOCKS : LA MÉTHODE WILSON

Propriété
Contrôle Outils Propriété Caption
Name

Boutons de commande (CommandButton) CB_Calculer Calculer

Boutons de commande (CommandButton) CB_Quitter Quitter

Remarque : les autres contrôles apparaissent dynamiquement à l’aide d’un programme en langage VBA.

Code VBA
'Option Explicit
Private Sub CB_Calculer_Click()
'Déclaration des variables
Dim tabPassation(12), tabPossession(12), tabCoutTotal(12) As Double
Dim PU, coutPassation, consoAnnuelle, tauxPossession, crNbreCde, crLotEco As Double
Dim Qt, indice, indiceTB, indice3, indice4 As Integer
'Vérification d'une valeur non nulle dans TB_Prix_Unitaire
If TB_Prix_Unitaire.Value = "" Or TB_Prix_Unitaire.Value <= 0 Then
MsgBox ("Le champ prix unitaire est incorrect.")
Exit Sub
End If
'Vérification d'une valeur non nulle dans TB_Quantité
If TB_Quantite.Value = "" Or TB_Quantite.Value <= 0 Then
MsgBox ("Le champ quantité est incorrect.")
Exit Sub
End If
'Vérification d'une valeur non nulle dans TB_CoûtPassation
If TB_CoutPassation.Value = "" Or TB_CoutPassation.Value <= 0 Then
MsgBox ("Le champ Cout de passation est incorrect.")
Exit Sub
End If
'Vérification d'une valeur non nulle dans TB_TauxPossession
If TB_TauxPossession.Value = "" Or TB_TauxPossession.Value <= 0 Then
MsgBox ("Le champ Taux de possession est incorrect.")
Exit Sub
End If
'ConsoAnnuelle = TB_ConsoAnnuelle.Value
PU = TB_Prix_Unitaire.Value
Qt = TB_Quantite.Value
coutPassation = TB_CoutPassation.Value
tauxPossession = TB_TauxPossession.Value
consoAnnuelle = Qt * PU
For indice = 1 To NombreVariable()
tabPassation(indice - 1) = CalculerCoutPassation(coutPassation, indice)
tabPossession(indice - 1) = CalculerCoutPossession(consoAnnuelle, indice, tauxPossession)
Next
For indice = 1 To NombreVariable()
tabCoutTotal(indice - 1) = CalculerCoutTotal(tabPassation(indice - 1), tabPossession(indice - 1))
Next
'Calcul du coût de passation et remplissage de la colonne
indiceTB = NombreVariable() + 1
indice3 = (NombreVariable() * 2) + 1
indice4 = (NombreVariable() * 3) + 1
For indice = 1 To NombreVariable()
Call SetValeurTextBox("TB_Variable" & (indiceTB), tabPassation(indice - 1))
Call SetValeurTextBox("TB_Variable" & (indice3), Format(tabPossession(indice - 1), "## ###0.00"))
Call SetValeurTextBox("TB_Variable" & (indice4), Format(tabCoutTotal(indice - 1), "## ###0.00"))
indiceTB = indiceTB + 1
indice3 = indice3 + 1
indice4 = indice4 + 1
Next
crNbreCde = CalculerNbreCommandeAn(consoAnnuelle, coutPassation, tauxPossession)
Call AfficherResultat(TB_NbreCdeAn, Format((crNbreCde), "###"))
© Groupe Eyrolles

Quantite_Optimale = Qt / crNbreCde
Call AfficherResultat(TB_Qt_Optimale, Format((Quantite_Optimale), "###"))
crLotEco = CalculerLotEconomique(consoAnnuelle, crNbreCde)
Call AfficherResultat(TB_LotEco, Int(crLotEco))
End Sub

LA GESTION SOUS EXCEL ET VBA 5/7 SOLUTIONS CHAPITRE 20

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Private Sub CB_Quitter_Click()

End
End Sub

Private Sub UserForm_Initialize()

Dim NbSerie As Single
For NbSerie = 6 To 12 Step 1
CB_NbreSerie.AddItem (NbSerie)
CB_NbreSerie.ListIndex = 0
Next NbSerie
End Sub
Private Sub CB_NbreSerie_Change()
'Déclaration des variables de travail
Const width As Integer = 60
Const height As Integer = 20
Const Size As Integer = 10
Const ecartLeft As Integer = 54
Const ecartTop As Integer = 24
Const nbColonne As Integer = 4
Const nbMaxLigne As Integer = 12
Dim top, left, nbLigne As Integer
Dim locked As Boolean
Dim indice, nbIteration1, nbIteration2, nbTextBox As Integer
'Suppression des données précédentes avant de création de nouveaux Text_Box
indice = 1
nbTextBox = (nbMaxLigne * nbColonne) + NombreVariable()
Do While nbIteration1 <= nbTextBox
Call SupprimerControle("TB_Variable" & indice)
nbIteration1 = nbIteration1 + 1
indice = indice + 1
Loop
TB_Prix_Unitaire.Value = ""
TB_Quantite.Value = ""
TB_CoutPassation.Value = ""
TB_TauxPossession.Value = ""
TB_NbreCdeAn = ""
TB_LotEco = ""
TB_CoutTotalMini = ""
'Création des objets Text_Box
locked = True
indice = 1
left = ecartLeft
top = 50
nbIteration1 = 1
nbIteration2 = 1
nbLigne = NombreVariable()
Do While nbIteration1 <= nbColonne
Do While nbIteration2 <= nbLigne
top = top + ecartTop
Call AjouterTextBox("TB_Variable" & indice, width, height, Size, top, left, nbTextBox,
locked)
indice = indice + 1
nbIteration2 = nbIteration2 + 1
If nbIteration2 > nbLigne Then
top = 50
left = left + ecartLeft
End If
Loop
nbIteration2 = 1
nbIteration1 = nbIteration1 + 1
Loop
'Remplissage de la première colonne de Text_Box par pas de 1
nbIteration1 = NombreVariable()
indice = 0
Do While nbIteration1 > 0
© Groupe Eyrolles

Call SetValeurTextBox("TB_Variable" & (indice + 1), indice + 1)

nbIteration1 = nbIteration1 - 1
indice = indice + 1
Loop
End Sub

LA GESTION SOUS EXCEL ET VBA 6/7 SOLUTIONS CHAPITRE 20

 CHAPITRE 20 – LA GESTION DES STOCKS : LA MÉTHODE WILSON

Private Sub AjouterTextBox(ByVal NomControle As String, ByVal width As Integer, ByVal height As Integer,
ByVal Size As Integer, ByVal top As Integer, ByVal left As Integer, ByVal nbTextBox As Integer, ByVal
locked As Boolean)
Set objetTextBox = Fm_Wilson.[Link]("[Link].1")
With objetTextBox
.Name = NomControle 'Définit le nom du Text_Box
.top = top 'Définit l'écart entre deux Text_Box dans le sens de la hauteur
.left = left 'Définit la distance entre le bord gauche de la fenêtre et le bord du conteneur
.width = width 'Définit la largeur de l'objet
.height = height 'Définit la hauteur de l'objet
.[Link] = Size 'Définit la taille de la police des valeurs saisies
.Visible = True
.locked = locked
End With
End Sub
Private Sub SupprimerControle(ByVal NomControle As String)
For Each Control In Fm_Wilson.Controls
If [Link] = NomControle Then
Fm_Wilson.[Link] NomControle
Exit Sub
End If
Next Control
End Sub

Private Function NombreVariable() As Integer

NombreVariable = CB_NbreSerie.Value
End Function

Private Sub SetValeurTextBox(ByVal NomTextBox As String, ByVal valeur As Double)

Controls(NomTextBox) = valeur
End Sub

Private Function CalculerCoutPassation(ByVal coutPassation As Double, ByVal Cadence As Integer) As

Double
CalculerCoutPassation = coutPassation * Cadence
End Function

Private Function CalculerCoutPossession(ByVal Consommation As Double, ByVal Cadence As Integer, ByVal

Taux As Double) As Double
CalculerCoutPossession = (Consommation * Taux) / (200 * Cadence)
End Function

Private Function CalculerCoutTotal(ByVal ValCoutPassation As Double, ByVal valTabCoutPossession As

Double) As Double
CalculerCoutTotal = ValCoutPassation + valTabCoutPossession
End Function

Private Function CalculerNbreCommandeAn(ByVal Consommation As Double, ByVal coutPassation As Double,

ByVal tauxPossession As Double) As Double
CalculerNbreCommandeAn = Sqr((Consommation * tauxPossession) / (200 * coutPassation))
End Function

Private Function CalculerLotEconomique(ByVal Consommation As Double, ByVal LotEconomique As Integer) As

Double
CalculerLotEconomique = Consommation / LotEconomique
End Function

Private Sub AfficherResultat(ByRef NomTB As Object, ByVal ValeurAfficher As Double)

[Link] = ValeurAfficher
End Sub
© Groupe Eyrolles

Private Sub CB_Méthode_WILSON_Click()

Fm_Wilson.Show
End Sub

LA GESTION SOUS EXCEL ET VBA 7/7 SOLUTIONS CHAPITRE 20

 CHAPITRE 21 – LA PROGRAMMATION LINÉAIRE :
L’ALGORITHME DU SIMPLEXE

Exercice 1 : optimisation de la production et solveur ••

Maquette du programme linéaire

Il faut fabriquer 100 produits x1

60 produits x2
Il ne restera aucune heure dans les ateliers A2 et A3.
© Groupe Eyrolles

Il restera 20 heures dans l’atelier A1.

La marge totale sera de 2 700 €.

LA GESTION SOUS EXCEL ET VBA 1/2 SOLUTIONS CHAPITRE 21

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Exercice 2 : programme linéaire et solveur ••

Maquette du programme linéaire

Il faut fabriquer 150 produits x1

50 produits x2
Il ne restera aucune heure dans les ateliers A1 et A3.
Il restera 200 heures dans l’atelier A2.
La marge totale sera de 9 500 €.
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 2/2 SOLUTIONS CHAPITRE 21

 CHAPITRE 22 – L’ALGORITHME DU STEPPING STONE

Exercice 1 : optimisation d’un programme de transport ••

Société Forez de Transport

Solution optimale : 17 500 €.

Tableau des formules

Cellule Définir Nom Formule

B3 Z =SOMMEPROD(CoutUnitaire;xij)
F8 :F10 Offre
B11 :D11 Demande
B8 :D10 CoutUnitaire
F11 =SOMME(F8:F10)
B16 :D18 xij
F16 :F18 Unités_expédiées
B19 :D19 Unités_reçues
F16 =SOMME(A16:E16) recopie vers le bas jusqu’à F18
B19 =SOMME(B16:B18) recopie vers le bas jusqu’à D19

F19 =SOMME(F16:F18)
© Groupe Eyrolles

LA GESTION SOUS EXCEL ET VBA 1/2 SOLUTIONS CHAPITRE 22

 PARTIE 3 – LES OUTILS MATHÉMATIQUES ET STATISTIQUES DE GESTION

Exercice 2 : optimisation d’un plan de transport et aide à la décision ••

1. Vérifiez l'acceptabilité du programme
Ce programme est acceptable car l’offre = la demande.
2. Établissez une solution de base par la méthode des coûts minimum.

Roanne Vienne Lyon Offre

20 21 19
Saint-Étienne
20 10 30
17 18 15
Clermont-Ferrand
25 25
19 20 21
Dijon
10 25 35
18 20 17
Grenoble
30 30
Demande 35 45 40 120

Calculez le coût total du transport.

21 × 20 = 420
19 × 10 = 190
17 × 25 = 425
19 × 10 = 190
20 × 25 = 500
17 × 30 = 510
Total : = 2 235 €

3. Déterminez la solution optimale par la méthode du Stepping-Stone à l’aide du solveur Excel.

Société Trans’Boisure
© Groupe Eyrolles

Solution optimale : 2 210 €.

LA GESTION SOUS EXCEL ET VBA 2/2 SOLUTIONS CHAPITRE 22