Komunitas Belajar Excel

Belajar RibbonX - 05

2015-03-28T12:08:00.002+07:00

Menonaktifkan Sebagian Ribbon Excel

Kaserat: NangAgus

Mematikan Sebagian Tabs (Ribbon) Bawaan Excel

Buka file excel anda dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI)). Ingat file excel berexsistensi xlsb (binary) tidak dapat diedit Ribbonnya.

Kita akan mematikan tabs bawaan Microsoft excel secara parsial.

Misalnya kita akan menonaktifkan tab Home, Buatlah sebuah kode setelah tabs:

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Simpan file tersebut, kemudian buka file di dengan Excel,

Tab Home telah hilang di Microsoft excel meskipun di excel option masih tersedia tab Home.

Coba buat cekrip baru untuk menghilangkan tab yang lain!

Masih ingatkah ada berapa tab di excel yang dapat diperlihatkan dan dihilangkan?

Coba buka lagi pelajaran yang lalu tentang membuat Tab baru, disana terdapat 26 tab yang dapat diatur.

Menghilangkan Group

Buka Buka File excel anda dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI))

Kita akan menghilangkan group di Excel, misalnya akan dihilangkan group Clipboard di Tab Home

Ketikkan kode di bawah tab Home perintah untuk menghilangkangroup clipboard seperti dibawah ini

</tab>

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Simpan file tersebut, kemudian buka file di dengan Excel,

Pada Tab Home group Clipboard sudah disembunyikan.

Silahkan belajar sendiri untuk menghilangkan Group yang lain.

Ada 128 Group di Excel yang dapat disembunyikan. Silahkan baca referensi group di akhir pembahasan.

Menghilangkan Button

Buka Buka File excel anda dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI))

Button yang tersedia pada setiap group di Tab tidak dapat dihilangkan! Tetapi dapat kita buat seolah-olah tombol tersebut hilang, dewngan cara menanonaktifkan group tersebut, kemudian membuat group baru yamg mirip dengan group yang dihilangkan.

Akan dihilangkan sebuah button cut di group Clipboard dan di Tab Home.

1. Buatlah group Clipboard tidak tampak

2. Buatlah group baru untuk menggantikan group yang dihilangkan dengan tombol yang masih diperlukan:

</group>

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Simpan file tersebut, kemudian buka file di dengan Excel,

Pada Tab Home group Clipboard button-nya sudah sesuai dengan button yang baru.

Silahkan belajar sendiri untuk menghilangkan tombol yang lain.

Ada 929 button di Excel yang dapat disembunyikan.

Jangan lupa untuk Klik tombol Validate untuk mengecek apakah penulisan script pada custom UI sudah benar atau belum?

Jika penulisan sudah benar akan muncul tulisan “ Custom UI XML is Well formed!”.

Apabila terdapat ketikan yang salah akan muncul peringatan pada baris keberapa tulisan tersebut yang salah! Sebagai contoh perhatikan pesan berikut ini!

Terdapat ketidak sesuaian pada group antara baris ke-5 dan baris ke-16.

Dari 128 Group yang tersedia pada Microsoft excel sebagai contah hanya untuk tab Home (selengkapnya ada pada File Contoh di sini)

No	Group	Tab
1	GroupClipboard	TabHome
2	GroupFont	TabHome
3	GroupAlignmentExcel	TabHome
4	GroupNumber	TabHome
5	GroupStyles	TabHome
6	GroupCells	TabHome
7	GroupEditingExcel	TabHome
8	GroupInsertTablesExcel	TabInsert
9	GroupInsertIllustrations	TabInsert
10	GroupInsertChartsExcel	TabInsert
11	GroupInsertLinks	TabInsert
12	GroupInsertText	TabInsert
13	GroupInsertBarcode	TabInsert
14	GroupThemesExcel	TabPageLayoutExcel
15	GroupPageSetup	TabPageLayoutExcel
16	GroupPageLayoutScaleToFit	TabPageLayoutExcel
17	GroupPageLayoutSheetOptions	TabPageLayoutExcel
18	GroupArrange	TabPageLayoutExcel
19	GroupFunctionLibrary	TabFormulas
20	GroupNamedCells	TabFormulas

Untuk pengetikan script adalah case sensitive, maka jangan sampai salah mengambil nama group id.

Untuk Bahasan selanjutnya adalah membuat tombol sendiri.

Lebih baik apabila belajar sedikit demi sedikit dengan waktu yang singkat daripada belajar banyak sekaligus dalam waktu yang lama.

Selamat Belajar.

|<< First <Prev | Next> a

BelajarVBA 103 - ListBox

2014-10-06T01:19:00.000+07:00

Coretan Mr. Kid

Belajar yuk.

Pada pembahasan tentang ComboBox, telah diulas tentang beberapa properti yang sering digunakan. Beberapa properti tersebut antara lain adalah Text, Value, ListIndex, BoundColumn, TextColumn, ColumnCount, dan sebagainya. Sebagian besar properti yang dimiliki oleh ComboBox juga dimiliki oleh ListBox.

Pembahasan kali ini adalah tentang ListBox. Sekilas akan diulang tentang properti yang juga dimiliki oleh ComboBox. Properti yang juga dimiliki oleh ComboBox yang akan turut dibahas kali ini adalah properti ListStyle. Pembahasan akan lebih mengkhususkan ke properti MultiSelect yang hanya dimiliki oleh ListBox. File tentang pembahasan ini dapat diunduh disini.

Sekilas properti yang dimiliki ListBox dan ComboBox

Pembahasan properti ComboBox pada coretan BelajarVBA 102 - ComboBox 02 dibagian bertajuk ComboBox list multi kolom hanya menampilkan satu kolom menggunakan sebuah userform bernama frmNomor3 yang berisi sebuah ComboBox bernama cboProd.

Sumber data ComboBox tersebut adalah range A2:C5 di dalam object sheet bernama Sheet2 seperti yang tampak pada gambar berikut ini.

Kali ini control ComboBox yang ada dalam userform frmNomor3 akan dibuang dan diganti dengan sebuah ListBox. ListBox tersebut akan diatur agar properti (Name), BoundColumn, ColumnCount, dan TextColumn memiliki nilai yang sama dengan yang diatur pada ComboBox, yaitu :

(Name) diisi cboProd
BoundColumn diisi 2
ColumnCount diisi 1
TextColumn diisi 1

Setelah seluruh ListBox pengganti telah diatur propertinya, maka userform frmNomor3 bisa dijalankan. Meskipun tidak ada script di dalam userform frmNomor3 yang diubah, userform bisa bekerja seperti semula dengan satu perbedaan yaitu penggunaan ListBox yang menggantikan ComboBox. Hal ini menunjukkan bahwa sebagian besar properti ComboBox juga dimiliki oleh ListBox. Cara penggunaannya, yaitu cara mengambil nilai atau mengisi nilai properti juga memiliki kesamaan. Userform frmNomor3 dalam file BelajarVBA102_02.xlsm dapat dibandingkan dengan yang ada dalam file BelajarVBA103.xlsm.

Membuat ListBox dapat dipilih lebih dari satu item

ListBox memiliki properti yang membuat user bisa memilih banyak item sekaligus. Properti ini bernama MultiSelect. Properti MultiSelect memiliki 3 (tiga) buah opsi, yaitu :

Single : bernilai 0 [fmMultiSelectSingle], yaitu hanya dapat memilih satu item (seperti di ComboBox)
Multi : bernilai 1 [fmMultiSelectMulti], yaitu bisa memilih banyak item, tetapi dipilih dengan mengklik setiap item
Extended : bernilai 2 [fmMultiSelectExtended], yaitu bisa memilih banyak item dengan bantuan tombol SHIFT ataupun CTRL

Pengaturan properti MultiSelect bisa dilakukan saat proses design dengan langsung mengubah nilai properti melalui Properties Window milik VBE. Sedangkan pengaturan dengan menggunakan script adalah dengan syntax :

objectListBox.MultiSelect = nilai_opsi

objectListBox : nama listbox atau isi dari properti (Name) maupun variabel yang menyimpan object control ListBox
nilai_opsi : salah satu dari 3 (tiga) opsi yang telah dijelaskan di atas, yaitu fmMultiSelectSingle, fmMultiSelectMulti, atau fmMultiSelectExtended

Contoh :

lstMulti

lstMulti.MultiSelect = fmMultiSelectExtended

Status terpilih atau tidaknya sebuah item dalam ListBox

Sebuah item yang terpilih (Selected) akan memiliki latar dengan warna tertentu. Jika sebuah item yang sedang dalam keadaan terpilih kemudian diklik, maka statusnya akan berubah menjadi tidak terpilih. Properti yang menyimpan status terpilih atau tidaknya setiap item dalam ListBox adalah properti Selected. Jika nilai dalam properti Selected milik item dengan index tertentu adalah TRUE, maka item listbox pada index tersebut sedang dalam keadaan terpilih. Jika bernilai FALSE, maka item pada index tersebut sedang dalam keadaan tidak terpilih. Properti Selected dapat digunakan untuk memilih atau pun membatalkan pilihan pada suatu item dengan menggunakan script.

Baris perintah untuk mengetahui sebuah item dalam keadaan terpilih atau tidak adalah dengan syntax :

var_boolean_penyimpan = objectListBox.Selected(index)

var_boolean_penyimpan : variabel bertipe Boolean yang akan menjadi penyimpan nilai status terpilih atau tidaknya item dengan index tertentu

objectListBox : nama listbox atau isi dari properti (Name) maupun variabel yang menyimpan object control ListBox

index : nomor index (base 0) item ListBox yang akan dicari ambil nilai status terpilih atau tidaknya

Contoh :

lstMulti

Dim bStatus As Boolean
bStatus = lstMulti.Selected(4)
IF bStatus=TRUE Then
   Msgbox "Terpilih"
Else
   Msgbox "Belum terpilih"
End IF

Script di atas dapat disederhanakan menjadi :

IF lstMulti.Selected(4) Then
   Msgbox "Terpilih"
Else
   Msgbox "Belum terpilih"
End IF

Sedangkan baris perintah untuk mengubah status sebuah item menjadi terpilih atau tidak terpilih adalah dengan syntax :

objectListBox.Selected(index) = nilai_status_yang_baru

objectListBox : nama listbox atau isi dari properti (Name) maupun variabel yang menyimpan object control ListBox

index : nomor index (base 0) item ListBox yang akan dicari ambil nilai status terpilih atau tidaknya

nilai_status_yang_baru : TRUE/FALSE, yaitu TRUE untuk membuat item dengan index tertentu menjadi dalam keadaan terpilih dan FALSE untuk membuat item dengan index tertentu tersebut tidak terpilih.

Contoh :

lstMulti

lstMulti.Selected(2) = FALSE

Mengetahui jumlah item yang terpilih

Jumlah item yang dipilih oleh user dalam sebuah multiselect listbox didapatkan melalui pembacaan status setiap itemnya. Berarti diperlukan proses loop dari item pertama sampai item terakhir. Dalam proses loop tersebut dilakukan pemeriksaan status terpilih atau tidaknya, yaitu melalui properti Selected. Jika statusnya adalah TRUE, maka variabel penampung jumlah item terpilih ditambah dengan satu.

Contoh :

lstMulti

Dim lIdx As Long    'variabel loop per item dalam listbox lstMulti
Dim lTerpilih As Long    'variabel penyimpan jumlah item terpilih

For lIdx = 0 To lstMulti.ListCount - 1
   IF lstMulti.Selected(lIdx) Then
      lTerpilih = lTerpilih + 1
   End IF
Next lIdx
Msgbox "Jumlah item terpilih : " & lTerpilih

Bagi pembaca yang sudah familiar dengan fungsi-fungsi WInAPI, penggunaan fungsi WinAPI bernama SendMessage jauh lebih cepat dibanding menggunakan proses loop di atas.

Memilih seluruh item

Proses memilih atau membatalkan pilihan pada seluruh item dalam listbox juga dilakukan dengan melakukan loop terhadap seluruh item. Di dalam proses loop seluruh item hanya berisi baris perintah untuk mengubah nilai properti Selected pada index tersebut menjadi TRUE (untuk memilih) atau FALSE (untuk membatalkan pilihan).

Contoh :

lstMulti

Dim lIdx As Long 'variabel loop per item dalam listbox lstMulti

For lIdx = 0 To lstMulti.ListCount - 1
lstMulti.Selected(lIdx) = TRUE
Next lIdx
Msgbox "Proses selesai." & vbcrlf & "Seluruh item telah terpilih."

Contoh penggunaan listbox yang memanfaatkan properti MultiSelect dapat dilihat di file BelajarVBA103.xlsm pada sheet bernama MultiSelect. Penggunaan control Label juga telah disinggung pada file ini.

;)
Insya Allah pembahasan lebih detil tentang control Label dan TextBox bisa segera terwujud.

Belajar Ribbon-X 04

2014-08-16T11:55:00.001+07:00

Tabs Baru

Kaserat: NangAgus

Mematikan Tabs (Ribbon) Bawaan Excel

Buka file anda pada saat belajar membuat Button Baru dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI))

Kita akan mematikan tabs bawaan Microsoft excel.

Ubahlah

Menjadi

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Simpan file tersebut, kemudian buka file di dengan Excel,

Semua tab Microsoft excel akan hilang dan hanya ada tab Baru dengan nama Ribbon Baru berisi “Group Baru” dengan beberapa button yang sudah dipilih.

Menambah Tab

Buka Buka File anda dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI))

Kita akan memunculkan tab Home dan tab Ribbon Baru

Ketikkan kode seperti dibawah ini pada Tabs:

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Simpan file tersebut, kemudian buka file di dengan Excel,

Yang tampak hanya Tab Home dan Tab “Ribbon Baru”.

Jangan lupa untuk Klik tombol Validate untuk mengecek apakah penulisan script pada custom UI sudah benar atau belum?

Jika penulisan sudah benar akan muncul tulisan “ Custom UI XML is Well formed!”.

Apabila terdapat ketikan yang salah akan muncul peringatan pada baris keberapa tulisan tersebut yang salah! Sebagai contoh perhatikan pesan berikut ini!

Terdapat ketidak sesuaian pada group antara baris ke-5 dan baris ke-16.

Sebagai Referensi terdapat 26 Tab yang tersedia pada Microsoft excel (Pada File Contoh disini)

Tab tersebut adalah sebagai berikut:

No	Control Name	Control Type
1	TabHome	tab
2	TabInsert	tab
3	TabPageLayoutExcel	tab
4	TabFormulas	tab
5	TabData	tab
6	TabReview	tab
7	TabView	tab
8	TabDeveloper	tab
9	TabAddIns	tab
10	TabPrintPreview	tab
11	TabSmartArtToolsDesign	tab
12	TabSmartArtToolsFormat	tab
13	TabChartToolsDesign	tab
14	TabChartToolsLayout	tab
15	TabChartToolsFormat	tab
16	TabDrawingToolsFormat	tab
17	TabPictureToolsFormat	tab
18	TabPivotTableToolsOptions	tab
19	TabPivotTableToolsDesign	tab
20	TabHeaderAndFooterToolsDesign	tab
21	TabTableToolsDesignExcel	tab
22	TabPivotChartToolsDesign	tab
23	TabPivotChartToolsLayout	tab
24	TabPivotChartToolsFormat	tab
25	TabPivotChartToolsAnalyze	tab
26	TabInkToolsPens	tab

Untuk pengetikan script adalah case sensitive, maka jangan sampai salah mengambil nama tab id.

Untuk Bahasan selanjutnya adalah mengisi Tab dengan Group, Spilt Button, Combo Box dan Togle button.

Lebih baik apabila belajar sedikit demi sedikit dengan waktu yang singkat daripada belajar banyak sekaligus dalam waktu yang lama.

Selamat Belajar.

|<< First <Prev | Next> a

Belajar Ribbon-X 03

2014-08-16T10:55:00.000+07:00

Groups Baru & Button Baru

Kaserat: NangAgus

Menambah Groups

Buka file anda pada saat belajar membuat Ribbon Baru dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI))

Kita akan buat sebuah Group baru di letakkan pada Ribbon Baru.

Ketikkan koden seperti dibawah ini pada tab Ribbon Baru:

</group>

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Simpan file tersebut, kemudian buka file di dengan Excel,

Akan tampak sebuah Group baru pada Ribbon Baru dengan nama “Group Baru”.

Coba buat beberapa Group dengan nama-nama yang berbeda.

misalnya menjadi:

</group>

</group>

</group>

</group>

Menambah Button Microsoft Excel

Kita akan Menambahkan beberapa Button di letakkan pada Group Baru yang sudah kita buat sebelumnya. Button yang ditambahkan disini merupakan Button default milik Microsoft excel, untuk Button dengan fungsi buatan sendiri akan dibahas pada kesempatan lainya.

Ketikkan kode seperti di bawah ini pada Group Baru:

Selengkapnya kode yang tertulis seperti gambar berikut ini!

Jangan lupa untuk Klik tombol Validate untuk mengecek apakah penulisan script pada custom UI sudah benar atau belum?

Jika penulisan sudah benar akan muncul tulisan “ Custom UI XML is Well formed!”.

Apabila terdapat ketikan yang salah akan muncul peringatan pada baris keberapa tulisan tersebut yang salah! Sebagai contoh perhatikan pesan berikut ini!

Terdapat ketidak sesuaian pada group antara baris ke-5 dan baris ke-16.

Simpan file tersebut, kemudian buka file di dengan Excel,

Akan tampak sebuah Group baru pada Ribbon Baru dengan nama “Group Baru” berisi beberapa Buttonyang sudah dipilih.

Sebagai Referensi terdapat 920 button yang tersedia pada Microsoft excel (Pada File Contoh disini)

Sebagian Button tersebut adalah sebagai berikut:

No	Control Name	Control Type
1	FileNew	button
2	FileOpen	button
3	UpgradeWorkbook	button
4	FileSave	button
5	FileSaveAs	button
6	FileSaveAsExcelXlsx	button
7	FileSaveAsExcelXlsxMacro	button
8	FileSaveAsExcelXlsb	button
9	FileSaveAsExcel97_2003	button
10	AdvertisePublishAs	button
11	FileSaveAsPdfOrXps	button
12	FileSaveAsOtherFormats	button
13	FilePrint	button
14	FilePrint	button
15	FilePrintQuick	button
16	FilePrintPreview	button
17	FileDocumentInspect	button
18	FilePermissionView	button
19	FilePermissionRestrictAs	button
20	FileAddDigitalSignature	button
21	FileCompatibilityChecker	button
22	FileLinksToFiles	button
23	FileSendAsAttachment	button
24	FileEmailAsPdfEmailAttachment	button
25	FileEmailAsXpsEmailAttachment	button
26	FileInternetFax	button
27	FilePublishExcelServices	button
28	FileSaveToDocumentManagementServer	button
29	FileCheckIn	button
30	FileCheckOut	button
31	FileCheckOutDiscard	button
32	FileVersionHistory	button
33	FileWorkflowTasks	button
34	FileExcelServicesOptions	button
35	FileStartWorkflow	button
36	FileClose	button
37	Paste	button
38	Paste	button
39	PasteFormulas	button
40	PasteValues	button

Untuk pengetikan script adalah case sensitive, maka jangan sampai salah mengambil nama button id.

Untuk Bahasan selanjutnya adalah mengisi Ribbon dengan Tab, Group, Spilt Button, Combo Box dan Togle button.

Selamat Belajar.

|<< First <Prev | Next> a

Belajar Ribbon-X 02

2014-08-16T09:16:00.000+07:00

Root Name & Ribbon Baru

Kaserat: NangAgus

Root Name Space

Open XML yang digunakan untuk membuat RibbonX terdapat dua bagian utama yaitu bagian NameSpace dan bagian Relationships. Kali ini kita hanya akan mempelajari Root name space dahulu, sedangkan untuk yang relationship kita pelajari di lain kesempatan.

Salah satu elemen dalam Root Name Space adalah CustomUI

