Fonction VBA Excel pour assembler les cellules d'une plage

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


Sujets que vous pourriez aussi aimer :


Fonction pour assembler les cellules

Si vous travaillez avec une version d'Excel antérieure à la version 2019, vous ne disposez pas de la fonction Concat, entre autres. Celle-ci permet d'assembler les données de plusieurs cellules à zoner avec la souris. Mais comme vous le savez, le VBA peut être le remède à tous les maux.



Sur l'exemple illustré par la capture, des mots accentués sont inscrits dans une première colonne d'un tableau. Sur la droite, les lettres de chacun de ces mots sont isolées dans des cellules indépendantes. Mais comme vous pouvez le voir, les accents disparaissent grâce à l'utilisation de la fonction RechercheV dans un tableau de correspondances. L'utilisateur exploite alors la nouvelle fonction VBA assembler en désignant toutes les cellules de ces caractères individuels. A validation et après réplication de la formule, il obtient la transcription de chacun de ces mots, mais cette fois dénués de tout caractère latin et donc de tout accent.

Classeur Excel à télécharger
Pour développer cette nouvelle fonction Excel en VBA, nous suggérons d'appuyer l'étude sur un classeur offrant déjà la formule d'extraction et de conversion de ces lettres accentuées.

• Télécharger le classeur remplacer-accents.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écouvrons bien le tableau des mots avec sa colonne vide pour réaliser les assemblages sans accents. Sur la droite, les lettres sont toutes fractionnées grâce à l'emploi notamment des fonctions Excel Stxt et RechercheV :

=SIERREUR(RECHERCHEV(STXT($B3; COLONNE(A1); 1); lettres; 2; FAUX); "")

Ces recherches sont effectuées dans la feuille references qui propose un tableau des lettres et de leurs correspondances.

La signature de la fonction
Nous souhaitons créer une fonction un peu particulière pour qu'elle soit plus souple. Elle doit attendre deux paramètres et le second doit être optionnel. Le premier doit représenter la plage des cellules à assembler. Le second doit indiquer, s'il est précisé par l'utilisateur, quel est le caractère d'espacement à intercaler entre chaque cellule à réunir avec les autres.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, cliquer sur le menu Insertion,
• Dans les propositions, choisir l'option Module,

Ainsi, nous ajoutons un nouveau module dans l'application en cours. Sa feuille de code est vierge pour l'instant. Elle apparaît au centre de l'écran. C'est dans ce module que nous proposons de créer cette nouvelle fonction Excel. Certes, elle ne sera pas disponible pour toutes les utilisations d'Excel. Mais si vous souhaitez l'intégrer à la bibliothèque des fonctions Excel, nous avions appris la technique.

• Dans ce nouveau module, créer la fonction assembler comme suit :

Function assembler(cellules As Range, Optional delimiteur As String)

End Function


En premier paramètre, elle attend donc la plage des cellules à assembler. En second, elle attend le potentiel délimiteur que l'utilisateur peut choisir de ne pas renseigner. Il peut par exemple s'agir d'un espace, d'un tiret ou encore d'un point-virgule par exemple, à nécessairement mentionner entre guillemets puisqu'il s'agit d'un texte (String).

Les variables
Pour poursuivre, nous avons tout d'abord besoin de variables notamment pour réceptionner et interpréter les paramètres transmis.

• Dans les bornes de la fonction, ajouter les déclarations et l'affectation suivante :

...
Dim plage As Range: Dim sep As String
Dim cellule As Range: Dim temp As String

Set plage = cellules
...

Nous déclarons la variable plage comme un objet de type Range pour représenter des cellules. Et c'est d'ailleurs ce que nous faisons trois lignes plus loin en l'initialisant sur la plage de cellules désignée par l'utilisateur (Set plage = cellules) en premier argument de la fonction. La variable sep est déclarée comme un texte (As String). Elle doit tester le délimiteur facultatif en second argument. Nous exploiterons la variable cellule pour parcourir toutes les cellules de la plage transmise en premier paramètre. Enfin, nous exploiterons la variable temp pour consolider toutes les cellules à assembler, avant de retourner le résultat à la fonction.

Tester le paramètre optionnel
Maintenant, si un séparateur est transmis en second argument, il doit être intercalé dans les assemblages sinon, il doit être ignoré.

• A la suite du code VBA, ajouter l'instruction conditionnelle suivante :

...
If delimiteur <> "" Then
sep = delimiteur
Else
sep = ""
End If
...

Si le second argument est renseigné (If delimiteur <> ""), nous le stockons dans la variable nommée sep. Dans le cas contraire, nous vidons le contenu de cette variable (""). Cette astuce nous permettra de l'intégrer dans l'assemblage quelle que soit la valeur qu'elle renferme.

Parcourir les cellules de la plage
Désormais et c'est un grand classique, pour parcourir toutes les cellules de la plage passée en premier argument, nous avons besoin d'une boucle For Each. Nous allons l'amorcer grâce à notre objet nommé cellule, déclaré avec le même type (Range) que la plage à analyser.

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

...
For Each cellule In plage
temp = temp & cellule & sep
Next cellule
...

Pour chaque cellule, nous récupérons son contenu (cellule) que nous associons au précédent (temp), sans oublier le séparateur (sep) s'il existe.

Renvoyer la chaîne
Pour finir et comme vous le savez en VBA, il n'existe pas d'instruction return. La fonction répond par son propre nom dans lequel nous devons donc stocker la chaîne assemblée.

• Après la boucle For Each, ajouter l'instruction VBA suivante :

assembler = temp

C'est bien sûr et seulement une fois que toutes les données sont réunies que nous pouvons livrer le résultat.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Cliquer sur la case du premier assemblage à reconstituer, soit en cellule C3,
• Taper le symbole égal (=) pour débuter le calcul,
• Appeler la fonction par son nom, suivi d'une parenthèse, soit : assembler(,
• Désigner les cases à réunir en sélectionnant la plage de cellule E3:S3,
• Ignorer le second paramètre en fermant la parenthèse de la fonction,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière et comme vous le savez, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement. Le premier assemblage surgit. Il s'agit bien du même mot que son voisin mais épuré de son ou de ses accents.

• Double cliquer sur la poignée du résultat pour répandre la logique sur la hauteur du tableau,

Cette fois, ce sont toutes les transcriptions réassemblées que nous obtenons instantanément grâce à cette nouvelle fonction Excel créée en VBA.



Et si vous ajoutez un délimiteur en second argument, comme un espace entre guillemets, vous obtenez aussitôt le résultat escompté. Cette fonction s'avère donc utile et souple.



Pour finir et vous l'avez sans doute compris, le test sur le délimiteur n'était pas nécessaire. Nous l'avons implanté pour la bonne compréhension. Il n'interfère pas lorsqu'il n'est pas défini. Il est considéré comme une chaîne vide. Le code VBA simplifié de cette petite fonction est donc le suivant :

Function assembler(cellules As Range, Optional delimiteur As String)
Dim plage As Range
Dim cellule As Range: Dim temp As String

Set plage = cellules

For Each cellule In plage
temp = temp & cellule & delimiteur
Next cellule

assembler = temp

End Function