Les quatre premiers volets de cette formation ont été consacrés à Excel. Les cinq et sixième volets abordent le langage VBA et l’automatisation. Ce septième volet clôt ce sujet du VBA.
La programmation n’est pas un sujet réservé aux développeurs professionnels. Bien au contraire. Tout un chacun peut s’approprier ce sujet sans connaissance approfondie des techniques de programmation.
Au programme :
- Convertir une chaîne de caractères en une variable tableau avec la fonction Split
- Algèbre de Boole (ou arithmétique booléenne)
- Tests conditionnels If… Then… ElseIf… Else:… End If
- Cas complexe : tests conditionnels If… Then… Else…, boucles compteur et variables tableaux
- Tests conditionnels Select Case… End Select
- Formater des cellules
- Ajouter/supprimer des lignes et des colonnes
- Approfondir le sujet
- Exemples et cas pratiques
- Découvrir la programmation avec le langage Basic
L’objectif de ce volet est d’approfondir les concepts présentés dans les deux précédents volets et d’en introduire de nouveaux.
A titre d’illustration, l’exemple qui suit mêle dans le même code source plusieurs concepts (fonction Split, variable tableau, boucle compteur, test conditionnel…) démontrant la puissance de calcul apporté par le langage VBA à Excel.
Cet exemple calcule des remises sur chiffre d’affaires selon une grille (tranches de CA et taux). Ce calcul donne le même résultat que celui de l’exemple 12 (test conditionnel If… Then… Else). Toutefois, la rédaction est ici beaucoup plus élégante notamment parce que la grille de remise est intégrée dans une constante ce qui facilite sa lisibilité et sa mise à jour.
Function Prime_CA_Grille(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
'Grilles CA et pourcentage (Pct) ; exemple CA >= 100 000 € et < 200 000 €, 1.5 % de prime
Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"
Dim Table_GrilleCA As Variant
Dim Table_GrillePct As Variant
Dim i As Integer
'Conversion des grilles en tableaux de données
Table_GrilleCA = Split(GrilleCA, ";")
Table_GrillePct = Split(GrillePct, ";")
'Parcours de la table de CA et calcul de la prime
For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
If CA >= Table_GrilleCA(i) Then
Prime_CA_Grille = CA * Val(Table_GrillePct(i)) / 100
Exit For
End If
Next i
End Function
Autre concept introduit dans ce volet, les tests conditionnels Select Case… End Select qui sont une forme de tests complémentaires des If.. Then… ElseIf… End If. Un test conditionnel permet de n’exécuter une instruction ou un ensemble d’instructions que si une ou plusieurs conditions sont remplies.
Dans cet exemple, les remises sont calculées en fonction de paliers. Dans le cadre d’un calcul par palier, le taux de remise appliqué varie en fonction de la décomposition du chiffre d’affaires réalisé par tranche (palier). Par exemple, l’application de la grille proposée plus haut à un CA de 220 K€ donne une remise de 0 % de jusqu’à 99 999 € de CA + 1,5 % de 100 000 € à 199 999 € + 2 % de 200 000 € à 220 000 € soit dans cet exemple une remise de 1 900 €.
Function Prime_CA_Palier(CA As Double)
'Calcul d'une prime en fonction du CA réalisé
'Grilles CA et pourcentage (Pct) ; exemple CA = 220 000 €, 0 % de prime jusque 99 999 € + 1,5 % de prime de 100 000 € à 199 999 € + 2 % de prime de 200 000 € à 220 000 €
'soit dans cet exemple une prime de 1 900 €
Const GrilleCA = "0;100000;200000;250000;300000"
Const GrillePct = "0;1.5;2;2.5;2.75"
Dim Table_GrilleCA As Variant
Dim Table_GrillePct As Variant
Dim i As Integer
Dim CA_Plafond As Double
'Conversion des grilles en tableaux de données
Table_GrilleCA = Split(GrilleCA, ";")
Table_GrillePct = Split(GrillePct, ";")
'Parcours de la table de CA et calcul de la prime
For i = UBound(Table_GrilleCA) To LBound(Table_GrilleCA) Step -1
Select Case i < UBound(Table_GrilleCA)
'Si i = UBound(Table_GrilleCA) -> dernière tranche de la grille de CA
Case True:
Select Case Table_GrilleCA(i + 1) < CA
Case True:
CA_Plafond = Table_GrilleCA(i + 1)
Case False:
If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
End Select
Case False:
If CA > Table_GrilleCA(i) Then CA_Plafond = CA Else CA_Plafond = Table_GrilleCA(i)
End Select
Prime_CA_Palier = Prime_CA_Palier + (CA_Plafond - Table_GrilleCA(i)) * Val(Table_GrillePct(i)) / 100
Next i
End Function
Pour de plus amples explications : consulter le support de formation téléchargeable en bas de cet article.
Différents exemples et cas pratiques sont proposés à l’usage du lecteur afin d’approfondir le sujet : contrôle des comptes, analyse du fichier des écritures comptables (FEC), calcul des pourcentages d’intérêts (comptes consolidés) y compris dans les cas les plus complexes (participations croisées, autocontrôle…) grâce au calcul matriciel… Plus insolite : comment savoir si votre ordinateur calcule faux… ou non.
Afin d’élargir le sujet, une introduction à la programmation en langage Basic (dont le VBA est une déclinaison) est proposée. Les langages VBA et Basic partagent les mêmes instructions de programmation et la même syntaxe ; l’apprentissage du Basic peut être une bonne introduction au VBA. La découverte de la programmation par le jeu vidéo s’avère plus ludique.
Plus classique, voici le tutoriel complet de programmation d’un calcul d’échéancier d’emprunt en langage Basic :
- ETAPE 1 : Présentation de FreeBASIC : https://www.auditsi.eu/?p=1628
- ETAPE 2 : Modélisation du projet : https://www.auditsi.eu/?p=1639
- ETAPE 3 : Déclaration des variables : https://www.auditsi.eu/?p=1642
- ETAPE 4 : Documentation et présentation du code-source : https://www.auditsi.eu/?p=1650
- ETAPE 5 : Affichage de texte, saisie de données, compilation et exécution d’un programme : https://www.auditsi.eu/?p=1674
- ETAPE 6 : Calculs et utilisation des variables : https://www.auditsi.eu/?p=1688
- ETAPE 7 : Boucles : https://www.auditsi.eu/?p=1696
- ETAPE 8 : Tests conditionnels : https://www.auditsi.eu/?p=1797
Le support de formation ainsi que les classeurs Excel comprenant les macros VBA sont téléchargeables ci-après.
A titre subsidiaire, ENI vient de sortir la deuxième édition du livre Business Intelligence avec Excel écrit par Boris NORO. Cet ouvrage propose à ses lecteurs de découvrir l’analyse de données à l’aide d’Excel et plus précisément de Power Query, de Power Pivot, du Langage DAX…
Il n’aura échappé à personne, que l’intelligence artificielle s’immisce de plus en plus dans nos vies. Tout récemment ChatGPT a révolutionné la génération de texte automatisée. Cet outil est non seulement capable de “créer” du texte mais également de rédiger du code informatique. Cet outil est actuellement en version beta mais il faut le tester pour en comprendre le fonctionnement et voir comment chacun peut se l’approprier. Voici, à titre d’illustration, une demande formulée par mes soins visant à obtenir de ChatGPT une fonction VBA calculant un montant TTC à partir d’un montant HT et d’un taux de TVA (sujet évoqué dans le précédent volet de cette formation) :
Finalement, il n’y aura peut-être bientôt plus besoin d’apprendre à programmer pour commander une machine ;-).
Le prochain volet s’attachera à présenter le système de gestion de bases de données Access.
___
Retrouver tous les volets de cette série dédiée à l’analyse de données et à l’automatisation avec Excel et Access
Formation-Analyse-de-donnees-Automatisation-Excel-Access-10-2022-Volet-7.pdf
Derniers articles parBenoît RIVIERE (voir tous)
- Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
- Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
- Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
- Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024
- Retrogaming : (re)jouer aux jeux vidéo d’antan - lundi 11 mars 2024
Pingback: ChatGPT et consorts : pour quoi faire ? - Audit & Systèmes d'Information