Application de Budget sur Excel comment bugeter facilement.

38
5677

Dans cette série de vidéo nous allons créer une nouvelle version du gestionnaire de budget avec beaucoup d’amélioration et d’automatisation.
Afin de pouvoir suivre ce cours à 100% je vous conseille d’utiliser Excel 2013, afin de pouvoir reproduire a 100% ce que je vais vous montrer. Sinon vous allez devoir adapter certaine chose.

1. Application gestion budget : les catégories dynamiques

  • changer le nom de la page en Catégorie
  • désormais nous allons indiquer nos catégories d’entrée et de sortie.
  • une fois les catégories connu nous allons pouvoir crée nos sous-catégorie pour notre application de budget sur excel.
  • Indiquer le nom de la sous-catégorie.ici nous voulons indiquer le nom de catégorie ou nous avons ajouter les sous-catégorie dans notre exemple cela devrais être Logement.ce si est possible avec la fonction Index et Equiv et la fonction sierreur qui affiche rien si il y a un problème:=SIERREUR(INDEX(Tableau2[Catégorie];EQUIV(1;Tableau2[ID];0));””)
  • Refaire la même chose pour tous nos catégories sortie et entrée.
  • Nommer les catégories.
    Pour nous aider à chercher dans les catégorie et sous-catégorie dans nos formule, nous allons devoir les nommées.
    Sélectionner les données du tableau et nomme le Sortie.pour les sous-catégories de sortie sélectionner le premier et nomme le Sortie1il faut faire la même chose pour la catégorie et sous-catégorie des entrées.
  • Une fois fini masquer les quadrillage
    +
  • cela fait il ne reste plus qua sauvegarder notre fichier. mais il faudra l’enregistré en tant que fichier qui prend en compte les macro

  • Désormais nous allons créer notre page de configuration.
  • Ici nous allons indiquer nos type d’action dans un tableau :- Sortie
    – Entrée
    – Budget
    – Transfer
  • Nommer le champ en Type
  • Nous allons créer une nouvelle page qui va nous permettre de registré nos compte.-ID
    -Compte
    -Somme D’ouverture
    -calcule
    – Solde ( =somme(somme d’ouverture + calcule)

2. Application gestion budget : La base de données

Comme dans la majorité des logiciels nous avons besoin d’une base de données ou nous allons registrer nos actions afin de pouvoir les traiter par la suite, et c’est ce que nous allons aussi faire  pour notre application de gestion de budget.

  • Création de la page base de donnée et nous allons la nommé input
  • Création de notre tableau avec ces colonnes :
    • ID
    • Date
    • Type
    • Somme
    • Compte
    • Du/Au
    • Description
    • CatID
    • Catégorie
    • SubID
    • Sous-Catégorie
    • Référence
    • Commentaire
    • Année
    • Mois
    • Recherche
  • configuré notre base de donnée :
    • ID –> ici nous voulons un numéro unique pour une transaction c’est pourquoi nous allons lui donner la ligne du tableau ou se trouve cette transaction cela va nous servir plus tard quand nous auront besoin de savoir où se trouve les données pour les modifier.
      c’est pourquoi nous allons introduire la formule suivante =ligne(b3)-2 qui nous affiche 1 pour la première lige et 2 pour la prochaine et ainsi de suite
    • Type –> rien
    • somme –> monétaire

    • Compte –> rien
    • du/Au –> rien

    • description –> rien

    • CatID –> rien
    • Catégorie –> =SIERREUR(SI(OU([Type]=”sortie”;[Type]=”budget”);RECHERCHEV([CatID];sortie;2;0);SI([Type]=”entrée”;RECHERCHEV([CatID];entrée;2;0);””));””)
      ici nous allons utiliser la fonction ou pour demander si le type est une sortie ou un budget alors il faut chercher le résultat dans le tableau des sortie, par contre si c’est une entrée il faudra le chercher dans le tableau entrée, si ce n’est ni l’un ni l’autre alors on affiche rien.
    • CatID –> rien
    • Sous-Catégorie –> =SIERREUR(SI(OU([Type]=”sortie”;[Type]=”budget”);RECHERCHEV([SubID];INDIRECT(“sortie”&[CatID]);2;0);SI([Type]=”entrée”;RECHERCHEV([SubID];INDIRECT(“entrée”&[CatID]);2;0);””));””)
      ici nous allons faire la même chose que pour rechercher la catégorie sauf qu’il faut que nous avons plusieurs tableau avec des sous-catégorie et il faut pourvoir dire le quel exactement. C’est pourquoi nous allons utiliser la fonction indirect avec le text voulu + le numéro du catégorie
    • Ref –> rien
    • Commentaire –> rien
    • Année–> ici nous allons uniquement affiché l’année avec la fonction Annee (c3)
    • Mois –> ici nous allons afficher le mois complet en format texte avec la fonction suivante =texte(c3 ;’mmmm ‘)
    • Recherche –> ici nous allons utiliser cette colonne comme zone de recherche et nous allons donc utiliser la fonction suivante pour tout regrouper. =CONCATENER(TEXTE([Date];”jj/mm/aaaa “);” “;[Type];” “;[Somme];” “;[Compte];” “;[Du/Au];” “;[Description];” “;[Catégorie];” “;[Sous-Catégorie];” “;[Ref];” “;[Commentaire];” “;[année];” “;[mois])la date nous allons la changer en texte avec la fonction texte(date ; »jj/mm/ aaaa ») sinon nous avons que des chiffre qui représente la date mais comme vous pouvez bien le penser ce n’est pas simple a rechercher une date comme ça.

Désormais que nous avons terminer de configuré notre base de donnée je vous invite à le compléter avec quelque sortie, entrée et budget ainsi nous allons pourvoir utiliser cela dans notre prochaine vidéo ou nous allons donc crée la page d’analyse.

3. Application gestion budget : Création Page d’analyse détailler avec les tableau croisé dynamique

Désormais nous avons notre base de donnée donc il est désormais possible de commencer à analyser nos données et nous allons voir comment crée une page d’analyse détailler. 

  • nous allons crée une nouvelle page que nous allons nommée analyse
  • pour comparé nos catégorie sortie face au budget nous allons crée un tableau croisée dynamique.
    il faut se positionner dans le tableau input et dans inseérer il faut choisir tableau croisé dynamique.

    sélectionner feuil de calcul existante et placer le dans analyse I5
  • sélectionner les données besoin.
    ici nous allons placer dans la colonne les types de sorties, dans les lignes les catégories et la valeurs la somme bien sûr. comme suite.

    ce n’est pas fini nous allons arranger un peut le tout pour que ça deviens plus présentable.
  • Type –> cliquer sur type et décoché tout sauf sortie et budget.

    puis dans notre tableau croisé dynamique nous allons changer la place du budget avec sortie.
    il suffit de cliquer sur budget et le placer après sortie.
  • Somme –> le montant nous allons les afficher en monétaire.
    Cliquer sur somme dans la case valeurs et sélectionner paramètres des champs de valeurs.

    un nouveau menu s’ouvre cliquer sur Format de nombre en bas à gauche. Sélectionner monétaire.
  • La mise en forme du tableau croisé dynamique.Désormais nous allons supprimer le total général de droite. pour cela allez dans création et dans totaux généraux sélectionner activé pour les colonnes uniquement.

    positionner le curseur sur étiquettes de lignes et appuyer sur la barre d’espace pour le supprimer.Nous allons aussi figer la taille du tableau et ne pas le laisser s’agrandir ou se réduire automatiques.
  • Analyse –> option du tableau croisé dynamique –> option.
    décocher la case ajuster automatiquement la largeur des colonnes lord de la mise à jour.

    nous allons masquer les filtre du tableau en décochant l’option en-têtes de champs
  • changer le style de tableau.
    sélectionner le style que vous voulez dupliquer la puis modifier la et re sélectionner celui que vous avez modifier.
  • placer un graphique dans le tableau croisé dynamique.
    sélectionner les sorties et placer grâce à la mise en forme conditionnelle la barre de donnée rouge (tout dépend de la version d’Excel).
  • et on fait la même chose pour les entrées.
  • le Graphique des sortie.
    – Sélectionner les catégorie + les montant de sortie.
    – Crée un graphique de secteur 3D
    – effacer le titre et la légende.
    – pour le format sélectionner étiquettes de donnée –> légende de données

    – le remplissage –> aucun
    – double click sur les valeurs et dans les options décocher % et cocher valeur.
    – dernière chose dans la Taille –> déplacer sans dimensionner avec les cellules.voila.
  • Création graphique Entrée Vs Sortie.
    – Crée un histogramme empilé.
    – dans sélectionner les données indiquer les sortie et les entrées.
    – indiquer les étiquette avec nom et somme.
    – changer les couleur des entrée et sortie.
    – mettre leurs transparence a 30%
    – le fond transparente.
    – déplacer sans dimensionner avec les cellule.
    – élargir les donnée a 10%
  • Création du détaille des sous-catégories.
    Dans la cellule b25 place un nouveau TCD avec tous les catégories et sous-catégories de sortie et budget.dans les sous)totaux ne pas les afficher ainsi nous avons uniquement le dépense des sous-catégories.La mise en forme conditionnel .- si en dessous du budget = c27<> ‘ ‘
    – si la valeur est plus que le budget = =$C27>$D27
    – si les dépense sont entre 10% ou = au budget = =ET($C27<=$D27;$C27>$D27-($D27*10%))
    – si la case est vide = c27 ‘ ‘
    11. Création d’analyse de dépense.
    – Dans i26 commencer a placer les mois d’une année
    – Création de la page rapport
    – Création du rapport- Chercher les sortie du mois dans le rapport : =SIERREUR(LIREDONNEESTABCROISDYNAMIQUE(“Somme”;rapport!$C$22;”Type”;”sortie”;”mois”;I$26);””)
    – Chercher le budget du mois =SIERREUR(LIREDONNEESTABCROISDYNAMIQUE(“Somme”;rapport!$C$22;”Type”;”budget”;”mois”;I$26);””)- Création du graphique
    – Changer le graphique en graphique combiné
    – Le budget en courbe empilé avec marque
    – Retiré les ligne
    – La marque une ligne droite a 25 en rouge
  •  Relier les données avec les segments ne pas oublier le rapport
  •  Modifier le segment
  •  Se qui nous reste a faire c’est de faire la mise en page.

