VBA|Seviye|Temel
1. Workbook ve Worksheet Değişkenleri
Workbook (Çalışma Kitabı) nesnesi Dim … As Workbook, Worksheet (Sayfa) nesnesi ise Dim … As Worksheet olarak deklare edilir.
Private Sub CommandButton1_Click()
Dim MyRng As Range, Ws As Worksheet, Wb As Workbook
'Objectler, hiyerarşiye göre iç içe yuvalanabilir.
Set Wb = Application.ThisWorkbook 'Doğrudan ThisWorkbook komutunu da kullanabilirsiniz.
Set Ws = Wb.Worksheets(2) 'Ya da Set Ws = Wb.Worksheets("İkinci Sayfa")
Set MyRng = Ws.Range("A1") 'Şu kodlarla aynı anlama gelir: Set MyRng = ThisWorkbook.Worksheets(2).Range("A1")
MyRng.Value = "ishakkutlu.com"
End SubYukarıdaki prosedürde CommandButton1, ishakkutlu.com isimli Sayfa1’e (yani indeks numarası 1 olan sayfaya) konumlandırılmış olup prosedürün sonucu İkinci Sayfa isimli Sayfa2’ye (yani indeks numarası 2 olan sayfaya) yazdırılmıştır.
Butonun tıklanması sonrasında ise aşağıdaki resimde gösterildiği gibi İkinci Sayfa isimli sayfanın A1 hücresine ishakkutlu.com yazdırılır.
2. Sayfa İndeks Numaraları ve İsimleri
Sayfaların indeks numaralarını görmek için Visual Basic editörüne gidiniz. Örnek olarak aşağıdaki ekran görüntüsünü inceleyebilirsiniz.
Sola doğru ok işaretleri ile belirtilen Sayfa1 (Sheet1), Sayfa2 (Sheet1) ifadelerinin karşılarındaki ishakkutlu.com, İkinci Sayfa ibareleri ise sayfa isimlerini temsil etmektedir. Ancak Sayfa1, Sayfa2 ifadelerinde yer alan indeks numaraları yanıltıcı olabilir. Bu karışıklığı göstermek için aşağıdaki gibi bir simülasyon yapılabilir.
Excel dosyasına gelin ve İkinci Sayfa isimli sayfayı sola sürükleyin. Sayfaları aşağıdaki gibi olmalıdır.
Butona tekrar tıklarsanız ishakkutlu.com yazdırılan sayfa, İkinci Sayfa isimli sayfa değil, ishakkutlu.com isimli sayfa olacaktır.
Halbuki Project Explorer penceresinde ishakkutlu.com isimli sayfa Sayfa1, İkinci Sayfa isimli sayfa ise Sayfa2 olarak yer alıyordu. İkinci Sayfa isimli sayfayı en sola getirdikten sonra derleyici, İkinci Sayfa isimli sayfanın indeks numarasını 1, ishakkutlu.com isimli sayfanın indeks numarasını ise 2 olarak kabul etti.
Özetle Excel dosyasının sol atında yer alan sayfalar, fare ile sürüklenmek suretiyle yerleri değiştirilirse, soldan sağa doğru olacak şekilde (Sayfa1, Sayfa2 ifadeleri değişmeksizin) indeks numaraları değişir. Birden fazla sayfayı ilgilendiren algoritmalarınız olduğunda ve sayfa isimleri yerine indeks numaralarını kullandığınızda, söz gelimi bir sayfadan veri alıp diğer bir sayfaya yazdırmak istediğinizde, karışıklık yaşayabileceğinizi kesinlikle göz önünde bulundurun.
Bu durumda indeks numaraları yerine sayfa isimlerini kullanmak isteyebilirsiniz.
Private Sub CommandButton1_Click()
Dim MyRng As Range, Ws As Worksheet, Wb As Workbook
'Objectler, hiyerarşiye göre iç içe yuvalanabilir.
Set Wb = Application.ThisWorkbook 'Doğrudan ThisWorkbook komutunu da kullanabilirsiniz.
Set Ws = Wb.Worksheets("İkinci Sayfa") 'Ya da Set Ws = Wb.Worksheets(2)
Set MyRng = Ws.Range("A1") 'Şu kodlarla aynı anlama gelir: Set MyRng = ThisWorkbook.Worksheets(2).Range("A1")
MyRng.Value = "ishakkutlu.com"
End Sub
Sayfaların yeri değiştirilmek suretiyle indeks numaraları değişse de yukarıdaki prosedür daima sonucu İkinci Sayfa isimli sayfaya yazdıracaktır. Ancak sayfanın ismi herhangi bir sebeple değişecek olursa, aşağıda gösterildiği gibi bir hata mesajı alırsınız.
Karşılaşılacak hata kodu ve metni:
Run-time error ’9’:
Subscript out of range
Debug butonuna tıklarsanız hatalı satıra yönlendirilirsiniz.
Yukarıda derleyicinin sarı ile işaretlediği satırda yer alan “İkinci Sayfa” isimli sayfa bulunamadığı için hatanın oluştuğunu anlıyoruz. Çünkü yukarıda İkinci Sayfa isimli sayfanın adını “İkinci Sayfanın Adı Değişti” olarak değiştirmiştik. Konuyla ilgili çözüm önerisi takip eden başlıkta yer almaktadır.
3. Çalışma Kitabının ve Sayfasının Korunması
Algoritmalarımızda sayfalardan yararlanmak için indeks numaraları veya sayfa isimler kullanılabilir. Ancak her iki yöntemin de dezavantajları bulunmaktadır. Her iki yöntemin riski de yukarıda bahsedildiği gibi nihai kullanıcının bilmeden bir karışıklığa yol açmasıdır. Bu riski ortadan kaldırmak için aşağıda gösterildiği gibi Excel dosyasını (sayfayı değil) kilitleyebilirsiniz.
Private Sub CommandButton1_Click()
Dim MyRng As Range, Ws As Worksheet, Wb As Workbook
ThisWorkbook.Unprotect "123"
'Objectler, hiyerarşiye göre iç içe yuvalanabilir.
Set Wb = Application.ThisWorkbook 'Doğrudan ThisWorkbook komutunu da kullanabilirsiniz.
Set Ws = Wb.Worksheets(2) 'Ya da Set Ws = Wb.Worksheets ("İkinci Sayfa")
Set MyRng = Ws.Range("A1") 'Şu kodlarla aynı anlama gelir: Set MyRng = ThisWorkbook.Worksheets(2).Range("A1")
MyRng.Value = "ishakkutlu.com"
ThisWorkbook.Protect "123"
End SubArtık sayfaları sürükleyip bırakarak indeks numaraları değiştirilemeyeceği veya sayfa isimleri tekrar adlandırılamayacağı için kodlarınız da sorunsuz bir şekilde çalışacaktır. Ancak yukarıda 123 şifresi kullanılarak gösterildiği gibi prosedürün başında dosya korumasını (çalışma kitabının korumasını) kaldırmalı, sonunda ise dosya korumasını tekrar aktif yapmalısınız.
Esasen ThisWorkbook.Unprotect “123” veya ThisWorkbook.Protect “123” komutunun yaptığı işlem, sol üst köşedeki Dosya menüsüne tıkladıktan sonra aşağıdaki resimlerde yer alan adımları uygulamak suretiyle manuel olarak yapılabilen bir işlemin VBA ile yapılmasıdır.
Eğer çalışma sayfalarını da kilitlemek veya korumak istiyorsanız prosedürü aşağıdaki şekilde güncellemelisiniz.
Private Sub CommandButton1_Click()
Dim MyRng As Range, Ws As Worksheet, Wb As Workbook
ThisWorkbook.Unprotect "123"
ThisWorkbook.Worksheets(1).Unprotect "123"
ThisWorkbook.Worksheets(2).Unprotect "123"
'Objectler, hiyerarşiye göre iç içe yuvalanabilir.
Set Wb = Application.ThisWorkbook 'Doğrudan ThisWorkbook komutunu da kullanabilirsiniz.
Set Ws = Wb.Worksheets(2) 'Ya da Set Ws = Wb.Worksheets ("İkinci Sayfa")
Set MyRng = Ws.Range("A1") 'Şu kodlarla aynı anlama gelir: Set MyRng = ThisWorkbook.Worksheets(2).Range("A1")
MyRng.Value = "ishakkutlu.com"
ThisWorkbook.Worksheets(1).Protect "123"
ThisWorkbook.Worksheets(2).Protect "123"
ThisWorkbook.Protect "123"
End SubYukarıdaki prosedür ile dosya kilidi ile birlikte Sayfa1 ve Sayfa2’yi de kilitlemiş olduk. Butona tıklandıktan sonra her iki sayfada da manuel olarak herhangi bir değişiklik yapılamaz.
ThisWorkbook.Worksheets(1).Unprotect “123” veya ThisWorkbook.Worksheets(1).Protect “123” komutunun yaptığı işlem, sol alt köşedeki ishakkutlu.com isimli sayfa isminin üzerinde fare ile sağ tık yaptıktan sonra, aşağıdaki resimlerde yer alan adımları uygulamak suretiyle manuel olarak yapılabilen bir işlemin VBA ile yapılmasıdır.
4. Bir Excel Dosyasını Açmak, Kapatmak veya Excel Uygulamasını Kapatmak (Open, Close, Quit)
Bir Excel dosyasını kapatmak (close) ile Excel uygulamasını kapatmak (quit) aynı işlem değildir. Söz gelimi açık olan 3 farklı Excel dosyanız varken sağ üst köşeden herhangi birini kapattığınızda, Excel uygulamasını değil, Excel uygulaması ile açtığınız üç dokümandan birini kapatmış olursunuz; Excel uygulaması kalan iki doküman için arka planda çalışmaya devam eder. Ancak açık olan 3 farklı Excel dosyanız varken, Excel uygulamasını kapatırsanız (quit), uygulamanın kendisi kapatılacağı için o uygulamayı kullanan tüm Excel dokümanlarınız (yani dosyalarınız) da kapatılır. Bu işlemi Excel dosyasını-dokümanını kapatmak (close) da olduğu gibi manuel olarak yapamayacağınız için örneklendiremiyorum. Ancak görev yöneticisinden Excel uygulamasını kapatırsanız da açık olan tüm Excel dosyalarınız (onları çalıştıran Excel uygulaması kapatılacağı için) kapatılır. Bu ayrımın farkında olmanız, VBA programlama dili ile karmaşık ve büyük projeleri yapmaya başladığınızda, özellikle de iş süreçlerinize ilişkin bir sistem-uygulama inşa etmek istediğinizde oldukça önemli olacaktır.
4.1. Open Metodu
Öncelikle kodların yazıldığı dosya dışında, örneğin masaüstünüzde “Test” isimli bir Excel dosyası oluşturunuz. Bu durumda dosyanın bulunduğu dizin aşağıdaki gibi olacaktır.
“C:\Users\Kutlu\Desktop\Test.xlsx”
Ancak Users\ ifadesinden sonra gelen kullanıcı adınızı, yani yukarıdaki satırda yer alan Kutlu ifadesini kendi kullanıcı adınız ile değiştirin. Kullanıcı adınızı bilmiyorsanız aşağıdaki şekilde elde edebilirsiniz.
Private Sub CommandButton1_Click()
Range("A1").Value = ThisWorkbook.Path
End Sub
Örnekte kodların yazıldığı Excel dosyası, masaüstünde yer alan “VBA-OBJECT DEĞİŞKENİ” isimli bir klasörün içinde olduğundan, yukarıdaki komut satırının sonucu \VBA-OBJECT DEĞİŞKENİ ile bitmektedir. Eğer kodların yazıldığı Excel dosyası doğrudan masaüstünde ise komut satırının sonucu \Desktop ile bitecektir.
Masaüstünde bulunan Test isimli (.xlsx uzantılı) Excel dosyası aşağıda gösterildiği gibi açılır.
Private Sub CommandButton1_Click() Dim StrPath As String StrPath = "C:\Users\Kutlu\Desktop\Test.xlsx" Workbooks.Open (StrPath) End Sub
Dilerseniz bir pencere aracılığıyla, açılacak Excel dosyasını kullanıcının seçmesini sağlayabilirsiniz.
Private Sub CommandButton1_Click() Dim StrFile As String StrFile = Application.GetOpenFilename() Workbooks.Open (StrFile) End Sub
İlgili Excel dosyasını bulunuz ve Aç butonuna tıklayınız. Ancak İptal butonuna tıklarsanız, aşağıdaki gibi bir hata mesajı ile karşılaşırsınız.
Debug butonuna tıklarsanız hatalı satıra yönlendirilirsiniz.
Hatanın sebebi StrFile değişkeninin geçerli herhangi bir değer taşımamasından kaynaklı olarak Open metodunun kullanılamamasıdır. Söz konusu hata sebebiyle prosedürün durdurulması istenmiyorsa, yukarıdaki kodları aşağıdaki şekilde revize edebilirsiniz.
Private Sub CommandButton1_Click() Dim StrFile As String StrFile = Application.GetOpenFilename() On Error Resume Next Workbooks.Open (StrFile) On Error GoTo 0 End Sub
On Error Resume Next komutu, takip eden satırlarda bir hata oluşursa derleyicinin devam etmesini; On Error GoTo 0 komutu ise On Error Resume Next komutunun iptal edilmesini, yani On Error GoTo 0 komutundan sonra hata oluşursa derleyicinin durmasını ifade eder.
4.2. Close Metodu
Açık olan Test isimli (.xlsx uzantılı) Excel dosyası aşağıda gösterildiği gibi kapatılır.
Private Sub CommandButton1_Click()
Workbooks("Test.xlsx").Close
End Sub4.3. Quit Metodu
Açık olan tüm Excel dosyaları (ki kodların yazıldığı Excel dosyası da dahil), yani Excel uygulaması aşağıda gösterildiği gibi kapatılır.
Private Sub CommandButton1_Click() Application.Quit End Sub
5. Excel Dosyasını Kaydetmek
Bir çalışma kitabını kaydetmek için aşağıdaki iki farklı komuttan uygun olanı kullanabilirsiniz.
Private Sub CommandButton1_Click() 'ActiveWorkbook.Save ThisWorkbook.Save End Sub
ThisWorkbook.Save komutu kodların bulunduğu Excel dosyasını kaydeder. ActiveWorkbook.Save komutu ise o sırada aktif olan Excel dosyasını kaydeder. Söz gelimi kodların bulunduğu Excel dosyası dışında, kodlarla arka planda birkaç Excel dosyasının daha açıldığını düşünün. Kodlarınızın belirli bir mantığa göre bir Excel dosyasından diğerine veri kopyaladığını varsayalım. ActiveWorkbook.Save komutu, ActiveWorkbook.Save komutunun okunduğu sırada aktif olan çalışma kitabını kaydeder. Aktif olan çalışma kitabına ilişkin manuel işleme göre bir örnek vermek gerekirse 1, 2 ve 3 adında üç farklı açık Excel dosyanız olsun. O sırada üzerinde çalıştığınız (ekranda görünen) dosya 2 isimli dosya ise (yani 1 ve 3 isimli dosyalar açık olmakla birlikte arka planda ise) aktif dosya da 2 isimli dosyadır.
6. Farklı Bir Excel Dosyasına Veri Yazmak ve Onu Kaydedip Kapatmak
Farklı bir Excel dosyasında bir işlem yapmak için önce onu açmalısınız. Sonrasında ise tam olarak dosyanın hangi sayfasının hangi hücresinde işlem yapmak istediğinizi net bir şekilde (object hiyerarşisini hatırlayın) kodlamanız gerekir. Aşağıdaki örneği inceleyebilirsiniz.
Private Sub CommandButton1_Click()
Dim StrFileName As String, StrPath As String, WsTest As Worksheet
StrPath = "C:\Users\Kutlu\Desktop\Test.xlsx"
StrFileName = "Test.xlsx"
Workbooks.Open (StrPath) 'Önce Test dosyasını aç
Set WsTest = Workbooks(StrFileName).Worksheets(1) 'Test dosyasının ilk sayfasını bir sayfa değişkenine ata
WsTest.Range("A1").Value = "ishakkutlu.com" 'Test dosyasının ilk sayfasının A1 hücresine ishakkutlu.com yaz
Workbooks(StrFileName).Close SaveChanges:=True 'Test dosyasını kaydedip kapat
End SubYukarıdaki kodlar çalıştırıldığında derleyici, kaydet-kapat komutunun bulunduğu (yani son) satırı okuduğunda aşağıdaki gibi bir uyarı mesajı ile karşılaşabilirsiniz.
Bu durumda kodlarınız da hata oluşmaz. Ancak kodlarınızın çalışmaya devam etmesi için manuel olarak yukarıdaki uyarı mesajını kapatmanız gerekir. Mesajı kapatmanızın ardından dosya kaydedilip kapatılacaktır.
6.1. Sistem Uyarı-Bilgilendirme Mesajlarını Geçici Olarak Kapatmak
Yukarıdaki gibi sistemin çeşitli sebeplerle gönderdiği uyarı-bilgilendirme mesajlarını aşağıdaki şekilde kapatabilir ve kodlarınızın kesintiye uğramadan sorunsuz bir şekilde çalışmasını sağlayabilirsiniz.
Private Sub CommandButton1_Click()
Dim StrFileName As String, StrPath As String, WsTest As Worksheet
Application.DisplayAlerts = False
StrPath = "C:\Users\Kutlu\Desktop\Test.xlsx"
StrFileName = "Test.xlsx"
Workbooks.Open (StrPath) 'Önce Test dosyasını aç
Set WsTest = Workbooks(StrFileName).Worksheets(1) 'Test dosyasının ilk sayfasını bir sayfa değişkenine ata
WsTest.Range("A1").Value = "ishakkutlu.com" 'Test dosyasının ilk sayfasının A1 hücresine ishakkutlu.com yaz
Workbooks(StrFileName).Close SaveChanges:=True 'Test dosyasını kaydedip kapat
Application.DisplayAlerts = True
End Sub“Application.DisplayAlerts” komutu ile prosedürün başında uyarıların görüntülenmesini kapatır, sonra da prosedürün sonunda tekrar aktif hale getirerek, prosedürün başında yaptığımız değişikliği varsayılana geri döndürmüş oluruz. Böylece hazırladığınız algoritma, kullanıcının varsayılan Excel ayarlarında herhangi bir değişiklik yapmadan ve sistem tarafından herhangi bir kesintiye de uğramadan pürüzsüz bir şekilde çalışır.
6.2. Ekran Güncellemeyi Geçici Olarak Kapatmak
Yukarıdaki prosedür çalıştırıldığında dikkatli bakılırsa ekranda bir dosya belirir ve kapanır. Çok sayıda dosyaya-sayfaya ve/veya çok sayıda veri yazdırmak istediğinizde, ekranın kendini güncelleme süreci, kodların çalışma performansını olumsuz etkiler. Bu sebeple yukarıdaki prosedür aşağıdaki şekilde uyarlanarak prosedürün, yapacağı işlemleri arka planda yapması, böylece daha verimli ve hızlı çalışması sağlanabilir.
Private Sub CommandButton1_Click()
Dim StrFileName As String, StrPath As String, WsTest As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
StrPath = "C:\Users\Kutlu\Desktop\Test.xlsx"
StrFileName = "Test.xlsx"
Workbooks.Open (StrPath) 'Önce Test dosyasını aç
Set WsTest = Workbooks(StrFileName).Worksheets(1) 'Test dosyasının ilk sayfasını bir sayfa değişkenine ata
WsTest.Range("A1").Value = "ishakkutlu.com" 'Test dosyasının ilk sayfasının A1 hücresine ishakkutlu.com yaz
Workbooks(StrFileName).Close SaveChanges:=True 'Test dosyasını kaydedip kapat
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub7. Çalışma Sayfasının Korunmasına İlişkin Özelleştirmeler
Bir çalışma sayfasını Protect metodu ile korumak istediğimizde, kolon ekleme, kaldırma veya satır ekleme, kaldırma gibi sayfanın bazı özelliklerinin korunmamasını, yani bu özelliklerin nihai kullanıcı tarafından erişilebilir olmasını sağlayabiliriz.
Private Sub CommandButton1_Click() Dim StrUygPath As String, StrFileName As String Dim Wb As Workbook, Ws As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False StrUygPath = "C:\Users\Kutlu\Desktop\Örnek Uygulama\Uygulama Dosyaları\" StrFileName = "Test.xlsx" Workbooks.Open (StrUygPath & StrFileName) Set Wb = Workbooks(StrFileName) Set Ws = Wb.Worksheets(1) Ws.Unprotect "123" ' 'Buraya diğer komutlarınız gelecek. ' Ws.Protect "123", _ DrawingObjects:=False, _ Contents:=True, _ Scenarios:=True, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingColumns:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=False, _ AllowDeletingColumns:=False, _ AllowDeletingRows:=False, _ AllowSorting:=False, _ AllowFiltering:=False, _ AllowUsingPivotTables:=False 'Ws.Protect "123", AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows:=False, AllowDeletingColumns:=True, AllowDeletingRows:=True 'Yukarıdaki satırda olduğu gibi özellikleri yan yana da yazabilirsiniz. VBA dilinde alt tire (_) işareti komutun alt satırdan devam ettiğini ifade eder. 'Başında Allow olmayanlardan değeri True olanlar ilgili özelliğin kilitleneceğini, başında Allow olanlardan 'değeri True olanlar ise ilgili özelliğin kilitlenmeyeceğini ifade eder. Wb.Save 'Wb.Close SaveChanges:=True Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Yukarıdaki algoritmada değeri False olan DrawingObjects özelliği kilitli değilken, değeri True olan Contents (yani hücreye veri yazma) özelliği kilitlidir. Başında Allow olanlar için durum tam tersidir. Örneğin değeri True olan AllowInsertingColumns (yani kolon ekleme) özelliği kilitli değilken, değeri False olan AllowDeletingColumns (yani kolon silme) özelliği kilitlidir.
İshak Kutlu, VBA Notları
