Excel’de Kullanıcı Tanımlı Fonksiyon Oluşturmak

Makale Özet: Excel’de yer alan fonksiyon kullanıcının bütün ihtiyaçlarını karşılayamayabilir. Bu makalede Excel’de var olmayan bir fonksiyonun VBA ortamında oluşturulmasından ve bütün çalışma kitaplarında kullanılabilmesi için gerçekleştirilmesi gereken işlemler anlatılmıştır. Kullanıcı Tanımlı Fonksiyon (User Defined Function) oluşturabilmek için orta seviyede VBA(Visual Basic Application) bilgisi gereklidir.

Makale İçeriği:

1. Fonksiyon nedir?

Fonksiyon, program içerisinde parametrelerle çağırılan, parametrelerle bir takım işlemler yapan ve sonunda da bir değer döndüren komutlar dizisidir. Microsoft Excel’de fonksiyonlar, ‘Bağımsız değişken olarak adlandırılan özel değerleri, belirli bir sırada ve yapıda alarak bir takım işlemler gerçekleştiren ve sonucunda da bir değer döndüren önceden tanımlanmış formüller’ olarak adlandırılır.

Fonksiyonlar önceden tanımlanmış oldukları için, özellikleri değiştirilemez. Farklı Excel sayfalarına ya da formlarına fonksiyonlar içerisinden atama yapılamaz. Sadece çağırdıkları yere değer döndürürler. Excel’de Formül sekmesindeki Fonksiyon Kütüphanesi’ alanında yer alan bütün formüller, yerleşik fonksiyonlardır.

Bilindiği gibi Excel’de yerleşik olarak gelen fonksiyonlar ile hemen hemen bütün ihtiyaçlar karşılanabilir. Ancak bazı durumlarda, istenen özel işlemlerde bu fonksiyonlar yeterli olmaz ve ayrıca, kullanıcının ihtiyaçlarını karşılayabilecek yeni fonksiyonlar oluşturulması gerekir. Yeni fonksiyonlar ise, Excel’de VBA(Visual Basic Application) ekranında, kodlarla yapılabilir.

2. UDF gerektiren durumlar

Excel VBA’in en büyük özelliklerinden biri, kullanıcının kendi fonksiyonlarını oluşturabilmesidir. Kullanıcı tanımlı Fonksiyonlar (UDF) karmaşık fonksiyonlarla elde edilen sonuçları, daha basit kodlar kullanarak elde edilmesini sağlar.

Veri tabloları üzerinde çalışılırken, elde etmek istenilen sonuca ulaşabilmek için, birden fazla fonksiyonu iç içe kullanmak ya da farklı sütunlarda adım adım işlemleri gerçekleştirmek gerekebilir. Bu hesaplamalar belirli aralıklarla düzenli bir şekilde yapılıyorsa; zaman hatta dikkat kaybına sebep olabilir. Örneğin bir KDV ya da Iskonto hesaplama bir kaç adımda gerçekleştirilebilir.

Bazen tablolar üzerinde analiz yapılırken Excel’deki fonksiyonların da yapamayacağı işlemleri gerçekleştirmek zorunda kalınabilir. Örneğin, seçili alandaki bir sayının yazıya çevrilmesi, hücrenin rengine göre toplama ya da saydırma gibi işlemler Excel’deki fonksiyonlarla çözüme ulaşmaz.

Bu ve bunun gibi durumlarda kullanıcının kendi fonksiyonlarını oluşturması gerekebilir.

3. Fonksiyon oluşturmak

Bu bölümde oluşturulacak iki fonksiyonun adı ‘RengeGoreTopla’ ve ‘RengeGoreSay’. Seçili bir alanda belirtilen renkteki hücrelerin değerlerinin toplamını ve sayısını verecektir. İşlem için öncelikle RengeGore adını verilen boş bir Excel sayfasında Alt+F11 tuşu ile VBA ekranı açılır. Ekranın sol tarafındaki Project ekranından Excel çalışma kitabına sağ click yapılarak yeni bir modül eklenir.

Şekil1: Yeni bir VBA Modülü eklemek

Buraya yazılacak iki fonksiyonun kodu aşağıdaki gibidir.

Function RengeGoreTopla(aralik As Range, hucre As Range)

Application.Volatile

sayi = aralik.Count

Dim toplam As Double

toplam = 0

For i = 1 To sayi

