Bagaimana cara menggunakan rentang dan sel di vba?

Pengantar Range dan Sel di VBA

Saat Anda melihat-lihat buku kerja Excel, Anda akan melihat bahwa semuanya berfungsi di sekitar sel. Sel dan rentang sel adalah tempat Anda menyimpan data dan semuanya dimulai.

Untuk mendapatkan hasil maksimal dari VBA, Anda perlu mempelajari cara menggunakan sel dan rentang dalam kode Anda. Untuk ini, Anda perlu memiliki pemahaman yang kuat tentang objek Range. Dengan menggunakannya, Anda bisa merujuk ke sel dalam kode Anda dengan cara berikut:

• Sebuah sel tunggal.
• Berbagai sel
• Sebuah baris atau kolom
• Rentang tiga dimensi

RANGE OBJECT adalah bagian dari hierarki objek Excel: Aplikasi ➜ Buku Kerja ➜ Lembar Kerja ➜ Rentang dan tempat lain di dalam lembar kerja. Jadi jika Anda menulis kode untuk merujuk ke objek RANGE, maka akan menjadi seperti ini:

 Application.Workbook(“Workbook-Name”).Worksheets(“Sheet-Name”).Range

Dengan merujuk ke sel atau rentang sel, Anda dapat melakukan hal berikut:

• Anda dapat membaca nilainya.
• Anda dapat memasukkan nilai di sana.
• Dan, Anda dapat melakukan perubahan pada formatnya.

Untuk melakukan semua hal ini, Anda perlu mempelajari cara merujuk ke sel atau rentang sel, dan di bagian selanjutnya dari tutorial ini, Anda akan mempelajari cara merujuk ke sel dengan berbagai cara. Untuk merujuk ke sel atau rentang sel, Anda bisa menggunakan tiga metode berbeda.

• Properti pantai
• Properti sel
• Properti Offset

Ya, mana yang terbaik tergantung pada kebutuhan Anda, tetapi ada baiknya mempelajari ketiganya agar Anda dapat memilih salah satu yang paling cocok untuk Anda.

Jadi, mari kita mulai.

Properti pantai

Properti Range adalah cara paling umum dan populer untuk merujuk ke rentang dalam kode VBA Anda. Dengan properti Range, Anda cukup merujuk ke alamat sel. Izinkan saya memberi tahu Anda sintaksnya.

 expression.range(address)

Di sini ekspresi adalah variabel yang mewakili objek VBA. Jadi jika Anda perlu merujuk ke sel A1, baris kode yang perlu Anda tulis adalah:

 Application.Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

Kode di atas memberitahu VBA bahwa Anda merujuk ke sel A1 yang ada di lembar kerja “Sheet1” dan buku kerja “Book1”.

