Contents [show]
Walaupun anda tidak tahu cara memprogram, terdapat banyak tempat (buku, tapak web, forum) di mana anda boleh mencari kod makro VBA siap sedia untuk sejumlah besar tugas biasa dalam Excel. Mengikut pengalaman saya, kebanyakan pengguna lambat laun mengumpul koleksi makro peribadi mereka untuk mengautomasikan proses rutin, sama ada menterjemah formula kepada nilai, memaparkan jumlah dalam perkataan atau menjumlahkan sel mengikut warna. Dan di sini masalah timbul - kod makro dalam Visual Basic perlu disimpan di suatu tempat untuk digunakan kemudian dalam kerja.
Pilihan paling mudah ialah menyimpan kod makro terus dalam fail yang berfungsi dengan pergi ke editor Visual Basic menggunakan pintasan papan kekunci Alt+F11 dan menambah modul kosong baharu melalui menu Sisipan – Modul:
Walau bagaimanapun, terdapat beberapa kelemahan dengan kaedah ini:
- Jika terdapat banyak fail yang berfungsi dan makro diperlukan di mana-mana, seperti makro untuk menukar formula kepada nilai, maka anda perlu menyalin kod dalam setiap buku.
- Tidak boleh dilupakan simpan fail dalam format yang didayakan makro (xlsm) atau dalam format buku binari (xlsb).
- Apabila membuka fail sedemikian perlindungan makro akan setiap kali mengeluarkan amaran yang perlu diakui (baik, atau lumpuhkan perlindungan sepenuhnya, yang mungkin tidak selalu diingini).
Penyelesaian yang lebih elegan ialah mencipta add-in anda sendiri (Excel Add-in) – fail berasingan dengan format khas (xlam) yang mengandungi semua makro "kegemaran" anda. Kelebihan pendekatan ini:
- Ia akan cukup sambung add-on sekali dalam Excel – dan anda boleh menggunakan prosedur dan fungsi VBAnya dalam mana-mana fail pada komputer ini. Menyimpan semula fail kerja anda dalam format xlsm- dan xlsb, oleh itu, tidak diperlukan, kerana. kod sumber tidak akan disimpan di dalamnya, tetapi dalam fail tambahan.
- perlindungan anda juga tidak akan diganggu oleh makro. alat tambah adalah, mengikut definisi, sumber yang dipercayai.
- Boleh buat tab berasingan pada reben Excel dengan butang yang bagus untuk menjalankan makro tambahan.
- Add-in ialah fail yang berasingan. miliknya mudah untuk menjalankan dari komputer ke komputer, kongsi dengan rakan sekerja atau pun jual 😉
Mari kita ikuti keseluruhan proses mencipta alat tambah Microsoft Excel anda sendiri langkah demi langkah.
Langkah 1. Buat fail tambahan
Buka Microsoft Excel dengan buku kerja kosong dan simpannya di bawah sebarang nama yang sesuai (contohnya MyExcelAddin) dalam format tambah dengan arahan Fail – Simpan Sebagai atau kunci F12, menyatakan jenis fail Tambahan Excel:
Sila ambil perhatian bahawa secara lalai Excel menyimpan tambahan dalam folder C:UsersYour_nameAppDataRoamingMicrosoftAddIns, tetapi, pada dasarnya, anda boleh menentukan mana-mana folder lain yang sesuai untuk anda.
Langkah 2. Kami menyambungkan tambahan yang dibuat
Sekarang add-in yang kami buat dalam langkah terakhir MyExcelAddin mesti disambungkan ke Excel. Untuk melakukan ini, pergi ke menu Fail – Pilihan – Alat tambah (Fail — Pilihan — Tambahan), klik pada butang Info (Pergi) di bahagian bawah tingkap. Dalam tetingkap yang terbuka, klik butang ULASAN (Semak imbas) dan nyatakan lokasi fail tambahan kami.
Jika anda melakukan semuanya dengan betul, maka kami MyExcelAddin harus muncul dalam senarai alat tambah yang tersedia:
Langkah 3. Tambahkan makro pada add-in
Add-in kami disambungkan ke Excel dan berfungsi dengan jayanya, tetapi belum ada satu makro pun di dalamnya. Jom isi. Untuk melakukan ini, buka editor Visual Basic dengan pintasan papan kekunci Alt+F11 atau dengan butang Visual Basic tab pemaju (Pembangun). Jika tab pemaju tidak kelihatan, ia boleh dipaparkan melalui Fail – Pilihan – Persediaan Reben (Fail — Pilihan — Sesuaikan Reben).
Harus ada tetingkap di sudut kiri atas editor Projek (jika ia tidak kelihatan, kemudian hidupkannya melalui menu Lihat — Penjelajah Projek):
Tetingkap ini memaparkan semua buku kerja terbuka dan menjalankan tambahan Microsoft Excel, termasuk kami. VBAProject (MyExcelAddin.xlam) Pilihnya dengan tetikus dan tambahkan modul baharu padanya melalui menu Sisipan – Modul. Dalam modul ini, kami akan menyimpan kod VBA makro tambahan kami.
Anda boleh sama ada menaip kod dari awal (jika anda tahu cara memprogram), atau menyalinnya dari tempat yang sudah siap (yang lebih mudah). Mari, untuk ujian, masukkan kod makro yang mudah tetapi berguna ke dalam modul kosong yang ditambahkan:
Selepas memasukkan kod, jangan lupa klik pada butang simpan (disket) di sudut kiri atas.
Makro kami FormulasToValues, seperti yang anda boleh bayangkan dengan mudah, menukar formula kepada nilai dalam julat yang diprapilih. Kadang-kadang makro ini juga dipanggil prosedur. Untuk menjalankannya, anda perlu memilih sel dengan formula dan membuka kotak dialog khas Makro daripada tab pemaju (Pembangun — Makro) atau pintasan papan kekunci Alt+F8. Biasanya, tetingkap ini menunjukkan makro yang tersedia daripada semua buku kerja terbuka, tetapi makro tambahan tidak kelihatan di sini. Walaupun begitu, kami boleh memasukkan nama prosedur kami dalam medan nama makro (Nama makro)dan kemudian klik butang Main (lari) – dan makro kami akan berfungsi:
| |
Di sini anda juga boleh menetapkan pintasan papan kekunci untuk melancarkan makro dengan cepat - butang bertanggungjawab untuk ini Parameter (Pilihan) dalam tetingkap sebelumnya Makro:
Apabila memberikan kekunci, perlu diingat bahawa ia adalah sensitif huruf besar dan reka letak papan kekunci. Jadi jika anda menetapkan kombinasi seperti Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Beralihuntuk mendapatkan huruf besar.
Untuk kemudahan, kami juga boleh menambah butang untuk makro kami pada bar alat akses pantas di penjuru kiri sebelah atas tetingkap. Untuk melakukan ini, pilih Fail – Pilihan – Bar Alat Akses Pantas (Fail — Pilihan — Sesuaikan Bar Alat Akses Pantas), dan kemudian dalam senarai juntai bawah di bahagian atas tetingkap pilihan Makro. Selepas itu makro kita FormulasToValues boleh diletakkan pada panel dengan butang Tambah (Tambah) dan pilih ikon untuknya dengan butang Tukar (Edit):
Langkah 4. Tambah fungsi pada add-in
Tetapi prosedur makro, terdapat juga makro fungsi atau seperti yang dipanggil UDF (Fungsi Ditakrifkan Pengguna = fungsi yang ditentukan pengguna). Mari buat modul berasingan dalam add-on kami (arahan menu Sisipan – Modul) dan tampal kod fungsi berikut di sana:
Adalah mudah untuk melihat bahawa fungsi ini diperlukan untuk mengeluarkan VAT daripada jumlah termasuk VAT. Bukan binomial Newton, sudah tentu, tetapi ia akan membantu kita sebagai contoh untuk menunjukkan prinsip asas.
Ambil perhatian bahawa sintaks fungsi adalah berbeza daripada prosedur:
- pembinaan digunakan Fungsi …. Fungsi Tamat sebaliknya Sub … Tamat Sub
- selepas nama fungsi, hujahnya ditunjukkan dalam kurungan
- dalam badan fungsi, pengiraan yang diperlukan dilakukan dan kemudian hasilnya diberikan kepada pembolehubah dengan nama fungsi
Juga ambil perhatian bahawa fungsi ini tidak diperlukan, dan adalah mustahil untuk dijalankan seperti prosedur makro sebelumnya melalui kotak dialog Makro dan butang Main. Fungsi makro sedemikian harus digunakan sebagai fungsi lembaran kerja standard (SUM, JIKA, VLOOKUP…), iaitu hanya masukkan dalam mana-mana sel, dengan menyatakan nilai amaun dengan VAT sebagai hujah:
… atau masukkan melalui kotak dialog standard untuk memasukkan fungsi (button fx dalam bar formula), memilih kategori Pengguna Ditentukan (Ditentukan Pengguna):
Satu-satunya detik yang tidak menyenangkan di sini ialah ketiadaan penerangan biasa fungsi di bahagian bawah tetingkap. Untuk menambahnya, anda perlu melakukan perkara berikut:
- Buka Editor Visual Basic dengan pintasan papan kekunci Alt+F11
- Pilih tambahan dalam panel Projek dan tekan kekunci F2untuk membuka tetingkap Pelayar Objek
- Pilih projek tambahan anda daripada senarai lungsur di bahagian atas tetingkap
- Klik kanan pada fungsi yang muncul dan pilih arahan Hartanah.
- Masukkan perihalan fungsi dalam tetingkap Penerangan Produk
- Simpan fail tambahan dan mulakan semula excel.
Selepas dimulakan semula, fungsi harus memaparkan penerangan yang kami masukkan:
Langkah 5. Buat tab tambahan dalam antara muka
Sentuhan terakhir, walaupun tidak wajib, tetapi menyenangkan adalah penciptaan tab berasingan dengan butang untuk menjalankan makro kami, yang akan muncul dalam antara muka Excel selepas menyambungkan tambahan kami.
Maklumat tentang tab yang dipaparkan secara lalai terkandung dalam buku dan mesti diformatkan dalam kod XML khas. Cara paling mudah untuk menulis dan mengedit kod tersebut adalah dengan bantuan program khas – editor XML. Salah satu yang paling mudah (dan percuma) ialah program Maxim Novikov Editor XML Reben.
Algoritma untuk bekerja dengannya adalah seperti berikut:
- Tutup semua tetingkap Excel supaya tiada konflik fail apabila kami mengedit kod XML tambahan.
- Lancarkan program Ribbon XML Editor dan buka fail MyExcelAddin.xlam kami di dalamnya
- Dengan butang tab di penjuru kiri sebelah atas, tambahkan coretan kod untuk tab baharu:
- Anda perlu memasukkan petikan kosong id tab dan kumpulan kami (sebarang pengecam unik), dan dalam label – nama tab kami dan sekumpulan butang padanya:
- Dengan butang butang pada panel kiri, tambahkan kod kosong untuk butang dan tambahkan tag padanya:
- label ialah teks pada butang
— imejMso — ini ialah nama bersyarat bagi imej pada butang. Saya menggunakan ikon butang merah yang dipanggil AnimationCustomAddExitDialog. Nama semua butang yang tersedia (dan terdapat beberapa ratus daripadanya!) boleh didapati di sebilangan besar tapak di Internet jika anda mencari kata kunci "imageMso". Sebagai permulaan, anda boleh pergi ke sini.
- onAction – ini adalah nama prosedur panggil balik – makro pendek khas yang akan menjalankan makro utama kami FormulasToValues. Anda boleh memanggil prosedur ini apa sahaja yang anda suka. Kami akan menambahnya sedikit kemudian.
- Anda boleh menyemak ketepatan semua yang dilakukan menggunakan butang dengan tanda semak hijau di bahagian atas bar alat. Di tempat yang sama, klik pada butang dengan cakera liut untuk menyimpan semua perubahan.
- Tutup Editor XML Reben
- Buka Excel, pergi ke editor Visual Basic dan tambahkan prosedur panggil balik pada makro kami KillFormulassupaya ia menjalankan makro utama kami untuk menggantikan formula dengan nilai.
- Kami menyimpan perubahan dan, kembali ke Excel, semak hasilnya:
Itu sahaja – alat tambah sedia untuk digunakan. Isi dengan prosedur dan fungsi anda sendiri, tambah butang yang cantik – dan ia akan menjadi lebih mudah untuk menggunakan makro dalam kerja anda.
- Apakah makro, cara menggunakannya dalam kerja anda, tempat untuk mendapatkan kod makro dalam Visual Basic.
- Cara membuat skrin percikan semasa membuka buku kerja dalam Excel
- Apakah itu Buku Makro Peribadi dan cara menggunakannya