Excel'de özel işlevler oluşturun

Makrolar gibi özel işlevler Visual Basic for Applications (VBA) programlama dilini kullanır. Makrolardan iki önemli şekilde farklıdırlar. İlk olarak, Alt yordamlar yerine İşlev yordamlarını kullanırlar. Başka bir ifade, Sub deyimi yerine function deyimiyle başlar ve End Sub yerine End İşlevi ile biter. İkincisi, işlem yapmak yerine hesaplamalar yapar. Aralıkları seçen ve biçimlendiren deyimler gibi belirli deyim türleri özel işlevlerin dışında tutulur. Bu makalede, özel işlevler oluşturmayı ve kullanmayı öğreneceksiniz. İşlevler ve makrolar oluşturmak için, Excel'den ayrı yeni bir pencerede açılan Visual Basic Düzenleyici (VBE) ile çalışırsınız.

Siparişin 100'den fazla birim olması koşuluyla şirketinizin bir ürünün satışı için yüzde 10 miktar indirimi sunduğunu varsayalım. Aşağıdaki paragraflarda, bu indirimi hesaplamak için bir işlev göstereceğiz.

Aşağıdaki örnekte her madde, miktar, fiyat, indirim (varsa) ve sonuçta elde edilen genişletilmiş fiyatı listeleyen bir sipariş formu gösterilmektedir.

Bu çalışma kitabında özel bir DISCOUNT işlevi oluşturmak için şu adımları izleyin:

