VBA toetreden - Stapsgewijze voorbeelden van Excel VBA Join-functie

Inhoudsopgave

Vergelijkbaar met wat we in het werkblad hebben als Concatenate-functie en de & -opdracht die wordt gebruikt om twee of meer dan twee strings samen te voegen, gebruiken we in VBA de opdracht Join om dit te doen, in Join in VBA nemen we de bron van de gegevens in een array en vergelijkbaar met de aaneenschakeling gebruiken we een scheidingsteken om ze samen te voegen.

Excel VBA JOIN-functie

Zoals de naam zelf suggereert, wordt de VBA JOIN-functie gebruikt om een ​​reeks subtekenreeksen samen te voegen met het opgegeven scheidingsteken. Als we geen scheidingsteken specificeren, neemt het 'spatie' in als een standaard scheidingsteken. Het doet hetzelfde werk als de functie Concatenate doet in Excel, behalve dat we het scheidingsteken maar één keer hoeven op te geven, terwijl we in de functie Concatenate elke keer een scheidingsteken tussen elke twee strings moeten opgeven.

De syntaxis van de functie is

Zoals we kunnen zien, heeft de functie twee argumenten en retourneert een string. Argumenten zijn:

  1. SourceArray : we moeten een reeks subtekenreeksen die moeten worden samengevoegd, specificeren of ernaar verwijzen.
  2. Scheidingsteken : het scheidingsteken wordt gebruikt om elk van de subtekenreeksen te scheiden bij het maken van de resulterende tekenreeks. Aangezien dit een optioneel argument is, wordt het scheidingsteken ingesteld op een spatie "" als we het weglaten.

De VBA SPLIT-functie is een exact tegenovergestelde functie van de VBA JOIN-functie.

Voorbeelden van VBA Join-functie

Hieronder staan ​​de voorbeelden van de join-functie in Excel VBA.

VBA Join - Voorbeeld # 1

Stel dat we ons willen aansluiten bij de eerste (Ramesh), middelste (Kumar) en achternaam (Mishra).

Stappen zouden zijn:

  • Eerst moeten we de visuele basiseditor openen. We kunnen hetzelfde doen door te klikken op de opdracht 'Visual Basic' in de groep 'Code' onder het tabblad 'Ontwikkelaar' excel of we kunnen de Excel-sneltoets Alt + F11 gebruiken .
  • Voeg de module in door met de rechtermuisknop op een "blad 1" te klikken en de opdracht 'Invoegen' te kiezen in het contextmenu en vervolgens 'Module' te kiezen om in te voegen.
  • Maak een subroutine met de naam 'JoiningName'.

Code:

Sub JoiningName () End Sub
  • Gebruik de JOIN-functie als volgt

Code:

Sub JoiningName () Bereik ("D2"). Waarde = Join (Array ("Ramesh", "Kumar", "Mishra")) End Sub

We kunnen zien dat we de ARRAY-functie hebben gebruikt om SourceArray aan de JOIN-functie te leveren en hebben overgeslagen om het scheidingsteken op te geven, zodat 'spatie' het standaardteken zou zijn. De verwerkte waarde van de JOIN-functie wordt in cel D2 geschreven wanneer we deze code uitvoeren met de F5-toets of handmatig.

VBA Join - Voorbeeld # 2

Stel dat we verschillende Excel-bestanden willen maken waarbij de artikelnaam alleen de verkoop voor dat artikel bevat.

  • Open de Visual Basic Editor met de sneltoets Alt + F11.
  • Klik met de rechtermuisknop op het blad 'Blad1 ′ (Voorbeeld 2)' om het contextmenu te openen en klik op 'Invoegen' om een ​​VBA 'Module' in het VBA-project in te voegen.
  • Definieer een subroutine met de naam 'CreateItemSoldFiles'.

Code:

Sub CreateItemSoldFiles () End Sub
  • We moeten een verwijzing naar de 'Microsoft Scripting Runtime'-objectbibliotheek instellen met behulp van het menu Extra -> Verwijzingen…, omdat we een code (objecten) zullen gebruiken die niet werken als we deze objectbibliotheek niet opnemen.
  • Nu zullen we alle variabelen declareren.