Untuk menjelaskan elemen dalam CustomUI kita dapat mengandaikan seperti hierarki dalam pemerintahan kita (mengutip penjelasan Mas Anton dalam kopdar 2#) mulai yang tertinggi, Presiden, Gubernur, Bupati, Camat, Lurah, RT, RW, KK. Setiap elemen memiliki elemen lain yang memiliki aturan sendiri-sendiri. Apakah nama setiap elemen? Mungkinkah sama dengan VBA yang disebut object?

Perhatikan diagram di bawah ini!

	CUSTOM UI


Ribbon	Commands	Ribbon

Quick Access Toolbar	Command	Tabs

Document Control		Tab

Saya sendiri masih minim pengetahuan tentang teori bahasa markup (boleh dibilang bingung dan gak tahu apa-apa), kita langsung praktik aja yah…..,

Ada dua type dalam mengubah Ribbon MS Excel, yaitu mengubah ribbon yang sudah ada dan mengganti total ribbon pada ms excel.

Menambah Ribbon

Ribbon yang sudah ada di Excell 2007 dapat kita ubah sesuai kebutuhan.

Buatlah sebuah File excel dan simpan dalam format xlsx, atau xlsm.

Buka file tersebut dengan customUI editor. (file tersebut tidak boleh terbuka pada excel. (tutuplah excel sebelum dibuka di CustomUI))

Tambahkan CustomUI part untuk office 2007

Untuk sementara kita belajar untuk Office 2007 dulu yaa, Office 2010 menyesuaikan, atau dicoba sendiri. Maklumlah saya sudah tua (rambut udah putih semua) dan saya sangat gaptek dibandingkan dengan anak muda zaman sekarang he.. he… he ….

Dan yang jelas office 2010 sudah menyediakan cara mengubah ribbon Excel (klik File option, Customize Ribbon). Namun apabila file dibuat menggunakan customUI dan berdasar office 2007 maka file tersebut juga dapat dibuka di office 2010 tanpa banyak perbedaan.

Sekarang Kita akan buat sebuah Tab baru yang di letakkan pada Ribbon paling akhir dengan nama Ribbon Baru.

Ketikkan kode seperti dibawah ini:

<tabs>

</tab>

</tabs>

</ribbon>

</customUI>

Simpan file tersebut, kemudian buka file dengan Excel,

Akan tampak sebuah ribbon baru dengan nama “Ribbon Baru”.

Sampai disini dahulu belajar tentang ribbon baru. Perlu Contoh?
Untuk latihan, coba buat beberapa Ribbon dengan nama-nama yang berbeda.

Untuk membuat Ribbon yang berbeda, selain nama ribbon (label) yang diganti, id-nya juga perlu diganti! misalnya menjadi:

</tab>

</tab>

</tab>

</tab>

Cukup sampai disini dahulu, untuk Bahasan selanjutnya adalah mengisi ribbon dengan Group dan Button.

Selamat Belajar.

|<< First <Prev | Next> a

MEMAKSA USER MENGAKTIFKAN MACRO

2014-07-04T07:13:00.001+07:00

kaserat:NangAgus

Secara bawaaan (default) Microsoft excel menonaktifkan macro untuk keamanan. Kita tidak dapat memaksa Microsoft untuk menjalankan macro saat membuka file excel yang ada macronya, demikian pula dengan penggunanya tidak dapat selalu menghidupkan macro saat membuka file. Agar file yang kita berikan selalu dihidupkan macronya saat dibuka, ikuti trik berikut ini:

Bahasa Manusia

Untuk mengawali suatu penulisan visual basic sebaiknya kita selalu mengawali dengan bahasa manusia agar lebih mudah memahami apa yang akan kita lakukan.

Agar file yang diberikan selalu dijalankan macronya kita buat trik dengan cara: selalu sheet1 yang berisi peringatan bahwa macro belum dijalankan akan terbuka apabila macro belum dijalankan, dan sheet2 yang terbuka apabila macro sudah di jalankan. Jadi user tidak dapat menggunakan file jika belum menjalankan macro tetapi hanya mendapatkan peringatan bahwa macro belum dijalankan.

Agar sheet1 terbuka saat macro belum dijalankan, saat sebelum di tututup (before close) semua sheet disembunyikan dan sheet1 saja yang nampak.

Sheet2 nampak abaila macro sudah dihidupkan, kita tulis dalam procedure Worksheet Open

Persiapan

Kita mulai dengan membuat 2 sheet yang diperlukan, sheet1 berisi peringatan agar menghidupkan macro

Sheet 2 berisi program yang akan berjalan jika macro dihidupkan.

Penulisan Macro

Bukalah VBA editor dengan menkan Alt+11.

Dobel Klik This Workbook

Pilih Object Workbook dan Prosedur Before Close:

Ketiklah kode berikut ini:

Sheet1.Visible = xlSheetVisible ‘perlihatkan sheet1

Sheet2.Visible = xlSheetVeryHidden ‘sembunyikan sheet2

Sheet3.Visible = xlSheetVeryHidden

Pada prosedur Workbook_open tulislah kode berikut:

Sheet2.Visible = xlSheetVisible ‘perlihatkan sheet2

Sheet1.Visible = xlSheetVeryHidden ‘sembunyikan sheet1

Sheet3.Visible = xlSheetVeryHidden

Kode selengkapnya seperti gambar di bawah ini!

Simpanlah file dalam type *.xlsm

Selamat Mencoba.

Contoh File

Belajar RibbonX 01 Perkenalan

2014-06-12T13:25:00.000+07:00

RIBBONX

kaserat:NangAgus

PENDAHULUAN

RibbonX merupakan sarana untuk meng”custom”isasi Ribbon yang telah disediakan oleh Microsoft, dengan ribbonX kita dapat menambah, mengurangi bahkan mengganti total Ribbon microsoft Excel.

Untuk membuat RibbonX diperlukan beberapa software untuk menulis script pada CustomUI. Ada banyak software yang dapat digunakan untuk menulis Script CustomUI, beberapa diantaranya CustomUI editor, notepad, notepad ++, dll.

Untuk pemula seperti saya sebaiknya menggunakan CustomUI editor, namun menurut Mr. Kid sebaiknya digunakan notepad ++. Semua software tersebut memiliki kekurangan dan kelebihan masing-masing.

No	Software	Kelebihan	Kekurangan	DownLoads
1.	Notepad	Semua komputer ada	Jika terjadi kesalahan script tidak terdeteksi, membuat file relasi	-
2.	Notepad ++	Hanya butuh 1 software	Membuat file relasi sendiri	Mriki
3.	CustomUI editor	Jika ada kesalahan mudah terdeteksi	Membutuhkan netframe work 3.5	Mriki & Mriki

Terserah pada anda sekalian silahkan dipakai sesuai dengan kebutuhan dan kebiasaan, dalam kesempatan ini saya mengajak rekan-rekan semua untuk belajar menggunakan CustomUI editor saja yang lebih mudah penggunaannya.

PERKENALAN DENGAN CUSTOM UI EDITOR

Setelah anda menginstall Custom UI Editor, marilah kita buka custom UI editor.CustomUI editor sangat sederhana dalam tampilannya dibandingkan dengan notepad++ lihat gambar di bawah ini!

Coba bandingkan kesederhanaan jendela CustomUI di atas dengan jendela Notepad++ di bawah ini!

Sekarang marilah kita lihat masing-masing Tab pada CustomUI.

Pada tab menu File terdapat dropdown menu Open, Save, Save As, Close dan Exit, sangat standar dan pasti anda semua sudah dimengerti semua gunanya.

Demikian pula untuk tab menu Edit isinya juga sangat standar isinya.

Adakah yang memerlukan penjelasan untuk tab menu File dan Edit? Silahkan email saya atau lebih bagus jika diemailkan ke milis sehingga lebih banyak yang akan menanggapi.

Untuk Tab Insert terdapat dropdown menu yang khusus untuk CustomUI.

Office 2010 CustomUI Part digunakan untuk mengubah RibbonX office 2010

Office 2007 CustomUI Part digunakan untuk mengubah RibbonX office 2007

Menu Icon digunakan untuk menambahkan icon buatan sendiri

Sample XML digunakan untuk membuka contoh yang sudah disediakan Custom UI editor.

Toolbar Validate digunakan untuk mengecek apakah penulisan script sudah betul apa belum?Jika kita sudah membuka file Excel maka CustomUI editor terbelah menjadi dua:

Disebelah kiri merupakan jendela exploler tempat memilih file xml yang akan ditulis, sedangkan jendela kanan tempat menulis script.

Mohon maaf apabila semua tulisan saya membingungkan, karena saya belajar sendiri tentang RibbonX ini, sudilah kiranya semua dapat mengoreksi tulisan ini sehingga bermanfaat untuk semuanya. Saya hanya ingin untuk membagi ilmu yang saya miliki, sebatas yang saya mengerti, semoga TUHAN memberi pencerahan kepada saya sehingga lebih mengerti tentang RibbonX melalui rekan rekan semua yang mau meralat tulisan ini, terima kasih.

Membuat Daftar Sheet Dengan Formula

2014-05-28T02:47:00.001+07:00

Terkadang kita bekerja dengan menggunakan banyak Sheet, dan untuk mempermudah kita membuka sheet tertentu, akan lebih baik jika kita mempunyai Sheet Menu, dimana di dalam sheet tersebut berisikan daftar Sheet yang ada.
Proses manual tetap saja bisa dilakukan, tetapi untuk orang "malas" sudah bukan jamannya lagi pakai cara manual...

Nah bagaimana cara membuatnya ?
Ada 2 cara, yang pertama menggunakan macro, bagi macro mania pasti mudah sekali ya, cukup looping sejumlah sheet, kemudian tuliskan pada sheet Menu.
Cara kedua yang akan saya bahas disini adalah menggunakan Formula.

Caranya adalah sebagai berikut :
1. Buatlah Name dengan nama DaftarSheet, scopenya Workbook dan Formulanya adalah :

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Syntax GET.WORKBOOK bisa anda lihat disini

2. Kemudian pada Sheet Menu, silahkan anda tuliskan formula berikut ini :

=IFERROR(INDEX(DaftarSheet,Row()),"")

Karena Formula ini menggunakan fungsi Row(), maka untuk mendapatkan nama Sheet yang pertama, formula ini harus diletakkan pada Row 1

3. Jika ingin meletakkan pada baris yang diinginkan, Row(), bisa diganti dengan cara manual, atau merujuk pada Cell tertentu, seperti pada contoh :

=IFERROR(INDEX(DaftarSheet,A5),"")

Copy formula ini sebanyak Sheet anda.

Dengan cara ini maka anda akan mendapatkan hasil pada Kolom B
Tetapi untuk kebutuhan Menu anda, maka hal ini kurang membantu...benar ?
Untuk menambahkan link ke sheet yang lain, maka tambahkan formula :

=HYPERLINK("#" & B5 & "!A1","Go to Sheet")

Kemudian copy formula tersebut sebanyak baris nama Sheet anda.

Match With Multiple Criteria

2014-05-22T02:19:00.001+07:00

Fungsi MATCH berfungsi untuk mendapatkan posisi suatu nilai dalam suatu array.

Syntax : MATCH(Lookup_Value, Array_Data, Match_Type)

Lookup_Value : Nilai yang akan dicari posisinya
Array_Data : Array (sekumpulan) data

http://excelformulaandmacro.blogspot.com/2010/10/fungsi-match.html

Bagaimana jika ada lebih dari 1 kondisi ?

Caranya adalah dengan menggunakan fungsi Match dengan kriteria lebih dari 1

Fungsi ini menggunakan array formula

http://excelformulaandmacro.blogspot.com/2010/10/penggunaan-array.html

Syntax : MATCH(1,(Array_Data1=Kondisi1)*(Array_Data2=Kondisi2),0)

Sebagai contoh :

Jangan lupa, karena fungsi ini menggunakan array formula, setelah menuliskan formula akhiri dengan menekan tombol Ctrl + Shift + Enter

Jika berhasil maka akan nampak tanda { diawal formula dan } di akhir formula

Kopdar 2 Belajar-Excel

2014-05-11T15:20:00.000+07:00

Minggu, 11 Mei 2014, berlangsung KOPDAR (Kopi Darat) ke-2 komunitas pemakai Excel yang tergabung di milis/group belajar-excel@yahoogroups.com.

Acara kangen-kangenan yang diisi dengan bagi ilmu model seminar ini dilaksanakan di perumahan Alam Sutra, Tangerang (Banten). Hadir pada acara tersebut 27 orang anggota komunitas, termasuk Mr. Kid, Pak Hendrik Karnadi, Bang Aliif, Bang Dwint, dan-lain-lain.

Seperti pada Kopdar pertama, kali ini panitia masih mengusung Mr. Kid sebagai pembicara, dengan topik VBA.

Berikut ini beberapa foto suasana kopdar:

BelajarVBA 102 - ComboBox 02

2014-02-23T22:49:00.000+07:00

Coretan Mr. Kid

Isilah masa sehat dengan kebaikan sebelum tiba masa sakit.

Pada pembahasan tentang combobox yang lalu, sumber data merujuk ke suatu area range yang diisikan ke dalam combobox dengan memanfaatkan properti ListFillRange (combobox di dalam worksheet) atau RowSource (combobox di dalam userform). Properti ListFillRange akan membuat daftar dalam combobox menjadi terikat erat dengan sumbernya, sehingga pengubahan isi daftar tanpa mengubah sumber data tidak dimungkinkan. Pengubahan bisa dilakukan bila yang diubah adalah nilai-nilai dalam sumber data.

Bahasan kali ini masih akan menggunakan data sumber untuk combobox list dari suatu range. Hanya saja, kali ini pengisian daftar combobox dilakukan tanpa properti ListFillRange atau RowSource, tetapi menggunakan methods AddItem ataupun properti List dari combbox. Hal ini akan membuat daftar menjadi lebih terbuka untuk diubah isinya. Kadang kala pengubahan isi daftar juga dibutuhkan pada beberapa kasus.

File yang akan digunakan adalah BelajarVBA102_02.xlsm. Data yang digunakan diletakkan pada sheet bernama 'populate' dengan nama object di dalam VBProject adalah Sheet2. Data tersebut seperti gambar berikut :

ComboBox list berisi satu kolom

Kali ini, pembahasan combobox akan memanfaat userform. Pada bahasan tentang combobox berisi satu kolom ini, akan menggunakan userform bernama frmNomor2 seperti gambar berikut :

yang berisi object controls utama sebagai berikut :

Combobox bernama cboProd : combobox dengan list berisi satu kolom yang dibaca dari sheet bernama populate (object Sheet2) pada range A2:A5.
Textbox bernama txtListIndex : menampilkan nilai yang disimpan oleh properti ListIndex milik cboProd.
Textbox bernama txtText : menampilkan nilai yang disimpan oleh properti Text milik cboProd.
Textbox bernama txtValue : menampilkan nilai yang disimpan oleh properti Value milik cboProd.

Pada kasus ini, textbox digunakan untuk menampilkan data dan tidak mengijinkan user untuk bisa mengubah nilainya. Maka properti Locked milik seluruh textbox diset bernilai TRUE. Properti milik cboProd menggunakan nilai default setiap pembuatan object baru di sebuah userform, yang antara lain adalah :

BoundColumn dan ColumnCount bernilai 1
ColumnHeads bernilai FALSE
ColumnWidths, ControlSource, RowSource, Text, Value dalam keadaan kosong
ListWidth bernilai 0pt
TextColumn bernilai -1

Alur proses pada frmNomor2 adalah sebagai berikut :

Saat frmNomor2 dimuat ke memori komputer, maka dilakukan pengisian item-item daftar pilihan combobox, yang dilanjutkan dengan manampilkan frmNomor2 kepada user. Artinya, proses pengisian dilakukan saat frmNomor2 sedang diinisialisasi ke memori komputer. Jadi, proses pengisian item ke combobox llist dilakukan pada sebuah event milik userform frmNomor2, yaitu event Initialize.
Ketika user mengubah pada combobox cboProd, maka akan dilakukan proses penulisan nilai-nilai yang disimpan oleh properti ListIndex, Text, dan Value dari cboProd ke :
- sheet bernama populate (Sheet2 di VBProject Explorer) pada range B13 sampai B15.
- textbox control terkait, yaitu txtListIndex, txtText, dan txtValue
Proses ini dilaksanakan saat user mengubah isi cboProd, yang berarti akan memicu event Change milik cboProd. Jadi proses dilakukan didalam event Change milik cboProd.
Saat userform frmNomor2 ditutup oleh user, maka dilakukan pembersihan isi range B13 sampai B15 yang ada di sheet bernama populate. Proses ini terjadi saat ada permintaan penutupan userform oleh user, yang akan memicu event milik userform yang bernama QueryClose. Jadi, proses pembersihan isi range tertentu tersebut diletakkan di dalam event QueryClose milik userform.

Setelah desain userform frmNomor2 terbentuk dan alur proses terjabarkan dengan jelas, maka penulisan script VBA untuk setiap proses tersebut bisa dilakukan dengan tartil. Berikut ini langkah penulisan script VBA untuk contoh kasus di atas.

Event Initialize milik userform frmNomor2
Diisi dengan proses pengisian daftar untuk combobox cboProd dengan langkah penyusunan sebagai berikut :
- Membuat blok prosedur sub event Initialize milik userform frmNomor2 dengan cara :
  - double click userform frmNomor2, sehingga muncul blok event Click milik userform frmNomor2
  - pada combobox prosedur (sudut kanan atas area penulisan script) -> pilih Initialize
  - letakkan cursor ke dalam blok prosedur sub event Initialize yang terbentuk
- Mendeklarasikan variabel yang dibutuhkan selama proses pengisian Pengisian item list combobox cboProd bisa dilakukan dengan 2 (dua) cara, yaitu : (pilih salah satu)
  - Memanfaatkan methods AddItem dengan cara melakukan loop terhadap setiap item sumber data. Sumber data yang berada di sheet populate (object Sheet2) pada range A2:A5 membutuhkan variabel loop bertipe Range. Maka perlu pendeklarasian variabel tersebut, yang misalnya diberi nama rngCurrent. Baris kode deklarasi tersebut berbunyi :
    Dim rngCurrent As Range
  - Memanfaatkan properti List dengan cara menyediakan array seluruh item dari sumber data. Berarti, sumber data yang ada di sheet populate (object Sheet2) pada range A2:A5 dibaca dan disimpan sebagai sebuah array. Area range sebenarnya adalah suatu array dan tipe data yang memungkinkan dengan mudah untuk menyimpan array dari suatu range adalah tipe Variant. Maka perlu pendeklarasian variabel bertipe variant, yang misalnya diberi nama vSumber. Baris kode deklarasi tersebut berbunyi :
    Dim vSumber As Variant
- Menyusun baris kode proses pengisian
  Sumber data di sheet populate (Sheet2) pada range A2:A5.
  - Jika memilih menggunakan methods AddItem, maka dibutuhkan proses loop terhadap setiap item dalam range A2:A5. Di dalam proses loop tersebut diisi proses penambahan item ke list milik combobox dengan methods AddItem. Baris-baris kode proses ini adalah :
    For Each rngCurrent In Sheet2.Range("a2:a5")
    cboProd.AddItem rngCurrent.Value
    Next rngCurrent
  - Jika memilih menggunakan properti List, maka dibutuhkan proses penyimpanan sumber data range A2:A5 menjadi array, yaitu ke dalam variabel vSumber. Kemudian array dalam variabel vSumber dimasukkan ke properti List. Baris-baris kode proses ini adalah :
    vSumber = Sheet2.Range("a2:a5")
    cboProd.List = vSumber
- Bentuk akhir prosedur sub event Initialize menjadi :
  - Methods AddItem
    Private Sub UserForm_Initialize()
       Dim rngCurrent As Range
    
       For Each rngCurrent In Sheet2.Range("a2:a5")
          cboProd.AddItem rngCurrent.Value
       Next rngCurrent
    End Sub
  - Properti List
    Private Sub UserForm_Initialize()
       Dim vSumber As Variant
    
       vSumber = Sheet2.Range("a2:a5")
       cboProd.List = vSumber
    End Sub
Event Change milik cboProd
Diisi dengan proses penulisan ke lokasi target dengan langkah penyusunan sebagai berikut :
- Membentuk blok prosedur sub event Change milik cboProd dengan cara double click object control combobox cboProd yang ada di dalam userform frmNomor2 dan meletakkan cursor di dalam blok prosedur yang terbentuk
- Menulis nilai-nilai properti ListIndex, Text, dan Value secara berurutan ke :
  - Sheet populate pada range B13 sampai B15 dengan baris-baris kode :
    Sheet2.Range("b13").Value = cboProd.ListIndex
    Sheet2.Range("b14").Value = cboProd.Text
    Sheet2.Range("b15").Value = cboProd.Value
  - Object controls textbox txtListIndex, txtText, dan txtValue dengan baris-baris kode :
    txtListIndex.Text = cboProd.ListIndex
    txtText.Text = cboProd.Text
    txtValue.Text = cboProd.Value
- Bentuk akhir blok prosedur sub event Change milik cboProd :
  Private Sub cboProd_Change()
     Sheet2.Range("b13").Value = cboProd.ListIndex
     Sheet2.Range("b14").Value = cboProd.Text
     Sheet2.Range("b15").Value = cboProd.Value
  
     txtListIndex.Text = cboProd.ListIndex
     txtText.Text = cboProd.Text
     txtValue.Text = cboProd.Value
  End Sub
Event QueryClose milik userform frmNomor2
Diisi dengan proses menghapus isi range B13 sampai B15 pada sheet bernama populate (object Sheet2), yang menjadi lokasi penulisan pada proses di dalam event Change milik cboProd. Langkah-langkah penyusunannya adalah sebagai berikut :
- Membentuk blok prosedur sub event QueryClose milik userform frmNomor2 dengan cara :
  - double click userform frmNomor2
  - pada combobox daftar prosedur (pojok kanan atas area penulisan script) -> pilih QueryClose
  - letakkan cursor di dalam blok prosedur yang terbentuk
- Menulis baris kode untuk menghapus isi range B13 sampai B15 di sheet populate (object Sheet2) yang berbunyi :
  Sheet2.Range("b13:b15").ClearContents
- Bentuk akhir blok prosedur sub event QueryClose menjadi :
  Private Sub UserForm_QueryClose( _
                            Cancel As Integer _
                          , CloseMode As Integer)
     Sheet2.Range("b13:b15").ClearContents
  End Sub

Prosedur-prosedur di atas membentuk sebuah proses sederhana tentang pengisian item list combobox dan pengambilan nilai dari item terpilih ke suatu lokasi target (range tertentu ataupun object control tertentu).

ComboBox list multi kolom hanya menampilkan satu kolom

Kali ini, dibuat sebuah userform yang diberi nama frmNomor3 yang mirip dengan userform frmNomor2. Perbedaannya adalah penambahan textbox bernama txtCol2 untuk menampilkan nilai dari kolom ke-3 list combobox. Properti BoundColumn di-set bernilai 2 dan properti TextColumn di-set bernilai 1. Sedangkan properti ColumnCount tetap bernilai 1 karena hanya akan menampilkan satu kolom saja. Sumber data untuk list combobox diambil dari sheet populate (object Sheet2) pada area range A2:C5 (3 kolom). Bentuk userform frmNomor3 seperti gambar berikut :

> Proses pend-disable-an event pada userform

Kebutuhan untuk membaca nilai dari kolom list yang tidak ditunjuk oleh properti BoundColumn maupun TextColumn, seperti ingin mengambil nilai dari kolom list ke-3 [indeks kolom bernilai 2 = Column(2)] bisa memicu timbulnya error ketika nilai pada kolom yang dirujuk tersebut belum terdefinisikan. Contohnya adalah ingin mengambil nilai dari kolom ke-3, tetapi pada baris item tersebut baru 2 kolom saja yang didefinisikan.

Error seperti ini sering terjadi ketika proses pengambilan nilai pada suatu kolom list dilakukan pada event Change milik combobox terkait (misal pada event Change milik cboProd). Artinya, dibutuhkan sebuah proses untuk mengabaikan kerja prosedur sub event change pada saat mengubah isi list, karena tidak ada pengaturan untuk men-disable event milik userform. Cara men-disable event di suatu userform dalam VBA dilakukan dengan menggunakan variabel ber-scope module yang diperiksa oleh setiap prosedur sub event yang tidak ingin dijalankan dengan alasan tertentu.

Pada contoh kasus ini, proses pada event Initialize userform frmNomor3 bisa memunculkan error, karena proses pengisian list combobox akan memicu event Change milik cboProd. Untuk men-disable event, maka dibuat sebuah variabel bernama bEventTidakBolehJalan dengan scope module. Baris kode deklarasi variabel ini diletakkan pada area General Declaration (di baris pertama area penulisan script atau setelah baris berbunyi Option Explicit). Baris kode tersebut berbunyi :
   Private bEventTidakBolehJalan As Boolean

Pada event Change milik cboProd, yaitu dibaris pertama setelah baris deklarasi variabel diberi proses cek terhadap nilai variabel bEventTidakBolehJalan dengan bentuk baris kode :
   If bEventTidakBolehJalan Then
Exit Sub
   End If
yang berarti, jika variabel bEventTidakBolehJalan sedang bernilai TRUE, maka proses langsung keluar dari prosedur alias tidak ada proses dalam prosedur event tersebut yang dilakukan.

Cara menggunakannya, pada prosedur yang salah satu prosesnya bisa memicu si event Change milik cboProd untuk dijalankan, seperti pada event Initialize milik userform frmNomor3, diberi baris kode untuk mengubah nilai variabel bEventTidakBolehJalan menjadi TRUE. Penempatannya adalah tepat sebelum proses yang memicu berjalannya event Change milik cboProd. Pada kasus ini, berarti baris kode berbunyi :
   bEventTidakBolehJalan = TRUE
diletakkan tepat sebelum proses pengisian item list cboProd di dalam prosedur sub event Initialize. Pada bagian akhir event Initialize, yaitu tepat sebelum keluar dari prosedur atau sebelum baris End Sub, diberi baris kode untuk mengembalikan nilai variabel bEventTidakBolehJalan menjadi FALSE. Baris kode tersebut berbunyi :
   bEventTidakBolehJalan = FALSE

> Proses pengisian multi kolom list dengan methods AddItem

Cara mengisi multi kolom list menggunakan methods AddItem memerlukan sebuah variabel penyimpan indeks baris list yang akan ditambahkan. Misalkan variabel ini diberi nama lCboIndex. Maka pada event Initialize diberi penambahan deklarasi variabel tersebut. Maka baris deklarasi variabel pada event Initialize menjadi :
   Dim rngCurrent As Range, lCboIndex As Long

Kemudian nilai lCboIndex di-set dengan nilai awal indeks baris list yang base-0, yaitu dengan nilai 0 seperti dengan baris berbunyi :
   lCboIndex = 0

Proses loop yang tadinya berbentuk :
   For Each rngCurrent In Sheet2.Range("a2:a5")
      cboProd.AddItem rngCurrent.Value
   Next rngCurrent
diubah menjadi :
   For Each rngCurrent In Sheet2.Range("a2:a5")
      cboProd.AddItem rngCurrent.Value 'pembuat item baru

      'pengisi kolom list ke-2 dan seterusnya
      cboProd.List(lCboIndex, 1) = rngCurrent.Offset(0, 1).Value
      cboProd.List(lCboIndex, 2) = rngCurrent.Offset(0, 2).Value

'set nilai indeks baris list berikutnya yang akan diisi

      lCboIndex = lCboIndex + 1
   Next rngCurrent

> Proses pengisian multi kolom list dengan properti List

Jika menggunakan properti List, maka yang diubah adalah rujukan range yang tadinya merujuk ke A2:A5 menjadi merujuk ke A2:C5 (lihat bagian yang di-merah).
vSumber = Sheet2.Range("a2:c5")

> Penambahan baris penulisan pada event Change cboProd

Pengambilan nilai kolom ke-3 (indeks kolom ke-2) dapat dilakukan dengan properti Column maupun properti List seperti yang telah dijelaskan pada coretan tentang combobox sebelum ini. Penulisan ke sheet populate pada kasus multi kolom list ini dilakukan ke range H18:H21, sehingga baris kode bagian penulisan ke sheet populate menjadi :
   Sheet2.Range("h18").Value = cboProd.ListIndex
   Sheet2.Range("h19").Value = cboProd.Text
   Sheet2.Range("h20").Value = cboProd.Value
   Sheet2.Range("h21").Value = cboProd.List(, 2) 'atau cboProd.Column(2)

Sedangkan pada penulisan ke textbox txtCol2 adalah dengan menambahkan baris kode :
   txtCol2.Text = cboProd.List(, 2) 'atau cboProd.Column(2)

Hasil akhir penyusunan script untuk userform frmNomor3 dapat dilihat pada file BelajarVBA102_02.xlsm.

Tambah, ubah, dan hapus item list combobox

> menambah sebuah item ke list combobox

Misalkan ada sebuah combobox bernama cboProd. Penambahan item list dilakukan dengan menggunakan methods AddItem dengan susunan :
   cboProd.AddItem nilai_baru, [indeks_posisi_penambahan]
Jika ingin menambahkan pada baris baru adalah dengan mengabaikan bagian [indeks_posisi_penambahan] seperti :
   cboProd.AddItem nilai_baru
Bagian nilai_baru adalah teks (bertipe String) yang akan menjadi nilai baru di kolom ke-1 (indeks kolom ke-0).
Contohnya adalah ingin menambahkan item baru 'X' di baris baru, maka baris kodenya adalah :
   cboProd.AddItem "X"
Jika item baru 'Y' ingin ditambahkan pada baris ke-1 list (indeks baris ke-0), maka baris kodenya adalah :
   cboProd.AddItem "Y", 0

Pada combobox dengan multi kolom list, maka bisa jadi dibutuhkan juga pengisian pada kolom ke-2 dan seterusnya (penambahan pada indeks kolom ke-1 dan seterusnya). Pengisian tersebut bisa dilakukan setelah ada penambahan item baru dengan methods AddItem di atas. Susunan penulisan item di kolom lain tersebut secara umum adalah :
   cboProd.List(idxrow, idxcol) = nilai_kolom_baru

Misalkan ingin menambahkan item 'Z' pada baris baru dengan nilai kolom ke-2 bernilai 'Z01' dan nilai kolom ke-3 bernilai 'ZZ11P001', maka baris-baris kode penambahan tersebut adalah :
   cboProd.AddItem "Z"
   cboProd.List( cboProd.ListCount-1 , 1) = "Z01"
   cboProd.List( cboProd.ListCount-1 , 2) = "ZZ11P001"
Properti ListCount dipergunakan untuk mendapatkan jumlah item dalam list. Karena properti ListIndex menggunakan base 0 (indeks baris item pertama bernilai 0), maka nilai properti ListCount - 1 adalah indeks baris item terakhir yang ada. Pada kasus di atas, berarti pada baris item 'Z' yang baru saja ditambahkan di baris baru.

Sedangkan jika ingin menyisipkan (menambah item pada baris tertentu) item 'M' pada baris ke-1 (indeks baris 0) dengan nilai kolom ke-2 bernilai 'M78' dan nilai kolom ke-3 bernilai 'KL98P113', maka baris-baris kode penambahan tersebut adalah :
   cboProd.AddItem "M", 0
   cboProd.List( 0 , 1) = "M78"
   cboProd.List( 0 , 2) = "KL98P113"
Kadangkala, pemeriksaan posisi penyisipan perlu dilakukan, sehingga dapat dipastikan bahwa indeks baris penyisipan tidak lebih dari nilai properti ListCount. Nilai properti ListCount bisa diartikan juga sebagai indeks baris baru jika akan dilakukan penambahan data. Bentuk pemeriksaan tersebut adalah sebagai berikut :
'0 adalah indeks baris lokasi penyisipan dan bisa diganti dengan variabel tertentu
If cboProd.ListCount >= 0 Then
   'proses penyisipan diletakkan disini
   cboProd.AddItem "M", 0
   cboProd.List( 0 , 1) = "M78"
   cboProd.List( 0 , 2) = "KL98P113"
End If

> mengubah isi sebuah item yang ada di list combobox

Misalkan ada sebuah combobox bernama cboProd. Pengubahan isi sebuah kolom dalam list combobox dilakukan dengan memanfaatkan propeti List. Bentuk umum beris kodenya adalah :
   cboProd.List(idxrow, idxcol) = nilai_kolom_baru
dengan syarat bahwa idxrow (indeks baris yang akan diubah isinya) kurang dari jumlah item (nilai properti ListCount). Biasanya, bentuk diatas disertai pemeriksaan terhadap indeks baris yang akan diubah isinya. Bentuk lengkapnya menjadi :
'idxrow adalah indeks baris yang akan diubah isinya
If idxrow < cboProd.ListCount Then
   cboProd.List(idxrow, idxcol) = nilai_kolom_baru
End If

Misalkan akan mengubah isi kolom ke-3 (indeks kolom ke-2) pada item di baris pertama (indeks baris ke-0) menjadi 'GJ71P123', maka baris kodenya adalah :
If 0 < cboProd.ListCount Then
   cboProd.List(0, 2) = "GJ71P123"
End If

> menghapus isi list combobox

Misalkan ada sebuah combobox bernama cboProd. Jika akan menghapus seluruh isi list combobox, maka dapat menggunakan methods Clear. Baris kodenya adalah :
   cboProd.Clear

Jika yang akan dihapus adalah sebuah item di baris tertentu, maka dilakukan dengan menggunakan methods RemoveItem disertai indeks baris (base 0) yang akan dihapus. Bentuk umum baris kodenya adalah :
   cboProd.RemoveItem indeks_baris_yang_dihapus
Biasanya, penghapusan sebuah baris item dilakukan setelah diperiksa bahwa baris yang akan dihapus ada didalam list. Artinya, indeks baris yang akan dihapus (indeks_baris_yang_dihapus) kurang dari jumlah item list (nilai properti ListCount). Jadi bentuk umum menghapus sebuah item list combobox yang disertai pemeriksaan keberadaan baris yang akan dihapus adalah :
   If indeks_baris_yang_dihapus < cboProd.ListCount Then
      cboProd.RemoveItem indeks_baris_yang_dihapus
   End If
Misalkan ingin menghapus item di baris ke-4 (indeks baris ke-3), maka baris kodenya adalah :
   If 3 < cboProd.ListCount Then
      cboProd.RemoveItem 3
   End If

Contoh tentang penerapan penambahan item, pengubahan isi, dan penghapusan item bisa dilihat di file BelajarVBA102_02.xlsm pada userform bernama frmNomor4 yang bisa diaktifkan melalui tombol di sheet bernama 'sisip_ubah_buang'. Pada userform tersebut juga disertakan contoh pemanfaatan event Click milik object control Label yang bisa menjadi alternatif sebuah tombol seperti object control CommandButton.

:)

Insya Allah pembahasan berikutnya tentang object control ListBox pada properti yang tidak dimiliki oleh ComboBox, yaitu properti MultiSelect. Selain itu, juga akan dibahas tentang object control Label, khususnya pada pemanfaatan properti Caption dan MultiLine. Object control textbox juga memiliki properti MultiLine, tetapi ada properti PasswordChar yang bisa digunakan untuk me-masking inputan user pada control tersebut. Event KeyDown acapkali terasa bermanfaat ketika digunakan oleh textbox, seperti membatasi inputan untuk numerik saja atau karakter tertentu saja dan sebagainya. Event KeyDown pada textbox juga akan dibahas disana.

BelajarVBA 102 - ComboBox 01

2014-02-23T00:01:00.002+07:00

Coretan Mr. Kid

Sempatkan berbagi sebelum sempit.

Setelah sekian lama tak sempat, Alhamdulillah tiba juga masa sempat tersebut. Bahasan kali ini tentang ActiveX ComboBox. ActiveX Controls memiliki banyak properti dan event yang memudahkan pengaturan alur proses menampilkan sesuatu. Salah satu ActiveX Controls yang sering digunakan adalah ComboBox. File yang digunakan mendampingi coretan kali ini adalah BelajarVBA102_01.xlsm.

ComboBox

Object control ini dapat digunakan untuk menampilkan daftar pilihan kepada user. Daftar pilihan bisa disertai beberapa kolom tambahan yang dapat dijadikan sebagai informasi tambahan bagi user. Object control ini dapat digunakan di dalam Worksheet maupun di dalam UserForm.

Selain dengan cara memilih dari pilihan yang tersedia, user juga bisa menuliskan nama item yang dibutuhkannya pada suatu area penulisan layaknya menulis pada sebuah textbox. Jika yang ditulis user ada dalam daftar pilihan, maka combobox akan segera memilihkan item tersebut untuk user.

Jadi, combobox memiliki 2 bagian utama, yaitu :

daftar pilihan
- Pada umumnya, daftar ini ditampilkan bila user meng-click dropdown, tetapi pengaturan properti untuk hal ini bisa dilakukan pada properti bernama ShowDropButtonWhen
- Daftar pilihan ditampilkan sebagai sebuah list yang mirip dengan control ListBox dan memiliki karakteristik (properti, methods, events) yang sebagian besar sama dengan listbox.
penulisan nama item
- Area penulisan ini memiliki karakteristik (properti, methods, events) yang sebagian besar sama dengan control TextBox.
- Teks yang di-input akan membuat combobox berusaha mencari dan memilihkan item dari dalam daftar pilihan. Karakteristik pencarian dan pemilihan item yang dilakukan oleh combobox akibat inputan dapat diatur pada properti MatchEntry dan MatchRequired.

Data yang akan digunakan sebagai daftar pilihan dalam combobox adalah seperti gambar berikut :

Properti ListFillRange dan LinkedCell

Bahasan pertama adalah tentang combobox yang diletakkan di dalam sebuah worksheet (misal worksheet bernama list_fill_range). Ketika daftar item yang akan dipasang pada combobox berada di suatu area range, sebut saja pada range A2:A5 (lihat gambar data di atas dan anggap saja data tersebut ada di sheet list_fill_range), maka properti ListFillRange bisa memberi kemudahan. Pengisian daftar item dalam combobox bisa dilakukan dengan mengisi properti ListFillRange. Isi properti ListFillRange adalah alamat area range dari daftar item, seperti menuliskan teks berbunyi A2:A5. Properti dari combobox bisa dimunculkan ketika dalam keadaan Design Mode (lihat di ribbon Developer dan klik Design Mode). Cara menampilkan window properties dari si object adalah dengan klik kanan si object dan pilih Properties.

Setelah daftar terisi dan Design Mode telah di-off-kan, maka user bisa memilih sebuah item dari daftar yang ada. Pilihan user ini bisa dituliskan ke sebuah cell tanpa perlu script VBA dengan memanfaatkan properti bernama LinkedCell. Isian properti LinkedCell juga berupa alamat sebuah range. Misalkan, pada properti LinkCell diisi alamat range berbunyi B10. Setiap kali user memilih sebuah item dari combobox, maka pada cell B10 akan tertulis pilihan user tersebut. Ketika user mengosongkan pilihan atau menulis sesuatu dalam combobox yang tidak ada di dalam daftar, maka cell B10 akan berisi error value #N/A.

Gambar berikut ini adalah cuplikan dari sheet bernama list_fill_range pada file yang disebutkan di atas.

Jadi, properti :

ListFillRange : untuk mengisi daftar combobox atau listbox dari suatu area range.
LinkedCell : untuk mengikatkan properti Value milik object ke sebuah range.

Multiple Column dan Event Change

Pengaturan yang dilakukan pada properti combobox agar bisa menampilkan banyak kolom informasi tambahan adalah sebagai berikut :

ColumnCount : jumlah kolom yang ditampilkan pada list. Jadi, list bisa berisi lebih banyak kolom dibanding yang ditampilkan. Misalkan list berisi 8 kolom, tetapi hanya ditampilkan 5 kolom saja dengan mengisi ColumnCount dengan nilai 5.
ColumnHeads : status menampilkan header atau tidak (TRUE/FALSE) ketika sumber data dari suatu area range.
ColumnWidths : pengaturan lebar masing-masing kolom dalam satuan point (pt) yang dipisah karakter titik koma ( ; ), seperti 60pt;80pt;80pt untuk 3 kolom yang ditampilkan.
ListWidth : lebar list keseluruhan dalam satuan point (pt) ketika dimunculkan atau saat user mengklik dropdown combobox. Jika total lebar setiap kolom lebih dari ListWidth, maka akan dimunculkan horisontal scrollbar, seperti 220pt.
BoundColumn : indeks kolom yang akan dijadikan nilai bagi properti Value (base 1 -> kolom ke-1 mendapat nomor indeks kolom 1). Nilai 0 berarti menampilkan nilai ListIndex atau indeks baris item (base 0).
TextColumn : indeks kolom yang akan dijadikan nilai bagi properti Text (base 1 dan defaultnya -1 yang berarti sama dengan BoundColumn). Nilai 0 berarti menampilkan nilai ListIndex atau indeks baris item (base 0).

Properti LinkedCell (jika digunakan) hanya bisa mengambil sebuah nilai yang dimiliki oleh properti Value yang terikat dengan kolom yang ditentukan pada properti BoundColumn. Sedangkan jumlah kolom dalam list ada lebih dari satu, maka sangat besar kemungkinan akan menggunakan script VBA untuk mendapatkan nilai dari kolom-kolom yang ada. Beberapa properti yang bermanfaat dalam pengambilan nilai dari kolom-kolom yang ada dalam list sebuah combobox antara lain adalah :

ListIndex : indeks baris list yang terpilih dengan nilai -1 yang berarti tidak ada yang terpilih dan nilai 0 sebagai item di baris pertama dalam list (base 0 -> item ke-1 diberi indeks bernilai 0).
Text : nilai yang ditampilkan oleh combobox yang berasal dari indeks kolom yang ditentukan pada properti TextColumn (base 1 -> kolom ke-1 dalam list diberi indeks kolom bernilai 1)
Value : nilai yang disimpan oleh combobox yang berasal dari indeks kolom yang ditentukan pada properti BoundColumn (base 1).
Column( idxcol ) : merujuk pada indeks kolom ke-idxcol (base 0), seperti Column(1) merujuk ke indeks kolom 1 yang berarti merujuk pada kolom ke-2 dalam list.
List( idxrow , idxcol ) : merujuk pada baris item dengan indeks baris ke-idxrow (base 0) dan kolom dengan indeks kolom ke-idxcol (base 0). Nilai idxrow pada baris terpilih adalah sebesar nilai properti ListIndex.

Pada saat user memilih sebuah item dari list, maka event yang bekerja adalah event Change. Jadi, jika diharapkan agar setiap user mengganti pilihan akan terjadi suatu proses tertentu seperti penulisan nilai-nilai ke suatu lokasi target, maka event change bisa diisi dengan script VBA tentang alur proses tersebut. Event Change milik combobox dapat dibuat dengan melakukan double click pada object control ketika dalam keadaan Design Mode. Gambar berikut ini adalah cuplikan dari sheet bernama list_fill_range yang menjadi contoh combobox dengan multiple column dan disertai dengan proses penulisan nilai ke suatu range.

Pada gambar, properti ListFillRange diisi dengan A2:C5 dengan jumlah kolom yang ditampilkan adalah 3 (isi ColumnCount) diisi 3. Jadi, sumber data list adalah range A2:C5 (3 kolom) ditampilkan dalam list sebanyak 3 kolom juga (ditampilkan seluruhnya). Properti LinkedCell juga digunakan dengan merujuk ke range B30 yang akan mengambil nilai di kolom ke-2 karena isi properti BoundColumn adalah 2 yang berarti properti Value akan mengambil nilai yang ada di kolom ke-2.

Pemanfaatan event Change milik combobox adalah untuk menulis beberapa informasi tentang nilai-nilai properti tertentu dari item yang terpilih, seperti properti ListIndex yang ditulis pada cell B33, Text pada cell B34 yang nilainya berasal dari kolom ke-1 berdasar isi properti TextColumn yang bernilai 1, Value yang nilainya berasal dari kolom ke-2 dan menghasilkan nilai yang sama dengan cell B30 yang diisi oleh properti LinkedCell. Juga nilai dari kolom ke-3 yang diambil dari list combobox menggunakan properti Column dengan idxcol bernilai 2 (cell B36) atau dengan properti List dengan idxrow senilai dengan nilai properti ListIndex (karena bagian idxrow dikosongkan) dan idxcol bernilai 2 (cell B37).

Prosedur Sub pada event change combobox tersebut berbunyi sebagai berikut :
Private Sub cboProd2_Change()
    'tulis informasi tentang data terpilih
    Range("b33").Value = cboProd2.ListIndex     'ListIndex
    Range("b34").Value = cboProd2.Text          'Text
    Range("b35").Value = cboProd2.Value         'Value
    Range("b36").Value = cboProd2.Column(2)     'Column indeks 2 [base 0]=kolom ke-3
    Range("b37").Value = cboProd2.List(, 2)     'List indeks kolom 2 [base 0]=kolom ke-3
End Sub

ComboBox pada sebuah UserForm

ActiveX Control memang lebih sering digunakan dalam UserForm yang tampak lebih cantik. ComboBox dalam sebuah UserForm juga bisa diisi dengan suatu data yang disimpan pada suatu area range secara mudah melalui pemanfaatan properti RowSource. Properti RowSource pada combobox di dalam UserForm setara dengan properti ListFillRange pada ComboBox yang diletakkan dalam sebuah worksheet. Properti LinkedCell di combobox dalam worksheet setara dengan properti ControlSource pada combobox di dalam sebuah UserForm.

Misalkan dibuat sebuah UserForm bernama frmNomor1 yang diberi combobox dengan nama cboProd. Pada cboProd, diatur dengan properti :

RowSource : berisi teks berbunyi list_fill_range!A2:C5
ColumnCount : berisi 3
ColumnHeads : TRUE
BoundColumn : berisi 2
ControlSource : berisi teks berbunyi list_fill_range!B57
TextColumn : berisi 1

Saat userform ditampilkan, maka cboProd akan berisi daftar yang bersumber dari sheet bernama list_fill_range pada area range A2:C5. Ketika user mengganti pilihan, maka cell B57 di sheet list_fill_range akan terisi dengan nilai dari kolom ke-2 dari list setelah combobox tidak lagi dalam keadaan aktif (kursor telah berpindah ke control lainnya). Hal ini diakibatkan oleh pengaturan properti ControlSource yang merujuk ke range di sheet tersebut. Sedangkan nilai yang diambil oleh ControlSource berasal dari properti Value yang diatur mengikuti isi properti BoundColumn yang merujuk pada kolom ke-2 dari list.

Event change pada cboProd bisa dimanfaatkan untuk menulis ke suatu range ataupun ke control lain saat user mengubah pilihan. Misalkan, saat user mengubah pilihan maka akan terjadi proses penulisan ke range tertentu seperti contoh prosedur sub pada event change cboProd berikut :
Private Sub cboProd_Change()
    'tulis informasi tentang data terpilih

    '>> ke worksheet 'Sheet1 (list_fill_range)'
    Sheet1.Range("b61").Value = cboProd.ListIndex   'ListIndex
    Sheet1.Range("b62").Value = cboProd.Text        'Text
    Sheet1.Range("b63").Value = cboProd.Value       'Value
    Sheet1.Range("b64").Value = cboProd.Column(2)   'Column indeks 2 [base 0]=kolom ke-3
    Sheet1.Range("b65").Value = cboProd.List(, 2)   'List indeks kolom 2 [base 0]=kolom ke-3
End Sub

Properti ControlSource juga dimiliki oleh control lain seperti TextBox. Misalnya di frmNomor1 tersebut juga ditambahkan 2 buah textbox. Textbox pertama diberi nama txtBound1 untuk terikat dengan range B57 (yang diisi oleh ControlSource milik combobox) dan textbox kedua diberi nama txtListIndex1 yang terikat dengan range B61. Isi properti ControlSource pada textbox txtBound1 berbunyi list_fill_range!B57 dan pada textbox txtListIndex1 berbunyi list_fill_range!B61. Setiapkali ada perubahan pada cell B57 dan B61, maka textbox juga akan berubah isinya ketika terjadi pembaruan tampilan frmNomor1. Pembaruan tampilan frmNomor1 bisa dipicu dengan mengganti control yang aktif melalui methods SetFocus. Jadi, pada event change milik cboProd juga bisa disertakan proses pembaruan tampilan ini dengan menambahkan baris-baris script berikut yang diletakkan pada bagian tepat sebelum End Sub setelah baris terakhir penulisan yang sudah ada.
    '>> supaya textbox control yang menggunakan properti ControlSource terupdate
    txtBound1.SetFocus      'salah satu control di-set focus
    'jika ingin dikembalikan ke combobox cboProd, maka set focus ke cboprod
    cboProd.SetFocus

Penulisan item terpilih ke control yang ada dalam userform juga bisa dilakukan dengan script VBA dan tanpa properti ControlSource milik control yang menjadi target. Misalnya pada frmNomor1 diberi textbox berikut :

txtListIndex : diisi dengan nilai properti ListIndex dari cboProd
txtValue : diisi dengan nilai yang disimpan oleh properti Value milik cboProd
txtText : diisi dengan nilai yang disimpan oleh properti Text milik cboProd
txtCol2 : diisi dengan nilai yang diambil dari kolom ke-3 list yang berindeks kolom senilai 2

maka pada event change milik cboProd bisa tambahkan script penulisan tersebut yang berbunyi :
    '>> ke TextBox di frame fraScript dalam userform frmNomor1
    txtListIndex.Text = cboProd.ListIndex   'ListIndex
    txtValue.Text = cboProd.Value           'Value -> tergantung BoundColumn
    txtText.Text = cboProd.Text             'Text -> tergantung TextColumn

    'mengambil nilai dari kolom tertentu bisa dengan
    'properti Column(indeks kolom) atau properti List( nilai list index , indeks kolom )
    'seperti :
    txtCol2.Text = cboProd.Column(2)        'Column(2) -> column(0) adalah kolom ke-1
    'atau
    txtCol2.Text = cboProd.List(, 2)        'List(,2) -> column(0) adalah kolom ke-1

Berikut ini adalah cuplikan gambar bentuk userform bernama frmNomor1 :

Contoh tampilan hasil penulisan yang dilakukan oleh proses yang ada dalam prosedur sub event change milik cboProd adalah :

Script lengkap dari prosedur sub event change milik cboProd berbunyi :
Private Sub cboProd_Change()
    'tulis informasi tentang data terpilih

    '>> ke worksheet 'Sheet1 (list_fill_range)'
    Sheet1.Range("b61").Value = cboProd.ListIndex   'ListIndex
    Sheet1.Range("b62").Value = cboProd.Text        'Text
    Sheet1.Range("b63").Value = cboProd.Value       'Value
    Sheet1.Range("b64").Value = cboProd.Column(2)   'Column indeks 2 [base 0]=kolom ke-3
    Sheet1.Range("b65").Value = cboProd.List(, 2)   'List indeks kolom 2 [base 0]=kolom ke-3

    '>> supaya textbox control yang menggunakan properti ControlSource terupdate
    txtBound1.SetFocus      'salah satu control di-set focus
    'jika ingin dikembalikan ke combobox cboProd, maka set focus ke cboprod
    cboProd.SetFocus

    '>> ke TextBox di frame fraScript dalam userform frmNomor1
    txtListIndex.Text = cboProd.ListIndex   'ListIndex
    txtValue.Text = cboProd.Value           'Value -> tergantung BoundColumn
    txtText.Text = cboProd.Text             'Text -> tergantung TextColumn

    'mengambil nilai dari kolom tertentu bisa dengan
    'properti Column(indeks kolom) atau properti List( nilai list index , indeks kolom )
    'seperti :
    txtCol2.Text = cboProd.Column(2)        'Column(2) -> column(0) adalah kolom ke-1
    'atau
    txtCol2.Text = cboProd.List(, 2)        'List(,2) -> column(0) adalah kolom ke-1
End Sub

:)

