Ik kreeg laatst de vraag: hoe kan ik bij onderwerp maken dat je in het invoervak van de keuzelijst de beginletters van het te zoeken woord kan intikken en verder kan scrollen in keuzelijst?

Standaard moet je bij gegevensvalidatie met een keuzelijst met de muis scrollen en de gewenste waarde zoeken:

Een auto complete / autofill functionaliteit die op basis van een letter de keuze mogelijkheden geeft zit eigenlijk niet in de lijst validatie van Excel, ik ben eens gaan zoeken en kwam op de volgende oplossing (zie voorbeeldbestand)

Stap 1:

Zet op blad2 een range met bijvoorbeeld een aantal plaatsnamen:

Stap 2

Kies tabblad Formules - Namen beheren - Nieuw - Geef naam MyList en bij 'verwijst naar':

=VERSCHUIVING(Blad2!$A$1;VERGELIJKEN(Blad1!$A$1&"*";Blad2!$A$1:$A$300;0)-1;0;AANTALARG(Blad2!$A:$A))

Ga naar cel A1 op blad1 en kies tabblad Gegevens - Gegevensvalidatie - kies Toestaan lijst, Bron=MyList

De gebruiker moet nu een letter opgeven en nu zal, als hij op het pijltje klikt, een selectie getoond worden vanaf de gekozen letter (let op de bron range moet alfabetisch gesorteerd zijn):

Om af te dwingen dat de juiste waarden in een cel worden ingevoerd kun je in Excel het beste werken met gegevensvalidatie. Buiten de standaard controles op bijvoorbeeld hele getallen zoals HIER beschreven biedt Excel de mogelijkheid om met keuzelijstjes te werken waarbij de mogelijke waarden beperkt worden door een range / aantal cellen.

In Office 2010 maken we om dit te realiseren eerst de lijstjes aan die we later willen koppelen aan de validatie, het maakt op zich niet uit waar je deze lijstjes maakt maar meestal worden deze op een ander blad als het hoofdblad (blad1) gedaan, doe dit bijvoorbeeld op blad2:

Vervolgens klikken we op de cel waarin we de validatie willen toepassen, klikken op het lint op de tab 'Gegevens' en vervolgens op 'Gegevensvalidatie':

We kiezen in de drop down box voor 'Lijst':

Let op de vinkjes in 'lege cellen negeren' (bij een lange keuzelijst met lege cellen worden de lege waarden niet meegenomen)!

Klik op het werkblad icoontje in het veld 'Bron' en selecteer de range die je wilt weergeven, staat je lijstje op blad2 'blader' daar dan naar toe.

Download het voorbeeldbestand HIER

Het invullen van alle spelersnamen + KNVB code bij de wedstrijden van mijn zoons zorgde er altijd voor dat ik een kwartier eerder bij de club aanwezig moest zijn. Met het spelersboekje in de hand was ik dan een tijd bezig om alle spelersnamen en codes in te vullen. Nu gebruik ik een Excel bestand waar ik mijn spelers in heb staan, werkt perfect ik heb de etiketten bij de spelerspassen loop de commissiekamer binnen en voordat ik goedemorgen heb gezegd heb ik de 3 stickers op het formulier geplakt!

Op het tabblad spelersinfo geef je de spelersnamen, voorletters, KNVB code en (rug) nummers op. Op het tabblad sticker wordt de volgende formule gebruikt om de KNVB code te 'ontleden' zodat ieder karakter in een aparte cel komt:

=ALS(ISNB(VERT.ZOEKEN(3;SPELERSINFO!$A$6:$F$34;1;ONWAAR));"";DEEL(VERT.ZOEKEN(3;SPELERSINFO!$A$6:$F$34;4;ONWAAR);3;1))

De ISNB formule zorgt ervoor dat we geen #N/B fouten te zien krijgen.

Etiketten / stickervellen voor de KNVB wedstrijdformulier stickers zijn HIER te krijgen:

Voor een paar euro maanden plezier!

Download het Excel bestand voor de KNVB wedstrijdformulier stickers HIER

Veel mensen zijn op zoek naar een formule die een eerstvolgende werkdag aangeeft op basis van een datum en daarbij rekening houdt met weekends en feestdagen.

Ik kwam een voorbeeld tegen in VBA alleen gebruikte dit voorbeeld een Access database, ik heb het dus iets verbouwd zodat het een lijst met feestdagen data pakt die je ergens in je Excel parkeert en waar je naar kan verwijzen.

Download het voorbeeld bestand HIER

Om te beginnen voegen we een module toe aan een Excel werkblad, kies ALT-F11 - Invoegen - Module

Copy en Paste de volgende code:

Private Function SkipHolidays(feestRange As Range, _
 dtmTemp As Date, intIncrement As Integer) _
 As Date
    ' Weekends en feestdagen zoals aangegeven in de feestrange overslaan.
    Dim strCriteria As String
    Dim c As Range
    On Error GoTo HandleErr
    'Ga een datum omhoog of omlaag als de laatste datum van de maand een dag in het weekend is
    'Sla feestdagen over en ga net zo lang door totdat je een geldige werkdag krijgt.
    Do
        Do While IsWeekend(dtmTemp)
            dtmTemp = dtmTemp + intIncrement
        Loop
        If Not feestRange Is Nothing Then
            If Len(dtmTemp) > 0 Then
                For Each c In feestRange.Cells
                    If c.Value = dtmTemp Then
                        dtmTemp = dtmTemp + intIncrement
                    End If
                Next c
            End If
        End If
    Loop Until Not IsWeekend(dtmTemp)
ExitHere:
    SkipHolidays = dtmTemp
    Exit Function

HandleErr:
    ' Ergens een fout gevonden...
    Resume ExitHere
End Function

Function EerstVolgendeWerkdag(Optional dtmDate As Date = 0, _
 Optional feestRange As Range) As Date
    ' Geef de eerstvolgende werkdag na de opgegeven datum
    Dim dtmTemp As Date
     If dtmDate = 0 Then
        'Datum als parameter meegegeven? Deze gebruiken en anders huidige datum pakken
        dtmDate = Date
    End If
    EerstVolgendeWerkdag = SkipHolidays(feestRange, dtmDate + 1, 1)
End Function
Function EerstVorigeWerkdag(Optional dtmDate As Date = 0, _
 Optional feestRange As Range) As Date
    ' Geef de eerstvorige werkdag na de opgegeven datum
    Dim dtmTemp As Date
    If dtmDate = 0 Then
        'Datum als parameter meegegeven? Deze gebruiken en anders huidige datum pakken
        dtmDate = Date
    End If
    EerstVorigeWerkdag = SkipHolidays(feestRange, dtmDate - 1, -1)
End Function


Private Function IsWeekend(dtmTemp As Date) As Boolean
    ' If your weekends aren't Saturday (day 7)
    ' and Sunday (day 1), change this routine
    ' to return True for whatever days
    ' you DO treat as weekend days.
    Select Case Weekday(dtmTemp)
        Case vbSaturday, vbSunday
            IsWeekend = True
        Case Else
            IsWeekend = False
    End Select
End Function

De VBA code heeft 2 publieke Functies: EerstVolgendeWerkdag en EerstVorigeWerkdag (Excuseer de naam ;) ).

Private Functions kun je niet aanroepen vanuit Excel maar als er voor een Function 'Public' staat of er staat gewoon Function dan betekent dit dat je die functies / formules vanuit Excel aan kan roepen als iedere andere formule.

Het rekenwerk wordt gedaan in de formule SkipHolidays, we sturen een datum mee waarmee we willen rekenen en een range die verwijst naar een lijstje met feestdagen.
SkipHolidays kijkt of de eerstvolgende datum in het weekend valt, is dat het geval dan hoogt hij een tellertje op net zo lang tot de datum niet in het weekend valt (Do While IsWeekend(dtmTemp))

Daarna gaat hij o.b.v. de feestrange kijken of de datum die we nu hebben een feestdag is en hoogte de datum weer op als dat het geval is. De formule checkt nog een keer of we nu een datum hebben die in het weekend valt en hoogte deze weer op als dat het geval is.

Theoretisch gezien zou de datum die we nu weer over houden ook weer een feestdag kunnen zijn ;)

Verticaal zoeken gebruik je vaak bij externe tekst bestanden die je wilt koppelen aan een bestaand Excel bestand. Niets frustrerender als je denkt dat je alles goed hebt gedaan dan de #N/B formule bij verticaal.zoeken!
Vaak komt dit doordat de kolom uit het tekst bestand (vaak .csv) wat je wilt koppelen door Excel wordt gezien als tekst kolom. Dit zie je door het groene driehoekje linksboven in de cel.

'Gewoon' Verticaal zoeken lukt dan niet omdat Excel zogezegd 'het getal niet kan vinden in de kolom met teksten':