Code:

Dim FSO als nieuwe Scripting.FileSystemObject

De bovenstaande FSO-variabele geeft toegang tot het VBA FileSystemObject. Na het binden kunnen we functies gebruiken zoals BuildPath, CopyFile, CreateTextFile, etc.

  • De volgende instructie maakt een TextStream-object. Via het TextStream-object kunnen we lezen van of toevoegen aan het originele bestand.

Code:

Dim FSO As New Scripting.FileSystemObject Dim ts As Scripting.TextStream
  • We will declare more variables. ‘r’ is for holding rows in the range, ‘fs’ is for storing final joined string, ‘cols’ for storing numbers of columns in the range, ‘FolPath’ for storing the path of the folder so that we can save the files in the folder and ‘Items_Sold’ for storing various item names to create a file with these names.

Code:

Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String
  • To count the total number of columns in range, we will define the following statement.

Code:

cols = Range("A1").CurrentRegion.Columns.Count

This statement will first select the current region for cell A1 and then will count the total number of columns in the current region.

  • We will write the following statements for assigning the variable ‘FolPath’ a path using VBA ENVIRON function and Concatenation Operator.

Code:

FolPath = Environ("UserProfile") & "DesktopItems_Sold" If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath

The second statement will create the folder if the folder does not exist in the same location.

  • This code will assign the values of B column one by one to ‘Items_Sold’ We have used ‘OFFSET function’ to get the reference of cell in B column as the currently selected cell is in column A.

Code:

Items_Sold = r.Offset(0, 1).Value
  • The following bordered statement will open the files with names stored in ‘Items_Sold’ variable in one by one in appending mode (the new values will be appended at last).

Code:

Set ts = FSO.OpenTextFile(FolPath & " " & Items_Sold & ".xls", ForAppending, True)

We have used Concatenate operator with variables ‘FolPath’ and ‘Items_Sold’ and static values (“” and”.xls”) to create file names for excel files.

  • We need to keep in mind that VBA JOIN function takes an only one-dimensional array as SourceArray To convert the rows into a one-dimensional array, we need to use Application.Transpose method two times.

Code:

fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab)

We have used the Resize method of range object to resize the range to the width of a number of columns in the range.

As delimiter, we have used ‘vbTab’ keyword so that values would be filled in different cells.

  • As we have stored the processed value of JOIN function into ‘fs’ variable, we will write the fs’s values into new lines of VBA created excel files for every row in our original file from row number 2 to the last row (in our case it is 350th row).
  • Before ending the loop, we will close the file opened. The code would be as shown in the screenshot.

We have written the full code now.

Code:

Sub CreateItemSoldFiles() Dim FSO As New Scripting.FileSystemObject Dim ts As Scripting.TextStream Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String cols = Range("A1").CurrentRegion.Columns.Count FolPath = Environ("UserProfile") & "DesktopItems_Sold" If Not FSO.FolderExists(FolPath) Then FSO.CreateFolder FolPath For Each r In Range("A2", Range("A1").End(xlDown)) Items_Sold = r.Offset(0, 1).Value Set ts = FSO.OpenTextFile(FolPath & " " & Items_Sold & ".xls", ForAppending, True) fs = Join(Application.Transpose(Application.Transpose(r.Resize(1, cols).Value)), vbTab) ts.WriteLine fs ts.Close Next r End Sub

Om de code nu uit te voeren, zullen we op F5 drukken en we kunnen zien dat een map met de naam 'Items_Sold' is gemaakt met behulp van VBA-code op het bureaublad.

In de map zijn er 7 unieke bestanden gemaakt met de namen van het item en we kunnen alleen details over dat specifieke item in bestanden vinden.

Laptop.xls

Dingen om te onthouden over de VBA JOIN-functie

  • De SourceArray moet een eendimensionale array zijn. We kunnen niet naar een individuele cel verwijzen, omdat hierdoor meerdere multidimensionale arrays ontstaan.
  • Als we een tekenreeks met lengte nul ("") als scheidingsteken specificeren, worden alle items in de array aaneengeschakeld zonder scheidingstekens.

Interessante artikelen...