VBA Bölme İşlevini Kullanma
VBA Böl işlevi, bileşen parçalarını, her bileşenin belirli bir sınırlayıcı karakter kullandığı standart bir metin dizesinden ayırmanıza olanak tanır; virgül veya iki nokta üst üste. Dizedeki sınırlayıcıları aramak için kod yazmaktan ve ardından değerleri çıkarmaktan daha kolaydır.
Virgülle Ayrılmış Değerden (CSV dosyası) bir satırda okuyorsanız veya tümü tek satırda olan bir posta adresiniz varsa, ancak bunu birden çok satır olarak görmek istiyorsanız kullanılabilir.
Sözdizimi:
| 1 | Bölünmüş ifade, sınırlayıcı[isteğe bağlı], sınır[isteğe bağlı], karşılaştır[isteğe bağlı] |
VBA Split işlevinin dört parametresi vardır:
- İfade - Farklı parçalara bölmek istediğiniz metin dizisi.
- sınırlayıcı (isteğe bağlı)- dize veya yazdırılamayan karakter - Bölme için kullanılacak sınırlayıcı karakteri tanımlar. Sınırlayıcı karakter sağlanmazsa, varsayılan boşluk kullanılır.
- sınır (isteğe bağlı) - sayı - Kaç bölme yapılacağını tanımlar. Boşsa, dize içinde mevcut tüm bölmeler yapılacaktır. 1 olarak ayarlanırsa, bölme yapılmayacaktır. Temel olarak, dizenin başlangıcından başlayarak belirli sayıda değeri ayırmanıza olanak tanır; dize çok uzun ve sadece ilk üç bölmeye ihtiyacınız var.
- Karşılaştırmak (isteğe bağlı) - Sınırlayıcınız bir metin karakteriyse, bu, sınırlayıcının büyük/küçük harfe duyarlı olup olmadığını değiştirmek için kullanılır. Değerler vbBinaryCompare (büyük/küçük harf duyarlı) ve vbTextCompare'dir (büyük/küçük harf duyarlı değildir).
split işlevi her zaman bir dizi döndürür.
Bölme İşlevinin Basit Örneği
| 123456789101112 | Alt SplitÖrnek()'Değişkenleri tanımlayınDim MyArray() As String, MyString As String, I As Variant'Boşluk sınırlayıcılı örnek dizeMyString = "Bir İki Üç Dört"'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString)'her değeri göstermek için oluşturulan diziyi yineleyinMyArray'deki Her I İçinMesaj Kutusu Isonraki benAlt Bitiş |
Bu örnekte, tüm sözcüklerin arasında boşluk olduğundan, sınırlayıcı belirtilmemiştir, bu nedenle varsayılan sınırlayıcı (boşluk) kullanılabilir.
Dizinin boyutu yoktur ve bir dize olarak ayarlanır. For… Next döngüsünde kullanılan I değişkeni bir varyant olarak boyutlandırılmalıdır.
Bu kod çalıştırıldığında, bölmelerin her biri için bir tane olmak üzere dört mesaj kutusu gösterecektir; Bir, iki, üç. Dört.
Dizedeki kelimeler arasında çift boşluk varsa, bunun içinde hiçbir şey olmamasına rağmen bölme olarak değerlendirileceğini unutmayın. Bu, görmek istediğiniz sonuç olmayabilir.
Herhangi bir çift boşluğu tek bir boşlukla değiştirmek için Değiştir işlevini kullanarak bu sorunu çözebilirsiniz:
| 1 | MyString = Değiştir(MyString, " ", " ") |
Sondaki veya önde gelen boşluk da boş bir bölme oluşturarak sorunlara neden olabilir. Bunları görmek genellikle çok zordur. Bu gereksiz boşlukları Kırp işlevini kullanarak kaldırabilirsiniz:
| 1 | MyString = Kırp(MyString) |
Ayırıcı Karakterle Bölme İşlevini Kullanma
Noktalı virgül (;) sınırlayıcı kullanabiliriz. Bu, adresleri ayırmak için sıklıkla e-posta adresi dizelerinde bulunur. Size birçok meslektaşınızla paylaşılan bir e-posta gönderilmiş olabilir ve bu e-postanın kime gittiğine dair çalışma sayfanızda bir liste görmek isteyebilirsiniz. E-posta adreslerini "Kime" veya "Kopyala" kutularından kodunuza kolayca kopyalayabilirsiniz.
| 123456789101112131415 | Sub SplitBySemicolonExample()'Değişkenleri tanımlayınDim MyArray() As String, MyString As String, I As Variant, N As Integer'Noktalı virgül sınırlayıcılı örnek dizeMyString = "[email protected];[email protected];[email protected];[email protected]"'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString, ";")'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'dizi boyunca yineleN = 0 için UBound(MyArray)'Her e-posta adresini çalışma sayfasının ilk sütununa yerleştirinAralık("A" & N + 1).Value = MyArray(N)Sonraki NAlt Bitiş |
For… Next döngüsünün diziyi yinelemek için kullanıldığını unutmayın. Dizideki ilk eleman her zaman sıfırdan başlar ve maksimum eleman sayısını elde etmek için Üst Sınır işlevi kullanılır.
Bu kodu çalıştırdıktan sonra çalışma sayfanız şöyle görünecektir:
Bir Bölme İşlevinde Bir Sınır Parametresi Kullanma
Limit parametresi, dizenin başlangıcından itibaren belirli sayıda bölmenin yapılmasına izin verir. Ne yazık ki, bir başlangıç pozisyonu veya yapılacak bir dizi bölme sağlayamazsınız, bu nedenle oldukça basittir. Bunu yapmak için bir işlev oluşturmak için kendi VBA kodunuzu oluşturabilirsiniz ve bu, bu makalenin ilerleyen bölümlerinde açıklanacaktır.
| 123456789101112131415 | Sub SplitWithLimitExample()'Değişkenler oluşturDim MyArray() As String, MyString As String, I As Variant, N As Integer'Virgül sınırlayıcılı örnek dizeMyString = "Bir,İki,Üç,Dört,Beş,Altı"'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString, ",", 4)'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'Dizi boyunca yinelemeN = 0 için UBound(MyArray)'Her bölmeyi çalışma sayfasının ilk sütununa yerleştirinAralık("A" & N + 1).Value = MyArray(N)Sonraki NAlt Bitiş |
Bu kodu çalıştırdıktan sonra çalışma sayfanız şöyle görünecektir:
Yalnızca ilk üç bölünmüş değer ayrı olarak gösterilir. Sonraki üç değer tek bir uzun dize olarak gösterilir ve bölünmez.
Bir dize içindeki sınırlayıcı sayısından daha büyük bir sınır değeri seçerseniz, bu bir hata oluşturmaz. Dize, sınır değeri sağlanmamış gibi tüm bileşen parçalarına bölünecektir.
Bir Bölme İşlevinde Karşılaştırma Parametresini Kullanma
Karşılaştırma parametresi, sınırlayıcının büyük/küçük harfe duyarlı olup olmadığını belirler. Sınırlayıcılar virgül, noktalı virgül veya iki nokta üst üste ise bu geçerli değildir.
Not: Bunun yerine, tüm modül için büyük/küçük harf duyarlılığını ortadan kaldırmak için her zaman Option Compare Text <> öğesini modülünüzün en üstüne yerleştirebilirsiniz.
| 123456789101112131415 | Sub SplitByCompareExample()'Değişkenler oluşturDim MyArray() As String, MyString As String, I As Variant, N As Integer'X sınırlayıcılı örnek dizeMyString = "OneXTwoXThreexFourXFivexSix"'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString, "X", , vbBinaryCompare)'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'dizi boyunca yineleN = 0 için UBound(MyArray)'Her bölmeyi çalışma sayfasının ilk sütununa yerleştirinAralık("A" & N + 1).Value = MyArray(N)Sonraki NAlt Bitiş |
Bu örnekte, bölünecek dize, sınırlayıcı olarak 'X' karakterini kullanır. Ancak bu dizgede büyük ve küçük harf 'X' karakterlerinin bir karışımı vardır. Böl işlevindeki Karşılaştır parametresi, büyük bir 'X' karakteri kullanır.
Karşılaştırma parametresi vbBinaryCompare olarak ayarlanırsa, küçük harfli 'x' karakterleri yok sayılır ve çalışma sayfanız şöyle görünür:
Karşılaştırma parametresi vbTextCompare olarak ayarlanmışsa, bölmede küçük harf 'x' karakterleri kullanılacak ve çalışma sayfanız şöyle görünecektir:
Küçük bir 'x' karakteri içerdiğinden A6 hücresindeki değerin kesildiğini unutmayın. Bölme büyük/küçük harfe duyarlı olmadığından, bir alt dizenin parçasını oluşturan herhangi bir sınırlayıcı bölmenin gerçekleşmesine neden olur.
Bu, metin sınırlayıcı ve vbTextCompare kullanırken akılda tutulması gereken önemli bir noktadır. Kolayca yanlış sonuca varabilirsiniz.
Ayırıcı Karakter Olarak Yazdırılamayan Karakterleri Kullanma
Ayırıcı olarak satır başı (satır sonu) gibi yazdırılamayan karakterleri kullanabilirsiniz.
Burada bir satır başı belirtmek için vbCr kullanıyoruz <>
| 123456789101112131415 | Sub SplitByNonPrintableExample()'Değişkenler oluşturDim MyArray() As String, MyString As String, I As Variant, N As Integer'Satır başı sınırlayıcıları olan örnek dizeMyString = "Bir" & vbCr & "İki" & vbCr & "Üç" & vbCr & "Dört" & vbCr & "Beş" & vbCr & "Altı"'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString, vbCr, , vbTextCompare)'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'Dizi boyunca yinelemeN = 0 için UBound(MyArray)'Her bölmeyi çalışma sayfasının ilk sütununa yerleştirinAralık("A" & N + 1).Value = MyArray(N)Sonraki NAlt Bitiş |
Bu örnekte, sınırlayıcı olarak vbCr (satır başı karakteri) kullanılarak bir dize oluşturulur.
Bu kod çalıştırıldığında çalışma sayfanız şöyle görünecektir:
Bir Bölmeyi Tersine Çevirmek için Birleştirme İşlevini Kullanma
Join işlevi, bir dizinin tüm öğelerini belirli bir sınırlayıcı kullanarak yeniden birleştirir. Sınırlayıcı karakter belirtilmezse boşluk kullanılır.
| 123456789101112131415 | Alt JoinÖrnek()'Değişkenler oluşturDim MyArray() As String, MyString As String, I As Variant, N As IntegerDize Olarak Hedefi Karart'Virgül sınırlayıcılı örnek dizeMyString = "Bir,İki,Üç,Dört,Beş,Altı"'MyString'i A1 hücresine yerleştirinAralık("A1").Value = MyString'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString, ",")'Bir noktalı virgül sınırlayıcı kullanarak orijinal dizeyi yeniden oluşturmak için Join işlevini kullanınHedef = Katıl(MyArray";")'Sonuç dizesini A2 hücresine yerleştirinAralık ("A2"). Değer = HedefAlt Bitiş |
Bu kod, virgül sınırlayıcıları olan bir dizeyi bir diziye böler ve noktalı virgül sınırlayıcıları kullanarak onu tekrar birleştirir.
Bu kodu çalıştırdıktan sonra çalışma sayfanız şöyle görünecektir:
A1 hücresinde virgül sınırlayıcılı orijinal dize bulunur ve A2 hücresinde noktalı virgül sınırlayıcılı yeni birleştirilmiş dize bulunur.
Kelime Sayımı yapmak için Bölme İşlevini Kullanma
Excel VBA'daki bir dize değişkeninin 2 Gb'ye kadar uzunlukta olabileceğini göz önünde bulundurarak, bir metin parçasında sözcük sayımı yapmak için bölme işlevini kullanabilirsiniz. Açıkçası, Microsoft Word bunu otomatik olarak yapar, ancak bu, basit bir metin dosyası veya başka bir uygulamadan kopyalanan metin için yararlı olabilir.
| 1234567891011121314 | Alt NumberOfWordsÖrnek()'Değişkenler oluşturDim MyArray() As String, MyString As String'Boşluk sınırlayıcılı örnek dizeMyString = "Bir İki Üç Dört Beş Altı"'Çift boşlukları kaldırınMyString = Değiştir(MyString, " ", " ")'Öndeki veya sondaki boşlukları kaldırınMyString = Kırp(MyString)'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString)'UBound işlevini kullanarak kelime sayısını gösterMsgBox "Kelime Sayısı" & UBound(MyArray) + 1Alt Bitiş |
Bu kelime sayma kodunun tehlikelerinden biri, çift boşluklar ve baştaki ve sondaki boşluklar tarafından atılacak olmasıdır. Bunlar varsa, bunlar fazladan kelimeler olarak sayılacak ve kelime sayısı hatalı olarak sonuçlanacaktır.
Kod, bu fazladan boşlukları kaldırmak için Değiştir ve Kırp işlevlerini kullanır.
Son kod satırı, dizinin maksimum eleman sayısını almak için UBound işlevi kullanılarak ve ardından 1 ile artırılarak bulunan sözcük sayısını görüntüler. Bunun nedeni, ilk dizi öğesinin sıfırdan başlamasıdır.
Bir Adresi Çalışma Sayfası Hücrelerine Bölme
Posta adresleri genellikle virgül sınırlayıcılı uzun metin dizeleridir. Adresin her bir bölümünü ayrı bir hücreye bölmek isteyebilirsiniz.
| 123456789101112131415 | Alt AdresÖrnek()'Değişkenler oluşturDim MyArray() As String, MyString As String, N As Integer'Microsoft Corporation Adresi ile dize kurMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 ABD"'Dizeyi virgül sınırlayıcı kullanarak bölmek için bölme işlevini kullanınMyArray = Böl(MyString, ",")'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'dizi boyunca yineleN = 0 için UBound(MyArray)'Her bölmeyi çalışma sayfasının ilk sütununa yerleştirinAralık("A" & N + 1).Value = MyArray(N)Sonraki NAlt Bitiş |
Bu kodu çalıştırmak, adresin her satırını ayrı bir hücreye koymak için virgül sınırlayıcıyı kullanır:
Yalnızca posta kodunu (son dizi öğesi) döndürmek istiyorsanız, kodu kullanabilirsiniz:
| 123456789101112 | Alt AdresZipCodeÖrnek()'Değişkenler oluşturDim MyArray() As String, MyString As String, N As Integer, Temp As String'Microsoft Corporation Adresi ile dize kurMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 ABD"'Dizeyi virgül sınırlayıcı kullanarak bölmek için bölme işlevini kullanınMyArray = Böl(MyString, ",")'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'Posta Kodunu A1 hücresine koyAralık("A1").Value = MyArray(UBound(MyArray))Alt Bitiş |
Bu, dizideki yalnızca UBound işlevi kullanılarak bulunan son öğeyi kullanır.
Öte yandan, bir adres etiketine yazdırılabilmesi için tüm satırları tek bir hücrede görmek isteyebilirsiniz:
| 1234567891011121314151617 | Alt AdresÖrnek()'Değişkenler oluşturDim MyArray() As String, MyString As String, N As Integer, Temp As String'Microsoft Corporation Adresi ile dize kurMyString = "Microsoft Corporation, One Microsoft Way, Redmond, WA 98052-6399 ABD"'Dizeyi bir virgül sınırlayıcı kullanarak bölmek için bölme işlevini kullanınMyArray = Böl(MyString, ",")'Çalışma sayfasını temizleActiveSheet.UsedRange.Clear'dizi boyunca yineleN = 0 için UBound(MyArray)'her dizi öğesini artı bir satır besleme karakterini bir dizeye yerleştirinSıcaklık = Sıcaklık & MyArray(N) & vbLfSonraki N'Dizeyi çalışma sayfasına koyAralık("A1") = SıcaklıkAlt Bitiş |
Bu örnek, tüm dizi öğelerinin geçici bir dizesini oluşturması, ancak her öğeden sonra bir satır besleme karakteri eklemesi dışında öncekiyle aynı şekilde çalışır.
Kod çalıştırıldıktan sonra çalışma sayfası aşağıdaki gibi görünecektir:
Dizeyi Çalışma Sayfası Hücrelerine Böl
Böl dizisini yalnızca bir komutla <> çalışma sayfası hücrelerine kopyalayabilirsiniz:
| 12345678910 | Alt CopyToRange()'Değişkenler oluşturDim MyArray() As String, MyString As String'Boşluk sınırlayıcılı örnek dizeMyString = "Bir,İki,Üç,Dört,Beş,Altı"'Dizenin bileşen parçalarını bölmek için Böl işlevini kullanınMyArray = Böl(MyString, ",")'Diziyi çalışma sayfasına kopyalayınRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Alt Bitiş |
Bu kod çalıştırıldığında, çalışma sayfanız şöyle görünecektir:
Belirli Bir Noktadan Bölmeye İzin Verecek Yeni Bir İşlev Oluşturma
Bölme işlevindeki Sınır parametresi, yalnızca bölmenin durmasını istediğiniz yerde bir üst sınır belirlemenize izin verir. Her zaman dizenin başlangıcından başlar.
Dize içinde bölmenin başlangıç noktasını ve o noktadan itibaren görmek istediğiniz bölme sayısını belirleyebileceğiniz benzer bir işleve sahip olmak çok faydalı olacaktır. Ayrıca, dizideki son öğe olarak çok büyük bir dize değerine sahip olmak yerine, yalnızca diziye belirttiğiniz bölmeleri ayıklar.
Bunu yapmak için VBA'da kolayca bir işlev (SplitSlicer olarak adlandırılır) oluşturabilirsiniz:
| 123456789101112131415161718192021222324 | Function SplitSlicer(Dize Olarak Hedef, Dize Olarak Del, Tamsayı Olarak Başla, Tamsayı Olarak N)'Dizi değişkeni oluşturDim MyArray() Dize Olarak'Sınırlayıcı karakteri kullanarak başlangıç değişkenini kullanarak bölmeyi yakalayınMyArray = Böl(Hedef, Del, Başlat)'Başlangıç parametresinin bölme sayısından büyük olup olmadığını kontrol edin - bu sorunlara neden olabilirBaşlat > UBound(MyArray) + 1 ise'Hatayı göster ve fonksiyondan çıkMsgBox "Başlangıç parametresi mevcut bölme sayısından fazla"SplitSlicer = MyArrayÇıkış FonksiyonuBitir'Son dizi öğesini dizeye koyunHedef = MyArray(UBound(MyArray))'Sınır olarak N kullanarak dizeyi bölünMyArray = Böl(Hedef, Del, N)'Kod son öğeyi kaldırdığı için üst sınırın sıfırdan büyük olduğunu kontrol edinUBound(MyArray) > 0 ise'Dizin son öğesini kaldırmak için ReDim'i kullanınReDim MyArray'i Koru(UBound(MyArray) - 1)Bitir'Yeni diziyi döndürSplitSlicer = MyArrayBitiş İşlevi |
Bu fonksiyon dört parametre ile oluşturulmuştur:
- Hedef - dize - bu, bölmek istediğiniz giriş dizesidir
- del - dize veya yazdırılamayan karakter - bu, kullandığınız sınırlayıcı karakterdir, ör. virgül, kolon
- Başlangıç - sayı - bu, diliminizin başlangıç bölümüdür
- n - sayı - bu, diliminizde yapmak istediğiniz bölmelerin sayısıdır.
Bu parametrelerin hiçbiri isteğe bağlı değildir veya varsayılan değerlere sahip değildir, ancak daha fazla genişletmek isterseniz bunu işlevin kodunda çalıştırabilirsiniz.
İşlev, Limit olarak Start parametresini kullanarak bir dizi oluşturmak için Split işlevini kullanır. Bu, dizi öğelerinin başlangıç parametresine kadar olan bölmeleri tutacağı, ancak dizenin geri kalanının son öğe olacağı ve bölünmeyeceği anlamına gelir.
Dizideki son eleman, bunun hangi eleman olduğunu belirlemek için UBound işlevi kullanılarak bir dizgeye geri aktarılır.
Dize daha sonra limit değişkeni olarak N kullanılarak tekrar diziye bölünür. Bu, dize için N konumuna kadar bölmelerin yapılacağı ve ardından dizenin geri kalanının dizideki son öğeyi oluşturacağı anlamına gelir.
ReDim ifadesi, dizide yalnızca belirli öğelerin kalmasını istediğimiz için son öğeyi kaldırmak için kullanılır. Preserve parametresinin kullanıldığını unutmayın, aksi takdirde dizideki tüm veriler kaybolacaktır.
Yeni dizi daha sonra çağrıldığı koda döndürülür.
Kodun "hata korumalı" olduğunu unutmayın. Kullanıcılar genellikle düşünmediğiniz garip şeyler yapacaktır. Örneğin, işlevi, dizedeki mevcut bölme sayısından daha fazla Başlat veya N parametresiyle kullanmayı denerlerse, bu, işlevin başarısız olmasına neden olabilir.
Başlangıç değerini kontrol etmek ve ayrıca dizide ReDim ifadesi kullanıldığında kaldırılabilecek bir öğe olduğundan emin olmak için kod eklenmiştir.
İşte işlevi test etmek için kod:
| 123456789101112 | Alt TestSplitSlicer()'Değişkenler oluşturDim MyArray() As String, MyString As String'Virgül sınırlayıcılarla örnek dize tanımlayınMyString = "Bir,İki,Üç,Dört,Beş,Altı,Yedi,Sekiz,Dokuz,On"'Yeni diziyi tanımlamak için Splitslicer işlevini kullanınMyArray = SplitSlicer(MyString, ",", 4, 3)'Etkin sayfayı temizleActiveSheet.UsedRange.Clear'Diziyi çalışma sayfasına kopyalayınRange("A1:A" & UBound(MyArray) + 1).Value = WorksheetFunction.Transpose(MyArray)Alt Bitiş |
Bu kodu çalıştırın ve çalışma sayfanız şöyle görünecektir:
