VBA JOIN

Podobnie do tego, co mamy w arkuszu jako funkcja Concatenate i polecenie &, które służy do łączenia dwóch lub więcej niż dwóch ciągów razem, w VBA używamy do tego polecenia Join, w Join in VBA pobieramy źródło danych w tablicy i podobnie jak w przypadku konkatenacji używamy separatora, aby do nich dołączyć.

Funkcja Excel VBA JOIN

Jak sama nazwa wskazuje, funkcja VBA JOIN służy do łączenia tablicy podciągów z określonym ogranicznikiem. Jeśli nie określimy żadnego separatora, wówczas jako domyślny separator przyjmuje „spację”. Działa tak samo, jak funkcja Concatenate w programie Excel, z wyjątkiem tego, że musimy określić znak separatora tylko raz, podczas gdy w funkcji Concatenate musimy określić znak separatora za każdym razem między dwoma ciągami.

Składnia funkcji to

Jak widać, funkcja przyjmuje dwa argumenty i zwraca ciąg. Argumentami są:

  1. SourceArray : Musimy określić lub podać odniesienie do tablicy podciągów, które mają zostać połączone.
  2. Separator : separator służy do oddzielania każdego z podciągów podczas tworzenia wynikowego ciągu. Ponieważ jest to argument opcjonalny, jeśli go pominiemy, jako separator zostanie ustawiona spacja ””.

Funkcja VBA SPLIT jest dokładnym przeciwieństwem funkcji VBA JOIN.

Przykłady funkcji łączenia VBA

Poniżej znajdują się przykłady funkcji łączenia w Excel VBA.

Możesz pobrać ten szablon programu Excel z funkcją dołączania do VBA tutaj - szablon programu Excel z funkcją dołączania do VBA

VBA Join - przykład # 1

Załóżmy, że chcemy dołączyć do pierwszego (Ramesh), środkowego (Kumar) i nazwiska (Mishra).

Kroki byłyby następujące:

  • Najpierw musimy otworzyć edytor Visual Basic. Możemy zrobić to samo, klikając polecenie „Visual Basic” w grupie „Kod” w zakładce „Deweloper” programu Excel lub możemy użyć klawisza skrótu programu Excel Alt + F11 .

  • Wstaw moduł, klikając prawym przyciskiem myszy „arkusz 1” i wybierając polecenie „Wstaw” z menu kontekstowego, a następnie wybierz „Moduł”, aby wstawić.

  • Utwórz podprogram o nazwie „JoiningName”.

Kod:

 Sub JoiningName () End Sub

  • Użyj funkcji JOIN w następujący sposób

Kod:

 Sub JoiningName () Range ("D2"). Value = Join (Array ("Ramesh", "Kumar", "Mishra")) End Sub

Widzimy, że użyliśmy funkcji ARRAY, aby dostarczyć SourceArray do funkcji JOIN i pominięto określenie znaku ogranicznika, więc spacja byłaby znakiem domyślnym. Przetwarzana wartość funkcji JOIN zostanie zapisana w komórce D2, gdy wykonamy ten kod za pomocą klawisza F5 lub ręcznie.

Połączenie VBA - przykład nr 2

Załóżmy, że chcemy utworzyć różne pliki programu Excel z nazwą przedmiotu zawierającą sprzedaż tylko dla tego przedmiotu.

  • Otwórz Edytor Visual Basic za pomocą klawisza skrótu Alt + F11.
  • Kliknij prawym przyciskiem myszy arkusz „Arkusz1” (Przykład 2) ”, aby otworzyć menu kontekstowe i kliknij„ Wstaw ”, aby wstawić„ Moduł ”VBA do projektu VBA.

  • Zdefiniuj podprogram o nazwie „CreateItemSoldFiles”.

Kod:

 Sub CreateItemSoldFiles () End Sub

  • Musimy ustawić odniesienie do biblioteki obiektów „Microsoft Scripting Runtime” za pomocą polecenia menu Narzędzia -> References…, ponieważ użyjemy kodu (obiektów), który nie będzie działał, jeśli nie włączymy tej biblioteki obiektów.

  • Teraz zadeklarujemy wszystkie zmienne.

Kod:

 Dim FSO As New Scripting.FileSystemObject

Powyższa zmienna FSO daje dostęp do VBA FileSystemObject. Po powiązaniu możemy skorzystać z funkcji takich jak BuildPath, CopyFile, CreateTextFile itp.

  • Następna instrukcja tworzy obiekt TextStream. Poprzez obiekt TextStream możemy czytać z oryginalnego pliku lub dołączać do niego.

Kod:

 Dim FSO As New Scripting.FileSystemObject Dim ts As Scripting.TextStream

  • Zadeklarujemy więcej zmiennych. „r” służy do przechowywania wierszy w zakresie, „fs” służy do przechowywania końcowego połączonego ciągu, „cols” do przechowywania numerów kolumn w zakresie, „FolPath” do przechowywania ścieżki do folderu, abyśmy mogli zapisać pliki w folderze i „Items_Sold” do przechowywania różnych nazw elementów, aby utworzyć plik o tych nazwach.

Kod:

 Dim r As Range Dim fs As String Dim cols As Integer Dim FolPath As String Dim Items_Sold As String

  • Aby policzyć całkowitą liczbę kolumn w zakresie, zdefiniujemy następującą instrukcję.

Kod:

cols = Range („A1”). CurrentRegion.Columns.Count

Ta instrukcja najpierw wybierze bieżący region dla komórki A1, a następnie policzy całkowitą liczbę kolumn w bieżącym regionie.

  • 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.