EXCEL VBADA ADLANDIRILMIŞ ARALIK OLUŞTURMA

 

Microsoft Excel'de, VBA Adlandırılmış Aralık, belirli bir hücreye, hücre aralığına veya sabit değere atanan kullanıcı tanımlı bir etiket veya addır. Çalışma kitabınızdaki verilere başvurmanın kullanışlı bir yolu olarak hizmet eder. Adlandırılmış Aralıklar formülleri daha okunabilir hale getirebilir, gezinmeyi basitleştirebilir ve VBA makrolarının işlevselliğini artırabilir. VBA Adlandırılmış Aralıkları kullanırken daha da güçlü hale gelir ve verileri düzenlemenize ve işlemleri daha verimli bir şekilde gerçekleştirmenize olanak tanır.

Aşağıdaki örnekte, aylık gelir rakamlarının bulunduğu C sütunundaki verilerle sık sık çalışmamız gereken Excel'de bir finansal modelimiz var.

Bu sütun için VBA kullanarak bir Adlandırılmış Aralık oluşturabilirsiniz. Bunu yapmak için basit bir VBA kodu şöyledir :

Bu örnekte, "MonthlyRevenue" adlı Adlandırılmış Aralık oluşturmak için VBA kullanıyoruz. Kod, verilerin bulunduğu çalışma sayfasını belirterek başlar ve aylık gelir rakamlarını (C2:C13) içeren hücre aralığını incomeRange olarak tanımlar. Daha sonra "MonthlyRevenue" Adlandırılmış Aralığını oluşturur. Bu, formüllerde ve VBA kodunda gelir verilerine başvurmayı ve bunlarla çalışmayı kolaylaştırır.

ÖNEMLİ NOKTALAR

1. VBA Adlandırılmış Aralık değeri, belirli hücre aralığında bulunan verileri ifade eder. VBA kodunu kullanarak bu verilere programatik olarak erişebilir ve bunları işleyebilirsiniz.
2. VBA Adlandırılmış Aralık Hücre Başvurusu, hücre başvurularını kullanarak VBA Adlandırılmış Aralık içindeki tek tek hücrelere başvurmanıza olanak tanır. Örneğin, Adlandırılmış Aralık içinde hücre başvurusunu belirterek belirli bir hücrenin değerine, biçimine veya özelliklerine erişebilirsiniz.
3. VBA Adlandırılmış Aralık Sütun Referansı'nı kullanarak Adlandırılmış Aralık içindeki tüm sütunlara başvurabilirsiniz.
4. Veri sütunları üzerinde işlem yapmak, örneğin toplam ve ortalama hesaplamak veya biçimlendirme uygulamak için yararlıdır.
5. VBA Adlandırılmış Aralık Döngüsü'nü kullanarak VBA Adlandırılmış Aralık içindeki hücreler veya öğeler arasında yineleme yapmak için döngüleri (Her Biri İçin veya Sonraki İçin gibi) kullanabilirsiniz. Bu, her hücre veya öğe üzerinde sırayla eylemler gerçekleştirmenizi sağlar.

Adlandırılmış Aralıklar Nasıl Oluşturulur?

Excel'de Adlandırılmış Aralıklar oluşturmak VBA olmadan manuel olarak yapılabilir. Adlandırılmış Aralık oluşturma adımları şunlardır:

Adım 1: Excel çalışma kitabınızı açın ve Adlandırılmış Aralığı oluşturmak istediğiniz çalışma sayfasına gidin.

Adım 2: İsimlendirmek istediğiniz hücreleri veya hücre aralığını tıklayıp sürükleyerek seçin.

Adım 3: Excel penceresinin sol üst köşesine yakın, sütun etiketlerinin hemen üstünde bulunan “Ad Kutusu”nu bulun.

Adım 4: Etkinleştirmek için “Ad Kutusu”nun içine tıklayın. Alternatif olarak, “Ad Yöneticisi” iletişim kutusunu açmak için Ctrl + F3 tuşlarına basın.

Adım 5: Adlandırılmış Aralık için istediğiniz adı girin. Adın bir harfle başladığından, boşluk içermediğinden ve Excel işlevleri veya anahtar sözcüklerle çakışmadığından emin olun.

