VBA'yı yeni kullanmaya başlıyorsanız, öncelikle bazı konuları öğrenmeniz gerekecek. VBA'ya yeni başlayanlar için bir rehberAncak, eğer deneyimli bir VBA uzmanıysanız ve Excel'de VBA ile yapabileceğiniz daha gelişmiş şeyleri arıyorsanız, okumaya devam edin.
Excel'de VBA işaretleme dilini kullanabilme özelliği, otomasyonun kapılarını ardına kadar açıyor. Excel hesaplamalarını, düğmeleri ve hatta e-posta göndermeyi bile otomatikleştirebilirsiniz. VBA ile günlük işlerinizi otomatikleştirmenin sandığınızdan çok daha fazla olanağı var.
Microsoft Excel için Gelişmiş VBA Kılavuzu
Excel'de VBA kodu yazmanın temel amacı, bir elektronik tablodan bilgi çıkarmanızı, üzerinde çeşitli hesaplamalar yapmanızı ve ardından sonuçları tekrar elektronik tabloya yazmanızı sağlamaktır.
Aşağıda Excel'de VBA'nın en yaygın kullanım alanları listelenmiştir.
- Verileri içe aktarma ve hesaplamalar yapma
- Kullanıcı buton tıklamalarına göre sonuçların hesaplanması
- Hesaplama sonuçlarını birine e-posta yoluyla göndermek
Bu üç örneği kullanarak, çeşitli gelişmiş Excel VBA kodları yazabilirsiniz.
Verileri içe aktarma ve hesaplamalar yapma
İnsanların Excel'i en sık kullandığı amaçlardan biri, Excel dışındaki veriler üzerinde hesaplamalar yapmaktır. Eğer VBA kullanmıyorsanız, bu, verileri manuel olarak içe aktarmanız, hesaplamaları yapmanız ve bu değerleri başka bir sayfaya veya rapora aktarmanız gerektiği anlamına gelir.
VBA kullanarak tüm süreci otomatikleştirebilirsiniz. Örneğin, her Pazartesi bilgisayarınızdaki bir dizine yeni bir CSV dosyası indiriliyorsa, VBA kodunuzu Salı sabahı elektronik tabloyu ilk açtığınızda çalışacak şekilde yapılandırabilirsiniz.
Aşağıdaki içe aktarma kodu çalıştırılacak ve CSV dosyası Excel elektronik tablonuza aktarılacaktır.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Excel VBA düzenleyicisini açın ve Sheet1 nesnesini seçin. Nesne ve Yöntem açılır listelerinden seçim yapın. Çalışma Sayfası و etkinleştirmekBu, elektronik tabloyu her açtığınızda kodun çalışmasına neden olacaktır.
Bu bir fonksiyon oluşturacak. Alt Çalışma Sayfası_Etkinleştir()Yukarıdaki kodu bu fonksiyona yapıştırın.
Bu, etkin çalışma sayfasını şu şekilde ayarlar: Sheet1Sayfayı tarayın ve değişkende belirttiğiniz dosya yolunu kullanarak dosyaya bağlanın. strFileSonra döngü İle Dosyadaki her satırı tek tek inceleyerek ve verileri A1 hücresinden başlayarak sayfaya yerleştirerek.
Bu kodu çalıştırdığınızda, CSV dosyasındaki verilerin boş elektronik tabloya aktarıldığını göreceksiniz. Sheet1.
İçe aktarma sadece ilk adım. Ardından, hesaplamalarınızın sonuçlarını içerecek yeni bir sütun başlığı oluşturmak isteyeceksiniz. Bu örnekte, her bir ürünün satışında ödenen %5 vergiyi hesaplamak istediğinizi varsayalım.
Kodunuzun gerçekleştirmesi gereken eylemlerin sırası şu şekildedir:
- Yeni bir sonuç sütunu oluşturun ve ona adını verin. vergiler.
- Bir sütun boyunca tekrarlayın. satılan birimler Ve satış vergisini hesaplayın.
- Hesaplama sonuçlarını kağıdın ilgili satırına yazın.
Aşağıdaki kod tüm bu adımları gerçekleştirecektir.
Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double Set StartCell = Range("A1") 'Son Satır ve Sütunu Bul LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Cells(1, 5) = "taxes" For Each cell In rng fltTax = cell.Value * 0.05 Cells(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Next cell
Bu kod, veri sayfanızın son satırını bulur, ardından hücre aralığını (satış fiyatlarını içeren sütun) verilerin ilk ve son satırlarına göre ayarlar. Kod daha sonra bu hücrelerin her birinde döngü yaparak vergiyi hesaplar ve sonuçları yeni sütununuza (5. sütun) yazar.
Yukarıdaki VBA kodunu önceki kodun altına yapıştırın ve betiği çalıştırın. Sonuçlar E sütununda görünecektir.
Artık Excel çalışma sayfanızı her açtığınızda, program otomatik olarak CSV dosyasından verilerin en son sürümünü alacak. Ardından hesaplamaları yapacak ve sonuçları çalışma sayfasına yazacaktır. Artık hiçbir şeyi manuel olarak yapmanıza gerek kalmayacak!
Sonuçları hesaplamak için düğmeye basın.
Hesaplamaların çalışma sayfası açıldığında otomatik olarak çalıştırılması yerine, ne zaman çalıştırılacağı konusunda doğrudan kontrol sahibi olmayı tercih ediyorsanız, bunun yerine bir kontrol düğmesi kullanabilirsiniz.
Hesaplamaları kontrol etmek istediğinizde kontrol düğmeleri kullanışlıdır. Örneğin, yukarıda bahsedilen aynı senaryoda, bir bölge için %5, diğer bölge için ise %7 vergi oranı kullanmak isteseydiniz ne olurdu?
Aynı CSV içe aktarma kodunun otomatik olarak çalışmasına izin verebilirsiniz, ancak vergi hesaplama kodunun ilgili düğmeye basıldığında çalışmasını sağlayabilirsiniz.
Yukarıda belirtilen aynı veri tablosunu kullanarak sekmeyi seçin. GeliştiriciVe seçin Ekle Bir gruptan Kontroller Şerit menüden bir kontrol seçin. butona basınız Açılır menüden ActiveX'i seçin.
Veri girişinin olmayacağı bir yere, kağıdın herhangi bir yerine basma düğmesini çizin.
Düğmeye sağ tıklayın ve seçin. EmlaklarÖzellikler penceresinde, Başlığı kullanıcıya göstermek istediğiniz şekilde değiştirin. Bu durumda, başlık şöyle olabilir: %5 Vergi Hesapla.
Bu metni düğmenin üzerinde göreceksiniz. Pencereyi kapat ÖzelliklerAynı butona çift tıklayın. Bu, bir kod düzenleyici penceresi açacak ve imleç, kullanıcının butona tıkladığında çalıştırılacak fonksiyonun içinde olacaktır.
Yukarıdaki bölümden aldığınız vergi hesaplama kodunu, vergi oranı çarpanını 0.05'te bırakarak bu fonksiyona yapıştırın. Etkin sayfayı tanımlamak için aşağıdaki iki satırı eklemeyi unutmayın.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")
Şimdi işlemi tekrarlayın ve ikinci bir düğme oluşturun. Ona bir etiket verin. %7 vergi hesaplaması.
Bu butona çift tıklayın ve aynı kodu yapıştırın, ancak vergi çarpanını 0.07 yapın.
Şimdi, hangi butona bastığınıza bağlı olarak vergi sütunu buna göre hesaplanacaktır.
İşlem tamamlandığında, sayfanızda her iki düğme de yer alacaktır. Her biri farklı bir vergi hesaplaması başlatacak ve sonuç sütununda farklı sonuçlar gösterecektir.
Bunu test etmek için bir liste seçin. GeliştiriciVe seçin Tasarım modu Şerit üzerindeki kontroller grubundan devre dışı bırakılacaklar Tasarım moduBu, düğmeleri etkinleştirecektir.
Her bir düğmeyi seçerek "Vergiler" sonuç sütunundaki değişiklikleri inceleyin.
Hesap sonuçlarını birine e-posta ile göndermek
Peki ya hesap tablosundaki sonuçları birine e-posta yoluyla göndermek isteseydiniz?
Başka bir düğme oluşturabilirsiniz. Patrona E-posta Gönder Yukarıda açıklanan aynı prosedürü kullanarak, bu düğmenin kodu, SMTP e-posta ayarlarını yapılandırmak ve sonuçları kullanıcı dostu bir biçimde e-postayla göndermek için bir Excel CDO nesnesi kullanmayı içerecektir.
Bu özelliği etkinleştirmek için seçim yapmanız gerekmektedir. Araçlar ve Referanslar. Aşağı kaydır Microsoft CDO for Windows 2000 LibraryEtkinleştirin ve seçin. OK.
E-posta göndermek ve elektronik tablo sonuçlarını eklemek için oluşturmanız gereken üç ana kod bölümü vardır.
İlk adım, konu başlığını, "alıcı" ve "gönderen" adreslerini ve e-posta metnini saklamak için değişkenler oluşturmaktır.
Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1") strSubject = "Bu Çeyrekte Ödenen Vergiler" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Aşağıda, bu çeyrekte yapılan satışlardan ödenen vergilerin dökümü yer almaktadır."
Elbette, metnin makaledeki sonuçlara bağlı olarak dinamik olması gerekiyor, bu nedenle burada aralığı tarayan, verileri çıkaran ve her seferinde metne bir satır yazan bir döngü eklemeniz gerekecek.
StartCell = Range("A1"). vbCrLf For Each cell In rng strBody = strBody & vbCrLf strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _ & " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Next cell
Sonraki bölümde, SMTP sunucunuz üzerinden e-posta gönderebilmeniz için SMTP kurulumu ele alınmaktadır. Gmail kullanıyorsanız, bu genellikle Gmail e-posta adresiniz, Gmail şifreniz ve Gmail SMTP sunucunuzdur (smtp.gmail.com).
CDO_Mail = CreateObject("CDO.Message") On Error GoTo Error_Handling CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Set SMTP_Config = CDO_Config.Fields With SMTP_Config .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Set .Configuration = CDO_Config End With
değiştirmek email@website.com Şifreniz hesap bilgilerinizi içerir.
Son olarak, e-postayı göndermeye başlamak için aşağıdaki kodu girin.
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.Description <> "" Then MsgBox Err.Description
ملاحظةBu kodu çalıştırmaya çalışırken bir aktarım hatası görürseniz, Google hesabınız büyük olasılıkla "daha az güvenli uygulamaların" çalışmasını engelliyor demektir. [link/reference] adresini ziyaret etmeniz gerekecektir. Daha az güvenli uygulamalar ayarları sayfası Ve bu seçeneği etkinleştirin.
Bu özelliği etkinleştirdikten sonra e-postanız gönderilecektir. Otomatik olarak oluşturulan e-postayı alan kişi için e-posta şu şekilde görünecektir.
Gördüğünüz gibi, Excel VBA kullanarak aslında birçok şeyi otomatikleştirebilirsiniz. Bu makalede öğrendiğiniz kod parçacıklarıyla oynayarak kendi benzersiz VBA otomasyonlarınızı oluşturun.
Yoruma kapalı.