Utilisation de la fonction de fractionnement VBA
La fonction VBA Split vous permet de séparer les composants d'une chaîne de texte standard où chaque composant utilise un caractère délimiteur spécifique, par ex. une virgule ou un deux-points. Il est plus facile à utiliser que d'écrire du code pour rechercher les délimiteurs dans la chaîne, puis extraire les valeurs.
Il peut être utilisé si vous lisez une ligne à partir d'une valeur séparée par des virgules (fichier CSV) ou si vous avez une adresse postale sur une seule ligne, mais que vous souhaitez la voir sur plusieurs lignes.
La syntaxe est :
| 1 | Expression fractionnée, délimiteur[facultatif], limite[facultatif], compare[facultatif] |
La fonction VBA Split a quatre paramètres :
- Expression - La chaîne de texte que vous souhaitez diviser en différentes parties.
- Délimiteur (optionnel)- chaîne ou caractère non imprimable - Définit le caractère délimiteur qui va être utilisé pour le fractionnement. Si aucun caractère délimiteur n'est fourni, la valeur par défaut d'un espace est utilisée.
- Limite (optionnel) - nombre - Définit le nombre de divisions qui seront effectuées. Si vide, toutes les divisions disponibles seront effectuées dans la chaîne. S'il est défini sur 1, aucune division ne sera effectuée. Fondamentalement, il vous permet de séparer un nombre spécifique de valeurs en commençant au début de la chaîne, par ex. où la chaîne est très longue et vous n'avez besoin que des trois premières divisions.
- Comparer (optionnel) - Si votre délimiteur est un caractère de texte, il est utilisé pour basculer si le délimiteur est sensible à la casse ou non. Les valeurs sont vbBinaryCompare (sensible à la casse) et vbTextCompare (non sensible à la casse).
La fonction split renvoie toujours un tableau.
Exemple simple de la fonction Split
| 123456789101112 | Sub SplitExample()'Définir les variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, I en tant que variante'Exemple de chaîne avec des délimiteurs d'espaceMyString = "Un Deux Trois Quatre"'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne)'parcourir le tableau créé pour afficher chaque valeurPour chaque I dans MyArrayMsgBox IEnsuite jeFin du sous-marin |
Dans cet exemple, aucun délimiteur n'est spécifié car tous les mots ont un espace entre eux, donc le délimiteur par défaut (espace) peut être utilisé.
Le tableau n'a pas de dimension et est défini comme une chaîne. La variable I, qui est utilisée dans la boucle For… Next doit être dimensionnée comme une variante.
Lorsque ce code est exécuté, il affichera quatre boîtes de message, une pour chacune des divisions, par exemple. Un deux trois. Quatre.
Notez que s'il y a un double espace entre les mots dans la chaîne, cela sera évalué comme un fractionnement, bien qu'il n'y ait rien dedans. Ce n'est peut-être pas le résultat que vous voulez voir.
Vous pouvez résoudre ce problème en utilisant la fonction Remplacer pour remplacer les doubles espaces par un seul :
| 1 | MaChaîne = Remplacer(MaChaîne, " ", " ") |
Un espace de fin ou de début peut également causer des problèmes en produisant un fractionnement vide. Ceux-ci sont souvent très difficiles à voir. Vous pouvez supprimer ces espaces superflus à l'aide de la fonction Trim :
| 1 | MaChaîne = Trim(MaChaîne) |
Utilisation de la fonction Split avec un caractère délimiteur
Nous pouvons utiliser un délimiteur d'un point-virgule (;). Cela se trouve fréquemment dans les chaînes d'adresses e-mail pour séparer les adresses. Vous pouvez recevoir un e-mail qui est partagé avec un certain nombre de collègues et vous souhaitez voir une liste dans votre feuille de calcul des destinataires. Vous pouvez facilement copier les adresses e-mail des boîtes e-mail « À » ou « Copier » et dans votre code.
| 123456789101112131415 | Sub SplitBySemicolonExample()'Définir les variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, I en tant que variante, N en tant qu'entier'Exemple de chaîne avec des délimiteurs points-virgulesMyString = "[email protected];[email protected];[email protected];[email protected]"'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne, ";")'Effacer la feuille de calculActiveSheet.UsedRange.Clear'parcourir le tableauPour N = 0 à UBound (MyArray)'Placez chaque adresse e-mail dans la première colonne de la feuille de calculPlage("A" & N + 1).Valeur = MonTableau(N)N suivantFin du sous-marin |
Notez qu'une boucle For… Next est utilisée pour parcourir le tableau. Le premier élément du tableau commence toujours à zéro et la fonction Upper Bound est utilisée pour obtenir le nombre maximal d'éléments.
Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :
Utilisation d'un paramètre de limite dans une fonction de division
Le paramètre limit permet d'effectuer un nombre spécifique de divisions à partir du début de la chaîne. Malheureusement, vous ne pouvez pas fournir une position de départ ou une plage de fractionnements à faire, c'est donc assez basique. Vous pouvez créer votre propre code VBA pour créer une fonction pour ce faire, et cela sera expliqué plus loin dans cet article.
| 123456789101112131415 | Sub SplitWithLimitExample()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, I en tant que variante, N en tant qu'entier'Exemple de chaîne avec des délimiteurs virgulesMyString = "Un,Deux,Trois,Quatre,Cinq,Six"'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne, ",", 4)'Effacer la feuille de calculActiveSheet.UsedRange.Clear'Itérer à travers le tableauPour N = 0 à UBound (MyArray)'Placer chaque division dans la première colonne de la feuille de calculPlage("A" & N + 1).Valeur = MonTableau(N)N suivantFin du sous-marin |
Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :
Seules les trois premières valeurs de fractionnement sont affichées séparément. Les trois dernières valeurs sont affichées sous la forme d'une longue chaîne et ne sont pas fractionnées.
Si vous choisissez une valeur limite supérieure au nombre de délimiteurs dans une chaîne, cela ne produira pas d'erreur. La chaîne sera divisée en tous ses composants comme si la valeur limite n'avait pas été fournie.
Utilisation du paramètre de comparaison dans une fonction de fractionnement
Le paramètre Compare détermine si le délimiteur est sensible à la casse ou non. Cela ne s'applique pas si les délimiteurs sont des virgules, des points-virgules ou des deux-points.
Remarque : Au lieu de cela, vous pouvez toujours placer le texte de comparaison d'options <> en haut de votre module pour éliminer la sensibilité à la casse pour l'ensemble du module.
| 123456789101112131415 | Sub SplitParComparerExample()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, I en tant que variante, N en tant qu'entier'Exemple de chaîne avec délimiteurs XMyString = "OneXTwoXThreexFourXFivexSix"'Utiliser la fonction Split pour diviser les composants de la chaîneMyArray = Split(MyString, "X", , vbBinaryComparer)'Effacer la feuille de calculActiveSheet.UsedRange.Clear'parcourir le tableauPour N = 0 à UBound (MyArray)'Placer chaque division dans la première colonne de la feuille de calculPlage("A" & N + 1).Valeur = MonTableau(N)N suivantFin du sous-marin |
Dans cet exemple, la chaîne à diviser utilise le caractère « X » comme délimiteur. Cependant, dans cette chaîne, il y a un mélange de caractères « X » majuscules et minuscules. Le paramètre Comparer de la fonction Split utilise un caractère « X » majuscule.
Si le paramètre Compare est défini sur vbBinaryCompare, les caractères « x » minuscules seront ignorés et votre feuille de calcul ressemblera à ceci :
Si le paramètre Compare est défini sur vbTextCompare, les caractères « x » minuscules seront utilisés dans le fractionnement et votre feuille de calcul ressemblera à ceci :
Notez que la valeur de la cellule A6 est tronquée car elle contient un caractère « x » minuscule. Étant donné que le fractionnement n'est pas sensible à la casse, tout délimiteur faisant partie d'une sous-chaîne provoquera un fractionnement.
C'est un point important à garder à l'esprit lors de l'utilisation d'un délimiteur de texte et de vbTextCompare. Vous pouvez facilement vous retrouver avec un mauvais résultat.
Utilisation de caractères non imprimables comme caractère délimiteur
Vous pouvez utiliser des caractères non imprimables comme délimiteur, comme un retour chariot (un saut de ligne).
Ici, nous utilisons le vbCr pour spécifier un retour chariot <>
| 123456789101112131415 | Sub SplitByNonPrintableExample()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, I en tant que variante, N en tant qu'entier'Exemple de chaîne avec des délimiteurs de retour chariotMyString = "Un" & vbCr & "Deux" & vbCr & "Trois" & vbCr & "Quatre" & vbCr & "Cinq" & vbCr & "Six"'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne, vbCr, , vbTextComparer)'Effacer la feuille de calculActiveSheet.UsedRange.Clear'Itérer à travers le tableauPour N = 0 à UBound (MyArray)'Placer chaque division dans la première colonne de la feuille de calculPlage("A" & N + 1).Valeur = MonTableau(N)N suivantFin du sous-marin |
Dans cet exemple, une chaîne est construite en utilisant vbCr (carriage return character) comme délimiteur.
Lorsque ce code est exécuté, votre feuille de calcul ressemblera à ceci :
Utilisation de la fonction Join pour inverser une division
La fonction Join rejoindra tous les éléments d'un tableau, mais en utilisant un délimiteur spécifié. Si aucun caractère délimiteur n'est spécifié, un espace sera utilisé.
| 123456789101112131415 | Exemple de sous-jointure()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, I en tant que variante, N en tant qu'entierDim Target As String'Exemple de chaîne avec des délimiteurs virgulesMyString = "Un,Deux,Trois,Quatre,Cinq,Six"'Placez MyString dans la cellule A1Plage("A1").Valeur = MaChaîne'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne, ",")'Utilisez la fonction Join pour recréer la chaîne d'origine à l'aide d'un délimiteur point-virguleCible = Join (MonTableau,";")'Placez la chaîne de résultat dans la cellule A2Plage ("A2"). Valeur = CibleFin du sous-marin |
Ce code divise une chaîne avec des délimiteurs virgules en un tableau et la joint à l'aide de délimiteurs point-virgule.
Après avoir exécuté ce code, votre feuille de calcul ressemblera à ceci :
La cellule A1 contient la chaîne d'origine avec des délimiteurs virgules et la cellule A2 contient la nouvelle chaîne jointe avec des délimiteurs points-virgules.
Utiliser la fonction Split pour faire un compte de mots
En gardant à l'esprit qu'une variable de chaîne dans Excel VBA peut atteindre 2 Go de long, vous pouvez utiliser la fonction de division pour compter les mots dans un morceau de texte. Évidemment, Microsoft Word le fait automatiquement, mais cela pourrait être utile pour un simple fichier texte ou du texte copié depuis une autre application.
| 1234567891011121314 | Sub NumberOfWordsExample()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne'Exemple de chaîne avec des délimiteurs d'espaceMyString = "Un Deux Trois Quatre Cinq Six"'Supprimer les doubles espacesMaChaîne = Remplacer(MaChaîne, " ", " ")'Supprimer tous les espaces de début ou de finMaChaîne = Trim(MaChaîne)'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne)'Afficher le nombre de mots à l'aide de la fonction UBoundMsgBox "Nombre de mots" & UBound(MyArray) + 1Fin du sous-marin |
L'un des dangers de ce code de comptage de mots est qu'il sera lancé par des espaces doubles et des espaces de début et de fin. S'ils sont présents, ils seront comptés comme des mots supplémentaires et le nombre de mots finira par être inexact.
Le code utilise les fonctions Replace et Trim pour supprimer ces espaces supplémentaires.
La dernière ligne de code affiche le nombre de mots trouvés en utilisant la fonction UBound pour obtenir le nombre maximal d'éléments du tableau, puis en l'incrémentant de 1. En effet, le premier élément du tableau commence à zéro.
Fractionnement d'une adresse en cellules de feuille de calcul
Les adresses de messagerie sont souvent de longues chaînes de texte avec des délimiteurs par des virgules. Vous voudrez peut-être diviser chaque partie de l'adresse dans une cellule distincte.
| 123456789101112131415 | Exemple de sous-adresse()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, N en tant qu'entier'Configurer la chaîne avec l'adresse Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Utilisez la fonction split pour diviser la chaîne à l'aide d'un délimiteur virguleMonTableau = Split(MaChaîne, ",")'Effacer la feuille de calculActiveSheet.UsedRange.Clear'parcourir le tableauPour N = 0 à UBound (MyArray)'Placer chaque division dans la première colonne de la feuille de calculPlage("A" & N + 1).Valeur = MonTableau(N)N suivantFin du sous-marin |
L'exécution de ce code utilisera le délimiteur virgule pour mettre chaque ligne de l'adresse dans une cellule distincte :
Si vous souhaitez uniquement renvoyer le code postal (dernier élément du tableau), vous pouvez utiliser le code :
| 123456789101112 | Exemple de sous-adresseZipCode()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, N en tant qu'entier, Temp en tant que chaîne'Configurer la chaîne avec l'adresse Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Utilisez la fonction split pour diviser la chaîne à l'aide d'un délimiteur virguleMonTableau = Split(MaChaîne, ",")'Effacer la feuille de calculActiveSheet.UsedRange.Clear'Mettez le code postal dans la cellule A1Plage("A1").Valeur = MonTableau(UBound(MonTableau))Fin du sous-marin |
Cela n'utilisera que le dernier élément du tableau, qui est trouvé en utilisant la fonction UBound.
D'un autre côté, vous souhaiterez peut-être voir toutes les lignes dans une cellule afin qu'elles puissent être imprimées sur une étiquette d'adresse :
| 1234567891011121314151617 | Exemple de sous-adresse()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne, N en tant qu'entier, Temp en tant que chaîne'Configurer la chaîne avec l'adresse Microsoft CorporationMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 USA"'Utilisez la fonction split pour diviser la chaîne à l'aide d'un délimiteur virguleMonTableau = Split(MaChaîne, ",")'Effacer la feuille de calculActiveSheet.UsedRange.Clear'parcourir le tableauPour N = 0 à UBound (MyArray)'place chaque élément du tableau plus un caractère de saut de ligne dans une chaîneTemp = Temp & MyArray(N) & vbLfN suivant'Mettez la chaîne sur la feuille de calculPlage ("A1") = TempératureFin du sous-marin |
Cet exemple fonctionne de la même manière que le précédent, sauf qu'il crée une chaîne temporaire de tous les éléments du tableau, mais en insérant un caractère de saut de ligne après chaque élément.
La feuille de calcul ressemblera à ceci après l'exécution du code :
Diviser la chaîne en cellules de feuille de calcul
Vous pouvez copier le tableau Split dans des cellules de feuille de calcul <> avec une seule commande :
| 12345678910 | Sous CopyToRange()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne'Exemple de chaîne avec des délimiteurs d'espaceMyString = "Un,Deux,Trois,Quatre,Cinq,Six"'Utiliser la fonction Split pour diviser les composants de la chaîneMonTableau = Split(MaChaîne, ",")'Copier le tableau dans la feuille de calculRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Fin du sous-marin |
Une fois ce code exécuté, votre feuille de calcul ressemblera à ceci :
Création d'une nouvelle fonction pour permettre le fractionnement à partir d'un point donné
Le paramètre Limit de la fonction Split vous permet uniquement de spécifier une limite supérieure à laquelle vous souhaitez que le fractionnement s'arrête. Il commence toujours au début de la chaîne.
Il serait très utile d'avoir une fonction similaire dans laquelle vous pouvez spécifier le point de départ de la division dans la chaîne et le nombre de divisions que vous souhaitez voir à partir de ce point. Il extraira également uniquement les divisions que vous avez spécifiées dans le tableau, plutôt que d'avoir une énorme valeur de chaîne comme dernier élément du tableau.
Vous pouvez facilement créer vous-même une fonction (appelée SplitSlicer) dans VBA pour le faire :
| 123456789101112131415161718192021222324 | Fonction SplitSlicer (Cible en tant que chaîne, Suppr en tant que chaîne, Démarrer en tant qu'entier, N en tant qu'entier)'Créer une variable de tableauDim MyArray() en tant que chaîne'Capturez le fractionnement à l'aide de la variable de démarrage à l'aide du caractère délimiteurMonTableau = Diviser (Cible, Suppr, Début)« Vérifiez si le paramètre de démarrage est supérieur au nombre de divisions - cela peut causer des problèmesSi Démarrer > UBound(MyArray) + 1 Alors« Afficher l'erreur et quitter la fonctionMsgBox "Le paramètre de démarrage est supérieur au nombre de divisions disponibles"SplitSlicer = MyArrayFonction de sortieFin si'Mette le dernier élément du tableau dans la chaîneCible = MonTableau(UBound(MonTableau))'Split la chaîne en utilisant N comme limiteMonTableau = Diviser (Cible, Suppr, N)‘Vérifiez que la limite supérieure est supérieure à zéro car le code supprime le dernier élémentSi UBound(MyArray) > 0 Alors'Utilisez ReDim pour supprimer le dernier élément du tableauReDim Preserve MyArray(UBound(MyArray) - 1)Fin si'Renvoyer le nouveau tableauSplitSlicer = MyArrayFonction de fin |
Cette fonction est construite avec quatre paramètres :
- Cible - chaîne - c'est la chaîne d'entrée que vous souhaitez diviser
- Suppr - chaîne ou caractère non imprimable - c'est le caractère délimiteur que vous utilisez, par ex. virgule, deux points
- Début - nombre - c'est la division de départ pour votre tranche
- N - nombre - c'est le nombre de divisions que vous voulez faire dans votre tranche
Aucun de ces paramètres n'est facultatif ou n'a de valeurs par défaut, mais vous pouvez l'intégrer dans le code de la fonction si vous souhaitez l'étendre davantage.
La fonction utilise la fonction Split pour créer un tableau en utilisant le paramètre Start comme limite. Cela signifie que les éléments du tableau maintiendront les divisions jusqu'au paramètre de début, mais le reste de la chaîne sera le dernier élément et ne sera pas divisé.
Le dernier élément du tableau est retransféré dans une chaîne à l'aide de la fonction UBound pour déterminer de quel élément il s'agit.
La chaîne est ensuite divisée à nouveau dans le tableau, en utilisant N comme variable de limite. Cela signifie que des divisions seront effectuées pour la chaîne jusqu'à la position N, après quoi le reste de la chaîne formera le dernier élément du tableau.
L'instruction ReDim est utilisée pour supprimer le dernier élément car nous ne voulons que les éléments spécifiques laissés dans le tableau. Notez que le paramètre Preserve est utilisé, sinon toutes les données du tableau seront perdues.
Le nouveau tableau est ensuite renvoyé au code à partir duquel il a été appelé.
Notez que le code est « protégé contre les erreurs ». Les utilisateurs feront souvent des choses étranges que vous n'avez pas envisagées. Par exemple, s'ils essaient d'utiliser la fonction avec le paramètre Start ou N supérieur au nombre de divisions disponibles dans la chaîne, cela entraînera probablement l'échec de la fonction.
Le code est inclus pour vérifier la valeur Start et également pour s'assurer qu'il existe un élément qui peut être supprimé lorsque l'instruction ReDim est utilisée sur le tableau.
Voici le code pour tester la fonction :
| 123456789101112 | Sous-TestSplitSlicer()'Créer des variablesDim MyArray() en tant que chaîne, MyString en tant que chaîne'Définir l'exemple de chaîne avec des délimiteurs virgulesMyString = "Un,Deux,Trois,Quatre,Cinq,Six,Sept,Huit,Neuf,Dix"'Utiliser la fonction Splitslicer pour définir un nouveau tableauMonTableau = SplitSlicer(MaChaîne, ",", 4, 3)'Effacer la feuille activeActiveSheet.UsedRange.Clear'Copier le tableau dans la feuille de calculRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Fin du sous-marin |
Exécutez ce code et votre feuille de calcul ressemblera à ceci :
