28.12.2017 tarihinde,VBA konularına Dosya Okuma/Yazma sayfası eklenmiştir.

20.12.2017 tarihinde,sitem mobil uyumlu hale gelmiştir.

02.12.2017 tarihinde,Excel Fonksiyon konularına "Dizi Formülleri ve SUMPRODUCT" sayfası eklenmiştir.

31.10.2017 tarihinde,VBA Dictionary sayfasına Dictionary, Collection ve Collection dizisinden oluşan ilginç bir örnek eklenmiştir.

26.10.2017 tarihinde,Excel Fonksiyon konularına "İsatistiki ve Matematiksel fonksiyonlar" sayfası eklenmiştir.

VBAMakro Diziler ve Dizimsi Yapılar 2

Collectionlar

Collection'lar, Diziler gibi benzer özellikli öğeleri bir arada tutan yapılardır. Dizilerden farklı olarak, tek bir veri tipleri yoktur, yani içlerinde aynı anda hem sayı hem metin tutabilirler. Ayrıca dizilerdeki boyut yerine sayısal bir Index ile metinsel bir Key unsurlarına sahiptirler. Dizilerle daha genel bir kıyasalamayı aşağıda bulabilirsiniz.

Genel Bakış

İki tür Collection

Daha derinlere dalmadan, birkaç tanıdık Collection'dan bahsetmekte fayda var. Workbooks, Worksheets, Sheets v.s. Bunlar Excel'in yerel(built-in) collectionlarıdır. Bunlar VB6 ve VB.Net'teki Collection class'ını baz alırlar ve çok daha fazla üyeye sahiptirler, o yüzden daha kullanışlıdırlar. Bizim burada işleyeceğimiz ise Collection classının VBA uyarlaması olup, VB6/VB.Net versiyonuna göre biraz daha çelimsizdir.

Yerel collection'lar VB'nin Collection sınıfından geldikleri için yereldirler, o yüzden ayrıca tanımlanmazlar, Excel açıldığı anda otomatikman kullanılır haldedirler(Hepsi olmasa da büyük çoğunluğu).

O yüzden şöyle bir kod yazmak çok anlamsızdır.

Dim wsc As New Worksheets
For Each ws in wsc
      ......
Next ws

Onun yerine Worksheets'i doğrudan kullanırız.

For Each ws in Worksheets
      ......
Next ws

Tanımlama

Şimdi, konumuz olan Collectionlara gelecek olursak bunların tanımlaması ve kullanımı iki şekilde olabilir.

Dim col As Collection 'Tanımlandı
Set col = New Collection 'Hafızada yer ayrıldı

Başka bir tanımlama şekli de tanımlama ve yaratma işleminin tek satırda olduğu şekildir.

Dim col As New Collection 

İlk yöntem her zaman en güvenli yöntem olmakla birlikte eski bir bilgisayarınız yoksa performans sorunu yaşamayacağınızı düşünerek ikinci yöntemi de güvenle kullanabilirsiniz. Bir diğer fark da şudur: Eğer yarattığınız collection üzerinde "Nothing mi?" kontrolü yapmanız gerekiyorsa ilk yöntemde(o an sadece ilk satırı yazılmışsa) yaratılan Collection bir değer döndürmezken, yani Nothing iken, ikinci yöntemle yaratılan False döndürür. Çünkü ikinci yöntemde New diyerek atamayı da yapmış oluyoruz, ilk yöntemde ise sadece hafızada yer ayırmış olduk, henüz Collection nesnesini yaratmadık. Aşağıda bunu açıklayan bir örnek görebilirsiniz.

Sub newnothingcol()
    Dim col1 As Collection    
    Dim col2 As New Collection
   
    MsgBox "col1'in türü " & TypeName(col1)
    MsgBox "col2'nin türü: " & TypeName(col2)
    
    If col1 Is Nothing Then 'buraya girer
        MsgBox "col1 hiçbirşeydir"
    End If
        
    If col2 Is Nothing Then 'buraya girmez
        MsgBox "col2 hiçbirşeydir"
    End If

    Set col1 = New Collection
    MsgBox "col1'in türü: " & TypeName(col1)

