Konten
Bahkan jika Anda tidak tahu cara memprogram, ada banyak tempat (buku, situs web, forum) di mana Anda dapat menemukan kode makro VBA siap pakai untuk sejumlah besar tugas umum di Excel. Dalam pengalaman saya, sebagian besar pengguna cepat atau lambat mengumpulkan koleksi makro pribadi mereka untuk mengotomatisasi proses rutin, baik itu menerjemahkan rumus menjadi nilai, menampilkan jumlah dalam kata, atau menjumlahkan sel berdasarkan warna. Dan di sini muncul masalah – kode makro dalam Visual Basic perlu disimpan di suatu tempat agar dapat digunakan nanti dalam pekerjaan.
Opsi termudah adalah menyimpan kode makro langsung di file kerja dengan masuk ke editor Visual Basic menggunakan pintasan keyboard lain+F11 dan menambahkan modul kosong baru melalui menu Sisipkan – Modul:
Namun, ada beberapa kelemahan dengan metode ini:
- Jika ada banyak file yang berfungsi, dan makro diperlukan di mana-mana, seperti makro untuk mengonversi rumus menjadi nilai, maka Anda harus menyalin kode di setiap buku.
- Tidak boleh dilupakan simpan file dalam format yang mendukung makro (xlsm) atau dalam format buku biner (xlsb).
- Saat membuka file seperti itu perlindungan makro akan setiap kali mengeluarkan peringatan yang perlu diakui (baik, atau menonaktifkan perlindungan sepenuhnya, yang mungkin tidak selalu diinginkan).
Solusi yang lebih elegan adalah dengan membuat add-in Anda sendiri (Excel Add-in) – file terpisah dengan format khusus (xlam) yang berisi semua makro “favorit” Anda. Keuntungan dari pendekatan ini:
- Ini akan cukup sambungkan pengaya sekali di Excel – dan Anda dapat menggunakan prosedur dan fungsi VBA dalam file apa pun di komputer ini. Menyimpan kembali file kerja Anda dalam format xlsm- dan xlsb, dengan demikian, tidak diperlukan, karena. kode sumber tidak akan disimpan di dalamnya, tetapi di file tambahan.
- Proteksi Anda juga tidak akan terganggu oleh makro. add-on, menurut definisi, adalah sumber tepercaya.
- Bisa melakukan tab terpisah pada pita Excel dengan tombol yang bagus untuk menjalankan makro tambahan.
- Add-in adalah file terpisah. Miliknya mudah dibawa dari komputer ke komputer, berbagi dengan rekan kerja atau bahkan menjualnya
Mari kita telusuri seluruh proses pembuatan add-in Microsoft Excel Anda sendiri selangkah demi selangkah.
Langkah 1. Buat file tambahan
Buka Microsoft Excel dengan buku kerja kosong dan simpan dengan nama yang sesuai (misalnya ExcelAddin saya) dalam format tambahan dengan perintah File – Simpan Sebagai atau kunci F12, menentukan jenis file Add-in Excel:
Harap dicatat bahwa secara default Excel menyimpan add-in di folder C:UsersYour_nameAppDataRoamingMicrosoftAddIns, tetapi, pada prinsipnya, Anda dapat menentukan folder lain yang nyaman bagi Anda.
Langkah 2. Kami menghubungkan add-in yang dibuat
Sekarang add-in yang kita buat di langkah terakhir ExcelAddin saya harus terhubung ke Excel. Untuk melakukan ini, buka menu File – Opsi – Pengaya (File — Opsi — Add-Ins), klik tombol Tentang Kami (Pergi) di bagian bawah jendela. Di jendela yang terbuka, klik tombol ULASAN (Jelajahi) dan tentukan lokasi file add-in kami.
Jika Anda melakukan semuanya dengan benar, maka kami ExcelAddin saya akan muncul dalam daftar add-on yang tersedia:
Langkah 3. Tambahkan makro ke add-in
Add-in kami terhubung ke Excel dan berfungsi dengan baik, tetapi belum ada satu makro pun di dalamnya. Mari kita mengisinya. Untuk melakukan ini, buka editor Visual Basic dengan pintasan keyboard lain+F11 atau dengan tombol Visual Basic tab pembangun (Pengembang). Jika tab pembangun tidak terlihat, dapat ditampilkan melalui File – Opsi – Pengaturan Pita (File — Opsi — Kustomisasi Pita).
Seharusnya ada jendela di sudut kiri atas editor Project (jika tidak terlihat, maka nyalakan melalui menu Lihat — Penjelajah Proyek):
Jendela ini menampilkan semua buku kerja yang terbuka dan menjalankan add-in Microsoft Excel, termasuk milik kami. Proyek VBA (MyExcelAddin.xlam) Pilih dengan mouse dan tambahkan modul baru melalui menu Sisipkan – Modul. Dalam modul ini, kami akan menyimpan kode VBA makro add-in kami.
Anda dapat mengetik kode dari awal (jika Anda tahu cara memprogram), atau menyalinnya dari tempat yang sudah jadi (yang jauh lebih mudah). Mari, untuk pengujian, masukkan kode makro sederhana namun berguna ke dalam modul kosong yang ditambahkan:
Setelah memasukkan kode, jangan lupa klik tombol simpan (disket) di pojok kiri atas.
makro kami RumusUntukNilai, seperti yang dapat Anda bayangkan dengan mudah, mengonversi rumus menjadi nilai dalam rentang yang telah dipilih sebelumnya. Terkadang makro ini juga disebut Prosedur. Untuk menjalankannya, Anda perlu memilih sel dengan rumus dan membuka kotak dialog khusus Macro dari tab pembangun (Pengembang — Makro) atau pintasan keyboard lain+F8. Biasanya, jendela ini memperlihatkan makro yang tersedia dari semua buku kerja yang terbuka, tetapi makro tambahan tidak terlihat di sini. Meskipun demikian, kami dapat memasukkan nama prosedur kami di bidang nama makro (Nama makro)lalu klik tombol Run (Lari) – dan makro kami akan berfungsi:
| |
Di sini Anda juga dapat menetapkan pintasan keyboard untuk meluncurkan makro dengan cepat – tombol bertanggung jawab untuk ini parameter (Pilihan) di jendela sebelumnya Makro:
Saat menetapkan tombol, perlu diingat bahwa tombol tersebut peka huruf besar/kecil dan peka terhadap tata letak keyboard. Jadi jika Anda menetapkan kombinasi seperti Ctrl+Й, sebenarnya, di masa mendatang Anda harus memastikan bahwa tata letak Anda diaktifkan dan tekan tambahan perubahanuntuk mendapatkan huruf kapital.
Untuk kenyamanan, kami juga dapat menambahkan tombol untuk makro kami ke bilah alat akses cepat di sudut kiri atas jendela. Untuk melakukan ini, pilih File – Opsi – Bilah Alat Akses Cepat (File — Opsi — Kustomisasi Bilah Alat Akses Cepat), dan kemudian di daftar tarik-turun di bagian atas jendela opsi Macro. Setelah itu makro kami RumusUntukNilai dapat ditempatkan di panel dengan tombol Add (Tambah) dan pilih ikon untuk itu dengan tombol Perubahan (Ubah):
Langkah 4. Tambahkan fungsi ke add-in
Tapi prosedur makro, ada juga fungsi makro atau begitu mereka disebut UDF (Fungsi yang Ditentukan Pengguna = fungsi yang ditentukan pengguna). Mari buat modul terpisah di add-on kita (perintah menu Sisipkan – Modul) dan rekatkan kode fungsi berikut di sana:
Sangat mudah untuk melihat bahwa fungsi ini diperlukan untuk mengekstrak PPN dari jumlah termasuk PPN. Bukan binomial Newton, tentu saja, tetapi itu akan berguna bagi kita sebagai contoh untuk menunjukkan prinsip-prinsip dasarnya.
Perhatikan bahwa sintaks suatu fungsi berbeda dari prosedur:
- konstruksi digunakan Fungsi …. Fungsi Akhir sebagai gantinya Sub… Akhir Sub
- setelah nama fungsi, argumennya ditunjukkan dalam tanda kurung
- di badan fungsi, perhitungan yang diperlukan dilakukan dan kemudian hasilnya diberikan ke variabel dengan nama fungsi
Perhatikan juga bahwa fungsi ini tidak diperlukan, dan tidak mungkin dijalankan seperti prosedur makro sebelumnya melalui kotak dialog Macro dan tombol Run. Fungsi makro seperti itu harus digunakan sebagai fungsi lembar kerja standar (SUM, JIKA, VLOOKUP…), yaitu cukup masukkan di sel mana pun, tentukan nilai jumlah dengan PPN sebagai argumen:
… atau masuk melalui kotak dialog standar untuk menyisipkan fungsi (tombol fx di bilah rumus), memilih kategori Ditetapkan pengguna (Ditetapkan pengguna):
Satu-satunya momen yang tidak menyenangkan di sini adalah tidak adanya deskripsi fungsi yang biasa di bagian bawah jendela. Untuk menambahkannya, Anda harus melakukan hal berikut:
- Buka Editor Visual Basic dengan pintasan keyboard lain+F11
- Pilih add-in di panel Proyek dan tekan tombol F2untuk membuka jendela Object Browser
- Pilih proyek add-in Anda dari daftar drop-down di bagian atas jendela
- Klik kanan pada fungsi yang muncul dan pilih perintah Properties.
- Masukkan deskripsi fungsi di jendela Deskripsi Produk
- Simpan file tambahan dan mulai ulang excel.
Setelah restart, fungsi tersebut akan menampilkan deskripsi yang kita masukkan:
Langkah 5. Buat tab add-on di antarmuka
Sentuhan terakhir, meskipun tidak wajib, tetapi menyenangkan adalah pembuatan tab terpisah dengan tombol untuk menjalankan makro kami, yang akan muncul di antarmuka Excel setelah menghubungkan add-in kami.
Informasi tentang tab yang ditampilkan secara default terdapat di dalam buku dan harus diformat dalam kode XML khusus. Cara termudah untuk menulis dan mengedit kode tersebut adalah dengan bantuan program khusus – editor XML. Salah satu yang paling nyaman (dan gratis) adalah program Maxim Novikov Editor XML Pita.
Algoritma untuk bekerja dengannya adalah sebagai berikut:
- Tutup semua jendela Excel agar tidak terjadi konflik file saat kita mengedit kode XML add-in.
- Luncurkan program Ribbon XML Editor dan buka file MyExcelAddin.xlam kami di dalamnya
- Dengan tombol tab di sudut kiri atas, tambahkan cuplikan kode untuk tab baru:
- Anda harus memasukkan tanda kutip kosong id tab dan grup kami (pengidentifikasi unik apa pun), dan di label – nama tab kami dan sekelompok tombol di atasnya:
- Dengan tombol tombol di panel kiri, tambahkan kode kosong untuk tombol dan tambahkan tag ke dalamnya:
— label adalah teks pada tombol
— gambarMso — ini adalah nama kondisional dari gambar pada tombol. Saya menggunakan ikon tombol merah yang disebut AnimationCustomAddExitDialog. Nama semua tombol yang tersedia (dan ada beberapa ratus di antaranya!) dapat ditemukan di banyak situs di Internet jika Anda mencari kata kunci "imageMso". Sebagai permulaan, Anda bisa pergi ke sini.
- diAction – ini adalah nama prosedur panggilan balik – makro pendek khusus yang akan menjalankan makro utama kami RumusUntukNilai. Anda dapat memanggil prosedur ini apa pun yang Anda suka. Kami akan menambahkannya nanti.
- Anda dapat memeriksa kebenaran semua yang dilakukan menggunakan tombol dengan tanda centang hijau di bagian atas bilah alat. Di tempat yang sama, klik tombol dengan floppy disk untuk menyimpan semua perubahan.
- Tutup Editor XML Pita
- Buka Excel, buka editor Visual Basic dan tambahkan prosedur panggilan balik ke makro kami BunuhFormulasehingga menjalankan makro utama kami untuk mengganti rumus dengan nilai.
- Kami menyimpan perubahan dan, kembali ke Excel, periksa hasilnya:
Itu saja – add-in siap digunakan. Isi dengan prosedur dan fungsi Anda sendiri, tambahkan tombol yang indah – dan akan menjadi lebih mudah untuk menggunakan makro dalam pekerjaan Anda.
- Apa itu makro, bagaimana menggunakannya dalam pekerjaan Anda, di mana mendapatkan kode makro di Visual Basic.
- Cara membuat layar splash saat membuka buku kerja di Excel
- Apa itu Buku Makro Pribadi dan cara menggunakannya