Liknar vad vi har i kalkylbladet som Sammankopplingsfunktion och kommandot & som används för att sammanfoga två eller fler än två strängar tillsammans, i VBA använder vi Kommandot Gå för att göra det, i Gå med i VBA tar vi källan till data i en matris och liknar sammankopplingen använder vi en avgränsare för att gå med i dem.
Excel VBA JOIN-funktion
Som själva namnet antyder används VBA JOIN-funktionen för att sammanfoga en matris av underlag med den angivna avgränsaren. Om vi inte anger någon avgränsare tar det "mellanslag" som ett standardavgränsningstecken. Det fungerar på samma sätt som funktionen Sammankoppla i Excel förutom att vi måste ange avgränsningstecken en gång, medan vi i Sammankopplingsfunktionen måste ange avgränsningstecken varje gång mellan varannan sträng.
Funktionens syntax är
Som vi kan se tar funktionen två argument och returnerar en sträng. Argument är:
- SourceArray : Vi måste ange eller hänvisa till en matris av underlag som ska sammanfogas.
- Avgränsare : Avgränsaren används för att separera var och en av understrängarna när den resulterande strängen skapas. Eftersom detta är ett valfritt argument, om vi utelämnar det, är avgränsaren inställd på att vara ett mellanslag "".
VBA SPLIT-funktionen är en exakt motsatt funktion av VBA JOIN-funktionen.
Exempel på VBA-anslutningsfunktion
Nedan följer exemplen på anslutningsfunktion i Excel VBA.
Du kan ladda ner denna Excel-mall för VBA-anslutningsfunktion här - VBA-funktion för Excel-mall för anslutningVBA Join - Exempel nr 1
Antag att vi vill gå med i första (Ramesh), mellan (Kumar) och efternamn (Mishra).
Stegen skulle vara:
- Först måste vi öppna den visuella grundredigeraren. Vi kan göra detsamma genom att klicka på 'Visual Basic' -kommandot i gruppen 'Kod' under fliken 'Utvecklare' excel eller så kan vi använda snabbkommandot excel Alt + F11 .
- Sätt in modulen genom att högerklicka på ett "ark 1" och välja "Infoga" -kommandot från snabbmenyn och välj sedan "Modul" för att infoga.
- Skapa en underrutin med namnet 'JoiningName'.
Koda:
Sub JoiningName () Avsluta Sub
- Använd JOIN-funktionen enligt följande
Koda:
Sub JoiningName () Range ("D2"). Värde = Join (Array ("Ramesh", "Kumar", "Mishra")) End Sub
Vi kan se att vi har använt ARRAY-funktionen för att tillhandahålla SourceArray till JOIN-funktionen och hoppat över för att ange avgränsningstecken så 'space' skulle vara standardtecken. Det bearbetade värdet för JOIN-funktionen kommer att skrivas i cell D2 när vi kör den här koden med F5-tangenten eller manuellt.
VBA Join - Exempel # 2
Anta att vi vill skapa olika excel-filer med artikelnamnet som endast innehåller försäljning för den artikeln.
- Öppna Visual Basic Editor med kortkommandot Alt + F11.
- Högerklicka på 'Sheet1' (Exempel 2) för att öppna snabbmenyn och klicka på 'Insert' för att infoga en VBA-modul i VBA-projektet.
- Definiera en underrutin med namnet 'CreateItemSoldFiles'.
Koda:
Sub CreateItemSoldFiles () Avsluta Sub
- Vi måste ställa in en referens till "Microsoft Scripting Runtime" -objektbiblioteket med hjälp av menyn Verktyg -> Referenser ..., eftersom vi kommer att använda någon kod (objekt), som inte fungerar om vi inte inkluderar detta objektbibliotek.
- Nu kommer vi att deklarera alla variabler.
Koda:
Dim FSO As New Scripting.FileSystemObject
Ovanstående FSO-variabel ger åtkomst till VBA FileSystemObject. Efter bindning kan vi använda funktioner som BuildPath, CopyFile, CreateTextFile, etc.
- Nästa uttalande skapar ett TextStream-objekt. Genom TextStream-objektet kan vi läsa från eller lägga till originalfilen.
Koda:
Dim FSO as New Scripting.FileSystemObject Dim ts As Scripting.TextStream
- Vi kommer att förklara fler variabler. 'r' är för att hålla rader i intervallet, 'fs' är för att lagra slutlig sammanfogad sträng, 'cols' för att lagra antal kolumner i intervallet, 'FolPath' för att lagra sökvägen för mappen så att vi kan spara filerna i mappen och 'Items_Sold' för att lagra olika artikelnamn för att skapa en fil med dessa namn.
Koda:
Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String
- För att räkna det totala antalet kolumner inom intervallet definierar vi följande uttalande.
Koda:
cols = Range ("A1"). CurrentRegion.Columns.Count
Detta uttalande väljer först den aktuella regionen för cell A1 och räknar sedan det totala antalet kolumner i den aktuella regionen.
- We will write the following statements for assigning the variable ‘FolPath’ a path using VBA ENVIRON function and Concatenation Operator.
Code:
FolPath = Environ("UserProfile") & "\Desktop\Items_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") & "\Desktop\Items_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
Now to execute the code, we will press F5 then, we can see that a folder named ‘Items_Sold’ has been created with the help of VBA code on the desktop.
In the folder, there are 7 unique files created with the names of the item and we can find out details about only that particular item in files.
Laptop.xls
Things to Remember About VBA JOIN Function
- The SourceArray must be a one-dimensional array. We cannot refer to an individual cell, as this will create multiple multi-dimensional arrays.
- If we specify a zero-length string (“”) as a delimiter, all items in the array are concatenated with no delimiters.