Genel Bakış
Kullanıcı Tanımlı Fonksiyon (User Defined Function, UDF), Excel'de yerleşik işlevlerin karşılayamadığı hesapları yapmak için VBA ile yazdığınız özel işlevdir. Bir standart modüle Function ifadesiyle yazılır ve hücreye yerleşik işlevler gibi yazılarak çağrılır. Böylece tekrar eden karmaşık formülleri tek bir ad altında toplayabilir, çalışma kitabınızı daha okunur hale getirebilirsiniz.
İşlev hücrede şöyle çağrılır:
Ayrıntılar:
İşlevin kodu şöyledir: Function KdvliFiyat(fiyat As Double, oran As Double) As Double ve gövdesinde fiyat * (1 + oran) sonucu işlev adına atanır.
D sütunundaki her hücre aynı işlevi kendi satırındaki değerlerle çağırır. Ancak bu çalışma kitabında işlev hücrelerden tanınmadığı için Klavye satırında #AD?, Kitap satırında #AD? görünür; diğer satırlarda da aynı hata alınır. İşlevin standart bir modülde bulunduğunu ve makroların etkin olduğunu kontrol edin. İşlev çalıştığında oran değiştikçe sonuçlar otomatik güncellenir.
İşlev kodu, iki sözcüklü adlarda iki, üç sözcüklü adlarda üç harf döndürecek biçimde yazılmıştır. Ancak bu çalışma kitabında işlev hücrelerden tanınmadığı için ilk satırda #AD?, üç sözcüklü ikinci satırda #AD? görünür. İşlevin standart bir modülde bulunduğunu ve makroların etkin olduğunu kontrol edin. İşlev çalıştığında yerleşik işlevlerle uzun ve iç içe bir formül gerektirecek bu işlem için tek bir ad yeterli olur.
İşlev kodu, hedef değiştiğinde prim oranını da değiştirecek biçimde yazılmıştır. Ancak bu çalışma kitabında işlev hücrelerden tanınmadığı için Elif için varsayılan hedefli formül #AD?, 60.000 hedefli formül #AD? gösterir. Hakan'ın satırında da ikinci sütun #AD? değerini alır. İşlevin standart bir modülde bulunduğunu ve makroların etkin olduğunu kontrol edin. İşlev çalıştığında hedef bağımsız değişkeni yazılmazsa varsayılan değer kullanılır; böylece aynı işlev iki farklı biçimde çağrılabilir.
Nerede bulunur ve nasıl oluşturulur
Kullanıcı tanımlı fonksiyonlar bir standart modülün içine yazılır. Adımlar şöyledir:- Visual Basic Düzenleyicisini açın: Alt+F11 tuşlarına basın ya da Geliştirici > Kod > Visual Basic (Developer > Code > Visual Basic) komutunu kullanın. Geliştirici sekmesi görünmüyorsa Dosya > Seçenekler > Şeridi Özelleştir (File > Options > Customize Ribbon) bölümünden işaretleyin.
- Modül ekleyin: Ekle > Modül (Insert > Module) komutuyla boş bir standart modül açın. İşlev sayfa ya da ThisWorkbook kod penceresine yazılırsa hücreden doğrudan çağrılamaz.
- İşlevi yazın: Function ile başlayıp End Function ile biten kodu modüle girin.
- Hücrede kullanın: Hücreye işlevin adını ve bağımsız değişkenlerini bir formül olarak yazın. Ayrıca Formüller > İşlev Ekle (Formulas > Insert Function) penceresinde kategori olarak Kullanıcı Tanımlı (User Defined) seçilirse işlevleriniz listelenir.
- Dosyayı doğru biçimde kaydedin: Kod içeren dosya Excel Makro İçeren Çalışma Kitabı (*.xlsm) olarak kaydedilmelidir; düz .xlsx biçiminde kod saklanmaz.
Söz dizimi ve bağımsız değişkenler
Temel yapı aşağıdaki gibidir. İşlevin sonucu, işlev adına atanarak döndürülür:
VBA
3 satır
Function KdvliFiyat(fiyat As Double, oran As Double) As Double
KdvliFiyat = fiyat * (1 + oran)
End Function=KdvliFiyat(B2;C2)=KdvliFiyat(B2,C2)- As Double, As String: Bağımsız değişkenlerin ve dönüş değerinin veri türünü belirtir. Tür yazılmazsa Variant kullanılır.
- Optional: İsteğe bağlı bağımsız değişken tanımlar; varsayılan değer verilebilir.
- ParamArray: Değişken sayıda bağımsız değişken almayı sağlar.
- As Range: Bağımsız değişken olarak bir hücre aralığı alınmasını sağlar; aralıktaki hücreler For Each döngüsüyle gezilebilir.
- Private Function: İşlev yalnızca aynı modülde kullanılır ve hücrelerde listelenmez. Public (varsayılan) olanlar hücrelerden çağrılabilir.
Function ile Sub arasındaki fark ve sınırlamalar
Sub prosedürü bir işlem yapar ama değer döndürmez; Function ise bir değer döndürür ve hücre formülünde kullanılabilir. Hücreden çağrılan bir kullanıcı tanımlı fonksiyonun yetkileri sınırlıdır:- Başka hücreyi değiştiremez: İşlev yalnızca çağrıldığı hücreye değer döndürür; başka hücrelere değer yazamaz.
- Biçim ve seçim yapamaz: Hücre biçimini değiştirme, Select ile seçim yapma, sayfa ekleme gibi işlemler hücreden çağrılan işlevde çalışmaz; sonuç genellikle #DEĞER! (#VALUE!) hatası olur.
- Yeniden hesaplama: İşlev, yalnızca bağımsız değişken olarak verilen hücreler değişince hesaplanır. Her hesaplamada çalışmasını istiyorsanız işlevin içine Application.Volatile satırını ekleyin; ancak bu, büyük dosyalarda yavaşlığa yol açar.
- Hata değeri döndürme: Hata üretmek için CVErr(xlErrNA) gibi ifadeler kullanılabilir.
Kapsam ve paylaşım
İşlev yalnızca yazıldığı çalışma kitabında kullanılabilir. Birden çok dosyada kullanmak için iki yol vardır:- Kişisel Makro Çalışma Kitabı: İşlevi PERSONAL.XLSB dosyasına yazarsanız bilgisayarınızdaki tüm dosyalarda kullanabilirsiniz; ancak başka bir bilgisayarda çalışmaz.
- Excel Eklentisi (.xlam): İşlevleri içeren dosyayı Excel Eklentisi (Excel Add-In) olarak kaydedip Geliştirici > Eklentiler (Developer > Add-ins) bölümünden yükleyerek paylaşabilirsiniz.
- Makro güvenliği: Makrolar devre dışıysa dosyayı açtığınızda işlevler hesaplanmaz ve formüller #AD? (#NAME?) hatası verebilir; İçeriği Etkinleştir (Enable Content) düğmesini kullanın.
1. KDV'li fiyat hesaplayan işlev
Fiyat ve KDV oranından KDV'li fiyatı bulan KdvliFiyat adlı bir işlev yazılır. Excel'de Alt+F11 ile modül açıp işlevi yazın, ardından D2 hücresine aşağıdaki formülü girip D7'ye kadar doldurun:=KdvliFiyat(B2;C2)=KdvliFiyat(B2,C2)Kullanıcı Tanımlı Fonksiyon
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ürün | Fiyat (TL) | KDV Oranı | KDV'li Fiyat (TL) |
| 2 | Klavye | 450,00 | 20% | #AD? |
| 3 | Fare | 220,00 | 20% | #AD? |
| 4 | Monitör | 4.800,00 | 20% | #AD? |
| 5 | Kitap | 135,00 | 10% | #AD? |
| 6 | Kulaklık | 750,00 | 20% | #AD? |
| 7 | Webcam | 980,00 | 20% | #AD? |
Fiyat ListesiExcelDepo v1.0.0
2. Metinle çalışan işlev
Ad soyad bilgisinden baş harfleri çıkaran BasHarfler adlı bir işlev yazılır. İşlev, metni boşluklardan Split ile parçalar, her parçanın ilk harfini Left ile alır ve birleştirir. Modüle işlevi yazdıktan sonra C2 hücresine işlevin adını ve A2 başvurusunu içeren formülü girip C7'ye kadar doldurun.Kullanıcı Tanımlı Fonksiyon
Formülünü incelemek için bir hücre seçin.| A | B | C | |
|---|---|---|---|
| 1 | Ad Soyad | Departman | Baş Harfler |
| 2 | Ayşe Yılmaz | Muhasebe | #AD? |
| 3 | Mehmet Ali Demir | Satış | #AD? |
| 4 | Fatma Nur Kaya | İnsan Kaynakları | #AD? |
| 5 | Can Öztürk | Üretim | #AD? |
| 6 | Zeynep Arslan | Pazarlama | #AD? |
| 7 | Burak Çelik | Lojistik | #AD? |
PersonelExcelDepo v1.0.0
3. İsteğe bağlı bağımsız değişkenli prim işlevi
PrimHesapla işlevi satış tutarını ve isteğe bağlı bir hedefi alır. Satış hedefin 1,2 katına ulaşırsa yüzde 10, hedefe ulaşırsa yüzde 5 prim verir; aksi halde prim sıfırdır. Hedef yazılmazsa Optional ile tanımlanan varsayılan 50.000 kullanılır. D sütunundaki formülde yalnızca satış, E sütunundaki formülde ise hedef olarak 60000 de verilir.Kullanıcı Tanımlı Fonksiyon
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Satıcı | Bölge | Satış (TL) | Prim (Hedef 50.000) | Prim (Hedef 60.000) |
| 2 | Elif | İstanbul | 72.000,00 | #AD? | #AD? |
| 3 | Murat | Ankara | 53.000,00 | #AD? | #AD? |
| 4 | Selin | İzmir | 41.000,00 | #AD? | #AD? |
| 5 | Oğuz | Bursa | 64.000,00 | #AD? | #AD? |
| 6 | Deniz | Antalya | 38.500,00 | #AD? | #AD? |
| 7 | Hakan | Adana | 60.000,00 | #AD? | #AD? |
Satış PrimiExcelDepo v1.0.0
Kullanırken dikkat edin
- Modül türüne dikkat edin: İşlevi sayfa kod penceresine ya da ThisWorkbook'a yazarsanız hücrede #AD? (#NAME?) hatası alırsınız. Hücrelerden çağrılacak işlevler daima Ekle > Modül ile eklenen standart modülde olmalıdır.
- Yeniden hesaplanmama sorunu: İşlev yalnızca bağımsız değişken olarak verdiğiniz hücreler değişince yeniden hesaplanır. İşlev içinde başka bir hücreyi doğrudan okuyorsanız o hücreyi bağımsız değişken yapın ya da işlevin içine Application.Volatile ekleyin. Bu ifade dosyayı yavaşlatabileceği için gerekmedikçe kullanmayın.
- Formül dili ve VBA: VBA içinde Range.Formula özelliği formülü İngilizce işlev adları ve virgül ayırıcıyla alır; Türkçe yazım için FormulaLocal kullanılır. Hücrede ise Türkçe Excel'de bağımsız değişkenler noktalı virgülle ayrılır; işlev adı ise iki dilde de aynıdır.
- Performans ve hata yönetimi: Binlerce hücrede çağrılan işlevlerde döngüyü ve hücre okumalarını en aza indirin. Büyük aralıklarda hücre hücre okumak yerine aralığı bir diziye alıp işleyin. Hatalı girişleri IsNumeric ile denetleyin; hata durumunda anlamlı bir değer döndürmek hesap tablosunu daha güvenilir kılar.
- Desteklenmeyen ortamlar: VBA kodu Excel Online ve mobil uygulamalarda çalışmaz; kod içeren dosya bu ortamlarda açıldığında işlevler hesaplanmayabilir. Makro içermeyen bir çözüm gerekiyorsa LAMBDA ile adlandırılmış işlevi tercih edin.
- İlgili konular:
- Makro ve Visual Basic Düzenleyicisi: Alt+F11 ile açılan kod ortamı
- Standart modül: İşlevlerin yazıldığı yer
- Sub prosedürü: Değer döndürmeyen makrolar
- Application.Volatile: Her hesaplamada çalışan işlev
- Excel Eklentisi (.xlam): İşlevleri paylaşma yolu
- Ad Yöneticisi: Adlandırılmış formüller ve LAMBDA tanımları
- İşlevler: LAMBDA, LET, EĞER
