Geri dön

EXCEL MUCİZELERİ - EXCEL VBA - MAKRO TANIMLAMA - KOD YAZMA

Microsoft Office paket programı içinde bulunan programların bazılarında, kullanıcıya kolaylık olsun diye ve sürekli tekrar edilen (rutin) işlemleri otomatik hale getirmek için Makro komutu kullanıcıya sunulmuştur.

Makrolar hazırlanırken, Excel’in arka plânında çalışan Visual Basic programlama dili de hazır halde beklemektedir. Herhangi bir kayıt yapıldığında bu programlama dili aktif hale gelir ve sizin yapmış olduğunuz herhangi bir makro komutunu programlama diline çevirir. Böylece daha sonra, hazırlamış olduğunuz makroyu çalıştırmak veya düzenlemek istediğinizde Excel bu imkânı size rahatlıkla sunar.

Excel’in en güçlü özelliklerinde biri de hiç şüphesiz VBA tarafı, yani Makrolardır. Office yüklenirken arka plânında hazır olarak gelen bu dili kullanarak, günlük rutin işlerinizi, haftalık, aylık raporlarınızı, koda dökebilir ve çok daha hızlı bir şekilde, hatta saniyeler içinde verilerinizi listeleyip raporlayabilirsiniz.

UserForm üzerinde ise ihtiyacınıza yönelik programlar hazırlayabilir, bu programları firma bünyesinde herkesin kullanmasını sağlayabilirsiniz.

Excel de makro kullanarak çok zor ve zaman işlemlerinizi çok kısa sürelerde yapabilirsiniz. Bu süreler o kadar kısadır ki bir örnek vermek gerekirse 3-5 günlük bir iş 3-5 dk’ya kadar düşebilir diyebiliriz. Hal böyle olunca makroların önemi daha da artıyor. Karmaşık, düzensiz verilerinizi normalleştirirken ya da farklı tablo yapıları oluştururken makrolar olmazsa olmazımızdır.

Makrolar geliştirilirken bazen benzer mantıkta işlemler çok fazla karşımıza çıkar her defasında bu kodları yazmak yerine bu kodları arşivleyerek gerektiği yerlerde birkaç ufak değişiklikle kolaylıkla uygulayabilirsiniz.

Bu makalemizde 1o Temel makro kodunu sizlere ne iş yaptıklarını açıklayarak sunuyoruz. Çalışmanızda daha üretken olmanıza yardımcı olacak bu örnekler oldukça basit ve kullanışlıdırlar. Bu hayat kurtaran temel kodları uygulamak da oldukça basittir. Bundan önce VBA kullanmamış olsanız bile bu kodları kullanabilirsiniz. Tek yapmanız gereken bu kodları VBA editörünüze yapıştırmak.

Bu kodları kullanmadan önce, VB editörüne erişmek için Excel şeridinizde geliştirici sekmenize sahip olmamız gerekiyor veya tüm Office paketlerinde Alt+F11 kısayol tuşu ile (Excel, Word, PowerPoint vb.) ulaşabilirsiniz.

Geliştirici sekmenize gidin ve “Visual Basic” üzerine tıklayın.

  • “Proje Penceresi” n de sol tarafta, çalışma kitabınızın adını sağ tıklayın ve yeni bir modül ekleyin.

  • Sadece kodlarınızı modüle yapıştırın ve kapatın. Dilerseniz F5 kısa yolu ile de kodlarınızı çalıştırabilirsiniz.

  • Şimdi geliştirici sekmenize gidin ve makro düğmesine tıklayın.

  • Dosyanızda bulunan makroların listesini içeren bir pencere gösterecektir ve bu listeden seçtiğiniz bir makroyu çalıştırabilirsiniz.

