Position de la première donnée dans un tableau Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Premier type de données

Dans ce nouveau volet VBA Excel, nous proposons de créer une nouvelle fonction toute particulière. Elle doit agir sur un tableau à désigner par l'utilisateur au moment de la construction de la formule. Elle doit être en mesure de déceler la position de la première date, du premier nombre ou encore du premier texte en fonction de l'indication et de la volonté de l'utilisateur, passée en second argument. Bref, elle doit s'adapter automatiquement selon la nature de l'information cherchée.



Sur l'exemple illustré par la capture, l'utilisateur travaille à partir d'un tableau fait de trous mais aussi rempli d'une certaine quantité de nombres, de textes et de dates. Et même si les dates sont des nombres spéciaux, nous allons le voir, en VBA Excel, la différence est majeure et c'est tant mieux. C'est ainsi sur la droite du tableau, en utilisant trois fois la même fonction mais avec une donnée adaptée en second argument, qu'il ressort les positions de la première date, du premier nombre et du premier texte.

Classeur Excel à télécharger
Pour développer cette nouvelle et intéressante fonction VBA Excel, nous suggérons d'appuyer l'étude sur ce tableau fait de données d'origines différentes.

• Télécharger le classeur ligne-premier.xlsm en cliquant sur ce lien,
• Cliquer droit sur le fichier réceptionné,
• En bas du menu contextuel, choisir la rubrique Propriétés,
• En bas de la boîte de dialogue qui suit, cocher la case Débloquer et valider par Ok,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,

Nous retrouvons le tableau à trous sur la gauche de la feuille entre les colonnes B et E. La fonction VBA Excel à construire doit être déployée sur la droite, dans les cellules respectives G6, G9 et G12 pour livrer la position de la première date, du premier nombre et du premier texte.

Créer la fonction VBA
Pour débuter, nous devons commencer par créer la fonction VBA avec sa signature, soit avec des paramètres dont elle a besoin pour son traitement. Il s'agit premièrement du tableau à analyser et deuxièmement d'une indication textuelle sur la nature de l'information à trouver. Le tableau à trous est reconnu sous l'intitulé tab. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet, double cliquer sur l'élément Module1,

Ainsi, nous affichons sa feuille de code au centre de l'écran.

• Dans cette feuille de code, créer la fonction posUn comme suit :

Function posUn(tbl As Variant, nature As String) As Integer

End Function

Le premier paramètre est typé comme un variant pour recevoir de la part de l'utilisateur, le tableau à analyser. Le second paramètre est typé comme un texte (As String) pour récolter l'indication textuelle sur la nature de la donnée à trouver. Enfin, nous typons la fonction elle-même comme un entier (As Integer) puisqu'elle doit retourner la position de la première information trouvée, selon la nature transmise.

Les variables
Ensuite, pour ce traitement particulier et adaptatif à la demande, nous avons besoin d'un certain nombre de variables, notamment pour prendre possession du tableau, pour connaître ses bornes aussi bien en hauteur qu'en largeur, afin de le parcourir sur les deux dimensions à la recherche de l'information demandée.

• Dans les bornes de la fonction, ajouter les déclarations et affectations suivantes :

...
Dim leTab: Dim test As Boolean: Dim ligne As Integer
Dim nbCol As Integer: Dim nbLignes As Integer
Dim colMin As Integer: Dim ligneMin As Integer
Dim chaqueLigne As Integer: Dim chaqueColonne As Integer

test = False
leTab = tbl
nbCol = UBound(leTab, 2): nbLignes = UBound(leTab, 1)
colMin = tbl.Column: ligneMin = tbl.Row
ligne = ligneMin
...

La variable leTab non typée doit prendre possession du tableau passé en paramètre. La variable ligne, typée comme un entier est celle qui doit retourner la position de la première occurrence trouvée, selon la nature de l'information demandée. Nous compterons ensuite les lignes et les colonnes du tableau dans les variables nbLignes et nbCol. En effet, l'objectif est de parcourir chaque cellule de ce dernier. C'est la raison pour laquelle nous déclarons aussi les variables colMin et ligneMin pour connaître les bornes inférieures dans les deux directions. A cet effet, nous déclarons les variables de boucles chaqueLigne et chaqueColonne, elles aussi comme des entiers, pour parcourir ces tableaux dans les deux directions des premiers indices aux derniers.

Dès lors, nous initialisons la variable booléenne test à False. Certes, nous n'en avions pas encore parlé. Tant que le traitement n'a pas commencé, nous considérons qu'aucune position n'a encore été trouvée. Nous prenons possession du tableau passé en premier paramètre (leTab = tbl). Grâce à lui et à la fonction UBound agissant sur la deuxième dimension puis sur la première, nous récoltons son nombre de colonnes et son nombre de lignes. Grâce à ses propriétés héritées Column et Row, nous prélevons l'indice de sa première colonne ainsi que l'indice de sa première ligne. Enfin, nous initialisons le compteur à retourner à l'issue sur la première ligne du tableau (ligne = ligneMin).

Parcourir chaque cellule du tableau
Pour trouver la première position correspondant à la nature de l'information souhaitée, nous devons analyser chaque cellule du tableau. Pour cela, nous devons parcourir chacune de ses lignes et pour chacune de ses lignes, nous devons parcourir chacune de ses colonnes. Grâce aux bornes que nous avons récoltées, nous allons donc pouvoir enclencher une double boucle à cet effet.

• A la suite du code VBA, créer la double boucle suivante :

