MS Excel İçin Gelişmiş VBA Kılavuzu


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.

  • Verileri içe aktarın ve hesaplamalar yapın
  • Bir düğmeye basarak kullanıcıdan sonuçları hesaplayın
  • Hesaplama sonuçlarını birine e-postayla gönderin
  • 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 String
    Set 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ı:

    1. Oluşturun taxes.
    2. satılan birimlersütununu inceleyin ve satış vergisini hesaplayın.
    3. Hesaplama sonuçlarını yazın
    4. 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.

      <şekil sınıfı = "tembel hizalayıcı boyutu büyük">

      Ş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.

      <şekil sınıfı = "tembel hizalama merkezi boyutu büyük">

      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.

      <şekil sınıfı = "tembel hizalama merkezi boyutu büyük">

      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.

      <şekil sınıfı = "tembel hizalayıcı boyutu büyük">

      İş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 = "MyEmail@gmail.com"
      strTo = "BossEmail@gmail.com"
      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@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  .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.

      Excel VBA Advanced Tutorial

      Related posts:


      11.02.2020