Vba'da kullanıcı tanımlı bir işlev nasıl oluşturulur?

Muhtemelen VBA öğrenmenin en harika faydalarından biri kendi işlevlerinizi yaratma yeteneğidir.

Excel’de 450’den fazla işlev vardır ve bunlardan bazıları günlük işlerinizde çok faydalıdır. Ancak Excel size VBA’yı kullanarak özel bir işlev oluşturma seçeneği sunar. Evet haklısın. KULLANICI TANIMLI İşlev, UDF olarak kısaltılır veya buna özel bir VBA işlevi de diyebilirsiniz.

Ve güvenle söyleyebileceğim bir şey var ki, her yeni başlayan VBA kullanıcısı, kullanıcı tanımlı bir işlevin nasıl oluşturulacağını öğrenmek ister. Sen değil? Özel bir özellik oluşturmak isteyen kişilerden biriyseniz, yorumlar bölümünde “Evet” deyin.

Bunun, VBA kullanarak ilk özel işlevinizi oluşturmanıza yardımcı olacak TAM bir KILAVUZ olduğunu söylemekten mutluluk duyuyorum ve bunun dışında, size ilham vermek için KULLANICI TANIMLI İŞLEVLER’in bazı örneklerini paylaştım.

1. Burada kullanıcı tanımlı fonksiyon, özel fonksiyon ve UDF kelimelerini birbirinin yerine kullanacağım. O yüzden benimle kal, önümüzdeki birkaç dakika içinde VBA rock yıldızı olacaksın.
2. VBA özel işlevine yönelik kod oluşturmak için onu yazmanız gerekir, makro kaydediciyi kullanarak kaydedemezsiniz .

Neden özel bir Excel işlevi oluşturmalısınız?

Söylediğim gibi Excel’de hemen hemen her sorunu çözmenize ve her türlü hesaplamayı yapmanıza yardımcı olabilecek birçok yerleşik işlev vardır. Ancak bazen belirli durumlarda bir UDF oluşturmanız gerekir.

Aşağıda özel bir işlevi kullanmanız gereken bazı nedenleri veya durumları listeledim.

1. Bunun için bir işlev olmadığında

Bu, VBA ile UDF oluşturmanın yaygın nedenlerinden biridir, çünkü bazen bir şeyi hesaplamanız gerekir ve bunun için özel bir işlev yoktur. Size bir hücreden kelime sayma örneği verebilirim ve bunun için UDF’nin mükemmel bir çözüm olabileceğini buldum.

2. Karmaşık bir formülü değiştirin

Formüllerle çalışıyorsanız, karmaşık formüllerin okunmasının zor olduğunu ve bazen başkaları için anlaşılmasının daha zor olduğunu bildiğinizden eminim. Dolayısıyla, özel bir işlev bu soruna bir çözüm olabilir çünkü bir UDF oluşturduğunuzda bu karmaşık formülü tekrar tekrar yazmanıza gerek kalmaz.

3. SUB Rutinini kullanmak istemediğinizde

Hesaplama yapmak için VBA kodunu kullanabilseniz de VBA kodları dinamik değildir*. Hesaplamanızı güncellemek istiyorsanız bu kodu tekrar çalıştırmanız gerekir. Ancak bu kodu fonksiyona dönüştürürseniz, bu kodu tekrar tekrar çalıştırmanıza gerek kalmaz çünkü onu sadece fonksiyon olarak ekleyebilirsiniz.

Excel’de İlk Kullanıcı Tanımlı İşlevinizi Nasıl Oluşturabilirsiniz?

Tamam o zaman bak. Tüm süreci üç adıma ayırdım:

1. Prosedürünüzü bir fonksiyon olarak bildirin
2. Bağımsız değişkenlerini ve veri türlerini tanımlayın
3. İstenilen değeri hesaplamak için kod ekleyin

Ama sana şunu söyleyeyim:

Tarih değerinden gün adını döndürebilecek bir fonksiyon oluşturmanız gerekiyor. Haftanın gününü döndüren ancak adı döndürmeyen bir fonksiyonumuz var. Ne dediğimi anlıyor musun? Evet?

Şimdi ilk kullanıcı tanımlı fonksiyonunuzu oluşturmak için aşağıdaki adımları izleyelim:

