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 özelliği olan opsiyonel bir Key unsurlarına sahiptirler. Dizilerle daha genel bir kıyasalamayı aşağıda bulabilirsiniz.
İ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
' Your code here
Next
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ğimiz 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 t
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 10
sayıcol.Add 20
sayıcol.Add 30
sayıdizi= CollectionToArray(sayıcol)
Debug.Print UBound(sayıdizi) '2(indeks 0dan başladığı için)
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 ihtiyacım olmadı, ama yine de sizlerin 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 bir 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 Collection'ı 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üşünecek 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 çok 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
Sub collfunc()
Dim files As Collection
Set files = dosyacoll()
For Each file In files
Debug.Print file
Next file
End Sub
'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