Şimdi kodlarımızı inceleyelim.

  • Seçtiğiniz hücreden itibaren istediğiniz kadar sayıda sağa doğru boş sütun ekler ve diğer sütunları da öteler.

    Sub CokluSutunEkle()

    Dim i As Integer

    Dim j As Integer

    ActiveCell.EntireColumn.Select

    On Error GoTo Son

    i = InputBox(“Eklemek istediğiniz sütun sayısını giriniz”, “Sütun Ekle”)

    For j = 1 To i

    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightorAbove

    Next j

    Son: Exit Sub

    End Sub

  • Seçtiğiniz hücreden itibaren istediğiniz kadar sayıda aşağı doğru boş sütun ekler ve diğer satırları da öteler.

    Sub CokluSatirEkle()

    Dim i As Integer

    Dim j As Integer

    ActiveCell.EntireRow.Select

    On Error GoTo Son

    i = InputBox(“Eklemek istediğiniz satır sayısını giriniz “, “Satır Ekle”)

    For j = 1 To i

    Selection.Insert Shift:=xlToDown, CopyOrigin:=xlFormatFromRightorAbove

    Next j

    Son: Exit Sub

    End Sub

  • Seçtiğiniz hücreden itibaren girdiğimiz sayı kadar otomatik doldurma yapar. Örneğin F7 hücresi seçili iken çalıştırdığımızda açılan giriş kutusuna 5 yazarsak F7 hücresinden aşağı doğru 1’den 5’e kadar sayı birer artan sayıları yazdırır.

Sub OtomatikSeriDoldurma()

Dim i As Integer

On Error GoTo Son

i = InputBox(“Değer giriniz”)

For i = 1 To i

ActiveCell.Value = i

ActiveCell.Offset(1, 0).Activate

Next i

Son: Exit Sub

End Sub

  • Seçtiğiniz hücrelerin sütun genişliklerini sütunun içindeki en uzun metne göre otomatik olarak ayarlar.

Sub SutunlariOtomatikAyarla()

Cells.Select

Cells.EntireColumn.AutoFit

End Sub

  • Seçtiğiniz hücrelerin satır genişliklerini satırın içindeki metne göre otomatik olarak ayarlar.

Sub AutoFitRows()

Cells.Select

Cells.EntireRow.AutoFit

End Sub

  • Excel’in tüm hücrelerini çözdükten sonra metni kaydır özelliğini çözer ve satır, sütun genişliklerini otomatik olarak ayarlar.

Sub MetniKaydirTemizle()

Cells.Select

Selection.WrapText = False

Cells.EntireRow.AutoFit

Cells.EntireColumn.AutoFit

End Sub

  • Birleştirilmiş seçili hücreleri eski haline geri döndürür.

Sub UnmergeCells ()

Selection.UnMerge

End Sub

  • Durum çubuğunda makro kodunun işlenmesi sırasındaki ilerlemenin yüzdesini görmek için aşağıdaki kodu kullanabiliriz.

    Sub DurumCubugundaIlerleme()

    Application.StatusBar = “İşlem başlıyor”

    For k= 1 To 5000

    Cells(k, 1) = k

    Application.StatusBar = ” İşlemin bitmesi için lütfen bekleyin ” & Round((k / 5000 * 100), 0) & “%”

    Next

    Application.StatusBar = “”

    End Sub

  • İlgili sayfanın sağ üst köşesine o günün tarihini yazdırır. Üst ortada Satış Raporu ve üst solda ise ABC şirketi yazar.

    Sub UstBilgiyeTarihEkleme()

    With ActiveSheet.PageSetup

    .LeftHeader = “ABC Şirketi”

    .CenterHeader = “Satış Raporu”

    .RightHeader = “&D”

    .LeftFooter = “”

    .CenterFooter = “”

    .RightFooter = “”

    End With

    ActiveWindow.View = xlNormalView

    End Sub

  • Makro ile Excel dosyasında çalışırken PowerPoint programını açmak için aşağıdaki kodu kullanabiliriz. Index değerini 0 yazarsak Hesap Makinası, 1 yazarsak Word, 3 yazarsak Outlook açılır.

Sub PowerPointAc()

Application.ActivateMicrosoftApp Index:=2

End Sub

Yukardaki kod blokları tek başlarına doğrudan bir anlam ifade etmeyebilir. Bizler yazdığımız kodlarda bu pratik kod bloklarını kullanarak daha hızlı uygulama geliştirebiliyoruz. En azından bir kere çalıştırıp sonucunu gördüğünüz zaman kafanızda yer edecek ve böyle bir durum ile karşılaşırsanız uygulamanız kolay olacaktır.

EXCEL MAKROLARI NEDİR?

Biz bu makroların genel adına Excel & VBA diyoruz.

VBA NEDİR?

VBA‘nın açılımı; Visual Basic for Applications
Yani; Office uygulamaları için uyarlanmış, Visual Basic nesne, metot ve özelliklerine erişmemizi sağlayan yapıdır.