1. Öncelikle ALT + F11 kısayol tuşunu kullanarak Visual Basic editörünüzü açın veya Geliştirici sekmesine gidin ve “Visual Basic” butonuna tıklamanız yeterlidir.
   
2. Bir sonraki şey bir modül eklemektir, bu nedenle VBA proje penceresine sağ tıklayın, ardından eklemeye gidin ve ardından “Modül” e tıklayın. (UYARI: Yalnızca standart modüllerde KULLANICI TANIMLI İŞLEV girmelisiniz. Hem Sayfa hem de ThisWorkbook modülleri özel bir modül türüdür ve bu iki modüle UDF girerseniz Excel, UDF oluşturduğunuzu algılamaz) .
   
3. Üçüncüsü ise fonksiyona isim belirlemek ve burada “myDayName” kullanıyorum. Bu yüzden “Function mydayName” yazmanız gerekiyor. Neden İsimden Önce İşlev? Bir VBA işlevi oluşturduğunuzda, “İşlev” sözcüğünü kullanmak Excel’e bu kodu bir işlev olarak ele almasını söyler (mesajdan önce UDF’nin kapsamını okuduğunuzdan emin olun).
4. Bundan sonra fonksiyonunuz için argümanları tanımlamanız gerekir. Bu yüzden ön parantezleri ekleyin ve “InputDate As Date” yazın. Burada, OutputDate bağımsız değişkenin adıdır ve tarih ise veri türüdür. Bağımsız değişken için bir veri türü tanımlamak her zaman en iyisidir.
   
5. Şimdi parantezleri kapatın ve “As String” yazın. Burada fonksiyonun döndürdüğü sonucun veri tipini ve günün ismini istediğiniz gibi tanımlıyorsunuz, dolayısıyla veri tipinin “String” olması gerekiyor. Sonucun dize dışında bir şey olmasını istiyorsanız veri türünü buna göre ayarladığınızdan emin olun. (myDayName(InputDate As Date) As String işlevi).
   
6. Sonunda ENTER’a basın. Bu noktada fonksiyon adınız, argümanı, argüman veri tipi ve fonksiyon veri tipi ayarlandı ve modülünüzde aşağıdakine benzer bir şey var:
   
7. Şimdi “Fonksiyon” ve “Son Fonksiyon”da hesaplamayı tanımlamanız gerekiyor veya bu UDF’nin çalışmasını söyleyebilirsiniz. Excel’de “Metin” diye bir çalışma sayfası fonksiyonu var ve biz de aynısını burada kullanıyoruz. Bunun için de aşağıdaki kodu yazmanız gerekiyor ve bu kodla fonksiyonun döndürmesi gereken değeri tanımlıyorsunuz. myDayName = WorksheetFunction.Text(InputDate, “dddddd”)
   
8. Şimdi VB düzenleyicinizi kapatın ve çalışma sayfasına geri dönün ve B2 hücresine “=myDayName(A2)” yazın ve Enter tuşuna basın; günün adını alacaksınız.
   

Tebrikler! İlk kullanıcı tanımlı fonksiyonunuzu yeni yarattınız. Bu gerçek Sevincin zamanıdır. Değil mi? Yorumlar kısmına “Sevinç” yazın.

Bu işlev nasıl çalışır ve hücrede değer döndürme

İlk özel işleviniz burada, ancak önemli olan bunun nasıl çalıştığını anlamanız gerektiğidir. Basitçe söylersem bu VBA kodudur ama siz bunu fonksiyon prosedürü olarak kullandınız. Üç parçaya bölelim:

• Bunu bir hücreye fonksiyon olarak girersiniz ve giriş değerini belirtirsiniz.
• Excel, işlevin arkasındaki kodu çalıştırır ve başvurduğunuz değeri kullanır.
• Sonuç hücrede var.

Ancak bu işlevin içeriden nasıl çalıştığını anlamalısınız. Bu yüzden tüm süreci, fonksiyon için yazdığınız kodun gerçekte nasıl çalıştığını görebileceğiniz üç farklı parçaya böldüm.

İşlev argümanı olarak “InputDate” belirttiğiniz için ve hücreye işlevi girip tarih belirttiğinizde VBA bu tarih değerini alır ve kodda kullandığınız metin işlevine sağlar.