Désormais nous avons notre page d’analyse qui est prête et nous pouvons parfaitement analyses nos transactions.
dans une prochaine vidéo nous allons voir comment crée un formulaire de saisie pour introduire nos sortie et entrée dans la base de donnée.

4. Application gestion budget : Formulaire d’entrée et sortie

  • Création du formulaire de base.
    – allez dans développeur –> Visual Basic
    – puis crée un formulaire
    – ajouter avec les labels 2 parties. une pour le titre et une pour afficher les soldes des comptes.
  • par la suite nous allons ajouter une listebox (qui affichera la solde de nos comptes).- pour immerger notre listbox nous allons changer la couleurs de fond à la même couleur que le label.
    – puis nous allons protéger notre listbox afin que personne ne peut le sélectionner et le changer en changeant la propriété Enable en false.
    – pour retiré le cadre nous allons changer l’option Specialeffect en Flat
  • pour afficher nos comptes nous allons :- indiquer ou se trouve nos liste dans Rowlist nous allons placer compte ( ou le nom que vous avez donner pour votre tableau compte)
    – dans colum nous allons afficher 5 pour indiquer complètement le tableau des compte.
    – et dans Columnwidth nous allons afficher 0 ; 70; 0 ; 0 ; 75 afin de masquer les information que nous n’avons pas besoin et d’afficher seulement se qui nous intéresse dans l’occurrence le nom du compte et la somme
  • puis nous allons exporter le ficher comme layout afin de pouvoir l’utiliser pour la suite.
  • avec notre layout nous allons crée notre formulaire VBA pour les sorties comme suite.
    – description = Textbox que je vais nommer description
    – Catégorie = Combobox que je vais nommer catégorie
    – Sous-catégorie = Combobox que je vais nommer subcat
    – date = textbox que je vais nommer Txtdate
    – montant = Textbox que je vais nommer somme
    – Compte = Combobox que je vais nommer compte
    – Créancier = Textbox que je vais nommer créancier
    – réferance = Textbox que je vais nommer Ref
    – Remarque = Textbox que je vais nommer remarque
    – Régistrée = Commandbouton que je vais nommer Registré
  • Pour alimenter la liste des catégories nous allons faire un double clique sur le formulaire .
    puis dans la console de code nous allons placer le code suivant.

    Private Sub UserForm_Initialize()
    Me.Catégorie.RowSource = “sortie”
    End Sub
  • par contre si nous teston le formulaire, nous voyons que la liste est bien rempli mais avec des codes et non pas la description.
    pour remedier à ce porbleme nous allons dans les propriétés du combobox catégorie et nous allons modiefier comme suite:- Columncount –> 2
    – Clumnwith –> 0; (ne pas oublier le ; )
  • désormais que nous avons la description de nos sortie nous allons travailler nos sous catégorie. faite un double clique sur le combobox Catégorie pour afficher la console et place le code suivant.
    Private Sub Catégorie_Change()

    Me.SubCat.Rowsource = “sortie” & Me.Catégorie

     

    End Sub
  • et on teste le tout et ho ici aussi nous avons que des chiffres. allé de la même façon que les catégories nous allons modifier les sous-catégories.- Columncount –> 2
    – Clumnwith –> 0; (ne pas oublier le ; )
  • pour afficher la date en format date dans un textbox nous allons utiliser un simple code
    Private Sub Txtdate_AfterUpdate()
    On Error GoTo fautunedate:
    Me.Txtdate = CDate(Me.Txtdate)
    Exit Subfautunedate:MsgBox (“format de date incorrecte!”)
    Me.Txtdate = Empty
    End Sub

    voyons un peux se code de plus prêt.
    ici nous avons demander que s’il y a une erreur alors il faut aller a Fautunedate: –> on error goto.
    et donc si nous saision n’importe quoi dans cette zone de texte nous allons aller a fautunedate et la le message “format de date incorrecte”, s’affichera.
    sinon il change le date afficher en format date.

  • pour la monnaies nous allons changer le format on numérique et monétaire, avec le code suivant.
    Private Sub somme_AfterUpdate()
    Me.somme = Replace(Me.somme, “.”, “,”)
    Me.somme = Format(Me.somme, “currency”)
    End Sub
  • et bien sur nous voulons registré le tout avec un bouton et pour cela nous allons d’abord vérifier si tout est bien rempli.
    Private Sub Registré_Click()
    ‘ controler si touts les champs obligatoire sont rempli If Me.déscription = “” Or Me.txtdate = “” Or Me.somme = “” Or Me.créancier= “” Or Me.Catégorie.ListIndex < 0 _
    Or Me.Subcat.ListIndex < 0 Or Me.Compte.ListIndex < 0 Then     MsgBox (“il manque des informations!”)Else

     

  • le contrôle est bon alors nous allons ajouter le touts dans notre tableau input.    ‘contrôler si la base de donnée est vide si ce n’est pas le cas ajouter une ligne.   
        If Sheets(“input”).Range(“b3”) <> “” Then        Sheets(“input”).ListObjects(1).ListRows.Add
        End If    
        ‘aller à la derniere ligne du tableau
        dlt = Sheets(“input”).Range(“b99999”).End(xlUp).Row
         ‘ajouter les données dans la base de donnée
        Sheets(“input”).Range(“c” & dlt) = Me.Txtdate
        Sheets(“input”).Range(“d” & dlt) = “Sortie”
        Sheets(“input”).Range(“e” & dlt) = CDbl(Me.somme) ‘CDBL nous permet de changer un format texte en numérique
        Sheets(“input”).Range(“f” & dlt) = Me.Compte.Column(1)
        Sheets(“input”).Range(“g” & dlt) = Me.créancier
        Sheets(“input”).Range(“h” & dlt) = Me.description
        Sheets(“input”).Range(“i” & dlt) = Me.Catégorie
        Sheets(“input”).Range(“k” & dlt) = Me.Subcat
        Sheets(“input”).Range(“m” & dlt) = Me.Ref
        Sheets(“input”).Range(“n” & dlt) = Me.Remarque
        ‘vider tout   
        Me.description = “”
        Me.Txtdate = “”
        Me.Somme = “”
        Me.Créancier = “”
        Me.ref = “”
        Me.remarque = “”
        Me.Catégorie = “”
        Me.Subcat.RowSource = “”
        Me.Compte = “”   
        ‘mettre a jour et sauvegarder.   
       ThisWorkbook.RefreshAll
       ThisWorkbook.Save
    End If
    End Sub
  • voila désormais notre formulaire de sortie est prêt. il ne reste plus qua faire la même chose pour les entrées. 