Insya Allah pembahasan tentang combobox akan dilanjutkan pada coretan berikutnya. Pembahasan combobox yang akan dibahas berikutnya antara lain tentang pengisian list dengan script VBA memanfaatkan methods AddItem maupun properti List yang diletakkan pada prosedur sub event initialize userform. Juga dibahas tentang methods Clear untuk menghapus seluruh isi list combobox. Proses pengubahan nilai suatu item dalam list, menghapus sebuah item, menambahkan sebuah item di baris baru ataupun disisipkan pada baris tertentu juga akan dibahas disana. Dampak pengubahan isi list combobox yang memicu dijalankannya event change beserta salah satu cara menghindarinya akan disertakan pada pembahasan tersebut.

BelajarVBA 101 - Controls Dalam Worksheet

2013-11-11T08:10:00.001+07:00

Coretan Mr. Kid

Belajar sedikit-sedikit secara rutin dan konsisten akan berdampak lebih besar daripada belajar banyak sesekali.

Worksheet bisa diberi Form Controls dan ActiveX Controls untuk memudahkan interaksi dengan user. Object berupa shape juga bisa dimanfaatkan sebagai sebuah tombol untuk menjalankan prosedur VBA yang telah dibuat dalam sebuah general module. Bahasan kali ini adalah tentang menggunakan sebuah shape, Button Form Control, dan Command Button ActiveX Control untuk menjalankan sebuah prosedur VBA. File yang bisa digunakan untuk mencoba secara mandiri adalah BelajarVBA101_01.xlsm. Sebaiknya dalam membaca pembahasan ini disertai dengan membuka file BelajarVBA101_01.xlsm pada sheet bernama 'shapes_n_controls'.

Mengenal object control

Object controls adalah object yang dapat digunakan untuk mengatur interaksi dengan user sesuai jenis control tersebut. Misalnya, sebuah List Box dapat digunakan untuk mengatur daftar item yang dapat dipilih oleh user. Ketika sebuah item dalam List Box dipilih user, maka dapat digunakan untuk mengatur proses yang harus terjadi terhadap pemilihan item tersebut.
Object control berada di ribbon Developer -> group Controls -> item Insert

Object control yang tersedia terbagi dalam 2 kelompok besar, yaitu :

Form Controls
- Object controls bawaan Excel.
- Tidak termasuk object controls yang diwaspadai oleh Security Excel.
- Hanya memiliki prosedur sub event click saja.
- Controls yang disediakan adalah Button, Label, Combo Box, List Box, Spin Button, Scroll Bar, Check Box, Option Button, Group Box.
- Controls tambahan ketika menggunakan MS Excel 5.0 Dialog adalah Text Field, Combo List, dan Combo Drop Down.
- Beberapa controls seperti Combobox, Listbox, Spin Button, dan sebagainya memiliki banyak properti yang bisa diset langsung melalui Format Control pada tab Control.
- Beberapa controls, seperti Spin Button, Combobox, dan semacamnya, memberikan suatu nilai output. Nilai output tersebut dapat digunakan ketika control tersebut di-link ke suatu cell.
- Form control berupa List Box dan Combo Box membutuhkan rujukan ke suatu range satu kolom saja untuk mengisi daftar pilihan yang dapat dipilih user.
ActiveX Controls
- Object controls tambahan dari library windows bernama mscomctl.ocx atau mscomctl2.ocx.
- Memiliki banyak properti melalui dialog window Properties.
- Memiliki banyak macam prosedur sub event.
- Bisa digunakan dalam sebuah sheet maupun dalam sebuah UserForm.
- Controls yang disediakan adalah Command Button, Label, Combo Box, List Box, Spin Button, Scroll Bar, Check Box, Option Button, Text Box, Image, Toggle Button.
- Beberapa controls memberikan suatu nilai output. Nilai output tersebut bisa digunakan melalui baris-baris kode VBA maupun di-link ke sebuah cell.
- Pengisian daftar pada control seperti Combo Box ataupun List Box bisa dengan merujuk ke suatu range tertentu ataupun melalui baris-baris kode VBA. Jumlah kolom dalam Combo Box atau List Box juga bisa lebih dari satu kolom.
- Bisa menambahkan ActiveX Controls lain yang belum ada dalam daftar yang disediakan melalui :
  ribbon Developer -> group Controls -> Insert -> bagian ActiveX Controls -> item More Controls
- Pengaturan nilai-nilai properti maupun penataan baris-baris kode dalam events dilakukan ketika dalam keadaan Design Mode ON, yaitu dengan cara :
  ribbon Developer -> group Controls -> klik Design Mode agar aktif (terjadi perubahan warna pada menu)
- Controls akan dapat berfungsi ketika Design Mode dalam keadaan OFF, yaitu dengan mengklik ulang item Design Mode dalam ribbon Developer.

Mengenal properti khusus pada sebuah form control

Properti khusus yang dimiliki sebuah form control diletakkan pada tab Control dalam dialog window Format Control. Properti khusus tersebut dapat diakses dengan :

klik kanan form control -> Format Control -> klik tab Control

Form Control yang memiliki properti khusus pada umumnya adalah controls yang bisa memberikan nilai output, kecuali control bernama Group Box yang berfungsi membentuk sebuah group check box maupun option button. Berikut ini adalah form controls yang bisa memberi nilai output :

Combo Box (Drop Down)
berupa nomor indeks item (item pertama bernomor indeks 1) yang dipilih user
List Box
berupa nomor indeks item (item pertama bernomor indeks 1) yang dipilih user
Spin Button
berupa nilai bilangan bulat setelah kegiatan naik atau turun terakhir
Scroll Bar
berupa nilai bilangan bulat setelah kegiatan naik atau turun terakhir
Check Box
berupa nilai TRUE ketika dalam status checked dan nilai FALSE ketika dalam status unchecked
Option Button
berupa nomor indeks dari option button (item pertama bernomor indeks 1) dalam sebuah grup option

Mengenal properti ActiveX Control

Setiap ActiveX Control memiliki banyak properti yang bisa diatur nilainya melalui baris kode VBA maupun melalui dialog window properties. Cara menampilkan dialog window properties dari sebuah ActiveX Control yang diletakkan pada sebuah sheet adalah dengan :

Aktifkan Design Mode
Membuka dialog window properties
klik kanan control -> pilih Properties

Properti utama dari setiap ActiveX Control adalah properti '(Name)' yang berfungsi untuk menentukan nama object control tersebut. Pada ActiveX Control yang diletakkan pada sebuah sheet, penggantian nilai properti '(Name)' juga dapat dilakukan melalui Name Box yang disediakan Excel. Teks yang diserahkan ke properti '(Name)' inilah yang akan aktif digunakan sebagai rujukan ke object control tersebut dalam baris-baris kode VBA.

Beberapa properti, yang ada disetiap ActiveX Control, yang sering digunakan adalah :

Enabled
Visible

Pada beberapa ActiveX Controls yang dapat memberikan nilai output, yang disimpan oleh si control pada properti bernama Value atau Text (bila ada), disediakan properties tambahan untuk mendukung fungsi control tersebut. Misalnya properti ListFillRange pada ComboBox dan ListBox atau properti SmallChange pada SpinButton atau ScrollBar. Beberapa ActiveX Control memiliki properti yang tidak ditampilkan dalam dialog window properties, tetapi memiliki fungsi yang sangat penting, seperti properti ListIndex pada ComboBox dan ListBox.

Mengenal event dari ActiveX Controls

Kelebihan dari ActiveX Controls adalah tersedianya banyak event yang menangkap perlakuan user terhadap control tersebut. Seperti event bernama Click yang akan menangkap perlakuan user saat mengklik control tersebut. Ada juga event bernama Change yang menangkap perlakuan user ketika mengubah nilai yang ada dalam control tersebut, seperti mengetik setiap karakter dalam Text Box Control atau mengubah pilihan dalam List Box maupun Combo Box dan sebagainya.

Dengan diketahuinya perlakuan user terhadap si control melalui event yang diproses tersebut, maka alur proses bisa diatur untuk melakukan suatu kegiatan komputasi tertentu. Misalnya, ketika user mengklik tombol tertentu, maka event Click akan diproses dan didalam event tersebut diberi baris-baris kode untuk menjalankan suatu proses copy data dari sheet input ke sheet database.

Dari sekian banyak event yang dimiliki setiap ActiveX Control, ada beberapa event yang selalu ada dan sering digunakan, antara lain adalah :

GotFocus
- Bereaksi ketika suatu proses atau user mengaktifkan control tersebut. Event ini bisa digunakan salah satu contohnya adalah untuk menata (bahkan sampai memvalidasi nilai) dari control tersebut atau controls yang lain.
- Misalnya control TextBox bernama Negara. Ketika TextBox Negara diaktifkan, maka event GotFocus dari textbox ini akan beraksi dan dijalankan. Pada event GotFocus milik si textbox ini bisa diisi dengan proses menghapus nilai dalam textbox dan memeriksa nilai dalam textbox lain seperti textbox Nama harus sudah terisi lebih dulu. Jika textbox Nama belum terisi, maka proses akan memerintahkan textbox Nama untuk diaktifkan.
LostFocus
- Bereaksi ketika suatu proses atau user mengaktifkan object lain. Event ini bisa digunakan salah satu contohnya adalah untuk memvalidasi nilai yang telah diinputkan pada control tersebut.
- Misalnya control ComboBox bernama PIC yang sedang aktif. Ketika user mengaktifkan control lain, maka event LostFocus milik combobox PIC akan beraksi dan dijalankan. Pada event ini bisa diisi dengan pemeriksaan bahwa combobox PIC telah diisi dengan salah satu item yang ada dalam daftar yang disediakan. Jika tidak ada, maka proses bisa mengembalikan control yang aktif ke combobox PIC.
KeyDown
- Bereaksi ketika si control sedang aktif dan user menekan sebuah tombol keyboard. Event ini menyediakan informasi kode tombol keyboard yang ditekan oleh user dalam variabel bernama KeyCode. Informasi tentang tertekannya tombol shift atau tidak disediakan dalam variabel bernama Shift. Event ini bisa digunakan untuk membatasi tombol keyboard yang bisa mempengaruhi si control.
- Misalnya sebuah textbox bernama NIP yang harus berisi angka saja bisa diatur agar tombol keyboard yang membuat si textbox berubah nilainya adalah tombol-tombol angka saja. Contoh lainnya adalah sebuah CommandButton bisa diatur agar proses penekanan (klik) bisa dengan menekan tombol keyboard huruf 'p' ketika si control dalam keadaan sedang aktif.
MouseMove
- Bereaksi ketika pointer mouse melintasi si control. Event ini biasanya digunakan untuk menampilkan informasi tentang si control. Salah satu media penulisan informasi yang disediakan adalah properti bernama ControlTipText. Properti ini hanya ada ketika control digunakan dalam sebuah userform. Ketika control diletakkan dalam sebuah sheet, maka control seperti Label bisa dijadikan media penampil informasi tentang si control.

Setiap ActiveX Controls juga memiliki events yang khusus untuk menunjang kerjanya. Beberapa events penunjang tersebut ada yang dapat digunakan dalam UserForm saja. Jadi, dalam penggunaan ActiveX Control perlu diketahui lokasi penggunaannya beserta events yang bisa digunakan dilokasi tersebut. Cara yang mudah adalah dengan melihat ComboBox event dan prosedur dalam VBE saat ComboBox object berisi control tersebut [silakan baca BelajarVBA 001 - VBE (Visual Basic Editor)]

Tombol pemanggil prosedur VBA

Beberapa kegiatan otomasi dengan VBA membutuhkan interaksi dengan user untuk menjalankan prosedur-prosedur otomasi yang telah dibuat. Sebuah tombol yang dapat ditekan oleh user akan memudahkan user dalam menjalankan suatu prosedur otomasi. Tombol bisa berupa Shape, Form Control, atau ActiveX Control.

Cara menambahkan suatu object yang akan berfungsi sebagai tombol dalam sebuah worksheet adalah sebagai berikut :

Menggunakan shape

ribbon Insert -> group Illustrations -> Shapes -> pilih sebuah shape -> gambar kotak dalam worksheet
Menggunakan Form Control

ribbon Developer -> group Controls -> Insert -> lihat bagian Form Controls (sisi atas) -> pilih Button -> gambar kotak dalam worksheet
Menggunakan ActiveX Control
ribbon Developer -> group Controls -> Insert -> lihat bagian Form Controls (sisi atas) -> pilih Button -> gambar kotak dalam worksheet

Cara mengganti nama tombol antara lain dengan :

Menggunakan shape
klik kanan shape dalam worksheet -> klik Name Box (pojok kiri atas worksheet [diatas nomor baris Excel 1]) -> ganti teks yang ada (misal menjadi shpPesan) -> tekan ENTER
Menggunakan Form Control
klik kanan Button dalam worksheet -> klik Name Box (pojok kiri atas worksheet [diatas nomor baris Excel 1]) -> ganti teks yang ada (misal menjadi ctlBPesan) -> tekan ENTER
Menggunakan ActiveX Control
- Aktifkan Design Mode
- Ganti nama control
  klik kanan Command Button dalam worksheet -> klik Name Box (pojok kiri atas worksheet [diatas nomor baris Excel 1]) -> ganti teks yang ada (misal menjadi cmdPesan) -> tekan ENTER
- Non aktifkan Design Mode

Pengaturan nama prosedur yang akan dijalankan oleh si tombol selalu diawali dengan adanya prosedur yang akan dijalankan. Misalkan pada sebuah general module telah dibuat sebuah prosedur bernama PesanShapeNFormControl yang berupa :
   Public Sub PesanShapeNFormControl()
       MsgBox "Hello " & Environ("USERNAME")
   End Sub
Kemudian diinginkan untuk dijalankan dengan object berupa sebuah shape atau sebuah form control Button.

Cara mengatur tombol berupa shape atau form control Button yang telah ada dalam worksheet, sehingga bisa menjalankan prosedur diatas ketika ditekan, adalah sebagai berikut :

Menggunakan shape
- klik kanan shape dalam worksheet -> pilih Assign Macro
- pilih nama prosedur PesanShapeNFormControl -> OK

Menggunakan Form Control
- klik kanan Button dalam worksheet -> pilih Assign Macro
- pilih nama prosedur PesanShapeNFormControl -> OK

Pada penggunaan ActiveX Control, proses pengaturan CommandButton untuk dapat menjalankan sebuah prosedur harus melalui event yang dimiliki si control CommandButton tersebut. Misalkan sebuah CommandButton bernama cmdPesan dijadikan sebuah tombol yang akan menjalankan prosedur lain bernama PesanActiveXControl.

Prosedur PesanActiveXControl berupa blok prosedur sebagai berikut :
   Public Sub PesanActiveXControl()
       MsgBox "Welcome, " & Environ("USERNAME")
   End Sub

Cara mengatur Command Button tersebut adalah sebagai berikut :

Aktifkan Design Mode

Bentuk prosedur event click (ketika tombol di-click maka akan diproses)

double click Command Button dalam worksheet -> muncul blok event Click milik tombol tersebut (gambar bawah sisi kiri) -> isi dengan nama prosedur PesanActiveXControl

Non aktifkan Design Mode

Catatan

Form Controls adalah bawaan Excel, sehingga cenderung dapat digunakan disemua komputer yang memiliki Excel dan tidak memerlukan pengaturan Excel Security.
ActiveX Controls tergantung pada file library, sehingga controls tersebut dapat berfungsi dengan baik jika komputer setempat (yang membuka workbook) juga memiliki file library yang berisi si control.
ActiveX Controls termasuk object yang diawasi oleh Excel Security, sehingga membutuhkan pengaturan Excel Security agar dapat berfungsi.
ActiveX Controls mudah ditata dengan baris-baris VBA dan memiliki banyak events.

:)

Insya Allah pembahasan berikutnya adalah tentang beberapa Form Control dan ActiveX Control yang sering digunakan seperti ComboBox, ListBox, dan sebagainya. Pembahasan masih tentang penggunaan controls dalam worksheet.

BelajarVBA 012 - Formula

2013-11-01T23:43:00.000+07:00

Coretan Mr. Kid

Selamat Tahun Baru 1435 H. Mari bersegera melakukan hijrah ke sesuatu yang lebih baik menurut tuntunan agama.

Mari kita lanjutkan obrolan tentang VBA yang sempat tertunda beberapa saat ini. Pembahasan kali ini adalah tentang memasang formula ke suatu area range dengan VBA. File yang bisa digunakan untuk mencoba secara mandiri adalah BelajarVBA012.xlsm.

Excel Reference Style

Excel menyediakan dua style dalam mereferensi ke suatu range, yaitu A1 dan R1C1. Style tersebut adalah tentang cara menuliskan rujukan ke suatu range. Karakteristik rujukan dan fungsional dari kedua style adalah sama. Pada A1 Style, karakter A mewakili cara penulisan indeks kolom Excel dan karakter 1 mewakili indeks baris Excel. Pada R1C1 Style, karakter R berarti Row yang diikuti dengan nomor indeks baris Excel yang diwakili oleh karakter 1 dibelakang karakter R. Sedangkan karakter C berarti Column yang diikuti dengan nomor indeks kolom Excel yang diwakili oleh karakter 1 dibelakang karakter C. Contoh A1 Style adalah sebuah alamat cell yang ditulis dengan $C$17. Karakter C menunjukkan indeks kolom Excel ke-3 dan karakter 17 mewakili indeks baris Excel ke-17. Jika alamat cell ber-A1 Style tadi ditulis dalam R1C1 style, maka menjadi R17C3 yang berarti Row ke-17 dan Column ke-3.

Banyak pengguna VBA yang berpendapat bahwa penggunaan R1C1 style dalam menyusun formula suatu range melalui VBA lebih mudah daripada menggunakan A1 style. Banyak juga pengguna VBA yang berpendapat bahwa penggunaan A1 style dalam menyusun formula suatu range melalui VBA lebih mudah daripada menggunakan R1C1 style. Pendapat saya pribadi, jika pengguna VBA saat berkegiatan di worksheet merasa lebih mudah menggunakan A1 style, biasanya akan lebih mudah menggunakan A1 style dalam menyusun formula suatu range melalui VBA. Begitu juga sebaliknya, jika pengguna VBA saat berkegiatan di worksheet merasa lebih mudah menggunakan R1C1 style, biasanya akan lebih mudah menggunakan R1C1 style dalam menyusun formula suatu range melalui VBA. Ketika pengguna VBA saat berkegiatan di worksheet merasa nyaman menggunakan style apapun, maka akan ada banyak opsi saat menyusun formula suatu range melalui VBA. Jadi, tetaplah pada apa yang telah dikuasai dan mulailah untuk dikembangkan. Tidak perlu memperdebatkan style mana yang lebih mudah digunakan dalam VBA.

Pemasangan formula dengan VBA adalah dengan memanfaatkan properti milik object range yang bernama :

Formula untuk A1 style dengan syntax :
object_range.Formula = "=bunyi formula"

FormulaR1C1 untuk R1C1 style dengan syntax :
object_range.FormulaR1C1 = "=bunyi formula"

Memasang formula ke suatu range dengan VBA

Data yang akan digunakan dalam pembahasan ini seperti yang ada di sheet bernama VBA dalam file BelajarVBA012.xlsm yang berisi seperti gambar berikut ini :

Pada gambar diatas, data menggunakan A1 style dan formula yang dibutuhkan adalah untuk menghitung :

Total Pembelian pada kolom total_beli dengan formulasi nilai kolom beli * nilai kolom qty
Total Penjualan pada kolom total_jual dengan formulasi nilai kolom jual * nilai kolom qty

Dari keadaan yang ada, maka dapat dijabarkan bahwa :

Karena kolom total_beli dan total_jual merujuk ke kolom qty, maka pada rujukan ke kolom qty dalam formula membutuhkan absolute reference pada sisi kolom ketika merujuk ke kolom qty.
Kolom total_beli berdampingan dengan kolom total_jual dan kolom beli yang dibutuhkan oleh formulasi total_beli juga berdampingan dengan kolom jual yang dibutuhkan formulasi total_jual, sehingga rujukan ke kolom beli saat menghitung total_beli, tidak perlu absolute reference pada sisi kolom agar formula bisa di-copy ke kolom total_jual yang secara otomatis akan membuat formulasi pada kolom total_jual akan merujuk ke kolom jual akibat proses copy formula tersebut.
Baris yang diproses adalah pada baris yang sama dan baris data lainnya juga merujuk ke baris masing-masing. Artinya, pada sisi baris yang merujuk ke kolom beli, kolom jual, dan kolom qty tidak membutuhkan absolute reference, sehingga formula bisa di-copy ke baris data berikutnya.

Langkah-langkah pemasangan formula ke area range diseluruh record data pada kolom total_beli dan total_jual adalah sebagai berikut :

Susun formula universal pada worksheet untuk mengkalkulasi total_beli pada record pertamanya.
- Bunyi formula sesuai reference style worksheet : (pilih salah satu)
  1. Formula dengan A1 style di cell i2 adalah =F2*$H2
  2. Formula dengan R1C1 style di cell R2C9 adalah =RC[-3]*RC8
- Buat sebuah general module dan sebuah prosedur sub untuk memasang formula. Misalkan nama prosedurnya adalah PasangFormula dengan bentuk blok prosedur sebagai berikut :
  Public Sub PasangFormula()
  
  End Sub
Definisikan variabel kerja
Dalam prosedur tersebut diberi baris kode deklarasi variabel kerja sebagai berikut :

Dim rngData As Range 'area data (termasuk header)
Dim rngTarget As Range 'area yang dipasangi formula
Dim lRecords As Long 'jumlah record
Definisikan range yang akan dipasangi formula
Pendefinisian dilakukan dengan informasi sebagai berikut :
1. Data berada di sheet bernama VBA, yang dimulai pada cell pertama (baris ke-1 dan kolom ke-1) dan berbentuk tabel yang baik. Area data seluruhnya bisa didefiniskan dengan properti CurrentRegion dari Cells(1,1) pada sheet VBA.
2. Jumlah baris yang akan dipasangi formula adalah sebanyak jumlah record data. Jumlah record data adalah jumlah baris data seluruhnya (termasuk header) dikurangi jumlah baris header (pada kasus ini, header membutuhkan 1 baris saja).
3. Area range yang akan dipasangi formula adalah area data seluruhnya yang diubah dimensi barisnya (resize pada sisi baris) menjadi sebanyak jumlah record data.
4. Jumlah kolom yang akan dipasangi formula yang berdampingan adalah sebanyak 2 kolom, yaitu kolom total_beli dan kolom total_jual yang berdampingan. Artinya, dibutuhkan pengubahan dimensi kolom dari seluruh area data menjadi sebanyak 2 kolom (resize pada sisi kolom menjadi 2).
5. Range yang akan dipasangi formula terletak mulai dibawah header (mulai baris ke-2), artinya, diperlukan offset sebanyak jumlah baris header dari seluruh area data (termasuk header). Pada kasus ini, header data hanya membutuhkan 1 baris saja, maka dilakukan offset sebanyak 1 baris.
6. Kolom pertama yang akan dipasangi formula terletak pada kolom ke-9, artinya, diperlukan offset sebanyak 8 kolom kekanan dari kolom pertama data.
Dari pendefinisian diatas, maka dapat disusun baris kode untuk mendapatkan area range yang akan dipasangi formula sebagai berikut :

Set rngData = Sheets("VBA").Cells(1, 1).CurrentRegion
lRecords = rngData.Rows.Count - 1
Set rngTarget = rngData.Resize(lRecords, 2).Offset(1, 8)
Memasang formula pada area range
Informasi yang diketahui adalah sebagai berikut :
- Area range yang akan dipasangi formula telah didefinisikan dalam variabel rngTarget. Artinya, properti milik variabel rngTarget yang akan digunakan.
- Bunyi formula yang disusun dalam worksheet adalah : (pilih salah satu reference style)
  1. A1 style berbunyi =F2*$H2
  2. R1C1 style berbunyi =RC[-3]*RC8
- Formula dalam worksheet telah bersifat universal atau dapat di-copy langsung ke seluruh area range yang harus dipasangi formula.
Dari informasi diatas, baris kode untuk memasang formula pada area range rngFormula adalah sebagai berikut : (pilih style sesuai kenyamanan masing-masing)
1. A1 style :
  rngTarget.Formula = "=F2*$H2"
2. R1C1 style :
  rngTarget.FormulaR1C1 = "=RC[-3]*RC8"

Bentuk prosedur bernama PasangFormula akan menjadi :
Public Sub PasangFormula()
    '------------------------'
    'deklarasi variabel kerja'
    '------------------------'
    Dim rngData As Range        'area data (termasuk header)
    Dim rngTarget As Range      'area yang dipasangi formula
    Dim lRecords As Long        'jumlah record

    '-----------------------------------------------'
    'pendefinisian area range yang dipasangi formula'
    '-----------------------------------------------'
    '1. pendefinisian area data seluruhnya
    'data tersusun sebagai tabel yang baik -> currentregion
    Set rngData = Sheets("VBA").Cells(1, 1).CurrentRegion

    '2. pendefinisian jumlah record data
    'nilai 1 adalah jumlah baris untuk header data
    lRecords = rngData.Rows.Count - 1

    '3 sampai 6 pendefinisian area range yang dipasangi formula
    '- rngData diubah dimensinya (resize) menjadi lRecords baris dan 2 kolom
    '- kemudian di-offset sebanyak 1 baris dan 8 kolom
    Set rngTarget = rngData.Resize(lRecords, 2).Offset(1, 8)

    '-----------------------------------------------'
    'pemasangan formula'
    '-----------------------------------------------'
    rngTarget.Formula = "=F2*$H2"
End Sub

Memasang Array Formula

Array formula selalu menghasilkan suatu blok array. Range yang masuk dalam area blok array tersebut tidak dapat diubah kecuali seluruh range yang ada dalam blok array tersebut yang diubah. Dimensi terkecil dari array blok adalah 1 baris x 1 kolom yang berisi sebuah cell berarray formula. Karakteristik lain dari blok array adalah tidak dapat diubah isinya dengan paste ketika salah satu range atau seluruh range dari blok array tersebut sedang aktif dikenai proses copy.

Misalnya, cell C17 berisi array formula berbunyi =MIN( IF( A1:A5>0 , A1:A5 ) ) yang akan membentuk sebuah blok array berdimensi 1 baris x 1 kolom. Ketika formula di C17 juga akan dipasang pada area range D17 sampai F17, maka cell C17 di-copy ke clipboard dengan menekan CTRL C. Jika range C17 sampai F17 diblok dan dilakukan paste (menekan CTRL V), maka proses paste akan gagal karena cell C17 adalah blok array yang sedang aktif di-copy. Maka proses paste ke area D17 sampai F17 harus dengan cara memblok D17 sampai F17 tanpa menyertakan C17, yang kemudian dilakukan paste dengan menekan CTRL V.

Array formula dipasang ke suatu range dengan memanfaatkan properti FormulaArray milik object range dengan syntax :
object_range.FormulaArray = "=bunyi array formula"
Ketika properti FormulaArray digunakan pada suatu object range yang berisi banyak cells, maka seluruh range yang dikenai properti FormulaArray akan membentuk sebuah blok array. Misalnya, ada sebuah variabel range yang bernama rngTarget yang berisi range D5:F9. Kemudian rngTarget dikenai properti FormulaArray dan diberi bunyi teks array formula apapun. Maka range D5:F9 akan membentuk sebuah blok array yang tidak akan mungkin diubah salah satu isi dari range D5:F9 tersebut.

Dengan demikian, pemasangan array formula membutuhkan ketelitian lebih dalam menentukan range yang akan diberi array formula agar blok array yang terbentuk sesuai kebutuhan. Pemasangan array formula dilakukan pada satu lokasi lebih dulu, kemudian di-copy ke lokasi lain. Artinya, diperlukan pendefinisian area range yang akan dipasangi array formula sebagai lokasi pertama dan pendefinisian area range yang akan menjadi lokasi paste dari array formula yang telah terpasang di lokasi pertama tadi. Syaratnya, area range lokasi paste tidak berpotongan sama sekali dengan area range lokasi pemasangan array formula pertama.

Contoh pemasangan array formula bisa memanfaatkan data yang ada di sheet bernama 'Array' seperti gambar berikut :

Kasus untuk memperlajari pemasangan array formula akan menggunakan A1 style. Contoh kasus tersebut adalah sebagai berikut :

Cell E2 diberi array formula berbunyi =A2, sehingga dalam cell E2 akan tampil di formula bar sebagai {=A2}. Kemudian array formula di E2 akan di-copy sampai E4. Misalkan nama prosedurnya adalah ArrayFormula1, maka bunyi prosedurnya adalah :
Public Sub ArrayFormula1()
    Dim rngPertama As Range 'range pertama yang diberi array formula
    Dim rngPaste As Range    'range lokasi paste array formula

    'lokasi pasang array formula yang akan di-copy
    Set rngPertama = Sheets("Array").Range("E2")

    'lokasi paste array formula di rngPertama
    Set rngPaste = Sheets("Array").Range("E3:E4")

    'pasang array formula di lokasi pertama
    rngPertama.FormulaArray = "=A2"

    'proses copy paste formula
    rngPertama.Copy
    rngPaste.PasteSpecial xlPasteFormulas
End Sub

Cell G2 diberi array formula berbunyi =A2, sehingga dalam cell G2 akan tampil di formula bar sebagai {=A2}. Kemudian array formula di G2 akan di-copy sampai H4. Proses paste dilakukan dalam 2 tahap, yaitu tahap 1 adalah ke area paste pertama di G3:G4 dan tahap 2 ke area paste kedua di H2:H4. Misalkan nama prosedurnya adalah ArrayFormula2, maka bunyi prosedurnya adalah :
Public Sub ArrayFormula2()
    Dim rngPertama As Range   'range pertama yang diberi array formula
    Dim rngPaste1 As Range    'range lokasi paste 1 array formula
    Dim rngPaste2 As Range    'range lokasi paste 2 array formula

    'lokasi pasang array formula yang akan di-copy
    Set rngPertama = Sheets("Array").Range("G2")

    'lokasi paste array formula di rngPertama
    Set rngPaste1 = Sheets("Array").Range("G3:G4")
    Set rngPaste2 = Sheets("Array").Range("H2:H4")

    'pasang array formula di lokasi pertama
    rngPertama.FormulaArray = "=A2"

    'proses copy paste formula
    rngPertama.Copy
    rngPaste1.PasteSpecial xlPasteFormulas
    rngPaste2.PasteSpecial xlPasteFormulas
End Sub

Range J2:K2 (1 baris x 2 kolom) diberi array formula berbunyi =A2:B2 (juga 1 baris x 2 kolom), sehingga dalam cell J2 dan K2 akan tampil di formula bar sebagai {=A2:B2}. Range J2:K2 akan menjadi sebuah blok array. Kemudian array formula di J2:K2 akan di-copy sampai J4:K4. Misalkan nama prosedurnya adalah ArrayFormula3, maka bunyi prosedurnya adalah :
Public Sub ArrayFormula3()
    Dim rngPertama As Range 'range pertama yang diberi array formula
    Dim rngPaste As Range    'range lokasi paste array formula

    'lokasi pasang array formula yang akan di-copy
    Set rngPertama = Sheets("Array").Range("J2:K2")

    'lokasi paste array formula di rngPertama
    Set rngPaste = Sheets("Array").Range("J3:J4")

    'pasang array formula di lokasi pertama
    rngPertama.FormulaArray = "=A2:B2"

    'proses copy paste formula
    rngPertama.Copy
    rngPaste.PasteSpecial xlPasteFormulas
End Sub

Range M2:M4 (3 baris x 1 kolom) diberi array formula berbunyi =A2:A4 (juga 3 baris x 1 kolom), sehingga dalam range M2:M4 akan tampil di formula bar sebagai {=A2:A4}. Range M2:M4 akan menjadi sebuah blok array. Kemudian array formula di M2:M4 akan di-copy sampai N2:N4. Misalkan nama prosedurnya adalah ArrayFormula4, maka bunyi prosedurnya adalah :
Public Sub ArrayFormula4()
    Dim rngPertama As Range 'range pertama yang diberi array formula
    Dim rngPaste As Range    'range lokasi paste array formula

    'lokasi pasang array formula yang akan di-copy
    Set rngPertama = Sheets("Array").Range("M2:M4")

    'lokasi paste array formula di rngPertama
    Set rngPaste = Sheets("Array").Range("N2")

    'pasang array formula di lokasi pertama
    rngPertama.FormulaArray = "=A2:A4"

    'proses copy paste formula
    rngPertama.Copy
    rngPaste.PasteSpecial xlPasteFormulas
End Sub

Range P2:Q4 (3 baris x 2 kolom) diberi array formula berbunyi =A2:B4 (juga 3 baris x 2 kolom), sehingga dalam range P2:Q4 akan tampil di formula bar sebagai {=A2:B4}. Range P2:Q4 akan menjadi sebuah blok array. Misalkan nama prosedurnya adalah ArrayFormula5, maka bunyi prosedurnya adalah :
Public Sub ArrayFormula5()
    Dim rngTarget As Range    'range yang diberi array formula

    'lokasi pasang array formula
    Set rngTarget = Sheets("Array").Range("P2:Q4")

    'pasang array formula di lokasi pertama
    rngTarget.FormulaArray = "=A2:B4"
End Sub

Catatan

