Strings VBA

In VBA kun je met verschillende soorten data types te maken krijgen. Het ene datatype (bijvoorbeeld datums) is vervelender om mee te werken dan de ander. Het datatype dat ik nu ga behandelen vind ik zelf aardig eenvoudig en prettig mee te werken. Het gaat om het datatype ‘string‘ wat eigenlijk gewoon ‘tekst‘ betekent. Er zijn echter wel een heleboel string functies waarmee je tekst in VBA kunt bewerken. Ik zet ze hier voor jou op een rijtje.

CONCATENATIE

De concatenatie is eigenlijk niet echt een functie, maar wel een hele belangrijke bewerking van een string die je in de praktijk zeker vaak nodig gaat hebben. Concatenatie is het combineren van twee of meer strings wordt concatenatie genoemd. In VBA wordt de & operator of het + teken gebruikt voor concatenatie.

Dim voorNaam As String
Dim achterNaam As String
Dim volledigeNaam As String

voorNaam = "Jan"
achterNaam = "Jansen"
volledigeNaam = voorNaam & " " & achterNaam 'Resulteert in "Jan Jansen"

LEFT & RIGHT

Met de LEFT & RIGHT functie kun je zoals de naam al aangeeft het linker of rechter deel van een stuk tekst vastleggen. In de eerste parameter voor de komma geef je de tekst aan waar het om gaat. De 2e parameter is dan het aantal tekens dat je wilt gebruiken van deze tekst. De uitkomst van het onderstaande voorbeeld wordt dan ‘dit’

Left("ditDatZusZo",3)

 

MID

Deze functie lijkt erg op LEFT en RIGHT, maar heeft meer mogelijkheden. Bij MID kun je elk deel van een string vastleggen, zoals de naam al zegt dus ook het middenstuk. Dit komt doordat je bij deze functie een extra parameter hebt. In de eerste parameter voor de komma geef je wederom de tekst aan waar het om gaat. De 2e parameter geeft de positie aan van de eerste letter die je wilt pakken. De 3e parameter geeft dan de lengte aan van het deel dat je wilt pakken. De uitkomst van het onderstaande voorbeeld wordt dan ‘Super’.

Mid("HoiSuperWebsite", 4, 5)

Lengte bepalen met de VBA string functie LEN

Met de LEN functie kun je het aantal karakters uit een bepaalde string ophalen. Dit kan handig zijn in het geval dat je een loop wilt opzetten waarbij je wilt kijken naar alle karakters in een woord. De LEN functie geeft een INTEGER terug. Als je de onderstaande code uitvoert zul je zien dat de waarde van de variabele LengteWoord gelijk is aan 3.

Sub DeLenfunctie()

    LengteWoord = Len("Hoi")
    MsgBox LengteWoord

End Sub

Door de MID functie en een LEN functie te combineren met een For Next loop kun je een loop uitvoeren over alle karakters van een bepaalde string. Je bepaalt dan eerst het aantal iteraties van de loop door de Lengte van de string op te halen met de LEN functie. Vervolgens gebruik je de MID functie binnen de loop waarbij je als start parameter de iteratie letter van de for loop gebruikt en een lengte van één invult. Deze loop zou bijvoorbeeld handig kunnen zijn als je bepaalde tekens uit een stuk tekst wilt verwijderen.

Vervangen met de VBA string functie REPLACE

Met de REPLACE functie kun je binnen een string een bepaald onderdeel vervangen met een ander stuk tekst. Binnen een string kun je aangeven welk deel van de tekst je wilt vervangen met een ander stuk tekst. Bij deze functie heb je ook 3 input parameters:

1. De tekst die je wilt gebruiken voor de replace actie
2. Het deel dat je wilt vervangen
3. De nieuwe tekst

Als je de onderstaande code zou afspelen zou de tekst “leuke website” worden.

Bericht = Replace("saaie website", "saaie", "leuke")

Ucase & Lcase