Catatan: Setiap kali Anda memasukkan alamat sel di objek rentang, pastikan untuk mengapitnya dengan tanda kutip ganda. Tapi ada sesuatu yang perlu dipahami. Karena Anda menggunakan VBA di Excel, tidak perlu menggunakan kata “Aplikasi”. Jadi kodenya adalah:

 Workbook(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

Dan jika Anda berada di Buku1, Anda dapat mengurangi kode Anda lebih lanjut:

 Worksheets(“Sheet1”).Range(“A1”)

Namun, jika Anda sudah berada di lembar kerja “Sheet1”, Anda dapat memperkecil kode Anda lebih lanjut dan hanya dapat menggunakan:

 Range(“A1”)

Sekarang misalkan Anda ingin merujuk ke seluruh rentang sel (yaitu beberapa sel), Anda perlu menulis kode seperti ini:

 Range("A1:A5")

Pada kode di atas, Anda mengacu pada rentang A1 hingga A5 yang terdiri dari lima sel. Anda juga dapat merujuk ke rentang bernama menggunakan objek rentang. Misalkan Anda memiliki rentang bernama dengan nama “Diskon Penjualan” untuk merujuknya, Anda dapat menulis kode seperti ini:

 Range("Sales Discount")

Jika Anda ingin merujuk ke rentang non-kontinyu, Anda perlu melakukan sesuatu seperti ini:

 Range("A1:B5,D5:G10")

Dan jika Anda ingin merujuk ke seluruh baris atau kolom, Anda perlu memasukkan kode seperti di bawah ini:

 Range("1:1") Range("A:A")

Pada titik ini, Anda memahami dengan jelas cara merujuk ke sel dan rentang sel. Namun untuk menjadi yang terbaik dalam menggunakannya, Anda perlu mempelajari cara menggunakannya untuk melakukan hal lain.

1. Pilih dan aktifkan sel

Jika Anda ingin memilih sel, Anda dapat menggunakan rentang. Pilih metodenya. Katakanlah jika Anda ingin memilih sel A5, yang perlu Anda lakukan hanyalah menentukan rentangnya lalu menambahkan “.Select” setelah itu.

 Range(“A1”).Select

Kode ini memberitahu VBA untuk memilih sel A5 dan jika Anda ingin memilih rentang sel, cukup rujuk rentang tersebut dan tambahkan “.Pilih” setelah itu.

 Range(“A1:A5”).Select

Ada juga metode lain yang bisa Anda gunakan untuk mengaktifkan sel.

 Range(“A1”).Activate

Di sini perlu Anda ingat bahwa Anda hanya dapat mengaktifkan satu sel dalam satu waktu. Meskipun Anda menentukan rentang dengan metode “.Activate ” , rentang tersebut akan dipilih tetapi sel yang aktif akan menjadi sel pertama dalam rentang tersebut.

2. Masukkan nilai dalam sel

Dengan menggunakan properti rentang, Anda dapat memasukkan nilai ke dalam sel atau rentang sel. Mari kita pahami cara kerjanya menggunakan contoh sederhana:

 Range("A1").Value = "Exceladvisor"

Dalam contoh di atas, Anda menentukan A1 sebagai rentang dan setelah itu Anda menambahkan ” .Value ” yang memberitahu VBA untuk mengakses properti nilai sel.

Hal berikutnya yang Anda miliki adalah tanda sama dengan dan kemudian nilai yang ingin Anda masukkan (Anda harus menggunakan tanda kutip ganda jika Anda memasukkan nilai teks). Untuk nomor, kodenya akan seperti ini:

 Range("A1").Value = 9988

Dan jika Anda ingin memasukkan nilai dalam rentang sel, maksud saya beberapa sel, maka yang perlu Anda lakukan hanyalah menentukan rentang tersebut.

 Range("A1:A5").Value = "Exceladvisor"

Dan berikut adalah kodenya jika mengacu pada rentang non-kontinyu.

 Range("A1:A5 , E2:E3").Value = "Exceladvisor"

3. Salin dan tempel sel/rentang

Dengan properti Range, Anda dapat menggunakan metode “.Copy” untuk menyalin sel lalu menempelkannya ke sel tujuan. Katakanlah Anda perlu menyalin sel A5, kode yang sesuai adalah:

 Range("A5").Copy

Saat Anda menjalankan kode ini, kode ini hanya menyalin sel A5, namun langkah selanjutnya adalah menempelkan sel yang disalin ini ke sel tujuan. Untuk melakukan ini, Anda harus menambahkan kata kunci tujuan setelahnya dan diikuti oleh sel tempat Anda ingin menempelkannya. Jadi, jika Anda ingin menyalin sel A1 lalu menempelkannya ke sel E5, kodenya adalah:

 Range("A1").Copy Destination:=Range("E5")

Demikian pula, jika Anda berurusan dengan rentang beberapa sel, kodenya akan terlihat seperti ini:

 Range("A1:A5").Copy Destination:=Range("E5:E9")

Jika Anda menyalin rentang sel dan kemudian menyebutkan sel sebagai rentang tujuan, VBA akan menyalin seluruh rentang yang disalin dari sel yang Anda tentukan sebagai tujuan.

 Range("A1:A5").Copy Destination:=Range("B1")

Saat Anda menjalankan kode di atas, VBA menyalin rentang A1:A5 dan menempelkannya ke B1:B5 meskipun Anda hanya menyebutkan B1 sebagai rentang tujuan.

Tip: Sama seperti metode “.Copy”, Anda dapat menggunakan metode “.Cut” untuk memotong sel, lalu cukup gunakan tujuan untuk menempelkannya.

4. Gunakan properti Font dengan properti Range

Dengan properti range, Anda dapat mengakses properti font sel yang membantu Anda mengubah semua pengaturan font. Ada total 18 properti berbeda untuk font yang dapat Anda akses. Katakanlah Anda ingin membuat teks menjadi tebal di sel A1, kodenya adalah:

 Range("A1").Font.Bold = True

Kode ini memberitahu VBA untuk mengakses properti BOLD dari font yang berada dalam rentang A1 dan Anda telah menyetel properti ini ke TRUE. Sekarang katakanlah Anda ingin menerapkan coretan ke sel A1, kode waktunya adalah:

Seperti yang saya katakan, ada total 18 properti berbeda yang dapat Anda gunakan, jadi pastikan untuk memeriksa semuanya untuk melihat mana yang berguna bagi Anda.

5. Hapus pemformatan dari sel

Dengan menggunakan metode “.ClearFormats”, Anda hanya dapat menghapus format sel atau rentang sel. Yang perlu Anda lakukan hanyalah menambahkan “.ClearFormat” setelah menentukan rentangnya, seperti di bawah ini:

 Range("A1").ClearFormats

Saat Anda menjalankan kode di atas, ini akan menghapus semua pemformatan dari sel A1 dan jika Anda ingin melakukannya untuk seluruh rentang, Anda tahu apa yang harus dilakukan, bukan?

 Range("A1:A5").ClearFormats

Sekarang kode di atas hanya akan menghapus format dari rentang A1 hingga A5.

Properti sel

Selain properti RANGE, Anda bisa menggunakan properti “Sel” untuk merujuk ke sel atau rentang sel di lembar kerja Anda. Di properti sel, alih-alih menggunakan referensi sel, Anda harus memasukkan nomor kolom dan nomor baris sel.

 expression.Cells(Row_Number, Column_Number)

Di sini ekspresinya adalah objek VBA dan Row_Number adalah nomor baris sel dan Column_Number adalah kolom sel. Jadi jika ingin merujuk ke sel A5, Anda bisa menggunakan kode di bawah ini:

 Cells(5,1)

Sekarang kode ini memberitahu VBA untuk merujuk ke sel yang ada di baris nomor lima dan kolom nomor satu. Seperti yang disarankan oleh sintaksisnya, Anda harus memasukkan nomor kolom sebagai alamat, tetapi kenyataannya Anda juga dapat menggunakan alfabet kolom jika Anda mau hanya dengan mengapitnya dengan tanda kutip ganda.

Kode di bawah ini juga akan merujuk ke sel A5:

 Cells(5,"A")

Dan di VBA untuk memilihnya, tambahkan saja “.Select” di akhir.

 Cells(5,1).Select

Kode di atas akan memilih sel A5 yang berada di baris ke-5 dan kolom pertama lembar kerja.

Properti OFFSET

Jika Anda ingin mahir menggunakan rentang di VBA, Anda perlu mengetahui cara menggunakan properti OFFSET. Berguna untuk merujuk ke sel yang berjarak beberapa baris dan kolom dari sel lain.

Misalkan sel Anda yang aktif saat ini adalah B5 dan Anda ingin menavigasi ke sel yang berjarak 3 kolom ke kanan dan 1 baris ke bawah dari B5, Anda dapat melakukan SHIFT ini. Di bawah ini adalah sintaks yang harus Anda gunakan untuk OFFSET:

 expression.Offset (RowOffset, ColumnOffset)

• RowOffset: Dalam argumen ini, Anda perlu menentukan angka yang akan memberi tahu VBA berapa banyak baris yang ingin Anda lalui. Bilangan positif menyatakan garis ke bawah dan bilangan negatif menyatakan garis ke atas.
• ColumnOffset : Dalam argumen ini, Anda perlu menentukan angka yang akan memberi tahu VBA berapa banyak kolom yang ingin Anda navigasikan. Angka positif menentukan kolom kanan dan angka negatif menentukan kolom kiri.

Mari kita tuliskan contoh kode sederhana yang telah kita bahas di atas.

1. Pertama-tama, Anda perlu menentukan rentang dari mana Anda ingin menavigasi dan mengetikkan kode di bawah ini:
   
2. Setelah itu ketik “.Offset” dan masukkan tanda kurung buka, seperti di bawah ini:
   
3. Selanjutnya, Anda perlu memasukkan nomor baris dan kemudian nomor kolom yang ingin Anda navigasikan.
   
4. Pada akhirnya Anda perlu menambahkan “.Pilih” untuk memberitahu VBA agar memilih sel yang ingin Anda navigasikan.
   

Jadi ketika Anda menjalankan kode ini, ia memilih sel yang berada satu baris di bawah dan 3 kolom di sebelah kanan sel B5.

Ubah ukuran rentang menggunakan OFFSET

OFFSET tidak hanya memungkinkan Anda menavigasi ke sel, tetapi Anda juga dapat mengubah ukuran rentang lebih jauh. Mari kita lanjutkan dengan contoh di atas.

 Range("B5").Offset(1, 3).Select

Kode di atas mengarahkan Anda ke sel E6, dan sekarang katakanlah Anda perlu memilih rentang sel yang terdiri dari lima kolom dan tiga baris E6. Jadi yang perlu Anda lakukan adalah setelah menggunakan OFFSET, gunakan properti resize dengan menambahkan “.Resize”.

 Range("B5").Offset(1, 3).Resize

Sekarang Anda perlu memasukkan ukuran baris dan ukuran kolom. Ketik tanda kurung di depan dan masukkan nomor untuk mengatur ukuran baris, lalu nomor untuk mengatur ukuran kolom.

 Range("B5").Offset(1, 3).Resize(3,5)

Di akhir, tambahkan “.Select” untuk memberi tahu VBA agar memilih rentang, dan saat Anda menjalankan kode ini, kode tersebut akan memilih rentang tersebut.

 Range("B5").Offset(1, 3).Resize(3, 5).Select

Jadi ketika Anda menjalankan kode ini, ia akan memilih rentang E6 hingga I8.

 Range("A1").Font.Strikethrough = True