Bagaimana cara mengubah pembatas di excel vba?

Saat menggunakan alat yang tersedia dari pita untuk mengekspor lembar kerja, sebagai file CSV, Arkadiusz mencatat bahwa dia dapat menentukan bahwa dia ingin menggunakan titik koma (;) sebagai pembatas bidang. Namun, jika dia menyimpan file CSV menggunakan makro (FileFormat. =xlCSV atau xlCSVWindows), maka dia tidak dapat menentukan titik koma sebagai pembatas

Ini bekerja dengan cara ini dengan desain di VBA. Implementasi Excel dari rutin ekspor untuk VBA selalu menggunakan apa pun pengaturan regional Windows untuk menentukan bagaimana item dalam CSV harus dipisahkan. Khususnya, rutin melihat bidang Pemisah Daftar untuk pembatas. Ini berarti Anda dapat, jika diinginkan, mengubah pembatas menjadi titik koma dengan mengubah pengaturan Pemisah Daftar di konfigurasi pengaturan regional Anda

Jika Anda tidak ingin mengubah pengaturan regional, Anda dapat menulis makro Anda sendiri yang akan menampilkan file dengan cara apa pun yang Anda inginkan. Pertimbangkan, sejenak, makro berikut, yang akan menampilkan file

Sub CreateFile()
    Dim sFName As String
    Dim Rows As Long
    Dim Cols As Long
    Dim J As Long
    Dim K As Long
    Dim sTemp As String
    Dim sSep As String

    sSep = ";"  'Specify the separator to be used

    sFName = ActiveWorkbook.FullName
    If Right(sFName, 5) = ".xlsx" Then
        sFName = Mid(sFName, 1, Len(sFName) - 5)
        sFName = sFName & ".txt"
        Open sFName For Output As 1

        With ActiveSheet
            'Number of rows to export is based on the contents
            'of column B. If it should be based on a different
            'column, change the following line to reflect the
            'column desired.
            Rows = .Cells(.Rows.Count, "B").End(xlUp).Row
            For J = 1 To Rows
                sTemp = ""
                Cols = .Cells(J, .Columns.Count).End(xlToLeft).Column
                For K = 2 To Cols
                    sTemp = sTemp & .Cells(J, K).Value
                    If K < Cols Then sTemp = sTemp & sSep
                Next
                Print #1, sTemp
            Next J
        End With

        Close 1

        sTemp = "There were " & Rows & " rows of data written "
        sTemp = sTemp & "to this file:" & vbCrLf & sFName
    Else
        sTemp = "This macro needs to be run on a workbook "
        sTemp = sTemp & "stored in the XLSX format."
    End If

    MsgBox sTemp
End Sub

Makro ini membuka file teks yang memiliki nama yang sama dengan buku kerja Anda. Kemudian langkah melalui setiap baris dan mulai menyusun string isi sel. (Ini dimasukkan ke dalam variabel sTemp. ) Setiap sel memiliki titik koma yang ditempatkan di antara sel tersebut, seperti yang ditentukan oleh variabel sSep. Nilai gabungan setiap baris disimpan dalam file teks, dan setelah selesai file teks ditutup. Rutinnya sangat cepat, dan setelah selesai akan menampilkan pesan yang menunjukkan berapa banyak baris yang diekspor ke file

Fungsi VBA Split yang digunakan adalah untuk membagi string teks menjadi array. Teks dibagi berdasarkan pembatas yang diberikan – e. g. koma, spasi, titik dua dll

Misalnya, bayangkan kita memiliki string berikut

"Apel. Oranye. Pir. Prem"

Anda dapat melihat bahwa setiap item dipisahkan oleh tanda titik dua. Kami menyebut tanda titik dua sebagai pembatas

Kita dapat membagi string ini menjadi sebuah array

' https://excelmacromastery.com/
Sub SplitBasic()

    Dim arr() As String
    arr = Split("John:Jane:Paul:Sophie", ":")

End Sub

Setelah berada dalam array, mudah untuk mengakses setiap item

Glosarium

Array – struktur untuk menyimpan sekelompok variabel serupa

– fungsi ini memberikan posisi terakhir dari sebuah array

– fungsi ini memberikan posisi pertama dari sebuah array. Untuk larik, yang dikembalikan oleh fungsi Split, posisi pertama adalah nol

– fungsi ini digunakan untuk mencari string di dalam string dan mengembalikan posisinya

– sama seperti Instr tetapi mencari string dari akhir

Pisahkan Sintaks

Pisahkan ekspresi, pembatas[opsional], batas[opsional], bandingkan[opsional]

Nilai Pengembalian Terpisah

Fungsi Split mengembalikan array