Met deze twee functies kun je stukken tekst omtoveren tot alleen grote of kleine letters. Ucase staat dan voor Uppercase dat hoofdletters betekent. Lcase staat voor Lowercase dat kleine letters betekent. Nu hoor ik je denken…in wat voor situatie kan ik dit nodig hebben? Bijvoorbeeld als je een bepaalde tekst zoekt binnen een range, maar als je niet wilt dat onverwachts gebruik van hoofdletters of kleine letters je in de weg zit. Ik zal dit verduidelijken aan de hand van een voorbeeld. Stel je gebruikt een For next loop om een bepaalde replace actie aan te brengen op een hele serie aan rijen. Onverwachts gebruik van een hoofdletter zorgt er dan voor dat de replace actie niet uitgevoerd wordt. Dit los je op door de tekst op de rij op te halen met lcase ervoor.  Dit vergelijk je dan door in de 2e parameter van de REPLACE functie de functie LCASE voor het deel dat je wilt vervangen te plaatsen. Stel op 1 van de 100 rijen in kolom A stond dezelfde tekst als in het voorgaande voorbeeld maar dan begint het woord ‘Saaie’ met een hoofdletter. Door de Lcase functie te gebruiken heeft je replace functie daar geen last meer van.

For i = 1 To 100
    tekst = Replace(LCase(ThisWorkbook.Sheets("Blad1").Range("A" & i)), "saaie", "leuke")
Next i

InStr functie

Met de INSTR functie kun je vaststellen of er een bepaalde sub string of karakter zich bevindt in een string. Deze string geeft altijd een integer terug. Als de sub string of het karakter is gevonden dan krijg je de locatie van het resultaat terug. Als er geen match is dan zal de INSTR functie het getal o teruggeven.

De eerste parameter ‘Start’ die je in de afbeelding hierboven ziet staan is optioneel. Je kunt hier meegeven als hij niet vanaf het eerste karakter in de string moet gaan zoeken. Is dit wel het geval dan kun je in de eerste parameter gelijk de string aangeven waarin gezocht moet worden. Vervolgens geef je aan waar hij naar moet gaan zoeken. Als laatste optie kun je nog meegeven hoe de vergelijking gedaan moet worden. Ook deze optie is optioneel. Als je niks meegeeft voert hij standaard een tekst vergelijking uit (vbTextCompare).

Stel je speelt de onderstaande code af. Je message box zal nu het getal drie laten zien. Dit komt omdat de letter ‘i’ (waar je naar zoekt) de derde letter is in het woord vliegtuig. 

Sub InstrGebruiken()

    MsgBox InStr("vliegtuig", "i")

End Sub

Mocht je nu de eerste parameter toepassen in het bovenstaande voorbeeld en je geeft aan dat hij pas op plek 4 moet beginnen met zoeken. De message box slaat dan de eerste 3 getallen in het woord vliegtuig over en vindt dan de letter ‘i’ als het achtste karakter van het woord.

Sub InstrGebruiken()

    MsgBox InStr(4, "vliegtuig", "i")

End Sub

InStrRev

De InStrRev functie lijkt zoals de naam al doet vermoeden erg op de InStr functie. Het enige verschil is dat hij nu vanaf rechts naar links gaat zoeken i.p.v. van links naar rechts. De positie die je terug krijgt indien hij hem vindt is echter wel vanaf links geteld. Ik zal dit weer verduidelijken aan de hand van een  voorbeeld. 

Sub stringTesten()

    positie1 = InStr("bladiebla", "bla")
    positie2 = InStrRev("bladiebla", "bla")

End Sub

In het bovenstaande voorbeeld zal de waarde van ‘positie1’ die je terug krijgt 1 zijn. De waarde van positie 2 is 7.