5. Application gestion budget : Rechercher puis Modifier ou supprimer avec un formulaire

Dans notre application nous sommes désormais capables d’ajouter des entrées et des sorties. Le problème c’est que sur la longue duré quand notre base de données sera bien remplie nous aimerions effectuer une recherche et afin de trouver certaine transaction précise et pourquoi pas les modifier ou tout simplement les effacer.

pour commencer nous allons voir comment chercher des données dans notre base de donnée par un certain critère, puis nous allons copier les données et les coller dans un autre tableau qui va nous servir pour alimenter notre liste de recherche.

aller dans input et crée une zone de rechercher avec un titre dans les cellules Z2 ( pour le titre ) et Z3 (pour le critère). Désormais copier les titre de notre tableau de base de donnée et coller le dans AB3.

nous allons crée un nom qui vas regrouper les données trouver afin de les placés dans la liste.

dans nom nommer la decaler et place le code suivant =decaler(AB3;0;0;nbval(ab3;ab999999);16)

il est de temps de crée notre formulaire de recherche et la c’est à vous de jouer afin de crée se que vous voulez.
le mien resemble à ça.

dans mon exemple je vais placer le code suivant pour afficher le résultat de recherche.

Private Sub CommandButton1_Click()>
Sheets(“input”).Range(“z3”) = “*” & Me.TextBox1 & “*” 
Range(“tableau26”).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(“input”).Range(“z2:z3”), CopyToRange:=Sheets(“input”).Range(“ab2:aq2”), Unique:= 
Me.ListBox2.RowSource = “decaler” 
End Sub

