Le contrôle des comptes à l’aide du langage VBA d’Excel (article publié dans la RFC)

La Revue Française de Comptabilité publie dans son numéro de juillet/août 2011 (n° 445) un article, que j’ai écrit, intitulé « Le contrôle des comptes à l’aide du langage VBA d’Excel ».

Résumé de l’article :

L’audit de données prend une part de plus en plus importante dans les travaux d’audit du commissaire aux comptes. Pour l’aider à remplir cette tâche, le commissaire aux comptes tient à sa disposition une palette complète d’outils. L’un de ces outils est le VBA (Visual Basic for Application). Il est souvent ignoré par la profession alors qu’il est présent sur tous nos ordinateurs. Facile à maîtriser et à mettre en œuvre, il n’attend qu’une seule chose : démontrer aux professionnels que nous sommes les formidables capacités de traitements de données qu’il est en mesure d’apporter à EXCEL.

Introduction :

L’audit de données peut être mené à l’aide de différents outils durant toutes les étapes de la mission du commissaire aux comptes : avec des logiciels spécialisés (IDEA, ACL), à l’aide de requêtes SQL ou encore avec des tableurs (Excel, Calc…). C’est souvent cette dernière catégorie d’outils qui a la préférence des auditeurs. Ce sont en effet les outils les plus maniables et les plus flexibles qui soient. Généralement, l’auditeur met en œuvre des fonctionnalités plus ou moins élaborées de son tableur : filtres, tris, formats conditionnels voire tableaux croisés dynamiques, liens intertables (requêtes SQL, « recherchev »)… Une autre possibilité offerte par les tableurs modernes est toutefois fréquemment négligée par méconnaissance : le VBA.

Pourtant le VBA (pour Visual Basic for Application) est un puissant langage de programmation qui accompagne toutes les applications de la suite Office de Microsoft. Ce langage de programmation étend à l’infini les capacités de traitement de l’information et d’analyse de données de ces logiciels et notamment d’Excel. Il permet d’automatiser des traitements comme l’analyse de données et de les reproduire de manière identique voire de les systématiser sur l’ensemble des dossiers. Cet article s’efforcera de démontrer l’intérêt que la profession peut tirer de cet outil de développement au travers d’un exemple simple mais efficace.

 Lire la suite :

Le contrôle des comptes à l’aide du langage VBA d’Excel (article publié dans la RFC)
622 KiB
7619 téléchargements
Détails...

Questions, suggestions ? N’hésitez pas à venir réagir sur le blog !

Programme d’audit présenté dans l’article :

  • Télécharger le classeur EXCEL de l’exemple comprenant l’application d’audit :
Application d'analyse des stocks
62 KiB
3712 téléchargements
Détails...
  • Consulter le code source de l’application : cf en pied d’article

Pour approfondir le sujet :

– Formation d’initiation au langage VBA animée par Michel PIRON, expert-comptable : cette formation propose la prise en main de l’EDI et donne les bases de la programmation en VBA EXCEL ; http://www.michelpironformation.com,

– Ouvrages : « VBA Excel 2007 – Maîtrisez la programmation sous Excel » (Editions ENI) et « VBA pour Office 2007 » (Micro-Application),

– Articles : de nombreux articles de ce blog traitent de l’analyse de données et du VBA, notamment : http://www.auditsi.eu/?p=400, http://www.auditsi.eu/?p=639 et http://www.auditsi.eu/?p=513. Pour disposer de la liste exhaustive, cliquez sur les mots-clefs en bas de cet article.

– Sites internet : http://vb.developpez.com/ et http://www.vbfrance.com/

– Espaces d’échanges ouverts à tous sur www.pacioli.fr, le réseau social de la profession comptable : « Audit & Systèmes d’Information » et « Le Cercle des Développeurs ».

Pour approfondir le sujet :

La figure 2 reproduite dans l’article de la RFC signale deux anomalies dues à des différences d’arrondi inférieures à un centime d’euros entre le total des stocks et le total calculé par l’application. La copie d’écran ci-après ne reprend que les anomalies supérieures à un euro. Les formules de calcul retranscrites dans la RFC sont correctes (l’arrondi est déclaré à l’aide de la fonction “Round()“).

Figure 2 - Résultat des tests

Figure 2 – Résultat des tests

 Code source de l’application :

Option Explicit
 
