Panduan VBA Lanjutan untuk MS Excel

Sekiranya anda baru sahaja bermula dengan VBA, maka anda akan ingin memulakan pengajian Panduan VBA untuk Pemula kami. Tetapi jika anda seorang pakar VBA yang berpengalaman dan anda mencari perkara yang lebih maju yang boleh anda lakukan dengan VBA di Excel, maka terus membaca.

Keupayaan untuk menggunakan pengekodan VBA di Excel membuka seluruh dunia automasi. Anda boleh mengotomatisasi pengiraan di Excel, Pushbuttons, dan juga menghantar e -mel. Terdapat lebih banyak kemungkinan untuk mengotomatisasi kerja harian anda dengan VBA daripada yang anda mungkin sedar.

Ini menetapkan lembaran kerja aktif untuk Lembaran1, membersihkan lembaran, menyambung ke fail menggunakan laluan fail yang anda tentukan dengan strfile pemboleh ubah, dan kemudian Dengan Kitaran gelung melalui setiap baris dalam fail dan meletakkan data ke dalam helaian bermula pada sel A1.

Sekiranya anda menjalankan kod ini, anda akan melihat bahawa data fail CSV diimport ke dalam spreadsheet kosong anda, masuk Lembaran1.

Mengimport hanya langkah pertama. Seterusnya, anda ingin membuat tajuk baru untuk lajur yang akan mengandungi hasil pengiraan anda. Dalam contoh ini, katakan anda ingin mengira cukai 5% yang dibayar atas penjualan setiap item.

Perintah tindakan yang harus diambil oleh kod anda ialah:

1. Buat lajur hasil baru yang dipanggil cukai.
2. Gelung melalui unit dijual lajur dan hitung cukai jualan.
3. Tulis hasil hitung ke baris yang sesuai di dalam helaian.

Kod berikut akan mencapai semua langkah ini.

Lastrow redup selagi
Dim Startcell sebagai julat
Dim Rowcounter sebagai Integer
Rng rng sebagai julat, sel sebagai julat
Dim flttax sebagai dua kali ganda
Tetapkan StartCell = Range ("A1")
'Cari baris dan lajur terakhir
LASTROW = WS.Sel (Ws.Baris.Kira, permulaan.Kolum).Akhir (xlup).Baris
Tetapkan RNG = WS.Julat (WS.Sel (2, 4), WS.Sel (lastrow, 4))
RowCounter = 2
Sel (1, 5) = "Cukai"
Untuk setiap sel di RNG
flttax = sel.Nilai * 0.05
Sel (RowCounter, 5) = flttax
RowCounter = RowCounter + 1
Sel seterusnya

Kod ini mendapati baris terakhir dalam lembaran data anda, dan kemudian menetapkan julat sel (lajur dengan harga jualan) mengikut baris data pertama dan terakhir. Kemudian kod gelung melalui setiap sel tersebut, melakukan pengiraan cukai dan menulis hasilnya ke dalam lajur baru anda (lajur 5).

Tampalkan kod VBA di atas di bawah kod sebelumnya, dan jalankan skrip. Anda akan melihat hasilnya muncul dalam lajur e.

Sekarang, setiap kali anda membuka lembaran kerja Excel anda, ia akan keluar secara automatik dan dapatkan salinan data segar dari fail CSV. Kemudian, ia akan melakukan pengiraan dan menulis hasilnya ke lembaran. Anda tidak perlu melakukan apa -apa lagi secara manual!

Kirakan hasil dari butang tekan

Sekiranya anda lebih suka mempunyai lebih banyak kawalan langsung apabila pengiraan dijalankan, bukannya berjalan secara automatik apabila lembaran dibuka, anda boleh menggunakan butang kawalan sebaliknya.

Butang kawalan berguna jika anda ingin mengawal pengiraan yang digunakan. Contohnya, dalam kes yang sama seperti di atas, bagaimana jika anda ingin menggunakan kadar cukai 5% untuk satu rantau, dan kadar cukai 7% untuk yang lain?

