Excel ile çalışırken, zamanınızın çoğu çalışma sayfası alanında - hücreler ve aralıklarla ilgili olarak - harcanır.
VBA kullanarak Excel'deki çalışmanızı otomatikleştirmek istiyorsanız, VBA kullanarak hücreler ve aralıklarla nasıl çalışacağınızı bilmeniz gerekir.
VBA'da aralıklarla yapabileceğiniz birçok farklı şey vardır (seç, kopyala, taşı, düzenle, vb.).
Bu konuyu ele almak için, bu öğreticiyi bölümlere ayıracağım ve örnekler kullanarak Excel VBA'da hücreler ve aralıklarla nasıl çalışacağınızı göstereceğim.
Başlayalım.
Bu eğitimde bahsettiğim tüm kodların VB Editörüne yerleştirilmesi gerekiyor. Nasıl çalıştığını öğrenmek için 'VBA Kodunu Nereye Koymalı' bölümüne gidin.VBA'yı kolay yoldan öğrenmekle ilgileniyorsanız, Çevrimiçi Excel VBA Eğitimi.
VBA kullanarak Excel'de Hücre / Aralık Seçme
VBA kullanarak Excel'de hücreler ve aralıklarla çalışmak için onu seçmeniz gerekmez.
Çoğu durumda, hücreleri veya aralıkları seçmemeniz (göreceğimiz gibi) daha iyidir.
Buna rağmen, bu bölümü gözden geçirmeniz ve nasıl çalıştığını anlamanız önemlidir. Bu, VBA öğreniminizde çok önemli olacak ve bu eğitim boyunca burada kapsanan birçok kavram kullanılacaktır.
O halde çok basit bir örnekle başlayalım.
VBA Kullanarak Tek Bir Hücre Seçme
Aktif sayfada tek bir hücre seçmek istiyorsanız (örneğin A1), aşağıdaki kodu kullanabilirsiniz:
Sub SelectCell() Range("A1").Select End Sub
Yukarıdaki kod, zorunlu 'Sub' ve 'End Sub' bölümüne ve A1 hücresini seçen bir kod satırına sahiptir.
Range(“A1”), VBA'ya başvurmak istediğimiz hücrenin adresini söyler.
Seçme Range nesnesinin bir yöntemidir ve Range nesnesinde belirtilen hücreleri/aralığı seçer. Hücre referansları çift tırnak içine alınmalıdır.
Bu kod, bir grafik sayfasının etkin bir sayfa olması durumunda bir hata gösterecektir. Bir çizelge sayfası çizelgeler içerir ve yaygın olarak kullanılmaz. İçinde hücre/aralık bulunmadığından, yukarıdaki kod onu seçemez ve sonunda bir hata gösterir.
Aktif sayfada hücreyi seçmek istediğiniz için hücre adresini belirtmeniz yeterli olacaktır.
Ancak başka bir sayfadaki hücreyi seçmek istiyorsanız (Sayfa2 diyelim), önce Sayfa2'yi etkinleştirmeniz ve ardından içindeki hücreyi seçmeniz gerekir.
Sub SelectCell() Worksheets("Sheet2").Activate Range("A1").Select End Sub
Benzer şekilde, bir çalışma kitabını etkinleştirebilir, ardından içinde belirli bir çalışma sayfasını etkinleştirebilir ve ardından bir hücre seçebilirsiniz.
Sub SelectCell() Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate Range("A1").Select End Sub
Çalışma kitaplarına başvurduğunuzda, dosya uzantısıyla birlikte tam adı kullanmanız gerektiğini unutmayın (yukarıdaki kodda .xlsx). Çalışma kitabının hiç kaydedilmemiş olması durumunda dosya uzantısını kullanmanıza gerek yoktur.
Şimdi, bu örnekler çok kullanışlı değil, ancak bu öğreticide daha sonra aynı kavramları Excel'de (VBA kullanarak) hücreleri kopyalayıp yapıştırmak için nasıl kullanabileceğimizi göreceksiniz.
Bir hücreyi seçtiğimiz gibi bir aralık da seçebiliriz.
Aralık olması durumunda, sabit bir boyut aralığı veya değişken bir boyut aralığı olabilir.
Sabit bir boyut aralığında, aralığın ne kadar büyük olduğunu bilirsiniz ve VBA kodunuzda tam boyutu kullanabilirsiniz. Ancak değişken boyutlu bir aralıkla, aralığın ne kadar büyük olduğu hakkında hiçbir fikriniz yoktur ve biraz VBA büyüsü kullanmanız gerekir.
Bunun nasıl yapılacağını görelim.
Sabit Boyutlu Bir Aralık Seçme
İşte A1:D20 aralığını seçecek kod.
Sub SelectRange() Range("A1:D20").Select End Sub
Bunu yapmanın başka bir yolu da aşağıdaki kodu kullanmaktır:
Sub SelectRange() Range("A1", "D20").Select End Sub
Yukarıdaki kod, sol üst hücre adresini (A1) ve sağ alt hücre adresini (D20) alır ve tüm aralığı seçer. Bu teknik, değişken boyutlu aralıklarla çalışırken kullanışlı olur (bu öğreticide daha sonra End özelliğinin ne zaman ele alınacağını göreceğimiz gibi).
Seçimin farklı bir çalışma kitabında veya farklı bir çalışma sayfasında olmasını istiyorsanız, VBA'ya bu nesnelerin tam adlarını söylemeniz gerekir.
Örneğin, aşağıdaki kod, Kitap2 çalışma kitabındaki Sayfa2 çalışma sayfasında A1:D20 aralığını seçer.
Sub SelectRange() Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate Range("A1:D20").Select End Sub
Şimdi, ya kaç satır olduğunu bilmiyorsanız. İçinde bir değeri olan tüm hücreleri seçmek isterseniz ne olur?
Bu durumlarda, bir sonraki bölümde gösterilen yöntemleri kullanmanız gerekir (değişken boyutlu aralık seçiminde).
Değişken Boyutlu Bir Aralık Seçme
Bir hücre aralığı seçmenin farklı yolları vardır. Seçtiğiniz yöntem, verilerin nasıl yapılandırıldığına bağlı olacaktır.
Bu bölümde, VBA'da aralıklarla çalışırken gerçekten yararlı olan bazı yararlı teknikleri ele alacağım.
CurrentRange Özelliğini Kullanarak Seçin
Verilerin kaç satır/sütun olduğunu bilmediğiniz durumlarda Range nesnesinin CurrentRange özelliğini kullanabilirsiniz.
CurrentRange özelliği, bir veri aralığındaki tüm bitişik doldurulmuş hücreleri kapsar.
A1 hücresini tutan geçerli bölgeyi seçecek kod aşağıdadır.
Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub
Yukarıdaki yöntem, içinde boş satır/sütun olmayan bir tablo olarak tüm verilere sahip olduğunuzda iyidir.
Ancak verilerinizde boş satır/sütun varsa, boş satır/sütunlardan sonrakileri seçmeyecektir. Aşağıdaki resimde, CurrentRegion kodu, 11. satır boş olduğundan 10. satıra kadar olan verileri seçer.
Bu gibi durumlarda, Çalışma Sayfası Nesnesinin UseRange özelliğini kullanmak isteyebilirsiniz.
UseRange Özelliğini Kullanarak Seçin
UseRange, değiştirilmiş tüm hücrelere başvurmanıza izin verir.
Bu nedenle, aşağıdaki kod, aktif sayfada kullanılan tüm hücreleri seçecektir.
Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub
Kullanılmış uzak bir hücreniz olması durumunda, yukarıdaki kod tarafından dikkate alınacağını ve kullanılan hücreye kadar tüm hücrelerin seçileceğini unutmayın.
Bitiş Özelliğini Kullanarak Seçin
Şimdi, bu kısım gerçekten faydalı.
End özelliği, en son doldurulan hücreyi seçmenizi sağlar. Bu, Kontrol Aşağı/Yukarı ok tuşunun veya Kontrol Sağ/Sol tuşlarının etkisini taklit etmenize olanak tanır.
Bunu bir örnek kullanarak anlamaya çalışalım.
Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve A sütunundaki son doldurulmuş hücreleri hızlıca seçmek istediğinizi varsayalım.
Buradaki sorun, verilerin değişebilmesi ve kaç hücrenin doldurulduğunu bilmemenizdir. Bunu klavyeyi kullanarak yapmanız gerekiyorsa, A1 hücresini seçin ve ardından Control + Aşağı ok tuşlarını kullanın, sütundaki son doldurulmuş hücreyi seçecektir.
Şimdi bunu VBA kullanarak nasıl yapacağımızı görelim. Bu teknik, değişken boyutlu bir sütunda son doldurulmuş hücreye hızlı bir şekilde atlamak istediğinizde kullanışlıdır.
Sub GoToLastFilledCell() Range("A1").End(xlDown).Select End Sub
Yukarıdaki kod, A sütunundaki son doldurulmuş hücreye atlayacaktır.
Benzer şekilde, arka arkaya son doldurulmuş hücreye atlamak için End(xlToRight) öğesini kullanabilirsiniz.
Sub GoToLastFilledCell() Range("A1").End(xlToRight).Select End Sub
Şimdi, son doldurulmuş hücreye atlamak yerine tüm sütunu seçmek isterseniz ne olur?
Bunu aşağıdaki kodu kullanarak yapabilirsiniz:
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Select End Sub
Yukarıdaki kodda seçmemiz gereken hücrenin ilk ve son referansını kullandık. Kaç tane dolu hücre olursa olsun, yukarıdaki kod hepsini seçecektir.
Aşağıdaki kod satırını kullanarak A1:D20 aralığını seçtiğimiz yukarıdaki örneği hatırlayın:
Aralık ("A1", "D20")
Burada A1 aralıktaki sol üst hücre ve D20 sağ alt hücreydi. Değişken büyüklükteki aralıkları seçerken aynı mantığı kullanabiliriz. Ancak sağ alttaki hücrenin tam adresini bilmediğimiz için, onu elde etmek için End özelliğini kullandık.
Aralık(“A1”, Aralık(“A1”). End(xlDown)), “A1” ilk hücreyi ve Aralık(“A1”).End(xlDown) son hücreyi ifade eder. Her iki referansı da sağladığımız için Select yöntemi bu iki referans arasındaki tüm hücreleri seçer.
Benzer şekilde, birden çok satırı ve sütunu olan bir veri kümesinin tamamını da seçebilirsiniz.
Aşağıdaki kod, A1 hücresinden başlayarak tüm doldurulmuş satırları/sütunları seçer.
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight))).Select End Sub
Yukarıdaki kodda, veri kümesinin sağ alt doldurulmuş hücresinin referansını almak için Range(“A1”).End(xlDown).End(xlToRight) kullandık.
CurrentRegion ve End Kullanımı arasındaki fark
CurrentRegion özelliğine sahipken doldurulmuş aralığı seçmek için neden End özelliğini kullandığınızı merak ediyorsanız, size farkı söyleyeyim.
End özelliği ile başlangıç hücresini belirtebilirsiniz. Örneğin, verileriniz A1:D20'deyse ancak ilk satır başlıklarsa, verileri başlıklar olmadan seçmek için End özelliğini kullanabilirsiniz (aşağıdaki kodu kullanarak).
Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select End Sub
Ancak CurrentRegion, başlıklar dahil tüm veri kümesini otomatik olarak seçer.
Şimdiye kadar bu öğreticide, farklı yollar kullanarak bir dizi hücreye nasıl başvurulacağını gördük.
Şimdi, bazı işleri halletmek için bu teknikleri gerçekten kullanabileceğimiz bazı yollar görelim.
VBA Kullanarak Hücreleri / Aralıkları Kopyalayın
Bu öğreticinin başında bahsettiğim gibi, üzerinde işlem yapmak için bir hücre seçmek gerekli değildir. Bu bölümde hücreleri ve aralıkları seçmeden nasıl kopyalayacağınızı göreceksiniz.
Basit bir örnekle başlayalım.
Tek Hücreyi Kopyalama
A1 hücresini kopyalayıp D1 hücresine yapıştırmak istiyorsanız, aşağıdaki kod bunu yapar.
Sub CopyCell() Range("A1").Copy Range("D1") End Sub
Aralık nesnesinin kopyalama yönteminin hücreyi kopyaladığını (tıpkı Control +C gibi) ve belirtilen hedefe yapıştırdığını unutmayın.
Yukarıdaki örnek kodda, hedef, Copy yöntemini kullandığınız satırda belirtilmiştir. Kodunuzu daha da okunabilir hale getirmek istiyorsanız aşağıdaki kodu kullanabilirsiniz:
Sub CopyCell() Range("A1").Copy Destination:=Range("D1") End Sub
Yukarıdaki kodlar, içindeki biçimlendirme/formüllerin yanı sıra değeri kopyalayıp yapıştıracaktır.
Fark etmiş olabileceğiniz gibi, yukarıdaki kod hücreyi seçmeden kopyalar. Çalışma sayfasının neresinde olursanız olun, kod A1 hücresini kopyalar ve D1'e yapıştırır.
Ayrıca, yukarıdaki kodun D2 hücresindeki mevcut herhangi bir kodun üzerine yazacağını unutmayın. Excel'in D1 hücresinde zaten bir şey olup olmadığını, üzerine yazmadan size bildirmesini istiyorsanız, aşağıdaki kodu kullanabilirsiniz.
Sub CopyCell() If Range("D1") "" Then Response = MsgBox("Mevcut verilerin üzerine yazmak istiyor musunuz", vbYesNo) End If If Response = vbYes Then Range("A1").Copy Range("D1 ") End If End Sub
Sabit Boyutlu Bir Aralığı Kopyalama
A1:D20'yi J1:M20'ye kopyalamak istiyorsanız, aşağıdaki kodu kullanabilirsiniz:
Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub
Hedef hücrede, sol üst hücrenin adresini belirtmeniz yeterlidir. Kod, tam olarak kopyalanan aralığı hedefe otomatik olarak kopyalar.
Verileri bir sayfadan diğerine kopyalamak için aynı yapıyı kullanabilirsiniz.
Aşağıdaki kod, A1:D20'yi aktif sayfadan Sayfa2'ye kopyalayacaktır.
Sub CopyRange() Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Yukarıdakiler, verileri etkin sayfadan kopyalar. Bu nedenle, kodu çalıştırmadan önce verileri içeren sayfanın etkin sayfa olduğundan emin olun. Güvende olmak için, verileri kopyalarken çalışma sayfasının adını da belirtebilirsiniz.
Sub CopyRange() Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Yukarıdaki kodun iyi yanı, hangi sayfa etkin olursa olsun, verileri her zaman Sayfa1'den kopyalayıp Sayfa2'ye yapıştırmasıdır.
Ayrıca, başvuru yerine adını kullanarak adlandırılmış bir aralığı kopyalayabilirsiniz.
Örneğin, 'SalesData' adında bir adlandırılmış aralığınız varsa, bu verileri Sayfa2'ye kopyalamak için aşağıdaki kodu kullanabilirsiniz.
Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub
Adlandırılmış aralığın kapsamı çalışma kitabının tamamıysa, bu kodu çalıştırmak için adlandırılmış aralığın bulunduğu sayfada olmanız gerekmez. Adlandırılmış aralığın kapsamı çalışma kitabı için olduğundan, bu kodu kullanarak herhangi bir sayfadan ona erişebilirsiniz.
Table1 adında bir tablonuz varsa, bunu Sheet2'ye kopyalamak için aşağıdaki kodu kullanabilirsiniz.
Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sayfa2").Range("A1") End Sub
Bir aralığı başka bir Çalışma Kitabına da kopyalayabilirsiniz.
Aşağıdaki örnekte, Excel tablosunu (Tablo1) Book2 çalışma kitabına kopyalıyorum.
Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub
Bu kod, yalnızca Çalışma Kitabı zaten açıksa çalışır.
Değişken Boyutlu Bir Aralığı Kopyalama
Değişken boyutlu aralıkları kopyalamanın bir yolu, bunları adlandırılmış aralıklara veya Excel Tablosuna dönüştürmek ve önceki bölümde gösterildiği gibi kodları kullanmaktır.
Ancak bunu yapamıyorsanız, range nesnesinin CurrentRegion veya End özelliğini kullanabilirsiniz.
Aşağıdaki kod, etkin sayfadaki geçerli bölgeyi kopyalar ve Sayfa2'ye yapıştırır.
Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub
Veri kümenizin ilk sütununu son doldurulan hücreye kadar kopyalayıp Sayfa2'ye yapıştırmak istiyorsanız aşağıdaki kodu kullanabilirsiniz:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1") End Sub
Sütunların yanı sıra satırları da kopyalamak istiyorsanız aşağıdaki kodu kullanabilirsiniz:
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1") End Sub
Tüm bu kodların yürütülürken hücreleri seçmediğini unutmayın. Genel olarak, üzerinde çalışmadan önce bir hücre/aralık seçmeniz gereken yalnızca birkaç durum bulacaksınız.
Nesne Değişkenlerine Aralık Atama
Şimdiye kadar, hücrelerin tam adresini kullanıyoruz (Workbooks(“Book2.xlsx”).Worksheets(“Sheet1”).Range(“A1”)).
Kodunuzu daha yönetilebilir hale getirmek için bu aralıkları nesne değişkenlerine atayabilir ve ardından bu değişkenleri kullanabilirsiniz.
Örneğin, aşağıdaki kodda, kaynak ve hedef aralığı nesne değişkenlerine atadım ve ardından bu değişkenleri bir aralıktan diğerine veri kopyalamak için kullandım.
Sub CopyRange() SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets("Sheet1").Range("A1:D20") Set DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange Alt Bitiş
Değişkenleri Range nesneleri olarak bildirerek başlıyoruz. Daha sonra Set deyimini kullanarak aralığı bu değişkenlere atarız. Aralık değişkene atandıktan sonra, değişkeni kullanabilirsiniz.
Sonraki Boş Hücreye Veri Girin (Giriş Kutusunu Kullanarak)
Kullanıcının verileri girmesine izin vermek için Giriş kutularını kullanabilirsiniz.
Örneğin, aşağıdaki veri setine sahip olduğunuzu ve satış kaydını girmek istediğinizi varsayalım, VBA'da giriş kutusunu kullanabilirsiniz. Bir kod kullanarak, bir sonraki boş satırdaki verileri doldurduğundan emin olabiliriz.
Sub EnterData() Dim RefRange As Range Set RefRange = Range("A1").End(xlDown).Offset(1, 0) Set ProductCategory = RefRange.Offset(0, 1) Set Quantity = RefRange.Offset(0, 2 ) Set Amount = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Ürün Kategorisi") Quantity.Value = InputBox("Miktar") Amount.Value = InputBox("Amount") End Sub
Yukarıdaki kod, kullanıcıdan girişleri almak için VBA Giriş kutusunu kullanır ve ardından girişleri belirtilen hücrelere girer.
Tam hücre referanslarını kullanmadığımızı unutmayın. Bunun yerine, son boş hücreyi bulmak ve içindeki verileri doldurmak için End and Offset özelliğini kullandık.
Bu kod kullanılabilir olmaktan uzaktır. Örneğin, giriş kutusu miktar veya miktar istediğinde bir metin dizisi girerseniz, Excel'in buna izin verdiğini fark edeceksiniz. Değerin sayısal olup olmadığını kontrol etmek için If koşulunu kullanabilir ve buna göre buna izin verebilirsiniz.
Hücreler / Aralıklar Arasında Döngü
Şimdiye kadar hücre ve aralıklardaki verileri nasıl seçeceğimizi, kopyalayacağımızı ve gireceğimizi gördük.
Bu bölümde, bir aralıktaki bir dizi hücre/satır/sütun arasında nasıl döngü yapılacağını göreceğiz. Bu, her bir hücreyi analiz etmek ve ona dayalı olarak bazı eylemler gerçekleştirmek istediğinizde faydalı olabilir.
Örneğin, seçimdeki her üçüncü satırı vurgulamak istiyorsanız, döngüye girip satır numarasını kontrol etmeniz gerekir. Benzer şekilde, yazı tipi rengini kırmızıya çevirerek tüm negatif hücreleri vurgulamak istiyorsanız, döngüye girip her hücrenin değerini analiz etmeniz gerekir.
İşte seçili hücrelerdeki satırlar arasında dolaşacak ve alternatif satırları vurgulayacak kod.
Sub HighlightAlternateRows() Myrange As Range Dim Myrow As Range Set Myrange = Myrange.Rows içindeki Her Myrows için seçim Myrow.Row Mod 2 = 0 Sonra Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Yukarıdaki kod, seçimdeki satır numarasını kontrol etmek için MOD işlevini kullanır. Satır numarası çift ise camgöbeği rengiyle vurgulanır.
Kodun her hücreden geçtiği ve içinde negatif değere sahip hücreleri vurguladığı başka bir örnek.
Sub HighlightAlternateRows() Myrange Aralık Olarak Dim Mycell Aralık Seti Olarak Dim Myrange = Myrange'deki Her Mycell İçin Seçim Mycell < 0 ise Mycell.Interior.Color = vbRed End Eğer Sonraki Mycell End Sub
Aynı şeyi Koşullu Biçimlendirme'yi kullanarak da yapabileceğinizi unutmayın (bu dinamiktir ve bunu yapmanın daha iyi bir yoludur). Bu örnek, yalnızca VBA'daki hücreler ve aralıklarla döngünün nasıl çalıştığını göstermek amacıyla verilmiştir.
VBA Kodunu Nereye Koymalı
Excel çalışma kitabınızda VBA kodunun nereye gittiğini merak mı ediyorsunuz?
Excel'in VBA düzenleyicisi adı verilen bir VBA arka ucu vardır. VB Editor modülü kod penceresine kodu kopyalayıp yapıştırmanız gerekir.
İşte bunu yapmak için adımlar:
- Geliştirici sekmesine gidin.
- Visual Basic seçeneğine tıklayın. Bu, arka uçta VB düzenleyicisini açacaktır.
- VB Düzenleyicisi'ndeki Proje Gezgini bölmesinde, kodu eklemek istediğiniz çalışma kitabı için herhangi bir nesneye sağ tıklayın.Proje Gezgini'ni görmüyorsanız, Görünüm sekmesine gidin ve Proje Gezgini'ne tıklayın.
- Ekle'ye gidin ve Modül'e tıklayın. Bu, çalışma kitabınız için bir modül nesnesi ekleyecektir.
- Modül penceresindeki kodu kopyalayıp yapıştırın.
