Panduan VBA tingkat lanjut untuk MS Excel

Jika Anda baru memulai dengan VBA, maka Anda ingin mulai mempelajari panduan VBA kami untuk pemula. Tetapi jika Anda seorang ahli VBA berpengalaman dan Anda mencari hal -hal yang lebih canggih yang dapat Anda lakukan dengan VBA di Excel, maka teruslah membaca.

Kemampuan untuk menggunakan pengkodean VBA di Excel membuka seluruh dunia otomatisasi. Anda dapat mengotomatiskan perhitungan di Excel, pushbuttons, dan bahkan mengirim email. Ada lebih banyak kemungkinan untuk mengotomatiskan pekerjaan sehari -hari Anda dengan VBA daripada yang mungkin Anda sadari.

Ini menetapkan lembar kerja aktif menjadi Sheet1, menghapus lembar, menghubungkan ke file menggunakan jalur file yang Anda tentukan dengan Strfile variabel, dan kemudian Dengan Siklus loop melalui setiap baris dalam file dan menempatkan data ke dalam lembar mulai dari sel A1.

Jika Anda menjalankan kode ini, Anda akan melihat bahwa data file CSV diimpor ke spreadsheet kosong Anda, di dalam Sheet1.

Mengimpor hanyalah langkah pertama. Selanjutnya, Anda ingin membuat header baru untuk kolom yang akan berisi hasil perhitungan Anda. Dalam contoh ini, katakanlah Anda ingin menghitung pajak 5% yang dibayarkan atas penjualan setiap item.

Urutan tindakan yang harus diambil kode Anda adalah:

1. Buat kolom hasil baru yang disebut pajak.
2. Lingkarkan melalui unit dijual kolom dan hitung pajak penjualan.
3. Tulis hasil perhitungan ke baris yang sesuai di lembar.

Kode berikut akan mencapai semua langkah ini.

Redup lastrow selama
Redup startcell sebagai jarak
Red rowcounter sebagai bilangan bulat
Redup rng sebagai rentang, sel sebagai rentang
Redup flttax sebagai ganda
Set startcell = range ("a1")
'Temukan baris dan kolom terakhir
LASTROW = WS.Sel (WS.Baris.Hitung, mulai.Kolom).End (xlup).Baris
Atur rng = ws.Rentang (WS.Sel (2, 4), WS.Sel (Lastrow, 4))
RowCounter = 2
Sel (1, 5) = "pajak"
Untuk setiap sel di RNG
flttax = sel.Nilai * 0.05
Sel (rowcounter, 5) = flttax
rowcounter = rowcounter + 1
Sel berikutnya

Kode ini menemukan baris terakhir dalam lembar data Anda, dan kemudian menetapkan kisaran sel (kolom dengan harga penjualan) sesuai dengan baris data pertama dan terakhir. Kemudian kode loop melalui masing -masing sel tersebut, melakukan perhitungan pajak dan menulis hasilnya ke kolom baru Anda (kolom 5).

Tempel kode VBA di atas di bawah kode sebelumnya, dan jalankan skrip. Anda akan melihat hasilnya muncul di kolom e.

Sekarang, setiap kali Anda membuka lembar kerja Excel Anda, secara otomatis akan keluar dan mendapatkan salinan data segar dari file CSV. Kemudian, itu akan melakukan perhitungan dan menulis hasilnya ke lembar. Anda tidak perlu melakukan apa pun secara manual lagi!

Hitung hasil dari tekan tombol

Jika Anda lebih suka memiliki lebih banyak kontrol langsung atas saat perhitungan berjalan, daripada berjalan secara otomatis saat lembar terbuka, Anda dapat menggunakan tombol kontrol sebagai gantinya.

Tombol kontrol berguna jika Anda ingin mengontrol perhitungan mana yang digunakan. Misalnya, dalam kasus yang sama seperti di atas, bagaimana jika Anda ingin menggunakan tarif pajak 5% untuk satu wilayah, dan tarif pajak 7% untuk yang lain?