Adım 6: Ad atamasını onaylamak için Enter tuşuna basın. Bu eylem, seçilen hücrelere seçilen adla başvuran bir Adlandırılmış Aralık oluşturur.

Adım 7: Adlandırılmış Aralığı bir formülde kullanmak veya ona gitmek için adını bir hücreye veya formül çubuğuna yazın. Excel bunu geçerli bir başvuru olarak tanıyacak ve vurgulayacaktır.

Adım 8: Adlandırılmış Aralıkları yönetmeniz gerekiyorsa, örneğin düzenleme veya silme gibi, bunu “Ad Yöneticisi” iletişim kutusu aracılığıyla yapabilirsiniz. Excel'in şeridindeki “Formüller” sekmesine gidip “Ad Yöneticisi”ne tıklayarak erişin. Buradan, gerektiği gibi Adlandırılmış Aralıkları değiştirebilir, silebilir veya yeni Adlandırılmış Aralıklar tanımlayabilirsiniz.

VBA Kodu Kullanılarak Adlandırılmış Aralıklar Nasıl Oluşturulur?

VBA aracılığıyla Adlandırılmış Aralıklar oluşturmak, süreci otomatikleştirmenize ve Excel iş akışlarınıza entegre etmenize olanak tanır. İşte bunu VBA koduyla nasıl yapabileceğiniz:

Adım 1: Excel çalışma kitabınızı açın ve Visual Basic for Applications (VBA) düzenleyicisine erişmek için ALT + F11 tuşlarına basın.

Adım 2: Menüde “Ekle”ye tıklayıp “Modül”ü seçerek yeni bir modül ekleyin.

Adım 3: Adlandırılmış Aralığı oluşturmak istediğiniz çalışma sayfasını belirtmek için bir VBA çalışma sayfası değişkeni bildirin. Örneğin:

Adım 4: Adlandırılmış Aralığın kapsaması gereken hücre aralığını belirtmek için bir aralık değişkeni bildirin. Örneğin:

Adım 5: Adlandırılmış Aralığı oluşturmak için ThisWorkbook.Names.Add metodunu kullanın. Aşağıdaki kodu ihtiyaçlarınıza göre özelleştirin:

ThisWorkbook.Names.Add Adı:=”BenimAdlandırılmışAralığım”, Başvurulan:=benimAralığım

“MyNamedRange” ifadesini Adlandırılmış Aralığınız için istediğiniz adla değiştirin.

RefersTo'nun myRange'de belirtilen aralığı ifade ettiğinden emin olun.

Adım 6: Excel çalışma kitabınıza dönmek için VBA düzenleyicisini kapatın.

Adım 7: VBA kodunu çalıştırmak ve Adlandırılmış Aralığı oluşturmak için:

“Makro” iletişim kutusunu açmak için ALT + F8 tuşlarına basın. Listeden “CreateNamedRange” makrosunu seçin.

“Çalıştır” butonuna tıklayın.

Adım 8: Adlandırılmış Aralığınız artık oluşturuldu ve çalışma sayfasındaki belirtilen hücre aralığıyla ilişkilendirildi.

Örnekler

Örnek 1: Basit Adlandırılmış Aralık

Diyelim ki “Sheet1” üzerindeki A1:A10 hücrelerinde yer alan kalemlerin giderlerinin listesini içeren bir çalışma kitabınız var. VBA kullanarak “Giderler” adında bir Adlandırılmış Aralık oluşturabilirsiniz.

Adım 1: Yeni modülde, VBA alt rutinini tanımlayarak başlıyoruz.

CreateExpensesNamedRange. Alt rutinler, belirli görevleri gerçekleştiren kod bloklarıdır ve bu durumda, alt rutinimiz bir Adlandırılmış Aralık oluşturacaktır.

Adım 2: Worksheet türünde ws adında bir değişken tanımlıyoruz ve bunu mevcut çalışma kitabındaki (ThisWorkbook) “Sheet1” adlı çalışma sayfasına atıyoruz.

Adım 3: Bu adımda, Range türünden bir değişken olan expenseRange'i bildiriyoruz . "Sheet1" çalışma sayfasında A1 ile A10 arasındaki bir hücre aralığını temsil eder.

Adım 4: Son olarak, “Expenses” adlı Adlandırılmış Bir Aralık oluşturmak için ThisWorkbook.Names.Add yöntemini kullanırız. Bu Adlandırılmış Aralık, expenseRange'de tanımlanan aralıkla ilişkilidir .

Adım 5: Şimdi makroyu kaydedin ve Çalıştır'a tıklayın.

Bu kodu çalıştırdığınızda, Excel'deki "Sheet3" üzerinde "Expenses" adlı bir Adlandırılmış Aralık oluşturur. Bu Adlandırılmış Aralık, A sütunundaki hücre aralığını A1'den A10'a kadar kapsar ve "Expenses"ı " Sheet3 " üzerindeki bu belirli on hücreye atıfta bulunarak etkili bir şekilde tanımlar.

İşte tam kod:

Sub CreateExpensesNamedRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet3”)
Dim expenseRange As Range
Set expenseRange = ws.Range(“A2:A10”)
ThisWorkbook.Names.Add Name:=”Expenses”, RefersTo:=expensesRange
End Sub

Örnek 2: Dinamik Adlandırılmış Aralık

Giriş sayısına göre boyutunu otomatik olarak ayarlayan dinamik bir VBA Adlandırılmış Aralık oluşturabilirsiniz. "Sheet4"te B sütununda bir satış verisi listeniz olduğunu ve B sütunundaki tüm boş olmayan hücreleri içeren "SalesData" adlı bir Adlandırılmış Aralık oluşturmak istediğinizi varsayalım:

Adım 1: Bu örnekte, CreateDynamicNamedRange adlı bir VBA alt rutini tanımlıyoruz. Alt rutinler, belirli görevleri gerçekleştirmek için tasarlanmış kod bloklarıdır ve bu durumda, alt rutinimiz dinamik bir Adlandırılmış Aralık oluşturur.

Adım 2: Worksheet türünde bir ws değişkeni tanımlıyoruz ve bunu geçerli çalışma kitabındaki (ThisWorkbook) “Sheet24” çalışma sayfasına başvuracak şekilde ayarlıyoruz.

Adım 3: Bu adımda, “Sheet2”nin B sütunundaki veriye sahip son satırı hesaplıyoruz. Bunu yapmak için, tüm B sütununa başvurmak için Cells özelliğini kullanıyoruz ve o sütundaki veriye sahip son hücreyi bulmak için End(xlUp) yöntemini uyguluyoruz. Satır numarası lastRow değişkeninde saklanır.

Adım 4: salesDataRange aralık değişkenini bildiriyoruz ve B1 hücresinden B sütunundaki verilerin bulunduğu son satıra kadar olan aralığı kapsayacak şekilde ayarlıyoruz . Veriler eklendikçe veya kaldırıldıkça otomatik olarak uyarlanan dinamik bir aralık oluşturur.

Adım 5: Son olarak, ThisWorkbook.Names.Add yöntemini kullanarak “SalesData” adlı dinamik bir Adlandırılmış Aralık oluşturuyoruz. Bu Adlandırılmış Aralık, salesDataRange değişkeninde belirtilen dinamik aralıkla ilişkilendirilir.

Adım 6: Makroyu kaydedin ve çalıştır'a tıklayın.

Bu kodu çalıştırdığınızda, Excel'deki "Sheet4" üzerinde "SalesData" adlı dinamik bir Adlandırılmış Aralık oluşturur. Bu Adlandırılmış Aralık, B2 hücresinden başlayarak B sütunundaki son boş olmayan hücreye kadar uzanan B sütunundaki tüm boş olmayan hücreleri içerir.

İşte tam kod:

Sub CreateDynamicNamedRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(“Sheet4”)
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, “B”).End(xlUp).Row
Dim salesDataRange As Range
Set salesDataRange = ws.Range(“B2:B” & lastRow)
ThisWorkbook.Names.Add Name:=”SalesData”, RefersTo:=salesDataRange
End Sub