String Fonksiyonları
Karakter Fonksiyonları
Asc ve Chr: Exceldeki, CODE ve CHAR fonksiyonlarının benzeridir. Sırayla bir karakterin ASCII kodunu ve bir ASCII kodunun karakter karşılığını verirler. Yani birbirlerini zıttı şeklinde çalışırlar.
Debug.Print Asc("a") '97
Debug.Print Chr(64) '@
Bunların W ile biten iki versiyonu daha var. Bunlarla da ASCII'nin genişletilmiş kümesi olan UNICODE karakter işlemleri yapılır.
For i = 1 To 65535
Cells(i, 1).Value = ChrW(i)
Next i
Parça alma ve pozisyon fonksiyonları
Left(string, n): Metnin solundan istenilen uzunlukta (n) parça keser.
Right(string, n): Metnin sağından istenilen uzunlukta (n) parça keser.
Mid(string, k, [n]): Metnin ortasından belirtilen indeksten (k) itibaren belirtilen uzunlukta (n) karakter keser. Son parametre girilmezse 2. parametreden itibaren tümünü keser.
Len(string): Metnin uzunluğunu (boyutunu) verir.
InStr([n], string, substring, [Compare]): n. karakterden itibaren aramaya başlayarak bir metin içinde başka bir metni veya karakteri arar, indeks numarasını (sırasını) döndürür. Bulamazsa 0 döner. Son opsiyonel parametrenin varsayılan değeri vbBinaryCompare'dir (constant olarak 0), yani default arama şekli case-sensitivedir. Küçük/büyük harf ayrımı olmadan arama yapılması isteniyorsa bu parametreye vbTextCompare (1) girilir. Ama bu parametre girildiğinde ilk parametrenin de girilmesi gerekir.
InStrRev(string, substring, [n], [Compare]): InStr fonksiyonunun aramayı sondan yapan versiyonudur. Ancak bulunan indeks yine baştan sayarak bulunan indekstir. Ör: "Ardahan"da a'yı aratırsak, sondan 2. indekste bulur, bunun da baştan sayılan indeksi 6'dır, sonuç 6 olur.
StrReverse(string): Metni terse çevirir.
Replace(metin, aranan, neyle değiştir, başlanacak yer, adet, compare): İlgili metin içinde aranan metni istenilen metinle değiştirir. Başlangıç indeksi 1'den farklı verilebilir, ve sadece belirli adet değişiklik yapılması istenebilir.
String tipi ilginç bir tiptir. Programlama dünyasına aşinaysanız bilirsiniz, string yapısı referans tipli ve hantal bir yapıdır. Üzerinde manipülasyon yapıldığında yeni bir kopyası oluşturulur. Replace işleminde de bu olur. Bu da şu demek; Replace edilecek bir metin olmasa bile kopya oluşturulur. O yüzden eğer döngüsel ve uzun bir replace işlemi yapacaksanız önce replace edilecek eleman var mı diye bakmakta fayda var. Aşağıdaki gibi:
If InStr(metin, aranan) <> 0 Then 'eleman var mı
metin = Replace(metin, aranan, değiştir)
End If
Bu tür optimizasyon teknikleri için şu siteye bakabilirsiniz.
Yukarıdaki açıklamalarda metin olarak verilen her şey bir hücrenin içeriği de olabilir. Bu arada yine yukarıdaki açıklamalardan anlaşıldığı üzere []'ler içindeki parametreler opsiyonel parametrelerdir.
metin = "Mustafa Kemal Atatürk"
Debug.Print Left(metin, 3) 'Mus
Debug.Print Right(metin, 3) 'ürk
Debug.Print Mid(metin, 3, 2) 'st
Debug.Print Mid(metin, 3) 'stafa Kemal Atatürk
Debug.Print Len(metin) '21
Debug.Print InStr(metin, "türk") '18
Debug.Print InStr(metin, "Türk") '0, çünkü case-sensitive
Debug.Print InStr(1, metin, "Türk", 1) '18
Debug.Print InStr(metin, "K") '9, Kemal'in k'si
Debug.Print InStr(metin, "k") 'Atatürk'ün sonundaki k
Debug.Print InStr(metin, "a") '5
Debug.Print InStr(10, metin, "a") '12. Aramaya 10'dan başlar ama bulduğu konumun indeksi 1'den itibaren sayılır. yani bu örnekte 3 değil, 12 bulunur.
Debug.Print InStr(10, metin, "z") '0
Debug.Print InStrRev(metin, "a") '17
Debug.Print InStrRev(metin, "a", 16) '12
Debug.Print StrReverse("volkan") 'naklov
Dönüşüm Fonksiyonları
LCase(string): Metni küçük harfe çevirir.
UCase(string): Metni büyük harfe çevirir.
Str(string) ve CStr(expression): Str, numerik değeri metinsel ifadeye dönüştürür. Aldığı parametrenin tamamen numerik bir değer olması gerekir. Str, bu numerik ifadeyi başında bir boşluk olacak şekilde metne çevirir. Ör: 123'ü " 123" şeklinde 4 karakterli bir metin yapar. CStr ise alfanumerik bir parametre alır ve başında boşluk olmadan dönüştürür. Bu bağlamda Str bana biraz anlamsız ve gereksiz geliyor. String dönüşümlerinde sadece CStr'yi kullanın derim.
Val(string): Metinsel ifadeyi rakamsal ifadeye dönüştürür. Dönüş değeri double'dır.
StrConv(string, tip): Metni istenen formattaki bir metne çevirir. Tip olarak vbUpperCase(1), vbLowerCase(2), vbProperCase(3), vbUnicode(64) ve vbFromUnicode(128) değerleri girilebilir. Hepsi de aşikar olduğu için ayrıca açıklamaya gerek bulmuyorum, aşağıdaki örnekler de zaten yeterli olacaktır.
Debug.Print LCase("VOLKAN") 'volkan
Debug.Print UCase("volkan") 'VOLKAN
Debug.Print Str(123) '" 123"
Debug.Print Val("123") '123
Debug.Print StrConv("merhaba dünya", 1) 'MERHABA DÜNYA
Debug.Print StrConv("MERHABA DÜNYA", 2) 'merhaba dünya
Debug.Print StrConv("MERHABA DÜNYA", 3) 'Merhaba Dünya
Kodunuzda kullanıcıdan bilgi girişi istediğinizde ve bunu bir değerle (şifre v.s) karşılaştırdığınızda küçük/büyük ayrımı önemli olacağı için girilen değeri büyük harfe çevirip, karşılaştırdığınız metni de büyük harf hazırlarsanız kullanıcı kaynaklı sorunları çözmüş olursunuz.
sifre = InputBox("Şifreyi giriniz")
If UCase(sifre) = "ABCD" Then
' diğer kodlar
Else
MsgBox "Yanlış şifre girdiniz"
Exit Sub
End If
Boşluklar
Trim(string): Metnin solundaki ve sağındaki tüm boşlukları siler.
LTrim(string): Metnin solundaki tüm boşlukları siler.
RTrim(string): Metnin sağındaki tüm boşlukları siler.
Space(n): n kadar boşluk üretir.
For i = 1 To 10
Cells(i, 1).Value = Space(25 - Len(Cells(i, 1).Value)) & Cells(i, 1).Value
Next
Sıfır uzunluklu metin (boş metin)
"" (içi boş çift tırnak) yazarak sıfır uzunluklu metin elde edebiliyoruz. Bir değerin boş metin olup olmadığını anlamak için if degisken="" yöntemi sık kullanılmaktadır. Ne var ki bu yöntem çok sağlıklı değildir. Özellikle büyük bir döngü içindeyken kesinlikle kaçınılmalıdır.
Alternatif olarak Len veya LenB kullanılabilir.
If LenB(x) = 0 Then
'diğer kodlar
End If
Boş metin atamaları da vbNullString şeklinde yapmanızı tavsiye ederim, "" olarak değil.
Split ve Join
Split ile, belirli bir ayraçla birbirinden ayrılmış kelimeleri birbirinden ayırıp tek boyutlu bir dizi elde ederiz. Mesela bir hücrede ; ile ayrılmış sicil numaralarını birbirinden ayırıp, bu kişileri mail sistemindeki alıcı listesine tek tek ekleyebiliriz. Bu mail gönderim örneğinin tamamını şu sayfada göreceğiz, ancak şu an bizi ilgilendiren kısmına bakalım.
Dim emailgrubu As Variant
'........ diğer kodlar
emailgrubu = Split(ActiveCell.Offset(0, 1).Value, ";")
'ilgili hücredeki metin 12345;12456;12894 ise, bunlar birbirinden ayrılacak ve 3 elemanlı bir dizi elde edilecektir
'....... diğer kodlar
Bir başka örnek de bir hücredeki kelimeleri saydıran veya belirli bir kelimeyi seçen bir Function yazmak olabilir. Benim kullandığım böyle bir fonksiyon var. Microsoft geliştiricileri böyle kritik bir fonksiyonu neden hala yerel fonksiyon listesine eklemiyor, gerçekten hayret ediyorum.
Function kelimesec(hucre As Range, kaçıncı As Byte, Optional ayrac As String = " ")
Dim kelimeler As String
kelimeler = Split(hucre.Value2, ayrac)
kelimesec = kelimeler(kaçıncı - 1)
End Function
Function konusunu henüz incelemediyseniz çok dert etmeyin, öğrendiğiniz zaman gelip tekrar bu örneği inceleyebilirsiniz.
Join ise, Split'in tersi mantıkta çalışır. Dizi elemanlarını, belirli bir ayraç ile metin olarak birleştirir. Hemen örneğe bakalım.
Dim siciller() As Integer
Dim birlesiksiciller As String
'dizi eleman sayısı bir yerden okunur, bu x olsun
ReDim siciller(1 To x)
For i = 1 To x
siciller(i) = Cells(i, 2).Value 'dizi elemanlarına değer atanıyor
Next i
birlesiksiciller = Join(siciller, ";")
Metin Formatlama
String modülünün Format fonksiyonu oldukça faydalı bir fonksiyon olmakla birlikte metinler üzerinde kullanımından ziyade tarihsel ve numerik alanları üzerinde kullanımı daha yaygındır. O yüzden bu kısımdan ziyade Tarih ve Numerik fonksiyon sayfalarında ele alacağız. Yine de sınırlı olan metin formatlama için bu sayfaya bakabilirsiniz.
$ işaretli fonksiyonlar
VBA'de bazı fonksiyonların aynısının sonu $ ile biten versiyonları mevcuttur. Bunların $'sız versiyonları string tipli variant döndürürken, $'lı versiyonları standart string döndürür. Bunun daha derin bir anlamı var, o da şu. $'sız olanlar, üzerinde işlem yaptığı metin null değer ise hata vermezken $'lı olanlar hataya neden olur. Ayrıca $'sız olanlar string döndürdükleri için hafıza ve dolayısıyla performans avantajı sunarlar.
'Şu kod hata vermez
Dim x
x = Null
Debug.Print Left(x, 3)
'Bu kod hata verir
Dim x
x = Null
Debug.Print Left$(x, 3)
Regex dünyası başlı başına ayrı bir dünya olmakla birlikte burda küçük birkaç örnek yapacağız. Konuyu daha iyi kavramak adına şu ve şu sayfalarda denemeler yapmayı ihmal etmeyin. Bunlar, programlama dilinden bağımsız olarak genel regex kullanımına yönelik test sayfalarıdır. Genel yapı dilden bağısmız olarak aynı olmakla birlikte syntax tabiki dilden dile değişmektedir.
Nedir ve ne işe yarar?
Yukarıda, Instr ile belirli bir metni başka bir metin içinde arıyorduk, keza Replace ile de belirli ifadeleri değiştiriyorduk. Ancak ya elimizde direkt bir ifade yoksa, ve onun yerine belirli bir şablon varsa? Örneğin bir metnin içinde "sız, siz, suz, süz" ifadelerinden biri var mı diye bakmak istiyoruz. Tek tek bu 4 sorgulamayı yapmak yerine "s-herhangi bir karakter-z" kalıbını aramak daha mantıklı olurdu değil mi?
Keza içinde herhangi bir rakam geçen metinleri, veya bir rakam bir sayı bir rakam(Ör:5a3, 4r8) gibi bir desen içeren metinleri filtrelemek isteyebiliriz.
İşte bu tür, desen(pattern) bazlı ifadelere düzenli ifadeler (regular expressions) deniyor, bunların kısaltması da regex oluyor.
Örnek kullanım alanları
- Mail adresi, telefon numarası analizlerinde ve/veya kurala uygun veri girişi yapılmasında
- Karakter maskelemede(şifreleme)
- html parsing veya code syntax işaretlemede. (Gerçi mümkün mertebe html parse etmede regex kullanmayın. Bunlar için diğer araçları kullanın)
Nasıl kullanılır?
Öncelikle Regex kullanabilmeniz için Tools>Reference'ten Microsoft VBScript Regular Expressions 5.5 kütüphanesini eklemeniz gerekiyor. (veya Late Binding ile de kullanabilirsiniz, ama bu durumda tabiki intellisense'i unutun) Sonra bazı kavramları bilmek gerekiyor. Bu kavramlara geçmeden önce şunu belirtmek isterim ki, eğer başka bi dilde Regex kullandıysanız işlerin VBA'de biraz farklı olduğunu görebilirsiniz.
Literal ve Meta karakterler
Literal karakterler bildiğimiz karakterlerdir. Örneğin bir metinde abc (literal) ifadesini arıyorsak, parametre olarak direkt abc'yi veririz. Metakarakterler ise biraz daha soyuttur. Bunlardan birden fazla karaktere denk gelebilrler.
Önemli: Farkettiyseniz bazı işlemler için daha kısayollar tasarlanmış. Mesela sayı seçimi için [0-9] yerine \d yapabiliyoruz. Ama mesela eğer kelime kelime arama yapacaksanız, "." gibi tüm whitespace'leri de içeren bir operatörü kullanmamanız gerekir. Mesela, bir mail adresi incelemesi yapalım. Amacımız @ işaretinden önceki kısmı bulmak. Bir mail adresinden neler olur, harf, rakam , nokta veya alt tire değil mi. Bunu normalde şöyle ifade edebiliri. "[a-zA-Z0-9_.]+@". Bunu daha kısa tutmak için ".+@" yaparsak "benim mail adresim volkan.yurtseven@hotmail.com" metni içinden "benim mail adresim volkan.yurtseven@" döndürür. Kısaltma yapmak için nokta kullanmadık, peki \S kullanabilir miyiz? Yine olmaz, çünkü bu durumda yanlışkla yazılmış bir "volkan+yurtseven@hotmail.com" 'daki + işareti koşulu sağladığı için o da gelir. O yüzden sırf kodu kısa tutmak amacıyla yanlış pattern kullanmamalısınız. Ancak şöyle bir alternatif de mevcut: "(\w|\.)+@", yorumu basit: bir alfanumerik karekter veya nokta'dan en az bir tane olsun sonra bir de @.
Regex VBA
Property ve Metodlar
Regex nesnesinin propertyleri şunlardır:
- Pattern: En önemli propertysi bu olup, aradığımız patterni yazarız.
- IgnoreCase: True ise, küçük büyük harf ayrımı yapmaz. (Türkçe karakter için aşağı bakınız)
- Global: True ise tüm eşleşmeler, False ise ilk eşleşme
- MultiLine: True ise, satır satır bakılır.
Metodlar ise şunlardır:
- Test: Patterne bakar, bulursa True bulamazsa False döner
- Replace: Eşleşmeleri başka bir ifade ile değiştirir
- Execute: Eşleşmeleri bir MatchCollection nesnesi olarak döndürür
Örnekler
İlk olarak bir prosedür örneği. Bu prosedürle bir alandaki hücrelerde parantez içinde bulunan ifadeleri sildireceğiz.
Sub ParantezYoket()
Dim Str As String
Dim Replace_Str As String
Dim regexObject As New RegExp
Dim matches As MatchCollection
Dim match As match
With regexObject
.Pattern = "\s?\([^)]+\)"
'\s?: parantez öncesi boşluk varsa veya yoksa, ?'nin 0 veya 1 tekrar olduğunu unutmayın
'\( : açılış parantezi
'[^)]+: bir veya daha fazla parantez olmayan karakter
'\) : kapanış parantezi
.Global = True
End With
Set alan = Range("a1:a3")
For Each cell In alan
Set matches = regexObject.Execute(cell.Value)
For Each match In matches
temp = regexObject.Replace(cell.Value, "")
Next match
cell.Value = temp
Next cell
End Sub
Şimdi bir de UDF örneği yapalım. Bu örnekte, bir kolondaki bulunan kişi isimlerinin (2-3-4 kelimeden oluşabilir) baş harflerini bırakıp kalanını yıldızlama işlemi yapacağız, yani maskeleme uygulayacağız.
Function MaskName(cell As Range) As String
Dim regexObject As New RegExp
Dim matches As MatchCollection
Dim match As match
Dim result As String
With regexObject
.Pattern = "\b(\w)\w+"
.Global = True
End With
Set matches = regexObject.Execute(cell.Value)
result = cell.Value
For Each match In matches
result = Replace(result, match.Value, match.SubMatches(0) & String(Len(match.Value) - 1, "*"))
Next match
MaskName = result
End Function
Function Maskele(metin As String, Optional pattern As String = "\B[A-Za-z]")
Dim reg As New RegExp
reg.Global = True
reg.pattern = pattern
Maskele = reg.Replace(metin, "*")
End Function
Bu örnekte sadece ilk karakterleri maskelemek istedik, o yüzden optional parametre verdik, ama istenirse başka bir pattern de verilerek onların maskelenmesi sağlanabilir.
Türkçe Karakter ve Unicode
Yukarıdaki maskeleme örneğinde isimlerden birinde Türkçe karakter olsaydı sıkıntı yaşardık. Mesela "şükran dağıstan" "şük*** d*ğıs***" olarak maskelenirdi. Bunu engellemek için unicode ifadelerini kullanmak gerekiyor ancak bu örnek için ben bir türlü uygun çözümü bulamadım, zira bu unicode konusu biraz karışık. Onun yerine metin değişkenini şöyle değiştirme yoluna gittim:
metin = Replace(Replace(Replace(Replace(Replace(metin, "ş", "s"), "ü", "u"), "ı", "i"), "ç", "c"), "ğ", "g")
Tabii ki ilk karakter de Türkçe karakterse bunu da değiştirmiş oluyoruz, o yüzden ideal bir çözüm değil. Diğer dillerin çoğunda unicode desteği internal olarak geliyor, maalesef VBA'de bu destek yok.