Hoe kan ik zoeken en vervangen in Excel VBA

Bij het bewerken van grote hoeveelheden data, bijvoorbeeld bij de conversie van data tussen verschillende databases, kan het zoeken en vervangen een erg handig hulpmiddel zijn. In dit blog besteed ik dan ook aandacht aan een zoek en vervang functie voor Excel waarmee we eenvoudig data kunnen aanpassen en gereed kunnen maken voor conversie. Soms is het noodzakelijk om dit in verschillende stappen te doen. Er zijn verschillende mogelijkheden om stringwaarden te zoeken en te vervangen. Ik beperk me in dit blog tot een betrekkelijk eenvoudig uit te leggen script.

In het voorbeeld heb ik een lijst met links waar de waarde 2013 vervangen moet worden door 2015. Maak nu eerst in een module de functie ZoekEnVervang zoals hieronder beschreven. Als je niet weet hoe je een module en functie toevoegt lees dan eerst het blog hoe maak ik een eigen functie in Excel.

Public Function ZoekEnVervang(strTotaal, strOld As String, strNew As String)
    'Vervangt in een veld de gezochte waarde door de opgegeven waarde.
    Dim intStart, intLTot, intLOld As Integer
    intLOld = Strings.Len(strOld)
    intLTot = Strings.Len(strTotaal)
    intStart = InStr(1, strTotaal, strOld, vbTextCompare)
    If intStart <> 0 Then
        strTotaal = Strings.Left(strTotaal, intStart - 1) & strNew & Strings.Right(strTotaal, (intLTot - (intStart + intLOld - 1)))
    End If
    ZoekEnVervang = strTotaal
    Debug.Print strTotaal
End Function

De functie uitgelegd

De variabele intLOld geven we als waarde de lengte van de huidige string mee. In ons voorbeeld is dat 4 tekens immers 2013.

De variabele intLTot geven we als waarde de lengte van de totale string waarin moet worden gezocht mee.

De variabele intStart geeft de eerste positie aan van de te zoeken waarde in de totaal string. Dus we gaan zoeken naar de eerste positie in de string [http://demo.com/demo-content/magazine/uploads/2013/02/tv_1.jpg] waar de waarde 2013 voorkomt. In dit geval is dat positie 47.

Nu lussen we de totaal string net zo lang totdat alle waarden 2013 zijn vervangen door de nieuwe waarde.