End Sub

Sınıfın Üyeleri(Property ve Metod)

Bu sınıfın yanlızca 4 üyesi vardır. Add, Remove, Item, Count.

Üye sayısı az olduğu için bunlara tek tek bakacağız.

Add metodu ile yeni eleman eklemek

Üyelerden en sık kullanılanıdır ve ana üyedir diyebiliriz.

Add metodu default olarak, yeni elemanları en sona ekler. Bununla beraber Before/After parametresi ile belirli bir indeksten önce veya sonra da eklenebilir.

Col.Add 10 'İçerdeki elemanlar:10
Col.Add 20 'Şimdi 10, 20
Col.Add 30, Before:= 1 'Şimdi 30, 10, 20

Excel'in built-in Collectionlarında da yine bu Add metodunu kullandığımızı biliyorsunuz, aşağıdaki gibi:

Workbooks.Add
Worksheets.Add 'bunda After ve Before da kullanılabilir

Ekleme işlemi yapılırken opsiyonel bir parametre olan Key de belirtilebilir. Key'in amacı, ilgili elemana indeks yerine ismi ile de ulaşmayı sağlamaktır.

Col.Add 10, "on"

Bu parametre string bir ifadedir ve benzersiz olmalıdır. Yani Item olarak 10'u birden fazla ekleyebilrisiniz(niye yapasınız ki :)), ama bunun key'i olan "on" sadece bir kez geçmeli.

 Dim notlar As New Collection

notlar.Add 67, "Volkan"
notlar.Add 67, "Meltem" 'item 2.kez geçiyor, hata vermez
notlar.Add 12, "Meltem" 'key 2.kez geçiyor, hata verir

Pratikte key kullanımı çok olmayacaktır diye tahmin ediyorum, daha çok Excelin kendi Collection'larında kullanılır. Workbook veya Worksheetlerde bunu yapıyoruz; Worksheets(1) yerine Worksheets("krediler") yazmak gibi.

Bununla birlikte, bölge kodu-bölge adı gibi bir ikili bilgiyi aynı anda eklemek istiyorsanız Collection yerine Dictionary kullanmanızı öneririm. Açıkçası bana yerel Collectionda Key kullanımı anlamlı geliyor ama Collection sınıfında bu özelliği neden koymuşlar anlayamadım, Dictionary bu ihtiyacı oldukça karşılarken!

Remove ile elemanları silmek

Eklenen elemanları çıkarmak için Remove metodunu kullanırız. Elemanın indeks numarasını(sade veya parantez içinde) ve Key'i belirtmek yeterlidir.

Şimdi 4.indekste 20 diye bir Item olduğunu düşünün, bunun Key'i de "yirmi" olsun. Bunu kaldırmak için şunlar yazılabilir:

col.Remove 4 
col.Remove (4)	 
col.Remove "yirmi"	 

Collectiondaki tüm elemanları silmek yani collection'ı boşaltmak için For Next içinde Remove kullanırız.

For i = coll.Count To 1 Step -1
    coll.Remove i
Next i

Collection'ı boşaltmaya benzese de tamamen farklı şey olan iki yöntem daha vardır.

'Collection'ı yeniden tanımlamak.
Set col = New Collection 

'veya onu yoketmek.
Set col = Nothing	

Count

Belirli bir anda Collection'ımız içinde kaç eleman var bunu kontrol etmek istersek Count özelliğini kullanırız.

Debug.Print col.Count
Sub collornek()
Dim col As New Collection
col.Add 10, "on"
col.Add 20, "yirmi"

Debug.Print col.Item(2)
Debug.Print col(2)
Debug.Print col("yirmi")
Debug.Print col.Count

col.Remove "yirmi"
Debug.Print col.Count

Set col = Nothing
Debug.Print col.Count

