A ‘replace’ funkció az Excel VBA-ban egy beépített, előre definiált funkció. Ha teljes szöveget, szövegrészleteket, vagy karaktereket akarunk kicserélni, akkor nagyban megkönnyíti a dolgunkat. Azonban vannak hiányosságai is, amiket a apróbb trükkel meg lehet kerülni.
Olvasói kérésre született a cikk, viszont valóban hiánypótló a téma a blogon.
Gyakorlati példa olvasói levél alapján
Feladat
Van egy táblázatunk több ezer sorral. Tegyük fel, hogy „A” oszlop soraiban egy 9 karakterből álló azonosító szerepel, minden sorban más-más. Ezek az azonosítók tartalmazhatnak betűt és számot is.
Feladatunk, hogy egy makróval a 9 karakteres azonosítókban „*” (csillag) karakterre cseréljük az 1., a 4. és a 9. karaktert.
Magát a cserét a „replace” VBA függvénnyel fogjuk végrehajtani, azonban kell majd egy kis trükk.
Magyarázat
Először is, hogy rugalmassá tegyük, a cserélendő „*” (csillag) karaktert egy változóba mentjük el, valamint az „A” oszlopot is. Később ha változtatni kell valamit, akkor ne kelljen mindenhol a kódban átírni, elég legyen csak egy helyen megtenni ezt.
Második lépés egy for ciklus lesz, amiben végigmegyünk az első sortól (vagy ha van fejléc, akkor a második sortól) az utolsó sorig, amiben érték szerepel. A cikluson belül fogjuk az éppen aktuális sor cellájában lévő karaktereket kicserélni, azaz a cikluson belül lesz minden más kódunk.
Viszont, tegyünk bele még egy ellenőrzést is, azaz vizsgáljuk meg egy if elágazással, hogy az aktuális sor cellájában valóban 9 karakternyi érték van e. Ha igen, akkor csak ott akarjuk kicserélni a karaktereket, ha nem, akkor színezzük ki azt a cellát sárgára (vagy bármi egyebet is tehetünk)
És itt elérkeztünk a cseréhez.
Nézzük meg először az első karakter cseréjét tartalmazó kódunkat:
Cells(i, oszlop).Value = replace(Cells(i, oszlop).Value, Left(Cells(i, oszlop).Value, 1), karakter, 1, 1)Cells(i, oszlop).Value = a ciklusunkban szereplő aktuális cella értéke legyen egyelő az azt következő kifejezéssel. „i” jelöli az aktuális sort, míg az „oszlop” változó tartalmazza az elején meghatározott „A” oszlop elnevezést
replace( a függvényünkben 5 paramétert használunk, ebből 3 kötelező, 2 pedig opcionális, de használnunk kell jelen esetben. Replace(expression, find, replace, [ start, [ count, [ compare ]]])
Cells(i, oszlop).Value, ez az első paramétern, az expression, azaz ebben a string-ben keresem a kifejezést/karaktert, amit le akarok majd cserélni. Ez ugye az adott cellánk értéke lesz
Left(Cells(i, oszlop).Value, 1), ez a második paraméter, a find, azaz ezt a kifejezést/karakter keressük. Jelenleg itt szintén egy beépített függvényt használunk, méghozzá a Left() függvénnyel vizsgáljuk meg, hogy a stringünk-nek mi az első karaktere. Hiszen ugye az első karaktert akarjuk most lecserélni.
karakter, ez a harmadik kötelező paraméter, a replace, azaz erre a stringre akarjuk lecserélni az expression-t. Azaz ez jelenleg a „*” (csillag) karakterünk.
1, ez a negyedig paraméter, a start. Ez már nem kötelező, de jelen esetben szükséges. Ez a kezdőpozíciót adja meg, hogy honnan kezdődjön a keresés. Jelen esetben nekünk az első karaktertől kell. Fontos megjegyezni itt, hogy ez azt is eredményezi majd, hogy az innentől számított értéket adja vissza. Tehát pl. ha a szavunk ALMA és itt 1 szerepel, akkor a végeredmény ALMA lesz, de ha itt 3 szerepel, akkor levágja az első két karaktert és csak MA lesz az eredmény, mert a 3. karaktertől számol.
1) az ötödik paraméter, a count, azt adja meg, hogy hányszor kell lecserélni a karaktert. Jelen esetben csak egyszer akarunk cserélni. Tehát ha a szavunk ALMA és 1 az értéke a paraméternek, akkor csak az első A betűt cseréli le, mert az első A betű az ALMA szóban. Viszont ha 2 az értéke, akkor mindkét A betűt kicseréli az ALMA szóban.
Ez eddig könnyebb volt, viszont nézzük meg mi van ha a negyedik illetve a kilencedik karaktert akarjuk lecserélni. Az egyszerűség kedvéért csak a 4-et mutatom meg, mivel ugyanaz a metódus lesz mindkettő.
Cells(i, oszlop).Value = Left(Cells(i, oszlop).Value, 3) & replace(Cells(i, oszlop).Value, Mid(Cells(i, oszlop).Value, 4, 1), karakter, 4, 1)Három fontos változás van.
A replace() függvény start paramétere 4 lesz, azaz a 4. karaktertől kezdődően akarjuk lecserélni egyszer (count paraméter 1 marad) a cellánk 4. karakterét.
Ezt a negyedik karakter ismét egy beápített függvénnyel határozzuk meg, csak most nem a Left(), hanem a Mid() fügvénnyel. Mid(Cells(i, oszlop).Value, 4, 1)
Viszont mivel a start paraméterünkbe ha 4-et írunk akkor levágja az első 3 karaktert, így szükséges manuálisan hozzáadni a cellánk string-jének első három karakterét. Azaz & jellel az a replace() fügvényünket összefűzzünk az cellánk első három karakterével. ( a 9. karakter cserénél az első 8 karakterrel)
Left(Cells(i, oszlop).Value, 3) & replace(…
Megoldás
Íme a teljes kód:
Sub replace_funkcio()
' Első lépésként felvesszük a változóinkat
'Milyen karakterre akarjuk lecserélni
Dim karakter As String
karakter = "*"
'Melyik oszlopban vannak a string-jeink
Dim oszlop As String
oszlop = "A"
'Meghatározzuk a táblázatunk utolsó sorát, azaz eddig fog lefutni a ciklusunk majd
Dim utolso_sor As Integer
utolso_sor = Cells(Rows.Count, oszlop).End(xlUp).Row
'Végig futunk egy for ciklussal a kívánt oszlopon.
For i = 1 To utolso_sor
'Ellenőrizzük, hogy valóban mindenhol 9 karakteres szöveg van e a cellában. Ha nem, akkor kisárgázzuk a cellát az "else" ágban.
If Len(Cells(i, oszlop).Value) = 9 Then
'első karaketert cseréljük ki
Cells(i, oszlop).Value = replace(Cells(i, oszlop).Value, Left(Cells(i, oszlop).Value, 1), karakter, 1, 1)
'negyedik karaktert cseréljük ki
Cells(i, oszlop).Value = Left(Cells(i, oszlop).Value, 3) & replace(Cells(i, oszlop).Value, Mid(Cells(i, oszlop).Value, 4, 1), karakter, 4, 1)
'kilencedik karaktert cseréljük ki
Cells(i, oszlop).Value = Left(Cells(i, oszlop).Value, 8) & replace(Cells(i, oszlop).Value, Mid(Cells(i, oszlop).Value, 9, 1), karakter, 9, 1)
Else
Cells(i, oszlop).Interior.ColorIndex = 6
End If
Next
End SubA feladatot persze több másik módon is meglehet oldani.