Zoekinstellingen opvragen

  • 01/06/2008
Voorbeeldbestanden bij dit artikel:
  • Zoekinstellingen opvragen
  • Zoekinstellingen opvragen

    • Update

    Ik heb 2 fouten in de code verbeterd. Dank aan Patrick Platteau om dit via e-mail te laten weten. Nu werkt het wel helemaal.

    Inleiding

    In VBA kan je via de Find methode gegevens zoeken in een blad of bestand, net hetzelfde als je doet via het menu Bewerken > Zoeken (of Ctrl + F). Bijvoorbeeld: je zoekt op het woord barbecue in een werkblad. Je hebt daarbij een aantal extra mogelijkheden:

    • Zoeken in formules, resultaten van formules, of opmerkingen;
    • Zoeken op een volledige cel of slechts een stuk daarvan;
    • Rij per rij zoeken of kolom per kolom;
    • Hoofdlettergevoelig zoeken of niet;
    • In het blad zoeken of het hele bestand (vanaf Excel 2002)
    • Een bepaalde opmaak zoeken (vanaf Excel 2002)

    Je kan die keuzes zelf maken in een werkblad, en je kan ze dus ook meegeven in de VBA-code. Maar Excel onthoudt wel de instellingen van de vorige zoekopdracht als je zelf niets specifieert. Dus zoek je eerst in volledige cellen, en dan doe je een Find in VBA zonder dit uit te schakelen, dan zoek je nog steeds in volledige cellen. Om zeker te zijn, moet je m.a.w. telkens de belangrijke parameters mee opgeven bij een Find commando in VBA.

    Voorbeeld

    Een voorbeeld is als volgt: 

    Sub ZoekenInExcel()
    
    Dim rGevondenCel As Range

    Set rGevondenCel = Sheets("Blad1").Cells.Find(what:="barbecue", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

    If rGevondenCel Is Nothing Then
        MsgBox "Dit werd niet gevonden.", vbCritical
    Else
        MsgBox "Dit werd gevonden in cel " & rGevondenCel.Address(0, 0) & ".", vbInformation
    End If


    End Sub

    Hier zoek je in blad Blad1 naar het woord barbecue in de volledige cel. BarBeCue kan ook gevonden worden omdat het niet hoofdlettergevoelig is. Noot: in de code hoef je de namen van de argumenten (what, lookin, …) niet allemaal in te typen. Maar dan moeten de argumenten wel in de juiste volgorde staan (zoals in de helpfiles) en moet je overgeslagen argumenten ook met een komma afbakenen.

    Kom je na een Find terug in Excel zelf, of doe je nog een Find in VBA zonder de parameters opnieuw mee te geven, dan zoek je automatisch terug in volledige cellen, hoofdletterongevoelig, en in waarden. Gevolg is dat je dat telkens zelf moet herzetten. Er blijkt trouwens geen enkele manier te zijn om via VBA op te vragen wat de "huidige" instellingen zijn. Regelmatig wordt er dan ook op forums gevraagd of dit te doen is. Niet rechtstreeks, maar ik heb nu een omweg geschreven.

    Basisidee

    De idee achter de code is, dat we kunnen achterhalen wat de huidige keuzes zijn, door een reeks van nieuwe zoekopdrachten te doen. Hier leg ik omstandig uit wat mijn code doet - neem ze er even bij:

    1. Zet ergens een uniek woord in een opmerking in een cel (in de code is dat de constante sUniqueWord die je zelf kan aanpassen. Strictu sensu hoeft het niet uniek te zijn, maar dat is iets "veiliger".) Als je via een zoekfunctie die cel kan vinden, weet je dat je opmerkingen (comments) aan het zoeken bent, en niet in waarden of formules. In de Find specifieer je niets, behalve waar en waarop VBA moet zoeken. De andere parameters zet je NIET in de code, dus dan worden de huidige parameters genomen door VBA.
    2. Heeft de code het woord niet gevonden in de opmerking, dan zoek je dus al niet in opmerkingen. Maar zoek je dan in waarden of formules? We deleten de opmerking en schrijven het unieke woord weg in een cel. Hier is nu een kleine truc nodig. Zet het unieke woord in een cel (niet in een opmerking), en verberg die kolom. Wie op formules zoekt, zal die cel vinden. Wie op waarden zoekt, niet. Op die manier kunnen we die 2 gevallen onderscheiden. Het Find statement in VBA dus exact hetzelfde is als in stap 1. Merk op dat hetgeen je zoekt helemaal hetzelfde moet zijn als het woord dat je wegschrijft. De huidige instellingen kunnen nl. zijn dat er hoogdlettergevoelig gezocht wordt, of slechts in een deel van een cel. Dat kan je dus beide opvangen door dit zo aan te pakken.
    3. Na deze 2 stappen weet je waarin je zoekt (opmerkingen, waarden of formules). De andere stappen zijn wat gemakkelijker. Om te weten of je op hele cellen zoekt of slechts een stuk, kan je bv. zoeken het eerste karakter van het unieke woord (neem een uniek woord in de code dat ten minste 1 karakter lang is). Als een Find opdracht dat karakter kan vinden binnen het bereik dat enkel het unieke woord bevat, dan weet je dat je zoekt op delen van cellen. Want zou je op hele cellen zoeken, dan zou dat karakter niet gevonden kunnen worden.
    4. Ten slotte willen we weten of we rij per rij zoeken, of kolom per kolom. Al is dit een instelling die maar zelden gebruikt wordt. Dit heb ik zo gedaan. Ik maak mijn zoekbereik als een bereik van 2 bij 2 cellen:

    5. Je zet het te zoeken woord in 2 verschillende cellen. Ik doe een zoekopdracht en vraag dan met de Address eigenschap welke cel we gevonden hebben. Zo kunnen we een onderscheid maken tussen de cel rechtsboven (die wordt gevonden als er rij per rij gezocht wordt), en de cel linksonder (die wordt gevonden als er kolom per kolom gezocht wordt). Je vergelijkt het bekomen adres met het adres van de cel rechtsboven (rij 1, kolom 2 van het zoekbereik). Vandaar...
    6. Dit was het. Nu we weten hoe je deze instellingen kan opvragen (en terug zetten na de zoekopdrachten die we doen), vraag je je wellicht af hoe het met die 3 andere instellingen zit:
      • Hoofdlettergevoelig zoeken of niet? Dit zal je nooit eenduidig kunnen achterhalen. De reden is dat elke Find opdracht in VBA dit vinkje uitschakelt; ook als je zelf niets specifieert voor deze instelling.
      • Zoeken in een blad of heel bestand (vanaf Excel 2002): dit gaat ook niet uitgevraagd kunnen worden, om dezelfde reden als hierboven. Telkens draait Excel de instelling terug naar zoeken binnen een werkblad, of je nu zelfs iets ingeeft of niet daaromtrent.
      • Zoeken naar bepaalde opmaak (vanaf Excel 2002): dit kan WEL. Je kan in de code aan het begin een stuk tussenvoegen dat de te zoeken opmaak uitvraagt en in variabelen bijhoudt. Bij wijze van voorbeeld: hier is code om het lettertype in de te zoeken opmaak opvraagt: 
        Sub ZoekopmaakWelkLettertype()
        
    Dim sLettertype As String

    On Error Resume Next
    sLettertype = Application.FindFormat.Font.Name
    On Error GoTo 0

    If Len(sLettertype) Then MsgBox sLettertype


        End Sub
        Dan volgt er de huidige code waarbij de zoekopmaak gewist wordt, en tot slot een stuk code die de zoekopmaak weer instelt. Je wist onderweg de opmaak omdat je anders het unieke woord mogelijk niet zal vinden (wij passen nl. geen enkele opmaak toe in de code).
      • Zoeken naar wat men laatst gezocht heeft (dus de tekst zelf), is standaard niet op te vragen, maar misschien wel met SendKeys en/of API calls. Dit heb ik niet geprobeerd.

    Zo, dit artikel is alweer een stuk langer dan eerst gedacht. Hopelijk is het een beetje duidelijk. Veel succes ermee.




    Homepage

    Rubriek onderdelen

    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links