Sub TestStocks()
  'Cette procédure teste la cohérence d'un état de stock
  'Procédure rédigée par Benoît-René RIVIERE, le 17/01/2011, contact@auditsi.eu
 
  'La procédure lit la feuille la plus à gauche et restitue le résultat des tests dans une feuille intitulée 'RESULTAT hh-mm-ss'
  'La feuille la plus à gauche doit comprendre le détail du stock présenté ainsi :
  ' Colonne A : Référence
  ' Colonne B : Désignation des références stockées
  ' Colonne C : Quantité
  ' Colonne D : Prix unitaire
  ' Colonne E : Total Qté x PU
 
  'Déclaration des variables
  Dim déb, fin As Variant '1ère et dernière lignes utilisées de la feuille de calcul
  Dim débanalyse As Boolean 'tant que FALSE : l'analyse n'est pas commencée
  Dim FT_Résultat As Variant 'contient le nom de la FT des résultats des tests
  Dim ligneencours As Variant 'ligne en cours d'utilisation
  Dim lignefiltre As Variant 'ligne où se situe le filtre automatique
  Dim i, j As Variant 'compteurs (boucles)
 
  'Recherche les premières et dernières lignes du stock
  Sheets(1).Select 'la feuille n°1 (la plus à gauche) contient le détail du stock
  ActiveSheet.UsedRange.Select
  déb = Selection.Cells(1, 1).Row
  fin = Selection.Cells.Rows.Count + déb - 1
 
  'Crée une FT additionnelle qui contiendra le résultat de l'analyse
  Sheets.Add
  FT_Résultat = "RESULTAT " & Hour(Now) & "-" & Minute(Now) & "-" & Second(Now)
  ActiveSheet.Name = FT_Résultat
  Sheets(FT_Résultat).Move After:=Sheets(2)
 
  ligneencours = 0
  'Titre feuille de calcul
  ligneencours = ligneencours + 1
  With Range("A" & ligneencours)
    .Value = "TESTS SUR LE STOCK"
    .Font.Size = 18
    .Font.Bold = True
  End With
 
  ligneencours = ligneencours + 1
  'Légende
  ligneencours = ligneencours + 1
  With Range("A" & ligneencours)
    .Value = "Légende :"
  End With
  ligneencours = ligneencours + 1
  With Range("B" & ligneencours)
    .Value = "ANOMALIE"
    .Interior.Color = vbRed
  End With
  ligneencours = ligneencours + 1
  With Range("B" & ligneencours)
    .Value = "ALERTE"
    .Interior.Color = vbYellow
  End With
  ligneencours = ligneencours + 1
 
  'Crée les entêtes de la nouvelle feuille
  ligneencours = ligneencours + 1
  Range("A" & ligneencours).Value = "INFORMATIONS REPRISES DE L'ETAT DE STOCK"
  Range("A" & ligneencours & ":E" & ligneencours).Merge
  Range("F" & ligneencours).Value = "INFO CALCULEES"
  Range("F" & ligneencours & ":J" & ligneencours).Merge
  ligneencours = ligneencours + 1
  Range("A" & ligneencours).Value = "Référence"
  Range("B" & ligneencours).Value = "Désignation"
  Range("C" & ligneencours).Value = "Quantité"
  Range("D" & ligneencours).Value = "Prix Unitaire"
  Range("E" & ligneencours).Value = "Total"
  Range("F" & ligneencours).Value = "Total CALCULE"
  Range("G" & ligneencours).Value = "ECART sur Total"
  Range("H" & ligneencours).Value = "Qté négative"
  Range("I" & ligneencours).Value = "PU négatif/nul"
  Range("J" & ligneencours).Value = "Total négatif/faux"
  lignefiltre = ligneencours
  'Format colonnes et titres colonnes
  With Range("A" & ligneencours - 1 & ":J" & ligneencours)
    .HorizontalAlignment = xlCenter
    .Font.Italic = True
  End With
  Range("B" & ligneencours).ColumnWidth = 30 'Colonne Désignation
 
  'Tests
    débanalyse = False
    For i = déb To fin
      If débanalyse = True Then
      'Reprise des info de l'état de stock
      ligneencours = ligneencours + 1
      For j = 1 To 5
        Sheets(FT_Résultat).Cells(ligneencours, j).Value = Sheets(1).Cells(i, j).Value
        If j > 2 Then Sheets(FT_Résultat).Cells(ligneencours, j).NumberFormat = "# ##0.00"
      Next j
      'Analyse des quantités => anomalie si < 0
      If Sheets(1).Range("C" & i).Value < 0 Then
        Sheets(FT_Résultat).Range("C" & ligneencours).Interior.Color = vbRed
        With Sheets(FT_Résultat).Range("H" & ligneencours)
          .Value = "X"
          .HorizontalAlignment = xlCenter
        End With
      End If
 
      'Analyse des PU => anomalie si < 0 et si = 0 avec qté <>0, alerte si = 0
      '---Anomalie
      If (Sheets(1).Range("D" & i).Value < 0) Or (Sheets(1).Range("D" & i).Value = 0 And Sheets(1).Range("C" & i).Value <> 0) Then
        Sheets(FT_Résultat).Range("D" & ligneencours).Interior.Color = vbRed
        With Sheets(FT_Résultat).Range("I" & ligneencours)
          .Value = "X"
          .HorizontalAlignment = xlCenter
        End With
      '---Alerte
      ElseIf Sheets(1).Range("D" & i).Value = 0 And Sheets(1).Range("C" & i).Value = 0 Then
        Sheets(FT_Résultat).Range("D" & ligneencours).Interior.Color = vbYellow
      End If
 
      'Analyse des totaux => anomalie si < 0 ou si qté * PU <> totaux
      If (Sheets(1).Range("E" & i).Value < 0) Or (Round(Sheets(1).Range("C" & i).Value * Sheets(1).Range("D" & i).Value, 2) <> Round(Sheets(1).Range("E" & i).Value, 2)) Then
        Sheets(FT_Résultat).Range("E" & ligneencours).Interior.Color = vbRed
        Sheets(FT_Résultat).Range("F" & ligneencours).Value = Sheets(1).Range("C" & i).Value * Sheets(1).Range("D" & i).Value
        Sheets(FT_Résultat).Range("G" & ligneencours).Value = Sheets(FT_Résultat).Range("F" & ligneencours).Value - Sheets(1).Range("E" & i).Value
        Sheets(FT_Résultat).Range("F" & ligneencours & ":" & "G" & ligneencours).NumberFormat = "# ##0.00"
        With Sheets(FT_Résultat).Range("J" & ligneencours)
          .Value = "X"
          .HorizontalAlignment = xlCenter
        End With
      End If
 
    End If
 
    'Tant que l'intitulé 'Référence' n'est pas trouvé, l'analyse des stock ne débute pas
    If (débanalyse = False) And (Sheets(1).Range("A" & i).Value = "Référence") Then débanalyse = True
  Next i
 
  'Filtres automatiques
  Sheets(FT_Résultat).Range("A" & lignefiltre).AutoFilter
 