Membagi Parameter Fungsi

ekspresi – ini adalah string teks yang akan dipisahkan

pembatas [opsional] – pembatas karakter yang digunakan dalam string untuk memisahkan setiap item. Jika Anda tidak menggunakan parameter ini maka VBA menggunakan spasi sebagai pembatas

limit [opsional] – ini memungkinkan kita menyetel jumlah item dalam hasil. Jika kita menggunakan 1 sebagai batas maka tidak ada pemisahan yang terjadi dan string penuh dikembalikan

bandingkan [opsional] – jika kita menggunakan huruf sebagai pembatas maka ini menentukan apakah kita mempertimbangkan kasus huruf

VBA Split – Contoh Sederhana

Kode berikut menunjukkan contoh penggunaan fungsi Split dan mencetak hasilnya ke Jendela Segera

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Keluaran
Yohanes
Jane
Paulus
Sophie

Saat kami membagi string menjadi array, kami memiliki item di setiap posisi dalam array. Ini berarti kita dapat dengan mudah mengakses item apapun menggunakan posisi array

' https://excelmacromastery.com/
Sub VBA_Split_PrintArr()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    Debug.Print arr(0) ' Print John
    Debug.Print arr(1) ' Print Jane
    Debug.Print arr(2) ' Print Paul
    Debug.Print arr(3) ' Print Sophie

End Sub

Split mengembalikan array yang dimulai dari posisi nol. Jika kita ingin menggunakan pernyataan For untuk membaca array kita dapat menggunakan LBound dan UBound untuk memberi kita posisi pertama dan terakhir dari array

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i)
    Next

End Sub

Parameter Batas Terpisah

Parameter Limit digunakan untuk menentukan bagaimana item ditempatkan dalam array. Dengan kata lain, berapa banyak item yang dipecah menjadi string asli

Tabel di bawah menunjukkan hasil penggunaan batas yang berbeda dari string sampel ini

Anda dapat mencoba sendiri kodenya

' https://excelmacromastery.com/
Sub VBA_Split_Limit()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":", 1)

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Pisahkan Bandingkan Parameter

Parameter Bandingkan digunakan untuk pembatas yang terdiri dari satu atau huruf

Misalnya, bayangkan kita ingin menggunakan huruf x sebagai pembatas pada string berikut

“12x34X45x”

1. Jika kita ingin membagi dengan x hanya dengan huruf kecil – maka kita menggunakan vbBinaryCompare
2. Jika kita ingin memisahkan dengan huruf besar atau kecil x – maka kita menggunakan vbTextCompare
3. vbUseCompareOption adalah default dan memberi tahu split untuk menggunakan modul Bandingkan pengaturan. Baca lebih lanjut tentang pengaturan perbandingan modul

Kode berikut menunjukkan bagaimana kita menggunakan parameter Bandingkan

' https://excelmacromastery.com/
Sub VBA_Split_Compare()

    Dim arr() As String
    ' Split the string to an array - not case sensitive
    arr = Split("12x34X45", "x", , vbTextCompare)

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Tabel berikut menunjukkan hasil dari berbagai argumen Bandingkan

Membalikkan Pemisahan

Kita dapat menggunakan fungsi Join untuk melakukan kebalikan dari fungsi split. Join mengonversi array menjadi string dan menambahkan pembatas yang diberikan

Ini bisa berguna karena terkadang kita mungkin ingin memisahkan sebuah string, memperbarui satu atau lebih nilai dalam array dan kemudian mengonversi array menjadi string

Contoh ini menunjukkan cara menggunakan Gabung

' https://excelmacromastery.com/
Sub UsingJoin()

    Dim arr As Variant
    
    ' create an array using the array function
    arr = Array("Apple", "Orange", "Pear")
    
    Dim s As String
    ' Covert the array to a string using the colon delimiter
    s = Join(arr, ":")
    
    ' Print the string to the Immediate Window(Ctrl + G)
    Debug.Print s

End Sub

Lihat bagian “Contoh Pemisahan – Menggunakan Penggabungan” untuk contoh penggunaan fungsi Penggabungan dengan fungsi Pemisahan

Contoh Terpisah – Nama

Contoh Split yang sangat bagus adalah ketika berhadapan dengan nama

Bayangkan kita memiliki nama "John Henry Smith" dan kita ingin mengekstrak setiap nama

Kita bisa menggunakan Left dan Instr untuk mendapatkan nama depan

' https://excelmacromastery.com/
Sub Instr_Firstname()
    
    Dim s As String
    s = "John Henry Smith"
    
    ' Get the position of the first space
    Dim position As Long
    position = InStr(s, " ") - 1

    ' Prints John
    Debug.Print Left(s, position)

