YBS301U-İŞLEM TABLOSU PROGRAMLAMA
Ünite 4: VBA ile Çalışma Kitaplarını Yönetmek
Giriş
Workbook, çalışma kitabını temsil ederken Worksheet, içinde verilerin saklandığı ve düzenlendiği tek bir sayfayı ifade etmektedir. Çalışma kitapları ve Çalışma sayfası işlemleri yapmak için VBA’nın sağladığı çeşitli yöntemler bulunmaktadır. Çalışma kitapları açılabilir, oluşturulabilir, kaydedilebilir ve kapatılabilir. Ayrıca, çalışma sayfalarını yönetmek için de benzer işlemler gerçekleştirilebilir. Workbook Koleksiyonu ve Workbook Nesnesi, Excel’deki çalışma kitaplarını temsil etmektedir. Çalışma kitabı ve sayfa işlemleri yaparken veri düzenleme, sıralama veya formatlama gibi çeşitli işlemler de gerçekleştirilebilir. Bu işlemler, veri analizi ve raporlama gibi Excel tabanlı görevlerini otomatikleştirmeye olanak tanımaktadır.
Application (Uygulama) Nesnesi
Genel olarak Excel nesne modeli sadece belirli görevleri yerine getirmek üzere tasarlanmış nesneler içerir. Uygulama nesnesi, Excel nesne modeli hiyerarşisinin en üstünde yer alır ve Excel’deki diğer tüm nesneleri içerir. Ayrıca, başka herhangi bir nesneye tam olarak uymayan ancak Excel’in programlı kontrolü için gerekli olan özellikler ve yöntemler için her şeyi kapsayan bir alan görevi görür.
Application nesnesinin yaygın kullanım biçimleri şu şekilde özetlenebilir:
• Çalışma Kitabı ve Çalışma Sayfası Yönetimi: Application nesnesi; çalışma kitaplarını açmak, kapatmak, kaydetmek ve yeni çalışma kitapları oluşturmak gibi işlemleri gerçekleştirmek için kullanılır. Ayrıca, çalışma sayfalarını seçmek, adlarını değiştirmek veya yeni sayfalar eklemek için de kullanılabilir. • Ekran Özelliklerini Yönetme: Application nesnesi; ekran güncellemelerini kapatmak veya açmak, Excel penceresinin boyutunu veya konumunu değiştirmek gibi ekran özelliklerini kontrol etmek için kullanılır. • Hesaplama Modunu Yönetme: Excel’deki hesaplama modunu değiştirmek, hesaplama işlemlerini başlatmak veya duraklatmak için Application nesnesi kullanılabilir. • Dosya İşlemleri: Excel dosyalarını açmak, kaydetmek, kapatmak veya farklı formatlarda kaydetmek gibi dosya işlemleri de Application nesnesi aracılığıyla gerçekleştirilir. • Kullanıcı Etkileşimlerini Kontrol Etme: Kullanıcı tarafından gerçekleştirilen işlemleri izlemek veya kullanıcıya mesajlar göstermek gibi kullanıcı etkileşimlerini yönetmek için Application nesnesi kullanılabilir.
Uygulama nesnesinin yöntem ve özelliklerinin çoğu aynı zamanda sınıf listesinin en üstünde bulunabilen <globals>’ın da üyeleridir. Bir özellik veya yöntem <globals> içindeyse, bir nesneye önceden başvurmadan bu özelliğe veya yönteme başvurabilirsiniz.
Örn:
Application.ActiveCell ActiveCell
Active Özelliği
Uygulama nesnesi, etkin nesnelere açıkça ad vermeden onlara erişmeye olanak tanıyan birçok kısayol sunmaktadır. Bu kullanım özelliği makro çalıştırıldığında o anda neyin etkin olduğunu keşfetmeyi mümkün kılar; aynı türdeki farklı adlara sahip nesnelere uygulanabilecek genelleştirilmiş kod yazmayı da kolaylaştırır. Etkin nesnelere başvurmak amacıyla kullanılabilecek Uygulama nesnesi özellikleri şunlardır:
• ActiveCell • ActiveChart • ActivePrinter • ActiveSheet • ActiveWindow • ActiveWorkbook • Selection
Yeni bir çalışma kitabı oluşturulduğunda ve bunu belirli bir dosya adı ile kaydetmek istenildiğinde ActiveWorkbook özelliğini kullanmak, yeni Çalışma Kitabı nesnesine bir başvuru döndürmenin kolay bir yoludur.
Selection özelliği, seçili hücreleri içeren bir Range nesnesi döndürür. Excel VBA’da Range nesnesi, bir veya daha fazla hücreyi temsil eden nesnedir. Nesne, hücre değerlerine erişim, hücrelerin biçimlendirilmesi, formüller uygulanması ve daha birçok işlem için hücre aralığı belirtilerek kullanılır.
Uyarıları Görüntüleme (Display Alerts) Özelliği
Bir makro çalışırken sistem uyarılarının etkin olması ve bu uyarılara sürekli yanıt vermek zorunda kalmak çalışma ortamında sorunlara yol açabilmektedir. Örneğin, bir makro bir çalışma sayfasını sildiğinde, bir uyarı mesajı görüntülenecektir ve devam etmek için Tamam düğmesine tıklamak gerekir. Bununla birlikte, bir kullanıcının İptal düğmesine tıklama olasılığı da vardır; bu durum, çalışma sayfasının silinme işlemini iptal edebilir ve silme işleminin gerçekleştirildiği varsayılan sonraki kodu olumsuz yönde etkileyebilir. DisplayAlerts özelliğini False olarak ayarlayarak çoğu uyarının verilmesi engellenebilir. Bir uyarı iletişim kutusu gizlendiğinde, o kutudaki varsayılan düğmeyle ilişkili eylem otomatik olarak aşağıdaki şekilde gerçekleştirilir.
DisplayAlerts, bir dosyanın farklı kaydedilmesinde çıkacak olan uyarı ekranını engellemek için yaygın olarak kullanılmaktadır. Bu uyarı ekranı engellendiğinde varsayılan işlem yapılır ve makro kesintiye uğramadan dosyanın üzerine yazma işlemini gerçekleştirir.
Ekran Güncelleme (Screen Updating) Özelliği
Excel’in VBA programlama dilinde, ScreenUpdating özelliği, Excel’in ekranı güncelleme durumunu kontrol etmek için kullanılmaktadır. Varsayılan olarak, Excel
ekranı güncellenir, yani herhangi bir değişiklik yapıldığında kullanıcıya anında gösterilir. Bir makro çalıştırıldığında ekranın değişmesi ve titremesi yani güncellenmesi istenmeyen bir durumdur. Ekran güncelleme olayı genelde makro kaydedici tarafından oluşturulan kodlarda ve bu makrolardaki nesneler seçen veya etkinleştiren işlemlerde meydana gelmektedir.
ScreenUpdating özelliğine True değeri atanana kadar veya makronun yürütülmesi tamamlanıp kontrol kullanıcıya geri gelene kadar ekran donmuş halde kalacaktır. Makro hala çalışırken ekran değişiklikleri görüntülenmek istenmediği sürece ScreenUpdating’i True’ya geri yüklemeye gerek yoktur.
Değerlendir (Evaluate) Yöntemi
Excel VBA’da Evaluate fonksiyonu, bir ifadeyi veya formülü çalıştırarak sonucunu döndüren bir yöntemdir. Evaluate fonksiyonu, Excel formüllerini veya VBA ifadelerini çalıştırmak, değerlendirmek veya işlemek için kullanılır. Evaluate fonksiyonu, özellikle dinamik formüller oluştururken veya farklı hücre değerlerine bağlı olarak ifadelerin hesaplanması istenildiğinde kullanışlıdır.
Excel VBA’da Evaluate fonksiyonu, bir ifadeyi veya formülü çalıştırarak sonucunu döndüren bir yöntemdir. Evaluate fonksiyonu, Excel formüllerini veya VBA ifadelerini çalıştırmak, değerlendirmek veya işlemek için kullanılır. Evaluate fonksiyonu, özellikle dinamik formüller oluştururken veya farklı hücre değerlerine bağlı olarak ifadeleri hesaplamak istediğinizde kullanılabilir. Evaluate fonksiyonunun kullanılabileceği bazı durumlar aşağıdaki gibidir:
• Dinamik Formüller Oluşturma: Evaluate fonksiyonu, bir dize içindeki formülü çalıştırarak sonucunu döndürür. Evaluate fonksiyonu, kullanıcı tarafından sağlanan verilere dayalı olarak formüller oluşturmaya ve sonuçlarını hesaplayarak döndürmeye olanak tanır. • Karmaşık Hesaplamaları Yapma: Evaluate fonksiyonu, VBA kodu içinde karmaşık matematiksel veya mantıksal ifadeleri değerlendirmek için kullanılabilir. Örneğin, bir döngü içinde farklı ifadeleri hesaplamak için kullanılabilir. • Dinamik Hücre Referansları ile Çalışma: Bir dize içindeki hücre referanslarını kullanarak hücre değerlerini almak veya değiştirmek için Evaluate kullanılabilir. Evaluate, belirli bir hücrenin değerine bağlı olarak başka bir hücrenin değerini hesaplamak için kullanılabilir. • Excel İşlevlerini Çalıştırma: Evaluate, bir dize içindeki Excel işlevlerini çalıştırarak sonuçlarını döndürebilir. Evaluate, belirli bir hücre aralığının toplamını veya ortalama değerini almak gibi işlemler için kullanılabilir.
Durum Çubuğu (StatusBar) Özelliği
StatusBar özelliği, Excel’in durum çubuğunun altında ekranın sol tarafında görüntülenmek üzere bir metin dizisi atamaya olanak tanımaktadır. Bu şekilde uzun bir makro işlemi sırasında kullanıcıları ilerleme konusunda bilgilendirmek mümkün olabilmektedir. Ekran güncelleme kapalı olduğunda ve ekranda herhangi bir etkinlik belirtisi olmadığında kullanıcıları bu şekilde bilgilendirmek mümkün olabilmektedir. Ekran güncelleme kapalı olsa bile, hala durum çubuğunda mesajlar görüntülenebilir.
Anahtarları Gönder (SendKeys) Yöntemi
Excel VBA’da SendKeys yöntemi, kullanıcıya klavye tuşları göndermek için kullanılır. Bu yöntem genellikle kullanıcı arayüzü etkileşimini otomatikleştirmek veya belirli uygulamalara veri girişi yapmak için kullanılır.
OnTime (Bekleme) Yöntemi
Bir makroyu gelecekte çalışacak şekilde zamanlamak için OnTime yöntemi kullanılabilir. İleri tarihli bir makro çalıştırmak için makronun çalışacağı tarih ve saati ve makronun adını belirtmek gerekmektedir. Bir makroyu duraklatmak için Uygulama nesnesinin Bekleme yöntemi kullanılırsa, manuel etkileşim de dahil olmak üzere tüm Excel etkinlikleri askıya alınır. OnTime yönteminin avantajı, zamanlanmış makronun çalışmasını beklerken diğer makroları çalıştırmak da dahil olmak üzere mevcut Excel etkileşimine dönmeye olanak sağlamasıdır.
Çalışma Kitabı (Workbook) İşlemleri
Workbooks Koleksiyonu: Çalışma kitabı koleksiyonu açık olan tüm Excel çalışma kitaplarını içeren bir koleksiyondur. Bir Excel uygulamasında birden fazla çalışma kitabı açıldığında, koleksiyon, çalışma kitaplarını içerir. Bir çalışma kitabına erişmek veya üzerinde işlem yapmak için çalışma kitabı koleksiyonu kullanılır.
Workbook Nesnesi: Çalışma kitabı nesnesi Excel çalışma kitabını temsil eder. Her açık çalışma kitabı için bir Workbook nesnesi bulunur. Bir çalışma kitabının içindeki verilere, hücrelere, sayfalara ve diğer özelliklere erişmek ve bu ögeler üzerinde işlem yapmak için Workbook nesnesi kullanılır.
Çalışma Kitabı Açmak ve Kaydetmek
Excel VBA’da bir çalışma kitabı açmak için Workbooks.Open yöntemi kullanılabilir. Aşağıda buna yönelik bir kod paylaşılmıştır. Workbooks.Open Çalışma kitaabını açmak için dosya tam yol adını alan bir fonksiyondur.
Sub CalismaKitabiAcma()
Dim wb As Workbook ' Çalışma kitabının yolunu belirtin Dim dosyaYolu As String dosyaYolu="C:\Users\KullanıcıAdı\Belgeler\Orn ekCalismaKitabi.xlsx" ' Çalışma kitabını açın Set wb = Workbooks.Open(dosyaYolu)
' Açılan çalışma kitabıyla yapılacak işlemler buraya yazılır ' İşlem tamamlandıktan sonra çalışma kitabını kapatmak için wb. Close End Sub
Varsayılan çalışma kitabını temel alan yeni bir boş çalışma kitabı oluşturmak için Çalışma Kitapları koleksiyonunun Ekle yöntemi kullanılmalıdır:
Workbooks.Add
Yukarıdaki kod ile eklenen yeni çalışma kitabı etkin çalışma kitabı olacaktır, dolayısıyla bu çalışma kitabına aşağıdaki kod bloğunda ActiveWorkbook olarak başvurulmuştur. Çalışma kitabı Farklı Kaydet yöntemi kullanılarak hemen kaydedilirse, artık etkin olmasa bile daha sonraki kod ile çalışma kitabına başvurmak için kullanılabilecek bir dosya adı verilebilir.
Dizinden Dosya Adını Almak
Excel VBA’da çalışırken çalışma kitapları için genellikle dosya yollarını ve dosya adlarını bilmek gerekir. Bazı işlemlerde dosya adı, bazı işlemlerde de dosya yolu dediğimiz dizin adını bilmek çalışma kitaplarını açma veya kaydetme işlemlerinde sıkça kullandığımız bilgilerdir. Çalışma kitabı açıldıktan sonra, dosya yolunu almak, tam yolu ve dosya adını almak veya sadece dosya adını almak istenebilir.
Mevcut Bir Çalışma Kitabının Üzerine Yazma
Bir çalışma kitabı Farklı Kaydet yöntemi kullanılarak belirli bir dosya adıyla kaydedilmek istenildiğinde, bu isimde zaten bir dosya diskte var olabilir. Eğer dosya zaten varsa, kullanıcı bir uyarı mesajı alacak ve mevcut dosyayı üzerine yazma konusunda bir karar vermek zorunda kalacaktır. İstenilirse, uyarı önlenebilir ve kontrol programatik olarak gerçekleştirilebilir. Her seferinde mevcut dosyanın üzerine yazmak isteniliyorsa aşağıdaki kodu kullanarak uyarı ekranının gelmesi engellenebilir.
Private Sub CommandButton1_Click() Set wkb1 = Workbooks.Add Application.DisplayAlerts=False wkb1.SaveAs Filename:="C:\Data\VeriDosyasi1.xlsx" Application.DisplayAlerts = True End Sub
Değişiklikleri Kaydetme
Çalışma Kitabı nesnesinin Kapat yöntemi kullanılarak bir çalışma kitabı kapatılabilir. Çalışma kitabında değişiklik yapılmışsa, çalışma kitabını kapatma girişiminde bulunulduğunda kullanıcıdan değişiklikleri kaydetmesi istenir. Bu istemden kaçınmak isteniyorsa değişiklikleri kaydetmek isteyip istenmediğine bağlı olarak çeşitli teknikler kullanılabilir. Değişiklikler otomatik olarak kaydedilmek isteniyorsa bunu Close yönteminin bir parametresi olarak belirtmek gerekir.
Çalışma Kitabını Korumak
Çalışma kitabı olan Excel dosyalarını görsel arayüzden korumanın yanı sıra VBA kodları ile de korumak için Protect özelliği kullanılmaktadır.
Çalışma Sayfası (Worksheet) İşlemleri
Çalışma Sayfaları
Çalışma Sayfaları koleksiyonu, bir çalışma kitabındaki tüm çalışma sayfası nesnelerinin toplanmasını ifade eder. Çalışma Sayfaları koleksiyonunda bir çalışma sayfasına adı veya dizin (index) numarası aracılığıyla erişim sağlanır. Üzerinde çalışılmak istenilen çalışma sayfasının adı biliniyorsa, Çalışma Sayfaları koleksiyonunun gerekli üyesini belirtmek için bu adı kullanmak uygun ve genellikle daha güvenlidir. Çalışma Sayfaları koleksiyonunun tüm üyelerini (örneğin For...Next döngüsünde) işlemek istenirse, genellikle her çalışma sayfasına dizin numarasıyla erişim gerçekleştirilir.
Çalışma Sayfaları Göster/Gizle
Çalışma sayfalarının gizlenmesi gerekebilir, Excel’de bunu sağ tıklayarak Gizle seçeneği ile yapılır. Aynı yöntemle çalışma sayfası adının herhangi birine sağ tıklayarak gizlenmiş çalışma sayfası varsa bunu Göster ile gizli olmaktan çıkarmak mümkündür.
Çalışma Sayfalarına Erişmek
Farklı çalışma kitaplarındaki çalışma sayfalarına erişip sayfayı aktifleştirmek için aşağıdaki kod kullanılabilir.
Private Sub CommandButton1_Click() Workbooks("VeriDosyasi2.xlsx").Worksheets("Sayfa1"). Activate End Sub
Çalışma Sayfası Kopyalama ve Taşıma
Çalışma Sayfası nesnesinin Copy ve Move yöntemleri, tek bir işlemde bir veya daha fazla çalışma sayfasını kopyalamaya veya taşımaya olanak tanır. Her iki yöntemde de işlemin hedefini belirlemeye izin veren iki isteğe bağlı parametresi bulunur. Hedef, belirli bir çalışma sayfasının önüne veya arkasına olabilir. Bu parametrelerden biri kullanılmazsa, çalışma sayfası yeni bir çalışma kitabına kopyalanır veya taşınır. Copy ve Move yöntemleri herhangi bir değer veya referans döndürmediği için, kopyalanan veya taşınan çalışma sayfalarına atıfta bulunan bir nesne değişkeni oluşturmak isteniyorsa farklı teknikler kullanılması gerekmektedir. Kopyalama işlemi tarafından oluşturulan ilk sayfa veya sayfanın taşınması sonucunda oluşan ilk sayfa, işlemden hemen sonra etkin hale gelmektedir.
Function CalismaSayfasiKopyalama() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets("Sayfa1") ' Kopyalanacak çalışma sayfasını belirtilir
' Belirtilen çalışma sayfasını kopyalar ws.CopyAfter:=ThisWorkbook.Worksheets(Worksheets.C ount)
' Kopyalandıktan sonra son çalışma sayfasının ardına yerleştiririr End Function
Çalışma Sayfalarını Sıralama
Çok kalabalık çalışma sayfaları barındıran çalışma kitaplarında belirli bir kritere göre sayfaları sıralamaya ihtiyaç duyulabilmektedir. Bu durumda çalışma sayfalarını ilgili kritere göre sıralamak için aşağıdaki kod bloğu kullanılabilir. Burada sıralama alfabetik olarak yapılmış olup farklı kriterlere göre sıralama da gerçekleştirilebilir.
Sub CalismaSayfasiSekmeAdinaGoreSirala() Application.ScreenUpdating = False Dim adet As Integer, i As Integer, j As Integer adet = Sheets.Count 'Çalışma sayfa sayısı For i = 1 To adet – 1 For j = i + 1 To adet If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub
Bu kod metin adlarıyla ve çoğu durumda yıl ve sayılarla da doğru bir sıralama sonucu ile çalışacaktır.