Komputer, Perisian
VBA Excel: contoh program. Macros dalam Excel
Beberapa orang tahu bahawa versi pertama produk Microsoft Excel yang popular muncul pada tahun 1985. Sejak itu, ia telah menjalani beberapa pengubahsuaian dan terdapat permintaan di kalangan berjuta-juta pengguna di seluruh dunia. Pada masa yang sama, ramai yang bekerja dengan hanya sebahagian kecil daripada keupayaan pemproses meja ini dan tidak menebak bagaimana mereka dapat menjadikan kehidupan lebih mudah dalam kemahiran pengaturcaraan Excel.
Apakah VBA?
Pemrograman dalam Excel dijalankan melalui bahasa pengaturcaraan Visual Basic for Application, yang pada asalnya dibina ke pemproses meja paling terkenal dari Microsoft.
Kepada meritnya, pakar menilai kemudahan perbandingan pembangunan. Sebagai menunjukkan amalan, pengguna VBA boleh menguasai walaupun mereka yang tidak memiliki kemahiran pengaturcaraan profesional. Keanehan VBA adalah pelaksanaan skrip dalam lingkungan aplikasi pejabat.
Kelemahan program ini adalah masalah yang berkaitan dengan kesesuaian versi yang berlainan. Ini adalah kerana kod program VBA merujuk kepada fungsi yang ada dalam versi produk baru, tetapi bukan pada yang lama. Juga, minus termasuk terlalu banyak keterbukaan kod untuk perubahan oleh orang yang tidak dibenarkan. Walau bagaimanapun, Microsoft Office, serta IBM Lotus Symphony, membenarkan pengguna menggunakan penyulitan kod awal dan menetapkan kata laluan untuk melihatnya.
Objek, koleksi, sifat, dan kaedah
Ia adalah dengan konsep-konsep ini yang anda perlu memahami siapa yang akan bekerja dalam persekitaran VBA. Pertama sekali, adalah perlu untuk memahami apa objek itu. Dalam Excel, ini adalah helaian, buku, sel, dan julat. Objek ini mempunyai hierarki khas, iaitu. Berunding satu sama lain.
Yang utama adalah Permohonan, yang sepadan dengan Excel itu sendiri. Kemudian ikuti Buku Kerja, Lembaran Kerja, dan juga Julat. Sebagai contoh, untuk mengakses sel A1 pada helaian tertentu, anda mesti menentukan laluan yang merangkumi hierarki.
Adapun konsep "pengumpulan", maka ini adalah kumpulan objek dari kelas yang sama yang dalam entri kelihatan seperti ChartObjects. Elemen individu juga objek.
Konsep seterusnya adalah sifat. Mereka adalah ciri-ciri yang perlu bagi mana-mana objek. Sebagai contoh, untuk Range, ini adalah Nilai atau Formula.
Kaedah adalah arahan yang menunjukkan apa yang perlu dilakukan. Apabila menulis kod di VBA, anda perlu memisahkannya dari objek mengikut tempoh. Sebagai contoh, seperti yang akan ditunjukkan kemudian, sangat kerap apabila pengaturcaraan dalam Excel, gunakan Sel Sel (1,1). Pilih. Ini bermakna anda perlu memilih sel dengan koordinat (1,1) iaitu A1.
Dengan itu, Selection.ClearContents sering digunakan. Melakukannya bermakna membersihkan kandungan sel yang dipilih.
Bagaimana untuk memulakan
Pertama sekali, anda perlu membuat fail dan simpannya dengan memberikan nama dan memilih jenis "buku kerja Excel dengan sokongan makro".
Kemudian anda perlu pergi ke aplikasi VB, hanya gunakan kekunci "Alt" dan "F11". Seterusnya:
- Dalam bar menu yang terletak di bahagian atas tetingkap, klik pada ikon di sebelah ikon Excel;
- Pilih arahan Mudule;
- Simpan dengan mengklik pada ikon dengan cakera liut imej;
- Tulis, katakan, lakaran kod itu.
Ia kelihatan seperti ini:
Program sub ()
'Kod kami
Sub Akhir
Perhatikan bahawa baris "Kod kami" akan diserlahkan dalam warna yang berbeza (hijau). Alasannya adalah di apostrophe pada awal baris, yang menunjukkan bahawa komen berikut.
Kini anda boleh menulis sebarang kod dan membuat alat baru untuk diri anda dalam VBA Excel (lihat contoh program di bawah). Sudah tentu, mereka yang biasa dengan asas-asas Visual Basic, akan lebih mudah. Walau bagaimanapun, walaupun mereka yang tidak mempunyai mereka, jika mereka mahu, dapat dengan cepat membiasakannya.
Macros dalam Excel
Untuk nama ini, program yang ditulis dalam bahasa Visual Basic untuk Aplikasi tersembunyi. Oleh itu, pengaturcaraan dalam Excel adalah penciptaan makro dengan kod yang dikehendaki. Terima kasih kepada ciri ini, pemproses spreadsheet Microsoft berkembang sendiri, menyesuaikan diri dengan keperluan pengguna tertentu. Setelah memahami cara membuat modul untuk menulis makro, anda boleh mula melihat contoh khusus program VBA Excel. Adalah lebih baik untuk memulakan dengan kod paling asas.
Contoh 1
Tugas: tulis satu program yang akan menyalin nilai kandungan satu sel dan kemudian tulis kepada yang lain.
Untuk melakukan ini:
- Buka tab "Lihat";
- Pergi ke ikon "Makro";
- Tekan "Rekod makro";
- Isikan borang yang dibuka.
Untuk kesederhanaan, medan "Makro nama" ditinggalkan dengan "Macro1", dan dalam medan "Pintasan papan kekunci", contohnya, hh dimasukkan (ini bermakna anda boleh melancarkan program dengan perintah "Ctrl + h"). Tekan Enter.
Sekarang, apabila rakaman makro sudah dimulakan, kandungan sel akan disalin ke yang lain. Kembali ke ikon asal. Klik pada "Merakam Makro". Tindakan ini bermaksud berakhirnya program.
Seterusnya:
- Sekali lagi pergi ke barisan "Makro";
- Dalam senarai pilih "Makro 1";
- Klik "Execute" (tindakan yang sama dimulakan dengan memulakan pintasan papan kekunci "Ctrl + hh").
Akibatnya, terdapat tindakan yang dilakukan semasa rakaman makro.
Ia masuk akal untuk melihat apa yang kelihatan seperti kod. Untuk melakukan ini, kembali ke baris "Macros" dan klik "Edit" atau "Log masuk". Akibatnya, mereka berada dalam persekitaran VBA. Sebenarnya kod makro itu sendiri terletak di antara Sub Subrouter Subro (Sub) dan Sub End.
Sekiranya penyalinan dilakukan, contohnya, dari sel A1 ke sel C1, maka salah satu baris kod akan kelihatan seperti Range ("C1"). Dalam terjemahan, ia kelihatan seperti "Julat (" C1 "). Pilih", dengan kata lain, ia bertukar kepada VBA Excel, dalam sel C1.
Bahagian aktif kod ditamatkan oleh perintah ActiveSheet.Paste. Ini bermakna merakam kandungan sel yang dipilih (dalam kes ini A1) di sel yang dipilih C1.
Contoh 2
Kitaran VBA membantu anda membuat pelbagai makro di Excel.
Kitaran VBA membantu anda membuat makro yang berbeza. Katakan bahawa terdapat fungsi y = x + x 2 + 3x 3 - cos (x). Anda perlu membuat makro untuk mendapatkan grafnya. Anda boleh melakukan ini hanya menggunakan gelung VBA.
Untuk nilai permulaan dan akhir hujah fungsi, ambil x1 = 0 dan x2 = 10. Di samping itu, anda mesti memasukkan nilai malar untuk langkah menukar hujah dan nilai awal untuk kaunter.
Semua contoh makro VBA Excel dicipta menggunakan prosedur yang sama seperti di atas. Dalam kes ini, kod tersebut kelihatan seperti:
Sub programm ()
X1 = 1
X2 = 10
Shag = 0.1
I = 1
Lakukan Semasa x1
Y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)
Sel (i, 1). Nilai = x1 (nilai x1 ditulis ke sel dengan koordinat (i, 1))
Sel (i, 2). Nilai = y (nilai y ditulis dalam sel dengan koordinat (i, 2))
I = i + 1 (kaunter beroperasi);
X1 = x1 + shag (hujah ditukar dengan saiz langkah);
Loop
Sub Akhir.
Hasil daripada menjalankan makro ini dalam "Excel" kita mendapat dua lajur, yang pertama mengandungi nilai untuk x, dan pada kedua - untuk y.
Kemudian mereka plot grafik dengan cara yang standard untuk Excel.
Contoh 3
Untuk melaksanakan kitaran dalam VBA Excel 2010, seperti dalam versi lain, bersama dengan Do Do yang telah disediakan, For digunakan.
Pertimbangkan satu program yang mencipta lajur. Dalam setiap sel-selnya, nombor baris bersamaan akan direkodkan. Menggunakan For build akan membolehkan anda untuk menulisnya tidak lama lagi, tanpa menggunakan kaunter.
Pertama anda perlu membuat makro, seperti yang diterangkan di atas. Seterusnya, kami menulis kod itu sendiri. Kami fikir kita berminat dengan nilai-nilai untuk 10 sel. Kod ini kelihatan seperti ini.
Untuk i = 1 hingga 10 Seterusnya
Perintah itu diterjemahkan ke dalam bahasa "manusia", seperti "Ulang dari 1 hingga 10 dalam langkah satu".
Jika tugasnya adalah untuk mendapatkan lajur dengan segi empat, sebagai contoh, semua nombor ganjil dari julat dari 1 hingga 11, maka kita menulis:
Untuk i = 1 hingga 10 langkah 1 Seterusnya.
Di sini langkah adalah satu langkah. Dalam kes ini, ia adalah sama dengan dua. Secara lalai, ketiadaan perkataan ini dalam gelung bermakna bahawa langkahnya adalah tunggal.
Hasil yang diperoleh hendaklah disimpan dalam sel dengan nombor (i, 1). Kemudian setiap kali kitaran bermula, dengan peningkatan saya dengan saiz langkah, bilangan baris juga akan meningkat secara automatik. Oleh itu, kod tersebut akan dioptimumkan.
Secara umum, kod tersebut akan kelihatan seperti:
Program sub ()
Untuk i = 1 hingga 10 Langkah 1 (anda boleh menulis hanya Untuk i = 1 hingga 10)
Sel (i, 1). Nilai = i ^ 2 (iaitu nilai persegi i) ditulis dalam sel (i, 1)
Seterusnya (dalam erti kata ia memainkan peranan kaunter dan bermakna satu lagi kitaran permulaan)
Sub Akhir.
Sekiranya semuanya dilakukan dengan betul, termasuk merakam dan menjalankan makro (lihat arahan di atas), setiap kali ia dipanggil, satu lajur saiz yang ditentukan (dalam kes ini terdiri daripada 10 sel) akan diperolehi.
Contoh 4
Dalam kehidupan seharian, sangat kerap terdapat keperluan untuk mengambil keputusan bergantung kepada beberapa jenis keadaan. Anda tidak boleh melakukannya tanpa VBA Excel. Contoh-contoh program di mana kursus selanjutnya pelaksanaan algoritma dipilih, dan bukannya awal yang ditetapkan, paling sering menggunakan If ... Kemudian pembinaan (untuk kes-kes yang rumit) Jika ... Kemudian ... END Jika.
Mari kita pertimbangkan satu kes konkrit. Katakan anda perlu membuat makro untuk Excel, supaya sel dengan koordinat (1,1) ditulis:
1 jika hujah adalah positif;
0 jika hujah adalah sifar;
-1 jika hujah adalah negatif.
Penciptaan makro seperti itu untuk Excel bermula dengan cara yang standard, dengan menggunakan kekunci "panas" Alt dan F11. Kemudian kod berikut ditulis:
Program sub ()
X = Sel (1, 1). Valid (arahan ini memberikan nilai kandungan sel dengan koordinat (1, 1))
Jika x> 0 Kemudian Sel (1, 1) .Value = 1
Jika x = 0 Kemudian Sel (1, 1). Nilai = 0
Jika x <0 Kemudian Sel (1, 1) .Value = -1
Sub Akhir.
Ia tetap untuk menjalankan makro dan mendapatkan nilai yang betul untuk hujah dalam Excel.
Fungsi VBA
Seperti yang anda perhatikan, pengaturcaraan dalam pemproses meja paling terkenal di Microsoft tidak begitu sukar. Terutama jika anda belajar bagaimana menggunakan fungsi VBA. Secara keseluruhannya dalam bahasa pengaturcaraan ini, dibuat khusus untuk menulis aplikasi dalam Excel dan Word, terdapat sekitar 160 fungsi. Mereka boleh dibahagikan kepada beberapa kumpulan besar. Ini adalah:
- Fungsi matematik. Memohon mereka kepada hujah, anda mendapat nilai kosinus, logaritma semula jadi, keseluruhan bahagian, dan sebagainya.
- Fungsi kewangan. Oleh kerana ketersediaan dan penggunaan pengaturcaraan dalam Excel, anda boleh mendapatkan alat yang berkesan untuk perakaunan dan perakaunan kewangan.
- Fungsi pemprosesan susun. Ini termasuk Array, IsArray; LBound; UBound.
- Fungsi VBA Excel untuk rentetan. Ini adalah kumpulan yang agak besar. Ini termasuk, sebagai contoh, fungsi Ruang untuk membuat rentetan dengan beberapa ruang sama dengan hujah integer, atau Asc untuk menterjemahkan aksara ke dalam kod ANSI. Kesemuanya digunakan secara meluas dan membolehkan anda bekerja dengan rentetan dalam Excel, mewujudkan aplikasi yang sangat memudahkan kerja dengan jadual ini.
- Fungsi penukaran jenis data. Sebagai contoh, CVar mengembalikan nilai hujah Ekspresi, menukarnya kepada jenis data Varians.
- Fungsi kerja dengan tarikh. Mereka dengan ketara mengembangkan ciri standard Excel. Jadi, fungsi WeekdayName memulangkan nama (penuh atau separa) dari hari minggu dengan nombornya. Malah lebih berguna adalah Pemasa. Ia memberikan bilangan saat yang berlalu dari tengah malam hingga saat tertentu pada hari itu.
- Fungsi untuk menukar hujah angka kepada sistem nombor yang berbeza. Sebagai contoh, Oct mengeluarkan perwakilan oktaf nombor.
- Fungsi pemformatan. Yang paling penting ialah Format. Ia mengembalikan nilai jenis Varian dengan ungkapan yang diformatkan mengikut arahan yang dinyatakan dalam deskripsi format.
- Dan lain-lain.
Kajian mengenai ciri-ciri fungsi dan aplikasi mereka akan memperluaskan ruang lingkup aplikasi Excel.
Contoh 5
Mari cuba selesaikan masalah yang lebih rumit. Sebagai contoh:
Memandangkan dokumen kertas laporan tahap sebenar kos perusahaan. Diperlukan:
- Untuk mengembangkan bahagian templatnya dengan cara pemproses meja "Excel";
- Buat program VBA yang akan menanyakan data sumber untuk mengisi, membuat pengiraan yang diperlukan dan isi sel-sel yang sesuai dari template.
Marilah kita pertimbangkan salah satu daripada varian penyelesaiannya.
Mewujudkan Templat
Semua tindakan dilakukan pada helaian standard dalam Excel. Sel-sel percuma yang disimpan untuk memasukkan data untuk bulan, tahun, nama syarikat-pengguna, jumlah kos, tahap mereka, perolehan. Memandangkan bilangan syarikat (syarikat) berkenaan dengan laporan yang dibuat tidak ditetapkan, sel-sel untuk membuat nilai-nilai berdasarkan keputusan dan nama pakar tidak dikhaskan sebelum ini. Lembaran kerja diberi nama baru. Sebagai contoh, "TABLE".
Pembolehubah
Untuk menulis program untuk mengisi templat secara automatik, anda mesti memilih simbol. Mereka akan digunakan untuk pemboleh ubah:
- NN- bilangan baris semasa jadual;
- TP dan TF - perolehan yang dirancang dan sebenar;
- SF dan SP - amaun kos sebenar dan dirancang;
- IP dan IF - tahap kos yang dirancang dan sebenar.
Nyatakan dengan huruf yang sama, tetapi dengan "awalan" Itog pengumpulan jumlah untuk lajur ini. Sebagai contoh, ItogTP merujuk kepada ruang jadual yang bertajuk "perolehan yang dirancang".
Penyelesaian Masalah Menggunakan Pengaturcaraan VBA
Dengan menggunakan notasi yang diperkenalkan, kami memperoleh formula untuk penyimpangan. Sekiranya anda ingin mengira%, anda mempunyai (F - P) / P * 100, dan jumlahnya - (F - P).
Hasil perhitungan ini sebaiknya segera ditambahkan ke sel yang bersamaan dalam jadual Excel.
Hasilnya sebenarnya dan ramalan diperolehi oleh formula ItogP = ItogP + P dan ItogF = ItogF + F.
Untuk penyimpangan gunakan = (ItogF - ItogP) / ItogP * 100, jika pengiraan dalam peratusan, dan dalam kes jumlah nilai - (ItogF - ItogP).
Hasilnya segera ditulis ke sel-sel yang bersamaan, sehingga tidak perlu memberikannya kepada pembolehubah.
Sebelum memulakan program yang dicipta, diperlukan untuk menyimpan buku kerja, contohnya, di bawah nama "Report1.xls".
Butang "Buat jadual laporan" perlu ditekan hanya sekali selepas memasukkan maklumat pengepala. Anda perlu tahu kaedah lain. Khususnya, butang "Tambah baris" perlu ditekan setiap kali selepas anda memasukkan nilai untuk setiap aktiviti dalam jadual. Setelah memasukkan semua data, anda perlu mengklik butang "Selesai" dan kemudian beralih ke tetingkap Excel.
Sekarang anda tahu bagaimana untuk menyelesaikan masalah ini untuk Excel dengan makro. Keupayaan untuk menggunakan VBA excel (lihat program sampel. Di atas) dan anda mungkin perlu untuk bekerja dalam persekitaran yang paling popular pada masa ini, editor teks "Word." Khususnya, dengan rakaman, seperti yang ditunjukkan pada awal artikel ini, atau dengan menulis kod untuk membuat butang menu di mana banyak operasi pada teks boleh dilakukan dengan menekan kekunci bertugas atau melalui ikon "View" dan tab "Makro".
Similar articles
Trending Now