Les quatre premiers volets de cette formation ont été consacrés à Excel. Le cinquième et ce sixième volet abordent le langage VBA et l’automatisation.
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 :
- Afficher un message (boîte MsgBox)
- Entrer des données avec une boîte de dialogue InputBox
- Calculs
- Créer une fonction
- Copier/collage spécial
- Boucles compteur For… To… Step… Next
- Manipuler des plages de cellules dans des variables tableaux
Parmi les sujets évoqués, la création de fonctions Excel. Il est très facile d’ajouter de nouvelles fonctions à Excel. Le code source ci-après en est la parfaite démonstration.
Function Calcule_MtTTC(MtHT As Double, TxTVA As Double) As Double
Dim MtTTC As Double
'Calcul du montant TTC
MtTTC = MtHT * (1 + TxTVA)
'Restitution du montant TTC
Calcule_MtTTC = MtTTC
End Function
Il ajoute la fonction Calcule_MtTTC(Mt HT;Taux TVA) qui calcule le montant TTC à partir du montant HT et du taux de TVA. Simple et rudement efficace.
Exemple de tableau Excel :
La fonction nouvellement créée peut être intégrée dans une formule de calcul plus complexe :
=SI(S3>1;CalculeMtTTC(B4;B5)*S3;0)
Plus évoluée mais guère plus complexe, cette fonction calcule la clef d’un numéro de sécurité sociale. Très utile pour auditer des listes de salariés.
Function ClefNumSS(numéroSS As String)
Dim num13 As Currency
Dim s As String
Dim soustrait As Currency
numéroSS = Left(Replace(numéroSS, " ", ""), 13)
'Retraitement des départements corses (2A et 2B)
soustrait = 0
s = Mid(numéroSS, 7, 1)
Select Case s
Case "A"
numéroSS = Replace(numéroSS, "A", "0")
soustrait = 1000000
Case "B"
numéroSS = Replace(numéroSS, "B", "0")
soustrait = 2000000
End Select
num13 = CCur(numéroSS) - soustrait
'Calcul de la clef
ClefNumSS = Format(97 - (num13 - Int(num13 / 97) * 97), "00")
End Function
Le copier/coller, opération simple au demeurant, permet de transposer des données voire d’effectuer des calculs.
Les boucles compteur For… To… Step… Next permettent de répéter un ensemble d’instructions un certain nombre de fois.
Enfin, la manipulation de plages de cellules à l’aide de variables tableaux est une technique plus complexe d’usage mais qui assure des gains de temps dans l’exécution de codes VBA nécessitant de nombreuses lectures/écritures de données Excel.
Sub BoucleCompteur_VarTab()
'Déclaration des variables
Dim TauxTVA As Double
Dim TableauExcel As Variant
Dim MtHT As Double
Dim MtTTC As Double
Dim TotalTTC As Double
Dim i As Integer
'Lecture du taux de TVA
TauxTVA = ActiveWorkbook.ActiveSheet.Range("B2").Value
'Lecture du tableau Excel
TableauExcel = ActiveWorkbook.ActiveSheet.Range("B5:C10").Value
TotalTTC = 0
'Boucle de calculs
For i = 1 To UBound(TableauExcel, 1)
'Lecture du montant HT
MtHT = TableauExcel(i, 1)
'Calcul du montant TTC de la lgne en cours
MtTTC = MtHT * (1 + TauxTVA)
'Restitution du montant TTC de la ligne en cours
TableauExcel(i, 2) = MtTTC
'Calcul du montant TTC total
TotalTTC = TotalTTC + MtTTC
Next i
'Restitution des résultats sous Excel
ActiveWorkbook.ActiveSheet.Range("B5:C10").Value = TableauExcel
'Efface le contenu de la variable
Erase TableauExcel
'Restitution du total TTC
ActiveWorkbook.ActiveSheet.Range("C12").Value = TotalTTC
End Sub
Le prochain volet continuera de développer le sujet de l’automatisation et du VBA.
Le support de formation ainsi que les classeurs Excel comprenant les macros VBA sont téléchargeables ci-après.
___
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-6.pdf
Pingback: Déboguer du code VBA ou des formules de calcul Excel avec ChatGPT (IA) - Audit & Systèmes d'Information