Worksheet
Excel Nesne Modelinde Workbooktan sonraki seviyede sayfalar gelir. Excelde 4 tip sayfa vardır. Bunlar;
- Çalışma sayfası(Worksheet:En çok bunları kullanacağız)
- Chart Sheet(Başlı başına bir sayfa olan grafik sayfaları)
- Macro Sheet(Bildiğimiz makro değil, eskiyle uyumluluk adına duruyor, biz bunlara hiç girmeyeceğiz)
- Dialog Sheet(Eskiyle uyum adına duruyor, bunlara da girmeyeceğiz)
Biz en çok Sheet ve Worksheet nesnelerini kullanacağız. Yukardaki 4 maddeden anlaşıldığı üzere Worksheet nesnesi, Sheet nesnesinin bir alt türü oluyor. Sheet nesnesi, Sheets koleksiyonunun, Worksheet nesnesi de hem Worksheets koleksiyonunun hem de Sheets koleksiyonunun bir üyesidir. Bu koleksiyonlar bir dosyadaki tüm sayfaları ifade eder ve genelde döngülerde (Döngü konusunu hiç bilmiyorsanız en azından bir For Next için buraya bakıp tekrar gelin), veya toplam sayfa adedini saydırma gibi özelliklerle birlikte kullanılır. Diyelim ki dosyamızda 3 çalışma sayfası bir de grafik sayfası varsa Sheets.Count 4 sonucunu döndürürken Worksheets.Count 3 sonucunu döndürür.
Bunu kendiniz de deneyebilrisinz. Yukardaki özelliklerde bir dosya hazırlayın ve VBE'de şu kodları çalışıtırın
Debug.Print ActiveWorkbook.Sheets.Count
Debug.Print ActiveWorkbook.Worksheets.Count
Not:Biz birçok yerde Sheets ve Worksheets'i birbiri yerine kullanacağız.
Sayfalara erişim ve referans
Sayfalar bir koleksiyon üyesi oldukları için onlara koleksiyonun item özelliği ve bu özelliğin index numarası ile ulaşabiliriz. Item özelliği koleksiyonların default özelliği olduğu için tüm diğer koleksiyonlarda olduğu gibi bunda da yazılmadan es geçilebilir.
Yani Worksheets.Item(1) ile Worksheets(1) tamamen özdeştir. Keza Workbooks.Item(1).Sheets.Item(2) ile Workbooks(1).Sheets(2) de özdeştir. Gördüğünüz üzere başka bir Workbooktaki bir sayfaya da Workbook ile sayfa arasına nokta koyarak ulaşıyoruz. Koleksiyonlarda indexler 1'den başlar(En soldaki sayfanın index değeri 1'dir.)
Sayfalara index numarasıyla olduğu gibi sayfa adı ile de, Worksheets("ilksayfa") gibi, ulaşabiliriz.
Mevcutta aktif olan sayfaya da ActiveSheet ifadesi ile ulaşırız. Activesheetle ilgili detaylara aşağıda değineceğiz.
Seçme ve Aktive etme
Range nesnesinde nasıl bir veya birden çok hücreyi seçmek için Select metodu kullanılıyorsa sayfalar için de aynı metod kullanılır. Yine Range'te olduğu gibi tek bir hücreyi aktive etmek için Activate metodu kullanılıyordu, burda da yine aynı metod tek bir sayfayı aktive etmek için kullanılır.
Aşağıdaki kod ile tüm sayfalarda dolaşıyor ve her sayfanın ilk hücresine artan bir şekilde sıra numarası yazıyorum.
For Each ws In ActiveWorkbook.Worksheets
ws.Select
Range("A1").Value=i
i=i+1
Next
Activate gizli olan sayfaların seçimi için de kullanılabilirken, Select ile gizli sayfalar seçilemez. Mesela şimdi bi dosya açın, 5 sayfası olsun, 2.sini gizleyin. Sonra aşağıdaki kodu çalıştırın.
Sub gizlisec_activateli()
Dim ws As Worksheet ' bu sefer ws'yi Worksheet olarak tanımladım
For Each ws In ActiveWorkbook.Sheets
ws.Activate
Range("a1") = ws.Index
Next
End Sub
Bu kod ile tüm sayfalarda A1 hücresinin dolu olduğunu görürsünüz, 2.sayfayı unhide edip kontrol edebilirsiniz.
Sub gizliyisec1()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.Select 'gizli olduğu için hata verir
Range("a1") = ws.Index
Next
End Sub
Bu yukardakini "sayfa visible mı?" diye kontrol ederek de yapalım, bu sefer hata almayız.
Sub gizliyisec2()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
If ws.Visible then
Range("a1") = ws.Index
ws.Select
End If
Next
End Sub
İsimlendirme
Name özelliği ile sayfanın ismini elde eder veya onu değiştiririz. Yani hem okunan hem yazılan(sets and returns) bir özelliktir. Index ise değiştirilemez bir özellik olup sadece okunurdur.
ws.Name="Yeni sayfa"
Yeni sayfa açma(ekleme) ve silme
Yeni sayfa açmak/eklemek için Add metodu kullanılır.
Syntax:Worksheets.Add(Konum) Konum belirtmezsek aktif sayfanın soluna ekler. Konum belirtmek için parametreleriyle birlikte kullanırız. Worksheets.Add Before:=Worksheets("Sheet3")
Sayfayı yaratırken aynı anda ismini de verebiliriz.
Worksheets.Add(Before:=Worksheets("Sheet2")).Name = "Yeni Sayfa"
Sayfaları silmek için Delete metodunu kullanırız.
Worksheets(1).Delete
Silme işlemlerinde Excel bize uyarı çıkarır, silmek istediğimizden emin miyiz diye. Bu tür uyarılar bazen uzun makrolarda sorun çıkarabilir, hele bir de bilgisayarımızın başında değilkenki bir saate ayarlanmış bir kod ise biz gelip müdahale edene kadar ekranın takılı kalmasına neden olur, ve varsa sonrasında ayarlanmış kodların da çalışmasını engellemiş our. Bu tür durumlarla karşılaşmamak için kodların başına Application.DisplayAlerts=False cümleciği yazılır, ilgili kodlar bittikten sonra da True'ya çevrilir. Bunun detaylarına Application konusunda tekrar geleceğiz.
Sayfaları Gizleme ve Gizleneni tekrar gösterme
Sayfaları gizlemek veya tekrar göstermek için beklenenin aksine bir eylem değil Visible adında bir özellik kullanıyoruz. Bu özellik booelan tipinde değer döndürür.
Worksheets("Sheet2").Visible = False/True
Bir diğer yöntem de bu özelliğe True/False atamak yerine XlSheetVisibility enumerationlarını kullanmak olabilir. Alacağı değerle şöyledir.
- xlSheetVisible:Sayfayı gösterir
- xlSheetHidden:Sayfayı gizler
- xlSheetVeryHidden:Sayfayı öyle bir gizler ki, kullanıcılar sayfalara sağ tıklayıp Unhide tuşuna bastıklarında bile gizli sayfa listesinde görünmez. Bunu nerede kullanmak isteyebilirsiniz? Mesela workbook/worksheet koruma şifrenizi veya bir veritabanı bağlantısı için yazdığnız Connection String içindeki bağlantı parolasını VBA kodu içine doğrudan yazmak istemiyorsunuzdur. Bunu bir xlVeryHidden nitelikli sayafanın bir hücresine yazıp buradan okutturabilirsiniz.
Bu konuyla ilgili örnekler için tıklayınız.
Taşıma ve Kopyalama
Move ve Copy metodularını kullanırız. İkisi de Before(Önce) ve After(Sonra) olmak üzere iki parametre alır.
code class="CodeMirror-activeline-background" data-lang="javascript">
Worksheets("Sheet3").Move After:=Worksheets("Sheet1")
Worksheets("Sheet3").Copy Before:=Worksheets("Sheet1")
Worksheets("Sheet1").Copy Before:=Workbooks("ExcelVBA.xlsm").Sheets("Sheet3")
Worksheets("Sheet1").Copy 'yeni bir dosya açıp ve direkt bu dosyaya kopayalar
Bu metodları tek başına kullandığınızda yeni bir dosya açar ve oraya taşır/kopyalar. Aşağıdaki şu örnekte bu özelliği kullanıyoruz.
Birkaç kopyalama ve taşıma örneğini macro recorder ile kendiniz de yapabilirsiniz. Basit bir konu olduğu için daha fazla detaya gerek görmedim.
Sayfalarda koruma (Protection & Unprotection)
Yazdığımız kodlar, Sayfa korumalı bir dosyada işlem yapmaya kalkarsa şöyle bir hata alırız: "Run-time error '1004': Application-defined or object-defined error".
Bu sorunun üstesinden gelmek için, öncesinde sayfanın korumalı olup olmadığını kontrol edebilir, varsa korumayı kaldırabiliriz. Ama bunun da bir sakıncası olabilir, o da şu ki, kodlarda ilerlerken başka bir hata çıkarsa program durur ve sayfamız korumasız kalır. Bunun için bir de Hata Yakalama kodu yazmamız ve ilgili yerde tekrar koruma koymamız gerekir.
Korumaya almak Protect, korumayı kaldırmak Unprotect metodu ile sağlanır.
Şimdi bir örnek yapalım. Yeni bi dosya açın ve ilk sayfasına koruma uygulayın, şifresi de 1234 olsun. Sonra da şu kodu ekleyip çalıştırın.
Sub sayfakoruma1()
Dim ws As Worksheet
Set ws = ActiveSheet
If ws.ProtectContents = True Then 'koruma var mı diye sorguluyoruz
ws.Unprotect Password:="1234"
End If
'On Error GoTo hatayakala 'herhangi bir hatada sayfayı tekrar korumaya almak için ilgili yönlendirmeyi yapıyoruz
Range("A1") = Environ("USERNAME")
'çeşitli işlemler
'0'a bölme olacak ve hata alacak
a = InputBox("Bir sayı girin")
b = InputBox("Bu sayıyı kaça bölelim")
MsgBox "Sonuç: " & a / b
ws.Protect Password:="1234"
Exit Sub
'hatayakala:
'ws.Protect Password:="1234"
End Sub
İlk olarak, a için abc değerini girin, b için bir sayı girin. İkinci denemede de b için 0 değerini girin. İlki sayısal bir değer girmediğmiz için, ikincisi de 0'a bölmeye çalıştığı için hataya neden olacaktır. Hata yakalama kodları commentli olduğu için de dosyamız protectionsız kalacaktır.
Şimdi yukardaki commentli kısımları commentsiz hale getirip tekrar çalıştıralım. Bu sefer hata yakalanacak ve çıkmadan önce tekrar şifreleme yapılacaktır.
NOT:Filtrelemeye izin verme gibi seçeneklerden faydalanmak icin makro kaydetme aracından faydalanabilirsiniz. Ancak bu şekilde protection koymaya çalıştığınızda recorder şifreyi kaydetmez, bunu manuel eklemeniz gerekir.
Protectionlı sayfalara devam
Yukarda korumalı sayfalarda çalışma şeklini görmüştük. Korumalı sayfalarla çalışmanın daha şık bir yolu var aslında. UserInterFaceOnly parametresi.
Bu parametreye True değeri atanırsa, sayfa sadece kullanıcı işlemlerine karşı korumalı olur, VBA kodları için koruma geçersiz olur. Gördüğünüz gibi Microsoft'taki abiler bunu da düşünmüşler. Bu parametrenin default değeri False olup değer atanmaması durumunda tahmin edeceğiniz üzere full koruma sağlanır.
Yanlız unutulmamalıdır ki, bu argüman sadece bir kereliğine koruma sağlar. Dosyayı kapatıp tekrar girdiğinizde, full korumalı açılır. Bu özelliğin daimi olması için Workbook_Open eventi içine uygun kod yazılabilir.
ActiveWorkbook.Worksheets("calculatedlar").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("calculatedlar").Sort.SortFields.Add Key:=Range( _
"F2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("calculatedlar").Sort
.SetRange Range("A2:F175")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Recorder'ın ürettiği kodda bir iki yeri değiştirmek gerekir, böylece makromuz daha dinamik hale gelir.
- Worksheets("calculatedlar")>ActiveSheet yapalım
- Key:=Range("F2")>Key:=ActiveCell(veya duruma göre kalabilir)
- Range("A2:F175")>Bunu nasıl değiştireceğimizi Range konusunda görmüştük
Keza filtreleme işleminin kodu da basit olup, dinamik hale getirilecek kısımları değiştirmek yeterlidir.
Range("B1").Select
ActiveSheet.Range("$A$1:$F$175").AutoFilter Field:=2, Criteria1:="Şube"
Bu örnekte Range("$A$1:$F$175") yerine Range("A1").CurrentRegion denilebilir. Görüğünüz gibi Sort işleminde başlık hariç tutulurken burada başlık satırı dahildir. O yüzden Resize ve Offset kullanmadan işimizi halledebiliriz.
Filtre modları ve filtreyi kaldırma
Filtrelerle ilgili olarak kafa karışıklığına neden olabilecek iki konur var. Filtrenin açık/kapalı olması ile kriterin uygulanıp/uygulanmamış olması. Bunlardan ilki AutoFilterMode özelliği ile elde edilirken ikincisi FilterMode özelliği ile elde edilir.
Filtreyi uygulamak/kaldırmak Range nesnesinin AutoFilter metodu ile olur.
- Filtre henüz yokken kullanılırsa filre uygulanır
- Yanında parametre yoksa sadece filtre okları görünür
- Parametreyle uygulanırsa direkt ilgili filtreleme yapılmış olur
- Filtre varken kullanılırsa kriterler silinir ve filtre okları kalkar
Kriter uygulanmış bir data kümesinde kriterleri silmek ama filtre oklarını açık bırakmak için Worksheet nesnesinin ShowAllData metodu kullanılır.
Aşağıda bütün bu durumları anlatan güzel bir örnek var. Bu örnekte sayfadaki data kümesinin rasgele olarak 4 durumundan birine girmesini sağlıyoruz, sonra hangi durumdaysa onunla ilgili bir mesaj veriyorum.
Sub filtresub()
a = WorksheetFunction.RandBetween(1, 4)
MsgBox "Case " & a & " gerçekleşecek"
Select Case a
Case 1 'o an kapalıysa kapalı kalmaya devam, açıksa kapanır
ActiveSheet.AutoFilterMode = False 'buna sadece false atanıyor, true atanamaz
Case 2 'açıkken filtre konursa kapanır
If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter
Case 3 'kapalıyken filtre konursa açılır
If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
Case 4 'filtre okları aktif olsa da olmasa da burası çalışır
ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Range("b2")
End Select
If ActiveSheet.AutoFilterMode = True Then 'filtre okları açıksa. Case 3 veya 4
If ActiveSheet.FilterMode = False Then
MsgBox "Case 3:Filtre açık ama kriter yok"
Else
MsgBox "Case 4:filtre açık ve kriter var, şimdi kriter kaldırılacak, ama filtre açık kalacak"
ActiveSheet.ShowAllData
End If
Else 'filtre okları yoksa, yani henüz bir autofilter düğmesine basılmamışsa
MsgBox "Case 1 veya 2:filtre yok"
End If
End Sub
Sayfa ayarları
Page Setup, Print v.b işlemleri için macro recorderdan faydalanmanızı öneriyorum.
Calculation
Calculation konusuna Application nesnesine detaylıca değiniyoruz. Önemli bir metod olup Application konusunda mutlaka incelemenizi tavsiye ediyorum.
Sayfalarda gezinme
Sayfalarda dolaşma işini yukarıdaki birçok örnekte gördüğümüz gibi bir döngü aracılığı ile yapabilmekle birlikte, sadece bir sayfa geri veya ileri gitme işini, sayfa adı v.s yazmadan kolayca yapmamızı sağlayan iki özellik var.
Activesheet.Next.Select ' sonraki sayfa
ActiveSheet.Previous.Select ' önceki sayfa
Hafızadan birşey yapıştırma
Hafızadaki(Clipboard) bilgiyi aktif sayfadaki aktif hücreye yapıştırmak için iki metod var. Birincisi Worksheet sınıfının normal Paste metdou, ikincisi Range nesnesinin PasteSpecial metodu. Worksheet.Paste metodu ile Excel arayüzünde yaptığımız gibi içerikte ne varsa yapıştırılır:Veri, formül, format v.s. Range.PasteSpecial ile ise yine Excel arayüzde yaptığımız özel yapıştırma türlerini yapabiliyoruz. Sadece değerleri, sadece formatı v.s.
Aşağıdaki kod ile 1.sayfada aktif hücrenin CurrentRegion'ındaki hücreleri 2.sayfadaki aktif hücreye yapıştırıyoruz.
Sheets(1).Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets(2).Select
ActiveSheet.Paste
Bu arada Worksheet sınıfının da PasteSpecial metodu var ama biz onu çok kullanmayacağız, zira bununla Grafik gibi nesneleri veya Access gibi diğer uygulamalardan birşeyler yapıştırabiliyorsunuz.
ActiveChart.ChartArea.Copy
Range("M2").Select
ActiveSheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
DisplayAsIcon:=False
Aşağıdaki örneği de MSDN'den aldım, burada diğer format seçeneklerine de bakabilirsiniz. Başka programlarla çalışmayacaksanız çok sık ihtiyacınız olacağını sanmam.
Worksheets("Sheet1").Range("F5").PasteSpecial _
Format:="Picture (Enhanced Metafile)", Link:=False,
DisplayAsIcon:=False
Parent özelliği
Bazen bir sayfanın hangi workbookta olduğunu elde etmek isteriz. Bunun için hiyerarşide bir üst basamağa çıkmamızı sağlayan Parent özelliğini kullanırız.
Debug.Print TypeName(Activesheet.Parent) 'Workbook
Debug.Print Activesheet.Parent.Name 'ilg
Tüm sayfaları ayrı workbooklar olarak kaydetme
Bu örnekte açık olan bir dosyadaki tüm sayfaları ayrı ayrı dosyalar olarak seçilen bir klasöre kaydediyoruz.
Sub sayfaları_wb_olarak_kaydet()
Dim ws As Worksheet
Dim wb As Workbook
Dim fd As FileDialog
Dim klasör As String
Dim dosya As String
On Error GoTo hata
Application.ScreenUpdating = False
Set wb = Application.ThisWorkbook
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show = True Then
klasör = fd.SelectedItems(1)
Else
Exit Sub
End If
For Each ws In wb.Worksheets
ws.Copy
'Burada bi versiyon kontrolü yapılmasında fayda var, ama örneği basitleştirmek adına onu es geçiyorum ve default formatta kaydetmesine izin veriyorum
dosya = klasör & "\" & Application.ActiveWorkbook.Sheets(1).Name & ".xlsx"
ActiveWorkbook.SaveAs dosya
ActiveWorkbook.Close False
Next
Call Shell("explorer.exe " & klasör, vbNormalFocus)
Application.ScreenUpdating = True
Exit Sub
hata:
Application.ScreenUpdating = True
MsgBox Err.Description
End Sub
Tüm sayfaları Unhide etme
Bu örnekte tüm gizli sayfaları açıyoruz. Buna QuickAccessBardan erişmek isteyebilirsiniz, ben öyle yapıyorum açıkçası.
Sub tümsayfalarunhide()
For i = 1 To Sheets.Count
Sheets(i).visible = True
Next i
Sheets(1).Select
End Sub
İlk sayfa hariç tümünü hide etme
Diyelim ki bir sayfasında Karne, diğer sayfalarında bu karneyi besleyen sayfaların olduğu bir dosyanız var. Sık sık sayfaları Unhide ve tekrar Hide etme ihtiyacınız oluyor. Bir üstteki kod ile tüm sayfaları Unhide etmiştik. Datayla oynadıktan sonra şimdi tekrar gizleyeceğiz. Bunu da QuickAccessBara eklerseniz müthiş pratiklik sağlar. Bu kodu Karne sayfasındayken çalıştırmanız gerekiyor.
Sub aktifhariç_hideall()
Dim ws As Worksheet
Set ws = ActiveSheet
For i = 1 To Sheets.Count
If i <> ws.Index Then
Sheets(i).visible = False
End If
Next i
End Sub
Tüm sayfalarda ilk kolonda A'dan Z'ye sıralama
Diyelim ki size birçok sayfası olan bir dosya geldi. Ama sayfaların hiçbiri sıralı değil. Bu örnekte tüm sayfalarda A'dan Z'ye sıralama işlemini tek seferde yapıyoruz
Sub tümsayfalarda_sırala()
For i = 1 To Sheets.Count
Sheets(i).Select
ActiveWorkbook.Sheets(i).Sort.SortFields.Clear
ActiveWorkbook.Sheets(i).Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Sheets(i).Sort
.SetRange Range(Range("A2"), Range("a2").End(xlDown).End(xlToRight))
.header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Next i
End Sub