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

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.

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.

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.

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▲

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

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

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.

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.

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.

 

Sélectionnez

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 :

 

Sélectionnez

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

Sélectionnez

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.

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.

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.

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.

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.

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.

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.

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.

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

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-a. initialisation▲

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

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

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

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

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

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.

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.

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.

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

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

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 :

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

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.

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

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

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.

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

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.

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

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

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

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 ;

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

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

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▲

 

Sélectionnez

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

Sélectionnez

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

Sélectionnez

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.

 

Sélectionnez

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

Sélectionnez

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

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

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

Sub ClearContents()
  mListColumn.DataBodyRange.ClearContents
End Sub

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

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.

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.

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.

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.

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

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.

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.

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

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.

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

Sélectionnez

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

Sélectionnez

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.

 

Sélectionnez

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

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.