...
For chaqueLigne = ligneMin To ligneMin + nbLignes - 1
For chaqueColonne = colMin To colMin + nbCol - 1

Next chaqueColonne
If (test = True) Then Exit For
ligne = ligne + 1
Next chaqueLigne
...

Pour chaque ligne (ligneMin To ligneMin + nbLignes - 1) et pour chaque colonne de chaque ligne (colMin To colMin + nbCol - 1), donc pour chaque cellule, nous engageons le traitement d'analyse. A chaque passage sur une nouvelle ligne, si la variable booléenne a été basculée à True (If (test = True) Then Exit For), nous mettons fin à la double boucle puisque la première position a été trouvée. Ce traitement consistant à basculer ce booléen reste à entreprendre dans les bornes de cette double boucle. Nous n'oublions pas d'incrémenter la variable ligne à chaque passage, tant que la première position n'a pas encore été trouvée, pour suivre la progression de l'analyse.

Le type de données
A chaque passage dans cette double boucle, donc pour chaque cellule du tableau, nous devons analyser le type de la valeur portée. S'il correspond à l'information passée en second paramètre de la fonction, nous devons stopper l'exécution du code et renvoyer la première position trouvée. Comme il y a trois possibilités, plutôt que d'imbriquer des instructions conditionnelles, nous proposons d'utiliser un groupe Select Case. Grâce à lui, nous allons pouvoir énumérer plus facilement et plus explicitement les cas.

• Dans les bornes de la double boucle, créer le Select Case comme suit :

...
For chaqueColonne = colMin To colMin + nbCol - 1
Select Case (nature)
Case "date":

Case "nombre":

Case "texte":

End Select
Next chaqueColonne
...

Nous testons donc la valeur passée en paramètre (Select Case (nature)) et nous évaluons (Case) les trois possibilités pour la cellule en cours d'analyse. Nous prévoyons une recherche sur la première date (Case "date":) comme sur le premier nombre (Case "nombre":) ou encore sur le premier texte (Case "texte":).

Est-ce une date ?
Dans chaque branche de ce Select Case, nous devons maintenant tester le type effectif de la cellule en cours d'analyse. S'il correspond, alors nous nous arrêterons sur la position trouvée (ligne). Le VBA offre quelques fonctions booléennes pour tester les types.

• Dans la première branche du Select Case, créer le test suivant :

...
Case "date":
If (IsDate(Cells(chaqueLigne, chaqueColonne).Value)) Then
test = True
Exit For
End If
Case "nombre":
...

Nous appliquons la fonction booléenne IsDate sur la cellule en cours d'analyse. Si elle répond favorablement, nous en déduisons que la première date a été trouvée. Donc, nous basculons le booléen test à True. Puis, nous sortons de la seconde boucle (Exit For). Grâce à l'état du booléen test et du critère qui lui est posé avant l'incrémentation de la variable ligne, nous allons ainsi également sortir de la première boucle dans l'enchaînement. De fait, nous allons préserver l'indice de ligne trouvé pour la nature de la cellule cherchée.

Est-ce un nombre ?
Ensuite, pour tester si la cellule en cours d'analyse porte une valeur numérique, nous devons honorer un double critère. Le contenu doit bien être numérique mais dans le même temps, la cellule ne doit pas être vide. Une cellule vide est effectivement considérée potentiellement comme un nombre.

• Dans la deuxième branche du Select Case, créer l'instruction conditionnelle suivante :

...
Case "nombre":
If (IsNumeric(Cells(chaqueLigne, chaqueColonne).Value) And Cells(chaqueLigne, chaqueColonne).Value <> "") Then
test = True
Exit For
End If
Case "texte":
...

C'est la fonction VBA IsNumeric appliquée sur la cellule en cours qui permet de savoir si elle porte un nombre. Si dans le même temps (And) cette même cellule n'est pas vide (<>""), nous procédons aux mêmes réglages que précédemment. C'est ainsi que nous stoppons l'incrémentation de la variable ligne pour retourner la position du premier nombre.

Est-ce un texte ?
Enfin, pour tester si la cellule héberge un texte, c'est encore une double condition qui doit être vérifiée. La cellule ne doit pas être numérique et ne doit pas accueillir de date.

• Dans la troisième branche du Select Case, créer l'instruction conditionnelle suivante :

...
Case "texte":
If (IsNumeric(Cells(chaqueLigne, chaqueColonne).Value) = False And IsDate(Cells(chaqueLigne, chaqueColonne).Value) = False) Then
test = True
Exit For
End If
End Select
...

Avant de tester, il nous reste à retourner la valeur trouvée par la fonction, comme vous le savez, par son propre nom.

• Après la double boucle, ajouter l'affectation suivante :

...
Next chaqueLigne

posUn = ligne

End Function
...

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel,
• Sélectionner la cellule G6 de la première date à trouver,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Appeler la nouvelle fonction parson nom suivi d'une parenthèse, soit : posUn(,
• Désigner le tableau à analyser par son nom, soit : tab,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction,
• Taper l'indication suivante entre guillemets : "date",
• Fermer la parenthèse de la fonction posUn,
• Puis, valider la formule avec la touche Entrée du clavier,

Comme vous pouvez l'apprécier, la première date est parfaitement trouvée. Maintenant, si vous adaptez les syntaxes comme suit : =posUn(tab; "nombre") et =posUn(tab; "texte") dans les cellules respectives G9 et G12, vous obtenez bien les positions du premier nombre et du premier texte. Bien sûr, si vous changez les données dans le tableau, les calculs de repérage s'ajustent automatiquement.