Microsoft Office paket programı içinde bulunan programların bazılarında, kullanıcıya kolaylık olsun diye ve sürekli tekrar edilen (rutin) işlemleri otomatik hale getirmek için Makro komutu kullanıcıya sunulmuştur.

Makrolar hazırlanırken, Excel’in arka plânında çalışan Visual Basic programlama dili de hazır halde beklemektedir. Herhangi bir kayıt yapıldığında bu programlama dili aktif hale gelir ve sizin yapmış olduğunuz herhangi bir makro komutunu programlama diline çevirir. Böylece daha sonra, hazırlamış olduğunuz makroyu çalıştırmak veya düzenlemek istediğinizde Excel bu imkanı size rahatlıkla sunar.

MAKRO KAYDET YÖNTEMİ NEDİR?

Excel’in arka plânında var olan Visual Basic dilini harekete geçirip, çalışma kitaplarında, çalışma sayfalarında veya hücrelerde yaptığımız tüm işlemleri koda döken bir araçtır ve yöntemdir. Size sunulan bu hazır kodlarla işlerinizi daha hızı halledebilirsiniz. Biz de bu özellikten faydalanarak verilen hazır kodları kullanacağız.

Makro Kaydet özelliğine 3 farklı noktadan ulaşabilirsiniz.

1- Geliştirici (Developer) Menüsünden

Şeritte bu menü yoksa şu yolu izleyerek menüyü şeride ekleyebilirsiniz.
Dosya ‣ Seçenekler ‣ Şeridi Özelleştir ‣ Sağdaki menüden Geliştirici seçeneği seçip Tamam’a basın.

2- Görünüm (View) Menüsünden

3- Durum (StatusBar) Çubuğundan

İstediğiniz herhangi bir yöntemi kullanarak Makro Kaydet yöntemini aktif edebilirsiniz.

Şimdi nasıl aktif edeceğinizi anlatacağım fakat sadece okuyun, daha sonra bunu uygulamalı olarak birlikte yapacağız.

Makro Kaydete bastığınızda karşınıza bu pencere gelecek.
Makro1 yazan kutucuğa yapacağınız işle ilgili bir isim verebilirsiniz.
Örneğin; filtreleme yapmak için kullanacaksanız oraya filtre yazıp Tamam butonuna basarız.
Bastığımız andan itibaren yapılan kayıt başlayacak ve her işlemi kaydedip arka plânda koda dökecektir.
İşiniz bitince de Kaydı Durdurmanız gereklidir. Yine aynı yerden Kaydı Durdura basabilirsiniz.

Evet.. şimdi gerçek bir örnek üzerinde bu işin ne kadar kolay olduğunu görelim.

Hepimiz Excel’de verilerimizi tablo hâline getiririz, yani ilk satırda başlıklarımız, altında da o başlıklara ait verilerimiz olur değil mi? Bu tabloda da gün içinde birçok kez filtreleme yapıyoruzdur. Filtreleme yaparken ne yaparız?

Hangi alanda (sütunda) bir filtreleme yapacaksak, o alandaki filtre oklarına tıklarız ve açılan pencereden filtrelemek istediğimiz veriyi seçeriz ya da arayıp Tamama basarız ve istediğimiz veriye göre tablomuz filtrelenmiş olur.

Bu basit bir filtreleme işlemini dahi gün içinde birçok kez yapıyor ve üzerinde gereksiz yere fazla zaman kaybediyoruz. Sürekli filtreyi aç oradan seçim yap ve Tamama bas, başka bir veri aradığımızda da yine aynı şekilde filtreyi aç oradan seç Tamama bas işlemini tekrarlıyoruz.

Oysa bu filtreleme işlemi için kullanacağımız boş bir hücremiz olsa ve oraya aradığımızı yazıp Entera bastığımızda ya da butonu tıkladığımızda hemen filtrelense çok daha kolay ve hızlı olmaz mıydı?

Bu sadece gün içinde yapacağınız tek bir işlem için size hız kazandırıyor olacak. Ancak bütün işlerinizi böyle hızlandırdığınızı düşünün.

HANGİ DURUMLARDA YAZDIĞINIZ KODU ÇALIŞTIRABİLİRSİNİZ?

