VBA Excel, l’outil du Übercomptable
La comptabilité se transforme de plus en plus et l’informatique commence à permettre un remplacement complet de certaines fonctions faisant partie du métier. L’automatisation, l’apport de l’IA et la spécialisation de certaines activités sont en marche et il serait naïf de croire que la profession ne connaîtra pas de modification irréversible. C’est une bonne chose en fait et une chance incroyable à saisir.
Dans la période de transition dans laquelle nous sommes, un outil à la disposition de tous est l’interface de programmation d’Excel qui utilise le langage VBA. Celui-ci permet de manipuler l’information de manière plus intime afin d’effectuer des tâches de modélisation, d’automatisation, de vérification et même de prévision. Je ne ferais pas un exposé sur l’origine de l’outil ni sur son apprentissage, cet article va plutôt se concentrer sur des étude de cas très sommaire qui démontrera la puissance de l’outil et sera pour certains d’entre vous une porte vers d’autres langages de programmation. Il s’agira d’extrait de codes ce qui implique une connaissance intermédiaire de VBA. Veuillez considérer que je suis un CPA-CMA et que je n’ai malheureusement pas eu accès à un cursus scolaire incluant l’apprentissage d’un langage. Je suis donc autodidacte sur ce point ce qui implique que mes programmes sont probablement moins bien écrits qu’un ingénieur. J’y travaille en continuant à étudier des macros et en suivant des cours en ligne. Je conseille à tous de faire de même.
Dans les tâches les plus fréquentes pour lesquels VBA est particulièrement utile est tout ce qui concerne la mise à page. Une fois le code en place, il est possible de relancer les mêmes manipulations qui auraient requis du temps inutile à refaire des opérations identiques.
Exemple de mise en page
Sub miseEnPage()
Worksheets(« Fusion »). Activate
fin = Cells (Rows.Count, 7). End(xlUp). Row
Range (Cells [2, 8], Cells [fin, 8]). Cut
Range (Cells [2, 11], Cells [fin, 11]). Select
ActiveSheet. Paste
Range(Cells [2, 9], Cells [fin, 9]). Cut
Range (Cells [2, 10], Cells [fin, 10]). Select
ActiveSheet. Paste
Call miseEnPage2
Call clé
Worksheets(« Parametre »).Activate
End Sub
Sub clé ()
Worksheets(« Fusion »). Activate
fin = Cells (Rows.Count, 3). End(xlUp). Row
For x = 2 To fin
Cells (x, 1). FormulaR1C1 = « =RC[10] & RC[3] »
Cells (x, 8). FormulaR1C1 = « =VLOOKUP(RC[-7], Fusion2! R2C1:R500C8, 5) »
Cells (x, 9). FormulaR1C1 = « =VLOOKUP(RC[-8], Fusion2! R2C1:R500C8, 6) »
Cells (x, 2). FormulaR1C1 = « =ISOWEEKNUM(RC[2]) »
Next
Columns(« H:H »).NumberFormat = « h:mm;@ »
Columns(« I:I »).NumberFormat = « h:mm;@ »
Worksheets(« Fusion2 »).Activate
fin2 = Cells(Rows.Count, 3).End(xlUp).Row
For x = 2 To fin
Cells(x, 1).FormulaR1C1 = « =RC[7] &RC[3] »
Next
Call masqueMiseEnPage
End Sub
Sub miseenpagek()
Columns(« K:K »).Select
selection.Cut
Columns(« G:G »).Select
selection.Insert Shift:=xlToRight
Columns(« K:K »).EntireColumn.AutoFit
End Sub
Une autre tâche implique la manipulation de donnée provenant de système comptable. Celles-ci seront transformées pour être analysés ou vérifiés. Dans le premier cas, l’analyse requiert souvent un type de filtre pour ordonner l’information.
Exemple de filtre
Sub filtreSemaine()
Worksheets(« Fusion »).Activate
fin = Cells(Rows.Count, 3).End(xlUp).Row
addition = 0
Call erreur2042
For x = 2 To 500
fin = Cells(Rows.Count, 3).End(xlUp).Row
actuel = Cells(x, 2).Value
last = Cells(x + 1, 2).Value
If last = actuel Then
addition = Cells(x + 1, 10).Value + addition
ElseIf last = « » Then
x = x + 1
Else
x = x + 1
Range(Cells(x, 1), Cells(x, 40)).Insert Shift:=xlDown,
CopyOrigin:=xlFormatFromLeftOrAbove
addition = addition * 1440 / 60
Cells(x, 10).NumberFormat = « 0.00 »
Cells(x, 10).Value = addition
Cells(x, 11).Value = Cells(x – 1, 11).Value
Cells(x, 12).Value = « Semaine » & » » & Cells(x – 1, 2).Value
addition = 0
End If
Next
Call delta
Call Pertinent
Worksheets(« Parametre »).Activate
End Sub
Sub delta()
Worksheets(« Fusion »).Activate
fin = Cells(Rows.Count, 3).End(xlUp).Row
For x = 2 To fin
If Cells(x, 4).Value <> « » Then
Cells(x, 13).FormulaR1C1 = « =(RC[-3]-RC[-6])*1440/60 »
If Cells(x, 13).Value = 0 Then
Cells(x, 13).Interior.Color = RGB(0, 255, 0)
ElseIf Cells(x, 13).Value < 1 Then
Cells(x, 13).Interior.Color = RGB(255, 255, 0)
ElseIf Cells(x, 13).Value > 1 Then
Cells(x, 13).Interior.Color = RGB(255, 0, 0)
End If
Else
Cells(x, 13).Value = « »
End If
Next
End Sub
Sub Pertinent()
finCL = Cells(1, Columns.Count).End(xlToLeft).Column
finLG = Cells(Rows.Count, 2).End(xlUp).Row
For x = 2 To finLG
r = Cells(x, 5).Value
v = Cells(x, 8).Value
s = Cells(x, 10).Value
t = Cells(x, 12).Value
If r = 0 And v = 0 And s = 0 And t = 0 Then
Range(Cells(x, 1), Cells(x, 18)).EntireRow.Hidden = True
Else
End If
Next
End Sub
Sub Test()
Dim varIn As Variant
Cells(1, 15).FormulaR1C1 = « =NA() »
If IsError(Cells(1, 15).Value2) Then
varIn = 0
Cells(1, 15) = 45
Else
varIn = 1
End If
End Sub
Sub erreur2042()
Worksheets(« Fusion »).Activate
fin = Cells(Rows.Count, 10).End(xlUp).Row
For x = 2 To fin
If IsError(Cells(x, 10).Value2) Then
Cells(x, 10).Value = 0
Else
End If
Next
End Sub
Note de la fin
En ce qui concerne la vérification, un travail nécessaire implique une agrégation de diverses sources de documents. Un aspect du contrôle interne nécessite souvent la validation que des postes financiers sont correctement classés. En fonction de la grosseur de la compagnie, il est possible de faire face à des milliers d’opérations voir des millions d’opérations. À l’aide d’outils statistiques et à des filtres, VBA permet d’effectuer des tests sur ces opérations. Ce travail prendrait des heures lorsqu’il est possible d’effectuer les tâches en quelques minutes. Je présenterai un exemple qui requiert un code plus élaboré dans un autre article.
Tout cela peut paraître un peu difficile à saisir pour ceux qui n’ont jamais programmé. C’est normal, il faut se laisser du temps pour comprendre d’une part les fondements de la logique de programmation et le jargon du VBA. De plus, il faut aussi savoir que le code est intimement lié à l’interface visuelle et que les exemples ne sont que des sections d’un programme qui est un ensemble de procédures. Cela prend un certain temps avant d’être en mesure de faire l’exercice d’abstraction.
Bravo à tous ceux qui ont réussi à passer à travers tout ce texte. Sachez que ce que je montre ici est un tout petit aperçu des possibilités qu’offre VBA et qu’un monde de possibilité gigantesque s’ouvre à ceux qui maîtrisent cet outil.
Il existe beaucoup de ressources sur des forums et divers manuels. Le site de github est aussi une bonne source d’exemples plus avancés. À titre indicatif, il existe de bonnes formations sur le site udemy.com pour ceux qui préfèrent suivre un cadre de formation.