Kullanıcı Tanımlı Fonksiyonlar(UDF)
Bazı anlar olur ki "Ya bunun formülü nasıl yazılıyordu" dersiniz, o formülü daha önce yazmışsınızdır ama o kadar uzun bir formüldür ki tekrar hatırlamak biraz zamana mal olur, hele o parantezler yok mu, "ya bu parantezlerden hangisi fazla" diye düşünür durursunuz. Burada bir alternatif, daha önceden yaptığınız ve bir yerlere(!) kaydettiğiniz çalışmayı bulmak ve formülü kopyalamak, bir diğeri de kendi fonksiyonunuzu yani UDF(User Defined Functions) tanımlamak ve bunu her ihtiyacınız olduğunda çok kolay şekilde kullanmaktır.
UDF'in tek kullanım amacı bu değildir tabiki, bir diğer kulanım amacı ise, Excel'in mevcut fonksiyonlarıyla yapmanın zor olduğu hatta imkansız olduğu şeyleri bunlarla yapabilmektir. Evet, her ne kadar Excel'in çok geniş bir fonksiyon kütüphanesi olsa bile bunlar bazen yetersiz kalabilmektedir. Gerçi her yeni versiyonda bazı eksiklikler tamamlanmaktadır. Şahsen benim yazdığım en az bi düzine fonksiyon artık son durumda(bu sayfayı yazarken son versiyon 2016 idi) boşa çıkmıştır. Bunlara da yeri gedikçe bakacağız.
UDF Konusuna Giriş
Şimdi UDF konusuna girerken olaylara bakışımızı biraz değiştirmekte, kendimizi Microsoft çalışanıymış gibi düşünmekte fayda var. Kendimize şunu soralım: Yerel fonksiyonları Excel mühendisleri nasıl hazırlamış olabilir?
Mesela SUM fonksiyonuna bakalım:
Bu yukarıdaki gibi bir fonksiyonu biz nasıl hazırlardık? Bunu önce sözlü dile getirelim. Seçili alandaki tüm hücrelerin değerini tek tek topla. Algoritmik hali de şöyledir:
- İlk hücreden toplamaya başla
- Sonraki hücreye geç, bir önceki değerle topla
- Bunu son hücreye kadar devam ettir
O zaman son minvalde kodumuz şöyle olacaktır:
Function Topla(alan As Range) As Double
Dim a As Range
Dim gecici As Double
For Each a In alan
gecici = gecici + a.Value
Next a
Topla = gecici
End Function
Ne yaptığımıza bir bakalım:
- Fonksiyon ismini Function kelimesi ile belirttim, tipini de Double. (Tip belirtmezsem Variant olur)
- Fonksiyonuma parametre olacak olan "alan"ı veri tipi Range olacak şekilde belirttim. (Normalde yerel SUM fonksiyonu kendisine bir alan değil de adedi belirsiz olan sayısal değerleri kabul eder, biz basit olsun diye alan belirttik. Yerel SUM'ın yaptığı gibi de yapabilirdik, bunu ParamArray kısmında göreceğiz)
- Sonra geçici bir değişken tanımladım, hücreler içinde dolaşırken ara toplamı hep bu geçici değişkende tuttum. (Geçici değişkenler Function tanımlamalarında sıklıkla kullanılırlar)
- Sonra bir For Each döngüsü ile tüm hücrelerin arasında dolaşıp toplamı hesapladım
- En son da fonksiyon ismi olarak belirttiğim ifadeye (Topla) geçicinin değerini atadım
Fonksiyon tanımlamanın jenerik yapısı aşağıdaki gibidir:
Function fonksiyonadı(parametre1 As veritipi, parametre2 As veritipi, ...) As DönüşTipi
'Gerekliyse değişken tanımlamaları
'Kod bloğu
'varsa geçicideğer
fonksiyonadı = geçicideğer
'geçici değer yoksa hesabı direk fonksiyon adı üzerinde yaparız
'fonksiyonadı = hesaplama kodları
End Function
Mesela aynı mantıkla COUNT fonksiyonunu düşünün. Bu fonksiyon bildiğiniz gibi sayı içeren hücreleri sayar. Hadi biz de aynı görevi gören bir UDF hazırlayalım. VBA, boş hücreleri de 0 gibi düşündüğü için bunları numerik sayar, o yüzden dolu hücrelere sayı içeriyor mu diye bakacağız.
Function NumerikSay(alan As Range)
Dim a As Range
Dim gecici As Double
For Each a In alan
If IsNumeric(a) And Not IsEmpty(a) Then gecici = gecici + 1
Next a
NumerikSay = gecici
End Function
Aynı mantıkla COUNTA fonksiyonunu düşünün. Bu fonksiyon bildiğiniz gibi içi dolu hücreleri sayar. Bunun da UDF versiyonunu hazırlayalım.
Function DolularıSay(alan As Range)
Dim a As Range
Dim gecici As Double
For Each a In alan
If Not IsEmpty(a) Then gecici = gecici + 1
Next a
DolularıSay = gecici
End Function
Performans
Hazırladığınız fonksiyonu binlerce satırdan oluşan listelerde kullanacaksanız mutlaka hem fonksiyonun parametrelerini hem de kodda kullanılacak tüm değişkenleri uygun veri tipinde tanımlayın. Buna rağmen fonksiyonunuz çok komplike ise yeterince hızlı çalışmayabilir. Yerel fonksiyonlarla yapmak çok zor değilse yerel fonksiyonları kullanmanız gerekebilir. Yerel fonksiyonlar sonuçta en temel seviyede çalışırlar. Kendi UDF'lerimiz ise bir seviye üstte çalışır, o yüzden temel seviyeye çevrilmeleri gerekir, en son da makine diline çevrilirler. Bu performans farkını test ettikten sonra görebilir, duruma göre karar verirsiniz.
"Fonksiyonu UDF olarak hazırlamak istiyorum ama performansı da hızlı olsun" diyorsanız XLL ile tanışma vaktiniz gelmiş demektir. Bunun için sizi şöyle alayım.
Yerleşim
Tekil kullanımlık UDF'ler
Yazdığımız fonksiyonları bazı durumlarda sadece ilgili dosya içinde çalışmasını isteriz, çünkü o dosyaya özgü çözüm sunarlar. Bunları başka bir yerde kullanmayacağımız için genel UDF'leri tutacağımız yerde bulundurmamıza gerek yoktur.
İlgili dosyada UDF yazmak için o dosyaya Modül eklemeli, modül sayfasına yazmalıyız. ThisWorkbook ve Sheet modülleri UDF'ler için uygun değildir.
DİKKAT:Tekil kullanımlık UDF'in kaydolduğu dosyayı xlsm veya xlsb uzantısıyla kaydetmek gerekmektedir.
Genel kullanım UDF'lerinin yerleşimi
Eğer kodlarınızı genele yaygın bir şekilde kullanmak istiyorsanız bunun için bir alternatif Personal.xlsb dosyasıdır ama bu genelde kötü bir alternatiftir. Normal Sub prosedürler için Personal.xlsb güzel bir adrestir ancak Function'lar için aynısını söyleyemeyiz. Zira bunlara ne Insert Function menüsündeki User Defined kategorisinden ulaşmak kolaydır, ne de bir hücreye doğrudan ismini yazarak ulaşabiliriz. User Defined kategorisinden ulaşmak biraz karışıktır. Diyelimki fonksiyon adı DolularıSay olsun. Bu fonksiyon listede Personal.xlsb!DolularıSay şeklinde görünür ancak bunun için P harfine gidip önce Personla.xlsb'yi bulmanız gerekmez. UDF'ler, içinde bulundukları dosyadan bağımsız olarak kendi adlarına göre alfabetik sıralıdır. Dolularısay için de D harfiyle başlayan fonksiyonlara gitmeniz gerekir. Aşağıda görüldüğü üzere N harfiyle başlayan NumerikSay fonksiyonu P harfiyle başlayan Personal.xlsb!DolularıSay 'dan sonra geliyor, önce değil.
Bunu seçtiğimizde yukardaki ilk örnekte gördüğümüz gibi Excel içindeki görünümü =Personal.xlsb!DolularıSay(B2:D7) şeklinde olup biraz garip bir görünüme sahiptir. Normal bir dosya içindeki tekil kullanımlık UDF'lerde ise bu sorun yoktur. Peki soru şu:UDF'imizi hem tekil kullanımlık UDF'teki gibi önünde dosya ismi olmadan kullanmak hem de genele yaygın kullanmak istiyorsak ne yapmalıyız? Cevap:UDF'lerimizi Add-in içinde kaydetmek.
Add-in içinde yerleşim
Boş bir dosya açın, kaydet düğmesine basın, dosya tipini Excel Add-in olarak değiştirin.
Otomatikman adresin yukardaki gibi değişmesi lazım. Sizdeki adres Excel versiyonunuza göre değişebilir. Bende şöyle: C:\Users\Volkan\AppData\Roaming\Microsoft\AddIns\ Bu add-in'i Developer menüsü>Excel Add-in menüsünden aşağıdaki gibi aktive edebilirsiniz. Bir kere aktive olduktan sonra Excel her açıldığında bu dosya da açılacaktır. Dosyanın kendisi görüntülenemez. Sadece VBE ortamında görünür. Yanlız bunlar Personal.xlsb'den biraz farklıdır. Personal.xlsb'yi istersek unhide yaparak görebiliriz ancak add-in dosyaları asla normal worbooklar gibi görüntülenemezler. Aşağıdaki görselde farkettiyseniz Excelle hazır gelen Anlaysis Toolpak ve Solver gibi add-inler de var. Bunların kullanımıyla ilgili detaylara yandaki anamenüde Excel altındaki Data menüsünden ulaşabilirsiniz.(NOT:Developer menüsündeki COM Add-in butonu bu sitenin VSTO konusuyla ilgili olduğu için ona burada değinmeyeceğiz.)
Bundan sonrasında yapılması Gereken bu dosyaya VBE'de modül ekleyip UDF kodlarımızı oraya yazmaktır.
Gördüğünüz gibi artık UDF'in önünde dosya adı yok ve de herhangi bir dosyada kullanabiliyoruz.
Erişim
Add-in'lerinizi başka kişilerle de paylaşmaya karar verdiyseniz bunların nerede olduğuna ulaşmak için VBE'de Immediate Window'a şunu yazın: ?Application.UserLibraryPath. Add-inlerinizin aslında istediğiniz yere kaydedebilirsiniz, ancak daha sonra aktive(veya pasifize) ederken kolaylık olması adına bu adreste olması daha iyidir. Aktivasyon işlemini yukarda göstermiştik. Bir diğer alternatif de, File> Options>Add-ins menüsündendir.
Son olarak, Add-inler sadece UDF depolamak için kullanılan araçlar değillerdir. Ayrıca menü yaratıp, bu menüye çeşitli düğmeler ve altmenülere ekleyerek Sub prosedürlerinizi yani makrolarınızı çalıştırmak için de bir arayüz sağlarlar. Bu konuya ayrı sayfada değineceğiz.
Fonksiyonlara Açıklama Eklemek
Fonksiyonlara açıklama eklemek özellikle başkalarının kullanımı için faydalı olabilir. Bunun için aşağıdaki gibi bir ayarlama yapılabildiği gibi toplu tanımlama yapma imkanı da vardır, ki bunun için tabii ki yine VBA kodu kullanırız.
Çıkan kutuya direkt açıklama yazılır.
Toplu işlem için kullanacağımız kodda ise fonksiyonun ait olacağı kategoriyi de belirtebiliriz. Mesela NumerikSay için İstatistiki kategorisine koyabiliriz, ki yukarıdaki yöntemde bu yapılamamaktadır.
Mevcut kategoriler aşağıdakiler olup bunların dışında yeni kategoriler de yaratabiliriz:
- '0 Kategori yok, All içinde görünür
- '1 Financial
- '2 Date & Time
- '3 Math & Trig
- '4 Statistical
- '5 Lookup & Reference
- '6 Database
- '7 Text
- '8 Logical
- '9 Information
- ...
- '14 User Defined default
- '15 Engineering (Analysis Toolpak add-in'i kuruluysa kullanılabilir)
Aşağıdaki kod, NumerikSay fonksiyonunu Sayısal Fonksiyonlar isimli kategori içine atar, bu kategori mevcut değilse bunu yaratır:
Sub UDFaçıklaması()
Application.MacroOptions _
Macro:="NumerikSay", _
Description:="Belli bir alandaki sayısal değerlerin toplam adedini verir", _
Category:="Sayısal Fonksiyonlar"
End Sub
Excel 2010 ile birlikte fonksiyonların argümanlarına da açıklama girme imkanı gelmiştir. MacroOptions metoduna ArgumentDescriptions parametresini ekleyerek bu işi hallederiz. (Normalde VBA ölü bir dildir, yani yeni güncellemeler, özellikler eklenmez ancak ender de olsa böyle küçük iyileştirmeler yapılmakta)
Önemli bir nokta var ki, o da bu girdiğiniz açıklamalar sadece sizde geçerli olur. UDF'lerinizi bir add-in haline getirip gönderirseniz ve onlarda da görünmesini isterseniz bu tanım ekleme kodlarını Workbook_Open içine yazmakta fayda var, ki kişinin add-ini yüklenir yüklenmez açıklama kodu da çalışsın. UDF'lerin açıklamaları ve parametrelerin açıklamalarıyla birlikte kodunuz çok uzayacak gibi olursa bunları bir text dosyasından veya Excel dosyasından okutabilirsiniz. (Biraz aşağıda örneği var)
Şimdi tek bir fonksiyon için nasıl yapıyoruz ona bakalım:
Sub UDFaçıklaması()
Dim fonkAd As String
Dim fonkTanım As String
Dim fonkKategori As String
Dim argumanAdedi As Integer
Dim argumanlar() As String
argumanAdedi = 1
ReDim argumanlar(1 To argumanAdedi)
fonkAd = "NumerikSay"
fonkTanım = "Belli bir alandaki sayısal değerlerin toplam adedini verir"
fonkKategori = 4
argumanlar(1) = "Toplanacak sayıların olduğu alanı seçin."
Application.MacroOptions _
Macro:=fonkAd, _
Description:=fonkTanım, _
Category:=fonkKategori, _
ArgumentDescriptions:=argumanlar
End Sub
Herhangi bir hücreye fonksiyonumuzu yazdığımızda aşağıdaki gibi görünür:
Aşağıda da Workbook_Open makrosuna yazılı olan kod var. Bu kod ile, fonksiyon açıklamalarını bir Excel dosyasından okuyoruz.
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open "C:\makrolar\kılavuz.xlsx", UpdateLinks:=0 'herkes dosyayı buraya koymalı
Workbooks("kılavuz.xlsx").Sheets("UDFDesc").Select
Call macrodesc
Workbooks("kılavuz.xlsx").Close savechanges:=False
Application.ScreenUpdating = True
End Sub
'bu da çaprılan macrodesc prosedürü
Sub macrodesc()
Dim fonkAd As String
Dim fonkTanım As String
Dim fonkKategori As String
Dim argumanAdedi As Integer
Dim argumanlar() As String
Dim i As Integer
Range("A2").Select
Do
fonkAd = ActiveCell.Value2
fonkTanım = ActiveCell.Offset(0, 1).Value2
fonkKategori = ActiveCell.Offset(0, 2).Value2
argumanAdedi = Range(ActiveCell, ActiveCell.End(xlToRight)).Cells.Count - 3
ReDim argumanlar(1 To argumanAdedi)
For i = 1 To argumanAdedi
argumanlar(i) = ActiveCell.Offset(0, i + 2).Value2
Next i
Application.MacroOptions _
Macro:=fonkAd, _
Description:=fonkTanım, _
Category:=fonkKategori, _
ArgumentDescriptions:=argumanlar
Loop Until ActiveCell.Value = ""
End Sub
ParamArray ile Belirsiz Sayıda Parametre Temini
Yukarıda Excel'in yerel SUM fonksiyonunu, kaynağı bir alan olacak şekilde tasarlamış ve demiştik ki, aynı yerel SUM gibi sayısı belirsiz olan elemanları içerecek şekilde de yapabiliriz. İşte bunun yolu ParamArray ifadesini kullanmaktır. Önce örneği yapalım sonra açıklayalım.
Function ToplaParamarray(ParamArray sayılar()) As Double
Dim a As Variant
Dim gecici As Double
For Each a In sayılar
gecici = gecici + a
Next a
ToplaParamarray = gecici
End Function
ParamArray ifadesinden sonra bu sayısı belirsiz olan elemanları içerecek bir dizi adını gireriz. Bu Variant tipli bir değişken olmak zorundadır, yani başka bir veri tipiyle tanımlayamayız. Zaten mantıklısı da budur. Mesela Toplam örneğinde hem 0,25 gibi double tipindeki sayıları hem de 5 gibi integerları input olarak alabiliriz, böyle bir durumda Variant'tan başka çare yoktur.
Diğer kısıtlamalar şöyle:
- Fonksiyonun parametre listesinde başka parametreler de varsa ParamArray son parametre olmalıdır. Örneğin:
Function ToplamınXinciÜssü(üs As Integer, ParamArray sayılar()) - Bir fonksiyonda sadece 1 tane ParamArray tanımlanabilir.
- Bir fonksiyonda Optional ve ParamArray parametrelerden yalnız biri kullanılabilir.
- İlgili dizinin tabanı 0'dır. İsterseniz Option Base ile genel dizi tabanını 1 yapmış olun fark etmez. For Each yerine klasik for kullanılacaksa
LBound(dizi) to UBound(dizi)şeklinde kullanılabilir.
NOT: Bu yukarıdaki örnekte bir alan seçimi yapılamayacağına dikkat edin; tek tek sayı temin etmek zorundasınız. Excel'in SUM fonksiyonu ise hem alan kabul edebiliyor hem de tek tek sayılar kabul edebiliyor. SUM'ın bunu nasıl yaptığını düşünün ve her iki versiyonu da kapsayacak bir fonksiyonu yazmayı deneyin.
Optional ile Opsiyonel Parametre Temini
Bazen girdiğimiz parametrelerin sık kullanılan değerini biz baştan gireriz ve son kullanıcıya bunu değiştirme imkanı veririz. Bu, yerel Excel fonksiyonlarında da var olan default (varsayılan) değerlerle aynı şeydir. Mesela VLOOKUP fonksiyonunun son parametresi opsiyonel olup default değeri TRUE (1)'dir, yani girilmezse TRUE (1) algılanır.
İşte biz de bu opsiyonel parametreyi Optional ifadesi ile sağlarız, istersek varsayılan değer de girebiliriz, girmezsek de bunun girilip girilmediğini IsMissing fonksiyonu ile test ederiz. Ancak IsMissing sorgulaması yapabilmek için ilgili parametrenin veri tipi Variant olarak girilmelidir. (ParamArray'da veri tipi Variant olmak zorundayken Opsiyonellerde ise tavsiyedir, olur da IsMissing ile sorgularız diye, ama zorunlu değildir). Bu değer Variant değilse ve kullanıcı tarafından girilmezse bunlara default değerleri atanır; String için "", sayısal tipler için 0, boolean için false.
Dikkat edilmesi gereken diğer hususlar şöyledir:
- Bunlar da ParamArray gibi son parametre olarak girilmelidir.
- ParamArray'de belirttiğimiz gibi aynı anda hem ParamArray hem Optional ifadesi kullanılamaz.
- Birden fazla Optional ifadesi kullanılabilir.
Opsiyonel Örnek 1
Aşağıda varsayılan değerin girilmiş olduğu bir örnek bulunuyor. Bu örnekte bir hücredeki metni kelimelere ayırıyoruz. Normal bir cümlede ayraç boşluk olacağı için ayracı girmeye gerek yok, o yüzden default olarak " " atadım. Ama kullanıcı isterse kelimeleri farklı bir ayraçla da ayırabilir. Mesela - ile ayrılmış kelimeler varsa ayraç olarak - kullanılabilir. Fonksiyonda Split fonksiyonu kullanarak kelimeleri parçalıyor ve bir diziye atıyorum. Kaçıncı parametresi ile de istediğim kelimeyi elde ediyorum.
Function kelimesec(hucre As Range, kaçıncı As Byte, Optional ayrac As String = " ")
Dim kelimeler As Variant
kelimeler = Split(hucre.Value2, ayrac)
kelimesec = kelimeler(kaçıncı - 1)
End Function
Örnek kullanım aşağıdaki gibidir. B7'deki formül şöyle:
=kelimesec(A7,2) //son parametreyi girmedim
Opsiyonel Örnek 2
Şimdi üç tane opsiyonel değişkeni olan bir fonksiyonumuz var. Bunlardan birinin varsayılan değeri girilmiş, birinin girilmemiş, biri de Variant olarak tanımlanmış.
Bu örnek bankacılık dünyasından bir örnek olacak. Mevduat/Kredi gibi hacimsel bir büyüklük ile bu hacimden ne kadar kar elde ettiğimizi gösteren spread bilgisini zorunlu olarak giriyoruz. Bu fonksiyonu iki şekilde kullanabiliyoruz. Eğer belirli bir alan seçilmezse şube bazında NFG (Net Faiz Geliri) hesaplayacağız, ancak ilgili alanı seçersek o alandaki MT (Müşteri Temsilcisi) adedi başına NFG hesaplanmış olacak. İşte bu alan bilgisini Variant olarak girdim, ki IsMissing ile alanın girilip girilmediğini sorgulayabileyim. İkinci opsiyonel seçenek Kur bilgisi olup, ilgili hacim türünün TL mi yoksa döviz mi olduğuna göre değer alacak. Varsayılan olarak döviz tipinin TL olduğunu düşünerek değeri 1 girdim, ancak kullanıcı isterse farklı bir kur değeri girebilir. Son değişken ise Para birimi olup, varsayılan değer girilmemiştir. Kullanıcı isterse TL, USD gibi değerler girebilir, eğer girmezse Stringlerin varsayılan değeri olan "" atanacak olup herhangi bir para birimi yazmayacaktır.
Son olarak, fonksiyon için dönüş tipi belirtmedim, yani Variant olacak. Zira Birim parametresi girilirse sonuç String, girilmezse double olacak, yani ikisini de kapsayan bir tip olmalı, ki bu da Variant oluyor.
Function NFGHesapla(hacim As Double, spread As Double, Optional alan As Variant, Optional Kur As Double = 1, Optional Birim As String)
Dim adet As Integer
If IsMissing(alan) = True Then
NFGHesapla = (hacim * spread * Kur / 1200) & Birim
Else
NFGHesapla = (hacim * spread * Kur / 1200) / WorksheetFunction.CountA(alan) & Birim
End If
End Function
Kullanım şekli aşağıdaki gibidir. G kolonunda şube başına ve para birimsiz versiyonunu görürken, H kolonunda MT başına ve para birimli versiyonunu görüyoruz.
NOT: Bu örnekle, aslında modern programlama dillerinde olan ancak VBA'de olmayan "method overloading" kavramını (tam olarak olmasa da) bir nevi taklit etmiş olduk. Yani bir metodun ismi aynı olup farklı parametre veya dönüş tipi alıyorsa bu işleme method overloading denir. Biz de buna benzer bir şey yapmış olduk. Hem alan tipini seçip seçmemeye göre hem de sonuna para birimi koyup koymamaya göre 4 farklı kullanım şekli sunduk.
Volatile
Yazdığımız fonksiyon, eğer sayfada bir güncelleme olduğunda bundan etkileniyorsa anında güncellenmez. Volatile ile bu güncellemeyi anlık olarak yapmış oluruz. Ancak üstadlar der ki, UDF'inizi öyle bir hazırlayın ki bu fonksiyonu kullanma ihtiyacınız hiç olmasın.
Bu ve şu sayfalarda bu konuyla ilgili çeşitli tartışmalar da yapılmış, İngilizceniz varsa bakabilirsiniz.
Özet tavsiyem: Araştırmalarınızda bu ifadeyi görürseniz ne olduğunu bilin ama bunu kullanmayın. UDF'inizde bunun kullanımına gerek olmayacak şekilde tüm parametreleri dahil edin. (Örnek vererek kafanızı da karıştırmak istemiyorum)
Default Değerler
Fonksiyonların da tıpkı değişkenler gibi default değerleri bulunur. Örneğin, Boolean tipli bir fonksiyon için bir koşul bloğu içinde sadece True ataması yapıyorsanız ve bu koşul sağlanmıyorsa, siz açıkça Else bloğu içinde False ataması yapmasanız bile fonksiyon False döndürür. (Bu açıklama hem Excel UDF'leri hem VBA UDF'leri için geçerlidir)
Aşağıdaki örneği inceleyelim:
Function aysonumu(tarih As Date) As Boolean
If Month(tarih) <> Month(tarih + 1) Then
aysonumu = True
Else
aysonumu = False
End If
End Function
Bu fonksiyonu daha kısa bir şekilde aşağıdaki gibi yazabiliriz. Zira fonksiyona ilk girildiği anda, fonksiyon Boolean tipli bir fonksiyon olduğu için giriş anında default değeriyle yani False olarak hayatına başlar. Sonrasında kendisine bir atama olmazsa da bu değerini korur, yani False döndürür.
Function aysonumu(tarih As Date) As Boolean
If Month(tarih) <> Month(tarih + 1) Then aysonumu = True
End Function
NOT: Bu fonksiyon hem VBA hem Excel UDF'i olarak kullanılabilen bir fonksiyondur.
Süpercombine ile Hücreleri Birleştirin
Bu fonksiyon, ardışık bir hücre grubunu belirli bir işaret/ayraç ile birleştirir. 2016 ile gelen TEXTJOIN ve CONCAT fonksiyonlarıyla gereksiz hale gelmiştir ancak eski Excel versiyonlarını kullananlar için hala geçerlidir.
Function Süpercombine(Hucre_grubu As Range, isaret As String)
x = ""
For Each k In Hucre_grubu
If Not IsEmpty(k) Then
x = x & k.Value & isaret
End If
Next k
Süpercombine = Mid(x, 1, Len(x) - 1)
End Function
Örnek kullanım aşağıdaki gibidir. Müşteri numaraları ";" işareti ile birleştirilmiştir.
Süperlookup ile Kayan Lookup İşlemi Yapın
Sheet2'de şöyle bir listemiz var:
Sheet1'de bulunan aşağıdaki listeye (sadece A kolonu olduğunu düşünün) lookup çekmek isteseydik şu formülü yazardık (veya yardımcı bir index satırı kullanırdık, ama şık olanı bu aşağıdakidir):
=VLOOKUP($A2;Sheet2!$A:$F;MATCH(B$1;Sheet2!$A$1:$F$1;0);0)
İşte bizim süperlookup fonksiyonumuz bizi bu uzun formülden kurtarmış oluyor.
Function süperlookup(alan As Range, sütun As Range, aranan As Range)
On Error GoTo hata
süperlookup = alan.Columns(1).Find(aranan, lookat:=xlWhole).Offset(0, sütun.Column - alan.Columns(1).Column).Value
Exit Function
hata:
süperlookup = "Bulunamadı"
End Function
Çok Eşleşmeli Lookup
Bu örneğin kodu aşağıda olup açıklamasını şu sayfada bulabilirsiniz.
Function çok_eşleşmeli_vlookup(aranan As Range, alan As Range, kaçıncıkolon As Integer, Optional distinctmi As Boolean = True)
Dim a As Range
Dim dict As New Scripting.Dictionary 'Reference olarak eklenmiş olmalı, ekli değilse Late binding olarak yaratılabilir
If alan.Columns(1).Rows.Count = 1048576 Then
Set alan = Range(alan(1, 1), alan(1, 1).End(xlDown))
End If
For Each a In alan.Resize(, 1)
If Not dict.Exists(a.Value) Then
dict.Add a.Value, a.Offset(0, kaçıncıkolon - 1).Value
Else
geçici = dict(a.Value)
dict.Remove (a.Value)
x = a.Offset(0, kaçıncıkolon - 1).Value
If distinctmi = True Then
dict.Add a.Value, geçici & IIf(InStr(1, geçici, x, vbTextCompare) > 0, "", ";" & x)
Else
dict.Add a.Value, geçici & ";" & a.Offset(0, kaçıncıkolon - 1).Value
End If
End If
Next a
Gün Numarasından (1-7) Gün Adını Veren Fonksiyon
Normalde bunu aşağıdaki formülle yazabiliriz ama TEXT fonksiyonu bazen karışık olabilmekte ve bazı kişilerin yıldızı bu formülle barışık olmayabiliyor.
=TEXT(A1+1;"gggg") //A1'de 2 yazıyorsa salı döndürür. 1 ekleme sebebi günlerin Amerikan formatına göre Pazardan başlamasıdır
Gün UDF'i çok daha basit gibi duruyor.
Function gün(hucre As Range)
Select Case hucre.Value
Case 1
gün = "Pazartesi"
Case 2
gün = "Salı"
Case 3
gün = "Çarşamba"
Case 4
gün = "Perşembe"
Case 5
gün = "Cuma"
Case 6
gün = "Cumartesi"
Case 7
gün = "Pazar"
Case Else
gün = "*****Hata*****, Ben sadece 1 ve 7 arasındaki günler için çalışırım"
End Select
End Function
Hücredeki formül:
=gün(A1)
Uçhariçortalma ile Bir Veri Kümesindeki Uç Değerleri Elimine Edin
Diyelim ki hedefleme veya tahminleme yapıyorsunuz. Hedef verdiğiniz birimin (bölge/şube/bayi/mağaza/v.s) son 1 yıllık satışlarına bakacak ve ortalama alacaksınız, ama bir seferlik aşırı düşük/büyük rakamların da ortalamayı etkilemesini istemiyorsunuz. O yüzden en küçük ve en büyük rakamları çıkartıp kalan 10 ayın ortalamasını almak istiyorsunuz. Bunun için normalde şu Excel fonksiyonunu yazardınız:
=(SUM(B2:B13)-SMALL(B2:B13;1)-LARGE(B2:B13;1))/(COUNT(B2:B13)-2)
Ama biz şu UDF ile işimizi kısaca hallederiz:
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
UDF'imizi =uçhariçort(B2:B13;1) şeklinde kullanınca sonuç aşağıdaki gibidir.
Sona girdiğimiz 1 parametresi en küçük/büyük 1 değeri hariç tutmamızı sağlar. Eğer 2'şer küçük/büyük hariç tutmak istersek buraya 2 gireriz, bu sefer kalan 8 ayın ortalaması alınmış olur.
Süpertrim ile TRIM Yapılamayan Karakterleri de Silin
Bu fonksiyon, Outlook gibi farklı bir ortamdan kopyalanarak alınan rakamların önündeki ve sonundaki boşlukları yok etmek için kullanılır. Genelde herkes Excel'in TRIM fonksiyonunu bilir ve bununla bu boşlukları kaldırabileceğini düşünür ancak aslında bunlar her zaman bildiğimiz boşluk olmayabiliyor, o yüzden TRIM yetersiz kalıyor. Metinsel fonksiyonlarda bu durumu açıklamıştık. Detayı oradan okuyabilirsiniz. İşte bu linkte verilen formülü yazmak yerine aşağıdaki UDF oldukça iş görecektir:
Function süpertrim(hucre As Range)
süpertrim = Val(WorksheetFunction.Trim(WorksheetFunction.Substitute(hucre, Chr(160), "")))
End Function
Uçhariçortalma ile Bir Veri Kümesindeki Uç Değerleri Elimine Edin
Bu fonksiyon ile incelediğiniz küme içinde bir dengesizlik var mı, rakamlar birbirinden çok mu farklı yoksa makul sınırlarda mı sapma var, bunu görmüş olursunuz. Yapılan işlem aslında kümenin standart sapmasının ortalamaya bölümüdür. Bu oran ne kadar küçükse o kadar dengelidir, yani rakamlar birbirine yakındır, ne kadar büyükse sapma miktarı o kadar yüksektir. Tek elemanlı bir kümede hata alınır, siz eleman sayısının tek olup olmamasına bakarak fonksiyonu iyileştirin.
Function sapmaoranı(alan As Range)
sapmaoranı = WorksheetFunction.StDev_S(alan) / WorksheetFunction.Average(alan)
End Function
Aşağıdaki örnekte şubelerdeki müşteri temsilcilerine bağlanmış müşteri adetleri görülmekte. 2 nolu şubede portföyler arasında dengesizlik söz konusu ve müşterilerin yeniden dağıtılmasında fayda var diyebiliriz.