1. Visual Basic Düzenleyici açmak için Alt+F11 tuşlarına basın (Mac'te FN+ALT+F11 tuşlarına basın) ve ardından > ModülüEkle'ye tıklayın. Visual Basic Düzenleyici sağ tarafında yeni bir modül penceresi görüntülenir.

2. Aşağıdaki kodu kopyalayıp yeni modüle yapıştırın.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Artık yeni İNDİrİm işlevini kullanmaya hazırsınız. Visual Basic Düzenleyici kapatın, G7 hücresini seçin ve aşağıdakileri yazın:

=İNDİrİm(D7,E7)

Excel, 200 birimdeki yüzde 10 indirimini birim başına 47,50 TL olarak hesaplar ve 950,00 ABD doları döndürür.

VBA kodunuzun ilk satırındaki İşlev İnDİrİmİ (miktar, fiyat), İnDİrİm işlevinin miktar ve fiyat olarak iki bağımsız değişken gerektirdiğini belirttiniz. çalışma sayfası hücresinde işlevi çağırdığınızda, bu iki bağımsız değişkeni eklemeniz gerekir. =İnDİrİm(D7,E7) formülünde D7 miktar bağımsız değişkeni, E7 ise fiyat bağımsız değişkenidir. Artık aşağıda gösterilen sonuçları almak için İnDİrİm formülünü G8:G13'e kopyalayabilirsiniz.

Şimdi Excel'in bu işlev yordamını nasıl yorumlayanı ele alalım. Enter tuşuna bastığınızda, Excel geçerli çalışma kitabındaki İnDİrİm adını arar ve bunun VBA modülünde özel bir işlev olduğunu bulur. Parantez, miktar ve fiyat içine alınmış bağımsız değişken adları, indirim hesaplamasının temel aldığı değerlerin yer tutucularıdır.

Aşağıdaki kod bloğundaki If deyimi miktar bağımsız değişkenini inceler ve satılan öğe sayısının 100'den büyük veya buna eşit olup olmadığını belirler:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Satılan öğelerin sayısı 100'den büyük veya buna eşitse, VBA aşağıdaki deyimi yürütür; bu da miktar değerini fiyat değeriyle çarpar ve ardından sonucu 0,1 ile çarpar:

Discount = quantity * price * 0.1

Sonuç , Discount değişkeni olarak depolanır. Bir değeri bir değişkende depolayan VBA deyimine atama deyimi adı verilir çünkü eşittir işaretinin sağ tarafındaki ifadeyi değerlendirir ve sonucu soldaki değişken adına atar. discount değişkeni işlev yordamıyla aynı ada sahip olduğundan değişkende depolanan değer, İNDİrİm işlevini çağıran çalışma sayfası formülüne döndürülür.

Miktar 100'den küçükse VBA aşağıdaki deyimi yürütür:

Discount = 0

Son olarak, aşağıdaki deyim discount değişkenine atanan değeri iki ondalık basameğe yuvarlar:

Discount = Application.Round(Discount, 2)

VBA'nın YUVARLA işlevi yoktur, ancak Excel'in vardır. Bu nedenle, bu deyimde ROUND kullanmak için VBA'ya Uygulama nesnesinde (Excel) Round yöntemini (işlev) aramasını söylersiniz. Bunu, Yuvarla sözcüğünden önce Uygulama sözcüğünü ekleyerek yaparsınız. VBA modülünden bir Excel işlevine erişmeniz gerektiğinde bu söz dizimini kullanın.

Özel bir işlevin function deyimiyle başlaması ve End function deyimiyle bitmesi gerekir. İşlev adına ek olarak function deyimi genellikle bir veya daha fazla bağımsız değişken belirtir. Ancak bağımsız değişken içermeyen bir işlev oluşturabilirsiniz. Excel, bağımsız değişkenleri kullanmayan çeşitli yerleşik işlevler (örneğin, S_SAYI_ÜRET ve ŞİmDİ) içerir.

İşlev deyiminin ardından işlev yordamı, karar veren ve işleve geçirilen bağımsız değişkenleri kullanarak hesaplamalar yapan bir veya daha fazla VBA deyimi içerir. Son olarak, işlev yordamının bir yerinde, işlevle aynı ada sahip bir değişkene değer atayan bir deyim eklemeniz gerekir. Bu değer işlevi çağıran formüle döndürülür.

Özel işlevlerde kullanabileceğiniz VBA anahtar sözcüklerinin sayısı, makrolarda kullanabileceğiniz sayıdan daha küçüktür. Özel işlevlerin çalışma sayfasındaki bir formüle veya başka bir VBA makrosunda veya işlevinde kullanılan bir ifadeye değer döndürmekten başka bir şey yapmalarına izin verilmez. Örneğin, özel işlevler pencereleri yeniden boyutlandıramaz, hücredeki bir formülü düzenleyemez veya hücredeki metnin yazı tipi, rengi veya desen seçeneklerini değiştiremez. Bir işlev yordamına bu tür bir "eylem" kodu eklerseniz, işlev #VALUE! seçin.

İşlev yordamının gerçekleştirebileceği tek eylem (hesaplamalar dışında) bir iletişim kutusu görüntülemektir. Özel bir işlevde, işlevi yürüten kullanıcıdan giriş almanın bir aracı olarak InputBox deyimini kullanabilirsiniz. Kullanıcıya bilgi iletme aracı olarak MsgBox deyimini kullanabilirsiniz. Özel iletişim kutularını veya UserForms'ı da kullanabilirsiniz, ancak bu konu bu giriş kapsamının dışındadır.

Basit makroları ve özel işlevleri bile okumak zor olabilir. Açıklama biçiminde açıklayıcı metin yazarak bunları daha kolay anlayabilirsiniz. Açıklama metninin önüne kesme işareti ekleyerek açıklamalar eklersiniz. Örneğin, aşağıdaki örnekte discount işlevi açıklamalarla gösterilmiştir. Bunun gibi açıklamalar eklemek sizin veya başkalarının VBA kodunuzu zaman geçtikçe korumasını kolaylaştırır. Gelecekte kodda bir değişiklik yapmanız gerekirse, başlangıçta ne yaptığını anlamak için daha kolay bir zaman elde edersiniz.

Kesme işareti, Excel'e aynı satırdaki sağdaki her şeyi yoksayması gerektiğini söyler, böylece kendi başına veya VBA kodu içeren satırların sağ tarafında açıklamalar oluşturabilirsiniz. Genel amacını açıklayan bir açıklamayla görece uzun bir kod bloğu başlatabilir ve ardından tek tek deyimleri belgeleyen satır içi açıklamaları kullanabilirsiniz.

Makrolarınızı ve özel işlevlerinizi belgelemenin bir diğer yolu da onlara açıklayıcı adlar vermektir. Örneğin, makro etiketlerini adlandırmak yerine, makronun hizmet etme amacını daha belirgin bir şekilde açıklamak için bunu MonthLabels olarak adlandırabilirsiniz. Makrolar ve özel işlevler için açıklayıcı adlar kullanmak, özellikle benzer ama özdeş amaçlara sahip olmayan yordamlar oluşturduğunuzda, özellikle birçok yordam oluşturduğunuzda yararlı olur.

Makrolarınızı ve özel işlevlerinizi nasıl belgelediğiniz kişisel tercih meselesidir. Önemli olan, bazı belge yöntemlerini benimsemek ve tutarlı bir şekilde kullanmaktır.

Özel bir işlev kullanmak için, işlevi oluşturduğunuz modülü içeren çalışma kitabının açık olması gerekir. Bu çalışma kitabı açık değilse bir #NAME alırsınız? hatasıyla karşılaşırsınız. farklı bir çalışma kitabındaki işleve başvurursanız, işlev adından önce işlevin bulunduğu çalışma kitabının adını yazmanız gerekir. Örneğin, Personal.xlsb adlı bir çalışma kitabında İNDİrİm adlı bir işlev oluşturursanız ve bu işlevi başka bir çalışma kitabından çağırırsanız, yalnızca =discount() değil , =personal.xlsb!discount() yazmanız gerekir.

İşlev Ekle iletişim kutusundan özel işlevlerinizi seçerek bazı tuş vuruşlarını (ve olası yazma hatalarını) kaydedebilirsiniz. Özel işlevleriniz Kullanıcı Tanımlı kategorisinde görünür:

Özel işlevlerinizi her zaman kullanılabilir hale getirmenin daha kolay bir yolu, bunları ayrı bir çalışma kitabında depolamak ve sonra bu çalışma kitabını eklenti olarak kaydetmektir. Ardından, Excel'i her çalıştırdığınızda eklentiyi kullanılabilir duruma getirebilirsiniz. Bunu şu şekilde yapabilirsiniz:

Bu adımları izledikten sonra, Excel'i her çalıştırdığınızda özel işlevleriniz kullanılabilir olur. İşlev kitaplığınıza eklemek istiyorsanız Visual Basic Düzenleyici dönün. Visual Basic Düzenleyici Proje Gezgini'ne VBAProject başlığı altında bakarsanız, eklenti dosyanızın adını taşıyan bir modül görürsünüz. Eklentinizde .xlam uzantısı bulunur.

Proje Gezgini'nde bu modüle çift tıklanması, Visual Basic Düzenleyici işlev kodunuzu görüntülemesine neden olur. Yeni bir işlev eklemek için ekleme noktanızı Kod penceresindeki son işlevi sonlandıran End function deyiminden sonra konumlandırın ve yazmaya başlayın. Bu şekilde ihtiyacınız olan sayıda işlev oluşturabilirsiniz ve bunlar her zaman İşlev Ekle iletişim kutusundaki Kullanıcı Tanımlı kategorisinde kullanılabilir.

Bu içerik ilk olarak Mark Dodge ve Craig Stinson tarafından Microsoft Office Excel 2007 Inside Out kitabının bir parçası olarak yazıldı. O zamandan beri Excel'in daha yeni sürümlerine de uygulanacak şekilde güncelleştirildi.