VBA Bergabung - Contoh Langkah-demi-Langkah dari Fungsi Gabungan VBA Excel

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

VBA Join - Contoh # 1

Misalkan, kita ingin menggabungkan nama depan (Ramesh), tengah (Kumar), dan nama 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:

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

Sekarang untuk mengeksekusi kodenya, kita akan tekan F5 lalu, kita bisa melihat bahwa folder bernama 'Items_Sold' telah dibuat dengan bantuan kode VBA di desktop.

Di dalam folder, ada 7 file unik yang dibuat dengan nama item tersebut dan kami hanya dapat mengetahui detail tentang item tersebut di file.

Laptop.xls

Hal yang Perlu Diingat Tentang Fungsi VBA JOIN

  • The SourceArray harus array satu dimensi. Kami tidak dapat merujuk ke sel individu, karena ini akan membuat beberapa array multi-dimensi.
  • Jika kita menetapkan string panjang-nol ("") sebagai pemisah, semua item dalam larik digabungkan tanpa pemisah.

Artikel yang menarik...