Herhangi bir hücreyi;

  • Seçtiğinizde
  • Sağ tıkladığınızda
  • Çift tıkladığınızda
  • Hücreye veri girişi yaptığınızda
  • Sayfayı açtığınızda
  • Sayfadan çıktığınızda
  • Dosyayı açtığınızda
  • Dosyayı kapattığınızda
  • Bir butonu tıkladığınızda
  • Klavyeden herhangi bir tuşa bastığımızda

vb. gibi.. birçok farklı şekilde yazdığınız kodları çalıştırabilirsiniz.

Bu dosyada H1 hücresine herhangi bir Marka ismini yazarsak, o markaya ait olan veriler filtrelensin istiyoruz. Biz hücreye QUARTZ yazdığımızda Marka alanındaki QUARTZ olanlar filtrelenecek, YELKEN yazdığımızda da YELKEN olanlar filtrelenecek. Dolayısıyla biz o hücreyi bir filtreleme kutucuğu olarak kullanacağız.

Birazdan eyleme geçeceğiz. Öncesinde şunu bilmenizde fayda var. Yukarıda, Hangi Durumlarda Yazdığınız Kodu Çalıştırabilirsiniz? altında belirttiğimiz çalıştırma yöntemlerine göre kodları yazıldığı alanlar vardır.

Bu alanlar;

  • Module – (Makro Kaydet ile ya da manuel oluşturulan kodlar burada yer alır.)
  • Sayfanın Kod Penceresi – (İlgili sayfanın kod çalıştırma olayları burada yer alır.)
  • Kitabın Genel Kod Penceresi – (Kitabının tamamını etkileyecek olaylar burada yer alır.)

Biz şu an için bir sayfadaki hücreye veri girişi yaptığımızda filtreleme işlemi yaptırmak istediğimiz için, Makro Kaydet ile elde ettiğimiz hazır kodları Sayfanın Kod Penceresine yapıştıracağız. Hücreye veri giriş yapıldığında da Change olayı tetiklenecek ve filtre işlemi uygulanacak.

Makro Kaydet yöntemini her işiniz için kullanabileceğinizi unutmayın.

İzlenecek yol her zaman şöyle olsun;

  • Makro Kaydete basın.
  • Olmasını istediğiniz işlemi manuel olarak yapın.
  • Kaydı Durdur

Kodlar arka plânda hazırlanmış olacak.

BU KODLARA NEREDEN NASIL ULAŞABİLİRSİNİZ?

Hayatta olduğu gibi, Excel’de de bir işi yapmanın birden fazla yolu vardır. Oluşan kodlara yine birkaç farkı yoldan ulaşabilirsiniz.

  1. Makro Kaydet dediğimiz noktadan Makroları Görüntüle diyerek

2. Alt + F8 tuşlarına basıp aşağıdaki pencereye kısa yoldan ulaşarak.

3. Alt + F11 tuşlarına basıp VBE Penceresine direkt olarak erişerek.

4. Sayfa sekmesi üzerinde sağ tıklayıp Kod Görüntüle diyerek.

Makro Kaydete basalım, makromuza herhangi bir isim verelim, örneğin; Filtre .. şimdi Tamama basarak kaydı başlatalım.

Tablomuzdaki herhangi bir hücreyi seçelim ve Veri menüsünden Filtreyi seçelim.
Not: Filtrenin üzerine gelip biraz beklerseniz; varsa kısayol tuşunu size verecektir. O kısayol tuşu ile de filtreyi aktif edebilirsiniz.

Sonrasında Marka alanından tümünün seçeneğini kaldırıp YELKEN’i seçin ve Tamama basın.

Temel olarak yapmak istediğimiz filtreleme olayının kodlarını edinmek olduğu için, eylemi gerçekleştirdik ve işimiz bitti.

Şimdi Kaydır Durdura basıp kaydı durduruyoruz. Makroları Görüntüleyi tıklıyoruz.
Makro Listesi penceresi Filtre makrosu seçili olarak ekrana gelecektir.
Düzenle butonuna basarak, oluşan Module1 içerisindeki kodları görüntüleyebilirsiniz.

Oluşan kodlarımız bu resimdeki gibi olacaktır. Tek tırnak ile başlayıp yeşil renkli görünen satırlar yorum satırlarıdır, kodları etkilemez, sadece açıklama içindir.
Not: Selection.AutoFilter satırını silebilirsiniz. Asıl işi hemen bir altındaki satır yapmakta.

Gördüğünüz gibi filtreleme işleminin kodlarını kolayca elde ettik.

