• Excel

  Giriş

  Home Menüsü

  Insert Menüsü

  Formulas Menüsü(Fonksiyonlar)

  Formulas Menüsü(Diğer)

  Data Menüsü

  Review Menüsü

  Developer Menüsü

  Yeni Eklenen Araçlar

• VBA/Makro

  Giriş

  Temeller

  Dört Temel Nesne

  Koşul ifadeleri ve Döngüler

  Diziler ve Dizimsi Yapılar

  Debugging ve Hata Yönetimi

  Fonksiyonlar

  Dosya işlemleri

  Olaylar

  Formlar

  Diğer Uygulamalarla iletişim

  İleri seviye konular

• VSTO

  Giriş

  Core VSTO

  .Net işlemleri

  Third Party Kütüphanler

  Projeyi Yayınlama

  Örnek Projeler

  Diğer Uygulamalarla Çalışmak

WorksheetFunction

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 browserdan 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ılki 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ğimde 12 haneden küçük portföy kodlarını 12 haneye tamalayan bir UDF var. Bu örnekte en başa gerekn miktarda 0 konulmaktadır. Bunun Excelde yapmak için kendisine çok benzeyen şu formülü girmem gerekirdi.

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

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(Tabi bu örnekte String alternatifimizin olduğunu birkez daha altını çizelim, ama her zaman böyle alternatifler olmaz)

Örnek 2

İkinci örneğimzde 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)

Gördüğünüz gibi Excel'in içinde herhangi bir hücreye Vlookup formülü yazıdrmış olmuyoruz, tamamen VBA tarafındayız ve sanki Excel'de Vlookup yapmış gibiyiz ve sonucunu da bir değişkene atıyoruz. Excel'de bir hücreye formül yazdırmak için Range nesnesinin Formula property'si kullanılır. Bu ayrım, özellikle ilk başlarda kafa karıştırıcı olabilir. Birkaç kez kullandıkça farkı anlayacaksınız.

Ö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

End With

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	

Evaluate

WorksheetFunction'a benzer bir de Evaluate metodu vardır. Bazı durumlarda WorksheetFunction'ın özdeşi olup buna göre yazımı daha kolay olduğu için tercih edilebilir. Mesela aşağıdaki iki ifade özdeştir.

WorksheetFunction.Sum(Range("A1:A10"))
Evaluate("SUM(A1:A10)")

Farkettiyseniz bunda Range yok, aslında tırnak içindeki formül tamamen Excel ortamında yazdığımız formül gibi, içerde hiçbir VBA terimi yok.

Ama Evaluate sadece bu kadar iş yapmıyor, onun üstün basan yönleri de var. Bence bunların en önemlisi içeriğindeki formülü dizi formülü olarak üretebilmesidir. Ve bu da Evaluate'i dizi formülü üreten UDF'ler yazmada oldukça kullanışlı kılar. Burada bu metod ile ilgili çok daha detaylı bilgi bulabilirsiniz.

Aşağıdaki UDF ile belirli bir alandaki En Büyük/Küçük X değerin toplamını alırız. X=True ise En büyük, False ise En küçük x değere bakılır.

Function EnXTopla(alan As Range, N As Long, Optional x As Boolean) As Single
Dim strAddress As String


On Error Resume Next
strAddress = alan.Address
    If x = False Then
        EnXTopla = Evaluate("=SUMPRODUCT((" _
            & strAddress & ">=LARGE(" & strAddress & "," & N & "))*(" & strAddress & "))")
    Else
       EnXTopla = Evaluate("=SUMPRODUCT((" _
            & strAddress & "<=SMALL(" & strAddress & "," & N & "))*(" & strAddress & "))")
    End If
End Function