- 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.
Partager