Tutoriel : apprendre à programmer des classes pour manipuler des tableaux en VBA

Modélisation d’un framework d’accès aux tableaux structurés d’Excel

Pierre Fauconnier

Apprendre à créer des classes personnalisées en VBA pour enrichir les objets de base d’Excel, créer une couche d’abstraction qui permet au développeur d’accélérer et de systématiser son approche et sa production de code en utilisant une architecture professionnelle.

Ce tuto s’adresse au développeur qui doit régulièrement manipuler des tableaux structurés en VBA et qui souhaite ne pas réinventer la roue à chaque nouveau développement. Outre des classes personnalisées prêtes à l’emploi et qu’il pourra enrichir, il trouvera dans ces pages des idées pour améliorer son code, son architecture et, in fine, sa productivité.

- Titre : Tutoriel : apprendre à programmer des classes pour manipuler des tableaux en VBA

- Auteur : Pierre Fauconnier

- Parution : 31 janvier 2022

- Mise à jour : 17 février 2022

- Public visé :

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2022 Pierre Fauconnier. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- I. Introduction

- II. Objectifs de ce tutoriel

- III. Définition du framework

- III-A. Les objets de manipulation du tableau

- III-A-1. Parallèle entre les objets natifs d’Excel et les classes personnalisées

- III-A-2. Gestion des erreurs dans nos classes personnalisées

- III-B. Le Design Pattern Factory

- IV. Rappels sur les classes

- IV-A. Création d’une classe

- IV-B. Méthode, propriété, fonction, variable privé, évènement, erreur

- IV-B-1. Méthode

- IV-B-2. Propriété

- IV-B-2-a. Propriété en lecture/écriture

- IV-B-2-b. Propriété en lecture seule

- IV-B-2-c. Propriété en écriture seule

- IV-B-3. Fonction

- IV-C. Variables privées

- IV-D. Evènements

- IV-E. Création d’erreurs personnalisées

- V. L’objet xlTable

- V-A. Création de la classe

- V-B. Initialisation avec un ListObject

- V-C. Première propriété, première méthode

- V-C-1. Création d’une propriété ListObject

- V-C-2. Création de la méthode Clear

- V-C-2-a. Rappel : Vidange d’un tableau structuré

- V-D. Amélioration de la méthode Init

- V-D-1. Solution procédurale

- V-D-2. Solution dans la classe

- V-D-3. Le Design Pattern Factory

- V-E. Autres méthodes et propriétés de xlTable

- V-E-1. Fonction IsEmpty

- V-E-2. Méthode AddColumns

- V-F. Conclusions de cette approche

- VI. L’objet xlRow

- VI-A. Rappels sur le ListRow

- VI-B. Création de l’objet xlRow

- VI-B-1. Création de la classe

- VI-B-2. Initialisation et propriété Value en lecture-écriture

- VI-B-2-a. initialisation

- VI-B-2-b. Récupération et modification de la valeur d’une cellule de la ligne.

- VI-C. Instanciation d’un objet xlRow

- VI-C-1. Création d’une ligne dans le tableau

- VI-C-2. Récupération d’une ligne existante par sa position

- VI-C-3. Récupération d’une ligne existante par la valeur de son index

- VI-C-4. Récupération d’une ligne existante par recherche de valeurs

- VI-D. Quelques autres propriétés et méthodes de l’objet xlRow

- VI-D-1. Supprimer la ligne

- VI-D-2. Récupérer la position de la ligne

- VI-D-3. Duplication de la ligne

- VI-D-4. Renvoyer le Range et l’adresse de la ligne

- VI-D-5. Tester qu’une ligne est vide

- VI-D-6. Renvoyer une cellule particulière de la ligne

- VI-E. Vider une ligne

- VI-F. Conclusions sur notre classe xlRow

- VII. L’objet xlColumn

- VII-A. Propriétés et méthodes de l’objet ListColumn

- VII-B. Création de la classe xlColumn

- VII-B-1. Création de la classe

- VII-B-2. Initialisation et propriétés Parent et ListColumn

- VII-C. Instanciation

- VII-D. Quelques propriétés et méthodes du xlColumn

- VII-D-1. Propriété isEmpty

- VII-D-2. Propriétés Liées à la ligne de total

- VII-D-2-a. Propriété TotalValue

- VII-D-2-b. Propriété TotalFormula

- VII-D-3. Fonction Duplicate

- VII-D-4. Méthode ClearContents

- VII-D-5. Méthode Delete

- VII-E. Conclusions sur notre classe xlColumn

- VIII. Nos classes personnalisées dans l’explorateur d’objets

- IX. La gestion des erreurs au sein de nos classes personnalisées

- IX-A. Préambule

- IX-A-1. Code VBA protégé contre l’affichage

- IX-A-2. Code VBA accessible

- IX-A-3. S’assurer que les erreurs sont toujours gérées

- IX-A-4. La pile des appels

- IX-B. La programmation par l’erreur

- IX-B-1. Exemples de programmation par l’erreur

- IX-C. Prévoir les problèmes et générer une erreur dans nos classes.

- IX-C-1. Générer une erreur en VBA

- IX-C-2. Exemple de création d’une erreur dans la fonction xlTable.NewRow.

- IX-C-3. Gestion des erreurs dans nos classes

- IX-C-4. Capturer l’erreur dans la classe pour générer une erreur personnalisée

- IX-C-4-a. Capture de l’erreur et génération d’une erreur personnalisée

- IX-C-5. Test de l’existence de la colonne dans une fonction (privée ou publique)

- IX-D. Conclusions de cette partie

- X. Annexes

- XI. Conclusions

- XII. Les fichiers

- XIII. Remerciements

- XIV. Annexes

- XIV-A. Méthodes et propriétés de xlTable

- XIV-B. Méthodes et propriétés de xlRow

- XIV-C. Méthodes et propriétés de xlColum

I - Introduction

Les tableaux structurés ont apporté à Excel l’outil qui lui manquait pour stocker et gérer les données. Ils n’ont pas transformé Excel en SGDB, loin de là, mais ils ont simplifié la gestion des données avec Excel et ont, dans les faits, révolutionné la manière de concevoir nos classeurs et notre approche d’Excel. A eux seuls, ils justifiaient amplement le passage à Excel 2007 et, à l’heure d’écrire ces lignes (février 2022 !), je ne peux que regretter leur sous-utilisation et le fait qu’encore peu de personnes les connaissent, les manipulent et les maîtrisent, ainsi que de le fait que de trop nombreuses réponses sur nos forums proposent encore une approche « à l’ancienne » obsolète et à mon avis non professionnelle.

J’ai écrit deux tutoriels, l’un pour la maîtrise des tableaux structurés en Excel et l’autre permettant la compréhension de l’objet ListObject, qui est le petit nom des tableaux structurés en VBA :

1. Excel :

2. VBA :

On pourra toutefois trouver des manques et des lacunes aux objets proposés par Excel. Par exemple, aujourd’hui, il n’existe toujours pas un outil qui permet de vider entièrement un tableau, c’est-à-dire d’en supprimer toutes les lignes. Il existe bien le raccourci CRL+A qui permet de sélectionner la plage de données du tableau dont dépend la cellule active et il suffit alors de supprimer les lignes sélectionnées par clic droit, mais il n’y a ni clic droit ni bouton du ruban pour vider le tableau en un clic. Le VBA n’offre pas non plus la possibilité de vider un tableau, style MonTableau.Clear. Il faut penser à défiltrer et à tester que le tableau n’est pas vide pour le vider.

C’est notamment au départ de ce constat qu’est née la création de cette classe personnalisée que j’utilise depuis longtemps et que je vous propose de découvrir, d’étudier, et d’étendre à vos propres besoins.

