Analyse de données et automatisation avec Excel et Access (6ème volet)

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.

Volet 6 VBA fonction

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.

Fonction Calcule_MtTTC

Exemple de tableau Excel :

Fonction Calcule_MtTTC Formule

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

Share Button
Formation Analyse De Données Automatisation Excel & Access 10-2022 Volet 6
Formation Analyse De Données Automatisation Excel & Access 10-2022 Volet 6
Formation-Analyse-de-donnees-Automatisation-Excel-Access-10-2022-Volet-6.pdf
7.0 MiB
344 téléchargements
Détails...
EXCEL - VBA - Exemple 4
EXCEL - VBA - Exemple 4
EXCEL-VBA-Exemple-4.xlsm
20 KiB
182 téléchargements
Détails...
EXCEL - VBA - Exemple 5
EXCEL - VBA - Exemple 5
EXCEL-VBA-Exemple-5.xlsm
20 KiB
157 téléchargements
Détails...
EXCEL - VBA - Exemple 6
EXCEL - VBA - Exemple 6
EXCEL-VBA-Exemple-6.xlsm
21 KiB
155 téléchargements
Détails...
EXCEL - VBA - Exemple 7
EXCEL - VBA - Exemple 7
EXCEL-VBA-Exemple-7.xlsm
19 KiB
161 téléchargements
Détails...
EXCEL - VBA - Exemple 8
EXCEL - VBA - Exemple 8
EXCEL-VBA-Exemple-8.xlsm
21 KiB
152 téléchargements
Détails...
EXCEL - VBA - Exemple 9
EXCEL - VBA - Exemple 9
EXCEL-VBA-Exemple-9.xlsm
18 KiB
151 téléchargements
Détails...
EXCEL - VBA - Exemple 10
EXCEL - VBA - Exemple 10
EXCEL-VBA-Exemple-10.xlsm
21 KiB
160 téléchargements
Détails...
EXCEL - VBA - Exemple 11
EXCEL - VBA - Exemple 11
EXCEL-VBA-Exemple-11.xlsm
21 KiB
174 téléchargements
Détails...
The following two tabs change content below.
Après seize années passées en cabinet d’expertise-comptable et de commissariat aux comptes (où j’ai exercé comme expert-comptable et chef de mission audit), j’ai pris le poste de directeur comptable d’un groupe de distribution automobile en novembre 2014. Au cours de ma carrière, j’ai acquis une expérience significative en audit et en exploitation des systèmes d’information (analyse de données, automatisation des tâches, programmation informatique) au service de la production des comptes annuels et consolidés. C’est cette expérience personnelle et ma passion pour l’informatique que je partage sur ce blog. Mon CV / Réalisations personnelles et projets informatiques / Ma collection / Me contacter

Un commentaire

  1. Pingback: Déboguer du code VBA ou des formules de calcul Excel avec ChatGPT (IA) - Audit & Systèmes d'Information

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

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