If aralik(i).Interior.ColorIndex = hucre.Interior.ColorIndex Then

toplam = toplam + aralik(i).Value

End If

Next i

RengeGoreTopla = toplam

End Function

Function RengeGoreSay(aralik As Range, hucre As Range)

Application.Volatile

sayi = aralik.Count

Dim sayisi As Integer

sayisi = 0

For i = 1 To sayi

If aralik(i).Interior.ColorIndex = hucre.Interior.ColorIndex Then

sayisi = sayisi + 1

End If

Next i

RengeGoreSay = sayisi

End Function

4. Add-in olarak kaydetme 

Fonksiyonların yazılımı gerçekleştirildikten sonra, VBA penceresi kapatılır. Tüm çalışma kitaplarında bu fonksiyonlardan yararlanabilmek için, dosyanın ‘.xlam’ olarak kaydedilmesi gerekir. Bunun için, File-> Save As dedikten sonra dosyanın kaydedilme türü olarak Excel Add-In(*.xlam) seçilir.

Şekil2: Dosyayı Add-In olarak kaydetmek

Daha sonra pencere otomatik olarak “C:\…..\AppData\Roaming\Microsoft\AddIns klasörüne yönlenir ve dosyanın buraya kaydedilmesini ister. Tamam dedikten sonra artık bu iki fonksiyon bir eklenti dosyası olarak ilgili yere kaydedilmiş olur. Çalışma kitabı kapatılır.

5. Add-In’leri Aktifleştirmek

RengeGoreTopla ve RengeGoreSay gibi kullanıcı tanımlı fonksiyonları oluşturup Eklenti (Add-In) olarak kaydettikten sonra çağırabilmek için, Excel çalışma kitabı tekrar açılır ve File-> Options-> Add-Ins seçeklerinden sonra pencerenin alt tarafındaki Go butonuna basılır.

Şekil3 Add-In’leri aktifleştirme butonu

Karşılaşılan pencere, Excel’e yüklü olan ama kullanabilmek için buradan aktifleştirilmesi gereken eklentilerdir. Oluşturduğumuz Add-In’i buradan işaretledikten sonra Tamam butonuna basılır.

Şekil4: Eklentilerin seçilmesi

6. Fonksiyonları Kullanmak

Add-In olarak kaydedilen fonksiyonlar artık her çalışma kitabında kullanılabilir durumdadır. Tek yapılması gereken “=” ile fonksiyonu hücre içerisine yazmak. Yazmaya başlayınca fonksiyonların geldiği görülecektir.

Şekil5: Fonksiyonun çağırılması

Her iki fonksiyon da çalışabilmek için iki parametre ister. Bunlarda birincisi toplamını almak istediğimiz alan, ikincisi de seçili alandaki toplamı alınmak istenen hücre rengi. Hücre rengi, toplamı alınacak alandan seçilebilir. Hiçbir sıkıntı yaratmaz. Ancak sırası, VBA kodunda yazılan sırada olmalıdır. İlk olarak alan, ikinci olarak da hücre seçilir. Dikkat edilmesi gereken bir başka nokta da iki parametre arasına “;” koymayı unutmamaktır. UDF olduğu için fonksiyon açıklaması şimdilik yok.

Şekil6: Fonksiyonun kullanılması

Enter tuşuna basıldığında fonksiyonun döndürdüğü sonuç gözlemlenebilir. Aynı şekilde oluşturulan diğer fonksiyonu da çalıştırıldığında, belirlenen alanda, belirtilen renge sahip hücrelerin sayısını verir.

Şekil7: Fonksiyoların sonuçları

7. Fonksiyonun güncellenmesi

Eğer, seçili olan alanda herhangi bir hücrenin renginde değişiklik yapılırsa toplama ve saydırma işlemlerinin güncellenebilmesi için hücre içerisine girip fonksiyonu yeniden çalıştırmak gerekirdi. Fakat hücre değerinde değişiklik yapıldığında güncelleme otomatik sağlanmakta. Renk değişikliğinin fonksiyon tarafından yakalanabilmesi için yazılan “Application.Volatile” komutu ile değişiklikler Excel tarafından izlenir bir hale getirildi. Herhangi bir alanda renk değişikliği yapıldığında “F9” tuşu ile fonksiyonlar kendini güncelleyebilir bir duruma gelmiştir.

Bu konuyla ilgili sorularınızı  alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.

Referanslar

https://www.mshowto.org