• Qui n’a jamais pesté après le plantage d’un programme qui avait déjà tourné pendant un temps interminable, faute d’avoir contrôlé la présence de toutes les colonnes ?
  • Qui ne s’est jamais trompé de colonne pour afficher des données ou mettre en place des formules ?


Le code qui est décrit dans ce message n’a rien d’extraordinaire mais je l’utilise très régulièrement pour mettre à jour des données et il est souvent présent dans mes réponses sur DVP. J’ai pensé qu'il pouvait vous intéresser.

Principe :

Il s’agit de repérer la présence ou non d’une colonne sur une ligne de titre d’un onglet en fonction de tout ou partie d’un libellé.


A quoi ça sert ?

• A vérifier si un programme peut être lancé.
• A faciliter la mise à jour des données.
• A simplifier la maintenance du code.


Exemple :

J’ai pris un exemple simple d’une situation financière de N projets à la fin de l’exercice 2014. D'une manière très résumée, il s’agit de calculer la valeur de l’engagement (ce qui reste à payer) par rapport à l’engagé (le commandé) et à ce qui a déjà été payé (dépenses réelles).

Engagement = Dépenses engagées – Dépenses réelles

On va donc rechercher la présence ou non des colonnes nécessaires au calcul et le cas échéant, mettre en place une formule pour calculer la valeur de l’engagement.

Pièce jointe 167624


Le module ColonneFeuille

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Option Explicit
 
Public DetectionColonnes As String
 
 
Function ColonneFeuille(ByVal FeuilleTitre As Worksheet, ByVal LigneTitre As Long, ByVal TitreRecherche As String) As Long
 
Dim NbColonnes As Long
Dim Cellule As Range
Dim Aire As Range
 
    With FeuilleTitre
 
         ColonneFeuille = 0
         NbColonnes = .Cells(LigneTitre, .Columns.Count).End(xlToLeft).Column
         Set Aire = .Range(.Cells(LigneTitre, 1), .Cells(LigneTitre, NbColonnes))
 
         For Each Cellule In Aire
             Select Case Mid(Cellule.Value, 1, Len(TitreRecherche))
                    Case TitreRecherche
                         ColonneFeuille = Cellule.Column
                         Exit For
             End Select
         Next
 
         If ColonneFeuille = 0 Then DetectionColonnes = DetectionColonnes & Chr(10) & TitreRecherche
 
         Set Aire = Nothing
 
    End With
 
End Function

L'exemple de traitement du fichier

Pour cela, on initialise les variables ColProjet, ColEngage, ColReel, ColEngagement.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Sub MettreAJourLEngagement()
 
Dim ShSituation As Worksheet
Dim AireProjet As Range
Dim LigneDeTitre As Long
Dim DerniereLigne As Long
Dim ColProjet As Integer
Dim ColEngage As Integer
Dim ColEngagement As Integer
Dim ColReel As Integer
 
 
    Set ShSituation = Sheets("Situation 2014-12-31")
 
    With ShSituation
         LigneDeTitre = 2
 
         DetectionColonnes = "Absence colonnes :" & Chr(10)
 
         ColProjet = ColonneFeuille(ShSituation, LigneDeTitre, "Projet")
         ColEngage = ColonneFeuille(ShSituation, LigneDeTitre, "Engagé")
         ColReel = ColonneFeuille(ShSituation, LigneDeTitre, "Réel")
         ColEngagement = ColonneFeuille(ShSituation, LigneDeTitre, "Engagement")
 
         ' Si les colonnes sont trouvées, DetectionColonnes garde sa valeur d'origine,
         ' sinon, la variable contient les colonnes manquantes. Dans ce cas, on montre les colonnes manquantes
         ' et on arrête le programme.
         If DetectionColonnes <> "Absence colonnes :" & Chr(10) Then
            MsgBox DetectionColonnes
            Exit Sub
         End If
 
         DerniereLigne = .Cells(.Rows.Count, ColProjet).End(xlUp).Row
         ' Colprojet devient la colonne de référence
         Set AireProjet = .Range(.Cells(LigneDeTitre + 1, ColProjet), .Cells(DerniereLigne, ColProjet))
 
         ' Pour travailler sur une colonne : On prend l'offset de la colonne - La colonne de référence
         AireProjet.Offset(0, ColEngagement - ColProjet).FormulaR1C1 = "=RC[" & ColEngage - ColEngagement & "]-RC[" & ColReel - ColEngagement & "]"
 
         ' Vous aurez noté que le principe marche même avec des dizaines de colonnes positionnées à n'importe quel endroit.
         Set AireProjet = Nothing
 
    End With
    Set ShSituation = Nothing
 
End Sub

Comment ça marche ?

La variable string DetectionColonnes :
On lui affecte par défaut la valeur : « Absence colonnes : » & Chr(10)
Lorsque les colonnes auront été cherchées, si toutes les colonnes ont été trouvées, DetectionColonnes conserve son libellé. Sinon, elle contient le nom des colonnes non trouvées. Cette variable est particulièrement utile pour lancer ou non la suite d’un programme.

Pièce jointe 167625

La fonction RechercheFeuille :
Elle recherche la présence sur la ligne de titre de tout ou partie du libellé saisi en paramètre. La valeur par défaut est 0.
Nb : En Exportant le module, celui est réutilisable sur d'autres fichiers.


Notion de colonne de référence

Dans l'exemple pris, j'ai défini un objet range (la colonne des projets), puis j'ai travaillé sur la colonne Engagement au moyen d'un offset
Code : Sélectionner tout - Visualiser dans une fenêtre à part
AireProjet.Offset(0, ColEngagement - ColProjet)
.
La colonne projet est ce que j'appelle la colonne de référence de l'objet range. Tout travail sur les autres colonnes se fait très simplement avec .Offset(0, ColXXX - Colonne de référence).


Conclusion


L’exemple n’est pas spectaculaire, mais il fonctionne de la même manière avec des dizaines de colonnes positionnées à n’importe quel endroit dans l’onglet.

La limite est l’homonymie des libellés, ce sera toujours le premier libellé trouvé qui sera pris en compte.