Er zijn een aantal manieren om de formule toch werkend te krijgen, we moeten dan ofwel de kolom met teksten in het zoekbereik omzetten naar getallen ofwel de formule zeggen dat we niet zoeken naar een getal maar naar een tekst.

1 - we selecteren de cellen met tekst waarden die we om willen zetten naar getal (met SHIFT ingedrukt) en klikken op het gele uitroepteken. Kies 'converteren naar getal'. Verticaal zoeken zal nu gewoon zijn werk doen.

2- we zetten een tijdelijke kolom naast de kolom met tekst waarden en zetten hier =WAARDE(A4), trekken deze formule door naar beneden en kiezen kopiëren. Dan gaan we op de kolom met tekst waarden staan, klikken met de rechtermuisknop en kiezen 'Plakken speciaal - Waarden'.

3 - Bij de laatste oplossing werken we andersom. We laten alle waarden intact en passen de verticaal.zoeken formule aan door er TEKST(C13;"#") in te zetten i.p.v. alleen C13. We zeggen nu eigenlijk: 'Zoek de tekst waarde van cel C13 in de kolom met tekstwaarden.

Voorbeeld:=VERT.ZOEKEN(TEKST(C13;"#");A13:B17;2;ONWAAR)

Download het voorbeeldbestand HIER

Als je met een macro en een variabele een dynamisch filter wilt maken waarbij je steeds de datum opgeeft waarop je wilt filteren heb je grote kans dat, hoewel je macro er goed uit ziet, deze als je hem draait niets 
laat zien waar je wel resultaten verwacht.
Dit wordt waarschijnlijk veroorzaakt omdat je macro in de US datum notatie gaat zoeken (MM/dd/YYYY) waar wij de datum in dd/MM/YYYY hebben staan..

Download het voorbeeldbestand hier

Hieronder 2 voorbeelden van een dynamisch filter m.b.v. VBA, in het eerste filter gebruiken we een 'gewone' string als datum om de range 
$A$1:$J$30" te filteren. Je zult zien dat de resultaten hiervan niet altijd zijn zoals je verwacht:

'Deze werkt alleen als je bijvoorbeeld 05-18-13 gebruikt als datum veld
Sub FoutiefFilterOpDatum()
'

Dim tmpdate As String
tmpdate = InputBox("Datum:", "Datum", "18-05-2013")
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$J$30").AutoFilter Field:=1, Operator:= _
        xlFilterValues, Criteria2:=Array(2, tmpdate)
End Sub



Met de volgende VBA code zetten we de datum m.b.v. een eigen functie eerst om naar een seriële datum. Waarbij we Jaar, Maand en Dag uit de datum halen zodat deze altijd op de juiste volgorde vertaald worden!

'Dit filter op datum werkt altijd
Public Sub MaakDynamischFilter()

    Dim nklantnaam As String
    Dim ntmpdate As Long
    Dim ntmpdatestring As String
               

    nklantnaam = InputBox("Geef het klantnummer:", "Klantnummer", "29704")
    ntmpdatestring = InputBox("Datum:", "Datum", "1-7-2013")

    ntmpdate = CreateSerialDate(CDate(ntmpdatestring))

    Cells.Select

    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:=nklantnaam
    Selection.AutoFilter Field:=1, Criteria1:="<" & ntmpdate
    Cells.Select
End Sub

'Functie om van een gewone datum een seriële datum te maken
Function CreateSerialDate(ndate As Date) As Long

Dim dDate As Date
Dim strDate As String
Dim lDate As Long


    dDate = DateSerial(Year(ndate), Month(ndate), Day(ndate))
    lDate = dDate
    CreateSerialDate = lDate

End Function

Download het voorbeeldbestand hier

Bij het openen van een .csv bestand met cellen met voorloopnullen heb je het probleem dat Excel de voorloopnullen weghaalt.
Zo had ik een tekst bestand met artikelnummers 00010, 00015, etc waarbij na het openen van het bestand Excel er 10, 15 e.d. van maakte. Wat ik ook probeerde ik kreeg mijn voorloopnullen niet meer terug, erg frustrerend...

Met de volgende workaround behoudt je de voorloopnullen:

• Open het originele .csv bestand in bijvoorbeeld windows kladblok.
• Selecteer de tekst met CTRL-A en kies Copy CTRL-C
• Open een nieuwe lege Excel file
• Kies Plakken / Paste (CTRL-V)
• Er verschijnt onderin een pijltje naar beneden met 'plakopties', kies 'Wizard Tekst Importeren gebruiken'

• Stap 1: Kies voor 'Gescheiden' en klik 'volgende'
• Stap 2: Geef bij scheidingsteken het teken aan waarop de .csv gescheiden is, bijvoorbeeld puntkomma en kies 'Volgende'
• Stap 3: Selecteer de kolom waarvan je de voorloopnullen wilt behouden en kies bij 'Gegevenstype per kolom' voor 'Tekst' en kies 'Voltooien'

Als je met een 64bits OS een ODBC koppeling maakt naar Oracle met een Oracle versie die lager is dan 9 heb je grote kans om de volgende foutmeldingen tegen te komen:

ORA-12154: tns could not resolve the connect identifier specified of ORA-6413 connection not open problem Excel / Visual Studio.

Dit wordt veroorzaakt doordat de bestanden van Excel maar ook bijvoorbeeld Visual Studio gedeeltelijk in de C:\Program Files (x86) map terrecht komen.
Als je dan vanuit Excel naar de Oracle database wil via bijvoorbeeld ODBC dan raakt de Oracle driver van streek door de haakjes in de padnaam (x86).

Oplossing

Om toch een verbinding op te zetten dien je het programma op te starten met de 8 tekens lange DOS pad naam, bijvoorbeeld  C:\PROGRA~2\MICROS~1\Office12\excel.exe.
Dit pad kun je vinden door in een DOS prompt (Start - uitvoeren - CMD) te navigeren naar de excel map en dan het commando dir/x te geven:

Als we het volledige 8 karakters lange DOS pad gevonden hebben zetten we dit in een snelkoppeling. Let op: Copy / Paste van de gevonden string werkt niet want zodra we dat in de snelkoppeling plakken maakt Windows er weer een (voor ons) nutteloze snelkoppeling van!

Wat we moeten doen is de string in kladblok / notepad plakken, en dit tekstbestand dan opslaan met de extensie .bat. Bijvoorbeeld StartExcelInDOSMode.bat
Als we deze batch file dan op ons bureaublad plaatsen, Excel via deze snelkoppeling opstarten, dan kunnen we probleemloos een ODBC opzetten naar onze Oracle database

Iedereen weet dat je in Excel kleuren kunt gebruiken om de cijfers van een werkblad meer te laten 'spreken', voorwaardelijke opmaak is al een stuk minder bekend maar wisten jullie dat je ook op kleur kan sorteren? In Excel 2007 en hoger kun je bij 'Sorteren op' in plaats van 'waarden' - 'Celkleur' kiezen en dan de kleurvolgorde aangeven waarop je wilt sorteren.

In Excel 2003 (maar natuurlijk ook gewoon in Excel 2007 / Excel 2010 / Excel 2013) en veel handiger eigenlijk kunnen we op kleur sorteren door een VBA functie te schrijven.

Kies: ALT-F11 - Invoegen - Module en plak de volgende code:

Function KleurRangorde(KleurVolgorde As Range, Opzoeken As Range)

Dim i As Integer
Dim ICol1 As Integer
Dim ICol2 As Integer

i = 1
ICol2 = -1
KleurRangorde = 0

'Loop until match is found
Do Until ICol1 = ICol2
'Vervang "Interior" door Font om op font kleur te sorteren
ICol1 = KleurVolgorde(i, 1).Interior.ColorIndex
ICol2 = Opzoeken.Interior.ColorIndex
If i = KleurVolgorde.Rows.Count + 1 Then
'Geen overeenkomst gevonden in de lijst
KleurRangorde = "Geen overeenkomst!!!"
Exit Do
End If
'Geef het nummer in de lijst terug van de overeenkomstige kleur
KleurRangorde = i
i = i + 1
Loop
End Function

In kolom A hebben we een lijst met kleuren of anders gezegd, hier bepalen we de volgorde van de kleuren. In kolom C staan dezelfde kleuren maar dan met de nieuw gemaakte formule / functie 'KleurRangorde'. Deze functie krijgt als eerste parameter (voor de punt-komma) een range ($A$2:$A$9) waarin de kleur(volgorde) staat en als 2e parameter de cel waar de formule in staat. We krijgen nu het rangnummer terug van de betreffende kleur, in bovenstaande afbeelding 7 omdat CYAAN de 7e positie in de lijst met kleuren in neemt!

Om te sorteren op Fontkleur i.p.v. achtergrondkleur veranderen we in de functie interior.ColorIndex door Font.ColorIndex.

Download HIER het voorbeeldbestand!