End Sub

Elemanlara erişim

Indeks(item no) ile veya doğrudan item numarası ile elemanlara erişebiliriz. Item özelliği Collectionlar için default özellik olduğu için bunu belirtmesek de olur. Bu arada dizilerden farklı olarak Collectionlarda indeks 0'dan değil 1'den başlar. Daha önce belirttiğimiz gibi elemanlara ismiyle ulaşmak da mümkündür, tabiki eklerken Key ile eklemişsek.

Sub collornek()
Dim col As New Collection
col.Add 10, "on"
col.Add 20, "yirmi"

Debug.Print col.Item(2) '20 yazar
Debug.Print col(2) '20 yazar
Debug.Print col("yirmi") '20 yazar
End Sub

İlk ve Son üyeye erişmek için

Collectionlar sıralı yapılar oldukları için ilk ve son eleman özel öneme sahiptirler, en azından bazı durumlarda böyledir. İlk elemana ulaşmak basit, indeksi 1 veririz. Son elemana ulaşmak için eleman sayısını saydırır, ve bu sayıyı indeks olarak veririz.

col(1) 'ilk eleman
col(col.count) 'son eleman

Tüm elemanlar üzerinden geçmek için For döngüsü kullanırız. Klasik veya For Each.

'Klasik For
For i = 1 To coll.Count
   Debug.Print coll(i)
Next i

'For Each
For Each m In meyveler
    Debug.Print m
Next m

Farkettiyseniz elemanları sadece okuduk, yani onları çağırdık ve değerlerini ekrana yazdırdık. Onlara bi değer atamadık, değerlerini değiştirmekdik. Bunun bir sebebi var: Collectionlar read-onlydir. Yani eklediğimiz elemanların değerini değiştiremeyiz, onları sadece okuruz. Bu önemli bir dezavantaj gibi görünmekle birlikte kullanım amacına uygun kullandığımıda çok da aradığımız bir özellik değildir. Ama siz ille de değiştirilebilir bir dizi yapısı kurmak istiyorsanız, ya normal dizi ya da Dictionary kullanmalısınız.

NOT:Collectionlarla hiçbir zaman Key'i elde edemeyiz. Bu ifade, Collection'ların Readonly olmasından farklı birşeydir. Key'i elemana ulaşmak için kullanırız ama ona değer atayamayız,  değerini de elde edemyiz. Yani 1.indekste yer alan Item=10, Key="On" olan bir elemana Col(1) diyerek 10 değerine ulaşabilirken "On" değerine hiçbir şekilde ulaşamayız. "On"u sadece bu elemana ulaşırken indeksin alternatifi olarak kullanırız.

"Elemanlar arasında X var mı?" kontrolü

Collection sınıfının çok az üyeye sahip olduğunu gördük. Ve bunların arasında da başka dillerde olan Contain, Exists gibi "Var mı?" kontrolü yapacak bir metodu yok. Bunun yerine bu işi görecek bir fonksiyon yazılmaktadır.

Function ColdaVarmı(col As Collection, kontrol As Variant) As Boolean
    On Error Resume Next
    ColdaVarmı= False
    Dim x As Variant
    For Each x In col
        If x = kontrol Then
            ColdaVarmı= True
            Exit Function
        End If
    Next
End Function

'kullanımı da şöyledir
Dim sayılar As New Collection
sayılar.Add 10
sayılar.Add 20
sayılar.Add 30
 
Debug.Print ColdaVarmı(sayılar, 20) 'True döner
Debug.Print ColdaVarmı(sayılar, 40) 'False döner

Başka bir fonksiyonu şöyle yazabiliriz. Buradan alıntıdır. Bu daha pratiktir. Yaptığı iş şudur. Contains'e önce doğrudan True atıyor, sonra obj değişkenine col(key) ile atama yapmaya çalışıyor, sanki varmış gibi. Eğer varsa atar ve fonksiyondan çıkar, Contains'e de en son True atandığı için True döner; obj=col(key) satırı hata alırsa, yani bu key bu Collection içinde yoksa, hata ele alma bloğunda Contains'e False atanır.