Yukarıda bahsettiğim örnekte ise A1 hücresindeki tarih 01-Ocak-2019.

Daha sonra TEXT fonksiyonu bu tarihi, fonksiyon kodunda belirttiğiniz “dddddd” format kodunu kullanarak bir güne çevirir. TEXT fonksiyonunun döndürdüğü bu gün ise “myDayName”e atanır.

Yani TEXT fonksiyonunun sonucu Salı ise bu değer “myDayName”e atanacaktır.

Ve burada fonksiyonun işleyişi sona eriyor. “myDayName” işlev adıdır, dolayısıyla “myDayName”e atanan herhangi bir değer sonuç değeri olacaktır ve çalışma sayfasına eklediğiniz işlev onu hücreye döndürecektir.

Özel bir işlev için kod yazarken , o kodun döndürdüğü değerin işlev adına atandığından emin olmalısınız.

İyilik İçin Bir UDF Nasıl Geliştirilir?

Özel bir VBA işlevinin nasıl oluşturulacağını biliyorsunuz.

ŞİMDİ…

Çalıştığınız kodun tüm olasılıkları karşılayacak kadar iyi olduğundan emin olmanız gereken bir şey var. Eğer yukarıda yazdığınız fonksiyondan bahsediyorsanız bir tarihten gün adını döndürebilirsiniz.

Amaç…

Peki ya belirttiğiniz değer bir tarih değilse? Bahsettiğiniz hücre boşsa ne olur? Başka olasılıklar da olabilir ama demek istediğimi anladığınıza eminim.

SAĞ? Öyleyse yukarıdaki sorunları çözebilecek bu özel işlevi geliştirmeye çalışalım. İYİ. Öncelikle argümanın veri türünü değiştirmeniz ve kullanmanız gerekir:

 InputDate As Variant

Bununla, özel işleviniz her türlü veriyi girdi olarak alabilir. Daha sonra, belirli koşullar için OutputDate’i kontrol etmek üzere VBA IF ifadesini kullanmamız gerekir. İlk koşul hücrenin boş olup olmadığıdır. Ve bunun için aşağıdaki kodu kullanmalısınız:

 If InputDate = "" Then myDayName = ""

Başvuruda bulunduğunuz hücre boşsa bu, işlevi boş hale getirecektir.

Bir sorun çözüldü, diğerine geçelim. Tarihin yanı sıra bir numara veya metin de olabilir. Dolayısıyla bunun için başvurulan değerin gerçek bir tarih olup olmadığını kontrol edecek bir koşul da oluşturmanız gerekir.

Kod şöyle olacaktır:

 If IsDate(InputDate) = False Then myDateName = ""

Burada her iki koşul için de boşluk kullanıyorum, böylece büyük verileriniz varsa, giriş değerinin geçerli olmadığı değerleri kolayca filtreleyebilirsiniz. Yukarıdaki koşulları ekledikten sonra kod şöyle görünecektir:

 Function myDayName(InputDate As Variant) As String If InputDate = "" Then myDayName = "" Else If IsDate(InputDate) = False Then myDateName = "" Else myDayName = WorksheetFunction.Text(InputDate, "dddddd") End If End If End Function

Ve şu anda işleyiş şekli şu şekilde: Bu özellik üzerinde hâlâ değişiklik yapabileceğinize eminim, ancak demek istediğimi açıkça anladığınıza eminim.

Özel bir VBA işlevi nasıl kullanılır?

Bu noktada Excel’de VBA fonksiyonunun nasıl oluşturulacağını kabaca biliyorsunuz. Ancak bir kez sahip olduğunuzda, onu nasıl kullanabileceğinizi bilmeniz gerekir. Yazının bu kısmında ise nasıl ve nerede kullanabileceğinizi sizlerle paylaşacağım. O halde hadi içeri girelim.

1. Basitçe bir e-tabloda

Neden özel bir işlev yaratıyoruz? Basit. Elektronik tabloda kullanmak için. Eşittir işaretini ve işlevin tür adını kullanarak bir UDF’yi bir e-tabloya kolayca girebilir, ardından bağımsız değişkenlerini belirtebilirsiniz.

Ayrıca, işlev kitaplığından kullanıcı tanımlı bir işlevi de girebilirsiniz. Formül sekmesine gidin ➜ İşlev Ekle ➜ Kullanıcı Tanımlı.