End Sub

Untuk mendapatkan nama belakang sedikit lebih rumit

' https://excelmacromastery.com/
Sub Instr_Lastname()
    
    Dim s As String
    s = "John Henry Smith"
    
    ' Get the position of the last space
    Dim position As Long, length As Long
    position = InStrRev(s, " ") - 1
    length = Len(s)
    
    ' Prints Smith
    Debug.Print Right(s, length - position)

End Sub

Mendapatkan nama yang tidak berada di posisi pertama atau terakhir memang sangat berantakan. Namun, dengan menggunakan Split, kami dapat menyederhanakan seluruh proses

' https://excelmacromastery.com/
Sub SplitName()

    Dim s As String: s = "John Henry Smith"
    
    Dim arr() As String
    
    arr = Split(s, " ")
    Debug.Print arr(0) ' John
    Debug.Print arr(1) ' Henry
    Debug.Print arr(2) ' Smith

End Sub

Kita sebenarnya tidak perlu menggunakan array seperti yang bisa kita lihat pada contoh berikut. Tidaklah efisien untuk memanggil fungsi Split 3 kali, bukan 1, tetapi terlihat bagus dalam contoh ini

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Contoh Terpisah – Nama file

Pada contoh berikutnya kami menggunakan Split untuk mendapatkan bagian ekstensi dari nama file

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Ini salah satu yang menarik yang bisa Anda coba sendiri. Diberi nama file lengkap, coba tulis kode untuk mengekstrak nama file tanpa ekstensi atau folder

Misalnya untuk “C. \MyDocs\Jan\MyResume. Doc” kami ingin mengekstrak MyResume

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Keluaran

Resume Saya
lagu cinta
savebattle

Contoh Terpisah – Salin ke Rentang

Karena hasil dari Split adalah sebuah array, kita dengan mudah menyalinnya ke sebuah range

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Contoh Terpisah – Hitung Item

Jika kami ingin menghitung jumlah item dalam string yang dibatasi, kami dapat menggunakan Split untuk melakukannya untuk kami

Kami cukup membagi string dan kemudian menggunakan fungsi Ubound untuk mengembalikan jumlah item. Kami sudah melihat bahwa UBound digunakan untuk mengembalikan posisi terakhir dalam array. Karena, array mulai dari nol, kita perlu menambahkan satu untuk mendapatkan jumlah item

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Contoh Terpisah – Menggunakan Gabung

Ini adalah salah satu yang menarik yang mungkin ingin Anda coba sendiri. Ambil tiga string di bawah ini

123. 3456. 96. 345
1234. 1156. 7. 345
1273. 9998. 123. 345

Kami ingin menambahkan satu ke angka ketiga di setiap string. Misalnya, string pertama harus menjadi  123. 3456. 97. 345

Sebelum Anda mencoba sendiri, saya akan memberikan satu petunjuk. Anda dapat menggunakan fungsi Gabung untuk membalikkan operasi Pemisahan. Dibutuhkan array dan pembatas dan membuat string

Anda bisa mulai dengan kode ini

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Ini adalah cara untuk melakukannya

' https://excelmacromastery.com/
Sub VBA_Split_Print()

    Dim arr() As String
    ' Split the string to an array
    arr = Split("John:Jane:Paul:Sophie", ":")

    ' Print each item in the array to the Immediate Window(Ctrl + G)
    Dim name As Variant
    For Each name In arr
        Debug.Print name
    Next

End Sub

Keluaran

123. 3456. 100. 345
1234. 1156. 8. 345
1273. 9998. 124. 345

Bacaan lebih lanjut

Panduan Utama untuk Fungsi String VBA

Mengekstrak menggunakan fungsi Split

Array VBA

VBA Untuk Putaran

Microsoft Docs – Fungsi terpisah

Jika Anda ingin melihat beberapa contoh dunia nyata menggunakan Split, Anda akan menemukannya di pos Cara Mudah Mengekstrak Dari Setiap String Tanpa Menggunakan VBA InStr

Apa berikutnya?

Tutorial VBA Gratis Jika Anda baru mengenal VBA atau ingin mempertajam keterampilan VBA yang ada, mengapa tidak mencoba Tutorial VBA Utama

Pelatihan Terkait. Dapatkan akses penuh ke webinar pelatihan Excel VBA dan semua tutorialnya

(CATATAN. Berencana membangun atau mengelola Aplikasi VBA? . )

Bagaimana Anda membatasi di Excel VBA?

Bagaimana cara mengubah pembatas di Excel?

Apa pembatas untuk fungsi split di VBA?

Bagaimana cara mengubah pembatas default dalam file CSV?