Public Function Contains(col As Collection, key As Variant) As Boolean
Dim obj As Variant
On Error GoTo err
    Contains = True
    obj = col(key)
    Exit Function
err:

    Contains = False
EEnd Function

'kullanım şekli yukardaki örnekle aynıdır

Dizi ve Collection karşılaştırması

  • Dizilerde Index 0'dan da 1'den de başlayabilir, Collectionlarda her zaman 1'den başlar.
  • Dizilerin aksine Collectionlarda yeni eleman ekleme ve çıkarma oldukça basittir, herhangi bir boyut, index v.s belirtmeden eleman eklenebilmektedir.
  • Diziler genellikle belirli bir boyuta sahiptirler, bu ya statik dizlerdeki gibi baştan belirlenir veya dinamik dizilerdeki gibi sonradan belirlenir, her halükarda genelde dizinin boyutu sabittir. Tabiki dinamik dizilerde ReDim Preserve deyimi kullanılarak dizinin içeriği korunacak şekilde boyut artırılabilir ancak bu birkaç kez kullanılırsa verimsiz bir yöntem olur. İşte böyle durumlarda collection kullanmak daha mantıklıdır, zira collectionlarda boyut diye birşey yoktur.
  • Collectionlar, eleman sayısı artınca hantallaşır ve performans kayıpları yaşanabilir. (Binlerce elemandan bahsediyorum tabi)
  • Collectionlar read-only'dir. Yani eğer elemanları değiştirmeyi düşünüyorsanız veya en azından böyle bir ihtimal varsa Collection yerine dizi kullanmalısınız.

Genelleme yapacak olursak, boyut baştan bilinmiyor ve çok sık değişecekse Collection kullanmak gerekirken, boyut baştan (veya ilerde bi noktada) biliniyor ve sabit ise dizi kullanmak daha doğru bir yol olacaktır .

Dictionary ile karşılaştırma

Dizilerle kıyastan ziyade, Collectionları aslında Dictionary'ler ile kıyaslamak daha mantıklıdır. Dictionary'lerin çok esnek yapıları olmasına rağmen neden Collection kullanalım ki? diye soranlar için bir karşılaştırma listesi Dictionary bölümünde yer almaktadır. Buradan bakabilirsiniz.

Bir örnek

İşyerinde, sık güncelleme yaptığım bazı Access dosyalarım bulunmakta. Bilen bilir, Access dosyalarında tablolara yeni alan ekledikçe boyut büyür, üstelik eskisini silip yenisini yükleseniz bile. İçerdeki data büyümediği halde boyut büyümesini engellemek için Compact işleminin yapılması gerekir. Bunlar da büyük dosyalar için vakit alan işlemlerdir. Benim de Application konusunda ele aldığım gibi Schedule edilmiş bir sürü rutinim var. Onlardan biri de bu accessleri compact edip boyutlarını küçülten bir makro. Schedule ediyorum, çünkü neden bu iş gece olabilecekken ben PC başındayken olsun ki!

Aşağıdaki kodda,

Sub accessleri_compact()
'tüm dosyaların kapalı olması lazım, çünkü exlusively açılıyor: Bu işi acceslerin içine timer koyarak hallettim
On Error Resume Next 
Dim app As Object
Dim DBler As New Collection 'dosyalara tek tek aynı işlemi yapmamak için bir collectiona atayacağız
Dim çalıştımı As Boolean
Dim IMsgFilter As Long 'ole mesajını yoketmek için. Buna takılmayın şimdi.

CoRegisterMessageFilter 0&, IMsgFilter 'ole mesajını yokediyoruz

adet = 0

Set app = CreateObject("Access.Application")

'dosya isimlerini değiştirirerek veriyorum
DBler.Add ("C:\..........accdb")
DBler.Add ("C:\..........accdb")
DBler.Add ("C:\..........accdb")
DBler.Add ("C:\..........accdb")

