Fonction pour répartir les textes espacés dans des colonnes

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


Sujets que vous pourriez aussi aimer :


Fonction VBA Excel - Séparer les mots

Avec cette nouvelle formation VBA Excel, nous allons apprendre à créer une fonction capable de fractionner les textes assemblés dans une même cellule, selon un séparateur à définir en second argument. Il pourrait par exemple s'agir de l'espace.



Sur l'exemple illustré par la capture, des identités complètes sont fournies dans une première colonne. Sur la droite, à partir de la première cellule d'extraction, l'utilisateur appelle la fonction que nous avons nommée fractionner. Il définit la rangée complète des identités à fractionner. En deuxième paramètre, il indique le caractère utilisé comme séparateur, l'espace ici (" "). A validation, sur cinq colonnes et sur la hauteur totale de la rangée d'origine, il obtient les informations dissociées sur la civilité, le nom, le prénom, le code postal et la ville.

Classeur Excel à télécharger
Pour créer cette nouvelle fonction VBA Excel, nous suggérons d'appuyer l'étude sur un classeur offrant ces identités complètes à découper.

• Télécharger le classeur fonction-separer-textes.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur une feuille dans laquelle une première colonne livre des identités assemblées dans des mêmes cellules. Sur la droite, un tableau vide d'extraction attend la fonction capable de fractionner tous les éléments de ces identités comme nous l'avons démontré précédemment.

La fonction et ses variables
Pour débuter, nous devons créer la fonction et déclarer les variables nécessaires à ses traitements.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur sur la gauche, cliquer sur l'élément VBAProject(fonction-separer-textes),
• En haut de l'éditeur, déployer le menu Insertion,
• Dans les propositions, choisir l'option Module,

De cette manière, nous ajoutons un nouveau module de code au projet en cours. Sa feuille de code est vierge pour l'instant. Elle apparaît au centre de l'écran.

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

Function fractionner(plage As Range,sep As String) As Variant
Dim laPlage As Range: Dim cellule As Range: Dim extract As Variant
Dim nbCel As Byte: Dim i As Byte: Dim compteur As Byte
Dim mots As Variant

End Function

Nous la typons comme un Variant pour qu'elle puisse à l'issue retourner un tableau de variables et fournir toutes les découpes pour toutes les cellules de la plage analysée. A ce titre, nous signons cette fonction avec deux paramètres en attente. Le premier représente la plage de cellules que doit sélectionner l'utilisateur au moment de la construction de la fonction. Le second représente le séparateur de mots, toujours à préciser par l'utilisateur et entre guillemets, puisqu'il s'agit d'un String.

L'objet laPlage est typé comme un Range pour prendre possession de la plage de cellules passée en paramètre. Grâce à la variable cellule du même type, nous les parcourrons toutes, dans une boucle For Each. La variable extract n'est pas encore typée (Variant). Elle doit représenter le tableau de variables devant accueillir toutes les découpes à restituer. Elle prendra son type précis au moment du dimensionnement.

Dans la variable nbCel, nous stockerons le nombre de cellules contenues dans la plage passée en paramètre. Grâce à son indication, nous pourrons dimensionner le tableau de variables en hauteur. i et compteur seront utilisées comme variables de boucles. Enfin, la variable mots sera utilisée comme tableau de variables pour séparer chaque mot d'une cellule, en fonction du séparateur passé en second argument de la fonction. C'est en les dissociant que nous serons capables de les empiler dans le tableau à retourner, sur des lignes explicitement différentes.

La plage et le nombre de ses cellules
Grâce à ces déclarations, nous allons maintenant pouvoir initialiser quelques-unes de ces variables.

• A la suite du code de la fonction, ajouter les instructions VBA suivantes :

...
Set laPlage = plage
nbCel = laPlage.Count
compteur = 1
...

Nous initialisons (Set) tout d'abord notre objet laPlage sur la plage de cellules à piloter. Dès lors, grâce à sa propriété Count ainsi héritée, nous retournons le nombre de cellules de cette plage que nous stockons dans la variable nbCel. Nous l'avons dit, nous l'utiliserons pour dimensionner le tableau de retour en hauteur. Enfin, nous initialisons la variable compteur sur le premier indice de ligne (1) pour ce tableau de variables. La variable i sera quant à elle initialisée dans une boucle For Next.

Parcourir chaque cellule de la plage
Maintenant que la plage passée en paramètre est domptée, nous pouvons entreprendre de parcourir chacune des ses cellules en vue de découper les informations qu'elles contiennent. Pour cela, il suffit d'engager une boucle For Each sur notre objet cellule, déclaré et typé à cet effet.