Bu listeden eklemek istediğiniz UDF’yi seçebilirsiniz.

2. Diğer alt prosedürleri ve işlevleri kullanma

Bir işlevi diğer işlevler içinde veya bir “Alt” prosedürde de kullanabilirsiniz. Aşağıda, geçerli tarihin gün adını almak için işlevi kullandığınız bir VBA kodu bulunmaktadır.

 Sub todayDay() MsgBox "Today is " & myDayName(Date) End Sub

Bir işlevi diğer prosedürlerde kullanma hakkında daha fazla bilgi edinmek için bu makalenin ilerleyen kısımlarındaki “UDF’nin Kapsamı” bölümünü mutlaka okuyun.

3. Başka bir çalışma kitabındaki işlevlere erişin

Bir çalışma kitabında UDF’niz varsa ve bunu başka bir çalışma kitabında veya tüm çalışma kitaplarında kullanmak istiyorsanız, bunun için bir eklenti oluşturarak bunu yapabilirsiniz. Şu basit adımları izleyin:

• Öncelikle, (özel işlev kodunun bulunduğu) dosyayı eklenti olarak kaydetmeniz gerekir.
• Bunu yapmak için Dosya sekmesine gidin ➜ Farklı kaydet ➜ “Excel Eklentileri (.xalm).

• Bundan sonra eklentiye çift tıklayın ve yükleyin.

Bu kadar. Artık tüm VBA işlevlerinizi herhangi bir çalışma kitabında kullanabilirsiniz.

Özel bir VBA işlevi oluşturmanın farklı yolları [ileri düzey]

Bu noktada VBA’da nasıl özel fonksiyon oluşturulacağını biliyorsunuz. Ancak sorun şu ki, Yerleşik işlevleri kullandığımızda, farklı türde argümanlarla gelirler.

Bu kılavuzun bu bölümünde farklı argüman türleriyle nasıl UDF oluşturulacağını öğreneceksiniz.

• Herhangi bir tartışma olmadan
• Tek bir argümanla
• Birkaç argümanla
• Argüman olarak diziyi kullanma

…ileriye doğru ilerliyoruz.

1. Herhangi bir argüman olmadan

ŞİMDİ ve BUGÜN gibi argüman girmenize gerek olmayan işlevleri hatırlıyor musunuz?

Evet. Herhangi bir bağımsız değişken girmenize gerek olmayan, kullanıcı tanımlı bir işlev oluşturabilirsiniz. Bir örnekle yapalım:

Geçerli dosyanın konumunu döndürebilecek özel bir işlev oluşturalım. Ve işte kod:

 Function myPath() As String Dim myLocation As String Dim myName As String myLocation = ActiveWorkbook.FullName myName = ActiveWorkbook.Name If myLocation = myName Then myPath = "File is not saved yet." Else myPath = myLocation End If End Function

Bu işlev, geçerli dosyanın depolandığı konumun yolunu döndürür ve çalışma kitabı herhangi bir yerde depolanmamışsa “Dosya henüz kaydedilmedi” şeklinde bir mesaj görüntüler.

Şimdi, eğer bu fonksiyonun koduna çok dikkat ederseniz, bir argüman (parantez içinde) tanımlamanıza gerek kalmaz. Fonksiyon sonucu için veri tipini az önce tanımladınız.

Bağımsız değişken içermeyen bir işlev oluşturmanın temel kuralı, hiçbir şey yazmanıza gerek olmayan koddur.

Basitçe söylemek gerekirse, fonksiyondan geri almak istediğiniz değerin otomatik olarak hesaplanması gerekir.

Ve bu fonksiyonda da aynı şeye sahipsiniz.

Bu ActiveWorkbook.FullName dosyanın konumunu döndürür ve bu ActiveWorkbook.Name adı döndürür. Hiçbir şey girmenize gerek yok.

2. Tek argümanla

Kullanıcı tanımlı bir işlevin nasıl oluşturulacağını öğrenerek bu konuyu zaten ele aldık. Ama biraz daha derine inelim ve farklı bir işlev yaratalım. Bu, birkaç ay önce bir köprüden URL’yi çıkarmak için oluşturduğum işlevdir.

 Function giveMeURL(rng As Range) As String On Error Resume Next giveMeURL = rng.Hyperlinks(1).Address End Function