Ufak bir bilgi daha verelim; yukarıdaki gibi Module içerisine yazılıp Sub ile başlayan kodları bir butona atayarak, butonu tıkladığınızda makroyu çalıştırabilirsiniz, isterseniz bu yolu deneyin.

Daha hızlı bir yol olan, hücreye veri girişi yaptıkça hemen filtrelemeyi göstereceğiz, o yüzden yazıyı okumaya devam edelim.

Evet.. kodları elde ettik. Geriye, kodlarda kriter olarak belirtilen “YELKEN” yerine, biz H1 hücresine ne yazdıysak onu filtrelemesi için kriteri dinamik yapmak ve hücreye veri girişi yapınca çalıştırmak kaldı.

Bunun için şöyle basit bir düzeltme yapacağız; filtrelemeyi yapan kod satırımız bu;

ActiveSheet.Range(“$A$1:$E$52″).AutoFilter Field:=3, Criteria1:=”YELKEN”

Koddaki “YELKEN” yerine aşağıdaki gibi hücre adresini yazarsanız, o hücreyi dinamik biçimde filtreleme kutusu olarak kullanabilirsiniz.

ActiveSheet.Range(“$A$1:$E$52”).AutoFilter Field:=3, Criteria1:=Range(“H1”).Value

Kriteri hücreden alacak şekilde dinamik hâle getirdik. Hadi artık bu kodu hücredeki veriye göre değişecek şekilde çalıştıralım.
Sayfadaki bir hücredeki veri değişince çalışmasını istediğimiz için bu kodu Sayfanın Kod Penceresinde Change olayında yazacağımızı daha önce belirtmiştik.

Peki şimdi ne yapmamız lâzım?

Sekmenin üzerine sağ tıklayıp Kod Görüntüle diyerek ilgili sayfanın kod penceresine ulaşın, ilk etapta görseldeki gibi olacaktır.

General alanından Worksheeti seçin.

Declarations alanında, kullanabileceğimiz sayfa olayları yüklenecektir, oradan Change olayını seçin.

İlgili olay pencereye eklenecektir.
O olayın içerisine, oluşturduğumuz makronun adını aşağıdaki gibi Call Filtre olarak yazalım.
Selection_Change olayı artık gereksiz olduğundan o olayı silebilirsiniz.

Sayfada herhangi bir hücrede veri girişi/değişikliği olduğunda artık Filtre makrosu çalışacaktır.
Aslında bu noktada işleyişte karşımızı bazı zorluklar çıkabilir, o da şudur; kodumuzu, belirli bir hücrede veri girişi olduğunda değil de, herhangi bir hücreye veri girişi olursa şeklinde bıraktık. H1 hücresi haricinde de bir hücrede değişiklik yaparsak yine filtreleme işlemini yapacaktır. Dolayısıyla sayfadaki her hareketimizde sürekli filtreleme uygulayacaktır.

Sadece H1 hücresinde bir değişiklik olduğunda çalış dememiz daha mantıklı olurdu.
Bunu da kodun daha stabil çalışması için son bir dokunuş olarak kabul edersek, ufak bir şart ilave ederek olayı tatlıya bağlayabiliriz.

O son dokunuş da bu şart olacak; If Target.Address(0, 0) = “H1” Then

Açıklaması: Eğer veri girişi yapılan hücrenin adresi H1 ise.. diyerek kodumuza son hâlini veriyoruz.

Sayfada sonucunu bu şekilde göreceksiniz. H1’e ne yazarsanız Marka alanından o ismi filtreleyecektir.

Ufak bir ipucu daha verelim; eğer hücreye yel* yazıp Entera basarsanız ya da mevcut kodun sonuna & “*” ibaresini eklerseniz, hücrede markanın tamamını yazmadan da sonucu filtreleyecektir. Örneğin; yel yazıp Entera basarsanız size yel ile başlayan tüm kayıtları listeleyecektir.

ActiveSheet.Range(“$A$1:$E$52”).AutoFilter Field:=3, Criteria1:=Range(“H1”).Value & “*”

Her şey bittikten sonra sıra dosyayı kaydetmeye gelirse, artık o dosyayı Makro İçerebilen Excel Çalışma Kitabı olarak farklı kaydetmelisiniz. (Farklı Kaydet kısa yolu; F12’dir.)

 

PAYLAŞ : Email Facebook Google Twitter