• A la suite du code de la fonction, créer la boucle For Each suivante:

...
For Each cellule In laPlage
mots = Split(cellule.Value, sep)

compteur = compteur + 1
Next cellule
...

Pour chaque cellule de la plage, nous exploitons la fonction Split sur le séparateur (sep) passé en second paramètre, afin de ranger chaque mot dans la variable mots qui est transformée en tableau de variables. Puis, nous n'oublions pas d'incrémenter la variable compteur.

Dimensionner le tableau de découpe
Bien sûr, avant de passer à la cellule suivante, donc avant de réinitialiser la variable mots sur d'autres termes, nous devons engager des traitements sur la cellule en cours. Pour cela, nous devons commencer par dimensionner le tableau de découpe.

• Dans la boucle et avant l'incrémentation, créer l'instruction conditionnelle suivante :

...
For Each cellule In laPlage
mots = Split(cellule.Value, sep)
If compteur = 1 Then
ReDim extract(1 To nbCel, 1 To UBound(mots) + 1)
End If
compteur = compteur + 1
Next cellule
...

A partir de la première cellule (compteur = 1), nous dimensionnons le tableau de variables une bonne fois pour toutes. Nous définissons sa hauteur (1 To nbCel) grâce au nombre de cellules désormais connu. Pour la largeur, nous partons du principe que toutes les cellules embarquent le même nombre de mots. La borne supérieure nous est retournée par la fonction UBound appliquée sur le tableau nommé mots. Mais cette borne considère un décompte démarrant à partir de l'indice zéro pour le premier mot. C'est la raison pour laquelle nous ajoutons une unité pour les considérer tous. Si vous souhaitez travailler sur des plages de cellules aux dimensions variables, il suffit de prévoir large en supposant par exemple que certaines cellules peuvent accueillir jusqu'à 20 mots (1 To 20).

Extraire chaque mot de chaque chaîne
Maintenant que le tableau de sortie est correctement dimensionné, à chaque passage dans cette boucle, donc pour chaque cellule, nous devons parcourir chacun de ses mots pour les ranger dans des colonnes différentes du tableau de variables pour la ligne en cours (compteur). Pour cela, nous devons intégrer une seconde boucle dans la première.

• Toujours dans la boucle et avant l'incrémentation, créer la boucle For Next suivante :

...
End If
For i = 0 To UBound(mots)
extract(compteur, i + 1) = mots(i)
Next i
compteur = compteur + 1
...

Nous parcourons chaque mot du premier (0) au dernier (UBound(mots)). Nous stockons chacun (mots(i)) dans le tableau de variables (extract) sur la ligne en cours (compteur) pour l'indice de colonne correspondant (i+1). Ainsi, à chaque passage suivant dans la première boucle, ce sont tous les mots des cellules suivantes qui seront empilés dans le tableau de variables sur les lignes suivantes. C'est de cette manière que la fonction matricielle que nous sommes en train d'aboutir sera capable de livrer les découpes sur toutes les cellules, avec une seule formule.

Retourner les éléments fractionnés
Une fois que tous les mots de toutes les cellules ont été passés en revue, le tableau de variables est intégralement chargé de toutes les découpes. Nous devons donc restituer ses résultats. Et pour cela comme vous le savez, unefonction VBA répond par son propre nom. Nous devons donc l'affecter.

• Après les boucles, ajouter les deux instructions VBA suivantes :

...
Next cellule

Set laPlage = Nothing
fractionner = extract

End Function
...

Tout d'abord et puisque l'objet laPlage n'est plus utilisé, nous prenons soin de le détruire (Set Nothing) pour libérer les ressources qu'il empruntait. Puis, nous transmettons les résultats du tableau de variables à la fonction pour qu'elle réponde à l'appel de l'utilisateur.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Sélectionner la première case d'extraction en cliquant sur la cellule D4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction que nous avons créée suivie d'une parenthèse, soit : fractionner(,
• Sélectionner les cases des textes à découper, soit la plage de cellules B4:B11,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction,
• Désigner le séparateur entre guillemets, un espace ici, soit : " ",
• Fermer la parenthèse de la fonction fractionner,
• Enfin, valider la formule par la touche Entrée du clavier,

Comme vous pouvez l'apprécier, tous les mots des cellules sont explicitement séparés dans des cases indépendantes et ce, pour toutes les lignes de la plage transmise à la fonction. Voilà donc, encore une nouvelle fonction VBA fort intéressante à ajouter à la bibliothèque Excel.