'şimdi de collection içinde geziniyor ve her eleman için aynı işlemi yapıyoruz
For Each d In DBler
    cmp = Left(d, Len(d) - 6) & "_cmp.accdb"
    okmi = app.CompactRepair(d, cmp, False) 'boolean döndürdüğü için böyle yapıyoruz
    
    If okmi = True Then 'başarılı şekilde compact olduysa
        If FileLen(d) = FileLen(cmp) Then 'eğer compact sonucunda dosya daha da küçülmediyse, boşuna işlem yapmaya gerek yok,
                                          'sadece yeni üretilen dosyayı silelim, böylece dosyamızın son erişim tarihini de değiştirmemiş oluruz
            Kill cmp
        Else
            Kill d 'orjinal dosyayı siliyoruz
            Name cmp As d 'Kompakt edilen dosyayı orjinal ismi ile rename ediyoruz
            adet = adet + 1
        End If
    End If
    
Next d

Set app = Nothing

If adet > 0 Then
    rapor = "access compact " & adet & " out of " & DBler.Count
    alici = "12345" 'benim sicilim ve aynı zamanda mail adresim
    Call Mailat2(rapor, alici) ' bu kendime bilgi maili gönderen bi başka prosedür
End If

CoRegisterMessageFilter IMsgFilter, IMsgFilter 'ole mesjaını restore ediyoruz
Exit Sub

hata:
    CoRegisterMessageFilter IMsgFilter, IMsgFilter 'ole mesjaını restore ediyoruz
    rapor = "accesler compact"
    alici = "35516"
    Application.Run "Personal.xlsb!mailnogo", rapor, alici
End Sub	

İleri konular

Collectionları prosedürlere parametre olarak göndermek

Tıpkı dizilerde olduğu gibi Collection'ları da parametre olarak başka bir posedüre gönderebiliriz. Dizilerdeki örneği buraya da uyarlayabiliriz.

Sub colgonder()
Dim col As New Collection

col.Add "volkan"
col.Add = "meltem"

Call Nesajver(col)
End Sub


Sub Mesajver(coll As Collection)
    MsgBox "Bu collectionda " & coll.Count & " adet elaman var"
End Sub	

Bu yöntemin güzel bir örneği de hemen bi alttaki kısımda yer almaktadır.

Collectionları Dizilere dönüştürme

Bazı durumlarda elde ettiğimz Collection'ı, Dizi özelliklerinden faydalanmak veya parametre olarak Dizi alan bir fonksiyonda kullanmak için diziye çevirmemiz gerekir. Aşağıdaki fonksiyon bu işi yapmaktadır.

Function CollectionToArray(col As Collection) As Variant()
    Dim arr() As Variant, i As Long, t As Variant

    'collectiondaki elemans sayısından 1 çıakrıp dizi boyutunu belirliyoruz
    ReDim arr(col.Count - 1) As Variant 
    'tüm elemanlar tek te diziye atanır
    For Each t In col
        arr(i) = t
        i = i + 1
    Next it
    CollectionToArray = arr
End Function

'Aşağıda da kullanım örneği bulunuyor
Sub TestCollectionToArray()
    Dim sayıcol As Collection, sayıdizi() as Variant
    Set sayıcol = New Collection
    sayıcol.Add 1
    sayıcol.Add 2
    sayıcol.Add 3
    sayıdizi= CollectionToArray(sayıcol)
End Sub 

Collection Collectionı(İçiçe Collection)

İçiçe dizilerde böyle bir kullanım şeklinin amacını ve yöntemini görmüştük. Henüz bakmadıysanız oraya bakmanızı tavsiye ederim. Benim böyle bir kullanım şekline şimdiye kadar ihtiiyacım olmadı, ama yine de sizilerin olabilir diye aşağıya bytecomb sitesinden aldığım bir örneği koyuyorum. Sitede belirtildiği gibi, eğer içteki kümeye sık sık eleman ekleyecekseniz içiçe dizi yerine içiçe collection kullanmak daha mantıklıdır. Onun dışında pek bi kullanım farkı yok gibi görünüyor.

