1. Mi az az InStr funkció?

Első körben még mindenféle VBA programozás nélkül, amikor az Excelünk egyik cellájában szereplő szövegben keresünk egy karakter vagy karakterlánc kezdő pozícióját, akkor arra SZÖVEG.KERES függvényt alkalmazzuk.

=SZÖVEG.KERES("t";"Ez egy teszt")

A fenti képlet értéke 8 lesz, mert a "t" betű a 8. karakter az "Ez egy teszt" szövegben.

Azonban Excel VBA-ban is van egy funkció, amit a makrónk írásakor tudunk használni a programunkban, ami nagyon hasonlít a SZÖVEG.KERES függvényre. Ez pedig az InStr funkció. Ennek is a célja, hogy megadja egy keresett karakter vagy karakterlánc pozícióját egy adott szövegben.

Nézzünk meg egy egyszerű példát:

Sub makro()
     Dim teszt As Integer
     teszt = InStr("Ez egy teszt", "t") 
     MsgBox teszt
End Sub

Ennek a makrónak a futtatásakor felugrik egy párbeszédablak, amelyben a 8-as szám fog szerepelni, mivel a "t" karakter a keresett szövegben a 8. helyen van.

2. A VBA InStr funkció szintaxisa

Az InStr funkció a következő képpen épül fel:

InStr( [Start], String1, String2, [Compare] )

[Start] – ez egy opciónális mező, ha használjuk, akkor egy egész számnak kell ide kerülnie. Ez a keresés kezdő pozícióját adja meg. Azaz ha a szövegünk legelejétől akarunk keresni, akkor az „1”-et kell beírniunk, vagy üresen hagyjuk.
- Míg ebben az esetben InStr(1, „Ez egy teszt”, „t”) az eredmény 8 lesz,
- úgy itt InStr(9, „Ez egy teszt”, „t”) 12.
String1 – kötelező mező, ez a fő szövegünk, amiben keressük a karakterünk pozícióját.
String2 – kötelező mező, ide kerül a keresett karakterünk, vagy karakterláncunk.
[Compare] – opciónális mező, az alábbi három (négy) lehetőségből tudsz választani:
- vbUseCompareOption – a default beállítást használja az összehasonlításban. A képletben helyettesíthetjük „-1”-gyel is. (Valószínűleg soha nem fogod használni.)
- vbBinaryCompare – ez már hasznosabb lehet, ennek segítségével különbséget tudunk tenni kis és nagy betűk között. A képletben helyettesíthetjük „0”-val is.
  - Míg ebben az esetben InStr(1, „Ez egy teszt”, „t”,0) értéke 8 lesz,
  - úgy ennél InStr(1, „Ez egy teszt”, „T”,0) az értéke 0 lesz, mivel nincsen a fő sövegünkben nagy „T” betű.
- vbTextCompare – ezt a képletben helyettesíthetjük az „1”-es számmal is. Ezt akkor használhatjuk, ha nem akarunk különbséget tenni kis és nagy betűk között.
- vbDatabaseCompare – igazából ezt csak Microsoft Access-ben használható csak, az adatbázisban szereplő információk alapján tesz összehasonlítást

Ha az alábbiak közül valamelyik igaz, akkor az InStr funkció a következő értéked adja vissza

Ha igaz:	Akkor a visszakapott érték:
String1 zéró hosszúságú	0
String1 értéke null	null
String2 zéró hosszúságú	start
String2 értéke null	null
String2 nem található	0
String2 a String1-en belül található	A pozicíó,ahol a találat van
start > String2	0

3. Gyakorlati példák az InStr funkció Excel-ben történő alkalmazására

1. Példa

Keressük meg hányadik helyen vannak a következő karakterek, karakterláncok a lenti szövegben és írjuk ki ezeket egy párbeszédablakba. Ügyeljünk a kis és nagy betűkre is!

Keresett karakterek: „a”, „A”, „b”, „B”, „Excel”, „makró”

Fő szöveg: „Excel VBA-ban jó ha ismerem az InStr funkció használatát”

Megoldás:

