VBA'ya yeni başlıyorsanız, Yeni başlayanlar için VBA kılavuzu 'ımızı incelemeye başlamak istersiniz. Ancak deneyimli bir VBA uzmanıysanız ve Excel'deki VBA ile yapabileceğiniz daha gelişmiş şeyler arıyorsanız, okumaya devam edin.
Excel'de VBA kodlamasını kullanma yeteneği tüm dünyayı açar otomasyon. Excel'de, butonlarda hesaplamaları otomatikleştirebilir ve hatta e-posta gönderebilirsiniz. VBA ile günlük çalışmanızı otomatikleştirebileceğinizden daha fazla olasılık var.
Microsoft Excel İçin Gelişmiş VBA Kılavuzu
Excel'de VBA kodu yazmanın temel amacı, bilgi alabilmenizdir bir e-tablodan çeşitli hesaplamalar yapın ve sonuçları tekrar e-tabloya yazın
VBA'nın Excel'deki en yaygın kullanımları aşağıdadır.
Bu üç örnekle, kendi gelişmiş Excel VBA kodunuzu yazabilirsiniz.
Verileri İçe Aktarma ve Hesaplamalar Yapma
İnsanların Excel için en yaygın olarak kullandıkları şeylerden biri Excel dışında bulunan veriler üzerinde hesaplamalar yapıyor. VBA kullanmıyorsanız, bu, verileri manuel olarak içe aktarmanız, hesaplamaları çalıştırmanız ve bu değerleri başka bir sayfaya veya rapora vermeniz gerektiği anlamına gelir.
In_content_1 all: [300x250] / dfp [640x360]->VBA ile tüm işlemi otomatikleştirebilirsiniz. Örneğin, her Pazartesi bilgisayarınızdaki bir dizine indirilen yeni bir CSV dosyanız varsa, VBA kodunuzu e-tablonuzu Salı sabahı ilk açtığınızda çalışacak şekilde yapılandırabilirsiniz.
Aşağıdaki içe aktarma kodu CSV dosyasını çalıştırın ve Excel e-tablonuza alın.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Excel VBA düzenleme aracını açın ve Sheet1 nesnesini seçin. Nesne ve yöntem açılır kutularından Çalışma Sayfasıve Etkinleştir'i seçin. Bu, e-tabloyu her açtığınızda kodu çalıştırır.
Bu bir Alt Çalışma Sayfası_Activate ()işlevi oluşturur. Yukarıdaki kodu bu işleve yapıştırın.
Etkin çalışma sayfasını Sayfa1olarak ayarlar, sayfayı temizler, strFiledeğişkeniyle tanımladığınız dosya yolunu kullanarak dosyaya bağlanır ve ardından İledöngü, dosyadaki her satırda dolaşır ve verileri A1 hücresinden başlayarak sayfaya yerleştirir.
Bu kodu çalıştırırsanız, CSV dosya verilerinin boş e-tablonuza Sayfa1'e aktarılır.
İçe aktarma yalnızca ilk adımdır . Ardından, hesaplama sonuçlarınızı içerecek sütun için yeni bir başlık oluşturmak istiyorsunuz. Bu örnekte, her bir öğenin satışı için ödenen% 5 vergiyi hesaplamak istediğinizi varsayalım.
Kodunuzun gerçekleştirmesi gereken işlem sırası:
Aşağıdaki kod bu adımların tümünü 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")
'Find Last Row and Column
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 son satırı bulur ve ardından hücre aralığını (satış fiyatlarının bulunduğu sütun) ilk ve son veri satırına göre ayarlar. Daha sonra kod bu hücrelerin her birinde dolaşır, vergi hesaplamasını yapar ve sonuçları yeni sütununuza yazar (sütun 5).
Yukarıdaki VBA kodunu önceki kodun altına yapıştırın ve komut dosyasını çalıştırın. Sonuçların E sütununda göründüğünü göreceksiniz.
Şimdi, Excel çalışma sayfanızı her açtığınızda otomatik olarak söner ve CSV dosyasından en yeni veri kopyasını alır. Ardından, hesaplamaları yapar ve sonuçları sayfaya yazar. Artık manuel olarak hiçbir şey yapmanıza gerek yok!
Düğmeden Sonuçları Hesapla Basın
Hesaplamalar çalıştırıldığında daha fazla doğrudan kontrol sahibi olmayı tercih ediyorsanız , sayfa açıldığında otomatik olarak çalıştırmak yerine, bunun yerine bir kontrol düğmesi kullanabilirsiniz.
Hangi hesaplamaların kullanıldığını kontrol etmek istiyorsanız kontrol düğmeleri kullanışlıdır. Örneğin, yukarıdaki gibi aynı durumda, bir bölge için% 5 vergi ve başka bir bölge için% 7 vergi oranı kullanmak isterseniz ne olur?
Aynı CSV içe aktarma kodunun otomatik olarak çalıştırın, ancak ilgili düğmeye bastığınızda vergi hesaplama kodunu çalışır durumda bırakın.
Yukarıdaki ile aynı elektronik tabloyu kullanarak Geliştiricisekmesini seçin ve Ekle'yi seçinşeridindeki Denetimlergrubundan. Açılır menüden basma düğmesiActiveX Denetimi'ni seçin.
Butonu sayfanın herhangi bir verinin gideceği yerden uzağa çekin.
Basma düğmesini sağ tıklayın ve Özellikler'i seçin. Özellikler penceresinde, Altyazıyı kullanıcıya göstermek istediğiniz şeye değiştirin. Bu durumda, % 5 Vergi Hesaplaolabilir.
Bu metni basma düğmesinin kendisine yansıtıldığını göreceksiniz. özelliklerpenceresini kapatın ve düğmenin kendisine çift tıklayın. Bu, kod düzenleyici penceresini açacak ve imleciniz, kullanıcı düğmeye bastığında çalışacak olan işlevin içinde olacaktır.
Vergi oranı çarpanını 0,05'te tutarak, yukarıdaki bölümden vergi hesaplama kodunu bu işleve yapıştırın. Etkin sayfayı tanımlamak için aşağıdaki 2 satırı eklemeyi unutmayın.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Şimdi, ikinci bir basma düğmesi oluşturarak işlemi tekrarlayın. Altyazıyı % 7 Vergi Hesaplayapın.
Bu düğmeyi çift tıklayın ve aynı kodu yapıştırın, ancak vergi çarpanını 0.07 yapın.
Şimdi, hangi düğmeye bastığınıza bağlı olarak vergi sütunu buna göre hesaplanmalıdır.
İşiniz bittiğinde, sayfanızda her iki düğme de bulunur. Her biri farklı bir vergi hesaplaması başlatacak ve sonuç sütununa farklı sonuçlar yazacaktır.
Kısa mesaj göndermek için Geliştiricimenüsünü seçin ve Tasarım ModuTasarım Modu'nu seçin. strong>. Bu, basma düğmelerini etkinleştirecektir.
“Vergiler” sonuç sütununun nasıl değiştiğini görmek için her bir basma düğmesini seçmeyi deneyin.
Hesaplama Sonuçlarını Birine E-postayla Gönder
Ne e-tablodaki sonuçları e-postayla birine göndermek istiyorsanız?
Yukarıdaki aynı prosedürü kullanarak Sayfayı Patrona E-postalaadlı başka bir düğme oluşturabilirsiniz. Bu düğmenin kodu, SMTP e-posta ayarlarını yapılandırmak için Excel CDO nesnesini kullanmayı ve sonuçları kullanıcı tarafından okunabilir bir biçimde e-postayla göndermeyi içerecektir.
Bu özelliği etkinleştirmek için Araçlar ve Kaynaklar. Windows 2000 Kitaplığı için Microsoft CDO'ya gidin, etkinleştirin ve Tamam' ı seçin.
<şekil sınıfı = "tembel wp-blok resmi">
E-posta göndermek ve e-tablo sonuçlarını gömmek için kodun oluşturmanız gereken üç ana bölüm vardır.
Birincisi, değişkenleri tutmak için ayarlamaktır konu, Kime ve Kimden adresleri ve e-posta gövdesi.
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 = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Elbette, vücudun hangi sonuçlara bağlı olarak dinamik olması gerekir. burada, aralıktan geçen, verileri ayıklayan ve gövdeye her seferinde bir satır yazan bir döngü eklemeniz gerekir.
Set StartCell = Range("A1")
'Find Last Row and Column
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
strBody = strBody & 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
Bir sonraki bölüm, SMTP sunucunuz üzerinden e-posta gönderebilmeniz için SMTP ayarlarının yapılmasını içerir. Gmail kullanıyorsanız, bu genellikle Gmail e-posta adresiniz, Gmail şifreniz ve Gmail SMTP sunucusudur (smtp.gmail.com).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set 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 protected]"
.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
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
1'i değiştirinve şifrenizi kendi hesap ayrıntılarınızla birlikte girin.
Son olarak, e-posta göndermesini başlatmak için aşağıdaki kodu ekleyin.
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 taşıma hatası görürseniz, bunun nedeni Google hesabınızın "daha az güvenli uygulamaların" çalışmasını engellemesidir. daha az güvenli uygulama ayarları sayfası 'i ziyaret etmeniz ve bu özelliği AÇIK hale getirmeniz gerekir.
Etkinleştirildikten sonra e-postanız gönderilecek. Otomatik olarak oluşturulan sonuç e-postanızı alan kişiye böyle görünür.
Gördüğünüz gibi aslında Excel VBA ile otomatikleştirebileceğiniz çok şey var. Bu makalede öğrendiğiniz kod parçacıklarıyla oynamayı deneyin ve kendi benzersiz VBA otomasyonlarınızı oluşturun.