Dim cAnimals As New Collection 
 
' Let's add stats on the Cheetah
Dim cCheetah As New Collection
 
' Easy to add inner collections to the outer collection.  Also, cCheetah refers
' to the same collection object as cAnimals(1).  
cAnimals.Add cCheetah          
 
' Easy to add items to inner collection.
' Working directly with the cCheetah collection:
For Each vMeasurment In GetMeasurements("Cheetah")
    cCheetah.Add vMeasurement
Next
 
' Working on the same collection by indexing into the outer object
For i = 1 To cAnimals.Count
    For j = 1 To cAnimals(i).Count
        cAnimals(i)(j) = cAnimals(i)(j) * dblNormalizingFactor
    Next
Next

Fonksiyonlarda dönen değer olarak

Bazen bir Colleciton'ı birkaç farklı prosedür içinde kullanmak gerekebilir. o yüzden bunu bir fonksiyon olarak yazıp, dönen değer olarak da ilgili Collection'ın gelmesini sağlayabiliriz.

Aşağıda benim işte kullandığım bir fonksiyon ve bunun kullanım örneği bulunmakta. Her ay Database şifrelerimizin süresi dolmaktadır ve bu yüzden şifrelerin aylık olarak yenilenmesi gerekmektedir. Benim SQL'lerin çoğu da Excel'e gömülü ve schedule edilmiş durumdalar. Şimdi yaklaşık 50 civarı rapor olduğunu ve bazısında birden çok connection olduğunu düşünüecek olursak toplam connection sayısı 100 civarında olduğunu söyleyebilirim. Bunların bazısı 5-10 sn'de çalışırken bazısı 10-15 dk sürebiliyor. Bunların her birinde manuel değişiklik yapmak çooooooook uzun zaman alacaktır, sadece connection sayısı çok olduğu için değil aynı zamanda her manuel değişiklik sonrasında sorguların çalışacak olması ve benim bunların bitmesini beklemem gerektiği için.

İşte bu kod ile, öncelikle dosyaları collection'a atıyorum. Bu collection'ı hem şifre değiştirmede hem de sonrasında değiştirdiğim şifrelerin hepsinin değişip değişmediğini görmek için iki kez kullanıyorum. Bu yüzden bir fonksiyona atamak daha mantıklı oldu. Dosyaları açmadan önce otomatik refresh olmasınlar diye EnableEvents=False yapıyorum, varsa protectionları geçici kaldırıyorum, bu detayları aşağıdaki koda koymadım, sadece kafanızda soru işareti olabilir diye belirtmek istedim. Kodun diğer detaylarını Connection'ları işlediğim sayfada veriyor olacağım.

'fonksiyonu çağırma ve kullanma
Dim files As Collection
Set files = dosyacoll()
For Each file In files
......
Next file


'Fonksiyonun kendisi
Function dosyacoll() As Collection
Const gunlukyol As String = "…………."
Const haftalıkyol As String = "…………."
Const bbsp As String = "…………."
Const hgtakip As String = "…………."
Dim files As Collection
Dim DBtür As Byte

DBtür = Application.InputBox("DB türünü girin. Oracle 1.grup için 1, 2.grup için 2, DB2 için 3", Type:=1)


'collecitionı oluşturalım
Set files = New Collection
With files
    If DBtür = 1 Then
            .Add (hgtakip + "Miy access data.xlsb")
            'Diğer 20 küsur rapor
            '.....    
    ElseIf DBtür = 2 Then
            '10 küsur rapor
            '.....    
    ElseIf DBtür = 3 Then
            '10 küsur rapor
            '.....    
    Else
        MsgBox "yanlış DB türü girdiniz"
        Application.EnableEvents = True
        Exit Function
    End If
End With

Set dosyacoll = files

End Function

YORUMLAR