Anda boleh membenarkan kod import CSV yang sama dijalankan secara automatik, tetapi tinggalkan kod pengiraan cukai untuk dijalankan apabila anda menekan butang yang sesuai.

Menggunakan spreadsheet yang sama seperti di atas, pilih Pemaju tab, dan pilih Masukkan daripada Kawalan kumpulan dalam reben. Pilih tekan butang Kawalan ActiveX dari menu dropdown.

Lukis butang tekan ke mana -mana bahagian lembaran dari mana data akan pergi.

Klik kanan butang tekan, dan pilih Sifat. Di tetingkap Properties, ubah kapsyen ke apa yang anda ingin paparkan kepada pengguna. Dalam kes ini mungkin Kirakan cukai 5%.

Anda akan melihat teks ini tercermin pada butang tekan itu sendiri. Menutup sifat tetingkap, dan klik dua kali tekan sendiri. Ini akan membuka tetingkap editor kod, dan kursor anda akan berada di dalam fungsi yang akan dijalankan apabila pengguna menekan butang tekan.

Tampalkan kod pengiraan cukai dari bahagian di atas ke dalam fungsi ini, menjaga pengganda kadar cukai pada 0.05. Ingatlah untuk memasukkan 2 baris berikut untuk menentukan lembaran aktif.

Dim ws sebagai lembaran kerja, strfile sebagai rentetan
Tetapkan WS = ActiveWorkBook.Lembaran ("Sheet1")

Sekarang, ulangi prosesnya lagi, buat butang tekan kedua. Buat kapsyen Kirakan cukai 7%.

Klik dua kali butang itu dan tampal kod yang sama, tetapi buat pengganda cukai 0.07.

Sekarang, bergantung kepada butang mana yang anda tekan, lajur cukai akan dikira dengan sewajarnya.

Sebaik sahaja anda selesai, anda akan mempunyai kedua -dua butang tekan pada helaian anda. Setiap daripada mereka akan memulakan pengiraan cukai yang berbeza dan akan menulis hasil yang berbeza ke dalam lajur hasil.

Untuk teks ini, pilih Pemaju menu, dan pilih Mod reka bentuk membentuk kumpulan kawalan dalam reben untuk melumpuhkan Mod reka bentuk. Ini akan mengaktifkan butang tekan.

Cuba pilih setiap butang tekan untuk melihat bagaimana lajur hasil "cukai" berubah.

Hasil pengiraan e -mel kepada seseorang

Bagaimana jika anda ingin menghantar hasil pada hamparan kepada seseorang melalui e -mel?

Anda boleh membuat butang lain yang dipanggil Lembaran E -mel ke Boss Menggunakan prosedur yang sama di atas. Kod untuk butang ini akan melibatkan menggunakan objek CDO Excel untuk mengkonfigurasi tetapan e-mel SMTP, dan menghantar e-mel kepada hasil dalam format yang boleh dibaca pengguna.

Untuk membolehkan ciri ini, anda perlu memilih Alat dan rujukan. Tatal ke bawah Microsoft CDO untuk Perpustakaan Windows 2000, membolehkannya, dan pilih okey.

Terdapat tiga bahagian utama untuk kod yang anda perlu buat untuk menghantar e -mel dan membenamkan hasil spreadsheet.

Yang pertama adalah menubuhkan pembolehubah untuk memegang subjek, ke dan dari alamat, dan badan e -mel.

Redam cdo_mail sebagai objek
Dim cdo_config sebagai objek
Dim smtp_config sebagai varian
Rentap strsubject sebagai rentetan
Dim strfrom sebagai rentetan
Dim strto sebagai rentetan
Dim strcc sebagai rentetan
Dim strbcc sebagai rentetan
Dim strody sebagai rentetan
Lastrow redup selagi
Dim Startcell sebagai julat
Dim Rowcounter sebagai Integer
Rng rng sebagai julat, sel sebagai julat
Dim flttax sebagai dua kali ganda