End Sub

Approfondir le sujet : programmer en VBA

Share Button
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

7 commentaires

  1. Bonjour,

    Je cherche à améliorer mes feuilles de travail pour la prochaine période et je ne n’avais jusque là pas pensé au VBA, j’ai tenté de transposer le code fourni pour effectuer des tests malheureusement de nombreuses lignes de programmation donnait ci dessus ne fonctionnent pas ; pour exemple :
    fin = Selection.Cells.Rows.Count + déb – 1

    With Range (” A ” & ligneencours – 1 & ” :J ” & ligneencours)

    ou encore

    Sheets(FT_Résultat).Range(” G ” & ligneencours).Value = Sheets(FT_Résultat).Range(” F ” & ligneencours).Value – Sheets(1).Range(” E ” & i).Value

    Pouvez vous apporter des précisions sur ces lignes afin de rendre le programme utilisable ?

  2. Bonjour,

    Tout d’abord, je vous remercie de vous intéresser à mon article et à ce programme d’audit.

    Avez-vous téléchargé le fichier Excel pour le tester ? Si oui, fonctionne-t-il correctement ?

    Voici quelques explications complémentaires :

    – fin = Selection.Cells.Rows.Count + déb – 1 : fin (numéro de la ligne de fin du tableau à obtenir) sera égal au nombre (count) de lignes du tableau sélectionné (selection) + le numéro de ligne de début du tableau à obtenir.
    – Range ( » A » & ligneencours – 1 & » :J » & ligneencours) : définit une plage de cellule s’étendant de Ax-1 à Jx. Ligneencours définit la ligne du tableau Excel en cours d’utilisation ; par exemple si ligneencours = 10, la plage Excel sera égale à “A9:J10”.
    – Sheets(FT_Résultat).Range( » G » & ligneencours).Value = Sheets(FT_Résultat).Range( » F » & ligneencours).Value – Sheets(1).Range( » E » & i).Value : la cellule Gx (range) de la feuille FT_Résultat sera égale à la soustraction entre la valeur de la cellule Fx de la feuille FT_Résultat et la valeur (value) contenue dans la plage Ex (range) de la feuille numéro 1 (sheets)

    Si vous avez d’autres questions, je suis disponible. N’hésitez pas à partager vos propres programmes, je serai ravi de les tester.

    Cordialement,

    B. RIVIERE

  3. Je vous remercie pour votre réponse. Malheureusement le code VBA ne reconnait pas les “-1” des lignes de programmation précédemment citées. Je travail sous Excel 22003, je ne sais pas si cela peut avoir une incidence.
    Je n’ai pas téléchargé le fichier excel pour la simple et bonne raison que je ne vois pas de lien permettant le téléchargement…
    J’ai donc juste fait un copier coller du code.
    Comme je n’arrivais pas a le modifier pour le faire fonctionner, je suis entrain de créer des macros pour faire les contrôles de bases

  4. Pingback: Audit des comptes consolidés – Validation des pourcentages d’intérêt à l’aide du calcul matriciel et du VBA | Audit & Systèmes d'Information

  5. Pingback: Audit & Programmation : S’initier au langage VBA d’EXCEL | Audit & Systèmes d'Information

  6. BONJOUR
    Vous êtes vraiment utile en se sens que vos outils sont tellement développés que j’avais des difficultés pour s’en servir.
    1- je veux que vous m’appreniez comment mettre en application ses différents outils
    2- surtout lors de mes missions au sein de la banque: par exemple audit des engagements, audit comptable et financier; audit juridique; audit exploitation; audit porte feuille clients etc.
    merci IDRISS

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.