Je gage que votre utilisation quotidienne de l’outil fera apparaître d’autres manques, et si vous utilisez un peu Power Query, vous constaterez notamment qu’il est possible de sélectionner certaines colonnes pour supprimer celles qui ne sont pas sélectionnées, manipulation impossible à réaliser dans un tableau structuré, en tout cas avec les outils natifs d’Excel ou du ListObject (Super lorsque l’on a 100 colonnes dans le tableau et que l’on souhaite en garder 5, par exemple.

On peut pallier ces manques à coup de programmation, que ce soit sur un ruban personnalisé ou lors de manipulations en VBA, à chaque fois que l’on rencontre ce besoin. C’est souvent alors une programmation « à l’arrache », en réinventant mille fois le code, avec plus ou moins de bonheur.

Afin de systématiser notre approche du ListObject en VBA, on peut créer des fonctions de manipulation du ListObject et, tant qu’à faire, les grouper dans un module, voire plusieurs. Laurent Ott propose cela dans une optique procédurale avec ce tutoriel :. Cette approche est dite procédurale car le programmeur manipulera le ListObject au travers d’un ensemble de fonctions auxquelles on passe le ListObject ou l’objet Range lié.

Pour ma part, je vais vous proposer la même finalité, à savoir une approche systématique du ListObject, de sa programmation et de sa manipulation par VBA, dans une optique POO, à savoir Programmation Orientée Objet. L’exercice va donc consister à créer un « sur-objet », une classe qui englobe le ListObject et augmente les outils de ce dernier. J’amène donc une couche d’abstraction qui permet, un peu comme Monsieur jourdain faisait de la prose sans le savoir, de manipuler un ListObject sans le savoir tout en systématisant l’écriture du code.

II - Objectifs de ce tutoriel

Ce tutoriel poursuit plusieurs objectifs :

- Proposer des classes personnalisées prêtes à l’emploi pour faciliter et systématiser la manipulation des tableaux structurés en VBA ;

- Illustrer la création de classes personnalisées, dans la continuité de mon tutoriel sur la création de ces classes, en proposant une démarche de création d’objets permettant de systématiser notre approche du code, classes qui pourront évoluer au fur et à mesure de nos besoins.

- Proposer une démarche de qualité dans la production de code en proposant de porter son attention sur l’architecture du code et la réutilisabilité des fonctions que nous créons. Cette approche repose sur l’idée d’un framework, un réel cadre de travail, qui nous permet d’embarquer dans chaque nouveau projet des modules de code générique et donc réutilisable

- Accélérer la production de code applicatif en s’appuyant sur du code testé et approuvé en permettant au programmeur utilisant ces classes de se concentrer sur le besoin de l’application et non sur la production de code.

III - Définition du framework

III-A - Les objets de manipulation du tableau

III-A-1 - Parallèle entre les objets natifs d’Excel et les classes personnalisées

Un ListObject met à disposition du programmeur trois classes :

- Le ListObject en lui-même qui permet la manipulation du tableau structuré ;

- Le ListColumn, qui permet de manipuler une colonne particulière de la collection ListColumns du ListObject ;

- Le ListRow, qui permet la manipulation d’un ligne particulière du tableau au travers de la collection ListRows du ListObject.

Ces trois objets ont été étudiés dans le tutoriel sur la manipulation des tableaux structurés en VBA ().

Nous allons donc retrouver trois classes personnalisées offrant une couche d’abstraction pour ces trois objets « natifs » du modèle objet Excel :

- Le xlTable, offrant des outils supplémentaires pour manipuler le ListObject ;

- Le xlColumn pour « surclasser » le ListColumn ;

- Le xlRow pour étendre les possibilités du ListRow.

Dans la mesure où nous accédons aux ListRow et ListColumn au travers du ListObject, nous accéderons aux xlRow et xlColumn au travers du xlTable.

III-A-2 - Gestion des erreurs dans nos classes personnalisées

Pour ne pas alourdir ce cours par la gestion des erreurs, je n’en parle pas dans le cadre de la création des classes personnalisées. J’ai réservé un chapitre spécifique pour cela en fin de tuto, avant les annexes.

III-B - Le Design Pattern Factory

Le Design Pattern Factory est un modèle de conception qui favorise la mise en place d’une fabrique (factory en anglais) d’objets. Ce Factory centralise la création et la mise à disposition des objets au sein de l’application. Dans le cadre de ce tuto, nous verrons comment le mettre en place pour servir notamment les xlTable souhaités au code client, permettant au développeur une systématiser la mise à disposition d’objets.

On retrouve donc ici un des objectifs du tutoriel, à savoir proposer la mise en place d’une architecture professionnelle du code, en proposant notamment l’adoption de Design Pattern qui ont fait leurs preuves (ici, le design pattern Factory).

IV - Rappels sur les classes

Si vous n’êtes pas à l’aise avec les classes personnalisées, je vous invite à lire mon tutoriel sur la création de classes personnalisées en VBA : https://fauconnier.developpez.com/articles/vba/general/classes/.

IV-A - Création d’une classe

Pour créer une classe, rien de plus simple. Il suffit d’ajouter un module de classe au classeur et de le nommer. Le nom attribué constituera le type des objets issus de cette classe.

IV-B - Méthode, propriété, fonction, variable privé, évènement, erreur

IV-B-1 - Méthode

Une méthode est à une classe ce qu’une procédure est à un module standard. On crée une méthode en créant une procédure dans le module de classe. Cette méthode est publique ou privée.

A la différence d’une procédure, et pour autant qu’elle soit publique, elle doit être préfixée de l’objet que l’on manipule. Une méthode privée ne peut pas être invoquée de l’extérieur de l’objet.

IV-B-2 - Propriété

Une propriété est à la classe ce que la fonction est à un module standard. Elle permet au code appelant d’exploiter une valeur de l’objet.

IV-B-2-a - Propriété en lecture/écriture

Une propriété en lecture/écriture permet de récupérer une valeur de l’objet, mais également de modifier cette valeur au sein de l’objet.

Pour que le code appelant puisse récupérer la valeur de la propriété (lecture), on utilise Property Get MyProperty() au sein de la classe. La propriété peut être typée : Property Get MyProperty() As String. Elle peut recevoir des arguments :  Property Get MyProperty(Name as String).

Pour que le code appelant puisse modifier la valeur de la propriété (écritue), on utilise Property Let MyProperty(Value). Value doit être un argument de même type que celui de la propriété éponyme en lecture. Property Get MyProperty() As String => Property Let MyProperty(Value as String). Si des arguments sont présents dans la propriété en lecture, ils doivent être présents et de même type dans la propriété en écriture : Property get MyProperty(Name as string) As Long => Property Let MyProperty(Name As String, Value As Long).

IV-B-2-b - Propriété en lecture seule

Une propriété en lecture seule ne peut pas être modifiée par le code qui manipule l’objet. Dans ce cas, la propriété dispose d’un Property Get mais pas d’un Property Let.

IV-B-2-c - Propriété en écriture seule

Une propriété en écriture seule peut être modifiée par le code qui manipule l’objet, mais ne peut pas être lue par celui-ci. Dans ce cas, la propriété dispose d’un Property Let, mais pas d’un Property Get.

IV-B-3 - Fonction

Une fonction est en fait comme une propriété en lecture seule. Perso, je fais peu de différences entre les deux. La saisie semi-automatique ne fait d’ailleurs pas de différenciation au niveau des icones.

IV-C - Variables privées

Les variables privées d’un module de classe ont comme caractéristiques de rester valorisées tant que l’objet existe, c’est-à-dire qu’on ne le détruit pas avec Set MyObject = Nothing. On notera toutefois qu’un arrêt du code repasse tous les objets à Nothing.

IV-D - Evènements

Il est possible de créer une évènement dans une classe personnalisée. Cet évènement sera écouté par le code appelant (on appelle cela un Listener dans d’autres langages) qui pourra le capturer et le gérer. L’écoute de l’évènement est facultative, et c’est donc le développeur applicatif qui décide d’écouter ou non les évènements d’une classe. On notera que les évènements ne peuvent être écoutés que si les objets ont été déclarés avec WithEvents et qu’ils sont déclarés dans un module de classe (module de classeur, de feuille, de userform, d’autre objet personnalisé).

Je vous invite à vous reporter au chapitre qui traite des évènements dans mon tutoriel sur les classes personnalisées : https://fauconnier.developpez.com/articles/vba/general/classes/#LIV-E-2

IV-E - Création d’erreurs personnalisées

Il et possible de déclencher des erreurs dans nos classes, qui seront gérées ou non par le code appelant. A la différence des évènements, l’erreur s’impose au code appelant, qui soit la gère avec un On Error…, soit ne la gère pas et stoppe l’exécution du code, avec affichage de la ligne de code en erreur si le code est affichable. La génération d’une erreur passe par l’instruction Err.Raise Number, Soure, Description.

L’exemple suivant montre comment générer une erreur si l’on essaie de valoriser la cellule d’une colonne inexistante dans un tableau.

Property Let Value(Name As String, Value)
If Not ColumnExists(Name) Then Err.Raise 2000, "xlRow.Value", "Colonne inexistante dans la table"
mListRow.Range(mListRow.Parent.ListColumns(Name).Index).Value = Value
End Property

Les erreurs générées par Err.Raise sont gérées comme toute erreur d’exécution VBA au traves des possibilités de On Error.

V - L’objet xlTable

V-A - Création de la classe

Pour créer la classe xlTable, on va créer un module de classe et le nommer xlTable. Il permettra de créer des objets issus de cette classe.

La création d’un module de classe est assez simple. Il suffit d’ajouter un module de classe et de lui attribuer un nom. Ce nom sera celui qui servira de type aux objets basés sur la classe.

A partir de ce moment, on peut déclarer et utiliser des objets de cette classe.

Sub Test()
Dim t As xlTable

Set t = New xlTable
Debug.Print TypeName(t)
End Sub

V-B - Initialisation avec un ListObject

Telle quelle, la classe ne sert à rien. Pour l’exemple, on va l’initialiser en lui passant le ListObject qu’elle va surclasser et on stockera ce ListObject dans une variable privée du module.

Option Explicit

Private mListObject As ListObject

Sub Init(Table As ListObject)
Set mListObject = Table
End Sub

On peut maintenant créer un objet de cette classe et lui passer le tableau structuré qui sera géré.

Sub Test()
Dim t As xlTable

Set t = New xlTable
t.Init Range("a1").ListObject
End Sub

Ne confondez pas la méthode Init que nous créons et la méthode évènementielle Class_Initialize qui est à notre disposition pour toute classe que nous créons. Dans le cas présent, gérer cet évènement n’a pas d’intérêt et nous laissons donc cette méthode de côté.

Je reviendrai dans un moment sur cette procédure, mais comme la classe ne nous sert toujours à rien actuellement, on va créer une propriété très intéressante et une méthode qui illustrera clairement l’intérêt de cette nouvelle classe qui constitue une couche d’abstraction pour le développeur qui va l’utiliser dans son code.

Pour des besoins que nous découvrirons plus tard, il pourrait être intéressant de mémoriser la colonne d’index ou de clé primaire. En effet, dans un tableau structuré, on a parfois (souvent ?), une colonne contenant des valeurs sans doublons permettant de pointer sur une ligne de façon univoque, par exemple avec RECHERCHEX (365 et 2021) ou INDEX-EQUIV (autres versions). C’est le cas, de manière évidemment non exhaustive d’un tableau reprenant la liste:

- des factures (les numéros de factures sont a priori uniques) ;

- des membres du personnel (n° de personnel ou ID unique) ;

- des véhicules (plaque minéralogique ou numéro de châssis ou numéro de registre) ;

- …

La fonction xlTable.Init va donc permettre de passer en argument optionnel le nom de la colonne d’index, qui sera mémorisé dans une variable du module de classe.

Option Explicit

Private mTable As ListObject
Private mIndexName As String

Sub Init(Table As ListObject, Optional IndexName As String)
Set mTable = Table
mIndexName = IndexName
End Sub

V-C - Première propriété, première méthode

V-C-1 - Création d’une propriété ListObject

Notre classe a pour vocation d’encapsuler un tableau structuré (ListObject) pour lui donner des possibilités supplémentaires qui ne sont pas nativement disponibles. Afin de ne pas devoir manipuler deux objets dans notre code, le xlTable pour « nos » méthodes et propriétés et un objet ListObject pour les méthodes et propriétés natives, il sera intéressant que notre classe expose le tableau structuré qu’elle surclasse. Une simple propriété Property Get dans le module de classe fera l’affaire.

Rappel : Property Get crée une propriété en lecture. Cela veut dire que l’on peut affecter la valeur de la propriété à une variable : MaVariable = MonObjet.MaPropriété. Sans un Property Let de même nom, la propriété est en lecture seule. C’est ce qui nous convient dans le cas présent.

Property Get ListObject() As ListObject
Set ListObject = mListObject
End Property

Grâce à cette propriété, on pourra manipuler le ListObject au travers de l’objet xlTable. Cette propriété permet donc de gérer les méthodes et propriétés que nous allons créer pour la classe xlTable, mais également toutes les méthodes et propriétés natives d’un objet ListObject. Notre classe n’est toujours pas plus utile pour l’instant, mais nous pouvons au moins faire quelque chose avec.

Bien que l’héritage n’existe pas en VBA, on peut considérer que cette propriété ListObject constitue une manière de modéliser l'héritage en VBA.

V-C-2 - Création de la méthode Clear

Avec la méthode Clear, nous allons réellement rentrer dans le vif du sujet et créer une méthode vraiment intéressante qui va illustrer la notion de couche d’abstraction.

Comprendre ce que nous mettons en place ici va véritablement vous permettre un gain considérable dans la production de code générique, et donc un gain en qualité et en temps de développement de vos applications VBA.

V-C-2-a - Rappel : Vidange d’un tableau structuré

Si vous avez lu mon tuto sur la manipulation des tableaux structurés en VBA, vous savez qu’un ListObject expose la propriété DataBodyRange qui est l’objet Range représentant la plage de données du tableau.

Pour vider le tableau, c’est-à-dire en supprimer toutes les lignes de données, il suffirait normalement de supprimer son DataBodyRange : Range("t_Contacts").ListObject.DataBodyRange.Delete

Ce n’est malheureusement pas aussi simple pour deux raisons :

- Si le tableau est vide, le DataBodyRange est Nothing et l’exécution plantera ;

- Si des lignes sont filtrées dans le tableau, seules les lignes visibles seront supprimées.

Il faut donc vérifier que le tableau n’est pas vide et le défiltrer avant de le vider.

Sub Test()
Dim t As ListObject

Set t = Range("t_Contacts").ListObject
If Not t.DataBodyRange Is Nothing Then
t.AutoFilter.ShowAllData
t.DataBodyRange.Delete
End If
End Sub

Et ce code, en tout cas le bloc If… End If, on devra le saisir pour chaque tableau que nous souhaiterons vider. Comme proposé dans le tuto de Laurent, on pourrait créer une procédure qui s’en charge :

Sub ClearTable(Table As ListObject)
If Not Table.DataBodyRange Is Nothing Then
Table.AutoFilter.ShowAllData
Table.DataBodyRange.Delete
End If
End Sub

Il suffit de stocker cette procédure dans un module TableTools à emporter dans tous nos développements et le tour est joué. Mais comme je propose ici une approche POO, je vais intégrer cette méthode dans la classe xlTable. Cette méthode n’a plus besoin d’argument puisque nous allons travailler dans la classe avec la variable privée mListObject.

Procédure du xlTable

Sub Clear()
If Not mTable.DataBodyRange Is Nothing Then
mTable.AutoFilter.ShowAllData
mTable.DataBodyRange.Delete
End If
End Sub

Maintenant, il suffit que le code appelant manipule un objet xlTable pour pouvoir le vider sans se soucier de tout le code nécessaire à cette opération.

V-D - Amélioration de la méthode Init

Notre tuto poursuit plusieurs buts, et l’un de ces buts est de permettre au développeur « de se passer du ListObject » pour utiliser xlTable qui l’encapsule et l’enrichit.

Proposer des classes personnalisées prêtes à l’emploi pour faciliter et systématiser la manipulation des tableaux structurés en VBA

Mais il reste le problème de l’initialisation du xlTable qui requiert… un ListObject (Sub Init(Table As ListObject, Optional IndexName As String). Dès lors, l’objectif de se passer du ListObject n’est pas totalement atteint. En effet, le développeur doit savoir comment retrouver le ListObject qu’il veut encapsuler.

V-D-1 - Solution procédurale

Si retrouver un ListObject dans le classeur actif est assez simple, il demande un peu plus de code lorsque le tableau se trouve dans un autre classeur ouvert. Bien sûr, on pourrait avoir deux approches, l’une pour le classeur actif qui utiliserait la référence structurée, et une pour le tableau à aller chercher dans un autre classeur. Je n’aime pas cette approche car elle requiert du développeur deux méthodes distinctes pour un même résultat, or l’objectif du tuto est d’apprendre à systématiser l’approche et à créer une architecture professionnelle.

On pourrait regrouper les deux approches au sein d’une seule procédure :

Function getTable(Name As String, Optional IndexName As String, Optional wb As Workbook) As xlTable
Dim item As xlTable
Dim ws As Worksheet
Dim t As ListObject
Dim i As Long, j As Long

If wb Is Nothing Then
Set getTable = New xlTable
getTable.Init Range(Name).ListObject, IndexName
Else
For i = 1 To wb.Worksheets.Count And getTable Is Nothing
For j = 1 To wb.Worksheets(i).ListObjects.Count And getTable Is Nothing
If UCase(wb.Worksheets(i).ListObjects(j).Name) = UCase(Name) Then
Set getTable = New xlTable
getTable.Init wb.Worksheets(i).ListObjects(j)
End If
Next
Next
End If
End Function

Cette approche peut paraître séduisante, mais elle lie le xlTools au xlTable, ce qui veut dire qu’il ne sera pas possible d’importer le xlTools sans importer aussi les classes que nous avons créées.

V-D-2 - Solution dans la classe

On comprend vite l’intérêt de placer la solution dans la classe même, en modifiant un peu la procédure xlTable.Init pour lui passer le nom du tableau et le classeur dans lequel on doit le chercher.

Sub Init(ByVal Name As String, Optional ByVal IndexName As String, Optional wb As Workbook)
Dim Item As xlTable
Dim ws As Worksheet
Dim t As ListObject
Dim i As Long, j As Long

If wb Is Nothing Then
Set mTable = Range(Name).ListObject
mIndexName = IndexName
Else
For i = 1 To wb.Worksheets.Count And mTable Is Nothing
For j = 1 To wb.Worksheets(i).ListObjects.Count And mTable Is Nothing
If UCase(wb.Worksheets(i).ListObjects(j).Name) = UCase(Name) Then
Set mTable = wb.Worksheets(i).ListObjects(j)
mIndexName = IndexName
End If
Next
Next
End If
End Sub

Avec cette fonction, le développeur n’a besoin ni du ListObject, ni de savoir comment le retrouver dans l’instance Excel. La recherche du tableau est inclue dans la méthode Init une fois pour toutes.

V-D-3 - Le Design Pattern Factory

xlTable.Init est donc très intéressant. Si la méthode est séduisante pour un tableau se trouvant dans le classeur actif, elle pose problème si le tableau se trouve dans un autre classeur que le classeur actif, car cela impose de scanner les feuilles et les tableaux des feuilles jusqu’à trouver le bon tableau. Bien sûr, à l’intérieur d’une même procédure/fonction, on pourra créer une variable pour le récupérer :

Sub Test()
Dim Source As xlTable, Target As xlTable

Set Source = getTable("t_Contacts", "ID", ThisWorkbook)
Set Target = getTable("t_Contacts", "ID", ThisWorkbook)
Debug.Print Source.Table.Parent.Name
Debug.Print Target.Table.Parent.Name
Target.Clear
End Sub

Dans le cas où le même tableau est réutilisé dans plusieurs procédures, il sera recherché à chaque fois. Il pourrait dès lors être intéressant, hors framework mais lié à celui-ci, de disposer de fonctions qui appellent un tableau spécifique mais qui le mémorise pour ne pas devoir le rechercher plus tard.

C’est ce que propose le Design Pattern Factory.

Un Design Pattern ou patron de conception sert à modéliser une opération informatique générique et récurrente en proposant The Best Practice. Google vous renseignera sur les différents Design Pattern qui existent.

Le Factory (fabrique, en anglais) est un module qui a pour mission de servir les objets dont l’application a besoin. Il s’intègre naturellement dans une architecture professionnelle puisqu’il permet de trouver rapidement le code qui produit un objet spécifique de l’application. En VBA, ce Factory se matérialisera par un module nommé Factory et auquel on fera appel pour manipuler les objets applicatifs.

Function TableContacts() As xlTable
Static Item As xlTable

If Item Is Nothing Then
Set Item = New xlTable
Item.Init "t_Contacts", "ID", ThisWorkbook
End If
Set TableContacts = Item
End Function

Function TableContactsArchive() As xlTable
Static Item As xlTable

If Item Is Nothing Then
Set Item = New xlTable
Item.Init "t_Contacts", "ID", ActiveWorkbook
End If
Set TableContactsArchive = Item
End Function

Ainsi, lors du premier appel, le Factory crée l’objet, l’initialise et le mémorise dans la variable Static Item. Lors d’un appel ultérieur, l’objet est donc directement à disposition sans avoir besoin de le rechercher. La variable reste valorisée tant que le code n’est pas arrêté.

Puisque les fonctions renvoient dans ce cas-ci des xlTable, on peut directement utiliser Factory.TableContacts comme un xlTable.

vb

Sub Test()
Debug.Print Factory.TableContacts.Table.Parent.Parent.Name
Debug.Print Factory.TableContactsArchive.Table.Parent.Parent.Name
Factory.TableContactsArchive.Clear
End Sub

Grâce à cela, on évite les variables publiques et les déclarations multiples d’objet un peu partout dans le code. Si pour une raison quelconque le nom du tableau venait à changer, il suffirait de modifier UNE ligne de code dans la fonction souhaitée du Factory.

Ici, le Factory est illustré uniquement avec des fonctions qui servent des xlTable, mais il regroupera toutes les fonctions qui servent des objets de l’application, par exemple une feuille d’un autre classeur, un classeur, etc, dans l’esprit du Design Patternen.

V-E - Autres méthodes et propriétés de xlTable

Dans le tuto relatif aux tableaux structurés en VBA, nous avons vu que nous pouvons déjà réaliser beaucoup de choses chouettes en utilisant les références structurées, que ce soit sur le tableau ou une de ses colonnes.

Ainsi, Range("t_Contacts").Address renvoie l’adresse de la zone de données du tableau, et Application.CountA(Range("t_Contacts[Nom]") renvoie le nombre de noms saisis dans la colonne des noms de famille. Tout cela pourrait être proposé par le xlTable ou ses objets enfants.

V-E-1 - Fonction IsEmpty

On a vu que lorsque le tableau est vide, Range("t_Contacts").Rows.Count renvoyait 1 et pas 0. Nous ne pouvons donc pas tester que le tableau est vide par cette méthode et nous devons passer par le listObject, qui n’expose pas non plus une propriété similaire. Nous sommes donc obligés, pour tester que le tableau est vide, de passer soit par ListObject.DataBodyRange Is Nothing, soit par ListObject.ListRows.Count = 0. Ce n’est guère pratique.

Puisque nous créons une classe dont l’ambition est de se passer du ListObject, du moins pour le développeur applicatif, nous allons lui créer une propriété IsEmpty.

Propriété du xlTable

Property Get IsEmpty() As Boolean
IsEmpty = mListObject.ListRows.Count = 0
End Property

Maintenant que nous avons bien compris le principe et l’utilité de cette classe, nous allons pouvoir nous atteler à la création de propriétés très spécifiques qui vont réellement booster notre production de code. Mais pour pouvoir enrichir notre xlTable, il faut d’abord nous pencher sur un autre objet que nous allons créer, le xlRow.

En annexe, je vous proposerai toutes les propriétés et méthodes actuellement disponibles dans « mes » classes xlTable, xlRow et xlColumn, et vous pourrez enrichir ces objets avec vos propres idées ou modifier celles qui sont proposées.

V-E-2 - Méthode AddColumns

Il pourra être intéressant d’ajouter des colonnes à notre tableau. Sans notre classe, cela passe par la manipulation de la collection ListColumns du ListObject : ListObject.ListColumns.Add.

Le xlTable va proposer une méthode pour réaliser l’opération. On passera à AddColumns un tableau reprenant les noms des tables à ajouter et il les ajoutera à la droite du tableau.

Sub AddColumns(Columns)
Dim i As Long
Dim c As ListColumn

For i = LBound(Columns) To UBound(Columns)
Set c = mListObject.ListColumns.Add()
c.Name = Columns(i)
Next i
End Sub

V-F - Conclusions de cette approche

Avec xlTable.Init et xlTable.Clear, on touche au cœur même de l’utilité de la classe xlTable et de l’abstraction qu’elle permet en développement.

Le programmeur n’a plus à instancier des ListObject, il lui suffit d’embarquer la classe xlTable dans ses projets pour bénéficier avec un seul objet des possibilités intrinsèques du ListObject grâce à la propriété xlTable.ListObject, mais également de toutes les méthodes et propriétés de xlTable telle que la méthode xlTable.Clear. La déclaration Dim t As ListObject devient obsolète au profit de Dim t As xlTable, puisque cette classe offre toutes les possibilités du ListObject et y ajoute toutes les possibilités de manipulation que vous créerez dans votre classe personnalisée.

C’est uniquement notre imagination et notre capacité à coder qui va nous limiter dans l’enrichissement de cette classe xlTable en méthodes et propriétés.

Bien sûr, ce n’est pas tout à fait vrai. Si l’on veut manipuler une ligne ou une colonne d’un tableau, il nous faut encore le ListObject. Mais nous allons créer deux nouveaux objets, xlRow et xlColumn qui vont encapsuler les objets listRow et ListColumn pour compléter notre arsenal et nous détacher totalement des ListXXX.

VI - L’objet xlRow

Le ListObject expose la collection ListRows qui regroupe tous les objets ListRow de la table, autrement dit toutes les lignes de données de la table.

Ne confondez pas ListRow et plage d’une ligne du tableau. Tout comme un ListObject n’est pas une plage, un ListRow n’est pas une plage, mais une « surclasse » qui englobe une plage particulière en l’enrichissant de propriétés et méthodes spécifiques

VI-A - Rappels sur le ListRow

Un objet ListRow expose une ligne spécifique de la plage de données du tableau. L’explorateur d’objets reprend 3 propriétés et une méthode intéressantes, en plus de deux propriétés que nous n’utilisons pas :

- La méthode Delete qui permet de supprimer la ligne ;

- La propriété Index qui mentionne la position de la ligne dans le tableau. Cet index commence à 1 pour la première ligne. On comprendra ici qu’un tri sur le tableau modifie bien entendu l’index des lignes ;

- La propriété Parent qui pointe vers le ListObject qui contient la ligne ;

- La propriété Range qui pointe vers la plage de données.

C’est « pauvre », comme fonctionnalités. On pourrait notamment vouloir récupérer ou valoriser rapidement la valeur d’une cellule du Range du ListRow, avec un code qui ressemblerait un peu à ceci :

FirstName = ListRow.Value("Prénom")
ListRow.Value("Prénom") = "Pierre"

Pour réaliser cela avec le ListRow, on va devoir remonter sur le parent (le tableau), aller chercher l’index de la colonne Prénom pour récupérer la valeur qui se trouve à la position Index de la plage de données.

Du côté du code, ça donne ceci :

Debug.Print r.Range(r.Parent.ListColumns("Prénom").Index).Value
r.Range(r.Parent.ListColumns("Prénom").Index).Value = "Pierre"

Ce n’est pas bien terrible, mais c’est tout de même plus lourd que le code synthétique vu plus haut, et surtout, ça impose de connaître le ListRow et le ListColumn, alors que l’idée développée depuis le début du tuto, c’est de permettre au programmeur de faire abstraction le plus possible des ListObject, ListRow et ListColumn.

VI-B - Création de l’objet xlRow

VI-B-1 - Création de la classe

On va donc créer une classe xlRow, l’initialiser avec le ListRow souhaité et créer les propriétés et méthodes qui nous intéressent. Pour créer la classe, on procède comme pour le xlTable, en ajoutant un module de classe au projet et en le nommant xlRow.

VI-B-2 - Initialisation et propriété Value en lecture-écriture

VI-B-2-a - initialisation

Après avoir créé le module de classe, on initialisera l’objet et on lui passera aussi son parent, l’objet xlTable.

Code du xlRow

Option Explicit

Private mListRow As ListRow
Private mParent As xlTable

Sub Init(ListRow As ListRow, Parent As xlTable)
Set mListRow = Row
Set mParent = Parent
End Sub

Bien sûr, comme pour le xlTable, on mettra à disposition du programmeur le ListRow correspondant, et on créera aussi la propriété permettant de renvoyer le parent.

Property Get ListRow() As ListRow
Set ListRow = mListRow
End Property

Property Get Parent() As xlTable
Set Parent = mParent
End Property

On avance ici un peu plus rapidement, puisque grosso modo, ces codes ont été vus plus haut lors de la création du xlTable.

Ici, on initialise le xlRow avec l’objet ListRow. Ce n’est pas grave car le développeur qui utilisera la classe ne passera pas par le Init. Comme nous allons le voir, c’est le xlTable qui servira le xlRow souhaité.

VI-B-2-b - Récupération et modification de la valeur d’une cellule de la ligne.

On a vu plus haut comment manipuler une valeur de cellule du ListRow. On va simplement transposer ces lignes dans les propriétés Get (lecture) et Let (Ecriture) :

Property Get Value(Name As String)
Value = mListRow.Range(mListRow.Parent.ListColumns(Name).Index).Value
End Property

Property Let Value(Name As String, Value)
mListRow.Range(mListRow.Parent.ListColumns(Name).Index).Value = Value
End Property

On utilise ici la variable mListRow pour lire ou écrire une valeur dans une cellule particulière du Range. Ici, la propriété Value n’est pas typée puisque l’objet est générique et doit donc pouvoir manipuler un String, un Long, un Double, un boolean, une date, voire une valeur d’erreur, c’est-à-dire n’importe quelle valeur que l’on peut stocker dans une cellule.

Cette propriété en lecture/écriture sera celle qui sera probablement principalement utilisée.

VI-C - Instanciation d’un objet xlRow

Si l’on observe d’abord le ListObject et le ListRow, on remarque qu’un ListRow est instancié au travers du ListObject qui le contient, soit pour récupérer une ligne existante, soit pour en créer une nouvelle au travers de la fonction Add du ListObject qui renvoie un objet de type ListRow :

Nous allons procéder de la même manière avec nos objets. C’est le xlTable qui va permettre d’instancier un xlRow.

VI-C-1 - Création d’une ligne dans le tableau

Nous allons d’abord permettre au xlTable de créer une nouvelle ligne et de renvoyer le xlRow correspondant à cette nouvelle ligne en plaçant le code suivant dans le module du xlTable.

La fonction permet de positionner la nouvelle ligne dans le tableau. Si Position = 0, la ligne est créée en fin de tableau. Ainsi, nous pouvons omettre la position pour ajouter une ligne en bas de tableau, le caractère Optional de l’argument le valorisant à 0 s’il n’est pas passé à la fonction.

La fonction NewRow de xlTable

Function NewRow(Optional Position As Long) As xlRow
Set NewRow = New xlRow
If Position = 0 Then
NewRow.Init mTable.ListRows.Add(), Me
Else
NewRow.Init mTable.ListRows.Add(Position), Me
End If
End Function

Si une ligne de total existe, ce n’est pas grave, puisque c’est le ListObject qui gère et qui insère la ligne avant la ligne de total.

On va maintenant utiliser tout cela pour insérer une ligne en deuxième position dans notre tableau des contacts et valoriser cette ligne avec les données de notre nouveau contact :

Sub Test()
Dim r As xlRow

Set r = Factory.TableContacts.NewRow(2)
With r
.Value("ID") = 45
.Value("Nom") = "Cover"
.Value("Prénom") = "Harry"
End With
End Sub

Je pense qu’à ce stade, le code se passe presque de commentaires. Nous utilisons le Factory pour manipuler le tableau des contacts. Le code est alors assez concis et nous remarquons à nouveau que :

- Le ListObject et le ListRow ne sont pas manipulés par le code puisque tout ce qui les concerne se passe dans nos classes personnalisées ;

- A aucun moment il n’est fait mention de l’emplacement du tableau, puisque c’est le Factory qui gère le ListObject.

Attention que la position est déterminée par rapport au tableau et pas par rapport à la feuille qui le contient.

N’hésitez pas à avancer en pas à pas pour bien comprendre les allers et venues entre le code appelant (ici la procédure Test), le Factory et les méthodes ou propriétés de nos objets.

On remarque que ce code, et celui du xlTable et du xlRow n’utilisent à aucun moment la position des données dans les feuilles, pas plus qu’il ne se préoccupe des feuilles sur lesquelles sont posés les tableaux structurés. C’est une avancée majeure dans la production de code que nous devons aux tableaux structurés.

VI-C-2 - Récupération d’une ligne existante par sa position

Pour pouvoir récupérer une ligne par sa position, on créera une fonction dans le xlTable dont l’argument sera simplement la position de la ligne dans le tableau.

Function RowByPosition(ByVal Position As Long) As xlRow
Set RowByPosition = New xlRow
RowByPosition.Init mListObject.ListRows(Position), Me
End Function

Ici aussi, le code est concis et compréhensible sans commentaires. On l’utilisera par exemple avec le code suivant dans un module standard :

Sub Test()
Dim r As xlRow

Set r = Factory.TableContacts.RowByPosition(2)
With r
Debug.Print r.Value("id") & ": " & r.Value("Prénom") & " " & r.Value("Nom")
End With
End Sub

VI-C-3 - Récupération d’une ligne existante par la valeur de son index

L’idée ici est de retrouver une ligne par la valeur de l’index ou clé primaire, c’est-à-dire la valeur trouvée dans la colonne renseignée comme index lors de l’initialisation du xlTable. Cette façon de faire permettra d’éviter de boucler sur toutes les lignes car nous allons utiliser les possibilités que Excel met à notre disposition.

Excel permet cela grâce à la fonction EQUIV.

On va simplement implémenter cela en VBA grâce à Application.Match sur la colonne renseignée comme étant l’index. On créera une propriété xlTable.RowByIndex

Propriété RowByIndex dans xlTable

Function RowByIndex(IndexValue) As xlRow
Dim Pos As Variant

If mIndexName <> "" Then
Pos = Application.Match(IndexValue, mListObject.ListColumns(mIndexName).DataBodyRange, 0)
If Not IsError(Pos) Then Set RowByIndex = RowByPosition(Pos)
End If
End Function

Le code appelant pourrait être celui-ci :

Code utilisant la propriété RowByIndex

Sub Test()
Dim r As xlRow

Set r = Factory.TableContacts.RowByIndex(45)
With r
Debug.Print r.Value("id") & ": " & r.Value("Prénom") & " " & r.Value("Nom")
End With
End Sub

VI-C-4 - Récupération d’une ligne existante par recherche de valeurs

VI-D - Quelques autres propriétés et méthodes de l’objet xlRow

On peut enrichir l’objet en lui créant Les méthodes et propriétés qui nous semblent intéressantes.

VI-D-1 - Supprimer la ligne

Sub Delete()
If Not mListRow Is Nothing Then mListRow.Delete
End Sub

Attention qu’après la suppression d’un xlRow, la ligne « physique » est supprimée du tableau, mais l’objet xlRow n’est pas détruit ni passé à Nothing. Son utilisation va systématiquement planter, comme le fait le ListRow du modèle-objet Excel.

Nous verrons dans l’annexe que le xlTable peut aussi proposer une méthode pour supprimer une ligne, nous dispensant ainsi d’instancier un objet xlRow aux seules fins de supprimer une ligne du tableau.

VI-D-2 - Récupérer la position de la ligne

Property Get Position() As Long
Position = mListRow.Index
End Property

On notera que le tri des données modifie les positions des xlRow.

VI-D-3 - Duplication de la ligne

On peut aussi proposer une propriété qui duplique la ligne. Tant qu’à faire, la propriété renverra la nouvelle ligne créée par la duplication.

Méthode Duplicate dans xlRow

Function Duplicate() As xlRow
Set Duplicate = New xlRow
Duplicate.Init mListRow.Parent.ListRows.Add(mListRow.Index + 1), mParent
Duplicate.ListRow.Range.Value = mListRow.Range.Value
End Function

Test de duplication dans un module standard

Sub Test()
Dim t As xlTable
Dim r As xlRow, r1 As xlRow

Set t = New xlTable
t.Init Range("t_Contacts").ListObject, "ID"
Set r = t.RowByPosition(2)
Debug.Print "Adresse ligne: " & r.ListRow.Range.Address
Set r1 = r.Duplicate()
Debug.Print "Addresse ligne dupliquée: " & r1.ListRow.Range.Address
End Sub

Ici, j’ai utilisé Duplicate comme une propriété pour récupérer la nouvelle ligne, mais on pourrait l’utiliser comme une méthode si l’on n’a pas besoin de manipuler la nouvelle ligne par la suite : r.Duplicate

On notera donc qu’une classe peut renvoyer un objet de son type, puisqu’ici, un xlRow renvoie un xlRow.

VI-D-4 - Renvoyer le Range et l’adresse de la ligne

A nouveau, pour éviter de passer par xlRow.ListRow, xlRow pourrait servir la plage de données.

Property Get Range() As Range
Set Range = mListRow.Range
End Property

Property Get Address() As String
Address = mListRow.Range.Address
End Property

VI-D-5 - Tester qu’une ligne est vide

Sur base du Range du mListRow encapsulé dans le xlRow, on peut tester qu’une ligne est vide

Function IsEmpty() As Boolean
IsEmpty = Application.CountA(mListRow.Range) = 0
End Function

VI-D-6 - Renvoyer une cellule particulière de la ligne

On peut renvoyer une cellule particulière de la ligne, sur base de sa position ou du nom de sa colonne. Passer la énième cellule est simple, mais renvoyer une cellule par le nom de sa colonne demande que l’on remonte au parent pour aller chercher l’index de la colonne choisie.

Property Get CellByPosition(Position As Long) As Range
Set CellByPosition = mListRow.Range(Position)
End Property

Property Get CellByName(Name As String) As Range
Set CellByName = mListRow.Range(mListRow.Parent.ListColumns(Name).Index)
End Property

VI-E - Vider une ligne

Sub ClearContents()
mListRow.Range.ClearContents
End Sub

VI-F - Conclusions sur notre classe xlRow

En gros, notre classe est construite. On pourra l’enrichir avec d’autres méthodes et surtout d’autres propriétés. Certains enrichissements seront abordés dans l’annexe. Ces ajouts auront pour but de dispenser le programmeur de passer par la propriété ListRow du xlRow.

Il vous appartiendra d’y ajouter vos propres méthodes, propriétés et fonctions selon vos besoins récurrents.

Beaucoup de ces propriétés ne seront que des passe-plats d’une ligne ou deux vers les propriétés du ListRow ou d’objets servis par le ListRow. D’autres demandent un peu plus de développement. L’idée ici est de faciliter au maximum le travail de codage en permettant au développeur qui utilise la classe d’aller à l’essentiel.

VII - L’objet xlColumn

L’objet xlColumn sera développé de la même manière que le xlRow.

VII-A - Propriétés et méthodes de l’objet ListColumn

L’explorateur d’objets nous expose les propriétés et méthodes d’un ListColumn.

VII-B - Création de la classe xlColumn

VII-B-1 - Création de la classe

C’est la troisième fois que l’on crée une classe, donc on va plus vite, maintenant :

- Créer du module de classe ;

- Nommer le module xlColumn ;

VII-B-2 - Initialisation et propriétés Parent et ListColumn

Ici aussi, on va plus vite, puisque c’est presque du copier-coller de xlRow :

- Créer la méthode Init ;

- Renvoyer le parent xlTable et le ListColumn associé.

Option Explicit

Private mParent As xlTable
Private mListColumn As ListColumn

Sub Init(ListColumn As ListColumn, Parent As xlTable)
Set mListColumn = ListColumn
Set Parent = Parent
End Sub

Property Get Parent() As xlTable
Set Parent = mParent
End Property

Property Get ListColumn() As ListColumn
Set ListColumn = mListColumn
End Property

On ajoutera les propriétés Name et Index qui seront de simples passe-plats vers les propriétés éponymes du ListColumn.

Property Get Name() As String
Name = mListColumn.Name
End Property

Property Get Index() As Long
Index = mListColumn.Index
End Property

VII-C - Instanciation

Comme pour le xlRow, c’est le parent xlTable qui va servir le xlColumn voulu, que l’on créera par son nom ou son index, c’est-à-dire sa position dans le tableau.

On remarque que la collection ListObject.ListColumns sert un ListColumn par son index, qui requiert indifféremment un Long (la position de la colonne) ou un String (son nom). On permettra la même chose à l’objet xlTable.

La propriété xlTable.Column, qui renvoie le xlColumn souhaité, sera donc la suivante :

Function Column(Index) As xlColumn
Set Column = New xlColumn
Column.Init mListObject.ListColumns(Index), Me
End Function

On l’utilise pour créer l’objet xlColumn que l’on peut manipuler. Le code suivant illustre les deux manières d’instancier le xlColumn, sur base de son index ou de son nom, et la récupération des propriétés Index et Name.

VII-D - Quelques propriétés et méthodes du xlColumn

Comme pour le xlRow, on envisage quelques propriétés du xlColumn qui vont simplifier la tâche du développeur. Pour certaines, on ira un peu plus dans l’abstraction, notamment pour la cellule de total de la colonne.

VII-D-1 - Propriété isEmpty

Le code se passe de commentaires. Il permet simplement d’éviter au développeur de devoir passer par le ListColumn

Property Get IsEmpty() As Boolean
IsEmpty = Application.CountA(mListColumn.DataBodyRange) = 0
End Property

VII-D-2 - Propriétés Liées à la ligne de total

L’explorateur d’objets nous renseigne la propriété ListColumn.Total qui pointe vers la cellule de la ligne de total pour la colonne considérée. On va manipuler ce Range dans les propriétés suivantes.

VII-D-2-a - Propriété TotalValue

Property Get TotalValue()
TotalValue = mListColumn.Total.Value
End Property

Lorsque la ligne de total est présente, TotalValue renvoie la valeur de la cellule de total de la colonne.

Par contre, si la ligne de total n’est pas présente, elle est Nothing et donc, le code renvoie une erreur. Pour l’instant, on va laisser la laisser filer, même si le message d’erreur n’est pas très explicite. Nous verrons plus loin comment gérer efficacement l’erreur qui survient.

On pourrait gérer l’erreur au niveau du code appelant, toujours avec ce message sibyllin.

Dans le chapitre sur la gestion des erreurs, nous verrons comment la classe peut renseigner l’erreur de façon plus explicite.

On pourrait aussi gérer le problème à l’intérieur de la classe en testant l’existence de la ligne de total. Par défaut, une variable As Variant est vide (IsEmpty = True), et l’on peut donc avoir le code suivant dans la classe :

La fonction TotalValue dans le xlColumn

Property Get TotalValue()
If Not mListColumn.Total Is Nothing Then
TotalValue = mListColumn.Total.Value
Else
TotalValue = Null
End If
End Property

Le code appelant peut alors gérer la valeur retournée par la propriété :

Gestion de la ligne de total manquante dans le code appelant

Sub Test()
Dim c As xlColumn

On Error Resume Next
Set c = Factory.TableContacts.Column(3)
If Not IsNull(c.TotalValue) Then
MsgBox c.TotalValue
Else
MsgBox "La ligne de total est absente"
End If
End Sub

Je rappelle ici que cette classe ne peut pas communiquer avec l’utilisateur, mais doit éventuellement prévenir le code appelant sur la survenance de l’erreur. On ne pourra donc pas avoir ici un msgbox dans le code de la classe.

VII-D-2-b - Propriété TotalFormula

On peut également récupérer la formule de la ligne de total. A ce niveau de VBA pour Excel, vous savez probablement que Range.Formula se décline de différentes manières (Formula, FormulaLocal, FormulaR1C1, FormulaR1C1Local, …). Perso, pour ne pas multiplier les TotalFormula, je propose de travailler avec un énumérateur qui sera placé en début du module xlColumn.

Public Enum eFormulaType
eFormulaTypeNone = 0
eFormulaTypeLocal = 1
eFormulaTypeR1C1 = 2
End Enum

Pour l’instant, les valeurs se suivent en incrément de 1, il n’est donc pas nécessaire de valoriser les constantes d’énumération. J’ai cependant pris l’habitude de le faire.

Du coup, on peut utiliser ces constantes dans le code, et il sera mis à disposition du code appelant.

TotalFormula dans xlColumn avec l’utilisation des constantes d’énumération

Property Get TotalFormula(Optional FormulaType As eFormulaType) As String
If Not mListColumn.Total Is Nothing Then
Select Case FormulaType
Case 0
TotalFormula = mListColumn.Total.Formula
Case 1
TotalFormula = mListColumn.Total.FormulaLocal
Case 2
TotalFormula = mListColumn.Total.FormulaR1C1
Case 3
TotalFormula = mListColumn.Total.FormulaR1C1Local
End Select
End If
End Property

On remarque que la constante 3 n’existe pas. Elle sera le résultat de eFormulaLocal+eFormulaR1C1. Voici une utilisation possible de cette propriété. Pour l’exemple, j’ai utilisé les références classiques au lieu des structurées pour illustrer le choix R1C1.

Avec des constantes d’énumération de ce type, on fera attention à ne les valoriser qu’avec des puissances de 2, pour pouvoir déterminer la combinaison choisie.

Ici, la propriété est en lecture, mais on pourrait la basculer en lecture/écriture pour permettre de modifier la formule

VII-D-3 - Fonction Duplicate

Comme pour un xlRow, on pourrait vouloir dupliquer une colonne.

Function Duplicate() As xlColumn
Set Duplicate = New xlColumn
Duplicate.Init mListColumn.Parent.ListColumns.Add(mListColumn.Index + 1), mParent
Duplicate.ListColumn.DataBodyRange.Value = mListColumn.DataBodyRange.Value
End Function

VII-D-4 - Méthode ClearContents

Comme pour le xlRow, la méthode ClearContents vide les cellules de la colonne.

Sub ClearContents()
mListColumn.DataBodyRange.ClearContents
End Sub

VII-D-5 - Méthode Delete

Sub Delete()
mListColumn.Delete
End Sub

Cette méthode n’est qu’un passe-plats sans autre intérêt que de se passer du ListColumn. Pourtant, on va voir ici qu’il pourrait être intéressant de demander la confirmation de la suppression, par exemple si la colonne n’est pas vide. On serait tenté de réaliser ceci :

Sub Delete()
Dim Answer As VbMsgBoxResult: Answer = vbYes
If Not IsEmpty Then Answer = MsgBox("La colonne n'est pas vide. Voulez-vous la supprimer?", vbYesNo)
If Answer = vbYes Then mListColumn.DataBodyRange.Delete
End Sub

A ne JAMAIS FAIRE ! Une classe comme celle-là ne peut pas dialoguer avec l’utilisateur et ne peut « dialoguer » qu’avec le code appelant.

Nous avons deux solutions :

- Laisser filer et supprimer la colonne, vide ou pas ;

- Tester que la colonne est vide au niveau du code appelant ;

La première solution a été vue. Pour la seconde, nous pouvons tester que la colonne est vide et agir en conséquence, car c’est en fait au code appelant de déterminer s’il doit supprimer une colonne non vide:

Sub Test()
Dim c As xlColumn
Dim Answer As VbMsgBoxResult: Answer = vbYes

Set c = Factory.TableContacts.Column(4)
If Not c.IsEmpty Then Answer = MsgBox("La colonne n'est pas vide. Voulez-vous la supprimer?", vbYesNo)
If Answer = vbYes Then c.Delete
End Sub

VII-E - Conclusions sur notre classe xlColumn

Comme pour le xlRow, nous avons enrichi l’objet ListColumn en l’encapsulant dans une classe xlColumn, et la seule limite sera nos besoins et notre imagination.

VIII - Nos classes personnalisées dans l’explorateur d’objets

L’explorateur d’objets permet d’afficher rapidement les propriétés et méthodes d’un objet selectionné. J’en parle un peu ici car c’est un outil manifestement trop méconnu mais qui est intéressant pour qui est curieux de savoir ce qu’il manipule. Cet explorateur est accessible via le menu Affichage du VBE ou par le raccourci CTRL+F2.

Dans l’illustration ci-dessus, on voit que l’on peut récupérer les classes du projet (le classeur xlTable), ainsi que ceux d’une classe particulière (la classe xlColumn), et les caractéristiques d’une méthode ou propriété particulière (ici, xlColumn.IsEmpty) qui permet de voir qu’elle est en lecture seule et qu’elle renvoie un Booléen.

L’explorateur d’objets permet donc de découvrir des méthodes et propriétés insoupçonnées de certains objets que nous manipulons « sans les connaître à fond ».

L’explorateur d’objets à mon estime sous-exploité, voire méconnu des développeurs VBA. C’est pourtant un outil très intéressant pour appréhender rapidement les méthodes et propriétés de nos objets, mais aussi pour voir ce qu’une bibliothèque particulière expose comme outils pour nous aider à développer rapidement.

IX - La gestion des erreurs au sein de nos classes personnalisées

Il faudrait un tuto spécifique sur la gestion des erreurs en VBA. Je ne vais donc pas détailler ici comment gérer les erreurs, mais il me semble important d’aborder le comportement de nos classes lorsqu’une erreur survient.

IX-A - Préambule

IX-A-1 - Code VBA protégé contre l’affichage

Lorsque le code VBA est protégé contre la lecture, le VBE (Visual Basic Editor) ne s’arrête pas sur la ligne en erreur en la surlignant en jaune. Dès lors,

- soit l’erreur est (bien) gérée et l’exécution continue sur la ligne stipulée par le On Error ;

- soit l’exécution est stoppée et la main est rendue à Excel, éventuellement dans un état instable.

Lorsque le code est arrêté,

les variables publiques sont réinitialisées ;

- certaines propriétés d’application sont basculées correctement
(ScreenUpdating, DisplayAlerts, …) ;

- certaines propriétés d’application restent « en l’état »
(Calculation, EnableEvents, …).

Normalement, en « production », le code devrait toujours être protégé contre l’affichage pour empêcher d’atterrir dans le code VBA en cas d’erreur non gérée. Du coup, il importe que les erreurs soient correctement prises en charge par le gestionnaire d’erreur.

IX-A-2 - Code VBA accessible

Lorsque le code est accessible, le VBE (Visual Basic Editor) offre trois options de comportement lorsqu’une erreur d’exécution survient :

- Il stoppe sur la ligne d’erreur, que l’erreur soit gérée ou non ;

- Il stoppe uniquement sur les erreurs dans les classes, que l’erreur soit gérée ou non ;

- Il stoppe uniquement sur les erreurs non gérées.

Normalement, on ne devrait pouvoir accéder au code qu’en mode « Développement » et donc il est nécessaire de protéger le code contre l’affichage avec un mot de passe.

La troisième option permet de simuler le comportement de l’application « comme si elle était en prod ». Cela signifie qu’il ne devrait normalement jamais y avoir d’erreur non gérée lorsque l’application est prête à être mise en production.

IX-A-3 - S’assurer que les erreurs sont toujours gérées

On devrait donc s’assurer que toutes les erreurs seront gérées par le code. Pour cela, il faut comprendre ce qui se passe lorsqu’une erreur d’exécution survient :

- Le thread (processus) est arrêté ;

- La main passe au processeur de gestion d’erreur ;

- Le processeur d’erreur parcourt la pile des appels jusqu’à trouver un gestionnaire d’erreur ;

- Si le processeur trouve un gestionnaire d’erreur, il relance le thread en branchant l’exécution sur l’étiquette (la ligne) fournie par le gestionnaire d’erreur (Resume, Resume Next, Goto Etiquette) ;

- S’il n’y a pas de gestionnaire d’erreur dans la pile d’appels, soit le code est accessible et le gestionnaire d’erreur surligne la ligne en erreur pour permettre de stopper le coder ou de brancher l’exécution manuellement sur une ligne, soit le code n’est pas accessible et la main est rendue à Excel « en l’état », c’est-à-dire dans un état potentiellement instable.

IX-A-4 - La pile des appels

La pile des appels reprend la succession et l’imbrication des appels de code VBA. Elle fonctionne en LIFO (Last In, First Out), un peu comme une pile d’assiettes. Lorsque l’on empile des assiettes puis qu’on les dépile une par une, c’est la dernière posée sur la pile qui est la première à être ôtée de cette pile (Sans cela => Vaisselle cassée, c’est la fes***, vaisselle foutue, panpan cu***)

Pour les appels de procédures, fonctions et propriétés, c’est la même chose. Chaque appel d’une fonction (au sens large du terme) ajoute un appel sur la pile, de sorte que l’on peut visualiser à tout moment le chemin parcouru depuis le premier appel.

Voici une illustration du fonctionnement de la pile des appels. Cette pile est affichable par le menu, ou par CTRL+L. Perso, je l’ai placée sur une barre d’outils avec quelques autres outils intéressants.

On comprend donc que, dans une bonne architecture de code, la procédure qui est le plus bas sur la pile est celle qui a été démarrée par Excel. Il s’agira soit d’une procédure évènementielle, soit d’une procédure lancée par un bouton, ce qui en fait de facto une procédure évènementielle.

Du coup, toute procédure susceptible d’être démarrée de l’interface d’Excel devrait contenir une gestion d’erreur, de manière à ce qu’il y ait toujours une gestion d’erreur dans la pile. A priori, sauf pour une gestion d’erreur contenue totalement à l’intérieur d’une procédure, cette gestion d’erreur devrait être la seule de la pile, en tout cas dans une bonne architecture de code.

IX-B - La programmation par l’erreur

Personnellement, je ne programme normalement pas par l’erreur Si je peux éviter la survenance d’une erreur qu’il faudra gérer par la suite, je le fais.

IX-B-1 - Exemples de programmation par l’erreur

Le xlTable expose une fonction qui ajoute une ligne dans le tableau, soit en fin de tableau, soit à la position souhaitée.

La fonction NewRow dans le xlTable

Function NewRow(Optional Position As Long) As xlRow
Set NewRow = New xlRow
If Position = 0 Then
NewRow.Init mListObject.ListRows.Add(), Me
Else
NewRow.Init mListObject.ListRows.Add(Position), Me
End If
End Function

Un problème peut survenir lorsque la position renseignée n’est pas valide, ou pour une autre raison :

- Valeur négative ;

- Valeur au-delà de la dernière ligne du tableau ;

- Feuille verrouillée.

On remarque que les positions non valides renvoient un message peu compréhensible à l’utilisateur et stoppent l’exécution du code.

Si le code n’est pas accessible, l’exécution s’arrête et la main est rendue à Excel qui affiche le message d’erreur, souvent peu explicite.

La « programmation par l’erreur » consiste à ne pas tester l’argument et à « gérer l’erreur », souvent à coup de Resume next qui dans les faits, ne gère rien du tout.

Dans l’exemple suivant, l’erreur n’est en fait pas gérée, le VBE se contenant d’exécuter chaque ligne en ne tenant pas compte de l’erreur.

Function NewRow(Optional Position As Long) As xlRow
On Error Resume Next
Set NewRow = New xlRow
If Position = 0 Then
NewRow.Init mListObject.ListRows.Add(), Me
Else
NewRow.Init mListObject.ListRows.Add(Position), Me
End If
End Function

Dès lors, la ligne factory.TableContacts.NewRow 19 ne créera pas de ligne mais ne renverra pas d’erreur.

IX-C - Prévoir les problèmes et générer une erreur dans nos classes.

IX-C-1 - Générer une erreur en VBA

Pour générer une erreur, on utilise la méthode Raise de l’objet Err : Err.Raise Numéro, Source, Description. Cette ligne va générer une erreur et forcer le processeur d’erreur à rechercher une gestion d’erreur dans la pile des appels. A charge pour le code appelant de gérer les erreurs, bien entendu.

L’instruction Err.Raise ne requiert que le numéro. La source et la description sont optionnels. Vous pouvez utiliser un numéro d’erreur VBA si vous le souhaitez, mais il est alors préférable qu’il lève la même erreur que le VBA.

Le bon développeur d’une classe va donc prévoir les problèmes et y remédier en générant des erreurs avec un message explicite.

IX-C-2 - Exemple de création d’une erreur dans la fonction xlTable.NewRow.

En reprenant le code d’ajout d’une ligne, on pourrait écrire le code suivant :

Function NewRow(Optional Position As Long) As xlRow
If Position < 0 Then Err.Raise 1000, "xlTable.NewRow", "Index de ligne négatif"
If Position > mListObject.ListRows.Count Then Err.Raise 1010, "xlTable.NewRow", "Index de ligne au delà du tableau"
If mListObject.Parent.ProtectionMode Then Err.Raise 1020, "xlTable.Newrow", "Feuille protégée"
Set NewRow = New xlRow
If Position = 0 Then
NewRow.Init mListObject.ListRows.Add(), Me
Else
NewRow.Init mListObject.ListRows.Add(Position), Me
End If
End Function

On remarque que les 3 erreurs possibles ont été contrôlées et donnent lieu à la création d’une erreur spécifique avec une description compréhensible.

Si le code appelant gère les erreurs, et pour autant que le code soit masqué ou que l’option « Arrêt sur les erreurs non gérées » soit cochée, le code ne plantera pas et la gestion de l’erreur au niveau du code appelant affichera un message clair à l’utilisateur

Sub Test()
Dim r As xlRow

On Error GoTo Catch
Set r = Factory.TableContacts.NewRow(19)
r.Value("ID") = 52
r.Value("Prénom") = "Alex"
r.Value("Nom") = "Térieur"

Catch:
If Err <> 0 Then
MsgBox "Erreur " & Err.Number & vbLf & Err.Source & vbLf & Err.Description
Err.Clear
End If
End Sub

IX-C-3 - Gestion des erreurs dans nos classes

Il conviendra donc de mettre en place une prévention des erreurs pour chaque méthode ou propriété de nos classes en générant des erreurs explicites. On établira pour cela une numérotation de nos erreurs. Par exemple, les erreurs 1000 à 1999 pour le xlTable, 2000 à 2999 pour le xlRow et 3000 à 3999 pour le xlColumn.

IX-C-4 - Capturer l’erreur dans la classe pour générer une erreur personnalisée

Parfois, il pourra être intéressant de capturer l’erreur dans la classe, lorsque l’on est certain que l’erreur capturée est bien celle que l’on veut gérer.

Dans la propriété xlRow.Value, on court le risque que le nom de la colonne n’existe pas et on va donc lever une erreur.Ici, le nom de la colonne comporte un espace à droite.

On peut gérer ce problème de deux manières :

- On capture l’erreur et on génère une erreur personnalisée ;

- On teste l’existence de la colonne par une fonction privée de la classe.

IX-C-4-a - Capture de l’erreur et génération d’une erreur personnalisée

Capture de l’erreur dans xlRow

Property Let Value(Name As String, Value)
On Error Resume Next
mListRow.Range(mListRow.Parent.ListColumns(Name).Index).Value = Value
If Err = 9 Then Err.Raise 2000, "xlRow.Value", "Colonne inexistante dans la table"
End Property

La gestion de l’erreur dans le code appelant

Sub Test()
Dim r As xlRow

On Error GoTo Catch
Set r = Factory.TableContacts.NewRow()
r.Value("ID") = 52
r.Value("Prénom") = "Alex"
r.Value("Nom ") = "Térieur"

Catch:
If Err <> 0 Then
If Not r Is Nothing Then r.Delete
MsgBox "Erreur " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Erreur dans " & Err.Source
End If
End Sub

Attention que cette manière de programme ne génèrera pas d’erreur pour une erreur autre que l’erreur 9 ! Ainsi, si la feuille est protégée, c’est une erreur 1004 qui est générée (erreur « bateau » qui ne renseigne pas vraiment sur l’erreur réelle rencontrée, sauf dans la description difficilement gérable par code ^^). Elle ne sera donc pas capturée par le test If Err = 9 et la cellule ne sera pas valorisée, mais aucune alerte ne sera déclenchée.

L’illustration suivantemontre que le code, exécuté sur une feuille protégée, n’a pas généré d’erreur en dehors de la classe, mais n’a rien modifié.

Il convient donc, dans ce cas, de « relancer » l’erreur lorsqu’elle n’est pas la source d’une erreur personnalisée.

Property Let Value(Name As String, Value)
On Error GoTo Catch

mListRow.Range(mListRow.Parent.ListColumns(Name).Index).Value = Value

Catch:
Select Case Err.Number
Case 9
Err.Raise 2000, "xlRow.Value", "Colonne inexistante dans la table"
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
End Property

IX-C-5 - Test de l’existence de la colonne dans une fonction (privée ou publique)

Dans le xlRow, on ajoute une fonction qui teste l’existence de la colonne, et le test est réalisé dans la propriété Value

Property Let Value(Name As String, Value)
If Not ColumnExists(Name) Then Err.Raise 2000, "xlRow.Value", "Colonne inexistante dans la table"
mListRow.Range(mListRow.Parent.ListColumns(Name).Index).Value = Value
End Property

Function ColumnExists(Name As String) As Boolean
Dim Columns
Dim i As Long: i = 1

Columns = mListRow.Parent.HeaderRowRange.Value
Do While i <= mListRow.Range.Count And ColumnExists = False
ColumnExists = UCase(Columns(1, i)) = UCase(Name)
i = i + 1
Loop
End Function

Dans les annexes, nous verrons une fonction de valorisation d’une ligne qui reçoit les valeurs à placer sur la ligne et qui effectue les tests de faisabilité avant d’opérer les modifications.

IX-D - Conclusions de cette partie

Comme on peut le remarquer, la gestion des erreurs prend toute son importance lors de la création d’objets personnalisés, et il est important d’y attacher une attention toute particulière puisque le but des classes telles que celles vues ici est de simplifier au maximum le travail du développeur qui les utilise.

Cette gestion d’erreurs sera mise en place dans les méthodes et propriétés abordées dans les annexes.

X - Annexes

XI - Conclusions

Il est possible de simplifier la vie du développeur en développant des outils génériques qu’il suffit d’emporter dans ses projets. Cela peut être réalisé de deux façons :

- Une approche procédurale en créant des procédures ou fonctions que l’on regroupe généralement dans un module ;

- Une approche POO (Programmation orientée objet) ou l’on crée des classes, permettant au développeur de manipuler les objets que l’on a créé.

Chacun choisira la technique qu’il préfère et qui correspond le mieux à son style de programmation et à son approche du code.

Ce tutoriel vous aura permis d’approcher par la pratique la construction de classes personnalisées, et les annexes ainsi que le classeur fourni vous permettront de vous approprier les outils abordés ici, de les utiliser dans vos propres développements et de les personnaliser selon vos besoin et envies.

XII - Les fichiers

XIII - Remerciements

XIV - Annexes

XIV-A - Méthodes et propriétés de xlTable

XIV-B - Méthodes et propriétés de xlRow

XIV-C - Méthodes et propriétés de xlColum