VBA BERGABUNG

Mirip dengan apa yang kami miliki di lembar kerja sebagai fungsi Concatenate dan perintah & yang digunakan untuk menggabungkan dua atau lebih dari dua string bersama-sama, di VBA kami menggunakan perintah Gabung untuk melakukannya, di Gabung di VBA kami mengambil sumber data dalam array dan mirip dengan penggabungan, kami menggunakan pembatas untuk menggabungkannya.

Fungsi Excel VBA JOIN

Seperti namanya, fungsi VBA JOIN digunakan untuk menggabungkan array substring dengan pembatas yang ditentukan. Jika kita tidak menentukan pembatas apapun, maka dibutuhkan 'spasi' sebagai karakter pembatas default. Itu melakukan pekerjaan yang sama seperti fungsi Concatenate di Excel kecuali kita harus menentukan karakter pembatas satu kali saja sedangkan, dalam fungsi Concatenate, kita harus menentukan karakter pembatas setiap kali di antara setiap dua string.

Sintaks fungsinya adalah

Seperti yang bisa kita lihat, fungsi tersebut mengambil dua argumen dan mengembalikan sebuah string. Argumennya adalah:

  1. SourceArray : Kita perlu menentukan atau memberikan referensi ke array substring yang akan digabungkan.
  2. Pembatas : Pembatas digunakan untuk memisahkan setiap substring saat membuat string yang dihasilkan. Karena ini adalah argumen opsional, jika kita menghilangkannya, pembatas disetel menjadi spasi ”“.

Fungsi VBA SPLIT adalah fungsi kebalikan dari fungsi VBA JOIN.

Contoh Fungsi Bergabung VBA

Di bawah ini adalah contoh fungsi join di Excel VBA.

Anda dapat mendownload Template VBA Join Function Excel ini di sini - VBA Join Function Excel Template

VBA Join - Contoh # 1

Misalkan, kita ingin menggabungkan nama depan (Ramesh), tengah (Kumar), dan belakang (Mishra).

Langkah-langkahnya adalah:

  • Pertama, kita perlu membuka editor visual basic. Kita dapat melakukan hal yang sama dengan mengklik perintah 'Visual Basic' di grup 'Kode' di bawah tab 'Pengembang' excel atau kita dapat menggunakan tombol pintas excel Alt + F11 .

  • Masukkan modul dengan mengklik kanan pada "lembar 1" dan memilih perintah 'Sisipkan' dari menu kontekstual, lalu pilih 'Modul' untuk disisipkan.

  • Buat subrutin bernama 'JoiningName'.

Kode:

 Sub JoiningName () End Sub

  • Gunakan fungsi JOIN sebagai berikut

Kode:

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

Kita dapat melihat bahwa kita telah menggunakan fungsi ARRAY untuk menyediakan SourceArray ke fungsi JOIN dan dilewati untuk menentukan karakter pembatas sehingga 'spasi' akan menjadi karakter default. Nilai yang diproses dari fungsi JOIN akan ditulis di sel D2 saat kita menjalankan kode ini menggunakan tombol F5 atau secara manual.

VBA Join - Contoh # 2

Misalkan kita ingin membuat berbagai file excel dengan nama item yang berisi penjualan hanya untuk item tersebut.

  • Buka Editor Visual Basic menggunakan tombol pintas Alt + F11.
  • Klik kanan pada lembar 'Sheet1 ′ (Contoh 2)' untuk membuka menu kontekstual dan klik 'Sisipkan' untuk memasukkan 'Modul' VBA dalam proyek VBA.

  • Tentukan subrutin bernama 'CreateItemSoldFiles'.

Kode:

 Sub CreateItemSoldFiles () End Sub

  • Kita perlu menyetel referensi ke pustaka objek 'Microsoft Scripting Runtime' menggunakan perintah menu Alat -> Referensi…, karena kami akan menggunakan beberapa kode (objek), yang tidak akan berfungsi jika kami tidak menyertakan pustaka objek ini.

  • Sekarang kita akan mendeklarasikan semua variabel.

Kode:

 Redupkan FSO Sebagai Skrip Baru.FileSystemObject

Variabel FSO di atas memberikan akses ke VBA FileSystemObject. Setelah mengikat, kita dapat menggunakan fungsi seperti BuildPath, CopyFile, CreateTextFile, dll.

  • Pernyataan berikutnya membuat objek TextStream. Melalui objek TextStream, kita dapat membaca dari atau menambahkan file aslinya.

Kode:

 Redupkan FSO Sebagai New Scripting.FileSystemObject Dim ts Sebagai Scripting.TextStream

  • Kami akan mendeklarasikan lebih banyak variabel. 'r' untuk menahan baris dalam range, 'fs' untuk menyimpan string gabungan terakhir, 'cols' untuk menyimpan jumlah kolom dalam range, 'FolPath' untuk menyimpan path folder sehingga kita dapat menyimpan file di folder dan 'Item_Sold' untuk menyimpan berbagai nama item untuk membuat file dengan nama ini.

Kode:

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

  • Untuk menghitung jumlah kolom dalam jangkauan, kami akan menentukan pernyataan berikut.

Kode:

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

Pernyataan ini pertama-tama akan memilih wilayah saat ini untuk sel A1 dan kemudian akan menghitung jumlah total kolom di wilayah saat ini.

  • Kami akan menulis pernyataan berikut untuk menetapkan variabel 'FolPath' jalur menggunakan fungsi VBA ENVIRON dan Operator Penggabungan.

Kode:

FolPath = Environ ("UserProfile") & "\ Desktop \ Items_Sold" Jika Tidak FSO.FolderExists (FolPath) Kemudian FSO.CreateFolder FolPath

Pernyataan kedua akan membuat folder jika folder tersebut tidak ada di lokasi yang sama.

  • Kode ini akan menetapkan nilai kolom B satu per satu ke 'Item_Sold' Kami telah menggunakan 'fungsi OFFSET' untuk mendapatkan referensi sel di kolom B karena sel yang saat ini dipilih ada di kolom A.

Kode:

Item_Sold = r.Offset (0, 1) .Nilai

  • Pernyataan berbatasan berikut akan membuka file dengan nama yang disimpan dalam variabel 'Item_Sold' satu per satu dalam mode penambahan (nilai baru akan ditambahkan pada akhirnya).

Kode:

 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.