WorksheetFunction

WorksheetFunction Class'ı ve Kullanımı

VBA'in en önemli fonksiyonlarından bir grup da WorksheetFunction class'ı içinde bulunan metodlardır. Bunlar, adı üzerinde, Excel içinde kullanılan fonksiyonlardır. Burada tüm fonksiyonlar bulunmamakla birlikte önemli bir kısmı dahil edilmiştir. Nelerin olduğunu, aşağıdaki gibi WorksheetFunction yazıp nokta koyduktan sonra intellisense aracılığıyla görebilirsiniz (veya F2'ye basıp object browser'dan da arayabilirsiniz). Genelleme yapacak olursak VBA eşleniği olan fonksiyonlar burada yoktur. Now, Left, Mid gibi.

Bunlar aslında UDF'in tersi gibi düşünülebilirler. Nasıl ki VBA aracılığıyla Excel'e yeni fonksiyonlar kazandırabiliyorsak, Excel aracılığıyla da VBA'e ek fonksiyonlar kazandırmış oluyoruz.

Mesela bir lookup işlemi yapmak istediğinizde tıpkı Excel içinde VLOOKUP yazarmış gibi VBA içinden bu lookup işlemini yapabilirsiniz. Bunun sonucunu bir hücrede görmek yerine bir değişkenin içine depolamış olursunuz, o kadar.

ÖNEMLİ NOT: Bazen WorksheetFunction yerine direkt Application nesnesinin kullanıldığını görebilirsiniz. Bu bağlamda ikisi özdeştir diyebiliriz, aşağıdaki örnekteki gibi. Ancak siz yine de WorksheetFunction'ı kullanın, zira yapılan testler bunun %20 civarında daha hızlı olduğunu söylüyor. Üstelik Application'lı versiyonda Intellisense de çıkmamaktadır. Hata yakalama bağlamında da farkları var ancak o detaya girmeyeceğim, arzu eden buradan bakabilir.

                        
                            x = Application.Sum(Range("A1:A10"))
                            x = WorksheetFunction.Sum(Range("A1:A10"))

Örnekler

Örnek 1

İlk örneğimizde sistemden çektiğimizde 12 haneden küçük portföy kodlarını 12 haneye tamamlayan bir UDF var. Bu örnekte en başa gereken miktarda 0 konulmaktadır. Bunun Excel'de yapmak için kendisine çok benzeyen şu formülü girmem gerekirdi:

                        
                        =REPT("0", 12 - LEN(A2)) & A2

VBA'de ise bu şekilde yapabiliriz:

                        
                        Function portföy12(pk As Range)
                            portföy12 = WorksheetFunction.Rept("0", 12 - Len(pk)) & pk
                        End Function

Hangisini yazmak daha kolay, Excel formülünü mü, UDF'i mi? Böylece bir önceki bölüme ithafen UDF'lerin gücünü de anmış olalım.

Bu arada bunu alternatif olarak şöyle de yapabilirdik ancak WorksheetFunction'ı örneklemek adına böyle yaptık:

                        
                                Function portföy12(pk As Range)
                                    portföy12 = String(12 - Len(pk), "0") & pk
                                End Function

Ama diyelim String yöntemi VBA'de yok, siz de WorksheetFunction'ı bilmiyorsunuz. Bu durumda bu UDF'i şöyle yazardık:

                        
                            Function portfoy12(pk As Range)
                                ilave = 12 - Len(pk)

                                For i = 1 To ilave
                                    ek = ek & 0
                                Next i

                                portfoy12 = ek & pk
                            End Function

Gördüğünüz gibi WorksheetFunction bizi gereksiz bir For döngüsü yazmaktan kurtarmış olur (Tabii bu örnekte String alternatifimizin olduğunu bir kez daha altını çizelim, ama her zaman böyle alternatifler olmaz).

Örnek 2

İkinci örneğimizde VLOOKUP kullanımı var. Bu örnekte de 'aranan' isimli bir değeri 'table' isimli bir range içinde arıyoruz. Bunu normal VBA ile yapmak için Dictionary tanımlamak gerekirdi, ki bu da kodlarımızı oldukça uzatırdı.

                        
                            .....
                            sonuc = WorksheetFunction.VLookup(aranan, table, 1, False)

Örnek 3

Aşağıda ise çifte VLOOKUP yapmayı sağlayan bir UDF var, burada COUNTIF fonksiyonunun kullanımı görüyorsunuz:

                        
                            Function Çiftevlookup(alan As Range, sütun As Long, İlk_kriter, İkinci_kriter)
                                Dim rCheck As Range, bFound As Boolean, lLoop As Long
                                On Error Resume Next

                                Set rCheck = alan.Columns(1).Cells(1, 1)
                                For lLoop = 1 To WorksheetFunction.CountIf(alan.Columns(1), İlk_kriter)
                                    Set rCheck = alan.Columns(1).Find(İlk_kriter, rCheck, xlValues, xlWhole, xlNext, xlRows, False)
                                    If UCase(rCheck(1, 2)) = UCase(İkinci_kriter) Then
                                        bFound = True
                                        Exit For
                                    End If
                                Next lLoop

                                If bFound = True Then
                                    Çiftevlookup = rCheck(1, sütun)
                                Else
                                    Çiftevlookup = "#N/A"
                                End If
                            End Function

Örnek 4

Yine bir önceki UDF bölümünde gördüğümüz bir fonksiyonda LARGE ve SMALL fonksiyonlarının kullanımına şahit oluyoruz:

                        
                            Function uçhariçort(alan As Range, Uç As Variant)
                                Dim aratoplam As Double
                                Dim enbüyükler As Double
                                Dim enküçükler As Double

                                For i = 1 To Uç
                                    enbüyükler = enbüyükler + WorksheetFunction.Large(alan, i)
                                Next i

                                For i = 1 To Uç
                                    enküçkler = enküçkler + WorksheetFunction.Small(alan, i)
                                Next i

                                aratoplam = WorksheetFunction.Sum(alan) - enbüyükler - enküçkler
                                uçhariçort = aratoplam / (alan.Count - Uç * 2)
                            End Function