Tetapkan WS = ActiveWorkBook.Lembaran ("Sheet1")
strsubject = "Cukai membayar suku ini"
strfrom = "[email protected] "
strto = "[email protected] "
strcc = ""
strbcc = ""
strody = "Berikut adalah pecahan cukai yang dibayar atas jualan suku ini."

Sudah tentu, badan perlu dinamik bergantung pada hasil yang ada di dalam lembaran, jadi di sini anda perlu menambah gelung yang melalui julat, mengekstrak data, dan menulis garis pada satu masa ke badan.

Tetapkan startCell = julat ("a1") 'Cari baris terakhir dan lajur lastrow = ws.Sel (Ws.Baris.Kira, permulaan.Kolum).Akhir (xlup).Set baris rng = ws.Julat (WS.Sel (2, 4), WS.Sel (lastrow, 4)) rowcounter = 2 strody = strody & vbcrlf untuk setiap sel dalam rng strody = strbody & vbcrlf strbody = strbody & "we sol" & sel (RowCounter, 3).Nilai & "Of" & Sel (RowCounter, 1).Nilai _ & "untuk" & sel (RowCounter, 4).Nilai & "dan cukai berbayar" & sel (RowCounter, 5).Nilai & "."RowCounter = RowCounter + 1 sel seterusnya

Bahagian seterusnya melibatkan menubuhkan tetapan SMTP supaya anda dapat menghantar e -mel melalui pelayan SMTP anda. Jika anda menggunakan Gmail, ini biasanya alamat e -mel gmail anda, kata laluan gmail anda, dan pelayan smtp gmail (SMTP.gmail.com).

Tetapkan cdo_mail = createObject ("CDO.Mesej ") pada ralat goto error_handling set cdo_config = createObject (" cdo.Konfigurasi ") cdo_config.LOAD -1 SET SMTP_CONFIG = CDO_CONFIG.Medan dengan smtp_config .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/sendusing ") = 2 .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/smtpserver ") =" SMTP.gmail.com " .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/smtpauthenticate ") = 1 .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/sendusername ") =" e -mel@laman web.com " .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/sendpassword ") =" kata laluan " .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/smtpserverport ") = 465 .Item ("http: // skema.Microsoft.com/cdo/konfigurasi/smtpusessl ") = Benar .Kemas kini akhir dengan set cdo_mail .Konfigurasi = CDO_CONFIG Tamat dengan

Ganti e -mel@laman web.com dan kata laluan dengan maklumat akaun anda sendiri.

Akhirnya, untuk memulakan hantar e -mel, masukkan kod berikut.

Cdo_mail.Subjek = strSubject
Cdo_mail.Dari = strfrom
Cdo_mail.Ke = strto
Cdo_mail.TextBody = Strody
Cdo_mail.Cc = strcc
Cdo_mail.Bcc = strbcc
Cdo_mail.Hantar
Error_handling:
Jika err.Penerangan "" Kemudian msgbox err.Penerangan

Catatan: Sekiranya anda melihat ralat pengangkutan ketika cuba menjalankan kod ini, kemungkinan besar kerana akaun Google anda menyekat "aplikasi kurang selamat" dari berjalan. Anda perlu melawat halaman tetapan aplikasi yang kurang selamat dan menghidupkan ciri ini.

Setelah itu diaktifkan, e -mel anda akan dihantar. Inilah yang kelihatan seperti orang yang menerima e -mel hasil yang dihasilkan secara automatik.

Seperti yang anda dapat lihat terdapat banyak yang anda boleh mengautomasikan dengan Excel VBA. Cuba bermain -main dengan coretan kod yang anda pelajari dalam artikel ini dan buat automasi VBA anda sendiri.