pour afficher les informations voulu dans la listbox il faut indiquer qu’il y a 16 colle dans Columncount. et puis cacher les données indisirable en placent 0pt dans la taillde collumn.

dans mon exemple:

0 pt;75 pt;49,95 pt;75 pt;49 pt;49,95 pt;150 pt;0 pt;49,95 pt;0 pt;49,95 pt;49,95 pt;40 pt;0 pt;0 pt;0 pt

afin d’afficher les information que nous voulons modifier ou supprimer, nous voulons effectuer une double clique sur la données et puis que tout s’afficher à ça place.

donc on fait un double clique dans notre zone de liste et ajouton le code suivant.

Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Ligne As Integer
On Error Resume Next 
Ligne = Me.ListBox2.ListIndex 
Me.rowid= me.listbox2.column(0,ligne)
Me.txtdate = Me.ListBox2.Column(1, Ligne)
Me.txtdate = CDate(Me.txtdate) ‘ <– mettre au format date
Me.Combotype = Me.ListBox2.Column(2, Ligne)
Me.Txtmontant = Me.ListBox2.Column(3, Ligne)
Me.Txtmontant = Format(Me.Txtmontant, “currency”) ‘<– mettre en format monetair
Me.Combocompte.RowSource =””

Me.Combocompte = Me.ListBox2.Column(4, Ligne)
Me.Combocompte.RowSource = “compte”
Me.Txtcreancier = Me.ListBox2.Column(5, Ligne)
Me.txtdescription = Me.ListBox2.Column(6, Ligne)
Me.txtref = Me.ListBox2.Column(11, Ligne)
Me.txtcom = Me.ListBox2.Column(12, Ligne) 
‘/////////////////////////////////////////////////////////////////
‘important de d’abord allimenter le combobobox et puis seulement placer la recherche
   If Me.Combotype = “Entrée” Or Me.Combotype = “Sortie” Then
        Me.combocatégorie.RowSource = Me.Combotype
    Else
        If Me.Combotype = “Budget” Then
            Me.combocatégorie.RowSource = “sortie”
        Else   
        End If
    End If
Me.combocatégorie = Me.ListBox2.Column(7, Ligne) 
    If Me.Combotype = “Entrée” Or Me.Combotype = “Sortie” Then
        Me.combosubcat.RowSource = Me.Combotype & Me.ListBox2.Column(7, Ligne)
    Else
        If Me.Combotype = “Budget” Then
            Me.combosubcat.RowSource = “sortie” & Me.ListBox2.Column(7, Ligne)
        Else   
        End If
    End If   
Me.combosubcat = Me.ListBox2.Column(9, Ligne) 
‘///////////////////////////////////////////////////////////////////// 
‘Me.TextBox5 = Me.ListBox1.Column(10, Ligne)
‘Me.TextBox6 = Me.ListBox1.Column(11, Ligne)
‘Me.TextBox7 = Me.ListBox1.Column(16, Ligne) 
End Sub

désormais que tout s’afficher à l’emplacement voulu nous allons voir comment modifier le tout avec le code suivant.

Private Sub CommandButton2_Click() 
If Me.txtdate = “” Or Me.Combotype < 0 Or Me.Txtmontant = “” Or Me.Combocompte < 0 Then 
MsgBox (“il manque des information!”)
Else
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 2) = Me.txtdate
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 3) = Me.Combotype
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 4) = Me.Txtmontant
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 5) = Me.Combocompte.Column(1)
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 6) = Me.Txtcreancier
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 7) = Me.txtdescription
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 8) = Me.combocatégorie
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 10) = Me.combosubcat
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 11) = Me.txtref
Sheets(“input”).ListObjects(1).DataBodyRange(Me.idrow, 12) = Me.txtcom
 ‘recharger la liste
Range(“tableau26”).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(“input”).Range(“z2:z3”), CopyToRange:=Sheets(“input”).Range(“ab2:aq2”), Unique:=False
Me.ListBox2.RowSource = “decaler”
ThisWorkbook.RefreshAll
ThisWorkbook.Save
End If
End Sub

pour supprimer vous allez voir que c’est plus simple avec le code suivant.

Private Sub CommandButton3_Click()
If MsgBox(“vous êtes sur le point de supprimé une donnée voulez vous continuez ?”, vbYesNo) = vbYes Then
On Error Resume Next
  Sheets(“input”).ListObjects(1).ListRows(Me.idrow).Delete   
    ‘recharger la liste
Range(“tableau26”).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets(“input”).Range(“z2:z3”), CopyToRange:=Sheets(“input”).Range(“ab2:aq2”), Unique:=False 
Me.ListBox2.RowSource = “decaler” 
ThisWorkbook.RefreshAll
ThisWorkbook.Save 
End If
End Sub

6. Application gestion budget : Planifier les budget automatiquement

Dans cette dernière partie nous allons voir comment planifier un budget automatiquement pour une duré d’une année, nous allons aussi voir comment transfère de l’argent entre les compte.

pour la création du budget nous allons d’abord indiquer dans la config quelque option supplémentaire qui vont nous permettre de calculer le budget sur une durée d’un an.

et nous allons sélectionner le tableau sans les titre et le nommer periodicité.

désormais nous pouvons crée notre formulaire de budget.

nommé les textbox et combobox de manière à comprendre ce que vous faite.

et la première chose que nous allons faire c’est de vérifier que tout est bien rempli avec le code suivant.

Private Sub CommandButton1_Click()
If Me.description = “” Or Me.somme = “” Or Me.txtdate = “” Or Me.periode = “” Or Me.catégorie < 0 Or Me.subcat < 0 Or Me.compte < 0 Then
    MsgBox (“Il manque des informations! “)

end if

avec se code nous allons vérifier si tous les champs obligatoire sont bien rempli.

désormais nous allons controler si nous avons placer une date de payment préci et si c’est le cas nous allons devoir calculer le nombre de mois d’ecart qu’il y a entre la date de planification et la date de payement. afin de connaître les mensualité.

donc nous allons modifier notre code comme suite.

Dim x As Integer
Dim payementmens As Double
Dim mensual As Double 
If Me.description = “” Or Me.somme = “” Or Me.txtdate = “” Or Me.periode = “” Or Me.catégorie < 0 Or Me.subcat < 0 Or Me.compte < 0 Then
    MsgBox (“Il manque des informations! “)