Anda dapat mengizinkan kode impor CSV yang sama berjalan secara otomatis, tetapi meninggalkan kode perhitungan pajak untuk dijalankan saat Anda menekan tombol yang sesuai.

Menggunakan spreadsheet yang sama seperti di atas, pilih Pengembang tab, dan pilih Menyisipkan dari Kontrol Kelompok di pita. Pilih tekan tombol Kontrol ActiveX dari menu dropdown.

Gambarlah tombol ke bagian mana pun dari lembar jauh dari ke mana data akan pergi.

Klik kanan tombol tekan, dan pilih Properti. Di jendela Properties, ubah judul ke apa yang ingin Anda tampilkan kepada pengguna. Dalam hal ini mungkin Hitung pajak 5%.

Anda akan melihat teks ini tercermin pada tombol tekan itu sendiri. Tutup properti jendela, dan klik dua kali pushbutton itu sendiri. Ini akan membuka jendela editor kode, dan kursor Anda akan berada di dalam fungsi yang akan berjalan saat pengguna menekan pushbutton.

Tempel Kode Perhitungan Pajak dari bagian di atas ke fungsi ini, menjaga pengganda tarif pajak di 0.05. Ingatlah untuk memasukkan 2 baris berikut untuk menentukan lembar aktif.

Redup sebagai lembar kerja, strfile sebagai string
Atur WS = ActiveWorkbook.Lembar ("sheet1")

Sekarang, ulangi prosesnya lagi, buat tombol tekan kedua. Membuat keterangannya Hitung pajak 7%.

Klik dua kali tombol itu dan tempel kode yang sama, tetapi buat pengganda pajak 0.07.

Sekarang, tergantung tombol mana yang Anda tekan, kolom pajak akan dihitung sesuai.

Setelah selesai, Anda akan memiliki kedua tombol tekan di lembar Anda. Masing -masing akan memulai perhitungan pajak yang berbeda dan akan menulis hasil yang berbeda ke kolom hasil.

Untuk mengirim SMS ini, pilih Pengembang menu, dan pilih Mode desain membentuk kelompok kontrol di pita untuk menonaktifkan Mode desain. Ini akan mengaktifkan tombol tekan.

Coba pilih setiap tombol tekan untuk melihat bagaimana hasil kolom “pajak” berubah.

Hasil Perhitungan Email untuk Seseorang

Bagaimana jika Anda ingin mengirimkan hasilnya di spreadsheet ke seseorang melalui email?

Anda bisa membuat tombol lain yang disebut Lembar email ke bos Menggunakan prosedur yang sama di atas. Kode untuk tombol ini akan melibatkan penggunaan objek Excel CDO untuk mengonfigurasi pengaturan email SMTP, dan mengirim email hasilnya dalam format yang dapat dibaca pengguna.

Untuk mengaktifkan fitur ini, Anda harus memilih Alat dan referensi. Gulir ke bawah ke Microsoft CDO untuk Windows 2000 Library, aktifkan, dan pilih OKE.

Ada tiga bagian utama untuk kode yang perlu Anda buat untuk mengirimkan email dan menanamkan hasil spreadsheet.

Yang pertama adalah menyiapkan variabel untuk menahan subjek, ke dan dari alamat, dan badan email.

Redup cdo_mail sebagai objek
Redup cdo_config sebagai objek
Redup smtp_config sebagai varian
Redup strsubject sebagai string
Redup strfrom sebagai string
Redup strto sebagai string
Redup strc sebagai string
Redup strbcc sebagai string
Dimbody Dim sebagai String
Redup lastrow selama
Redup startcell sebagai jarak
Red rowcounter sebagai bilangan bulat
Redup rng sebagai rentang, sel sebagai rentang
Redup flttax sebagai ganda

