SERTAI VBA

Sama dengan apa yang kita miliki di lembar kerja seperti fungsi Concatenate dan perintah & yang digunakan untuk menggabungkan dua atau lebih dari dua rentetan bersama-sama, di VBA kita menggunakan perintah Join untuk melakukannya, dalam Join in VBA kita mengambil sumber data dalam array dan serupa dengan gabungan kami menggunakan pembatas untuk bergabung dengan mereka.

Fungsi GABUNGAN VBA Excel

Seperti namanya sendiri, fungsi VBA JOIN digunakan untuk menyatukan array substring dengan pembatas yang ditentukan. Sekiranya kita tidak menentukan pembatas, maka ia memerlukan 'ruang' sebagai watak pembatas lalai. Ia berfungsi sama seperti fungsi Concatenate di Excel kecuali kita harus menentukan watak pembatas sekali sahaja sedangkan, dalam fungsi Concatenate, kita harus menentukan watak pembatas setiap kali di antara setiap dua rentetan.

Sintaks fungsi adalah

Seperti yang kita lihat, fungsi ini mengambil dua argumen dan mengembalikan rentetan. Hujah adalah:

  1. SourceArray : Kita perlu menentukan atau memberikan rujukan kepada pelbagai substring yang akan disatukan.
  2. Pembatas : Pembatas digunakan untuk memisahkan setiap substring ketika membuat rentetan yang dihasilkan. Oleh kerana ini adalah argumen pilihan, jika kita menghilangkannya, pembatas ditetapkan sebagai ruang "".

Fungsi VBA SPLIT adalah fungsi bertentangan dengan fungsi VBA JOIN.

Contoh Fungsi Gabung VBA

Berikut adalah contoh fungsi bergabung dalam Excel VBA.

Anda boleh memuat turun Templat VBA Join Function Excel ini di sini - Templat VBA Join Function Excel

Sambungan VBA - Contoh # 1

Andaikan, kita mahu bergabung dengan nama pertama (Ramesh), tengah (Kumar), dan nama belakang (Mishra).

Langkahnya adalah:

  • Pertama, kita perlu membuka editor asas visual. Kita boleh melakukan perkara yang sama dengan mengklik perintah 'Visual Basic' dalam kumpulan 'Code' di bawah tab 'Developer' excel atau kita boleh menggunakan kunci pintasan excel Alt + F11 .

  • Masukkan modul dengan mengklik kanan pada "helaian 1" dan memilih perintah "Masukkan" dari menu kontekstual dan kemudian pilih 'Modul' untuk dimasukkan.

  • Buat subrutin bernama 'JoiningName'.

Kod:

 Sub JoiningName () End Sub

  • Gunakan fungsi GABUNG seperti berikut

Kod:

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

Kita dapat melihat bahawa kita telah menggunakan fungsi ARRAY untuk menyediakan SourceArray ke fungsi JOIN dan dilangkau untuk menentukan watak pembatas sehingga 'spasi' akan menjadi watak lalai. Nilai yang diproses dari fungsi JOIN akan ditulis dalam sel D2 apabila kita melaksanakan kod ini menggunakan kunci F5 atau secara manual.

Sambungan VBA - Contoh # 2

Katakanlah, kami ingin membuat pelbagai fail excel dengan nama item yang mengandungi penjualan hanya untuk item tersebut.

  • Buka Editor Visual Basic menggunakan kekunci pintas Alt + F11.
  • Klik kanan pada helaian 'Sheet1 ′ (Contoh 2)' untuk membuka menu kontekstual dan klik pada 'Insert' untuk memasukkan VBA 'Module' dalam projek VBA.

  • Tentukan subrutin bernama 'CreateItemSoldFiles'.

Kod:

 Sub CreateItemSoldFiles () Akhir Sub

  • Kita perlu menetapkan referensi ke perpustakaan objek 'Microsoft Scripting Runtime' menggunakan menu Tools -> References ... perintah, kerana kita akan menggunakan beberapa kod (objek), yang tidak akan berfungsi jika kita tidak memasukkan perpustakaan objek ini.

  • Sekarang kita akan menyatakan semua pemboleh ubah.

Kod:

 Dim FSO Sebagai Skrip Baru.FileSystemObject

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

  • Pernyataan seterusnya membuat objek TextStream. Melalui objek TextStream, kita dapat membaca dari atau menambahkan ke fail asalnya.

Kod:

 Dim FSO Sebagai Skrip Baru.FileSystemObject Dim ts Sebagai Skrip.TextStream

  • Kami akan menyatakan lebih banyak pemboleh ubah. 'r' adalah untuk menahan baris dalam rentang, 'fs' adalah untuk menyimpan rentetan bergabung akhir, 'cols' untuk menyimpan bilangan lajur dalam julat, 'FolPath' untuk menyimpan jalur folder sehingga kita dapat menyimpan fail dalam folder dan 'Item_Sold' untuk menyimpan pelbagai nama item untuk membuat fail dengan nama-nama ini.

Kod:

 Dim r Sebagai Julat Dim fs Sebagai String Dim cols Sebagai Integer Dim FolPath Sebagai String

  • Untuk mengira jumlah lajur dalam julat, kami akan menentukan pernyataan berikut.

Kod:

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

Penyataan ini terlebih dahulu akan memilih kawasan semasa untuk sel A1 dan kemudian akan mengira jumlah lajur di wilayah semasa.

  • Kami akan menulis pernyataan berikut untuk menetapkan pemboleh ubah 'FolPath' jalan menggunakan fungsi VBA ENVIRON dan Concatenation Operator.

Kod:

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

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

  • Kod ini akan menetapkan nilai lajur B satu persatu ke 'Item_Sold' Kami telah menggunakan 'fungsi OFFSET' untuk mendapatkan rujukan sel di lajur B kerana sel yang dipilih saat ini ada di lajur A.

Kod:

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

  • Pernyataan bersempadan berikut akan membuka fail dengan nama yang tersimpan dalam pemboleh ubah 'Item_Sold' dalam satu persatu dalam mod penambahan (nilai baru akan ditambahkan akhirnya).

Kod:

 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.