Else   
    ‘ controler si nous avoin défini une date de payement
    If Me.txtpayement <> “” Then       
        x = DateDiff(“m”, Me.txtdate, Me.txtpayement)
        payementmens = CDbl(Me.somme) / x ‘<– un calcule des mensualité qui reste a payer entre ces deux dates       
        Do Until x = 0       
            Sheets(“input”).ListObjects(1).ListRows.Add
            dlt = Sheets(“input”).Range(“b1048575”).End(xlUp).Row            
            Sheets(“input”).Range(“c” & dlt) = Me.txtdate
            Sheets(“input”).Range(“d” & dlt) = “Budget”
            Sheets(“input”).Range(“e” & dlt) = payementmens
            Sheets(“input”).Range(“f” & dlt) = Me.compte
            Sheets(“input”).Range(“h” & dlt) = Me.description
            Sheets(“input”).Range(“i” & dlt) = Me.catégorie
            Sheets(“input”).Range(“k” & dlt) = Me.subcat           
            Me.txtdate = DateAdd(“m”, 1, Me.txtdate) ‘<– ici nous allons ajouter un mois en plus a la date de départ
            x = x – 1 ‘<– on décompte le nombre de fois qu’il faut faire la loop           
        Loop
    End If

désormais que nous avons calculé le montant à budgeter entre la date de début et la date de payment. il nous reste plus qu’à budgeter pour la période d’un an en ajoutant le code suivant après le code si dessus.

x = DateDiff(“m”, Me.txtdate, DateAdd(“yyyy”, 1, Me.txtdate))
    mensual = DateDiff(Me.periode.Column(1), Me.txtdate, DateAdd(“yyyy”, 1, Me.txtdate))   
    Do Until x = 0   
            Sheets(“input”).ListObjects(1).ListRows.Add
            dlt = Sheets(“input”).Range(“b1048575”).End(xlUp).Row           
            Sheets(“input”).Range(“c” & dlt) = Me.txtdate
            Sheets(“input”).Range(“d” & dlt) = “Budget”
            Sheets(“input”).Range(“e” & dlt) = CDbl(Me.somme) * mensual / 12
            Sheets(“input”).Range(“f” & dlt) = Me.compte
            Sheets(“input”).Range(“h” & dlt) = Me.description
            Sheets(“input”).Range(“i” & dlt) = Me.catégorie
            Sheets(“input”).Range(“k” & dlt) = Me.subcat           
        Me.txtdate = DateAdd(“m”, 1, Me.txtdate)
        x = x – 1   
    Loop              
    ThisWorkbook.RefreshAll
    ThisWorkbook.Save 
End If 
End Sub

et avec thisworkbook.refreshall nous mettons à jour nos tableau croisé dynamique et avec le code thisworkbook.save nous sauvegardons le tout.

alé on test le tout pour voir si ça fonctionner.

voila nous avons notre budget il est temps pour crée notre formulaire de transfer afin que nous puisons transfère de l’argent entre nos compte.

les compte de et le compte au sont alimenter par le tableau Compte que vous devez indiquer dans les proprièté des combobox dans rowsource

et comme toujours nous allons vérifier si tout est bien indiquer et par la suite nous allons crée une entrée dans notre base de donnée pour la sortie d’un compte. puis crée une nouvelle entrée pour l’entrée d’argent sur l’autre compte.

Private Sub CommandButton1_Click()
If Me.Description = “” Or Me.somme = “” Or Me.txtdate = “” Or Me.compte1 < 0 Or Me.compte2 < 0 Then
MsgBox (“Il manque des informations!”)
Else
Sheets(“input”).ListObjects(1).ListRows.Add
dlt = Sheets(“input”).Range(“b145465”).End(xlUp).Row
Sheets(“input”).Range(“c” & dlt) = Me.txtdate
Sheets(“input”).Range(“d” & dlt) = “Transfer”
Sheets(“input”).Range(“e” & dlt) = CDbl(Me.somme) * -1
Sheets(“input”).Range(“f” & dlt) = Me.compte1.Column(1)
Sheets(“input”).Range(“h” & dlt) = Me.Description
Sheets(“input”).ListObjects(1).ListRows.Add
dlt = Sheets(“input”).Range(“b145465”).End(xlUp).Row
Sheets(“input”).Range(“c” & dlt) = Me.txtdate
Sheets(“input”).Range(“d” & dlt) = “Transfer”
Sheets(“input”).Range(“e” & dlt) = CDbl(Me.somme)
Sheets(“input”).Range(“f” & dlt) = Me.compte2.Column(1)
Sheets(“input”).Range(“h” & dlt) = Me.Description
Me.txtdate = “”
Me.somme = “”
Me.Description = “”
Me.compte1 = “”
Me.compte2 = “”
ThisWorkbook.RefreshAll
ThisWorkbook.Save
End If
End Sub

voila votre page transfer est prête.

il est temps de finaliser le tout et de crée notre page d’accueil –> HOME

sur chaque bouton nous allons ajouter le code suivant afin d’afficher le formulaire besoin.

Private Sub CommandButton1_Click()
Unload Me
Sortie.Show
End Sub
Private Sub CommandButton2_Click()
Unload Me
Entrée.Show
End Sub
Private Sub CommandButton3_Click()
Unload Me
Transfer.Show
End Sub
Private Sub CommandButton4_Click()
Unload Me
Budget.Show
End Sub
Private Sub CommandButton5_Click()
Unload Me
Database.Show
End Sub
Private Sub CommandButton6_Click()
Sheets(“Analyse”).Visible = True
Sheets(“Analyse”).Select
End Sub
Private Sub CommandButton7_Click()
Sheets(“catégorie”).Visible = True
Sheets(“Catégorie”).Select
End Sub

une autre chose que nous pouvons faire c’est de masquer les feuils que nous n’utilisons pas, c’est à dire que si je quite la page config je veux que la page config soit masquer.

et ça on le fait avec le code suivant en fesant un double clique sur la page dans le navigateur VBA en sélectionnant l’évènement désactivâtes..

 

Private Sub Worksheet_Deactivate()
Sheets(“catégorie”).Visible = False
End Sub

et ça pour chaque page.