Kaidah tentang penulisan data teks dalam VB/VBA terhadap karakter tanda petik dua (") :
- Ketika formula dalam worksheet berisi karakter tanda petik dua ("), maka seluruh karakter tanda petik dua harus diubah menjadi 2 buah tanda petik dua ("") saat bunyi formula tersebut akan dipasang dalam VBA. Misal, formula dalam worksheet berbunyi :
- maka dalam VBA, setiap karakter tanda petik dua yang mengapit kata 'kolom' harus diubah menjadi 2 buah tanda petik dua sesuai kaidah penulisan karakter tanda petik dua dalam suatu data teks di-VB/VBA, sehingga bunyi formula yang dipasang dalam VBA berubah menjadi :
- Bila range yang akan dipasangi formula adalah variabel bernama rngPasang, maka baris kode menjadi :
- Pada formula bar akan tampak formula berbunyi :
Pemasangan formula pada kolom yang tidak berdampingan (berjejer) dan tidak dapat disusunkan sebuah formula universal (yang dapat di-copy keseluruh area secara langsung), maka proses yang dijabarkan diatas harus dilakukan terhadap masing-masing kolom dengan formula masing-masing.
Properti Formula, FormulaR1C1, maupun FormulaArray dapat diberi bunyi formula dengan referensi A1 style maupun R1C1 style. Formula yang tampak pada formula bar akan mengikuti setting reference style yang digunakan oleh Excel setempat.

:)

Insya Allah pembahasan berikutnya adalah memasang tombol dalam worksheet berupa shape dan sebuah ActiveX CommandButton untuk menjalankan sebuah prosedur VBA. Pembahasan ini adalah pembukaan untuk pembahasan tentang object module UserForm.

Advance Filter dengan VBA untuk Lookup Records

2013-10-17T07:57:00.000+07:00

Oleh: Zanul Ulum

Tulisan ini berisi sharing penggunaan advance filter yang sering saya gunakan untuk mencari record dari sebuah tabel database yang disimpan dalam format workbook atau microsoft excel. Agar tabel bisa diolah dengan baik oleh advance filter, silahkan dibaca ulasan dari Mr. Kid tentang bagaimana cara menyusun data sebagai tabel yang baik.

Selain untuk memudahkan dalam penggunaan, VBA atau macro untuk AdvancedFilter dimaksudkan agar hasil filter bisa ditampilkan dalam workbook dan sheet di luar tabel database. Jika tidak menggunakan VBA, secara default hasil advance filter harus ditampilkan dalam sheet yang sama dengan sheet tabel database atau dalam sheet yang aktif. Pesan di bawah akan muncul apabila kita mencoba untuk menampilkan hasil filter di sheet yang lain.

Pesan di atas tidak muncul apabila proses Advance Filter dilaksanakan melalui VBA atau macro dengan menggunakan method AdvancedFilter. Untuk mengetahui cara penggunaan dari method tersebut, tekan tombol F2 atau Object Browser saat di Microsoft Visual Basic Editor (VBE). Masukkan keyword atau kata kunci AdvancedFilter, kemudian click icon teropong.

Click atau pilih AdvancedFilter, kemudian tekan F1 untuk melihat keterangan detail dari method yang dipilih.

Dari keterangan object browser di atas bisa dijelaskan sebagai berikut:

Fungsi AdvancedFilter bekerja di object Excel.Range atau object Range dengan menggunakan parameter berupa Action, [CriteriaRange], [CopyToRange],[Unique].
Parameter Action harus ada sedangkan [CriteriaRange], [CopyToRange],[Unique] bersifat optional atau pilihan. Ada dua pilihan untuk action yaitu xlFilterCopy dan xlFilterInPlace. xlFilterCopy dipilih untuk proses advance filter ini karena hasil filter akan ditampilkan di tempat lain bukan ditempat table yang sama (xlFilterInPlace).

Dalam contoh ini saya menggunakan dua buah file workbook yaitu workbook yang berisi tabel database dan workbook yang berisi macro atau vba. File database saya beri nama isbn.xlsx berisi tabel nomer isbn dan penerbitnya, sedangkan file isbn.xlsm berisi macro atau vba untuk menampilkan hasil lookup record. Kedua file tersebut harus diletakkan dalam folder yang sama.

Tabel database di file isbn.xlsx disimpan dalam sheet [Publisher] tersusun dari 4 (empat) header/field yaitu,
- [Publisher code]
- [Publisher]
- [Notes]
- [strKey]

Field [strKey] adalah header dummy hasil gabungan dari 3 (tiga) header sebelumnya. Header dummy ini dibuat secara otomatis dengan macro/vba saat file pertama kali dibuka atau melalui event Workbook_Open().

Maksud dari penggabungan ini adalah untuk membuat header bantuan sehingga jika kita memasukkan sembarang keyword atau kata kunci dalam satu cell, bisa didapatkan record antar header.

Dalam file isbn.xlsm terdapat sheet [LookUpPublisher] dengan tampilan sebagai berikut:

Range [B1] adalah tempat untuk memasukkan keyword atau kata kunci. Jika ingin mencari [publisher code] dengan kode yang diawali dengan 870, maka di [B1] dimasukkan kata kunci 870*. Record hasil pencarian akan ditampilkan mulai baris ke 3.

jika ingin mencari [publisher]=Autodesk, di [B1] dimasukkan kata kunci Autodesk. Apabila ingin dicari [Notes] yang berisi UK, di [B1] dimasukkan kata kunci UK.

Jadi dengan memasukkan satu kata kunci di [B1], program akan mencari kata kunci tersebut di header [Publisher Code], [Publisher] dan [Notes].

Untuk mengetahui alur program dan code vba-nya download file berikut.

BelajarVBA 011 - Range 06 (Range ke Range, CurrentRegion)

2013-09-26T21:23:00.000+07:00

Coretan Mr. Kid

Rajin pangkal pandai

Pembahasan kali ini adalah tentang merujuk suatu area range dari 2 (dua) object range dan properti CurrentRegion milik Range. File berisi data yang bisa digunakan untuk uji coba pada pembahasan kali ini adalah BelajarVBA011_06.xlsm.

Area range berasal dari 2 (dua) range

Sebuah area range dibentuk dengan diketahui 2 (dua) range, yaitu 2 (dua) range pojok yang berseberangan diagonal. Misalnya, area range A1:C5 bisa dibentuk dengan mengetahui range A1 dan C5 atau range C1 dan A5.

Syntax : Range( object_range1 , object_range2 )

Prosedur berikut ini adalah contohnya.
Public Sub TentangRangeToRange()
    Dim rng1 As Range, rng2 As Range, rngHasil As Range

    '>> Contoh 1 : Area A1:C5 diketahui A1 dan C5
    Set rng1 = Range("A1")
    Set rng2 = Range("C5")
    Set rngHasil = Range( rng1 , rng2 )
    rngHasil.Copy
    MsgBox "rng1 : " & rng1.Address & vbCrLf & _
            "rng2 : " & rng2.Address & vbCrLf & _
            "rngHasil dibentuk dengan Range( rng1 , rng2 ) : " _
            & rngHasil.Address

    '>> Contoh 2 : Area A1:C5 diketahui C1 dan A5
    Set rng1 = Range("C1")
    Set rng2 = Range("A5")
    Set rngHasil = Range( rng1 , rng2 )
    rngHasil.Copy
    MsgBox "rng1 : " & rng1.Address & vbCrLf & _
            "rng2 : " & rng2.Address & vbCrLf & _
            "rngHasil dibentuk dengan Range( rng1 , rng2 ) : " _
            & rngHasil.Address
End Sub

Pada prosedur diatas, contoh 1 yang akan membentuk area range A1:C5 dengan diketahui range A1 dan C5. Proses yang terjadi pada bagian tersebut adalah :

Deklarasi variabel object range untuk rng1, rng2, dan rngHasil
Definisi isi rng1 yang diisi dengan range A1, sedangkan rng2 diisi dengan range C5
Kemudian rngHasil didefinisikan sebagai hasil penyusunan area range dari rng1 sampai rng2 oleh baris kode :

Set rngHasil = Range( rng1 , rng2 )

Proses Copy adalah contoh penggunaan rngHasil dan pesan kepada user ditampilkan menggunakan MsgBox

Pada bagian contoh 2 juga mirip dengan contoh 1. Perbedaannya adalah range yang menjadi penyusun area. Pada contoh 1 diketahui range pojok kiri atas dan pojok kanan bawah, sedangkan pada contoh 2 adalah range pojok kanan atas dan range pojok kiri bawah. Penggunaan object range seperti ini bisa digunakan untuk membentuk area range sebaris, sekolom, ataupun n baris x m kolom. Setiap object range yang menjadi penyusunnya (misal rng1 dan rng2 pada contoh diatas) dapat didefinisikan dengan banyak cara, seperti menggunakan properti End atau alamat range secara langsung dan sebagainya.

Properti CurrentRegion

Properti ini digunakan untuk mendapatkan 1 (satu) area range dari sebuah range yang menjadi titik awalnya. Penggunaan properti ini adalah seperti ketika di worksheet melakukan Goto Special dan memilih CurrentRegion (tekan CTRL G -> tekan Special -> pilih CurrentRegion -> OK).

Syntax : object_range.CurrentRegion

Contoh prosedur berikut ini bisa memberi sedikit gambaran tentang penggunaan properti CurrentRegion. Contoh ini menggunakan data yang berada di sheet bernama 'Data16'. Contoh ini berupa penggunaan CurrentRegion dengan range titik awalnya di range A1 untuk mendapatkan seluruh area tabel data di sheet tersebut.
Public Sub TentangCurrentRegion()
    Dim rngAwal As Range, rngHasil As Range

    Sheets("Data16").Activate
    Set rngAwal = Sheets("Data16").Range("A1")
    Set rngHasil = rngAwal.CurrentRegion
    rngHasil.Copy
    MsgBox "Range titik awal : " & rngAwal.Address & vbCrLf & _
       "Range(""" & rngAwal.Address & """).CurrentRegion : " & _
       rngHasil.Address
End Sub
Pada prosedur diatas, proses yang terjadi adalah :

Deklarasi variabel object range bernama rngAwal dan rngHasil
Mengaktifkan sheet bernama 'Data16' pada baris kode :
Sheets("Data16").Activate
Mendefinisikan variabel rngAwal dengan range A1
Memanfaatkan properti CurrentRegion milik variabel rngAwal dan hasilnya disimpan divariabel rngHasil
Range yang ada dalam rngHasil di-Copy ke Clipboard, kemudian menampilkan pesan kepada user melalui kotak pesan

Cara Excel membentuk sebuah area range

Properti CurrentRegion dalam membentuk area range mengikuti cara Excel dalam membentuk area range. Berikut ini adalah gambar untuk menjelaskan hal ini. Warna kuning adalah cell aktif dan warna merah adalah cells yang ada disekitar cell aktif (kuning) yang berdampingan secara kontinyu.

Ketika properti CurrentRegion dari cell aktif (kuning) yang berisi data dimanfaatkan, maka area range hasil akan tergantung cells merah yang berisi data. Seluruh cell merah yang berisi data akan diblok. Dirinya (cell aktif) yang berisi data juga akan terblok. Kemudian dibuatlah kotak area dari seluruh cell pojok terluar yang terblok. Hasilnya, pasti cell aktif akan berada dalam area range hasil.
Ketika cell aktif (kuning) tidak berisi data, maka yang terblok adalah seluruh cell merah yang berisi data saja, sedangkan dirinya (cell aktif) tidak ikut terblok. Kemudian dibuatlah kotak area dari seluruh cell pojok terluar yang terblok. Akibatnya, cell aktif belum tentu berada dalam area range hasil. Misalnya saja, ketika yang terblok hanyalah cell merah di pojok kiri atas dan kiri bawah saja, maka area range hasil adalah range satu kolom dikiri cell aktif dan cell aktif tidak masuk dalam area range hasil tersebut.

Cell berisi data adalah cells yang berisi suatu nilai tertentu atau suatu formula. Beberapa karakter tidak akan tampak dalam sebuah cell seperti karakter kosong (NULLSTRING), karakter ENTER, karakter TAB, dan sebagainya. Bisa jadi area range yang dibentuk oleh Excel akan tampak tidak seperti yang dijabarkan di atas, yaitu beberapa cell yang tampak tidak ada isinya turut menjadi bagian dari area range yang terbentuk. Hal seperti ini umumnya terjadi karena ada cell yang berisi data :

karakter-karakter yang tidak tampak
berisi suatu formula yang menghasilkan karakter tidak tampak
di-format number custom tertentu (termasuk melalui Conditional Formatting)

Beberapa bentuk dataset dan pengaruhnya pada hasil CurrentRegion

Susunan data dalam dataset akan mempengaruhi hasil CurrentRegion dari suatu range titik awal. Seringkali ditemui susunan dataset memiliki blank cells. Susunan blank cells yang membuat sebuah baris kosong atau kolom kosong bisa membuat hasil CurrentRegion menjadi tidak sesuai dengan yang dibutuhkan ketika tidak tepat menentukan range titik awalnya. Bahkan bisa membuat dataset menjadi tidak memungkinkan untuk bisa didapatkan area range-nya melalui properti CurrentRegion akibat kehadiran blank cells sebaris atau sekolom tersebut.

Berikut ini adalah beberapa contoh pengaruh blank cells yang ada dalam dataset yang sering ditemui. Hasil CurrentRegion berupa area yang dibatasi garis batas (border) warna hitam yang tebal. Warna kuning didalam area hasil CurrentRegion adalah range yang bisa dijadikan titik awal untuk mendapatkan area range tersebut. Jadi range titik awal bisa dipilih salah satu dari cell yang berwarna kuning tersebut.

Dataset dengan record dan kolom yang rapat

Sebuah dataset yang memiliki kaidah penyusunan tabel data yang baik. Seluruh record data adalah rapat atau tidak ada baris kosong diantara 2 (dua) buah record yang berisi data. Kolom data juga rapat atau tidak ada kolom kosong antara 2 (dua) buah kolom yang berisi data. Setiap kolom memiliki nama kolom (header). Header diletakkan cukup pada 1 (satu) baris Excel saja. Record pertama dimulai setelah baris header atau tidak ada baris kosong antara baris header dengan record pertama.

Pada dataset seperti digambar, dimanapun letak range titik awal, selama didalam dataset, akan menghasilkan area range hasil CurrentRegion berupa seluruh dataset. Dataset 1.1 adalah sebuah tabel data yang tersusun secara rapat. Blank cells pada dataset 1.1 ini umumnya karena memang field tersebut tidak memiliki nilai. Jadi, arti setiap blank cells pada dataset 1.1 adalah 'tidak ada nilainya'. Dataset 1.2 adalah sebuah crosstab data yang tersusun secara rapat seperti dataset 1.1. Arti blank cells pada dataset 1.2 berarti 'memiliki nilai yang sama dengan baris data sebelumnya'.

Blank cells pada dataset 1.1 maupun dataset 1.2 tidak akan mempengaruhi hasil penggunaan properti CurrentRegion. karena minimal ada satu kolom yang pasti penuh terisi data. Kolom utama pada dataset 1.1 pasti berisi data diseluruh record-nya. Jadi selalu ada setidaknya 1 (satu) kolom yang berisi data penuh diseluruh record-nya. Pada dataset 1.2 tidak ada baris kosong dan data yang tidak memiliki nilai akan diisi dengan karakter kosong atau teks '(blank)' karena arti blank cells pada dataset 1.2 adalah 'memiliki nilai yang sama dengan baris data sebelumnya'.

Satu baris kosong antara header dan record data

Dataset 2 (yang ditampilkan menjadi dataset 2.1 dan dataset 2.2) memiliki sebuah baris kosong antara header dan record data. Satu baris kosong ini akan membuat dataset terpisah menjadi 2 (dua) area, yaitu area baris header (area diatas baris kosong) dan area record data (area dibawah baris kosong). Kehadiran satu baris kosong ini tidak sepenuhnya memberi masalah pada penggunaan CurrentRegion. Penggunaan properti CurrentRegion akan bermasalah ketika ada lebih dari satu baris kosong. Meskipun demikian, pemilihan range titik awal pada dataset seperti ini harus lebih diperhatikan, yaitu :

Ketika range titik awal terletak disalah satu field dalam record data (kuning), maka area range hasil CurrentRegion adalah pada area yang dibatasi oleh garis batas (border) warna hitam tebal (lihat Dataset 2.1).
Ketika range titik awal diletakkan pada salah satu cell di baris header, maka area range hasil CurrentRegion adalah baris header (bayangkan di Dataset 2.1).
Ketika range titik awal diletakkan pada salah satu cell dibaris kosong, maka area range hasil CurrentRegion adalah seluruh dataset (lihat Dataset 2.2).

Andaikan yang ada adalah satu kolom kosong, maka kehadiran satu kolom kosong tersebut adalah sebagai pembentuk dua area data, yaitu area data disisi kiri kolom kosong dan area data disisi kanan kolom kosong. Jika range titik awal adalah disalah satu cell disisi kiri kolom kosong, maka area range hasil CurrentRegion adalah area data disisi kiri kolom kosong. Begitu juga jika range titik awal dipilih disalah satu cell disisi kanan kolom kosong, akan menghasilkan area range hasil CurrentRegion berupa area data disisi kanan kolom kosong. Ketika range titik awal dipilih pada salah satu cell di kolom kosongnya, maka akan diperoleh area range hasil CurrentRegion adalah seluruh dataset. Kehadiran dua atau lebih kolom kosong akan menyulitkan penggunaan properti CurrentRegion.

Lebih dari satu baris kosong

Dataset yang berisi banyak baris kosong, apalagi yang tersebar (tidak terkumpul menjadi satu area), akan menyulitkan penggunaan properti CurrentRegion. Pada dataset seperti ini sebaiknya menghindari penggunaan properti CurrentRegion. Seperti pada gambar diatas, penggunaan properti CurrentRegion tidak dapat menghasilkan area range hasil berisi seluruh dataset. Jika ingin mendapatkan area range diseluruh dataset, dibutuhkan kolom bantu (kolom buatan yang ditambahkan) yang berisi data diseluruh record dataset.

Dampak karakter tidak tampak atau formula bernilai karakter kosong

Kadangkala ditemui suatu dataset dengan banyak baris kosong maupun kolom kosong yang tersebar. Ketika properti CurrentRegion digunakan pada dataset tersebut, area range yang dihasilkan adalah diseluruh area dataset. Padahal tidak ada penambahan kolom bantu berisi data diseluruh record. Contohnya seperti gambar berikut ini :

Dataset 4 (gambar diatas) mirip dengan dataset 3, tetapi pada dataset 4 ini, dimanapun range titik awal pilihan dalam dataset, akan menghasilkan area range diseluruh dataset yang dibatasi oleh garis batas (border) hitam tebal. Padahal pada daset 4 ini tampak adanya baris kosong antar record data, yang semestinya menjadi pemisah antar area dalam dataset. Ketika ditemukan kejadian seperti ini, maka dugaan pertama adalah adanya karakter kosong hasil formula atau adanya karakter tidak tampak pada baris-baris kosong tersebut. Cara memeriksanya adalah dengan mewarnai seluruh range yang blank. Dengan demikian akan ditemukan keberadaan range yang tampak kosong tetapi tidak turut terwarnai seperti blank cell.

Cara untuk memilih blank cell pada suatu area range adalah sebagai berikut : (bisa dicoba pada file BelajarVBA011_06.xlsm disheet bernama 'AreaRange')

Blok seluruh range dataset
Aktifkan fitur Goto (tekan CTRL G)
Aktifkan opsi Special (tekan tombol Special)
Pilih opsi Blank
Tekan OK

Blank cells yang terpilih bisa diberi warna tertentu, misalnya merah. Seluruh cell yang tampak kosong tapi bukan berwarna merah adalah cell yang berisi formula yang menghasilkan karakter kosong atau berisi karakter tidak tampak.

Simpulan

Penggunaan properti CurrentRegion bisa mendapatkan seluruh area range dataset berdasar sebuah range yang ada dalam dataset. Properti CurrentRegion memang rentan terhadap kehadiran baris kosong maupun kolom kosong. Karena data dasar (data yang disimpan dan dipelihara, seperti tabel database) tidak pernah berisi baris kosong atau kolom kolom kosong, maka properti CurrentRegion sangat bermanfaat dalam mendapatkan seluruh area range tabel data.

:)

Insya Allah pembahasan berikutnya adalah tentang memasang formula melalui VBA.

BelajarVBA 011 - Range 05 (Properti End)

2013-09-23T14:18:00.000+07:00

Coretan Mr. Kid

Merasa diri kita memiliki kemampuan yang lebih tinggi dari orang lain adalah awal kerusakan karakter budi pekerti yang baik.

Pembahasan kali ini adalah tentang mendapatkan sebuah range dibatas terluar tertentu dengan memanfatkan properti End milik object Range. Pembahasan kali ini akan menggunakan data di sheet bernama 'Data16' dan 'Data15' pada file BelajarVBA011_05.xlsm, yang juga dapat dijadikan sebagai media untuk mempraktekkan langsung script yang ada.

Kebutuhan untuk segera menuju ke kolom terakhir di baris terakhir suatu dataset dari suatu range tertentu dapat dilakukan dengan memanfaatkan properti End milik object Range. Properti End akan mendapatkan 1 (satu) buah range yang terakhir pada batas area arah pergerakannya. Penggunaan properti END sama halnya dengan penekanan tombol CTRL yang diikuti penekanan arah panah pada keyboard saat berada di Workbook window (berada di worksheet).

Syntax :
object_range.END(arah_pergerakan)

Properti End memiliki 4 (empat) arah pergerakan, yaitu :
(Lihat gambar berikut. Range merah adalah range pojok kiri atas dari area range yang menjadi range asal)

xlDown
bergerak kearah bawah (baris selanjutnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range N baris x 1 kolom.
xlUp
bergerak kearah atas (baris sebelumnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range N baris x 1 kolom.
xlToRight
bergerak kearah kanan (kolom selanjutnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range 1 baris x N kolom.
xlToLeft
bergerak kearah kiri (kolom sebelumnya) dari range pojok kiri suatu area range dengan area pergerakan sebagai sebuah range 1 baris x N kolom.

Properti End akan bergerak dalam arah range sebaris atau range sekolom pada worksheet tersebut. Batas maksimum pergerakan End adalah sampai batas worksheet, yaitu :

baris terakhir Excel sebagai batas terbawah
baris 1 Excel sebagai batas teratas
kolom terakhir Excel sebagai batas terkanan
kolom 1 Excel sebagai batas terkiri

Karakteristik pergerakan properti End adalah sebagai berikut :
(berlaku untuk seluruh arah pergerakan, contoh pada gambar adalah arah dengan xlToLeft)

Melintasi blank cells sampai batas worksheet (tanpa ada satupun cell berisi data setelah range asal)
- Terjadi ketika digunakan mulai dari sebuah range asal, baik berisi data (Case 1.1) maupun blank (Case 1.2), kemudian range selanjutnya adalah blank cells dan tidak pernah bertemu satupun range berisi data sampai batas worksheet.
- Range asal bisa berisi data seperti Case 1.1 ataupun berupa blank cell seperti Case 1.2
- Hasil pergerakan End adalah pada range yang berada dibatas worksheet. Seperti pada gambar yang menggunakan xlToLeft, maka hasil pergerakan End adalah pada range yang berada dikolom pertama Excel (kolom A) dibaris range asalnya.
- Jika area lintasan yang berupa blank cells tersebut dirapatkan hingga tidak ada lintasannya lagi alias range asal berada di kolom A, maka pergerakan End tidak mengalami pergeseran karena lokasi range hasil adalah pada range batas worksheet yang dalam hal ini juga di kolom A.

Melintasi blank cells sampai suatu range yang ada isinya
- Terjadi ketika digunakan mulai dari sebuah range asal, baik berisi data (Case 1.1) maupun blank (Case 1.2), kemudian range selanjutnya adalah blank cells sampai bertemu 1 (satu) range yang berisi data
- Range asal bisa berisi data seperti Case 2.1 ataupun berupa blank cell seperti Case 2.2
- Hasil pergerakan End adalah pada range yang berisi data setelah range asal. Seperti pada gambar yang menggunakan xlToLeft, maka hasil pergerakan End adalah pada 1 (satu) range yang berisi data berupa teks 'Kid' sesuai baris range asal masing-masing Case
- Jika area lintasan yang berupa blank cells tersebut dirapatkan hingga tidak ada lintasannya lagi alias range asal berdampingan dengan range berisi data setelah range asal diarah pergerakannya, maka pergerakan End pada :
  1. range asal yang berupa blank cell (Case 2.2) akan menghasilkan range hasil di range berisi data diarah pergerakan setelah range asal alias sesuai kaidah Case 2.2 tersebut
  2. range asal yang berisi data (Case 2.1) akan mengikuti kaidah Case 3 yang dijelaskan berikutnya

Bergerak disuatu range yang padat berisi data
- Terjadi ketika digunakan mulai dari sebuah range asal yang berisi data dengan range selanjutnya juga berisi data
- Seluruh range berisi data, yaitu mulai dari range asal sampai range tertentu yang bersinggungan (berdampingan) searah pergerakan
- Hasil pergerakan adalah pada range terakhir diarah pergerakan yang berisi data. Seperti pada gambar yang menggunakan xlToLeft, maka hasil pergerakan End adalah pada range terakhir dari area range berisi data yang ada, yaitu pada cell yang berisi data teks berbunyi 'Kid'

Karakteristik yang dijabarkan diatas adalah hal-hal yang perlu diwaspadai dalam penggunaan properti End. Dengan demikian resiko dari pergerakan End berdasar struktur data atau pola data yang akan diolah sudah dapat diketahui.

Contoh-contoh berikut ini akan menggunakan data di sheet bernama 'Data16'. Pada sheet tersebut ada sebuah tabel berkaidah database pada area range A1:H12. Header tabel berada di baris 1 Excel dan hanya 1 (satu) baris saja. Record data dimulai dari baris 2 Excel. Kolom-kolom kunci milik tabel adalah pada kolom A sampai kolom E, yang seluruhnya harus berisi data. Jadi, sheet 'Data16' berisi tabel yang padat berisi. Sheet 'Data16' tersebut adalah seperti gambar berikut ini :

Contoh 1 : Bergerak dalam area data

Penggunaan properti End pada sheet 'Data16' dengan range asal adalah range E1 akan diletakkan pada prosedur berikut ini :
Public Sub TentangEnd_Contoh1()
    Dim sht As Worksheet
    Dim rngAsal As Range, rngHasil As Range

    'pendefinisian sheet kerja,
    'disimpan dalam variabel sht
    'dan sheet diaktifkan (activate)
    Set sht = Sheets("Data16")
    sht.Activate

    'baris-baris kode akan diletakkan setelah baris ini

End Sub
Variabel object Worksheet bernama sht telah didefinisikan berisi sheets 'Data16' pada baris berbunyi :
Set sht = Sheets("Data16")

Penggunaan xlDown (ke BAWAH)
   Set rngAsal = sht.Range("E1")
   Set rngHasil = rngAsal.End(xlDown)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
      "Arah : xlDown" & vbCrLf & _
      "Hasil : " & rngHasil.Address
Proses pada potongan script diatas adalah :
- Mendefinisikan variabel rngAsal agar berisi range E1 yang ada di sheet yang tersimpan dalam variabel object worksheet bernama sht.
- Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlDown yang dimulai dari rngAsal. Variabel rngAsal berisi range E1. Jadi properti End digunakan dari range E1 [rngAsal] ke BAWAH [.End(xlDown)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range E12 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
  Set rngHasil = rngAsal.End(xlDown)
- Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
- Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.

Penggunaan xlToLeft (ke KIRI)
Setelah proses menggunakan xlDown diatas, proses dilanjutkan dengan menggunakan xlToLeft. Jadi, range asal adalah range hasil proses sebelumnya yang bernama rngHasil yang berisi range E12.
   Set rngAsal = rngHasil
   Set rngHasil = rngAsal.End(xlToLeft)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
      "Arah : xlToLeft" & vbCrLf & _
      "Hasil : " & rngHasil.Address
Proses pada potongan script diatas adalah :
- Mendefinisikan variabel rngAsal agar berisi range yang disimpan oleh variabel rngHasil. Pada contoh ini, rngHasil berisi range hasil proses pada penggunaan xlDown yang berupa range E12. Jadi, rngAsal akan berisi range E12.
- Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlToLeft yang dimulai dari rngAsal. Variabel rngAsal berisi range E12. Jadi properti End digunakan dari range E12 [rngAsal] ke KIRI [.End(xlToLeft)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range A12 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
  Set rngHasil = rngAsal.End(xlToLeft)
- Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
- Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.

Penggunaan xlUp (ke ATAS)
Setelah proses menggunakan xlToLeft diatas, proses dilanjutkan dengan menggunakan xlUp. Jadi, range asal adalah range hasil proses sebelumnya yang bernama rngHasil yang berisi range A12.
   Set rngAsal = rngHasil
   Set rngHasil = rngAsal.End(xlUp)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
      "Arah : xlUp" & vbCrLf & _
"Hasil : " & rngHasil.Address
Proses pada potongan script diatas adalah :
- Mendefinisikan variabel rngAsal agar berisi range yang disimpan oleh variabel rngHasil. Pada contoh ini, rngHasil berisi range hasil proses pada penggunaan xlToLeft yang berupa range A12. Jadi, rngAsal akan berisi range A12.
- Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlUp yang dimulai dari rngAsal. Variabel rngAsal berisi range A12. Jadi properti End digunakan dari range A12 [rngAsal] ke ATAS [.End(xlUp)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range A1 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
  Set rngHasil = rngAsal.End(xlUp)
- Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
- Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.

Penggunaan xlToRight (ke KANAN)
Setelah proses menggunakan xlUp diatas, proses dilanjutkan dengan menggunakan xlToRight. Jadi, range asal adalah range hasil proses sebelumnya yang bernama rngHasil yang berisi range A1.
   Set rngAsal = rngHasil
   Set rngHasil = rngAsal.End(xlToRight)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
       "Arah : xlToRight" & vbCrLf & _
       "Hasil : " & rngHasil.Address
Proses pada potongan script diatas adalah :
- Mendefinisikan variabel rngAsal agar berisi range yang disimpan oleh variabel rngHasil. Pada contoh ini, rngHasil berisi range hasil proses pada penggunaan xlUp yang berupa range A1. Jadi, rngAsal akan berisi range A1.
- Mendefinisikan variabel rngHasil yang berupa hasil suatu proses penggunaan properti End dengan arah xlToRight yang dimulai dari rngAsal. Variabel rngAsal berisi range A1. Jadi properti End digunakan dari range A1 [rngAsal] ke KANAN [.End(xlToRight)]. Range hasil penggunaan properti End akan berisi 1 (satu) cell beralamat di range H1 dan disimpan ke dalam variabel bernama rngHasil. Proses ini adalah pada baris kode :
  Set rngHasil = rngAsal.End(xlToRight)
- Contoh pemanfaatan hasil penggunaan properti End, yang dalam hal ini berupa proses meng-Copy ke Clipboard.
- Menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat range asal, arah pergerakan End, dan alamat range hasil.

Pengaruh susunan data
Selain sheet 'Data16', pada workbook tersebut juga ada sheet 'Data15' yang digunakan pada pembahasan lalu tentang UsedRange, Intersection, dan Union seperti gambar berikut ini :

Proses dalam prosedur bernama TentangEnd_Contoh1 diatas diterapkan pada dataset 'cabang2'. Range asal saat penggunaan properti xlDown adalah pada range J5. Proses dalam prosedur akan berjalan sebagai berikut :

dari range J5
ke range J10 (hasil xlDown)
ke range I10 (hasil xlToLeft)
ke range I5 (hasil xlUp)
ke range J5 (hasil xlToRight)

Pada proses yang terjadi, area range I12:J15 tidak pernah terlintasi atau menjadi hasil dari proses penggunaan properti End diatas. Artinya, tidak semua record pada dataset 'cabang2' terlingkupi oleh proses jika setiap proses dibuat garis maya pembentuk batas. Hal ini disebabkan karena kehadiran blank cells di range I11:J11. Karakteristik pergerakan properti END yang telah dijelaskan diatas, terutama Case 3, tampak jelas pada contoh proses ini.

Contoh 2 : Mendapatkan batas terluar area data

Properti End sering digunakan untuk mendapatkan batas terluar area data. Batas terluar area data bisa berarti :

Baris terakhir area data
Kolom terkanan area data
Baris pertama area data ketika data diletakkan bukan pada baris pertama Excel atau ada baris kosong antara header data dengan record pertama data
Kolom pertama area data ketika data diletakkan bukan pada kolom pertama Excel

Penggunaan properti End pada sheet 'Data16' untuk mendapatkan batas-batas terluar area data akan diletakkan pada prosedur berikut ini :
Public Sub TentangEnd_Contoh2()
    Dim sht As Worksheet
    Dim rngAsal As Range, rngHasil As Range

    'pendefinisian sheet kerja,
    'disimpan dalam variabel sht
    'dan sheet diaktifkan (activate)
    Set sht = Sheets("Data16")
    sht.Activate

    'baris-baris kode akan diletakkan setelah baris ini

End Sub
Variabel object Worksheet bernama sht telah didefinisikan berisi sheets 'Data16' pada baris berbunyi :
Set sht = Sheets("Data16")

Baris terakhir data dengan xlDown
   '>> Menggunakan xlDown
   '- Khusus untuk tabel yang pasti memiliki record data
   ' dan rapat berisi data
   ' (tidak ada blank cell diantara record)
   '- Range asal pada baris header di kolom yang penuh datanya
   '- Header di baris 1 Excel
   '- Kolom yang penuh datanya adalah kolom A
   '- Range asal adalah A1
   Set rngAsal = sht.Range("A1")
   Set rngHasil = rngAsal.End(xlDown)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
      "Arah : xlDown" & vbCrLf & _
      "Hasil : " & rngHasil.Address & vbCrLf & _
      "Baris terakhir data (baris Excel) : " & rngHasil.Row
Pada penggunaan xlDown, range asal harus berada disalah satu record data yang bukan record terakhir pada kolom yang pasti penuh isinya, seperti pada kolom-kolom kunci yang harus ada isinya. Proses pada potongan script yang menggunakan xlDown diatas adalah :
- Mendefinisikan range asal di range A1
- Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlDown terhadap rngAsal
- Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
- Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks baris Excel dari range hasil yang merupakan baris terakhir data
Bahan latihan dan uji coba
Apa yang terjadi jika syarat :
- 'pasti memiliki record data' tidak terpenuhi alias script diatas dijalankan pada data yang tidak memiliki record ?
- 'rapat berisi data' tidak terpenuhi alias script diatas dijalankan pada data yang ada blank cell-nya ?

Baris terakhir data dengan xlUp
   '>> Menggunakan xlUp
   '- Khusus untuk tabel yang tidak mungkin
   ' berisi record diseluruh baris Excelnya
   '- Range asal dari baris terakhir Excel
   '- Jumlah baris Excel didapat dengan Rows.Count
   '- Kolom yang penuh datanya adalah kolom A (kolom ke-1 Excel)
   '- Range asal adalah Cells(Rows.Count, 1)
   ' yang berarti cell pada
   ' baris Excel terakhir (hasil Rows.Count)
   ' dikolom ke-1 Excel
   Set rngAsal = sht.Cells(Rows.Count, 1)
   Set rngHasil = rngAsal.End(xlUp)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
       "Arah : xlUp" & vbCrLf & _
       "Hasil : " & rngHasil.Address & vbCrLf & _
       "Baris terakhir data (baris Excel) : " & rngHasil.Row
Pada penggunaan xlUp, range asal harus berada dibaris diluar area data. Umumnya range asal adalah baris terakhir Excel pada kolom data yang baris terakhirnya pasti berisi data, seperti pada kolom-kolom kunci yang harus ada isinya. Proses pada potongan script yang menggunakan xlUp diatas adalah :
- Mendefinisikan range asal di baris terakhir Excel pada kolom A. Kolom A adalah kolom ke-1 Excel. Baris terakhir Excel bisa didapat dengan Rows.Count dan range di baris terakhir Excel pada kolom A bisa didapat dengan menggunakan properti Cells yang membutuhkan informasi nomor indeks baris Excel dan nomor indeks kolom Excel. Proses ini adalah pada baris kode :
  Set rngAsal = sht.Cells(Rows.Count, 1)
- Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlUp terhadap rngAsal
- Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
- Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks baris Excel dari range hasil yang merupakan baris terakhir data
Bahan latihan dan uji coba
- Apa yang terjadi ketika potongan script diatas dijalankan pada seluruh baris Excel yang terisi data ?
Tidak ada hal yang mustahil meskipun menggunakan Excel 2007 ke atas yang berisi sampai satu juta lebih baris Excel. Dulu pada saat Excel 2003 kebawah adalah versi terakhir Excel, dianggap bahwa baris sebanyak 65,535 yang dimiliki worksheet Excel 2003 tidak mungkin penuh terisi. Nyatanya, saat ini jumlah baris sebanyak 65,535 menjadi terlalu sedikit karena data yang diolah selalu melebihi nilai tersebut. Bahkan jumlah baris sebanyak satu juta lebih yang disediakan worksheet Excel 2007 ke atas sebenarnya masih relatif sedikit.

Kolom terakhir data dengan xlToRight
   '>> Menggunakan xlToRight
   '- Khusus tabel yang pasti memiliki lebih dari 1 kolom
   ' dan rapat berisi data
   ' (tidak ada blank cell diantara nama kolom)
   '- Range asal pada baris header di kolom pertama data
   '- Header di baris 1 Excel
   '- Kolom pertama data adalah kolom A
   '- Range asal adalah A1
   Set rngAsal = sht.Range("A1")
   Set rngHasil = rngAsal.End(xlToRight)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
    "Arah : xlToRight" & vbCrLf & _
      "Hasil : " & rngHasil.Address & vbCrLf & _
      "Kolom terakhir data (kolom Excel ke-) : " & _
      rngHasil.Column _
      & vbCrLf & "yaitu pada kolom : " & _
    Replace(rngHasil.Address(False, False), _
      rngHasil.Row, vbNullString)
Pada penggunaan xlToRight, range asal harus berada disalah satu header yang bukan header terakhir, seperti pada kolom pertama data. Proses pada potongan script yang menggunakan xlToRight diatas adalah :
- Mendefinisikan range asal di range A1
- Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlToRight terhadap rngAsal
- Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
- Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks kolom Excel dari range hasil yang merupakan kolom terakhir data beserta nama kolom Excel-nya
Bahan latihan dan uji coba
Apa yang terjadi jika syarat :
- 'pasti memiliki lebih dari 1 kolom' tidak terpenuhi alias script diatas dijalankan pada data yang berisi 1 (satu) kolom saja ?
- 'rapat berisi data' tidak terpenuhi alias script diatas dijalankan pada data yang kolomnya tidak ada namanya (blank) ?

Kolom terakhir data dengan xlToLeft
   '>> Menggunakan xlToLeft
   '- Khusus untuk tabel yang tidak mungkin
   ' menggunakan seluruh kolom Excel
   '- Range asal dari kolom terakhir Excel dibaris header
   '- Baris header di baris 1 Excel
   '- Jumlah kolom Excel didapat dengan Columns.Count
   '- Range asal adalah Cells(1, Columns.Count)
   ' yang berarti cell pada baris 1 Excel
   ' dikolom terakhir Excel (hasil Columns.Count)
   Set rngAsal = sht.Cells(1, Columns.Count)
   Set rngHasil = rngAsal.End(xlToLeft)
   rngHasil.Copy
   MsgBox "Asal : " & rngAsal.Address & vbCrLf & _
    "Arah : xlToLeft" & vbCrLf & _
      "Hasil : " & rngHasil.Address & vbCrLf & _
      "Kolom terakhir data (kolom Excel ke-) : " & _
      rngHasil.Column _
      & vbCrLf & "yaitu pada kolom : " & _
      Replace(rngHasil.Address(False, False), _
      rngHasil.Row, vbNullString)
Pada penggunaan xlToLeft, range asal harus berada dibaris diluar area data. Umumnya range asal adalah kolom terakhir Excel pada baris data yang kolom terakhirnya pasti berisi data, seperti pada baris header data yang seluruh kolomnya pasti diberi nama. Proses pada potongan script yang menggunakan xlToLeft diatas adalah :
- Mendefinisikan range asal di kolom terakhir Excel pada baris 1 Excel karena header di baris 1 Excel. Kolom terakhir Excel bisa didapat dengan Columns.Count dan range di kolom terakhir Excel pada baris 1 Excel bisa didapat dengan menggunakan properti Cells yang membutuhkan informasi nomor indeks baris Excel dan nomor indeks kolom Excel. Proses ini adalah pada baris kode :
  Set rngAsal = sht.Cells(1, Columns.Count)
- Menyimpan range hasil ke rngHasil yang merupakan hasil proses penggunaan xlToLeft terhadap rngAsal
- Meng-Copy rngHasil ke Clipboard sebagai salah satu contoh proses yang bisa dilakukan terhadap range hasil
- Menampilkan pesan kepada user dengan kotak pesan (MsgBox) yang berisi informasi tentang alamat range asal, arah pergerakan End, alamat range hasil dan indeks kolom Excel dari range hasil yang merupakan kolom terakhir data beserta nama kolom Excel-nya
Bahan latihan dan uji coba
- Apa yang terjadi ketika potongan script diatas dijalankan pada seluruh kolom Excel yang digunakan sebagai kolom data ?
Hal ini sangat kecil kemungkinannya, dan andaikan terjadi pun, maka biasanya harus segera ditata ulang penyusunan dataset-nya. Meski demikian, tidak ada salahnya untuk menganggap hal tersebut terjadi.

Pengaruh susunan data
Bagaimana jika prosedur bernama TentangEnd_Contoh2 yang berisi potongan-potongan proses untuk mendapatkan batas terkanan dan terbawah data bisa diterapkan pada worksheet bernama 'Data15' ? Berikut ini adalah salah satu dari sekian banyak langkah yang bisa digunakan :

Kolom pertama data pada dataset 'cabang1' didapat dengan range asal di range A5 kemudian xlToRight
Kolom terakhir data pada dataset 'cabang1' didapat dengan range asal berupa hasil proses untuk mendapatkan kolom pertama data pada dataset 'cabang1' yang kemudian memanfaatkan xlToRight
Kolom terakhir data pada dataset 'cabang2' didapat dengan range asal adalah kolom terakhir Excel dibaris 5 Excel kemudian xlToLeft
Kolom pertama data pada dataset 'cabang2' didapat dengan range asal berupa hasil proses untuk mendapatkan kolom terakhir data pada dataset 'cabang2' yang kemudian memanfaatkan xlToLeft

Permasalahan utama dalam penggunaan properti End pada contoh data seperti di sheet 'Data15' ini adalah dalam penentuan baris terakhir data setiap dataset. Dengan pola susunan data yang setiap kolomnya disetiap baris terakhir data tidak ada yang pasti terisi data, maka akan relatif rumit karena harus menghitung jumlah baris terbanyak dari setiap kolom disetiap dataset. Sebaiknya proses untuk mendapatkan baris terakhir data pada dataset yang polanya demikian tidak menggunakan properti End. Pembahasan berikutnya tentang properti CurrentRegion akan lebih mudah digunakan daripada penggunaan properti End.

Simpulan

Pola susunan data sangat mempengaruhi proses pendinamisan rujukan dalam Excel VBA. Semakin berkaidah database suatu dataset, setidaknya dengan tidak adanya baris kosong seluruhnya maupun kolom kosong seluruhnya, maka akan semakin mudah proses perujukan secara dinamis dalam Excel VBA.

:)

Insya Allah pembahasan berikutnya adalah tentang CurrentRegion. Properti CurrentRegion milik range termasuk salah satu tulang punggung pendinamisan rujukan range dalam Excel VBA.

BelajarVBA 011 - Range 04 (Offset dan Resize)

2013-09-19T13:57:00.001+07:00

Coretan Mr. Kid

Tuhan tidak akan membebani mahluk dengan cobaan yang melebihi batas kemampuan si mahluk. Kenapa harus menghabiskan energi untuk berkeluh kesah ketika ada sesuatu yang terasa tidak nyaman menimpa diri kita ? Bukankah lebih bermanfaat jika kita mengevaluasi diri dan berpikir untuk memperbaikinya ?

Merujuk ke suatu range dengan VBA bisa beranjak dari suatu range yang sudah pasti diketahui. Bisa dikatan jika ada suatu range A dan suatu range B, maka masing-masing range, yaitu range A dan range B, memiliki dimensi jumlah baris dan kolom tertentu dan memiliki range awal (range pojok kiri atas) tertentu. Antara range B dengan range A memiliki jarak tertentu sebanyak y baris dan x kolom. Jadi, dari dimensi suatu range bisa diketahui batas-batas range tersebut di keempat sisinya. Dari sebuah range dapat diketahui jumlah baris dan kolomnya. Sedangkan dari dua buah area range dapat diketahui jarak antar keduanya berdasar range pojok kiri atas masing-masing, baik dari jarak dalam dimensi baris dan jarak dalam dimensi kolomnya.

Hal-hal tersebut diataslah yang menjadi salah satu modal untuk membuat suatu rujukan range yang dinamis. Cukup dengan mengetahui sebuah area range, maka bisa merujuk area range lain yang dibutuhkan selama tahu dimensi maupun jarak antara range yang dirujuk dengan range yang pasti diketahui. Artinya, minimal mengetahui sebuah range dalam data dan memahami pola susunan data, maka bisa dilakukan penjelajahan terhadap seluruh data dalam worksheet. Properti range yang dapat digunakan untuk keperluan diatas adalah Offset dan Resize.

Pembahasan kali ini adalah tentang range pada sisi properti bernama Offset dan Resize. Kedua properti ini termasuk tulang punggung dalam merujuk suatu range secara dinamis dalam Excel VBA. Untuk keperluan pembelajaran kali ini, data yang akan digunakan adalah data pada sheet bernama 'Data16' yang ada pada file BelajarVBA011_04.xlsm. Gambar berikut ini adalah bentuk susunan data yang ada di sheet bernama 'Data16'.

Memanfaatkan OFFSET

Properti Offset adalah sebuah properti milik object Range. Offset digunakan untuk melompat menuju suatu range tertentu dengan mengetahui jarak antar baris atau jarak antar kolom relatif terhadap range asalnya. Offset tidak mengubah dimensi suatu area range.

Syntaxnya :
object_range.OFFSET( [jarak_antar_baris] , [jarak_antar_kolom] )
dengan :

object_range adalah range asal
jarak_antar_baris :

adalah bilangan bulat jumlah baris dari baris range asal sampai baris range tujuan
yang bernilai positif akan memiliki arah menuju ke bawah atau ke baris setelah baris range asal
yang bernilai negatif akan memiliki arah menuju ke atas atau ke baris sebelum baris range asal
yang bernilai nol atau dikosongkan akan berarti tidak akan menuju kemanapun alias tetap pada baris range asal

jarak_antar_kolom :

adalah bilangan bulat jumlah kolom dari kolom range asal sampai kolom range tujuan
yang bernilai positif akan memiliki arah menuju ke kanan atau ke kolom setelah kolom range asal
yang bernilai negatif akan memiliki arah menuju ke kiri atau ke kolom sebelum kolom range asal
yang bernilai nol atau dikosongkan akan berarti tidak akan menuju kemanapun alias tetap pada kolom range asal

Contoh penerapannya akan diletakkan pada prosedur berikut ini :
   Public Sub TentangOffset()
Dim sAlamatAsal As String, rngAsal As Range
Dim rngOffset1 As Range, rngOffset2 As Range
Dim rngOffset3 As Range, rngOffset4 As Range

sAlamatAsal = "A1"
Set rngAsal = Range(sAlamatAsal)

'baris kode contoh akan diletakkan setelah baris ini

   End Sub

Berikut ini adalah beberapa contoh penggunaan setelah rngAsal didefinisikan sebagai range dengan alamat yang disimpan pada sAlamatAsal :
Contoh 1 : rngAsal ke 5 kolom dikanan rngAsal
   '----------------------------------
   'rngAsal ke 5 kolom dikanan rngAsal
   '----------------------------------
   'disimpan di rngOffset1
   Set rngOffset1 = rngAsal.Offset(0, 5)
   rngOffset1.Copy
   MsgBox "rngAsal : " & rngAsal.Address & vbCrLf & _
      "ke 5 kolom di-KANAN rngAsal : " & rngOffset1.Address & _
      vbCrLf & "yaitu : rngAsal.Offset(0, 5)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngAsal, dilakukan Offset (lompat) sebanyak 0 baris kebawah dan 5 kolom kekanan oleh bagian :

rngAsal.Offset(0, 5)

kemudian disimpan ke variabel rngOffset1 pada bagian :

Set rngOffset1 =

di baris berbunyi :

Set rngOffset1 = rngAsal.Offset(0, 5)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset1 ke Clipboard oleh baris kode :

rngOffset1.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset1

Contoh 2 : rngAsal ke 9 baris dibawah rngAsal
   '----------------------------------
   'rngAsal ke 9 baris dibawah rngAsal
   '----------------------------------
   'disimpan di rngOffset2
   Set rngOffset2 = rngAsal.Offset(9, 0)
   rngOffset2.Copy
   MsgBox "rngAsal : " & rngAsal.Address & vbCrLf & _
      "ke 9 baris di-BAWAH rngAsal : " & rngOffset2.Address & _
      vbCrLf & "yaitu : rngAsal.Offset(9, 0)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngAsal, dilakukan Offset (lompat) sebanyak 9 baris kebawah dan 0 kolom kekanan oleh bagian :

rngAsal.Offset(9, 0)

kemudian disimpan ke variabel rngOffset2 pada bagian :

Set rngOffset2 =

di baris berbunyi :

Set rngOffset2 = rngAsal.Offset(9, 0)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset2 ke Clipboard oleh baris kode :

rngOffset2.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset2

Contoh 3 : rngAsal ke 3 baris dibawah rngAsal dan 4 kolom dikanan rngAsal
   '--------------------------------------------------------------
   'rngAsal ke 3 baris dibawah rngAsal dan 4 kolom dikanan rngAsal
   '--------------------------------------------------------------
   'disimpan di rngOffset3
   Set rngOffset3 = rngAsal.Offset(3, 4)
   rngOffset3.Copy
   MsgBox "rngAsal : " & rngAsal.Address & vbCrLf & _
"ke 3 baris di-BAWAH rngAsal dan " & _
"4 kolom diKANAN rngAsal : " & _
rngOffset3.Address & vbCrLf & _
"yaitu : rngAsal.Offset(3, 4)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngAsal, dilakukan Offset (lompat) sebanyak 3 baris kebawah dan 4 kolom kekanan oleh bagian :

rngAsal.Offset(3, 4)

kemudian disimpan ke variabel rngOffset3 pada bagian :

Set rngOffset3 =

di baris berbunyi :

Set rngOffset3 = rngAsal.Offset(3, 4)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset3 ke Clipboard oleh baris kode :

rngOffset3.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset3

Contoh 4 : Range hasil offset (rngOffset3) ke 2 baris diatas dan 1 kolom dikirinya
   '----------------------------------------------------------
   'Range hasil offset ke 2 baris diatas dan 1 kolom dikirinya
   '----------------------------------------------------------
   'disimpan di rngOffset4
   Set rngOffset4 = rngOffset3.Offset(-2, -1)
   rngOffset4.Copy
   MsgBox "rngOffset3 : " & rngOffset3.Address & vbCrLf & _
"ke 2 baris di-ATAS dan " & _
"1 kolom di-KIRI-nya : " & _
rngOffset3.Address & vbCrLf & _
"yaitu : rngOffset3.Offset(-2, -1)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngOffset3, hasil contoh 3 yang berupa range di E4, dilakukan Offset (lompat) sebanyak 2 baris keatas (berarti negatif pada sisi baris senilai -2) dan 1 kolom kekiri (berarti negatif pada sisi kolom senilai -1) oleh bagian :

rngOffset3.Offset(-2, -1)

kemudian disimpan ke variabel rngOffset4 pada bagian :

Set rngOffset4 =

di baris berbunyi :

Set rngOffset4 = rngOffset3.Offset(-2, -1)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngOffset4 ke Clipboard oleh baris kode :

rngOffset4.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAsal dan rngOffset4

Bagaimana jika dicoba untuk alamat range asal yang disimpan pada variabel sAlamatAsal yang berupa teks berbunyi A1 di baris kode :
sAlamatAsal = "A1"
diganti menjadi B2:E6

Memanfaatkan RESIZE

Properti Resize adalah sebuah properti milik object Range. Resize digunakan untuk mendapatkan suatu range yang mengalami pengubahan luasan area range dari range asalnya, dengan mengetahui jumlah baris luasan baru atau jumlah kolom luasan baru dari range pojok kiri range asalnya. Resize tidak mengubah alamat range pojok kiri atas range asalnya.

Syntaxnya :
object_range.RESIZE( [jumlah_baris_baru] , [jumlah_kolom_baru] )
dengan :

object_range adalah range asal
jumlah_baris_baru :

adalah bilangan bulat jumlah baris dari luasan yang baru dimulai dari range pojok kiri atas range asalnya
selalu bernilai lebih dari nol (>0) alias minimal 1
yang dikosongkan akan berarti memiliki jumlah baris yang tetap alias sama dengan jumlah baris range asalnya

jumlah_kolom_baru :

adalah bilangan bulat jumlah kolom dari luasan yang baru dimulai dari range pojok kiri atas range asalnya
selalu bernilai lebih dari nol (>0) alias minimal 1
yang dikosongkan akan berarti memiliki jumlah kolom yang tetap alias sama dengan jumlah kolom range asalnya

Contoh penerapannya akan diletakkan pada prosedur berikut ini :
   Public Sub TentangResize()
Dim sAlamatAnchor As String, rngAnchor As Range
Dim rngResize1 As Range, rngResize2 As Range
Dim rngResize3 As Range

sAlamatAnchor = "A1"
Set rngAnchor = Range(sAlamatAnchor)

'baris kode contoh akan diletakkan setelah baris ini

   End Sub

Berikut ini adalah beberapa contoh penggunaan setelah rngAnchor (sebagai range asalnya) didefinisikan sebagai range dengan alamat yang disimpan pada sAlamatAnchor :
Contoh 1 : Perluas rngAnchor menjadi 1 baris x 15 kolom
   '--------------------------------------------
   'Perluas rngAnchor menjadi 1 baris x 15 kolom
   '--------------------------------------------
   'dari rngAnchor diperluas menjadi 1 x 15
   'disimpan dalam variabel rngResize1
   Set rngResize1 = rngAnchor.Resize(1, 15)
   rngResize1.Copy
   MsgBox "rngAnchor : " & rngAnchor.Address & _
" ( " & rngAnchor.Rows.Count & " x " & _
rngAnchor.Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngResize1 : " & rngResize1.Address & _
      " ( " & rngResize1.Rows.Count & " x " & _
      rngResize1.Columns.Count & " )" & vbCrLf & _
      "yaitu : rngAnchor.Resize(1, 15)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngAnchor, dilakukan Resize (pengubahan luasan) menjadi berdimensi 1 baris x 15 kolom oleh bagian :

rngAnchor.Resize(1, 15)

kemudian disimpan ke variabel rngResize1 pada bagian :

Set rngResize1 =

di baris berbunyi :

Set rngResize1 = rngAnchor.Resize(1, 15)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize1 ke Clipboard oleh baris kode :

rngResize1.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAnchor sebagai range asalnya dan rngResize1 sebagai range penyimpan hasil pengubahan luasan, disertai dimensi masing-masing

Contoh 2 : Perluas rngResize1 (hasil contoh 1) menjadi 17 baris x 3 kolom
   '---------------------------------------------
   'Perluas rngResize1 menjadi 17 baris x 3 kolom
   '---------------------------------------------
   'dari rngResize1 diperluas menjadi 17 x 3
   'disimpan dalam variabel rngResize2
   Set rngResize2 = rngResize1.Resize(17, 3)
   rngResize2.Copy
   MsgBox "rngResize1 : " & rngResize1.Address & _
      " ( " & rngResize1.Rows.Count & " x " & _
      rngResize1.Columns.Count & " )" & vbCrLf & _
      "menjadi" & vbCrLf & _
      "rngResize2 : " & rngResize2.Address & _
      " ( " & rngResize2.Rows.Count & " x " & _
rngResize2.Columns.Count & " )" & vbCrLf & _
"yaitu : rngResize1.Resize(17, 3)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngResize1, hasil contoh 1, dilakukan Resize (pengubahan luasan) menjadi berdimensi 17 baris x 3 kolom oleh bagian :

rngResize1.Resize(17, 3)

kemudian disimpan ke variabel rngResize2 pada bagian :

Set rngResize2 =

di baris berbunyi :

Set rngResize2 = rngResize1.Resize(17, 3)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize2 ke Clipboard oleh baris kode :

rngResize2.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngResize1 sebagai range asalnya dan rngResize2 sebagai penyimpan hasil pengubahan luasan, disertai dimensi masing-masing

Contoh 3 : Perluas rngResize2 (hasil contoh 2) menjadi baris yang berjumlah tetap x 9 kolom
   '---------------------------------------------------------------
   'Perluas rngResize2 menjadi baris yang berjumlah tetap x 9 kolom
   '---------------------------------------------------------------
   'dari rngResize2 diperluas menjadi berjumlah baris tetap x 9 kolom
   'disimpan dalam variabel rngResize3
   Set rngResize3 = rngResize2.Resize(, 9)
   rngResize3.Copy
   MsgBox "rngResize2 : " & rngResize2.Address & _
      " ( " & rngResize2.Rows.Count & " x " & _
      rngResize2.Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngResize3 : " & rngResize3.Address & _
" ( " & rngResize3.Rows.Count & " x " & _
rngResize3.Columns.Count & " )" & vbCrLf & _
"yaitu : rngResize2.Resize(, 9)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngResize2, hasil contoh 2, dilakukan Resize (pengubahan luasan) menjadi berdimensi tetap seperti jumlah baris range asalnya (rngResize2) x 9 kolom. Karena jumlah baris yang baru adalah tetap seperti jumlah baris range asalnya, maka bagian barisnya bisa dikosongkan. Proses ini dilakukan oleh bagian :

rngResize2.Resize(, 9)

kemudian disimpan ke variabel rngResize2 pada bagian :

Set rngResize3 =

di baris berbunyi :

Set rngResize3 = rngResize2.Resize(, 9)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngResize3 ke Clipboard oleh baris kode :

rngResize3.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngResize2 sebagai range asalnya dan rngResize3 sebagai penyimpan hasil pengubahan luasan, disertai dimensi masing-masing

Contoh 4 : Mengubah luasan diri sendiri si rngAnchor menjadi 3 baris x 4 kolom
   '---------------------------------------------------
   'Mengubah luasan rngAnchor menjadi 3 baris x 4 kolom
   '---------------------------------------------------
   'dari rngAnchor diperluas menjadi 3 x 4
   'disimpan pada dirinya sendiri (rngAnchor juga)
   Set rngAnchor = rngAnchor.Resize(3, 4)
   rngAnchor.Copy
   MsgBox "rngAnchor (awal) : " & Range(sAlamatAnchor).Address & _
" ( " & Range(sAlamatAnchor).Rows.Count & " x " & _
Range(sAlamatAnchor).Columns.Count & " )" & vbCrLf & _
"menjadi" & vbCrLf & _
"rngAnchor (baru) : " & rngAnchor.Address & _
" ( " & rngAnchor.Rows.Count & " x " & _
rngAnchor.Columns.Count & " )" & vbCrLf & _
"yaitu : rngAnchor.Resize(3, 4)"
Baris-baris kode diatas akan diproses sebagai berikut :

Proses pertama adalah dari rngAnchor, dilakukan Resize (pengubahan luasan) menjadi berdimensi 3 baris x 4 kolom oleh bagian :

rngAnchor.Resize(3, 4)

kemudian disimpan ke dirinya sendiri, yaitu variabel rngAnchor pada bagian :

Set rngAnchor =

di baris berbunyi :

Set rngAnchor = rngAnchor.Resize(3, 4)

Proses kedua adalah meng-Copy range yang tersimpan dalam variabel rngAnchor yang sudah memiliki luasan baru ke Clipboard oleh baris kode :

rngAnchor.Copy

Proses ketiga adalah menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi tentang alamat rngAnchor yang masih berisi range dengan luasan yang lama (sebagai range asalnya) dan rngAnchor yang sudah berisi range dengan luasan yang baru (sebagai penyimpan hasil pengubahan luasan), disertai dimensi masing-masing

Bagaimana jika dicoba untuk alamat range asal yang disimpan pada variabel sAlamatAnchor yang berupa teks berbunyi A1 di baris kode :
sAlamatAnchor = "A1"
diganti menjadi B2:E6

Memanfaatkan OFFSET dan RESIZE secara berurutan

Properti OFFSET dan RESIZE dapat digunakan secara berurutan. Proses OFFSET bisa diletakkan sebelum proses RESIZE ataupun sebaliknya. Syntax :
   object_range _
     .OFFSET( [jarak_baris] , [jarak_kolom] ) _
     .RESIZE( [jumlah_baris] , [jumlah_kolom] )
atau
   object_range _
     .RESIZE( [jumlah_baris] , [jumlah_kolom] ) _
     .OFFSET( [jarak_baris] , [jarak_kolom] )

Pada syntax berbunyi :
   object_range _
     .OFFSET( [jarak_baris] , [jarak_kolom] ) _
     .RESIZE( [jumlah_baris] , [jumlah_kolom] )
sebenarnya berisi 2 buah proses, yaitu object_range di-OFFSET dan kemudian hasilnya di-RESIZE. Jika proses pertama disimpan dalam variabel range bernama rngHasil1 dan hasil akhir disimpan dalam variabel range bernama rngHasilAkhir, maka prosesnya akan setara dengan :
   Set rngHasil1 = object_range.OFFSET( [jarak_baris] , [jarak_kolom] )
   Set rngHasilAkhir = rngHasil1.RESIZE( [jumlah_baris] , [jumlah_kolom] )
begitu juga pada syntax berbunyi :
   object_range _
     .RESIZE( [jumlah_baris] , [jumlah_kolom] ) _
     .OFFSET( [jarak_baris] , [jarak_kolom] )
sebenarnya berisi 2 buah proses, yaitu object_range di-RESIZE dan kemudian hasilnya di-OFFSET. Jika proses pertama disimpan dalam variabel range bernama rngHasil1 dan hasil akhir disimpan dalam variabel range bernama rngHasilAkhir, maka prosesnya akan setara dengan :
   set rngHasil1 = object_range.RESIZE( [jumlah_baris] , [jumlah_kolom] )
   set rngHasilAkhir = rngHasil1.OFFSET( [jarak_baris] , [jarak_kolom] )

Contohnya adalah untuk mendapatkan area range (lihat gambar data diatas sana) record untuk kolom negara dan port, yang beralamat di D2:E12, dari range A1. Prosedur untuk proses ini bisa berbunyi :
   Public Sub TentangOffsetDanResizeBerurutan()
Dim rng As Range

'dari A1,
'proses 1 -> lompat (OFFSET) ke D2 dari A1 yang berarti
'lompat 1 baris kebawah dan 3 kolom kekanan dari A1
'proses 2 -> perluas (RESIZE) area di D2
'menjadi 11 baris x 2 kolom
Set rng = Range("A1").Offset(1, 3).Resize(11, 2)
rng.Copy
MsgBox "Range hasil : " & rng.Address & " ( " & _
       rng.Rows.Count & " x " & rng.Columns.Count & ") " & _
     vbCrLf & "yaitu : Range(""A1"").Offset(3, 1).Resize(11, 2)"
   End Sub
Proses yang terjadi pada prosedur diatas adalah :

Dari range asal di range A1,yaitu pada bagian Range("A1")
dilakukan OFFSET untuk mencapai D2, yaitu sebanyak jarak 1 baris keBAWAH dan jarak 3 kolom keKANAN, pada bagian .Offset(1, 3)
yang hasil dari OFFSET tersebut kemudian di-RESIZE dari berdimensi 1 baris x 1 kolom menjadi berdimensi baru 11 baris x 2 kolom, pada bagian .Resize(11, 2)
dilanjutkan dengan menyimpan hasil RESIZE terhadap hasil OFFSET dari range asal tersebut ke variabel range bernama rng, pada bagian Set rng =
dengan bentuk akhir baris kode semua proses tersebut berbunyi :

Set rng = Range("A1").Offset(1, 3).Resize(11, 2)

Proses dilanjutkan dengan meng-Copy range dalam variabel rng ke Clipboard oleh baris kode :

rng.Copy

Proses diakhiri dengan menampilkan pesan kepada user menggunakan kotak pesan (MsgBox) yang berisi informasi alamat hasil akhir proses disertai dimensinya

Simpulan

Beberapa hal yang penting dari contoh-contoh diatas adalah :

Properti Offset :

adalah untuk mendapatkan suatu range dengan luasan yang sama dengan range asalnya dijarak tertentu dari sisi baris dan kolomnya.
tidak pernah mengubah luasan. Artinya, luasan hasil Offset selalu sama dengan luasan range asalnya.
bisa diberi nilai negatif, nol, atau positif. Jika dikosongkan, maka akan dianggap bernilai 0.
sisi baris negatif berarti keATAS, positif berarti keBAWAH, dan nol adalah TETAP pada baris range asal
sisi kolom negatif berarti keKIRI, positif berarti keKANAN, dan nol adalah TETAP pada kolom range asal
jarak baris maksimum adalah sebanyak baris tersisa setelah baris range asal dikurangi jumlah baris range asal ditambah 1, ketika diberi nilai positif
jarak baris minimum adalah sebanyak baris yang ada sebelum baris range asal ketika diberi nilai negatif
jarak kolom maksimum adalah sebanyak kolom tersisa setelah kolom range asal dikurangi jumlah kolom range asal ditambah 1, ketika diberi nilai positif
jarak kolom minimum adalah sebanyak kolom yang ada sebelum kolom range asal ketika diberi nilai negatif

Properti Resize :

adalah untuk mendapatkan suatu range dengan luasan yang baru dengan mengubah nilai-nilai dimensi baris dan kolom dari range asalnya.
tidak pernah berpindah alamat pojok kiri atasnya.
hanya bisa menerima nilai positif atau selalu lebih dari nol (>0) alias minimal 1 (satu).
jika nilai jumlah baris baru atau nilai jumlah kolom baru dikosongkan, maka akan dianggap sama dengan dimensi sisi tersebut milik range asalnya.
arah perluasan selalu keKANAN atau keBAWAH karena hanya bisa menerima nilai positif saja.
jumlah baris baru maksimum adalah sebanyak baris tersisa setelahnya dari baris range asal
jumlah kolom baru maksimum adalah sebanyak kolom tersisa setelahnya mulai dari kolom range asal

Properti Offset dan Resize :

bekerja pada range utama adalah range pojok kiri atas dari range asalnya atau range yang diproses.
bisa digunakan berurutan yang harus memperhatikan batas-batas maksimal atau minimal setiap nilai yang di-input-kan ke bagian [jarak_baris], [jarak_kolom], [jumlah_baris], [jumlah_kolom] relatif terhadap range asal atau hasil OFFSET maupun RESIZE sebelumnya.
penggunaan Offset dan Resize pada range asal yang berupa 1 (satu) buah range secara berurutan mirip dengan built-in function Offset dalam worksheet.

:)

Insya Allah pembahasan selanjutnya adalah tentang properti END

Kasus 001 BelajarVBA - UpTo 011 Range 03

2013-09-18T07:20:00.000+07:00

Keisengan Mr. Kid

Materi ini adalah contoh kasus sederhana untuk BelajarVBA sampai dengan BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union). File contoh yang bisa di-unduh adalah Imp001.xlsm.
Kasus :

Sheet 'Sumber' di range A2:B5. Data item (kolom A) dan jumlah record yang harus ditulis (kolom B).
Sheet 'Output', A1:C1 adalah header tabel output. Terdiri dari Item, Waktu, dan Nomor
Record pertama output mulai dari A2:C2.
Jika nama item (range A2:A5) di-doubleclick, maka nama item akan ditulis pada sheet Output sebagai record baru (ditulis mulai baris kosong pertama di Output) sebanyak nilai kolom B di sheet Sumber.
Tanggal dan jam saat di-doubleclick juga ditulis di sheet Output, yaitu ke kolom B.
Nomor urut dari 1 sampai nilai yang di sheet Sumber pada kolom B di item yang di-doubleclick
Diakhiri dengan menampilkan pesan tentang nama item, waktu, dan jumlah record yang ditulis.

Langkah menyusun otomasi :
1. Menyiapkan data dan menyimpan file dengan tipe Macro Enabled Workbook (.xlsm)

Buat workbook baru
Siapkan data di sheet bernama Sumber.
Siapkan header data di sheet bernama Output.
Susun data seperti gambar.
Simpan file sebagai .xlsm atau .xls (lihat : BelajarVBA 001 - VBE (Visual Basic Editor) bagian akhir)
Ke area penulisan kode alias ke VBE dengan ALT F11

2. Mendefinisikan pemicu proses

Proses bekerja jika cell tertentu di sheet Sumber di-doubleclick. Artinya, Worksheet bernama Sumber harus bisa merespon kegiatan user. Hal ini berarti harus menggunakan event worksheet. Berarti ke VBE dan aktifkan code window milik sheet bernama Sumber. (lihat : BelajarVBA 007 - Property, Methods, Events bagian Events dalam object Worksheet)
Kegiatan yang harus dilakukan user adalah melakukan doubleclick pada sebuah cell. Artinya, sesaat sebelum proses doubleclick bekerja (yang biasanya akan membuat cell bisa diedit isinya) harus segera menjalankan proses penyelesaian kasus yang dihadapi ini. Jadi dibutuhkan event worksheet bernama BeforeDoubleClick
Cara membuatnya prosedur sub event worksheet bisa dilihat di BelajarVBA 007 - Property, Methods, Events bagian Events dalam object Worksheet
Bentuk blok prosedur sub event worksheet untuk BeforeDoubleClick adalah :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Baris kosong sebelum END SUB adalah lokasi penulisan baris kode proses.
Variabel Target adalah range yang di-doubleclick oleh user.
Variabel Cancel adalah penerima data pembatalan proses doubleclick oleh user alias dipaksa tidak jadi di-doubleclick

3. Memastikan bahwa yang di-doubleclick oleh user adalah bagian dari range yang ditentukan, yaitu range A2:A5

Area range yang memicu proses hanyalah A2:A5 di sheet Sumber.
User bisa saja melakukan doubleclick dirange selain range yang ditentukan tersebut.
Maka harus tahu lebih dulu, apakah range yang di-doubleclick berpotongan dengan range A2:A5 yang ditentukan. Artinya, dibutuhkan proses perpotongan antara range yang di-doubleclick oleh user (variabel Target) dengan area range tertentu (range A2:A5).
Proses perpotongan akan menggunaan Intersect.
Hasil perpotongan adalah suatu area range dan harus disimpan disuatu variabel object range. Hal ini mengindikasikan bahwa dibutuhkan deklarasi variabel object range penyimpan hasil perpotongan.
Kemudian variabel tersebut digunakan untuk menyimpan hasil proses perpotongan.
Jadi, urutannya adalah menyediakan variabel object range penyimpan hasil perpotongan lalu digunakan untuk menyimpan hasil proses perpotongan. (lihat : BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union) bagian Perpotongan Range)
Pada baris kosong blok prosedur bisa diisi :

Dim rngHasil As Range
Set rngHasil = Intersect( Target , Range("A2:A5") )

Hasil perpotongan adalah berupa object (benda) Range. Untuk memeriksa benda adalah dengan pertanyaan 'Apakah ada rangenya ?'. Kata 'rangenya tidak ada' dalam VBA berbunyi Range is Nothing (karena datatype-nya object). Sedangkan 'ada rangenya' bisa dikatakan dengan 'TIDAK dari rangenya tidak ada' yang dalam VBA berbunyi Not Range is Nothing. Karena range hasil perpotongan disimpan dalam variabel bernama rngHasil, maka untuk mengatakan rngHasil ada bendanya dalam VBA bisa dikatakan dengan NOT rngHasil is Nothing.
Jadi, kondisi untuk memeriksa hasil perpotongan adalah berbunyi NOT rngHasil is Nothing. Pemeriksaan dengan IF. (lihat : BelajarVBA 008 - Blok IF). Bunyi blok pemeriksaan dengan IF adalah :

     IF NOT rngHasil is Nothing THEN
       'baris kode ketika rngHasil ada bendanya
     ELSE
       'baris kode ketika rngHasil tidak ada bendanya
     END IF

Sampai terbentuknya blok IF ini, bentuk blok prosedur sudah menjadi :

     Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
       Dim rngHasil As Range
       'tempat deklarasi variabel lainnya jika diperlukan

       'mulai baris kode proses
       Set rngHasil = Intersect( Target , Range("A2:A5") )
       IF NOT rngHasil is Nothing THEN
         'baris kode ketika rngHasil ada bendanya
       ELSE
         'baris kode ketika rngHasil tidak ada bendanya
   END IF
     End Sub

4. Mendefinisikan isi proses disetiap keadaan dalam blok kondisi IF
Jika IF menghasilkan TRUE, artinya rngHasil ada bendanya, maka proses pengerjaan sampai penulisan ke sheet output akan dilakukan. Jika IF menghasilkan FALSE, artinya rngHasil tidak ada bendanya, maka tidak melakukan apapun. Jadi, fokus penyusunan baris kode adalah diarea blok IF yang bernilai TRUE, yaitu mengisi baris kode dibagian yang bertuliskan :
   'baris kode ketika rngHasil ada bendanya

5. Mendefinisikan nilai-nilai yang bisa didapatkan dan akan digunakan
Prosesnya adalah menulis nama item yang di-doubleclick, berarti dibutuhkan teks nama item tersebut. Nama item akan disimpan pada sebuah variabel ber-datatype String karena berupa data teks. Tentukan saja nama variabel nama item adalah sItem dengan type String. Selain itu, harus diketahui nilai Record untuk item yang di-doubleclick. Nilai record tersimpan di kolom B sheet Sumber pada baris item yang di-doubleclick. Jadi, harus tahu lebih dulu indeks baris Excel si item yang di-doubleclick.

Nilai indeks baris Excel adalah suatu bilangan yang ber-type numerik bilangan bulat. Maka variabel penyimpan indeks baris Excel bisa dipilih ber-type Long agar bisa menampung baris Excel yang sampai 1 juta. Nama variabel indeks baris Excel diberi nama saja dengan lBarisItem. Untuk bisa merujuk ke kolom B pada indeks baris Excel yang disimpan oleh lBarisItem, dibutuhkan teks alamat range si nilai record. Berarti dibutuhkan variabel ber-type String. Namakan saja variabel tersebut dengan sAlamatRecordItem.

Setelah tahu alamat range yang menyimpan nilai Record, maka nilai Record bisa diambil dan disimpan dalam sebuah variabel. Nilai record selalu bilangan bulat dan diasumsikan tidak lebih dari 32,000 maka dipilih datatype Integer. Nilai Record akan menjadi nilai maksimal nomor urut yang akan ditulis dari nomor 1 untuk setiap doubleclick oleh user, maka nama variabelnya adalah iRecordMax. Karena nomor akan ditulis dari nomor 1 sampai nomor maksimumnya (yang disimpan dalam iRecordMax), maka dibutuhkan variabel nomor yang ber-datatype sama dengan iRecordMax. Nama variabel nomor dipilih iRecordAwal yang ber-datatype Integer. Salah satu data yang akan ditulis nantinya adalah waktu user melakukan doubleclick yang berisi data tanggal dan jam. Berarti data ini akan ber-datatype datetime. Variabel penyimpan nilai tersebut dipilih datatype VB/VBA bernama Date. Nama variabelnya adalah dtWaktu (lihat BelajarVBA 004 - Tipe data, Variabel, Konstanta).

Sampai disini, sudah diketahui bahwa dibutuhkan beberapa variabel. Variabel-variabel itu harus dideklarasikan lebih dulu. Deklarasi prosedur umumnya ditulis diawal blok prosedur untuk scope level procedure (lihat : BelajarVBA 003 - Scope seri 1). Bunyi baris deklarasi variabel tambahannya adalah :
   Dim sItem As String, lBarisItem As Long
   Dim sAlamatRecordItem As String
   Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date

Kemudian, variabel-variabel baru tersebut diisi nilainya. Variabel sItem diisi dengan teks item yang disimpan oleh rngHasil sebagai perpotongan range yang di-doubleclick user dengan area A2:A5. Bunyi baris kode untuk ambil nilai dari suatu variabel range (dalam hal ini dari rngHasil) adalah :
   sItem = rngHasil.Value
Variabel lBarisItem diisi dengan indeks baris Excel dari rngHasil, yang diperoleh dengan baris kode :
   lBarisItem = rngHasil.Row
Variabel sAlamatRecordItem diisi dengan teks alamat range untuk kolom B di indeks baris yang disimpan oleh lBarisItem. Maka baris kode penyusun alamat range Record adalah :
   sAlamatRecordItem = "B" & lBarisItem
Variabel iRecordMax diisi dengan nilai kolom B yang alamatnya disimpan di sAlamatRecordItem dengan baris kode :
   iRecordMax = Range( sAlamatRecordItem ).Value
Variabel iRecordAwal diisi dengan angka 1, karena harus selalu dimulai dari angka 1 setiap proses doubleclick oleh user, dengan baris kode :
   iRecordAwal = 1
Variabel dtDate akan diisi datetime saat user melakukan doubleclick, yang berarti adalah waktu saat ini (Now). Jadi beris kodenya adalah :
   dtWaktu = Now
Semua proses pengambilan nilai ini masuk ke blok IF bagian TRUE. Jadi diletakkan setelah baris keterangan berbunyi :
   'baris kode ketika rngHasil ada bendanya
sehingga event worksheet BeforeDoubleClick akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF
End Sub

6. Mendefinisikan lokasi penulisan
Penulisan dilakukan di sheet bernama 'Output'. Berarti akan bekerja pada worksheet 'Output'. Supaya lebih fleksibel, dibuat variabel penyimpan worksheet penulisan. Variabel ber-type object dari worksheet dideklarasikan dengan datatype Worksheet. Nama variabelnya adalah shtTulis.

Penulisan dilakukan di baris kosong selanjutnya. Berarti harus tahu area range yang sudah terpakai di sheet Output. Variabel object Range penyimpan area yang sudah terpakai dideklarasikan ber-type Range. Misalnya diberi nama rngTerpakai. Untuk tahu baris kosong pertama lokasi penulisan, harus diketahui jumlah baris yang sudah terpakai. Jumlah baris yang terpakai berupa bilangan bulat yang bisa sebanyak jumlah baris Excel. Datatype Long akan mampu menampungnya. Nama variabelnya adalah lBarisTerpakai.

Sampai disini, sudah ada beberapa variabel baru yang akan dideklarasikan lebih dulu. Baris-baris deklarasi tersebut adalah :
   Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
Kemudian, variabel-variabel baru tersebut diisi.
Variabel shtTulis diisi dengan worksheet Output dengan baris kode definisi variabel object :
   Set shtTulis = Sheets("Output")
Variabel rngTerpakai diisi dengan range yang sudah terpakai di sheet 'Output'. Properti UsedRange milik worksheet tersebut. Lihat BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union). Baris kode pendefinisian rngTerpakai agar terisi dengan hasil properti UsedRange adalah :
   Set rngTerpakai = shtTulis.UsedRange
Variabel lBarisTerpakai diisi jumlah baris pada variabel range yang sudah terpakai yang bernama rngTerpakai dengan baris kode :
   lBarisTerpakai = rngTerpakai.Rows.Count
Baris-baris kode untuk mengisi nilai variabel-variabel baru tersebut adalah lanjutan proses kerja, jadi diletakkan setelah baris kode pengisian nilai yang nomor 5. Sampai disini, bentuk blok prosedur akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now

        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF
End Sub

7. Membentuk blok kerja penulisan
Penulisan akan dilakukan sebanyak nilai iRecordMax, dimulai dari iRecordAwal bernilai 1. Artinya akan ada proses penulisan berulang kali alias proses loop. Meski iRecordAwal saat ini selalu dimulai dari 1, bisa jadi nanti suatu saat akan berubah dan tidak akan lagi dimulai dari 1. Oleh sebab itu dibutuhkan variabel penyimpan baris baru lokasi penulisan, yang selalu bertambah 1 dari baris penulisan sebelumnya dalam proses loop. Artinya, sesaat sebelum memasuki proses loop, sudah harus diketahui lebih dulu lokasi baris baru penulisan untuk kegiatan tulis data yang pertama. Untuk keperluan ini, dibutuhkan penyimpan nomor baris baru penulisan yang bisa menampung nilai jumlah baris Excel seluruhnya. Datatype Long bisa menampungnya. Nama variabelnya adalah lBarisBaru. Baris deklarasinya diletakkan pada bagian deklarasi variabel dengan baris kode berbunyi :
   Dim lBarisBaru As Long
Nilai lBarisTerpakai adalah yang sudah terpakai. Maka baris baru penulisan pertama adalah 1 baris setelah lBarisTerpakai. Jadi lBarisBaru sebelum proses loop bisa ditentukan dengan baris kode :
   lBarisBaru = lBarisTerpakai + 1

Karena yang diketahui adalah nilai awal dan akhir proses loop, maka dipilih loop berupa blok FOR ... NEXT. Blok For ... Next membutuhkan variabel loop. Karena loop-nya pada nilai iRecordAwal sampai iRecordMax, maka dideklarasikan variabel loop bernama iNomor dengan datatype Integer. Baris kode deklarasinya diletakkan pada bagian deklarasi variabel. Baris kodenya berbunyi :
   Dim iNomor As Integer
Kemudian blok FOR ... NEXT dibuat dalam blok IF sebagai kelanjutan proses kerja. Bentuk blok FOR ... EACH nya adalah :
   FOR iNomor = iRecordAwal To iRecordMax

   NEXT iNomor
Blok prosedur akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
    Dim lBarisBaru As Long, iNomor As Integer

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now

        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count

        'blok kerja penulisan
        lBarisBaru = lBarisTerpakai + 1
        FOR iNomor = iRecordAwal To iRecordMax
            'detil kerja penulisan disini
        NEXT iNomor
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF
End Sub

8. Menulis data dibaris baru penulisan dan menentukan baris baru untuk data berikutnya
Ketika proses memasuki proses loop dan lokasi baris baru penulisan sudah diketahui, maka proses selanjutnya adalah menulis seluruh nilai yang diketahui ke baris baru penulisan pada kolom masing-masing nilai. Lihat BelajarVBA 011 - Range 01 (berdasar alamat range). Nilai item dalam sItem akan ditulis di kolom A pada lBarisBaru. Baris kode penulisannya berbunyi :
   shtTulis.Range("A" & lBarisBaru).Value = sItem
Nilai waktu dalam dtWaktu akan ditulis di kolom B pada lBarisBaru. Baris kode penulisannya berbunyi :
   shtTulis.Range("B" & lBarisBaru).Value = dtWaktu
Nilai nomor urut dalam iNomor (yang dipakai oleh loop) akan ditulis di kolom C pada lBarisBaru. Baris kode penulisannya berbunyi :
   shtTulis.Range("C" & lBarisBaru).Value = iNomor

Setelah seluruh data ditulis, maka nilai baris baru untuk proses penulisan pada loop berikutnya harus ditentukan.
Artinya, lBarisBaru harus bertambah 1. Jadi, saat proses loop mengolah iNomor berikutnya, baris penulisannya sudah dibaris yang baru lagi. Penambahan 1 untuk lBarisBaru adalah dengan baris kode :
   lBarisBaru = lBarisBaru + 1
Sampai disini, blok prosedur akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
    Dim lBarisBaru As Long, iNomor As Integer

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now

        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count

        'blok kerja penulisan
        lBarisBaru = lBarisTerpakai + 1
        FOR iNomor = iRecordAwal To iRecordMax
            'detil kerja penulisan disini
            shtTulis.Range("A" & lBarisBaru).Value = sItem
            shtTulis.Range("B" & lBarisBaru).Value = dtWaktu
            shtTulis.Range("C" & lBarisBaru).Value = iNomor

            'membuat baris baru penulisan untuk loop berikutnya
            lBarisBaru = lBarisBaru + 1
        NEXT iNomor
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF
End Sub

9. Membuat proses akhir
Seluruh proses pokok penulisan data telah selesai. Karena proses dijalankan saat cell di-doubleclick, maka harus dipastikan bahwa proses doubleclick tidak membuat cell masuk ruang edit data cell. Hal ini dilakukan dengan membuat variabel Cancel milik event worksheet BeforeDoubleClick harus bernilai TRUE. Baris kodenya adalah :
   Cancel = TRUE
Proses pembatalan terhadap dampak doubleclick yang akan masuk ruang edit data cell dilakukan tepat setelah proses loop. Tahapan berikutnya adalah proses akhir yang biasanya diisi dengan penampilan pesan selesai dan sebagainya. Pesan kepada user bisa disampaikan setelah blok loop dengan FOR ... NEXT. Misalnya dengan menggunakan kotak pesan (MsgBox) seperti baris kode berikut :
MsgBox "Selesai." & vbcrlf & "Item : " & sItem & vbcrlf & _
    "Pada : " & dtWaktu & vbcrlf & "Jumlah record : " & iRecordMax _
    ,vbInformation,"Contoh Kasus Belajar VBA 001"
Blok prosedur lengkapnya akan menjadi :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim rngHasil As Range, sItem As String, lBarisItem As Long
    Dim sAlamatRecordItem As String        'teks alamat si item
    Dim iRecordMax As Integer, iRecordAwal As Integer, dtWaktu As Date
    Dim shtTulis As Worksheet, rngTerpakai As Range, lBarisTerpakai As Long
    Dim lBarisBaru As Long, iNomor As Integer

    'mulai baris kode proses
    Set rngHasil = Intersect( Target , Range("A2:A5") )
    IF NOT rngHasil is Nothing THEN
        'ambil nilai dari sheet Sumber
        sItem = rngHasil.Value
        lBarisItem = rngHasil.Row
        sAlamatRecordItem = "B" & lBarisItem
        iRecordMax = Range( sAlamatRecordItem ).Value
        iRecordAwal = 1
        dtWaktu = Now

        'ambil informasi lokasi penulisan
        Set shtTulis = Sheets("Output")
        Set rngTerpakai = shtTulis.UsedRange
        lBarisTerpakai = rngTerpakai.Rows.Count

        'blok kerja penulisan
        lBarisBaru = lBarisTerpakai + 1
        FOR iNomor = iRecordAwal To iRecordMax
            'detil kerja penulisan disini
            shtTulis.Range("A" & lBarisBaru).Value = sItem
            shtTulis.Range("B" & lBarisBaru).Value = dtWaktu
            shtTulis.Range("C" & lBarisBaru).Value = iNomor

            'membuat baris baru penulisan untuk loop berikutnya
            lBarisBaru = lBarisBaru + 1
        NEXT iNomor

        'batalkan dampak doubleclick pada cell
        Cancel = TRUE

        'pesan untuk user
        MsgBox "Selesai." & vbcrlf & "Item : " & sItem & vbcrlf & _
            "Pada : " & dtWaktu & vbcrlf & "Jumlah record : " & iRecordMax _
            ,vbInformation,"Contoh Kasus Belajar VBA 001"
    ELSE
        'baris kode ketika rngHasil tidak ada bendanya
    END IF
End Sub

:)

Selesai sudah proses detil dalam menyelesaikan kasus sederhana ini. Setelah dipahami alurnya dan semakin terbiasa melakukan coding, biasanya prosedur akhir tersebut, dengan batasan materi sampai 011_03 dan record awal selalu mulai dari 1, bisa menjadi lebih ringkas seperti ini :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim sItem As String, iRecordMax As Integer, dtWaktu As Date
    Dim lBarisTerpakai, iNomor As Integer

    If Not Intersect(Target, Range("A2:A5")) Is Nothing Then
        'ambil nilai dari sheet Sumber
        sItem = Target.Value
        iRecordMax = Range("B" & Target.Row).Value
        dtWaktu = Now

        'blok kerja penulisan
        lBarisTerpakai = Sheets("Output").UsedRange.Rows.Count
        For iNomor = 1 To iRecordMax
            'detil kerja penulisan disini
            Sheets("Output").Range("A" & lBarisTerpakai + iNomor).Value = sItem
            Sheets("Output").Range("B" & lBarisTerpakai + iNomor).Value = dtWaktu
            Sheets("Output").Range("C" & lBarisTerpakai + iNomor).Value = iNomor
        Next iNomor

        Cancel = True   'batalkan dampak doubleclick pada cell

        'pesan untuk user
        MsgBox "Selesai." & vbCrLf & "Item : " & sItem & vbCrLf & _
            "Pada : " & dtWaktu & vbCrLf & "Jumlah record : " & iRecordMax _
            , vbInformation, "Contoh Kasus Belajar VBA 001"
    End If
End Sub

Ngobrolin EVALUATE di-VBA yuk...

2013-09-18T03:19:00.001+07:00

Dongengan Mr. Kid

Kid mau dongeng di lepas tengah malem...

Pada jaman kuda sudah pakai whatsap, terdengar selentingan ocehan tentang Evaluate...

Ocehannya begini...

Evaluate : sebuah methods (sebut saja prosedur fungsi) yang menjadi anggota object Application, Worksheet, dan Chart.
Evaluate bisa digunakan dengan 2 cara, yaitu :

Evaluate( inputnya_berdatatype_string )
[ inputnya_bunyi_yang_bisa_dipasang_jadi_formula_atau_nama_object_dalam_chart ]

Guna evaluate : mengkomputasi yang di-input-kan

Datatype hasil : VARIANT

artinya :

jika yang diinputkan menghasilkan suatu object, maka hasilnya ber-datatype object.
jika yang diinputkan menghasilkan suatu nilai skalar, maka hasilnya ber-datatype nilai skalar hasil tersebut

Jadi, ketika yang diinputkan adalah kegiatan pada worksheet (ndak usah membahas yang inputnya chart dulu ya), maka hasilnya bisa ada 2 datatype, yaitu :

jika yang diinputkan menghasilkan object Range (karena kegiatan worksheet dalam hal ini artinya tulisan formula), maka hasilnya ber-datatype object Range
jika yang diinputkan menghasilkan nilai skalar, maka hasilnya ber-datatype sesuai datatype nilai hasil tersebut.

Contoh 001 :

di cell Excel, misal di cell G7, bisa diberi formula berbunyi =A1

1. Formula tersebut, hasilnya di cell excel, menghasilkan nilai yang ada di A1 ya kan...

2. Kegiatan yang terjadi dalam formula tersebut sebenarnya suatu proses evaluate. Dalam VBA, methods evaluate bisa digunakan dengan bunyi :

Evaluate("A1")
atau
Evaluate("=A1")
atau
[A1]
(lihat lagi cara penggunaan Evaluate diatas).

3. hasil Evaluate adalah object range, karena formula di G7 yang dipakai dalam evaluate (di VBA) akan menghasilkan object range dan bukan nilai skalar.

Jadi, kaidah datatype akan berlaku dalam hal ini (sudah baca BelajarVBA tentang variabel dan datatype ?)

Mari kita buktikan.
Isi cell A1 dengan bilangan (bertype numerik) senilai 12345
Tulis disebuah prosedur (buat sendiri prosedurnya) di sebuah general module.

Jika ada variabel yang dideklarasikan dengan baris kode : (ingat ya, A1 isinya bilangan 12345)

Dim rng As Range

Dim dblNilai As Double

'definisi object range -> selalu pakai Set kan
Set rng = Range("A1") 'biasanya begini kan cara definisi sebuah variabel object range
'kalau pakai evaluate

Set rng = Evaluate("A1") 'inputnya harus teks kan (lihat cara pakai evaluate diatas)

'atau

Set rng = [A1] 'inputnya harus bunyi yang bisa dipasang sebagai formula dalam cell kan

'kedua baris diatas tidak akan menghasilkan error, karena evaluate akan menghasilkan object range

'sekarang pakai nilai skalar ada di object range

'nilai di range A1 bisa diambil dengan bunyi :

dblNilai = Range("A1").Value

'karena default properti object range adalah proeperti VALUE, maka kebanyakan pengguna VBA yang menulis :

dblNilai = Range("A1") 'tidak akan menghasilkan error, tapi tetap saja baris ini sama dengan bunyi baris Range("A1").Value

'jadi dblNilai bukan diisi oleh object range A1, tapi diisi oleh nilai yang ada dalam properti VALUE dari range A1

'kalau dblNilai akan diisi dengan object range A1,
'maka bunyi baris kodenya harus Set dblNilai = Range("A1")
'dan ini pasti error, coba aja kalo gak percaya.
'Kenapa kok error hayo ?

'mengisi variabel dblNilai yang berdatatype Double yang berisi nilai skalar bisa menggunakan evaluate seperti :

dblNilai = Evaluate("A1").Value 'bagian Evaluate("A1") menghasilkan object Range
'karena bunyi inputannya menghasilkan suatu range

'atau kebanyakan VBA yang pengen pakai default value dari object range akan menulis :

dblNilai = Evaluate("A1") 'yang sama dengan baris kode berbunyi : dblNilai = Range("A1")

'kalau pakai bracket (kurung siku) dalam menggunakan evaluate, jadinya :

dblNilai = [A1].Value 'hasil bagian [A1] adalah object range karena bunyi inputannya menghasilkan suatu range

'atau yang pengen pakai default properti object range yang berupa properti VALUE, bisa menulis :

dblNilai = [A1]

'sekali lagi, proses 4 baris diatas bukan mengisi dblNilai dengan range A1, tapi mengisi dblNilai dengan nilai properti VALUE milik range A1

Sekarang,
Contoh 002 :

di A2 diisi teks berbunyi BeExcel

1. di cell F5 bisa diberi formula berbunyi : =A2 dan akan tampak dicell isi F5 adalah sama dengan isi A2 dan ber-datatype string (teks)

2. untuk pakai evaluate seperti contoh 001, ketika memanfaatkan hasil evaluate yang ber-type Object (karena formula berbunyi =A2 menghasilkan object range), variabel object range bernama rng yang ada di contoh 001 masih bisa dipakai. Cara pakai evaluate ke variabel rng :

'definisi object range -> selalu pakai Set kan
Set rng = Range("A2") 'biasanya begini kan cara definisi sebuah variabel object range
'kalau pakai evaluate

Set rng = Evaluate("A2") 'inputnya harus teks kan (lihat cara pakai evaluate diatas)

'atau

Set rng = [A2] 'inputnya harus bunyi yang bisa dipasang sebagai formula dalam cell kan

'kedua baris diatas tidak akan menghasilkan error, karena evaluate akan menghasilkan object range

'tapi kalau untuk ambil nilai dalam object range hasil evaluate, dibutuhkan variabel ber-datatype string. Jadi harus ada deklarasi variabel seperti :

Dim sNilai As String

'lalu variabel sNilai bisa dipakai
sNilai = Range("A2").Value

'atau kalau mau pakai default properti milik object range yang bernama properti VALUE,

sNilai = Range("A2")

'untuk hasil evaluate juga demikian, bisa ditulis :

sNilai = Evaluate("A2").Value

'atau kalau mau pakai default properti milik object range yang bernama properti VALUE,

sNilai = Evaluate("A2")

'pakai evaluate yang notasinya bracket (kurung siku) juga bisa,

sNilai = [A2].Value

'atau kalau mau pakai default properti milik object range yang bernama properti VALUE,

sNilai = [A2]

simpulan dari contoh 001 dan contoh 002 (inputannya menghasilkan object)
1. jadi, kalau ambil hasil evaluate yang nilai skalarnya, berarti mengambil properti VALUE hasil evaluate jika inputan evaluate menghasilkan Object, bisa disimpan divariabel yang datatype-nya sesuai dengan si datatype nilai skalar yang akan diambil

2. kalau akan ambil object range hasil evaluate-nya, maka harus disimpan dalam variabel object range (variabel object juga bisa, cuma lebih lama)

Yuk mainan yang inputannya bukan object.

Contoh 003 :

di A1 sudah ada data numerik 12345

di A2 sudah ada data teks berbunyi BeExcel

di E3 bisa diberi formula berbunyi : =A1 & " milik " & A2 dan hasilnya adalah teks berbunyi '12345 milik BeExcel'

Hasil formula di E3 ini bukanlah ber-datatype object Range, tapi nilai skalar berbunyi '12345 milik BeExcel' yang ber-type String (teks)

1. menyusun bunyi formula seperti formula E3 dalam VBA, disimpan dalam variabel bernama sFormula yang ber-type String

Dim sFormula As String

'menulis petik dua (") dalam ekspresi string di VBA harus diganti dengan 2 (dua) buah petik dua ("")

'jadi, bunyi formula : A1 & " milik " & A2 harus ditulis di-VBA menjadi A1 & "" milik "" & A2

'karena ekspresi string di-VBA juga harus diapit petik dua ("), maka bentuknya menjadi :

sFormula = "A1 & "" milik "" & A2"

'cek deh hasilnya, pastikan di E3 ada formula berbunyi =A1 & " milik " & A2

msgbox "Formula di E3 : " & range("e3").formula _

& vbcrlf & "Formula di sFormula : " & sformula _

& vbcrlf & "Hanya selisih karakter sama dengan ya....",vbinformation,"Jas Inpo ajah"

2. Jadi, variabel object range bernama rng di prosedur yang ada, sudah ndak bisa dipakai untuk menyimpan nilai hasil formula yang bunyinya seperti formula di E3 atau yang disimpan dalam variabel sFormula.

'artinya, kalau baris kode berikut diaktifkan dan dijalankan prosedurnya, akan menghasilkan error

'Set rng = Range(sFormula)

'jadi kita lewatkan saja bagian yang akan menyimpan sebagai object range (soale ndak mungkin kalau langsung pakai formula)

3. pakai evaluate, bunyi formula : A1 & " milik " & A2

'hasilnya akan berupa nilai skalar ber-type string. Jadi disiapkan dulu variabel penyimpannya

Dim sHasilEval As String

'baru deh si variabel sHasilEval dipakai

sHasilEval = Evaluate( sFormula ) 'kalau pakai Evaluate() harus diberi inputan teks kan ya, oleh sebab itu pakai isi dalam sFormula

sHasilEval = [A1 & " milik " & A2] 'kalau pakai bracket (kurung siku) bisa tulis formula dalam cell tanpa sama dengan kan

Sekarang pakai formula yang lebih ruwet ya... coba secara perlahan saja.

Contoh 004 :

di B1 diisi teks berbunyi Ini Angka

di B2 diisi teks berbunyi Ini milis kita

di C6 diberi formula VLookUp berbunyi : =VLOOKUP("BeExcel",$A$1:$B$2,2,0)

-> me-lookup teks 'BeExcel' di A1:B2 dan ambil nilai di kolom ke-2 dari A1:B2 (ambil kolom B) dengan pencarian yang Exact (sama percis)

hasil formula vLookUp adalah nilai skalar. Begitu juga dengan fungsi LookUp jika formula menggunakan fungsi LookUp. Jadi hasil evaluate disimpan pada variabel skalar dan bukan variabel object.

'karena kolom B ber-type teks semua, maka hasil evaluate akan ber-type string. Jadi pakai sHasilEval

sHasilEval = [VLOOKUP("BeExcel",$A$1:$B$2,2,0)]

'kalau mau pakai evaluate(), berarti susun bunyi formula dalam VBA, simpan di sFormula dan lakukan sHasilEval untuk menyimpan hasil Evaluate()

sFormula = "VLOOKUP(""BeExcel"",$A$1:$B$2,2,0)"

sHasilEval = Evaluate( sFormula )

Contoh 005 :

pakai formula me-lookup data yang lain ya...

di C8 diberi formula Index Match berbunyi =INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))

hasil formula Index bisa berupa object range. Formula Index Match (yang paling luarnya kan Index tuh) di atas menghasilkan object Range. Begitu juga dengan formula Offset dalam bentuk Offset Match. Jadi, variabel object range bernama rng bisa digunakan.

'ketika hasil evaluate formula Index bisa menghasilkan object range, maka variabel rng bisa dipakai sebagai berikut :

Set rng = [INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))]

'atau jika akan menggunakan Evaluate(), harus membuat teks formulanya
sFormula = "INDEX($B$1:$B$2,MATCH(""BeExcel"",$A$1:$A$2,0))"

Set rng = Evaluate( sFormula )

'karena kolom B ber-type teks semua, maka nilai properti VALUE dari object range hasil evaluate akan ber-type string. Jadi pakai sHasilEval

sHasilEval = [INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0))].Value

'kalau mau pakai evaluate(), berarti susun bunyi formula dalam VBA, simpan di sFormula dan lakukan sHasilEval untuk menyimpan nilai properti VALUE dari object range hasil Evaluate()

sFormula = "INDEX($B$1:$B$2,MATCH(""BeExcel"",$A$1:$A$2,0))"

sHasilEval = Evaluate( sFormula ).Value

Nah, gimana kalau yang disimpan dalam cell adalah teks formula. Artinya si cell bukan berisi formula tapi teksnya formula.

Kita pakai formula vLookUp dan Index Match lagi ya... supaya manteb puyengnya...

Contoh 006 :

yang Index Match dulu ya

di B11 ditulis bunyi teks INDEX($B$1:$B$2,MATCH("BeExcel",$A$1:$A$2,0)) [tidak pakai sama dengan loh ya]

karena data di B11 berisi teks formula (bukan berisi formula), maka tipe data di B11 adalah teks (string) karena bunyi tulisannya dan bukan karena hasil olahan bunyi tulisannya.

jadi, kalau akan menggunakan evaluate yang bracket, hasilnya akan berupa string dan harus disimpan dalam variabel string. Simpan saja ke sFormula ya, karena teks di B11 bunyi teks formula.

'B11 berisi teks yang bunyinya terdengar seperti formula.

sFormula = [B11] 'akan menghasilkan teks isi B11

Msgbox "Isi sFormula setelah dikenai [B11] adalah " & sFormula,vbinformation,"jas inpo" 'cek aja deh

'untuk mendapatkan hasil olahan dari teks formula yang tertulis di cell B11, maka harus digunakan Evaluate().

'karena sFormula sudah diisi bunyi teks formula dari B11 oleh baris sFormula = [B11] , berarti bisa tinggal dipakai dong

'misal disimpan dalam variabel rng (karena bunyi teksnya terdengar bunyi formula Index yang bisa menghasilkan object range)

Set rng = Evaluate( sFormula )

'atau tetap mau dapat nilai properti VALUE dari object range hasil evaluate terhadap bunyi teks formula yang terdengar seperti Index yang menghasilkan object range, maka bisa :

sHasilEval = Evaluate( sFormula ).Value

'atau pakai default properti,

sHasilEval = Evaluate( sFormula )

Contoh 007 :

sekarang pakai yang vLookUp. Hasilnya nilai skalar, jadi variabel rng ndak bisa dipakai.

di B12 ditulis bunyi teks VLOOKUP("BeExcel",$A$1:$B$2,2,0) [tidak pakai sama dengan loh ya]

karena data di B12 berisi teks formula (bukan berisi formula), maka tipe data di B12 adalah teks (string) karena bunyi tulisannya dan bukan karena hasil olahan bunyi tulisannya.

jadi, kalau akan menggunakan evaluate yang bracket, hasilnya akan berupa string dan harus disimpan dalam variabel string. Simpan saja ke sFormula ya, karena teks di B12 bunyi teks formula.

'B12 berisi teks yang bunyinya terdengar seperti formula.

sFormula = [B12] 'akan menghasilkan teks isi B12

Msgbox "Isi sFormula setelah dikenai [B12] adalah " & sFormula,vbinformation,"jas inpo" 'cek aja deh

'untuk mendapatkan hasil olahan dari teks formula yang tertulis di cell B12, maka harus digunakan Evaluate().

'karena sFormula sudah diisi bunyi teks formula dari B12 oleh baris sFormula = [B12] , berarti bisa tinggal dipakai dong

'bunyi teksnya seperti formula vLookUp yang hanya bisa menghasilkan nilai skalar. Kolom B berisi datatype teks (string). Berarti hasil olahan teks formula di sFormula akan ber-type String

'jadi dipakailah variabel sHasilEval yang ber-type string untuk menampung hasil Evaluate()

sHasilEval = Evaluate( sFormula ) 'hanya bisa demikian bentuknya. Jika diubah jadi sHasilEval = Evaluate( sFormula ).Value akan error... Kenapa hayo ?

brrr.... ih dingiin...

badannya kedinginan tapi jarinya panas karena bengkak nulis dongengan...

kayanya lebih enak kalo ndongeng-nya langsung secara lisan deh...

kopdar yuk...
[eh ada yang nyahut...] ayok...
asik....
kapan, dimana, berapa yang mau dateng, di Jakarta atau dipinggiran Jakarta atau malah didaerah lain ?
[xixixi... ada yang ngegerundel...] ih... kepo amat...
wakakaka... kepo adalah sebagian tanda kecerdasan, sayangnya, harus dipakai secara cerdas juga kali ya...
kayanya detox-nya masih kurang banyak nih... ocehannya jadi gak karuan..
udah ah..

met mencoba ya guys...
kasih deh filenya. dilengkapi sendiri dengan msgbox penampil angka hasilnya ya. sila unduh disini.

BelajarVBA 011 - Range 03 (UsedRange,Intersect,Union)

2013-09-17T10:55:00.000+07:00

Coretan Mr. Kid

Bisa jadi, salah satu alasan Tuhan memberi pilihan kebaikan dan keburukan adalah untuk membuat manusia berpikir dengan akalnya menjadi tahu bahwa hanya Tuhan yang Maha Pengampun dan hanya Tuhan yang bisa mengampuni segala dosa dan kesalahan sebesar apapun, sehingga manusia mau bersegera memohon ampunan Tuhan. Apakah kita termasuk orang yang berpikir ?

Setelah pembahasan yang lalu diakhiri dengan mendapatkan rujukan ke seluruh cell, maka mulai pembahasan kali ini adalah tentang mendapatkan suatu rujukan range yang lebih spesifik. Pembahasan kali ini akan menggunakan sebuah data di-sheet bernama 'Data15' yang ada difile bernama BelajarVBA011_03.xlsm.

Gambar berikut ini adalah bentuk data dalam sheet bernama 'Data15' tersebut.

Mendapatkan sebuah area range yang berisi seluruh data

Pembahasan terakhir yang lalu adalah mendapatkan seluruh range dalam worksheet. Sangat jarang terjadi data akan tersebar dari cell pertama (A1) sampai dengan baris terkahir di kolom terkanan suatu worksheet. Pada umumnya, data dalam sebuah worksheet akan membentuk kumpulan-kumpulan dataset atau bahkan hanya berisi sebuah tabel atau dataset kecil. Tentu saja merujuk ke seluruh range dalam worksheet akan menjadi terasa berlebihan karena sebaran area data tidak sampai seluas worksheet.

Object worksheet memiliki properti bernama UsedRange yang dapat digunakan untuk mendapatkan sebuah area yang melingkupi seluruh sebaran data. Pada gambar diatas, data tersebar mulai dari range B2 sampai L18. Baris 1 ataupun kolom A tidak berisi data. Begitu juga dengan kolom K sampai kolom terkanan dan baris 19 sampai baris terakhir range dalam worksheet. Prosedur berikut ini adalah contoh penggunaan properti UsedRange milik object Worksheet untuk mendapatkan sebuah area yang melingkupi seluruh sebaran data di sheet bernama 'Data15'.
   Public Sub TentangUsedRange()
      Dim rng As Range

      Set rng = Sheets("Data15").UsedRange
      rng.Copy
      MsgBox "UsedRange : " & rng.Address & vbCrLf & _
            "Jumlah area : " & rng.Areas.Count
   End Sub
Proses yang terjadi dalam prosedur tersebut adalah :

Deklarasi variabel ber-type object Range sebagai penyimpan range yang dirujuk pada baris kode :

Dim rng As Range

Mengisi variabel range dengan rujukan berupa range terpakai (UsedRange) yang ada di sheet bernama 'Data15' pada baris kode :

Set rng = Sheets("Data15").UsedRange

Contoh proses yang dilakukan terhadap area range yang sudah didapatkan dan disimpan pada variabel bernama rng. Kali ini contohnya adalah meng-Copy ke Clipboard, pada baris kode :

rng.Copy

Menampilkan pesan kepada user dengan sebuah kotak pesan (MsgBox) yang diisi teks informasi tentang alamat range terpakai (UsedRange) melalui bagian berbunyi rng.Address. Pesan juga berisi (digabung) dengan jumlah area range yang tersimpan dalam variabel bernama rng melalui bagian berbunyi rng.Areas.Count. Teks pesan tentang jumlah area diletakkan pada baris baru, dengan adanya bagian berbunyi vbCrLF. Seluruh teks informasi dirangkai menjadi sebuah teks panjang dengan cara menggabungkannya, yaitu dengan memanfaatkan operator penggabungan teks berupa karakter ampersand (&).

Karena sebaran data dimulai dari B2 sampai L18, maka prosedur diatas akan menghasilkan alamat UsedRange berupa teks berbunyi '$B$2:$L$18' dengan jumlah area range adalah 1 (satu) yang artinya sebagai sebuah range yang diblok dari B2 sampai L18.

Beberapa hal yang sebaiknya dicoba adalah ketika :

minimal sebuah cell di baris 1 mulai kolom B diisi data, sedangkan seluruh kolom A adalah kosong, maka UsedRange akan berubah menjadi '$B$1:$L$18'
minimal sebuah cell di kolom A mulai baris 2 diisi data, sedangkan seluruh baris 1 adalah kosong, maka UsedRange akan berubah menjadi '$A$2:$L$18'
cell A1 diisi data, sedangkan di baris 1 mulai kolom B adalah kosong dan di kolom A mulai baris 2 adalah kosong, maka UsedRange akan berubah menjadi '$A$1:$L$18'
sebuah cell di baris 1 mulai kolom B diisi data dan sebuah cell di kolom A mulai baris 2 diisi data serta cell A1 adalah kosong, maka UsedRange akan berubah menjadi '$A$1:$L$18'

Uji coba serupa juga bisa dilakukan pada area kolom mulai kolom L diseluruh barisnya atau mulai baris 19 diseluruh kolomnya. Hasil uji coba akan membantu untuk memahami bahwa UsedRange akan selalu menunjukkan suatu area mulai cell terpojok kiri atas yang berisi data sampai dengan cell terkanan bawah yang terisi data.

Perpotongan range

Antara 2 (dua) area range yang saling berpotongan bisa didapatkan suatu area range perpotongan. Salah satu kegunaan dari memperoleh perpotongan range adalah sebagai alat cek keberadaan range yang diubah user dengan suatu area range data yang boleh diubah user. Jika ada range perpotongannya, maka user melakukan pengubahan pada tempatnya dan proses pengubahan boleh dilakukan. Jika tidak ada hasil perpotongannya, maka proses pengubahan tidak boleh terjadi.

Perpotongan range bisa didapatkan dengan menggunakan methods milik Application yang bernama Intersect. Bagian ini akan menjabarkan konsep perpotongan range dengan methods bernama Intersect tersdebut. Contohnya, pada data yang ada dalam gambar diatas, dibutuhkan data milik header data h2 dan h3 dari kedua dataset cabang. Range data (yang di-border) bisa didapatkan dari perpotongan antara kolom D:E dan I:K dengan baris 6:16. Prosedur untuk proses ini bisa berbentuk seperti prosedur berikut ini :
   Public Sub TentangIntersection()
Dim rngCabang As Range, rngBarisData As Range
Dim rngPotongan As Range

Set rngCabang = Range("D:E,I:J")
Set rngBarisData = Range("6:16")
Set rngPotongan = Intersect(rngCabang, rngBarisData)
rngPotongan.Copy
MsgBox "Data Kolom : " & rngCabang.Address & vbCrLf & _
            "Data Baris : " & rngBarisData.Address & vbCrLf & _
            "Potongan : " & rngPotongan.Address & vbCrLf & _
            "Jumlah area : " & rngPotongan.Areas.Count
   End Sub
Proses yang terjadi pada prosedur diatas adalah :

Deklarasi variabel rngVabang ber-type object Range sebagai wadah range yang merujuk ke kolom-kolom yang berisi header tabel h2 dan h3 dikedua dataset. Juga dideklarasikan variabel rngBarisData yang ber-type object Range sebagai wadah range yang merujuk ke baris-baris data dari kedua dataset cabang.
Deklarasi variabel rngPotongan ber-type object Range sebagai wadah range hasil perpotongan antara rngCabang dengan rngBarisData.
Mendefinisikan isi variabel rngCabang dengan range dikolom-kolom dengan header h2 dan h3 di kedua dataset cabang pada baris kode :

Set rngCabang = Range("D:E,I:J")

Mendefinisikan isi variabel rngBarisData dengan range dibaris-baris kedua dataset cabang pada baris kode :

Set rngBarisData = Range("6:16")

Mendefinisikan isi variabel rngPotongan dengan hasil pemanfaatan methods Intersect, dengan range yang diperpotongkan adalah rngCabang dengan rngBarisData. Proses ini ada dibaris kode :

Set rngPotongan = Intersect(rngCabang, rngBarisData)

Memanfaatkan hasil perpotongan range. Pada prosedur diatas, contoh pemanfaatannya adalah meng-Copy hasil perpotongan range ke Clipboard pada baris kode :

rngPotongan.Copy

Menampilkan pesan kepada user menggunakan sebuah kotak pesan (MsgBox) yang berisi alamat dari rngCabang, alamat rngBarisData dan alamat hasil perpotongan (rngPotongan) yang memanfaatkan properti Address milik object Range. Juga diinformasikan jumlah area range dalam hasil perpotongan (rngPotongan) menggunakan properti Count milik object collection Areas, dimana object collection Areas adalah salah satu properti milik object Range. Seluruh informasi disusun menggunakan operator penggabungan teks berupa karakter ampersand (&) dan diatur letak barisnya dengan vbCrLf.

Ketika prosedur diatas dijalankan, maka akan didapat 2 (dua) area range hasil perpotongan. Artinya, dalam variabel rngPotongan ada 2 (dua) area range. Hal ini disebabkan karena rngCabang berisi 2 (dua) area range, yaitu area range kolom D:E dan area range kolom I:J, dipotongkan dengan rngBarisData yang berisi 1 (satu) area range, yaitu baris 6 sampai baris 16.

Menyimpan banyak area range kedalam sebuah variabel object Range

Kadangkala ditemukan suatu kondisi bahwa data yang akan diolah membentuk banyak area dan tidak didapatkan secara sekaligus. Sedangkan pengolahan lanjutan akan mengolahnya secara berkesinambungan. Oleh sebab itu, area-area range yang telah didapatkan tersebut harus disimpan dalam sebuah variabel object Range.

Menyimpan berbagai area range ke dalam sebuah variabel object Range bisa dilakukan dengan memanfaatkan methods Union milik Application. Contohnya, pada gambar diatas, telah didapatkan 3 (tiga) area range yang berupa data-data di header h2 dan h3 dikedua dataset cabang yang memiliki nilai di header h2-nya. Artinya, ada area range D6:E13, I6:J10 dan I12:J15. Ketiga area range tersebut akan disimpan disebuah variabel object range agar bisa diolah lebih lanjut sebagai satu kesatuan data yang dibutuhkan. Prosedur untuk contoh ini dapat berbunyi :
   Public Sub TentangUnion()
Dim rngCabang1 As Range
Dim rngCabang2A As Range, rngCabang2B As Range
Dim rngGabung As Range

Set rngCabang1 = Range("D6:D13")
Set rngCabang2A = Range("i6:i10")
Set rngCabang2B = Range("i12:i15")
Set rngGabung = Union(rngCabang1, rngCabang2A, rngCabang2B)
MsgBox "Range 1 : " & rngCabang1.Address & vbCrLf & _
            "Range 2 : " & rngCabang2A.Address & vbCrLf & _
            "Range 3 : " & rngCabang2B.Address & vbCrLf & _
            "Gabungan : " & rngGabung.Address & vbCrLf & _
            "Jumlah area : " & rngGabung.Areas.Count
   End Sub
Proses yang terjadi pada prosedur diatas adalah :

Deklarasi variabel rngCabang1 ber-type object Range sebagai wadah data header h2 dan h3 di dataset cabang1.
Deklarasi variabel rngCabang2A ber-type object Range sebagai wadah data header h2 dan h3 di dataset cabang2 pada area atas sebelum baris cell kosong di range I11:J11. Sedang variabel rngCabang2B adalah untuk setelah baris kosong tersebut.
Deklarasi variabel rngGabung ber-type object Range sebagai wadah hasil penggabungan seluruh area tersebut kedalam sebuah variabel object range.
Mendefinisikan range untuk rngCabang1 dengan range D6:D13
Mendefinisikan range untuk rngCabang2A dengan range I6:J10
Mendefinisikan range untuk rngCabang2B dengan range I12:J15
Mendefinisikan range untuk rngGabung dengan hasil penggabungan seluruh range yang diketahui dengan menggunakan methods Union. Proses ini adalah pada baris kode :

Set rngGabung = Union(rngCabang1, rngCabang2A, rngCabang2B)

Menampilkan pesan kepada user menggunakan sebuah kotak pesan (MsgBox) berisi alamat masing-masing range yang digabungkan dan alamat range hasil penggabungan. Juga disertai informasi jumlah area hasil penggabungan.

Simpulan

Pengolahan data membutuhkan rujukan range yang spesifik pada yang dibutuhkan saja. Proses otomasi atau aplikasi berbasis Excel juga dituntut untuk bisa mendapatkan rujukan range yang spesifik tersebut.

Proses perujukan range yang spesifik pada bahasan kali ini baru pada area range yang berisi data saja dan beberapa cara mendapatkan range yang dibutuhkan dari suatu range yang luas melalui perpotongan (Intersect). Selain itu juga dibahas tentang menyatukan tempat penyimpanan range dalam memori komputer (dalam variabel) menggunakan Union.

Pembelajaran lebih lanjut untuk bisa mendapatkan area data yang akan di-Intersect-kan atau yang akan di-Union-kan sangat diperlukan, sehingga proses otomasi atau aplikasi berbasis Excel yang sedang dibangun bisa menjadi lebih dinamis terhadap perubahan jumlah record data yang akan diolah.

:)

Insya Allah bahasan selanjutnya tentang Offset dan Resize. Kedua hal ini termasuk tulang punggung untuk mendinamiskan perujukan range yang dibutuhkan dalam suatu proses otomasi atau aplikasi berbasis Excel.

BelajarVBA 011 - Range 02 (seluruh baris/kolom)

2013-09-13T01:58:00.002+07:00

Coretan Mr. Kid

Meremehkan dosa sekecil apapun adalah awal bencana diri. Janganlah pernah meremehkan dosa sekecil apapun. Segeralah meminta ampunan-Nya.

Kadangkala dalam mengotomasi ataupun membangun aplikasi berbasis Excel, dibutuhkan pemilihan range utuh seluruh kolom atau seluruh baris. Bagian ini akan membahas tentang mendapatkan range di seluruh baris atau seluruh kolom tersebut.

Mendapatkan range disuatu baris

Prosedur yang digunakan untuk menampung script belajar pada kegiatan ini adalah prosedur bernama RangeSuatuBaris yang memiliki bentuk blok prosedur sebagai berikut :
   Public Sub RangeSuatuBaris()
   Dim rng As Range

   'baris kode akan dimulai dari baris kosong setelah baris ini

   End Sub

Beberapa cara merujuk range suatu baris adalah sebagai berikut :
Satu baris saja, yaitu baris 3
'>> baris 3 saja
'dengan Rows diberi inputan nilai index baris
Set rng = Rows(3)       'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Rows diberi inputan teks baris
Set rng = Rows("3")     'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Rows diberi inputan teks baris seperti inputan range
Set rng = Rows("3:3")   'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks baris dari dan sampai
Set rng = Range("3:3") 'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar sebuah range dalam baris tersebut
Set rng = Range("A3").EntireRow 'simpan di variabel bernama rng
rng.Copy                         'copy range terpilih ke clipboard
MsgBox rng.Address               'pesan bahwa range terpilih sudah ter-copy

Dari contoh diatas, untuk mendapatkan seluruh range pada sebuah baris (misal pada baris 3) dapat dilakukan dengan menggunakan :

properti Rows milik Application yang dapat diberi inputan berupa :
- nilai index baris yang bertipe numerik (3)
- teks nomor baris yang bertipe teks ("3")
- teks alamat baris untuk seluruh kolom dari awal sampai akhir seperti ("3:3")
object Range yang :
- merujuk langsung dengan inputan berupa teks alamat baris untuk seluruh kolom dari awal sampai akhir seperti ("3:3")
- menggunakan properti EntireRow milik object range berdasar sebuah range di baris tersebut seperti baris kode :
  Range("A3").EntireRow
  yang berarti pada range A3 dilanjutkan proses untuk memilih suatu baris di range tersebut

Proses untuk menyusun alamat range seperti yang dijelaskan pada pembahasan sebelumnya juga bisa dimanfaatkan.

Beberapa baris yang kontinyu, yaitu dari baris 3 sampai baris 7
Baris yang kontinyu artinya akan terkumpul banyak range yang saling bersebelahan tanpa putus membentuk 1 (satu) area range. Untuk merujuk ke satu area range dari baris 3 sampai baris 7 dibutuhkan alamat range bertipe teks yang berbunyi "3:7".
'>> dari baris 3 sampai baris 7
'dengan Rows diberi inputan teks baris
Set rng = Rows("3:7")   'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks baris dari dan sampai
Set rng = Range("3:7") 'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam baris-baris tersebut
Set rng = Range("A3:A7").EntireRow 'simpan di variabel bernama rng
rng.Copy                            'copy range terpilih ke clipboard
MsgBox rng.Address                'pesan bahwa range terpilih sudah ter-copy

Dari contoh diatas, properti Rows masih bisa digunakan karena yang dirujuk masih berupa 1 (satu) area range. Seluruh inputan sudah bertipe teks berupa alamat range.

Beberapa baris yang saling terpisah, yaitu baris 3, 8, dan dari baris 15 sampai baris 17
Teks alamat range untuk merujuk baris-baris seperti ini akan berbunyi "3:3,8:8,15:17". Hasilnya akan berupa 3 (tiga) buah area range, yaitu area range baris 3, area range baris 8, dan area range baris 15 sampai 17. Karena sudah ada lebih dari 1 (satu) area range, maka properti Rows sudah tidak dapat digunakan. Jadi range dirujuk menggunakan object Range.
'>> baris 3, 8, 15:17
'dengan Range diberi inputan teks alamat range
Set rng = Range("3:3,8:8,15:17")    'simpan di variabel bernama rng
rng.Copy                            'copy range terpilih ke clipboard
MsgBox rng.Address                'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam baris-baris tersebut
Set rng = Range("A3,A8,A15:A17").EntireRow 'simpan di variabel bernama rng
rng.Copy                                    'copy range terpilih ke clipboard
MsgBox rng.Address                        'pesan range terpilih sudah ter-copy

Mendapatkan range disuatu kolom

Pada dasarnya, proses untuk mendapatkan range disuatu kolom mirip dengan untuk mendapatkan seluruh baris. Perbedaan mendasar adalah alamat range yang digunakan adalah nama-nama kolom Excel. Indeks kolom Excel bisa digunakan ketika memanfaatkan properti Columns dan hanya untuk merujuk 1 (satu) kolom saja. Seperti halnya properti Rows ketika diberi inputan berupa teks alamat range, properti Columns hanya bisa digunakan untuk merujuk ke 1 (satu) area range suatu kolom utuh.

Prosedur yang digunakan untuk menampung script belajar pada kegiatan ini adalah prosedur bernama RangeSuatuKolom yang memiliki bentuk blok prosedur sebagai berikut :
   Public Sub RangeSuatuKolom()
   Dim rng As Range

   'baris kode akan dimulai dari baris kosong setelah baris ini

   End Sub

Beberapa cara merujuk range suatu kolom adalah sebagai berikut :
Satu kolom saja, yaitu kolom B
'>> kolom B saja
'dengan Columns diberi inputan nilai indeks kolom
Set rng = Columns(2)    'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Columns diberi inputan teks kolom
Set rng = Columns("B") 'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Columns diberi inputan teks kolom seperti inputan range
Set rng = Columns("B:B")    'simpan di variabel bernama rng
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address        'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks kolom dari dan sampai
Set rng = Range("B:B") 'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar sebuah range dalam kolom tersebut
Set rng = Range("B1").EntireColumn 'simpan di variabel bernama rng
rng.Copy                            'copy range terpilih ke clipboard
MsgBox rng.Address                'pesan bahwa range terpilih sudah ter-copy

Contoh-contoh diatas menggunakan properti Columns milik Application dan ada yang menggunakan properti EntireColumn milik object Range.

Beberapa kolom yang kontinyu, yaitu dari kolom B sampai kolom E
Teks alamat range untuk merujuk ke suatu kolom dari kolom B sampai kolom E akan berbunyi "B:E".
'>> dari kolom B sampai kolom E
'dengan Columns diberi inputan teks kolom seperti inputan range
Set rng = Columns("B:E")    'simpan di variabel bernama rng
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address        'pesan bahwa range terpilih sudah ter-copy

'dengan Range diberi inputan teks kolom dari dan sampai
Set rng = Range("B:E") 'simpan di variabel bernama rng
rng.Copy                'copy range terpilih ke clipboard
MsgBox rng.Address    'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam kolom tersebut
Set rng = Range("B1:E1").EntireColumn   'simpan di variabel bernama rng
rng.Copy                                'copy range terpilih ke clipboard
MsgBox rng.Address                    'pesan range terpilih sudah ter-copy

Dari contoh diatas, penggunaan indeks kolom Excel sudah tidak dapat digunakan lagi karena akan merujuk ke banyak kolom. Inputan sudah berupa teks alamat range.

Beberapa kolom yang saling terpisah, yaitu kolom B, F, dan dari kolom K sampai kolom M
Teks alamat range untuk merujuk kolom-kolom seperti ini akan berbunyi "B:B,F:F,K:M". Hasilnya akan berupa 3 (tiga) buah area range, yaitu area range kolom B, area range kolom F, dan area range kolom K sampai M. Karena sudah ada lebih dari 1 (satu) area range, maka properti Columns sudah tidak dapat digunakan. Jadi range dirujuk menggunakan object Range.
'>> kolom B, F, K:M
'dengan Range diberi inputan teks kolom dari dan sampai
Set rng = Range("B:B,F:F,K:M") 'simpan di variabel bernama rng
rng.Copy                        'copy range terpilih ke clipboard
MsgBox rng.Address            'pesan bahwa range terpilih sudah ter-copy

'dengan Range berdasar suatu range dalam kolom tersebut
Set rng = Range("B1,F1,K1:M1").EntireColumn 'simpan di variabel bernama rng
rng.Copy                                    'copy range terpilih ke clipboard
MsgBox rng.Address                        'pesan range terpilih sudah ter-copy

Mendapatkan seluruh range dalam worksheet

Keadaan ini bisa juga diartikan seluruh baris atau seluruh kolom yang ada dalam worksheet. Prosedur yang digunakan untuk menampung script belajar pada kegiatan ini adalah prosedur bernama RangeSemuanya yang memiliki bentuk blok prosedur sebagai berikut :
   Public Sub RangeSemuanya()
      Dim rng As Range

      'baris kode akan dimulai dari baris kosong setelah baris ini

   End Sub

Beberapa cara merujuk range seluruh range dalam worksheet adalah sebagai berikut :
Cara 1 : memanfaatkan properti Cells
'Cara 1 : Cells
Set rng = Cells     'simpan di variabel bernama rng
rng.Copy            'copy range terpilih ke clipboard
MsgBox rng.Address 'pesan bahwa range terpilih sudah ter-copy

Cara 2 : merujuk seluruh range pada baris pertama kemudian merujuk ke seluruh kolomnya
'Cara 2 : Rows 1 dan EntireColumn
Set rng = Rows(1)           'merujuk baris pertama secara utuh
Set rng = rng.EntireColumn 'hasilnya dirujuk ke seluruh kolom
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address        'pesan bahwa range terpilih sudah ter-copy
Cara 3 : merujuk seluruh range pada kolom pertama kemudian merujuk ke seluruh barisnya
'Cara 3 : Columns 1 dan EntireRow
Set rng = Columns(1)        'merujuk kolom pertama secara utuh
Set rng = rng.EntireRow     'hasilnya dirujuk ke seluruh kolom
rng.Copy                    'copy range terpilih ke clipboard
MsgBox rng.Address        'pesan bahwa range terpilih sudah ter-copy

Simpulan

Beberapa hal yang menjadi benang merah dari contoh-contoh diatas adalah :

Properti Rows atau Columns dapat digunakan untuk merujuk ke seluruh range dalam baris-baris tersebut atau kolom-kolom tersebut selama hasilnya membentuk 1 (satu) area range. Jadi, baris-baris atau kolom-kolom yang dirujuk haruslah kontinyu.
Indeks baris maupun indeks kolom bisa digunakan sebagai inputan pada penggunaan properti Rows atau Columns untuk merujuk ke 1 (satu) baris saja atau 1 (satu) kolom saja. Hal ini dikarenakan area range yang dihasilkan adalah sebanyak 1 (satu) area range, yaitu 1 (satu) baris saja atau 1 (satu) kolom saja.
Untuk merujuk lebih dari satu baris atau satu kolom, maka dibutuhkan alamat range yang menunjukkan dari range awal sampai range akhir yang bertipe teks.
Object Range memiliki keluwesan yang lebih tinggi daripada properti Rows atau Columns, karena bisa merujuk ke banyak area.
Object Range membutuhkan inputan berupa teks alamat range.
Properti EntireColumn atau EntireRow milik object Range dapat digunakan untuk merujuk ke seluruh range pada baris-baris atau kolom-kolom yang dibutuhkan dengan diketahui sebuah range dimasing-masing baris-baris atau kolom-kolom yang dibutuhkan.
Properti Cells akan menghasilkan seluruh range dalam worksheet.
Merujuk ke seluruh range dalam worksheet dapat juga dilakukan dengan merujuk ke seluruh range pada baris pertama lalu merujuk ke seluruh kolom yang ada, atau sebaliknya, yaitu merujuk ke seluruh range pada kolom pertama lalu merujuk ke seluruh baris yang ada.

:)

Insya Allah, pembahasan selanjutnya tentang dirty range yang sering disebut Used Range, perpotongan range, perujukan ke banyak range yang terpisah dalam sebuah variabel melalui banyak proses (Union).

BelajarVBA 011 - Range 01 (berdasar alamat range)

2013-09-10T00:25:00.001+07:00

Coretan Mr. Kid

Sudahkah kita mensyukuri apa yang kita dapatkan hari ini ?

Kegiatan VBA dalam Excel tidak pernah bisa lepas dari melakukan sesuatu terhadap range. Range adalah object yang paling pokok dalam Excel. Ketika bisa memanfaatkan range dengan baik, maka sebagian besar kegiatan otomasi ataupun pembangunan aplikasi berbasis Excel akan menjadi lebih mudah. Bahasan kali ini adalah tentang range yang akan dimulai dari hal dasarnya, yaitu tahu cara merujuk ke suatu range yang dibutuhkan. Apapun tindakan terhadap range adalah permasalahan yang bisa dipelajari lebih lanjut setelah tahu cara mendapatkan range yang dibutuhkan. Usahakan untuk mencobanya secara langsung pada sebuah workbook ber-macro (.xlsm) disebuah module umum.

Mendapatkan range dialamat tertentu

Hal dasar dalam merujuk suatu range adalah mengetahui ada dimana range yang dibutuhkan tersebut. Yang paling sering adalah merujuk range yang sudah diketahui alamatnya, seperti range di B2 atau di D4 sampai G9 dan sebagainya. Jika sudah diketahui alamatnya, maka alamat tersebut disusun berupa data teks. Jika ditulis langsung, maka pasti harus diapit karakter tanda petik dua ("). Jika disimpan dalam sebuah variabel, maka variabelnya harus ber-datatype String.

Untuk bahasan pertama ini, seluruh script akan diletakkan pada sebuah prosedur bernama RangeAlamatTertentu di sebuah module umum. Bunyi blok prosedur tersebut adalah :
Public Sub RangeAlamatTertentu()
    Dim rng As Range
    Dim sAlamat As String, lBaris As Long, sKolom As String
    Dim sAlamat1 As String, lBaris1 As Long, sKolom1 As String
    Dim sAlamat2 As String, lBaris2 As Long, sKolom2 As String
    Dim sAlamatDari As String, sAlamatSampai As String
    Dim lBarisDari As Long, lBarisSampai As Long
    Dim sKolomDari As String, sKolomSampai As String

    'baris kode akan dimulai dari baris kosong setelah baris ini

End Sub

Beberapa cara merujuk range berdasar alamat yang sudah diketahui :
Sebuah range dengan alamat B2
'>> alamat B2 saja
'langsung
Set rng = Range("B2")           'simpan di variabel bernama rng
rng.Value = "Be-Excel"        'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

'pakai variabel alamat
sAlamat = "B2"                'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"        'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

'nomor baris divariabel tertentu
lBaris = 2                    'simpan nomor baris di variabel bernama lBaris
sAlamat = "B" & lBaris        'susun alamat berbunyi B2 dan disimpan di sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"        'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

'nama kolom divariabel tertentu
sKolom = "B"                    'simpan nama kolom di variabel bernama sKolom
sAlamat = sKolom & 2            'susun alamat berbunyi B2 dan disimpan di sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"        'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

'nomor baris dan nama kolom divariabel tertentu
lBaris = 2                    'simpan nomor baris di variabel bernama lBaris
sKolom = "B"                    'simpan nama kolom di variabel bernama sKolom
sAlamat = sKolom & lBaris       'susun alamat berbunyi B2 dan disimpan di sAlamat
Set rng = Range(sAlamat)        'simpan di variabel bernama rng
rng.Value = "Be-Excel"        'tulis ke range
rng.Copy                        'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address   'pesan bahwa range terpilih sudah ter-copy

Dari contoh script diatas tampak bahwa kegiatan yang utama adalah mendapatkan alamat range yang dibutuhkan. Apapun informasi yang sudah dimiliki, harus bisa disusun menjadi alamat range yang dibutuhkan, meski harus memprosesnya lebih dulu seperti penyusunan alamat pada baris-baris kode sAlamat = blablabla.

Beberapa range yang terpisah, yaitu K2 dan M2
Alamat range disusun dengan menggabungkan seluruh alamat yang dibutuhkan dengan dipisah karakter koma (,). Untuk merujuk ke range K2 dan M2, maka teks alamat harus tersusun berbunyi "K2,M2".
'>> alamat K2 dan M2
'langsung
Set rng = Range("K2,M2")            'simpan di variabel bernama rng
rng.Value = "Be-Excel"              'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

'pakai variabel alamat
sAlamat = "K2,M2"                   'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)          'simpan di variabel bernama rng
rng.Value = "Be-Excel"              'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

'alamatnya didapat dari 2 variabel berbeda
sAlamat1 = "K2"                     'simpan alamat1 di variabel bernama sAlamat1
sAlamat2 = "M2"                     'simpan alamat2 di variabel bernama sAlamat2
sAlamat = sAlamat1 & "," & sAlamat2 'susun alamat lengkap dan simpan di sAlamat
Set rng = Range(sAlamat)            'simpan di variabel bernama rng
rng.Value = "Be-Excel"            'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

'semua alamatnya berupa elemen penyusun
lBaris1 = 2                         'simpan baris alamat1 di lBaris1
sKolom1 = "K"                       'simpan kolom alamat1 di sKolom1
sAlamat1 = sKolom1 & lBaris1        'susun alamat1 dan simpan di sAlamat1
lBaris2 = 2                         'simpan baris alamat2 di lBaris2
sKolom2 = "M"                       'simpan kolom alamat2 di sKolom2
sAlamat2 = sKolom2 & lBaris2        'susun alamat2 dan simpan di sAlamat2
sAlamat = sAlamat1 & "," & sAlamat2 'susun alamat lengkap dan simpan di sAlamat
Set rng = Range(sAlamat)            'simpan di variabel bernama rng
rng.Value = "Be-Excel"            'tulis ke range
rng.Copy                            'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address       'pesan bahwa range terpilih sudah ter-copy

Dari contoh script diatas, apapun proses untuk mendapatkan masing-masing alamat, tetap saja harus ada sebuah proses yang memastikan bahwa ada alamat yang berbunyi "K2,M2" alias adanya karakter koma yang menjadi delimiter antar alamat range yang dibutuhkan. Penyusunan alamat yang memasang karakter koma tersebut adalah pada baris kode berbunyi :
   sAlamat = sAlamat1 & "," & sAlamat2
yaitu teks alamat pertama digabungkan dengan karakter koma dan digabungkan juga dengan teks alamat kedua.

Dari range pertama sampai range kedua, yaitu D2 sampai G9
Alamat range disusun dengan menggabungkan seluruh alamat yang dibutuhkan dengan dipisah karakter titik dua (:). Untuk merujuk ke range D2 sampai G9, maka teks alamat harus tersusun berbunyi "D2:G9".
'>> alamat D2 sampai G9
'langsung
Set rng = Range("D2:G9")         'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
rng.Copy                         'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address    'pesan bahwa range terpilih sudah ter-copy

'pakai variabel alamat
sAlamat = "D2:G9"                'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)         'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
rng.Copy                         'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address    'pesan bahwa range terpilih sudah ter-copy

'alamatnya didapat dari 2 variabel berbeda
sAlamatDari = "D2"                        'simpan dari di sAlamatDari
sAlamatSampai = "G9"                        'simpan sampai di sAlamatSampai
sAlamat = sAlamatDari & ":" & sAlamatSampai 'susunan lengkap, simpan di sAlamat
Set rng = Range(sAlamat)                    'simpan di variabel bernama rng
rng.Value = "Be-Excel"                    'tulis ke range
rng.Copy                                    'copy range terpilih ke clipboard
MsgBox "Range " & rng.Address               'pesan sudah ter-copy

Dari contoh script diatas, Penyusunan alamat yang memasang karakter titik dua adalah pada baris kode berbunyi :
   sAlamat = sAlamat1 & ":" & sAlamat2
yaitu teks alamat pertama digabungkan dengan karakter titik dua dan digabungkan juga dengan teks alamat kedua. Sampai disini, tentu sudah bisa menyusun alamat range yang data penyusun setiap alamatnya terpisah-pisah seperti dari kolom D baris 2 sampai kolom G baris 9. Silakan dicoba.

Beberapa range tunggal terpisah dan sebuah area range, yaitu B10 dan dari C13 sampai L27
Alamat range disusun dengan menggabungkan kedua bentuk contoh diatas. Alamat range B10 digabungkan dengan alamat range dari C13 sampai L27 dengan karakter pemisah berupa karakter koma (,). Alamat range C13 sampai L27 itu sendiri disusun dengan menggabungkan alamat C13 dan L27 dengan karakter pemisah berupa karakter titik dua (:). Jadi alamat lengkapnya akan berupa teks berbunyi "B10,C13:L27".
'>> alamat B10 dan dari C13 sampai L27
'langsung
Set rng = Range("B10,C13:L27")   'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
MsgBox "Range " & rng.Address    'pesan alamat range terpilih

'pakai variabel alamat
sAlamat = "B10,C13:L27"        'simpan alamat di variabel bernama sAlamat
Set rng = Range(sAlamat)         'simpan di variabel bernama rng
rng.Value = "Be-Excel"           'tulis ke range
MsgBox "Range " & rng.Address    'pesan alamat range terpilih

'alamatnya didapat dari variabel-variabel yang berbeda
sAlamat1 = "B10"                             'simpan alamat1 di sAlamat1
sAlamatDari = "C13"                        'simpan dari di sAlamatDari
sAlamatSampai = "L27"                        'simpan sampai di sAlamatSampai
sAlamat2 = sAlamatDari & ":" & sAlamatSampai 'susun alamat2 di sAlamat2
sAlamat = sAlamat1 & "," & sAlamat2        'susun lengkap di sAlamat
Set rng = Range(sAlamat)                     'simpan di variabel bernama rng
rng.Value = "Be-Excel"                       'tulis ke range
MsgBox "Range " & rng.Address                'pesan alamat range terpilih

Dari contoh diatas, tampak bahwa ada proses penyusunan alamat yang dari suatu range sampai range lainnya dengan karakter pemisah adalah karakter titik dua (:). Kemudian alamat lengkap disusun dengan menggabungkan alamat pertama dengan hasil penyusunan alamat kedua dengan karakter pemisah adalah karakter koma (,).

Pada contoh ini juga tidak ada proses meng-Copy range terpilih ke Clipboard. Hal ini disebabkan karena range terpilih membentuk 2 (dua) area range yang tidak memiliki jumlah baris atau jumlah kolom yang sama setiap areanya. Selain itu, juga tidak memiliki nomor baris atau lokasi kolom pojok kiri atas yang sama disetiap areanya. Area pertama adalah area range 1 baris x 1 kolom di range B10. Area kedua adalah C13:L27 yang terdiri dari 15 baris x 10 kolom. Area pertama tidak dimulai (pojok kiri atasnya) di baris 13 atau di kolom C. Jika area pertama membentu area dengan dimensi N baris x 10 kolom dengan pojok kiri atas area di kolom C atau 15 baris x N kolom dengan pojok kiri atas area di baris 13, maka proses Copy terhadap range terpilih dapat dilakukan dan tidak menimbulkan error.

Sampai disini, tentu sudah bisa menyusun alamat range yang setiap data penyusunnya terpisah seperti kolom A baris 10 dan dari kolom C baris 13 sampai kolom L baris 27. Silakan dicoba.

Simpulan

Merujuk ke suatu range berdasar alamatnya cenderung membutuhkan pemahaman bekerja dengan datatype String. Pemahaman untuk bekerja dengan datatype String digunakan untuk menyusun secara detil dan tepat alamat range yang akan dirujuk atau yang dibutuhkan. Pada umumnya, dengan proses setahap demi setahap yang membutuhkan banyak variabel seperti pada contoh-contoh diatas, akan memudahkan menyusun alur proses penyusunannya. Hal ini akan mengurangi kemungkinan munculnya kesalahan dalam menyusun baris kode. Biasanya, dengan latihan terus menerus akan tiba juga untuk bisa bekerja dengan datatype String dalam menyusun bentukan teks tertentu tanpa menggunakan banyak variabel.

Bahasan diatas bisa jadi terasa terlalu dasar dan tampak sepele. Tetapi, bahasan diatas adalah konsep dasar tentang merujuk suatu range. Semoga bahasan diatas bisa mudah dipahami, sehingga akan memudahkan pemahaman pada pembahasan-pembahasan berikutnya.

:)

Insya Allah bahasan berikutnya adalah tentang merujuk ke range diseluruh baris atau diseluruh kolom.

BelajarVBA 010 - Perulangan 3 : FOR EACH dan keluar dari loop

2013-09-01T12:57:00.000+07:00

Coretan Mr. Kid

Bermewah-mewahan dan tidak memerangi kebodohan diri akan membuat seseorang menjadi pecinta dunia dan melupakan bahwa kelak dia akan mempertanggungjawabkan segala perbuatannya

Perulangan (loop) berupa blok FOR ... NEXT dan DO ... LOOP telah dibahas pada episode sebelumnya. Pada umumnya, proses loop yang telah dibahas tersebut digunakan untuk melakukan loop terhadap suatu nilai dari suatu kondisi awal sampai kondisi akhir yang berupa nilai numerik. Penggunaan blok loop seperti FOR ... NEXT atau DO ... LOOP terhadap suatu array adalah dengan melakukan loop terhadap indeks elemen array. Sedangkan loop pada suatu object collection dilakukan terhadap indeks koleksinya.

Pembahasan kali ini adalah tentang blok FOR EACH yang dapat digunakan untuk melakukan loop terhadap setiap item arrayatau setiap item object collection. Misalnya melakukan loop terhadap setiap range pada suatu kumpulan range tertentu atau melakukan loop terhadap setiap worksheet yang ada di dalam workbook. Selain itu, akan dibahas pula tentang statement dan penyusunan baris kode agar proses loop berhenti ketika memang dikehendaki untuk berhenti pada saat nilai loop tersebut.

Bentuk dasar FOR EACH

Perulangan yang menggunakan statement FOR EACH akan melakukan perulangan dengan mengambil setiap elemen suatu grup data. Grup data bisa berupa array ataupun object collection. Jadi pada perulangan ini, nilai loop yang diproses adalah elemen suatu grup itu sendiri. Jika grup data berupa object collection, maka nilai loop adalah setiap item object dari object colletion tersebut.

Syntax :
   FOR EACH var_item IN grup_data
      'baris kode yang di-loop untuk setiap var_item
   NEXT var_item

Hal yang perlu diperhatikan :

var_item ber-datatype Variant jika grup_data adalah suatu array
var_item ber-datatype Variant, Object atau nama class object dari item dalam grup data jika grup_data adalah suatu object collection
minimal ada sebuah elemen atau item didalam grup_data

Contoh 1 :
Menampilkan nilai setiap kode dari sebuah daftar kode yang berisi nilai-nilai kode 10,15, dan 20. Pada kasus ini, dapat diketahui bahwa ada sebuah daftar kode yang berisi 3 buah nilai kode, yaitu 10,15, dan 20. Daftar ini berupa array. Proses loop dilakukan untuk mengambil setiap nilai kode dalam daftar tersebut. Kemudian memprosesnya, dimana proses tersebut adalah menampilkan kepada user. Prosedur loop untuk kasus ini dapat berbunyi :
   Public Sub Contoh1_ForEachNext()
      Dim vKode As Variant

      For Each vKode In Array(10, 15, 20)
       MsgBox vKode
      Next vKode
   End Sub
Proses yang terjadi pada prosedur di atas adalah :

Deklarasi variabel item loop bernama vKode yang akan menampung salah satu elemen dari daftar kode. Karena data akan berupa array, maka dipilihlah datatype Variant untuk variabel vKode.
Proses loop dimulai dengan mendefinisikan variabel penampung setiap elemen array [vKode] dan mendefinisikan grup data yang akan di-loop [Array(10, 15, 20)] pada baris berbunyi :

For Each vKode In Array(10, 15, 20)

Baris tersebut memiliki arti bahwa setiap elemen dari Array(10, 15, 20) diproses satu persatu dengan meletakkannya di wadah bernama vKode
Proses loop pertama akan membuat vKode berisi elemen array pertama yang bernilai 10
Kemudian proses dalam loop dikerjakan, yaitu baris kode berbunyi :

MsgBox vKode

Setelah itu, akan beralih ke elemen array berikutnya saat proses mencapai baris kode berbunyi :

Next vKode

Karena masih ada elemen array yang belum pernah dimasukkan ke dalam vKode, maka terjadilah pengambilan elemen array tersebut dan memasukkannya ke dalam vKode. Jadi, vKode akan berubah isinya menjadi elemen array kedua yang bernilai 15.
Baris kode dalam blok loop kembali dikerjakan, yaitu menampilkan nilai vKode kepada user melalui baris kode :

MsgBox vKode

Proses akan berlangsung demikian terus menerus sampai seluruh elemen array pernah diambil dan dimasukkan ke dalam variabel vKode
Ketika seluruh elemen array pernah diambil dan dimasukkan ke dalam vKode, maka proses loop selesai atau keluar dari blok loop
Proses akan beralih ke baris kode berikutnya. Ternyata baris kode berikutnya adalah akhir blok prosedur (END SUB), maka prosedur selesai dikerjakan

Contoh 2 :
Menampilkan nama worksheet dari setiap worksheet yang ada di dalam workbook. Pada kasus ini dapat diketahui bahwa grup data adalah berupa object collection, yaitu kumpulan worksheet dalam workbook. Setiap item dalam collection tersebut memiliki datatype object Worksheet. Jadi, variabel item dapat menggunakan datatype Worksheet. Prosedur untuk kasus ini dapat berbunyi :
   Public Sub Contoh2_ForEachNext()
      Dim sht As Worksheet

      For Each sht In ThisWorkbook.Worksheets
       MsgBox sht.Name
      Next sht
   End Sub
Proses yang terjadi pada prosedur di atas adalah :

Deklarasi variabel item bernama sht yang ber-datatype Worksheet karena grup data berupa object collection yang berisi object worksheet
Proses loop dimulai dengan mendefinisikan variabel penampung setiap item object collection [sht] dan mendefinisikan grup data yang akan di-loop [ThisWorkbook.Worksheets] pada baris berbunyi :

For Each sht In ThisWorkbook.Worksheets

Baris tersebut memiliki arti bahwa setiap item Worksheet (karena datatype sht adalah Worksheet) dari kumpulan worksheet dalam workbook diproses satu persatu dengan meletakkannya di wadah bernama sht
Proses loop pertama akan membuat sht berisi object worksheet dengan indeks 1 yang ada dalam workbook
Kemudian proses dalam loop, yang berupa menampilkan nama worksheet, dikerjakan. Proses tersebut adalah baris kode berbunyi :

MsgBox sht.Name

Setelah itu, akan beralih ke item worksheet berikutnya saat proses mencapai baris kode berbunyi :

Next sht

Jika masih ada item worksheet yang belum pernah dimasukkan ke dalam sht, maka terjadilah pengambilan item worksheet tersebut dan memasukkannya ke dalam sht. Jadi, sht akan berubah isinya menjadi object worksheet yang memiliki indeks 2 yang ada dalam workbook.
Baris kode dalam blok loop kembali dikerjakan, yaitu menampilkan nama worksheet kepada user melalui baris kode :

MsgBox sht.Name

Proses akan berlangsung demikian terus menerus sampai seluruh item dalam object collection worksheet pernah diambil dan dimasukkan ke dalam variabel sht
Ketika seluruh item worksheet dalam object collection worksheet pernah diambil dan dimasukkan ke dalam sht, maka proses loop selesai atau keluar dari blok loop
Proses akan beralih ke baris kode berikutnya. Ternyata baris kode berikutnya adalah akhir blok prosedur (END SUB), maka prosedur selesai dikerjakan

Nested For Each

Seperti halnya blok loop lainnya, blok loop dengan FOR EACH juga bisa berada dalam blok loop lainnya, termasuk dalam blok loop dengan FOR EACH lainnya.

Bentuk dasarnya adalah :
   FOR EACH var_item1 IN grup_data1
'baris kode yang di-loop untuk setiap var_item1
FOR EACH var_item2 IN grup_data2
       'baris kode yang di-loop untuk setiap var_item2
      NEXT var_item2
      'baris kode yang di-loop untuk setiap var_item1
   NEXT var_item1
Kaidah pemilihan datatype setiap var_item disesuaikan dengan grup_data masing-masing.

Contoh :
Menuliskan kombinasi nomor urut dan nomor kode. Nomor urut dari nomor 1 sampai 5. Nomor kode adalah 10, 15, dan 20. Setiap nomor urut akan mendapatkan setiap nomor kode. Ditulis pada sheet bernama 'Dataku' mulai baris 2, dengan nomor urut ditulis di kolom A (kolom ke-1 dalam sheet Dataku) dan nomor kode ditulis di kolom B (kolom ke-2 dalam sheet Dataku).

Kasus ini akan diselesaikan dengan FOR EACH seluruhnya, meskipun bisa dilakukan dengan blok loop lainnya. Pada kasus ini dapat diketahui bahwa :

Untuk nomor urut, akan ada grup data berupa array berisi angka 1 sampai 5
Setiap elemen array nomor urut akan dimasukkan ke wadah ber-datatype Variant yang diberi nama vNomor
Untuk nomor kode, akan ada grup data berupa array berisi angka-angka 10, 15, dan 20
Setiap elemen array nomor kode akan dimasukkan ke wadah ber-datatype Variant yang diberi nama vKode
Penulisan dilakukan ke sheet bernama Dataku mulai baris 2
Penulisan dimulai dari baris 2. Artinya dibutuhkan sebuah variabel penyimpan posisi penulisan, misalnya bernama lBaris dan akan dimulai dari nilai 2
Setiap nomor urut akan mendapatkan setiap nomor kode. Artinya bahwa loop terhadap array nomor kode ada di dalam loop terhadap array nomor urut
Penulisan nomor urut dan nomor kode dilakukan setiap pembacaan elemen array nomor kode atau di dalam blok loop terhadap array nomor kode.
Nilai lBaris akan bertambah 1 setiap selesai menuliskan nomor kode
Baris kode penambahan lBaris tersebut diletakkan dalam blok loop terhadap array nomor kode

Prosedur untuk contoh kasus ini dapat berbunyi :
   Public Sub Contoh1_NestedForEach()
Dim vNomor As Variant, vKode As Variant
      Dim lBaris As Long

      lBaris = 2
      For Each vNomor In Array(1, 2, 3, 4, 5)

       For Each vKode In Array(10, 15, 20)
            Sheets("Dataku").Cells(lBaris, 1).Value = vNomor
            Sheets("Dataku").Cells(lBaris, 2).Value = vKode

            lBaris = lBaris + 1
       Next vKode

    Next vNomor
   End Sub

Keluar dari loop

Seringkali terjadi bahwa sebuah loop akan diarahkan untuk segera selesai atau proses segera keluar dari loop tanpa perlu menyelesaikannya sampai dengan item terakhir yang telah didefinisikan dalam baris definisi loop. Misalkan saja ketika melakukan loop terhadap suatu area range dan proses loop akan dihentikan ketika range tidak berisi suatu nilai. Pada dasarnya, ketika nilai yang di-loop tidak memenuhi ekspresi kondisi batas loop maka proses loop akan selesai atau dihentikan. Jadi, dengan mengubah nilai yang di-loop menjadi tidak memenuhi ekspresi kondisi adalah salah satu cara untuk keluar dari loop. Saat ini, cara ini umumnya digunakan hanya ketika menggunakan blok loop WHILE ... WEND. Blok loop FOR ... NEXT, FOR EACH, dan DO ... LOOP juga bisa menggunakan cara ini jika dikehendaki.

Khusus untuk blok loop FOR ... NEXT, FOR EACH dan DO ... LOOP dapat juga menggunakan statement yang sudah disediakan oleh VB/VBA, yaitu :

EXIT FOR untuk keluar dari blok loop yang menggunakan FOR (FOR ... NEXT atau FOR EACH)
EXIT DO untuk keluar dari blok loop yang menggunakan DO ... LOOP, meskipun pada beberapa kasus, batasan harus keluar dari loop bisa dijadikan salah satu elemen penyusun ekspresi kondisi dengan memanfaatkan statement logika seperti AND atau OR

Pada blok loop yang bersarang (nested loop), maka EXIT FOR atau EXIT DO akan keluar dari blok loop di level tersebut. Misal, EXIT FOR terletak pada blok loop dengan FOR ... NEXT yang ada di dalam blok loop dengan DO ... LOOP. Maka EXIT FOR akan membuat proses loop keluar dari blok loop dengan FOR ... NEXT. Meski demikian, proses loop pada blok DO ... LOOP masih bekerja karena blok loop ini berada dilevel lebih atas daripada blook loop dengan FOR ... NEXT.

Contoh 1 :
Menampilkan hasil penjumlahan nilai 1 sampai suatu nilai akhir tertentu yang tertinggi atau senilai 17. Prosedur loop untuk kasus ini bisa berbunyi :
1. Blok loop FOR ... NEXT

Menggunakan EXIT FOR

     Public Sub Contoh1_KeluarExitFor()
        Dim lNomor As Long, lTotal As Long, lAkhir As Long

        lAkhir = 5
        For lNomor = 1 To lAkhir
           lTotal = lTotal + lNomor
           If lTotal >= 17 Then
              lTotal = lTotal - lNomor
              Exit For
           End If
        Next lNomor
        MsgBox "Berhenti di lNomor = " & lNomor - 1 & vbCrLf & _
            "dengan lTotal = " & lTotal _
            , vbExclamation
     End Sub

Mengubah nilai yang di-loop

     Public Sub Contoh1_KeluarSetNilai()
        Dim lNomor As Long, lNomorReal As Long
        Dim lTotal As Long, lAkhir As Long

        lAkhir = 5
        For lNomor = 1 To lAkhir
       lTotal = lTotal + lNomor
           lNomorReal = lNomor
           If lTotal >= 17 Then
              lTotal = lTotal - lNomor
              lNomorReal = lNomor - 1
              lNomor = lAkhir
           End If
        Next lNomor
      MsgBox "Berhenti di lNomor = " & lNomorReal & vbCrLf & _
            "dengan lTotal = " & lTotal _
            , vbExclamation
     End Sub
Pada kedua prosedur di atas, proses loop akan berhenti ketika lTotal telah mencapai nilai 17 atau lebih atau ketika batas akhir loop tercapai. Kemudian menampilkan hasil penjumlahan kepada user menggunakan sebuah kotak pesan.
Perbedaan antara kedua prosedur adalah :

penggunaan baris EXIT FOR pada prosedur bernama Contoh1_KeluarExitFor dan menggunakan baris kode pengubah nilai loop menjadi nilai diluar area kerja loop yang berbunyi lNomor = lAkhir.
Nilai nomor urut saat nilai total tercapai dapat dihitung dengan pasti pada prosedur Contoh1_KeluarExitFor, yaitu dengan mengurangkannya dengan nilai interval yang diletakkan setelah statement STEP. Pada kasus ini, intervalnya menggunakan nilai default, yaitu 1, karena tidak ada statement STEP dalam prosedur.
Pada prosedur bernama Contoh1_KeluarSetNilai dibutuhkan sebuah variabel tambahan bernama lNomorReal untuk menyimpan nilai nomor urut untuk nilai total yang sedang dihasilkan, karena nilai lNomor akan diubah menjadi lAkhir agar proses loop bisa terhenti ketika batas nilai total telah tercapai.
Penambahan baris-baris kode untuk menentukan nilai lNomorReal akan menambah waktu proses yang akan terasa signifikan ketika melakukan loop terhadap data yang sangat banyak.

2. Blok loop DO ... LOOP

Menggunakan EXIT DO

     Public Sub Contoh2_KeluarExitDo()
Dim lNomor As Long, lAkhir As Long
Dim lTotal As Long

      lNomor = 1
lAkhir = 5
Do While lNomor <= lAkhir
       lTotal = lTotal + lNomor
       If lTotal >= 17 Then
              lTotal = lTotal - lNomor
              Exit Do
       End If
     lNomor = lNomor + 1
      Loop
      MsgBox "Berhenti di lNomor = " & lNomor - 1 & vbCrLf & _
            "dengan lTotal = " & lTotal _
            , vbExclamation
     End Sub

Mengubah nilai yang di-loop

     Public Sub Contoh2_KeluarSetNilai()
      Dim lNomor As Long, lNomorReal As Long
      Dim lTotal As Long, lAkhir As Long

lNomor = 1
lAkhir = 5
Do While lNomor <= lAkhir
       lTotal = lTotal + lNomor
       lNomorReal = lNomor
       If lTotal >= 17 Then
              lTotal = lTotal - lNomor
              lNomorReal = lNomor - 1
              lNomor = lAkhir
     End If
     lNomor = lNomor + 1
Loop
      MsgBox "Berhenti di lNomor = " & lNomorReal & vbCrLf & _
            "dengan lTotal = " & lTotal _
            , vbExclamation
     End Sub

Memasukkan batasan keluar kedalam ekspresi kondisi

     Public Sub Contoh2_KeluarEkspresiKondisi()
        Dim lNomor As Long, lNomorReal As Long
      Dim lTotal As Long, lAkhir As Long

      lNomor = 1
      lAkhir = 5
      Do While lNomor <= lAkhir And lTotal + lNomor <= 17
       lTotal = lTotal + lNomor
         lNomor = lNomor + 1
Loop
MsgBox "Berhenti di lNomor = " & lNomor - 1 & vbCrLf & _
            "dengan lTotal = " & lTotal _
            , vbExclamation
     End Sub
Perbedaan antara prosedur Contoh2_KeluarExitDo dengan Contoh2_KeluarSetNilai sama dengan perbedaan antara prosedur Contoh1_KeluarExitFor dengan Contoh2_KeluarSetNilai. Hanya saja, statement yang digunakan adalah EXIT DO dan bukan EXIT FOR. Pada prosedur Contoh2_KeluarEkspresiKondisi, batasan keluar dari loop dimasukkan ke dalam ekspresi kondisi. Batasan keluar loop adalah ketika nilai total ditambahkan nilai nomor yang baru masih di dalam batas nilai total yang ditentukan (17). Kondisi kerja loop juga dipengaruhi nilai nomor yang kurang dari atau sama dengan nilai akhir (lAkhir). Penggunaan statement logika AND untuk menghubungkan kedua kondisi tersebut disebabkan karena seluruh kondisi harus terpenuhi. Ekspresi kondisi yang terbentuk adalah :
   lNomor <= lAkhir And lTotal + lNomor <= 17
Blok loop yang digunakan untuk ekspresi kondisi di atas adalah DO WHILE

3. Blok loop WHILE ... WEND
     Public Sub Contoh3_KeluarWhileWend()
      Dim lNomor As Long, lNomorReal As Long
Dim lTotal As Long, lAkhir As Long

lNomor = 1
lAkhir = 5
While lNomor <= lAkhir And lTotal + lNomor <= 17
     lTotal = lTotal + lNomor
     lNomor = lNomor + 1
Wend
MsgBox "Berhenti di lNomor = " & lNomor - 1 & vbCrLf & _
            "dengan lTotal = " & lTotal _
            , vbExclamation
     End Sub
Prosedur Contoh3_KeluarWhileWend mirip dengan prosedur bernama Contoh2_KeluarEkspresiKondisi. Hanya berbeda jenis blok loop yang digunakan, yaitu WHILE ... WEND pada prosedur Contoh3_KeluarWhileWend dan DO WHILE ... LOOP pada prosedur Contoh2_KeluarEkspresiKondisi.

Contoh 2 :
Menampilkan hasil penjumlahan dari nilai-nilai yang ada di sheet bernama Dataku pada kolom Q mulai baris 1 sampai suatu cell di kolom Q tersebut adalah kosong. Prosedur loop untuk kasus ini bisa berbunyi :
   Public Sub Contoh4_KeluarForEach()
      Dim rng As Range, lTotal As Long

      For Each rng In Sheets("Dataku").Range("Q:Q")
       If Len(rng.Value) = 0 Then
            Exit For
     End If
     lTotal = lTotal + rng.Value
      Next rng
      MsgBox "lTotal = " & lTotal & vbCrLf & _
            "terakhir di baris " & rng.Row - 1 _
            , vbExclamation
   End Sub
Pada prosedur di atas, pemeriksaan tentang keberadaan isi suatu cell adalah dengan membandingkan bahwa jumlah karakter (LEN) di cell tersebut adalah 0. Hal ini dilakukan pada baris :
   If Len(rng.Value) = 0 Then
Ketika hasil pemeriksaannya adalah TRUE alias panjang karakter cell tersebut adalah 0, maka proses akan masuk ke dalam blok IF yang berisi baris kode untuk keluar blok FOR yang berbunyi :
   Exit For

Simpulan

Penggunaan FOR EACH bisa jadi akan lebih lambat dibandingkan penggunaan DO ... LOOP atau FOR ... NEXT pada kondisi-kondisi tertentu. Penggunaan grup data yang bisa diisi dengan data-data pilihan saja adalah salah satu yang membuat FOR EACH terasa begitu praktis dan efisien. Contohnya adalah pada hasil autofilter suatu tabel yang menghasilkan data yang tidak kontinyu baris-baris Excel-nya akan mudah dijadikan sebagai sebuah grup data, sehingga proses loop yang harus dilakukan pada hasil autofilter tersebut akan lebih sederhana jika menggunakan FOR EACH.

:)

Insya Allah pembahasan selanjutnya adalah tentang dasar-dasar dalam menggunakan object range. Hal ini mencakup tentang pemahaman LastCell, CurrentRegion, Offset, Resize, End xlUp atau End xlDown, dan sebagainya. Dasar-dasar dalam menggunakan object range akan memudahkan dalam mengolah data menggunakan VBA, karena menyusun rujukan yang dinamis membutuhkan pemahaman tentang cara menggunakan object range. Contohnya adalah menentukan batas bawah tabel data yang ada atau menentukan baris kosong pertama yang ada disebuah tabel data berkaidah database.

BelajarVBA 010 - Perulangan 2 : DO ... LOOP

2013-08-29T21:15:00.002+07:00

Coretan Mr. Kid

Seorang petani yang ingin memanen buah tomat harus menamam tanaman tomat dengan bibit yang baik kemudian merawatnya dengan sungguh-sungguh agar Tuhan berkenan menumbuhkan buah tomat kualitas super. Apakah yang ingin Anda panen kelak ? Sudahkah Anda menanamnya saat ini ? Jika sudah menanamnya, apakah sudah Anda rawat dengan sungguh-sungguh ?

Pada pembahasan yang lalu telah dibahas tentang perulangan menggunakan statement FOR ... NEXT. Blok FOR ... NEXT yang telah dibahas adalah yang bekerja berdasar nilai batas awal dan batas akhir dengan interval lompatan antar nilai yang diproses yang tertentu dalam susunan awal TO akhir STEP interval. Jadi dibutuhkan setidaknya 2 (dua) nilai sebagai nilai batas dan 1 (satu) nilai interval dengan default adalah 1. Kali ini pembahasan akan menggunakan DO ... LOOP disertai pengenalan penggunaan statement WHILE ... WEND.

Bentuk dasar DO ... LOOP

Pembahasan kali ini adalah tentang perulangan memanfaatkan statement DO ... LOOP. Perulangan ini membentuk blok DO ... LOOP yang membutuhkan ekspresi kondisi sebagai pembatas proses dan penentuan nilai yang diproses didalam blok tersebut. Ekspresi pembatas didefinisikan bersama statement WHILE atau UNTIL. Blok DO ... LOOP berdasar letak pendefinisian ekspresi batas memiliki 2 (dua) macam, yaitu :

Ekspresi batas di baris DO, yaitu memulai proses perulangan ketika benar-benar telah terpenuhi kondisi batasnya
Ekspresi batas di baris LOOP, yaitu memproses perulangan setidaknya sekali sebelum memeriksa kondisi batasnya

Syntax :

Ekspresi batas di baris DO

     DO WHILE|UNTIL ekspresi_kondisi_batas
        'baris kode yang diulang
     LOOP

Ekspresi batas di baris LOOP

     DO
        'baris kode yang diulang
     LOOP WHILE|UNTIL ekspresi_kondisi_batas

Statement WHILE atau UNTIL dipilih sesuai kebutuhan, yaitu :

Statement WHILE membutuhkan ekspresi kondisi batas terakhir dari nilai yang boleh diproses.
Statement UNTIL membutuhkan ekspresi kondisi batas pertama dari nilai yang diluar area nilai yang boleh diproses.

Contohnya adalah membuat nomor urut dari 3 sampai 8 dan ditulis di sheet bernama 'Dataku' mulai baris 5 secara berurutan. Dari definisi kasusnya, diketahui bahwa :

nilai batas awal adalah 3, yang kemudian disimpan dalam variabel tertentu, misalnya bernama lNomor.
nilai terakhir adalah 8, yang akan membentuk ekspresi kondisi batas untuk lNomor
ketika akan menggunakan stetement WHILE, maka lNomor selalu kurang dari atau sama dengan 8 yang berbunyi WHILE lNomor <= 8
ketika akan menggunakan statement UNTIL, maka lNomor pertama yang diluar batas nilai yang boleh diproses adalah ketika lNomor lebih dari 8 yang berbunyi UNTIL lNomor > 8
interval nomor urut yang berurutan berarti adalah selalu bertambah 1, sehingga dibutuhkan baris kode untuk mengubah nilai lNomor menjadi baru yang didapat dari penambahan nilai 1 terhadap nilai lNomor yang lama yang berbunyi lNomor = lNomor + 1
penulisan pada sheet bernama 'Dataku'
penulisan dimulai dari baris 5, yang berarti nomor barisnya akan selalu berupa lNomor + 2 karena lNomor dimulai dari nilai 3 dan membutuhkan penambahan 2 agar bisa mencapai nomor baris 5.

Prosedur loop untuk kasus ini adalah :
Ekspresi batas di baris DO WHILE LOOP ditulis di kolom A
   Public Sub Contoh1_DoWhileLoop()
Dim lNomor As Long, lBaris As Long

'hapus yang lama dulu ya
Sheets("Dataku").Columns(1).ClearContents

'mulai proses contoh
lNomor = 3
Do While lNomor <= 8
     lBaris = lNomor + 2
     Sheets("Dataku").Range("A" & lBaris).Value = lNomor
     lNomor = lNomor + 1
Loop
   End Sub

Proses yang terjadi adalah :

Deklarasi variabel nomor urut (lNomor) ber-type Long dan nomor baris penulisan (lBaris) ber-type Long.
Menghapus isi data dikolom penulisan
Menetapkan nilai awal nomor baris adalah 3
Memulai blok loop dengan membawa nilai lNomor bernilai 3, dan memeriksa kondisi batas adalah nomor urut selalu kurang dari atau sama dengan 8 pada baris kode :

Do While lNomor <= 8

Karena lNomor saat ini bernilai 3, berarti lNomor memenuhi kondisi batas. Proses akan dilanjutkan ke baris-baris kode dalam blok DO ... LOOP
Mendapatkan nomor baris penulisan pada loop pertama (lNomor = 3) berupa lNomor + 2 yang menghasilkan nilai 3 + 2 = 5
Menulis nilai lNomor ke sheet bernama 'Dataku' pada kolom A di baris lBaris
Mendapatkan nilai lNomor yang baru dengan menambahkan lNomor yang lama (3) dengan nilai 1, sehingga menghasilkan nilai 4
Mengulangi proses blok loop, yaitu kembali ke baris DO, dengan membawa nilai lNomor baru (4)
Proses terus berjalan, dengan nilai lNomor yang terus bertambah, hingga suatu saat nanti lNomor yang baru akan bernilai 9, dan proses kembali ke baris DO dengan membawa nilai lNomor yang baru (9) ini.
Memeriksa kondisi batas terhadap lNomor (9)
Karena nilai 9 yang ada dalam lNomor tidak memenuhi kondisi berbunyi lNomor <= 8, maka proses loop pada blok DO ... LOOP telah selesai dan proses berlanjut ke baris berikutnya seteoah blok DO ... LOOP
Baris berikutnya setelah blok DO ... LOOP adalah baris akhir prosedur (END SUB), maka prosedur selesai dikerjakan

Ekspresi batas di baris DO UNTIL LOOP ditulis di kolom C
   Public Sub Contoh1_DoUntilLoop()
      Dim lNomor As Long, lBaris As Long

      'hapus yang lama dulu ya
      Sheets("Dataku").Columns(3).ClearContents

      'mulai proses contoh
      lNomor = 3
      Do Until lNomor > 8
       lBaris = lNomor + 2
       Sheets("Dataku").Range("C" & lBaris).Value = lNomor
       lNomor = lNomor + 1
      Loop
   End Sub
Prosedur bernama Contoh1_DoUntilLoop akan dijalankan seperti prosedur bernama Contoh1_DoWhileLoop. Perbedaannya adalah pada proses pemeriksaan terhadap ekspresi kondisi. Pada penggunaan UNTIL, yang diperiksa adalah tercapainya kondisi nilai proses yang diluar nilai-nilai yang boleh diproses. Pada kasus ini, nilai-nilai yang boleh diproses adalah sebelum nilai 9. Maka proses akan dikerjakan sampai (UNTIL) mencapai kondisi bahwa nilai lNomor mencapai 9 atau lebih (lNomor > 8).

Ekspresi batas di baris DO LOOP WHILE ditulis di kolom E
   Public Sub Contoh1_DoLoopWhile()
Dim lNomor As Long, lBaris As Long

'hapus yang lama dulu ya
Sheets("Dataku").Columns(5).ClearContents

'mulai proses contoh
lNomor = 3
Do
       lBaris = lNomor + 2
       Sheets("Dataku").Range("E" & lBaris).Value = lNomor
       lNomor = lNomor + 1
Loop While lNomor <= 8
   End Sub
Prosedur bernama Contoh1_DoLoopWhile akan diproses sebagai berikut :

Deklarasi variabel nomor urut (lNomor) ber-type Long dan nomor baris penulisan (lBaris) ber-type Long.
Menghapus isi data dikolom penulisan
Menetapkan nilai awal nomor baris adalah 3
Memulai blok loop dengan membawa nilai lNomor bernilai 3, dengan menjalankan baris DO
Mendapatkan nomor baris penulisan pada loop pertama (lNomor = 3) berupa lNomor + 2 yang menghasilkan nilai 3 + 2 = 5
Menulis nilai lNomor ke sheet bernama 'Dataku' pada kolom A di baris lBaris
Mendapatkan nilai lNomor yang baru dengan menambahkan lNomor yang lama (3) dengan nilai 1, sehingga menghasilkan nilai 4
Mengulangi proses blok loop ketika hasil pemeriksaan ekspresi kondisi batasnya terpenuhi (LOOP)
Pemeriksaan nilai lNomor yang baru (4) dengan ekspresi kondisi batas yang berbunyi While lNomor <= 8
Karena lNomor yang baru (4) masih memenuhi batas kondisi, yaitu kurang dari atau sama dengan 8, maka proses akan kembali ke baris DO
Proses ini akan berlangsung terus menerus dengan nilai lNomor yang terus bertambah, sehingga suatu saat nanti, lNomor yang baru akan mencapai nilai 9
Saat lNomor yang baru bernilai 9, dilakukan pemeriksaan oleh baris kode berbunyi :

Loop While lNomor <= 8

Karena lNomor yang baru (9) tidak bisa memenuhi kondisi batas (<=8), maka proses loop oleh blok DO ... LOOP selesai dan proses dilanjutkan ke baris berikutnya setelah blok DO ... LOOP
Baris berikutnya setelah blok DO ... LOOP adalah baris akhir prosedur (END SUB), maka prosedur selesai dikerjakan.

Ekspresi batas di baris DO LOOP UNTIL ditulis di kolom G
   Public Sub Contoh1_DoLoopUntil()
Dim lNomor As Long, lBaris As Long

'hapus yang lama dulu ya
Sheets("Dataku").Columns(7).ClearContents

'mulai proses contoh
lNomor = 3
Do
         lBaris = lNomor + 2
         Sheets("Dataku").Range("G" & lBaris).Value = lNomor
         lNomor = lNomor + 1
Loop Until lNomor > 8
   End Sub
Prosedur bernama Contoh1_DoLoopUntil akan dijalankan seperti prosedur bernama Contoh1_DoLoopWhile. Perbedaannya adalah pada proses pemeriksaan terhadap ekspresi kondisi. Pada penggunaan UNTIL, yang diperiksa adalah tercapainya kondisi nilai proses yang diluar nilai-nilai yang boleh diproses. Pada kasus ini, nilai-nilai yang boleh diproses adalah sebelum nilai 9. Maka proses akan dikerjakan sampai (UNTIL) mencapai kondisi bahwa nilai lNomor mencapai 9 atau lebih (lNomor > 8).

Ekspresi kondisi yang diletakkan setelah statement DO akan memiliki alur proses yang berbeda dengan yang diletakkan setelah statement LOOP pada bagian awal proses loop-nya. Hal ini akan tampak jelas ketika lNomor di set untuk dimulai dari nilai 9 pada setiap prosedur contoh di atas. Pada ekspresi kondisi yang diletakkan setelah statement DO tidak akan menulis hasil apapun. Ekspresi kondisi yang diletakkan setelah statement LOOP akan menulis sebuah hasil. Artinya, pada ekspresi kondisi yang diletakkan setelah statement LOOP akan selalu diproses minimal 1 (satu) kali. Hal ini disebabkan karena proses pemeriksaan ekspresi kondisi dilakukan saat akan berpindah ke nilai loop berikutnya.

Nested DO ... LOOP

Blok DO ... LOOP, baik menggunakan WHILE atau UNTIL yang diletakkan pada baris DO ataupun LOOP, bisa diletakkan dalam blok DO ... LOOP apapun yang lain untuk membentuk perulangan yang bersarang (nested).

Syntax umumnya :

Ekspresi batas di baris DO

     DO WHILE|UNTIL ekspresi_kondisi_batas1
        'baris kode yang diulang dalam batas1
        DO WHILE|UNTIL ekspresi_kondisi_batas2
           'baris kode yang diulang dalam batas2
        LOOP
        'baris kode yang diulang dalam batas1
     LOOP
          atau
     DO WHILE|UNTIL ekspresi_kondisi_batas1
        'baris kode yang diulang dalam batas1
        DO
           'baris kode yang diulang dalam batas2
        LOOP WHILE|UNTIL ekspresi_kondisi_batas2
        'baris kode yang diulang dalam batas1
     LOOP

Ekspresi batas di baris LOOP

     DO
        'baris kode yang diulang dalam batas1
        DO WHILE|UNTIL ekspresi_kondisi_batas2
           'baris kode yang diulang dalam batas2
        LOOP
        'baris kode yang diulang dalam batas1
     LOOP WHILE|UNTIL ekspresi_kondisi_batas1
          atau
     DO
        'baris kode yang diulang dalam batas1
        DO
           'baris kode yang diulang dalam batas2
        LOOP WHILE|UNTIL ekspresi_kondisi_batas2
        'baris kode yang diulang dalam batas1
     LOOP WHILE|UNTIL ekspresi_kondisi_batas1

Contohnya adalah menulis nomor urut dari 3 sampai 8, dengan setiap nomor urut akan mendapat kode 10,15, dan 20 dan ditulis mulai baris 5. Kasus ini memberi informasi bahwa :

setiap nomor urut akan mendapat 3 buah kode, maka di setiap nilai nomor urut yang di proses loop akan ada proses loop pemberian kode
nomor urut diproses loop dengan interval penambahan 1, dimulai dari nomor urut 3, maksimal adalah 8, dan dengan variabel bernama lNomor
nomor kode diproses loop dengan interval penambahan 5, dimulai dari nomor kode 10, maksimal adalah 20, dan dengan variabel bernama lKode
baris penulisan selalu bertambah dengan interval 1 dan dimulai dari baris ke-5 Excel

Misalkan nomor urut akan ditulis di kolom J (kolom ke-10 sheet Dataku) dan nomor kode akan ditulis di kolom K (kolom ke-11 sheet Dataku). Blok DO ... LOOP bisa menggunakan :

WHILE dengan kondisi batas nomor urut adalah lNomor <= 8 dan kondisi batas nomor kode adalah lKode <= 20
UNTIL dengan kondisi batas nomor urut adalah lNomor > 8 dan kondisi batas nomor kode adalah lKode > 20

Kali ini blok DO ... LOOP akan menggunakan UNTIL. Penggunaan WHILE bisa Anda coba sendiri. Prosedur untuk kasus ini bisa berbunyi :
   Public Sub Contoh1_NestedDoLoopUntil()
      Dim lNomor As Long, lKode As Long, lBaris As Long

      'hapus hasil yang lama
      Sheets("Dataku").Range("j5").CurrentRegion.ClearContents

      'set nilai awal nomor urut dan baris penulisan
      lNomor = 3
lBaris = 5

      'memulai proses loop
'loop nomor urut di sisi terluar
Do

       'set nilai awal nomor kode
       'dilakukan setiap akan memulai loop nomor kode
       lKode = 10

       'loop nomor kode di dalam loop nomor urut
       'karena setiap nomor urut akan mendapat nomor kode
     Do

            'menulis data di kolom J (kolom ke-10)
            'dan kolom K (kolom ke-11)
            Sheets("Dataku").Cells(lBaris, 10).Value = lNomor
            Sheets("Dataku").Cells(lBaris, 11).Value = lKode

            'set nilai nomor kode berikutnya
            lKode = lKode + 5

            'set nomor baris penulisan berikutnya karena setiap
            'menulis kode akan bertambah baris penulisannya
            lBaris = lBaris + 1

       Loop Until lKode > 20

     'set nomor urut berikutnya
       lNomor = lNomor + 1

Loop Until lNomor > 8
   End Sub

Blok WHILE ... WEND

Proses loop dengan statement WHILE ... WEND juga akan membentuk sebuah blok loop. Blok WHILE ... WEND sama dengan blok DO WHILE ... LOOP.

Bentuk dasarnya adalah :
   WHILE ekspresi_kondisi
'baris kode yang di-loop
   WEND

Contohnya adalah prosedur bernama Contoh1_DoWhileLoop di atas dapat ditulis dalam blok WHILE ... WEND menjadi :
   Public Sub Contoh1_WhileWend()
      Dim lNomor As Long, lBaris As Long

      'hapus yang lama dulu ya
      Sheets("Dataku").Columns(13).ClearContents

      'mulai proses contoh
      lNomor = 3
      While lNomor <= 8
       lBaris = lNomor + 2
       Sheets("Dataku").Range("M" & lBaris).Value = lNomor
       lNomor = lNomor + 1
      Wend
   End Sub

Simpulan

Blok-blok loop yang telah dijabarkan bisa disusun nested terhadap blok-blok loop lain. Misalnya level 1 proses loop menggunakan blok DO ... LOOP, level 2 proses loop menggunakan WHILE ... WEND, level 3 proses loop menggunakan blok FOR ... NEXT, dan sebagainya. Contoh-contoh di atas adalah contoh sederhana. Implementasi dari penjabaran di atas dalam kasus nyata membutuhkan kejelian dalam menyusun ekspresi kondisi.

:)

Insya Allah, pembahasan selanjutnya adalah tentang loop dengan blok FOR EACH ... NEXT yang bisa digunakan untuk loop setiap elemen array atau object collections.