Şimdi bu fonksiyonda yalnızca bir argümanınız var.

Bunu bir hücreye girdiğinizde ve ardından köprünün bulunduğu hücreyi belirttiğinizde, köprünün URL’sini döndürecektir. Şimdi bu fonksiyonda asıl iş şu şekilde yapılır:

 rng.Hyperlinks(1).Address

Ancak rng belirtmeniz gereken şeydir. UDF oluşturmayı kolay buluyorsanız yorumlar bölümünde “Kolay” deyin.

3. Çeşitli argümanlarla

Normalde çoğu Excel yerleşik işlevinin birden çok bağımsız değişkeni vardır. Bu nedenle, birden fazla argümanla özel bir işlevin nasıl oluşturulacağını öğrenmeniz çok önemlidir.

Bir örnek verelim: Bir metin dizesinden belirli harfleri çıkarmak ve geri kalan kısmını korumak istiyorsunuz.

Bu özel fonksiyonda kullanacağınız RIGHT ve LEN gibi fonksiyonlarınız var. Ama burada buna ihtiyacımız yok. Tek ihtiyacımız olan VBA kullanan özel bir işlev.

Yani, işte fonksiyon:

 Function removeFirstC(rng As String, cnt As Long) As String removeFirstC = Right(rng, Len(rng) - cnt) End Functio

Tamam o zaman bak:

Bu fonksiyonda iki argümanınız var:

• rng: Bu bağımsız değişkende, bir metnin ilk karakterini kaldırmak istediğiniz hücreyi belirtmeniz gerekir.
• cnt: Ve argümanda kaldırılacak karakter sayısını belirtmeniz gerekir (eğer metinden birden fazla karakter kaldırmak istiyorsanız).

Bir hücreye girdiğinizde aşağıdaki gibi çalışır:

3.1 İsteğe bağlı ve gerekli bağımsız değişkenlerle kullanıcı tanımlı bir işlev oluşturma

Yukarıdaki örnekte az önce oluşturduğumuz, iki farklı argümanın olduğu fonksiyonu düşünürseniz, her ikisi de gereklidir. Ve bunlardan herhangi birini kaçırırsanız, bunun gibi bir hata alırsınız.

Şimdi mantıksal düşünürseniz oluşturduğumuz fonksiyon ilk karakteri kaldırmaktır. Ancak burada kaldırılacak karakter sayısını belirtmeniz gerekiyor. Demek istediğim, bu argümanın isteğe bağlı olması ve bir tanesini varsayılan olarak alması gerektiğidir.

Ne düşünüyorsun?

Bu konuda benimle aynı fikirdeyseniz yorum kısmına “Evet” deyin.

Tamam o zaman bak. Bir argümanı isteğe bağlı yapmak için önüne “İsteğe Bağlı” ifadesini eklemeniz yeterlidir. Aynen böyle:

Ancak önemli olan kodunuzun bu argümanın değeri olsun veya olmasın çalışmasını sağlamaktır. Yani aynı işlev için yeni kodumuz şöyle görünecektir: Şimdi, eğer ikinci argümanı belirtmeyi göz ardı ederseniz, kodda.

4. Diziyi argüman olarak kullanın

Dizi bağımsız değişkenlerini alabilen birkaç yerleşik işlev vardır ve bunu yapmak için özel VBA işlevinizi de oluşturabilirsiniz.

Bunu, sayıların ve metnin bulunduğu bir aralığın değerlerini topladığınız bir fonksiyon oluşturmanız gereken basit bir örnekle yapalım. Buradayız.

 Function addNumbers(CellRef As Range) Dim Cell As Range For Each Cell In CellRef If IsNumeric(Cell.Value) = True Then Result = Result + Cell.Value End If Next Cell addNumbers = Result End Function

Fonksiyonun yukarıdaki kodunda tek bir değer veya hücre referansı yerine A1:A10 tamsayı aralığını kullandık.

FOR EACH döngüsünü kullanarak aralıktaki her hücreyi kontrol eder ve hücre bir sayı içeriyorsa değeri toplar.

Kullanıcı tanımlı bir fonksiyonun kapsamı

