Eğer VBA'ya yeni başlıyorsanız, öncelikle VBA Başlangıç Kılavuzu'nu incelemeniz önerilir . Ancak Excel'de VBA ile daha gelişmiş şeyler yapmak isteyen deneyimli bir VBA kullanıcısıysanı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 menülerinden Çalışma Sayfası'nı seçin ve Etkinleştir'i seçin . Bu, elektronik tabloyu her açtığınızda kodu çalıştıracaktır.
Bu, bir Sub Worksheet_Activate() fonksiyonu oluşturacaktır . Yukarıdaki kodu bu fonksiyona yapıştırın.
Bu işlem, etkin çalışma sayfasını Sheet1 olarak ayarlar, sayfayı tarar, strFile değişkeniyle belirttiğiniz dosya yolunu kullanarak dosyaya bağlanır ve ardından With döngüsü dosyadaki her satırı tek tek inceleyerek verileri A1 hücresinden başlayarak sayfaya yerleştirir.
Bu kodu çalıştırdığınızda, CSV dosyasındaki verilerin boş elektronik tabloya, Sheet1 sayfasına aktarıldığını göreceksiniz.
İç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 bahsedilen aynı elektronik tabloyu kullanarak, Geliştirici sekmesini seçin ve ardından şeritteki Denetimler grubundan Ekle'yi seçin. Açılır menüden ActiveX Düğme Denetimi'ni 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 Özellikler'i seçin . Özellikler penceresinde, Başlık'ı kullanıcıya göstermek istediğiniz metinle değiştirin. Bu durumda, " Vergi %5 Hesapla" olabilir.
Bu metni düğmenin üzerinde göreceksiniz. Özellikler penceresini kapatın ve düğmeye çift tıklayın. Bu, bir kod düzenleyici penceresi açacak ve imleç, kullanıcının düğmeye 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 tekrar edin ve ikinci bir düğme oluşturun. Butonun adını " %7 Vergi Hesaplaması" olarak belirleyin.
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 Geliştirici menüsünü seçin ve ardından şeritteki kontroller grubundan Tasarım Modu'nu seçerek Tasarım Modu'nu devre dışı bırakın . Bu, 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?
Yukarıda açıklanan aynı prosedürü kullanarak "Patrona E-posta Gönder" adlı başka bir düğme oluşturabilirsiniz . Bu düğmenin kodu, SMTP e-posta ayarlarını yapılandırmak ve sonuçları kullanıcı dostu bir biçimde e-posta yoluyla göndermek için bir Excel CDO nesnesi kullanacaktır.
Bu özelliği etkinleştirmek için Araçlar ve Referanslar'ı seçin . Aşağı kaydırarak Microsoft CDO for Windows 2000 Kitaplığı'nı bulun , etkinleştirin ve Tamam'ı seçin.
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
email@website.com ve password kısımlarını kendi hesap bilgilerinizle değiştirin .
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
Not : 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" seçeneğini engelliyordur. Daha az güvenli uygulamalar ayarları sayfasına gidip bu seçeneği etkinleştirmeniz gerekecektir.
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ı.