afin d’afficher la page d’aceuil à l’ouverture du fichier lance le code suivant dans Thisworkbook éventent open

Private Sub Workbook_Open()
Sheets(“home”).Select
home.Show
End Sub

pour retourner sur le formulaire quand vous le fermer crée une page et nomme la Home, placer un bouton et indiquer le code suivant dans le bouton.

Private Sub CommandButton1_Click()
home.Show
End Sub

voila voila note application budget excel est terminer. j’espaire que cela vous a plu et si vous avez des question surtout n’hésiter pas à les poser dans les commentaire ici en bas ou sur la chaine YouTube.

38 Commentaires

  1. Bonjour DANIEL,
    Permettez moi de vous remercier avant tout, pour ce partage de connaissances.

    Dans ma situation actuelle, cette application me sera d’une très grande utilité. j’ai suivi avec grande attention, les étapes mais je cale sur la partie budget.
    Vous voudriez bien m’envoyer le fichier? Dites moi aussi si vous le vendez parce que j’en ai trop besoin en ce moment.
    merci d’avance

  2. Bonjour, j’ai un problème avec ce code:

    ‘controler si la base de donnée est vide oui ou non

    If Sheets(“input”).Range(b3) “” Then
    Sheets(“input”).ListObjects(1).ListRows.Add
    End If

    il me dit qu’il y a l’erreur “9”

    pouvez vous m’aider?

    merci

  3. Bonjour

    Je suis très heureux d’avoir découvert ton application via excel par rapport à d’autres méthodes que j’ai pu voir …

    J’aspire à devenir administrateur d’internat. Dans ma futur fonction, je suis amené à gérer le compte de l’établissement. et pour y arriver j’ai besoin de gérer ton programme sur le bout des doigts.
    Pour Cela, j’aimerais savoir si je peux encoder des numéros de comptes personnel. Car dans ton fichier, il n’y a qu’une seule ligne pour un compte…

    La deuxième réflexion est de savoir comment je peux encoder des numéros de compte des bénéficiaires et leurs noms,… Cela m’aiderait beaucoup.

    D’AVANCE MERCI ET ENCORE SUPER BOULOT

    • Bonjour Daniel

      Je me suis lancé dans la manipulation du petit programme, J’ai pu encoder mes différents numéro de compte,… j’en étais fier…

      A présent, j’aimerais si possible ajouter un tableau avec les bénéficiaires et leurs numéros de compte ( électricité, eau, téléphonie, mutuelle, …) vers qui, je pourrais faire les versements et que cela soit enregistré dans la base de donné et les enregistré parmi les comptes bénéficiaires, ceux ci devrait être différents des comptes perso… qu’en pensez-vous ?
      Manu

  4. Bonjour,
    je suis sur Excel 2019 et une des formule ne fonctionne pas (J’ai possiblement fait une erreurs, C’est celle dans la partie compte qui sert a calculé ce qui est entrée et sortie.
    =(SOMME(SOMME.SI.ENS(Tableau22[Somme];Tableau22[Compte];[@Compte];Tableau22[Type];{entrée”;”transfer”})))-SOMME.SI.ENS(Tableau22[Somme];Tableau22[Compte];[@Compte];Tableau22[Type];”sortie”)
    Merci bonne journée

  5. Bonjour tuto de rien,vu votre video concernant le budget,j’ai suivi a la lettre tout ce que vous avez dit mais quand je fais un test la date du debut change automatiquement dans le budget et cela ne prend pas en compte le format de date que j’ai mis

    Ex:
    Montant:600 €
    Date du debut:1/1/2019
    Date de paiment:4/1/2019
    Periode:mensuel

    Dans le tableau de recherche cela apparait
    3 fois avec « 200 » puis 9 fois avec 600 et les dates ne sont pas ordonnées

  6. Bonjour,
    La partie Budget ne me satisfaisant pas, j’ai essayé de la modifier. Le résultat voulu est de pouvoir rentrer un montant soit à une date soit de manière répétée périodiquement jusqu’à la date de fin. Quand je lance la budgetisation, ca plante excel comme sur une boucle infinie.
    Si vous voulez m’apporter votre aide, je suis preneur 🙂

    Voici le code que j’ai rentré :
    ———————————————-
    Private Sub CommandButton1_Click()
    Dim x As Integer

    If Me.CAB < 0 Or Me.Somme = "" Or Me.TxtDate = "" Or Me.Période < 0 Or Me.Catégorie < 0 Or Me.SubCat < 0 Or Me.Créancier = "" Then
    MsgBox ("Informations manquantes !")
    Else ' Vérifier si la date de fin est présente
    If Me.DateFin = "" Then
    Sheets("Comptes").ListObjects(1).ListRows.Add
    dlt = Sheets("Comptes").Range("A999999").End(xlUp).Row

    Sheets("Comptes").Range("A" & dlt) = "Prévision"
    Sheets("Comptes").Range("D" & dlt) = Me.TxtDate
    Sheets("Comptes").Range("E" & dlt) = Me.CAB
    Sheets("Comptes").Range("F" & dlt) = Me.Catégorie
    Sheets("Comptes").Range("G" & dlt) = Me.SubCat
    Sheets("Comptes").Range("H" & dlt) = Me.Remarque
    Sheets("Comptes").Range("I" & dlt) = Me.Créancier
    Sheets("Comptes").Range("K" & dlt) = CDbl(Me.Somme)

    Else ' Calcul du nombre de périodes restantes
    x = DateDiff(Me.Période.Column(1), MeTxtDate, Me.DateFin)
    ' Mise en place de la boucle
    Do Until x = 0
    Sheets("Comptes").ListObjects(1).ListRows.Add
    dlt = Sheets("Comptes").Range("A999999").End(xlUp).Row

    Sheets("Comptes").Range("A" & dlt) = "Prévision"
    Sheets("Comptes").Range("D" & dlt) = Me.TxtDate
    Sheets("Comptes").Range("E" & dlt) = Me.CAB
    Sheets("Comptes").Range("F" & dlt) = Me.Catégorie
    Sheets("Comptes").Range("G" & dlt) = Me.SubCat
    Sheets("Comptes").Range("H" & dlt) = Me.Remarque
    Sheets("Comptes").Range("I" & dlt) = Me.Créancier
    Sheets("Comptes").Range("K" & dlt) = CDbl(Me.Somme)

    Me.TxtDate = DateAdd(Me.Période.Column(1), 1, Me.TxtDate) 'Ajout d'une période
    x = x – 1 'Décompte de la boucle
    Loop
    End If
    End If
    End Sub
    —–

    • Bonjour, j’ai essayé également avec cette boucle. Pour rappel, mon but est d’indiquer la même somme tous les “périodes” jusqu’à la date de fin. Ca ne fonctionne pas non plus :/
      ——————————
      Else ‘ Calcul du nombre de périodes restantes

      ‘ Mise en place de la boucle
      Do Until DateDiff(Me.Période.Column(1), MeTxtDate, Me.DateFin) < 1
      Sheets("Comptes").ListObjects(1).ListRows.Add
      dlt = Sheets("Comptes").Range("A999999").End(xlUp).Row

      Sheets("Comptes").Range("A" & dlt) = "Prévision"
      Sheets("Comptes").Range("D" & dlt) = Me.TxtDate
      Sheets("Comptes").Range("E" & dlt) = Me.CAB
      Sheets("Comptes").Range("F" & dlt) = Me.Catégorie
      Sheets("Comptes").Range("G" & dlt) = Me.SubCat
      Sheets("Comptes").Range("H" & dlt) = Me.Remarque
      Sheets("Comptes").Range("I" & dlt) = Me.Créancier
      Sheets("Comptes").Range("K" & dlt) = CDbl(Me.Somme)

      Me.TxtDate = DateAdd(Me.Période.Column(1), 1, Me.TxtDate) 'Ajout d'une période

      Loop

  7. Bonjour et Merci pour les tutos.
    J’utilisais la première version et je passe maintenant à la seconde. Cependant, j’ai un soucis avec la formule “Décaler” =decaler(AB3;0;0;nbval(ab3;ab999999);16)
    Ca ne m’affiche systématiquement que la première ligne du tableau peu importe le nombre de ligne remplie en réalité.
    J’ai beau relire et écouter de nouveau le tuto, je ne vois pas mon erreur.

  8. Bonsoir Daniel,

    Merci pour ce très bon tutoriel, pour ma part, je suis très satisfaite de ces vidéos, celles-ci m’ont permis de développer mes connaissances en VBA (niveau débutant) et de redécouvrir certaines fonctions (formules, tableau croisés dynamique, filtres avancés….etc….).
    Merci pour ce travail !

    J’ai visionné l’ensemble des vidéos et mon application « gestion du budget » est terminée et fonctionne dans son ensemble plutôt bien, hâte de pouvoir la tester concrètement.
    Mais malheureusement, je rencontre 2 difficultés dans l’exécution de certains formulaires (j’utilise Excel 2016) :

    ****Dans le formulaire « Database » :

    Lorsque je clique sur le bouton « Chercher », la feuille « Input » se met bien à jour automatiquement en fonction du « type » souhaité (crédit, frais, etc…), donc de ce côté pas de souci.
    • Mon problème : aucune donnée ne s’affiche dans la « ListBox2 »….
    Pourtant j’ai bien modifié comme suit :
    ListBox2
    Boundcolum : 1
    Columncount : 16
    ColumnWidths : 0 pt;75 pt;49,95 pt;75 pt;49,95 pt;49,95 pt;150 pt;0 pt;49,95 pt;0 pt;49,95 pt;49,95 pt;40 pt;0 pt;0 pt;0 pt
    ColumnHeads : True

    Je ne suis donc pas en mesure de pouvoir utiliser la base de donnée dans son intégralité.

    ****Dans le formulaire « Budget» :

    Lorsque je clique sur le bouton « Budgéter », tout remonte correctement dans la feuille « Input » : montants Ok, affichage Ok, durée de l’échéance Ok… etc..
    • Mon problème : dans la colonne « Date », les dates d’échéances ne se mettent pas à jour…
    – Exemple :
    Date de début : 01/01/2020
    Date de paiement : 01/04/2020
    —-) Le tableau dans « Input » mentionne bien les 3 échéances mais la date reste identique (au 01/01/2020 au lieu de 01/01/2020 puis 01/02/2020 puis 01/03/2020).

    Cela serait-il du à un problème sur la formule « DateDiff » ?

    Auriez-vous la possibilité de m’aider sur ces 2 points ? Je peux vous envoyer mon fichier si nécessaire pour les codes VBA.

    Je vous remercie par avance !
    Bonne soirée!

    • Bonjour Daniel,

      Juste pour vous informer que finalement, après plusieurs vérification sur mon code, j’ai pu résoudre le problème rencontré dans le formulaire “Database ” : les données s’affichent dorénavant dans la « ListBox2 »!!
      Comme quoi quand on lâche rien…merci à vous car maintenant j’ai un fichier qui fontionne à 90%!
      Je dois juste chercher encore pour ma seconde erreur sur le formulaire”Budget”.
      En tout cas, vos vidéos me motivent à aller plus loin en VBA!

      Bon week-end!!

      • super ShantyOne, c’est comme ça que vous allez réussir ! quand il y a un problème persévérer et chercher l’erreur cela vas vous permettre de mieux comprendre le code et surtout ne plus vite corriger d’autre problème de syntaxe similaire.

  9. Bonjour/Bonsoir la ligne de code{ CatID –> rien
    Catégorie –> =SIERREUR(SI(OU([Type]=”sortie”;[Type]=”budget”);RECHERCHEV([CatID];sortie;2;0);SI([Type]=”entrée”;RECHERCHEV([CatID];entrée;2;0);””));””)
    ici nous allons utiliser la fonction ou pour demander si le type est une sortie ou un budget alors il faut chercher le résultat dans le tableau des sortie, par contre si c’est une entrée il faudra le chercher dans le tableau entrée, si ce n’est ni l’un ni l’autre alors on affiche rien.
    CatID –> rien
    Sous-Catégorie –> =SIERREUR(SI(OU([Type]=”sortie”;[Type]=”budget”);RECHERCHEV([SubID];INDIRECT(“sortie”&[CatID]);2;0);SI([Type]=”entrée”;RECHERCHEV([SubID];INDIRECT(“entrée”&[CatID]);2;0);””));””)

    }de Application de Budget sur Excel comment bugeter” Ne fonctionne pas merci de m’apporter de l’aide ce serait très gentil de votre part
    Je l’utilise Excel 2016 “

    • Bonsoir.
      Je ne sais pas si vous avez réussi à résoudre votre problème mais j’avais rencontré le même souci à ce niveau.
      J’ai utilisé les formules ci-dessous et cela fonctionne (j’utilise également Excel 2016):
      ====) LA BASE DE DONNEE
      1) CATEGORIE
      =SIERREUR(SI(OU([@Type]=”sortie”;[@Type]=”budget”);RECHERCHEV([@CatId];sortie;2;0);SI([@Type]=”entrée”;RECHERCHEV([@CatId];entrée;2;0);””));””)

      2)SOUS-CATEGORIE
      =SIERREUR(SI(OU([@Type]=”sortie”;[@Type]=”budget”);RECHERCHEV(K3;INDIRECT(“sortie”&[@CatId]);2;0);SI([@Type]=”entrée”;RECHERCHEV(K3;INDIRECT(“entrée”&[@CatId]);2;0);””));””)

      Bonne soirée !

  10. Re bonjour Daniel,
    Joker pour mon commentaire précédent, il me manquait le code 5B car je n’étais passé par YouTube pour le suivi des tutos…
    Encore bravo !!! Et merci…

  11. bonjour Jean-Luc,
    j’ai fait un opération d’entre “facture” qui se met en plusieurs échéances , 1éré versement comptant et 3 autres versement sur 3 chèques antidaté,merci de m’aider
    autre chose j’aimerai changer la symbole de monnaie en $ dans userforme

  12. Dans le formulaire de recherche, lorsque que je lance une recherche et après avoir sélectionne une ligne il ni a que la combo type où rien ne s’affiche ce qui a pour effet lorsque je fait une modification la colonne type n’est plus renseignée
    Je n’arrive pas à trouver mon erreur
    pouvez-vous m’aider
    merci

  13. Bonjour daniel
    Je voudrais vous remercier pour vos tutos. Ils sont tres bon et de tres grandes utilités.
    J’ai un soucis. quand ‘jessai de faire le formulaire de saisie , a l’initialisation du formulaire lorsque je tape la ligne
    Me.listbox1.rowsource = “compte”, il me renvoi: impossible de definir la propriété rowsource. Valeur de propriété non valide. Je suis bloqué. Que dois faire

    • Bonjour
      Est-ce que vous avez définit le nom compte, aller dans formule puis gestion de noms. Contrôler que compte existe et s’il contient bien des données.
      En espèrent que cela vous aide

  14. Bonjour,
    C’est dans la construction de la macro que se trouve mon CDbl :

    Sheets (« Input »).Range (« e » & dlt) = CDbl (Me.TxtAmount)

    Dans le champ « Amount « je rentre bien des nombres et le format « currency » si affiche bien également.
    C’est sur ma feuille « InPut » après transfert que mon montant est en TEXTE au lieu de CURRENCY.
    Encore merci pour vos réponses.
    Alain

  15. Bonjour Daniel,
    Je n’arrive toujours pas à activer la fonction CDbl voir ci-dessus et dessous :
    Ma question précédente :
    1) La fonction CDbl ne marche pas pour: Sheets (« Input »).Range (« e » & dlt) = CDbl (Me.TxtAmount)
    Votre réponse :
    est-ce que le format de votre cellule dans input E est bien numérique et non pas autre chose ?
    Lorsque je vais sur ma feuille « InPut » et si je rentre les infos à la main aucun problème, par contre si je le fais par les macros impossible, bien que le code CDbl lorsque je le rentre en minuscule se met bien en 2 majuscules + 2 minuscules, il est bien reconnu.
    Le message suivant s’affiche :
    Compile error :
    Method or data member not found

    Svp avez-vous une solution à me suggérer?

    Merci encore et bonne continuation

    Alain

  16. Bonjour Daniel,
    un grand bravo pour tous ces tutos. J’ai presque terminé le dernier du budget personnel, je bloque au formulaire de budget, je ne sais pas pourquoi, je vais encore chercher par moi même…
    En tous cas, merci vraiment, c’est du très beau travail.
    J’aimerai si possible vous contacter pour une proposition de collaboration éventuelle.
    Vous avez mon adresse mail ci-dessous.
    Renvoyez-moi un mail dès que vous avez 5 minutes si cela vous intéresse.
    Très bon dimanche.
    Aude

  17. Bonjour,
    Je tiens en premier à vous féliciter pour vos tutoriels.
    Comme je vis à l’étranger mon Excel (2013) est une version Anglaise.
    Pouvez-vous svp me dire pourquoi?:
    1) La fonction CDbl ne marche pas pour: Sheets (“Input”).Range (“e” & dlt) = CDbl (Me.TxtAmount)
    2) Lors de la création de votre exemple de Budget, lors de la dénomination des champs “Tableaux” si je change plusieurs noms, pour plus de facilitées de compréhension, les anciens noms restent actifs même s’ils n’apparaissent plus ?, et polluent la construction des macros.
    Pouvez-vous la aussi m’éclairer.
    Merci et bonne continuation
    Alain

    • le codage en VBA n’a qu’une seul langue donc la version français ou anglais ne change rien au code. ce n’est pas le cas avec les formule par contre.
      est-ce que le format de votre cellule dans input E est bien numérique et non pas autre chose ?

      je ne comprend pas trop votre seconde question. est-ce que vous avez changer les noms des tires d’un tableau ?

    • bonjour Jean-Luc,
      si vous suivez vos entrée et sortie votre résultat devrait être le même que voter comptes bancaire. sauf que vous avez un meilleur aperçu des transaction

      • Oui.
        Cependant, si j’émets un chèque, je connais la date de signature. Mais, l’encaissement peut intervenir plusieurs jours, voire semaines, et dans ce cas, le solde connu par ma banque est différent du mien.
        Je veux dire qu’il arrive qu’il y ait un décalage de la prise en compte des opérations bancaires, d’où l’intérêt de suivre ses comptes avec un tableur, en plus de l’analyse des recettes et dépenses.
        Merci.

LAISSER UN COMMENTAIRE

Please enter your comment!
Please enter your name here

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.