Basit bir ifadeyle, bir fonksiyonun kapsamı onun diğer prosedürlerden çağrılıp çağrılamayacağı anlamına gelir. Bir FDU’nun iki farklı kapsamı olabilir.

1. İzleyici

Özel işlevinizi herkese açık hale getirerek çalışma kitabındaki tüm çalışma sayfalarında çağırabilirsiniz. Bir işlevi herkese açık hale getirmek için aşağıdaki gibi “Genel” kelimesini kullanmanız yeterlidir.

Ancak bir işlevi özel yapmazsanız varsayılan olarak genel bir işlevdir. Ele aldığımız tüm örneklerde hepsi halka açıktır.

2. Özel

Bir fonksiyonu özel yaptığınızda onu aynı modüldeki prosedürlerde kullanabilirsiniz.

Diyelim ki “Module1″de UDF’niz varsa onu sadece “Module1″de yaptığınız işlemlerde kullanabilirsiniz. Ve çalışma sayfası işlevler listesinde görünmez (= işaretini kullandığınızda ve adı yazmaya çalıştığınızda), ancak yine de adını yazıp bağımsız değişkenleri belirterek kullanabilirsiniz.

Kullanıcı Tanımlı İşlevin Sınırlamaları [UDF]

UDF’ler son derece kullanışlıdır. Ancak bazı durumlarda sınırlıdırlar. VBA’da özel bir işlev oluştururken not etmenizi ve hatırlamanızı istediğim birkaç şey var.

• Özel bir işlev kullanarak hücreleri ve aralığı düzenleyemez, silemez veya biçimlendiremezsiniz.
• Ayrıca çalışma kitabını taşıyamaz, yeniden adlandıramaz, silemez veya çalışma kitabına ekleyemezsiniz.
• Başka bir hücrenin değerini değiştirin.
• Ayrıca ortam seçeneklerini değiştiremez.

Yerleşik bir işlev ile kullanıcı tanımlı bir işlev arasında bir fark var mı?

Sorduğuna sevindim. Bu soruyu cevaplamak için sizin için önemli olduğunu düşündüğüm bazı noktaları paylaşmak istiyorum.

1. Yerleşikten daha yavaş: Yerleşik işlevlerle VBA işlevlerinin hızını karşılaştırırsanız, ilkinin hızlı olduğunu göreceksiniz. Bunun nedeni yerleşik fonksiyonların C++ veya FORTRAN dilinde yazılmış olmasıdır.
2. Dosya paylaşmanın zorluğu: Dosyaları genellikle e-posta ve bulut aracılığıyla paylaşırız. Özel işlevlerden herhangi birini kullanıyorsanız, başka birinin de özel işlevinizi kullanabilmesi için bu dosyayı “xlam” formatında paylaşmanız gerekir.

Ancak yukarıda “Neden Özel Excel İşlevi Oluşturmalısınız” bölümünde söylediğim gibi, VBAözel işlevini tercih edebileceğiniz belirli durumlar vardır.

Çözüm

Kullanıcı tanımlı bir işlev oluşturmak basittir. Tek yapmanız gereken, onu bir fonksiyon olarak tanımlamak, argümanlar eklemek, argümanların veri tipini ayarlamak ve ardından dönüş değeri için veri tipini ayarlamak için adından önce “Fonksiyon” kullanmaktır.

Son olarak fonksiyondan geri almak istediğiniz değeri hesaplamak için kodu ekleyin. Bugün sizlerle paylaştığım bu rehber, VBA’da özel fonksiyon oluşturmayı öğrenmenin en kolay rehberidir ve eminim faydalı bulmuşsunuzdur.

Ama şimdi bana bir şeyi söyle.

UDF’ler faydalıdır, ne düşünüyorsunuz?

Lütfen görüşlerinizi yorum kısmında benimle paylaşın. Sizden haber almayı çok isterim ve lütfen bu gönderiyi arkadaşlarınızla paylaşmayı unutmayın, eminim onlar da bunu takdir edeceklerdir.

İlgili eğitimler

• VBA işlevlerinin listesi – örneklerle açıklanmıştır
• Excel’de makro nasıl çalıştırılır
• VBA’da IF deyimi nasıl kullanılır?
• VBA’da aralıklar ve hücrelerle nasıl çalışılır
• VBA kodunu kullanarak yeni bir sayfa ekleyin