Lorsque vous travaillez avec VBA dans Excel, vous devrez peut-être diviser une chaîne en différentes parties en fonction d'un délimiteur.
Par exemple, si vous avez une adresse, vous pouvez utiliser la fonction VBA Split pour obtenir différentes parties de l'adresse séparées par une virgule (qui serait le délimiteur dans ce cas).
SPLIT est une fonction de chaîne intégrée dans Excel VBA que vous pouvez utiliser pour diviser une chaîne de texte en fonction du délimiteur.
Fonction Excel VBA SPLIT - Syntaxe
Diviser ( Expression, [Délimiteur], [Limite], [Comparer] )
- Expression: Il s'agit de la chaîne que vous souhaitez diviser en fonction du délimiteur. Par exemple, dans le cas de l'exemple d'adresse, l'adresse entière serait l'« expression ». Dans le cas où il s'agit d'une chaîne de longueur nulle (""), la fonction SPLIT renverrait un tableau vide.
- Délimiteur : Il s'agit d'un argument facultatif. C'est le délimiteur qui est utilisé pour diviser l'argument « Expression ». Dans le cas de notre exemple d'adresse, une virgule est un délimiteur utilisé pour diviser l'adresse en différentes parties. Si vous ne spécifiez pas cet argument, un caractère espace est considéré comme le délimiteur par défaut. Si vous donnez une chaîne de longueur nulle (""), la chaîne "Expression" entière est renvoyée par la fonction.
- Limite: Il s'agit d'un argument facultatif. Ici, vous spécifiez le nombre total de sous-chaînes que vous souhaitez renvoyer. Par exemple, si vous souhaitez uniquement renvoyer les trois premières sous-chaînes de l'argument « Expression », ce serait 3. Si vous ne spécifiez pas cet argument, la valeur par défaut est -1, qui renvoie toutes les sous-chaînes.
- Comparer: Il s'agit d'un argument facultatif. Ici, vous spécifiez le type de comparaison que la fonction SPLIT doit effectuer lors de l'évaluation des sous-chaînes. Les options suivantes sont disponibles :
- Lorsque Comparer vaut 0: Ceci est une comparaison binaire. Cela signifie que si votre délimiteur est une chaîne de texte (disons ABC), alors ce serait sensible à la casse. « ABC » ne serait pas égal à « abc ».
- Lorsque Comparer vaut 1: Ceci est une comparaison de texte. Cela signifie que si votre délimiteur est une chaîne de texte (disons ABC), alors même si vous avez « abc » dans la chaîne « Expression », il serait considéré comme un délimiteur.
Maintenant que nous avons couvert les bases de la fonction SPLIT, voyons quelques exemples pratiques.
Exemple 1 - Divisez les mots dans une phrase
Supposons que j'ai le texte - "Le renard brun rapide saute par-dessus le chien paresseux".
Je peux utiliser la fonction SPLIT pour obtenir chaque mot de cette phrase en tant qu'élément séparé dans un tableau.
Le code ci-dessous correspondrait à ceci :
Sub SplitWords() Dim TextStrng As String Dim Result() As String TextStrng = "Le renard brun rapide saute par-dessus le chien paresseux" Result() = Split(TextStrng) End Sub
Bien que le code ne fasse rien d'utile, il vous aidera à comprendre ce que fait la fonction Split dans VBA.
La fonction Split divise la chaîne de texte et affecte chaque mot au tableau Result.
Donc dans ce cas :
- Result(0) stocke la valeur « Le »
- Résultat(1) stocke la valeur « Quick »
- Result(2) stocke la valeur « Brown » et ainsi de suite.
Dans cet exemple, nous n'avons spécifié que le premier argument - qui est le texte à diviser. Comme aucun délimiteur n'a été spécifié, il prend le caractère espace comme délimiteur par défaut.
Note importante:
- La fonction VBA SPLIT renvoie un tableau qui commence à partir de la base 0.
- Lorsque le résultat de la fonction SPLIT est affecté à un tableau, ce tableau doit être déclaré en tant que type de données String. Si vous le déclarez en tant que type de données Variant, il affichera une erreur de non-concordance de type). Dans l'exemple ci-dessus, notez que j'ai déclaré Result() en tant que type de données String.
Exemple 2 - Compter le nombre de mots dans une phrase
Vous pouvez utiliser la fonction SPLIT pour obtenir le nombre total de mots dans une phrase. L'astuce ici est de compter le nombre d'éléments dans le tableau que vous obtenez lorsque vous divisez le texte.
Le code ci-dessous afficherait une boîte de message avec le nombre de mots :
Sub WordCount() Dim TextStrng As String Dim WordCount As Integer Dim Result() As String TextStrng = "Le renard brun rapide saute par-dessus le chien paresseux" Result = Split(TextStrng) WordCount = UBound(Result()) + 1 MsgBox "Le Le nombre de mots est " & WordCount End Sub
Dans ce cas, la fonction UBound nous indique la limite supérieure du tableau (c'est-à-dire le nombre maximum d'éléments du tableau). Puisque la base du tableau est 0, 1 est ajouté pour obtenir le nombre total de mots.
Vous pouvez utiliser un code similaire pour créer une fonction personnalisée dans VBA qui prendra le texte en entrée et renverra le nombre de mots.
Le code ci-dessous créera cette fonction :
Function WordCount(CellRef As Range) Dim TextStrng As String Dim Result() As String Result = Split(WorksheetFunction.Trim(CellRef.Text), " ") WordCount = UBound(Result()) + 1 End Function
Une fois créée, vous pouvez utiliser la fonction WordCount comme n'importe quelle autre fonction normale.
Cette fonction gère également les espaces de début, de fin et doubles entre les mots. Cela a été rendu possible en utilisant la fonction TRIM dans le code VBA.
Si vous souhaitez en savoir plus sur le fonctionnement de cette formule pour compter le nombre de mots dans une phrase ou si vous souhaitez en savoir plus sur une formule non VBA pour obtenir le nombre de mots, consultez ce didacticiel.
Exemple 3 - Utilisation d'un délimiteur autre qu'un espace
Dans les deux exemples précédents, nous n'avons utilisé qu'un seul argument dans la fonction SPLIT, et les autres étaient les arguments par défaut.
Lorsque vous utilisez un autre délimiteur, vous devez le spécifier dans la formule SPLIT.
Dans le code ci-dessous, la fonction SPLIT renvoie un tableau basé sur une virgule comme délimiteur, puis affiche un message avec chaque mot sur une ligne distincte.
Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "The,Quick,Brown,Fox,Jump,Over,The,Lazy,Dog" Result = Split(TextStrng, ",") For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Next i MsgBox DisplayText End Sub
Dans le code ci-dessus, j'ai utilisé la boucle For Next pour parcourir chaque élément du tableau "Result" et l'affecter à la variable "DisplayText".
Exemple 4 - Divisez une adresse en trois parties
Avec la fonction SPLIT, vous pouvez spécifier le nombre de divisions que vous souhaitez obtenir. Par exemple, si je ne spécifie rien, chaque instance du délimiteur serait utilisée pour diviser la chaîne.
Mais si je spécifie 3 comme limite, alors la chaîne sera divisée en trois parties seulement.
Par exemple, si j'ai l'adresse suivante :
2703 Chemin Winifred, Indianapolis, Indiana, 46204
Je peux utiliser la fonction Split dans VBA pour diviser cette adresse en trois parties.
Il divise les deux premiers en fonction du délimiteur virgule et la partie restante devient le troisième élément du tableau.
Le code ci-dessous afficherait l'adresse sur trois lignes différentes dans une boîte de message :
Sub CommaSeparator() Dim TextStrng As String Dim Result() As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split(TextStrng, ",", 3) For i = LBound(Result( )) To UBound(Result()) DisplayText = DisplayText & Result(i) & vbNewLine Next i MsgBox DisplayText End Sub
L'une des utilisations pratiques de ceci pourrait être lorsque vous souhaitez diviser une adresse sur une seule ligne dans le format indiqué dans la boîte de message. Ensuite, vous pouvez créer une fonction personnalisée qui renvoie l'adresse divisée en trois parties (avec chaque partie dans une nouvelle ligne).
Le code suivant ferait cela :
Function ThreePartAddress(cellRef As Range) Dim TextStrng As String Dim Result() As String Dim DisplayText As String Result = Split(cellRef, ",", 3) For i = LBound(Result()) To UBound(Result()) DisplayText = DisplayText & Trim(Result(i)) & vbNewLine Next i ThreePartAddress = Mid(DisplayText, 1, Len(DisplayText) - 1) End Function
Une fois que vous avez ce code dans le module, vous pouvez utiliser la fonction (ThreePartAddress) dans le classeur comme n'importe quelle autre fonction Excel.
Cette fonction prend un argument - la référence de cellule qui a l'adresse.
Notez que pour que l'adresse résultante apparaisse sur trois lignes différentes, vous devez appliquer le format de texte d'habillage aux cellules (c'est dans l'onglet Accueil du groupe Alignement). Si le format « Wrap Text » n'est pas activé, vous verrez l'adresse entière sur une seule ligne.
Exemple 5 - Obtenir le nom de la ville à partir de l'adresse
Avec la fonction Split dans VBA, vous pouvez spécifier quelle partie du tableau résultant vous souhaitez utiliser.
Par exemple, supposons que je divise l'adresse suivante en fonction de la virgule comme délimiteur :
2703 Chemin Winifred, Indianapolis, Indiana, 46204
Le tableau résultant ressemblerait à quelque chose comme indiqué ci-dessous :
{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}
Puisqu'il s'agit d'un tableau, je peux choisir d'afficher ou de renvoyer une partie spécifique de ce tableau.
Vous trouverez ci-dessous un code pour une fonction personnalisée, où vous pouvez spécifier un nombre et il renverra cet élément du tableau. Par exemple, si je veux le nom de l'état, je peux spécifier 3 (car c'est le troisième élément du tableau).
Fonction ReturnNthElement(CellRef As Range, ElementNumber As Integer) Dim Result() As String Result = Split(CellRef, ",") ReturnNthElement = Result(ElementNumber - 1) End Function
La fonction ci-dessus prend deux arguments, la référence de cellule qui a l'adresse et le numéro d'élément que vous souhaitez renvoyer. La fonction Split divise les éléments d'adresse et les affecte à la variable Result.
Ensuite, il renvoie le numéro d'élément que vous avez spécifié comme deuxième argument. Notez que puisque la base est 0, ElementNumber-1 est utilisé pour renvoyer la partie correcte de l'adresse.
Si vous voulez le nom de la ville, vous pouvez utiliser 2 comme deuxième argument. Si vous utilisez un nombre supérieur au nombre total d'éléments, cela renverrait la #VALUE ! Erreur.
Vous pouvez encore simplifier le code comme indiqué ci-dessous :
Fonction ReturnNthElement(CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split(CellRef, ",")(ElementNumber - 1) End Function
Dans le code ci-dessus, au lieu d'utiliser la variable Result, elle ne renvoie que le numéro d'élément spécifié.
Donc, si vous avez Split("Bonjour")(0), il ne retournera que le premier élément, qui est "Bon".
De même, dans le code ci-dessus, il ne renvoie que le numéro d'élément spécifié.
