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)
RFC 1107 p43-47 VBA STOCKS.pdf

Article intitulé « Le contrôle des comptes à l’aide du langage VBA d’Excel », Revue Française de Comptabilité, juillet/août 2011 (n° 445)

Auteur/autrice:	Benoît-René RIVIERE
Date:	lundi 18 juillet 2011

622 KiB
7958 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
TEST-STOCK.zip

Complément de l'article "Le contrôle des comptes à l’aide du langage VBA d’Excel" : Classeur Excel de l'application VBA d'analyse de stock (compressé au format ZIP)

Auteur/autrice:	Benoît-René RIVIERE
Date:	lundi 18 juillet 2011

62 KiB
3850 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

 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

The following two tabs change content below.

• Bio
• Derniers articles

Benoît RIVIERE

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

Derniers articles parBenoît RIVIERE (voir tous)

• Nouveautés de l’interpréteur de formules de calcul (v1.1) - dimanche 3 novembre 2024
• Dématérialisation de la facturation : nouvelles mentions obligatoires - lundi 28 octobre 2024
• Interpréteur de formules de calcul en Python - dimanche 13 octobre 2024
• Les données de la facturation électronique - mercredi 9 octobre 2024
• VBA/SQL vs Power Query : deux solutions complémentaires - mercredi 2 octobre 2024