Atur WS = ActiveWorkbook.Lembar ("sheet1")
strsubject = "pajak dibayar kuartal ini"
strfrom = "[email protected] "
strto = "[email protected] "
strcc = ""
strbcc = ""
Strbody = "Berikut ini adalah rincian pajak yang dibayarkan atas penjualan kuartal ini."

Tentu saja, tubuh harus dinamis tergantung pada hasil apa yang ada dalam lembar, jadi di sini Anda harus menambahkan lingkaran yang melewati jangkauan, mengekstrak data, dan menulis garis pada satu waktu ke tubuh.

Setel startcell = range ("a1") 'temukan baris terakhir dan kolom lastrow = ws.Sel (WS.Baris.Hitung, mulai.Kolom).End (xlup).Set baris rng = ws.Rentang (WS.Sel (2, 4), WS.Sel (lastrow, 4)) rowcounter = 2 strbody = strbody & vbcrlf untuk setiap sel dalam rng strbody = strbody & vbcrlf strbody = strbody & "kami menjual" & sel (rowcounter, 3).Nilai & "& Sel (Rowcounter, 1).Nilai _ & "untuk" & sel (RowCounter, 4).Nilai & "dan pajak berbayar" & sel (Rowcounter, 5).Nilai & "."RowCounter = RowCounter + 1 sel berikutnya

Bagian selanjutnya melibatkan pengaturan pengaturan SMTP sehingga Anda dapat mengirim email melalui server SMTP Anda. Jika Anda menggunakan Gmail, ini biasanya alamat email Gmail Anda, kata sandi Gmail Anda, dan server SMTP Gmail (SMTP.Gmail.com).

Setel cdo_mail = createObject ("cdo.Pesan ") di error goto error_handling set cdo_config = createObject (" cdo.Konfigurasi ") cdo_config.Muat -1 Set SMTP_Config = CDO_CONFIG.Bidang dengan SMTP_Config .Item ("http: // skema.Microsoft.com/cdo/configuration/sendusing ") = 2 .Item ("http: // skema.Microsoft.com/cdo/configuration/smtpserver ") =" smtp.Gmail.com " .Item ("http: // skema.Microsoft.com/cdo/configuration/smtpAuthenticate ") = 1 .Item ("http: // skema.Microsoft.com/cdo/configuration/sendusername ") =" email@situs web.com " .Item ("http: // skema.Microsoft.com/cdo/configuration/sendpassword ") =" kata sandi " .Item ("http: // skema.Microsoft.com/cdo/configuration/smtpserverport ") = 465 .Item ("http: // skema.Microsoft.com/cdo/configuration/smtpusessl ") = true .Perbarui Akhir Dengan dengan Set CDO_Mail .Konfigurasi = cdo_config end dengan

Ganti email@situs web.com dan kata sandi dengan detail akun Anda sendiri.

Akhirnya, untuk memulai email mengirim, masukkan kode berikut.

Cdo_mail.Subjek = STRSubject
Cdo_mail.Dari = strfrom
Cdo_mail.Untuk = strto
Cdo_mail.Textbody = Strbody
Cdo_mail.CC = STRCC
Cdo_mail.Bcc = strbcc
Cdo_mail.Mengirim
ERROR_HANDLING:
Jika err.Deskripsi "" Lalu msgbox err.Keterangan

Catatan: Jika Anda melihat kesalahan transportasi saat mencoba menjalankan kode ini, kemungkinan karena akun Google Anda memblokir "aplikasi yang kurang aman" dari berjalan. Anda harus mengunjungi halaman Pengaturan Aplikasi yang kurang aman dan menyalakan fitur ini.

Setelah itu diaktifkan, email Anda akan dikirim. Inilah yang terlihat seperti orang yang menerima email hasil yang Anda hasilkan secara otomatis.

Seperti yang Anda lihat ada banyak hal yang sebenarnya dapat Anda otomatisasi dengan Excel VBA. Cobalah bermain -main dengan cuplikan kode yang telah Anda pelajari di artikel ini dan buat otomatisasi VBA unik Anda sendiri.