Sub makro()
     Dim szoveg As String
     szoveg = "Excel VBA-ban jó ha ismerem az InStr funkció használatát"
     MsgBox InStr(1, szoveg, "a", 0)
     MsgBox InStr(1, szoveg, "A", 0)
     MsgBox InStr(1, szoveg, "b", 0)
     MsgBox InStr(1, szoveg, "B", 0)
     MsgBox InStr(1, szoveg, "Excel", 0)
     MsgBox InStr(1, szoveg, "makró", 0)
End Sub

2. Példa

Az „A1” cellánk értéke legyen „Gipsz Jakab”. Keressük meg benne, hogy hányadik helyen van a „p” betű. Ügyeljünk rá, hogy ne a „Gipsz Jakab” legyen szövegként a képletünkben, hanem a cellára hivatkozzunk!

Megoldás:

Sub makro()
     Dim szoveg As String
     szoveg = Cells(1, 1).Value
     MsgBox InStr(szoveg, "p")
End Sub

3. Példa a valós életből

Most pedig álljon itt egy nehezebb gyakorlati példa, amit a valóságban is kellett alkalmaznom:

Feladat:

Egy forgalmas website háttérrendszeréből exportálunk ki rendszeresen egy adatbázist, amiben az adott heti érdeklődők kapcsolati adatai vannak. Ebből készítünk statisztikát hetente. A website-on több sales funnel is van, folyamatosan változik az oldal, folyamatosan dolgoznak rajta a fejlesztők. Éppen ezért közös megegyezés, ha egy fejlesztő vagy tesztelő tölt ki egy űrlapot az oldalon, akkor a vezeték neve „Teszt” lesz, a keresztneve pedig a saját keresztneve.

Azonban mivel sok fejlesztő van és ők is cserélődnek, így az adatbázis minden cellájában előfordulhat a „teszt” szó különböző verziója: „teszt”, „TESZT”, „Tesztelek”, „Teszt Ádám”, „Béla tesztel”.

Feladatunk, hogy minden ilyet kiszűrjünk, és töröljük ezeket a sorokat a táblázatunkból!

Töltsük le a minta adatbázisunkat!

Eszközkészlet:

A feladat megoldásához ismernünk kell az alábbiakat VBA-ban:

Cellák értékének meghatározása, főként a „Cells” kifejezést fogjuk használni
If elágazás
For ciklus alkalmazása. Ráadásul így három For ciklus is szerepelni fog egymásban
Tömbök használata

Megoldás:

Sub makro()

'Meghatározzuk a táblázat utolsó sorát és oszlopát, amely területen dolgozunk majd
     Dim utolso_sor As Integer
     Dim utolso_oszlop As Integer
     utolso_sor = Cells(Rows.Count, "A").End(xlUp).Row
     utolso_oszlop = Cells(1, Columns.Count).End(xlToLeft).Column

'Létrehozunk egy tömböt, amibe felvisszük az összes számunka irreleváns kifejezést, ami a "teszt"-tel kapcsolatos
     Dim tesztnevek As Variant
     tesztnevek = Array("teszt", "Teszt", "TESZT", "testing")

'Egy változóban tároljuk, hogy hány elem is van a tömbünkben, így később ha bővítjük a nevek listáját, akkor automatikusan le tudja követni a makrónk.
     Dim tesztnevek_szama As Integer
     tesztnevek_szama = UBound(tesztnevek, 1) - LBound(tesztnevek, 1) + 1

'Töröljük azokat a sorokat, amelyekben valamelyik "teszt" nevünk szerepel a listánkról.
     For k = 0 To tesztnevek_szama - 1
          For j = 1 To utolso_oszlop
               For i = utolso_sor To 1 Step -1
                    If InStr(1, Cells(i, j).Value, tesztnevek(k)) Then
                       Cells(i, "A").EntireRow.Delete
                    End If
               Next
          Next
      Next
End Sub

Bízok benne sikerült jobban megértened az InStr funkció alkalmazását Excel VBA-ban. Ha segített a cikk, nyomj egy értékelést vagy írj bátran egy kommentet! Ha értesülni szeretnél a legújabb cikkekről, akkor iratkozz fel a hírlevélre!

Mennyire találtad hasznosnak ezt cikket?

[Összes szavazat: 7 Átlag értékelés: 5]