Je kunt net als bij InStr als 3e parameter weer een positie meegeven die gebruikt wordt als startpunt voor de zoekactie. Bij deze functie werkt het dan net andersom. als je als 3e parameter het cijfer 4 meegeeft dan begint hij bij positie 5 vanaf de rechterkant naar links te zoeken naar het zoekwoord.

Sub stringTesten()

    positie1 = InStr("bladiebla", "bla")
    positie2 = InStrRev("bladiebla", "bla", 4)

End Sub

In het bovenstaande voorbeeld krijgt zowel ‘positie1’ als ‘positie2’ een waarde mee van 1.

JOIN

Met behulp van de JOIN functie kun je eenvoudig meerdere waardes uit een array halen en deze achter elkaar in een string plakken. Je kunt er dan nog voor kiezen om hier een scheidingsteken tussen te plaatsen. Indien je hier niet voor kiest plaatst de functie een spatie.

Sub stringTesten()
    
    Dim arrWebsite(2) As String
    
    arrWebsite(0) = "macro"
    arrWebsite(1) = "excel"
    arrWebsite(2) = ".nl"

    samenvoeging = Join(arrWebsite)

End Sub

De waarde van samenvoeging is na het afspelen van de bovenstaande macro “macro excel .nl”. Mocht je deze spaties willen verwijderen dan kan dat eenvoudig met de hierboven beschreven replace functie.

SPLIT

Deze functie doet het tegenovergestelde van de hierboven beschreven JOIN functie. Met deze functie kun je namelijk de inhoud van een string variabele opnemen in een array. De eerste parameter moet de op te splitsen variabele bevatten en de tweede parameter het scheidingsteken.

Sub stringTesten()
    
    Dim arrUitsplitsing() As String
    
    TeSplitsenVariabele = "1;2;3;4;5;6;7;8;9;10"

    arrUitsplitsing = Split(TeSplitsenVariabele, ";")

End Sub

Spaties verwijderen met de VBA string functie LTrim & RTrim

Met behulp van deze functies kun je spaties aan het begin en einde van een string verwijderen. Het maakt hierbij niet uit of dit één of meerdere spaties betreft. Hij verwijdert alles tot aan de eerste letter van de string.

In het onderstaande voorbeeld verwijdert de LTrim functie alle spaties aan de linkerkant van de variabele ‘TeSplitsenVariabele’

Sub stringTesten()
        
    TeSplitsenVariabele = "     1;2;3;4;5;6;7;8;9;10 "

    TeSplitsenVariabele = LTrim(TeSplitsenVariabele)

End Sub

Spaties toevoegen in een string met de functie Space

Deze functie doet het tegenovergestelde als de TRIM functie. Met de SPACE functie voeg je een aantal spaties toe aan een stuk tekst. Je hebt bij deze functie één parameter en dat is het aantal spaties dat je wilt invoegen.

Sub SpatiesToevoegen()

    MacroExcel = "een" & Space(1) & "handige" & Space(1) & "website"
    MsgBox MacroExcel
    
End Sub

Ik moet eerlijk bekennen dat ik van deze space functie niet echt de toegevoegde waarde zie aangezien je de spaties ook direct kunt verwerken in de tekst. De onderstaande code geeft immers hetzelfde resultaat als de bovenstaande code.

Sub SpatiesToevoegen()

    MacroExcel = "een " & "handige " & "website"
    MsgBox MacroExcel
    
End Sub

String data vergelijken met de functie StrCompare

Er is een functie waarmee je 2 stukken tekst met elkaar kunt vergelijken. De output van deze functie kent dan de volgende uitkomsten:

1. Resultaat -1 (string1 < string 2)
2. Resultaat 0 (string 1 = string 2)
3. Resultaat 1 (string 1 > string 2)

Je kunt hierbij als derde parameter nog meegeven op welke manier de twee strings vergeleken moeten worden. Je hebt hier de keuze uit de volgende drie opties:

1. vbBinaryCompare (Vergelijkt de 2 strings en is hierbij gevoelig voor kleine letters/hoofdletters)
2. vbTextCompare (Vergelijkt de 2 strings en is hierbij niet gevoelig voor kleine letters/hoofdletters)
3. vbDatabaseCompare ( Kun je alleen gebruiken i.c.m. Microsoft Acces. De strings worden vergeleken op basis van informatie in je database)

De default waarde voor een vergelijking is altijd vbBinaryCompare. Houd dus rekening met het feit dat verschillen in kleine letters vs hoofdletters invloed hebben op de uitkomst.

De functie controleert en ‘waardeert’ de tekst door te kijken naar het eerste karakter dat hij tegenkomt in een string. Zijn deze tekens gelijk dan kijkt hij naar het volgende teken. Dat gaat door totdat de tekens stoppen of totdat de tekens niet meer gelijk zijn aan elkaar. In het geval dat de tekens stoppen dan is de string dus in zijn geheel gelijk en geeft hij als resultaat ‘0’. Hij vergelijkt de tekens adhv de locatie in het alfabet. Getallen zijn in deze functie minder dan tekst. Zie hieronder een aantal voorbeelden van uitkomsten van deze functie (vergelijking met vbBinaryCompare):

• MsgBox (StrComp(“a”, “A”)) geeft als resultaat ‘1’ want A > a
• MsgBox (StrComp(“a”, “b”)) geeft als resultaat ‘-1’  want a < b
• MsgBox (StrComp(“a”, “ab”)) geeft als resultaat ‘-1’ want a < ab
• MsgBox (StrComp(“1”, “11”)) geeft als resultaat ‘-1’ want 1 < 11
• MsgBox (StrComp(“19”, “11”)) geeft als resultaat ‘1’ want 19 > 11
• MsgBox (StrComp(“aaaaaaa”, “c”)) geeft als resultaat ‘-1’ want aaaaaaa < c

Tekst omdraaien met de string functie StrReverse

Met de string functie StrReverse kun je de karakters in een bepaalde tekst omdraaien van achter naar voren. Hele eenvoudige functie. De enige input is de tekst die omgedraaid moet worden. Wat zal de uitkomst van de onderstaande msgbox zijn?

Sub TekstOmdraaien()

    rating = "gidleweg"
    
    rating = StrReverse(rating)
    MsgBox "MacroExcel is " & rating
End Sub

Andere typen data converteren naar String

Mocht je een variabele van een ander type dan string willen converteren naar het String datatype dan kun je hiervoor de functies Str en Cstr gebruiken. Ik zal je een voorbeeld geven van het effect dat dit kan hebben op de toewijzing van een waarde aan een variabele. In het onderstaande voorbeeld is de uitkomst van de msgbox het getal drie. Hij telt immers getaléén en getal twee op. Deze bezitten ook de waardes één en twee. VBA ziet deze variabelen zonder dat ze gedeclareerd zijn als een integer (getal zonder decimalen). 

Sub ConverteerDataNaarString()

    getaléén = 1
    getaltwee = 2
    
    getalDrie = getaléén + getaltwee
    MsgBox getalDrie
End Sub

Als we bij de waarde toekenning nu de Str functie meegeven dan verandert de inhoud van beide variabelen naar een string (tekst). Zoals je weet kun je tekst niet optellen. Dit kan alleen met getallen. De uitkomst van de onderstaande sub zal dus “1 2” zijn. Als je namelijk gaat optellen met strings dan plakt hij de waardes achter elkaar in plaats van dat hij ze optelt.

Sub ConverteerDataNaarString()

    getaléén = Str(1)
    getaltwee = Str(2)
    
    getalDrie = getaléén + getaltwee
    MsgBox getalDrie
End Sub

Overige string functies VBA

In dit artikel hebben wij de meest nuttige VBA string functies voor je behandeld. Indien je een volledige lijst met string functies wilt bekijken kun je terecht op de Microsoft website.

Indien je nog vragen hebt met betrekking tot string functies. Stel ze in de reacties!