Category Archives: Excel VBA

Export Range to HTML file

With the code below you can export values or formulas from a worksheet range to a HTML-file:

© erlandsendata.no

Example of a worksheet range with random data.

Result, the basic HTML table.

2 lijsten vergelijken

Je kent dat wel. Je hebt twee lijsten die gegevens bevatten. Nu wil je checken of de items in Lijst_2 voorkomen in Lijst_1. Als het lange lijsten zijn is dat een hels karwei. Bijvoorbeeld, komt “Drachenblut Delikatessen” voor in Lijst_1? Ja (TRUE). Komt “QUICK-Stop” voor in Lijst_1? Nee (FALSE).

Formule die je daarvoor kan gebruiken is simpel:

D1 =ISNUMBER(MATCH($B2;$A$2:$A$11;0))

Enter en doorvoeren naar beneden.

Wil je weten of een item NIET in Lijst_1 voorkomt dan gebruik je de formule:


E1 =ISNA(MATCH($B2;$A$2:$A$11;0))

Enter en doorvoeren naar beneden.

Webscrape voorbeeld

We gaan naar de website:
https://www.gameshop.nl/webshop/index.php

Om wat gegevens van computerspellen binnen te halen middels VBA-Code.

Voeg onderstaande code toe:

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om terug te keren naar het Excel werkblad
7. Tenslotte ga naar View | Macros | View macros en kies je de juiste macro.

Je kunt de opgehaalde broncode van de webpagina loggen (vastleggen) in een tekstbestand ter referentie. In bovenstaande code staat deze zin:

‘LogInformation (HTMLdoc.body.innerHTML)

Haal de apostrof die aan het begin van de zin staat weg. Vervolgens onderstaande code toevoegen:

Zorg dat je het bestand textfile.html in de juiste directory hebt staan namelijk:
C:\temp\textfile.html
Telkens als je logt wordt eerst de inhoud van dat bestand verwijderd en vervolgens wordt er nieuwe tekst naar geschreven.

Bestanden in map tonen met hyperlinks

De VBA code genereert een lijst met bestanden in een map. Je kunt zelf een map (directory) kiezen waarvan je de bestanden wil zien.

Voorbeeld weergave:

Voeg onderstaande code toe:

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om terug te keren naar het Excel werkblad
7. Tenslotte ga naar View | Macros | View macros en kies je de juiste macro.

Unieke lijst genereren

Indien je een unieke lijst wil genereren is dit de manier om dat te doen. De waarden staan in kolom B. Daar staan ook dubbele of zelfs drie-dubbele waarden in

In D2 zet je de volgende formule:

=IFERROR(INDEX($B$2:$B$1000;SMALL(IF(FREQUENCY(IF($B$2:$B$1000<>””;MATCH($B$2:$B$1000;$B$2:$B$1000;0));ROW($B$2:$B$1000)-ROW(INDEX($B$2:$B$1000;1;1))+1);ROW($B$2:$B$1000)-ROW(INDEX($B$2:$B$1000;1;1))+1);ROWS($A$2:A2)));””)

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden

Nog een mogelijkheid met een kortere maar minder robuuste formule

In F2 zet je de volgende formule:

=IFERROR(INDEX($B$2:$B$1000; MATCH(0;COUNTIF($F$1:F1; $B$2:$B$1000);0));””)

Ook dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden

Tenslotte, in kolom H staan tekst en cijfers door elkaar. In kolom I zet je alweer een array formule:

=IFERROR(INDEX($H$2:$H$30;SMALL(IF(FREQUENCY(IF($H$2:$H$30<>””;MATCH($H$2:$H$30;$H$2:$H$30;0));ROW($H$2:$H$30)-ROW(INDEX($H$2:$H$30;1;1))+1);ROW($H$2:$H$30)-ROW(INDEX($H$2:$H$30;1;1))+1);ROWS($A$2:A2)));””)

Ook dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden

Waarden optellen terwijl Id telkens verandert

Best een moeilijke klus. In de kolommen A, C, E, G staan de Id’s van de verkopers. In de kolommen B, D, F, H staan de verkoopcijfers. Je wil de verkoopcijfers van elke Id (verkoper) optellen. Natuurlijk is er een probleem, de Id’s en verkoopcijfers kunnen telkens veranderen.

Ten eerste voeg je onderstaande code toe:

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V

Vervolgens heb je de volgende formules nodig. Eerst maak je 2 benoemde bereiken. Ga naar:
Formulas | Name manager | New
en geef de naam Ivec. Vervolgens:
In het vak Refers to zet je de volgende 

=ROW(INDIRECT(“1:”&COLUMNS(PNdata)))

Herhaal dit:
Naam: PNdata
Refers to:

=arrayunion(Sheet1!$A$2:$A$12;Sheet1!$C$2:$C$12;Sheet1!$E$2:$E$12;Sheet1!$G$2:$G$12)

In J1:

=SUM(IF(FREQUENCY(IF(PNdata<>””;MATCH(“~”&PNdata;PNdata&””;0));Ivec);1))

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

In J3:

=IF(ROWS($J$3:J3)<=$J$1;MIN(IF(ISNUMBER(MATCH(PNdata;$J$2:J2;0));””;PNdata));””)

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Doorvoeren naar beneden.

In K3:

=SUMIFS(B:H;A:G;J3)

(Dit is GEEN array formule, dus gewoon invoeren met alleen Enter).
Doorvoeren naar beneden

Download bestand

Credits gaan naar: 
Code: Juan Pablo González
Spec: Aladin Akyurek
Source: https://tinyurl.com/y3b9r9qg
May 4, 2003

Het liggend streepje, the underscore _

Een onderstrepingsteken “_” wordt doorgaans wordt gebruikt in een VBA-coderegel.

Een underscore (wordt ALTIJD direct voorafgegaan door een spatie) in een VBA-coderegel geeft aan dat de huidige instructie doorgaat op de volgende regel. Omdat VBA geen tekstterugloop heeft, wordt het gebruikt om een ​​enkele regel code over twee regels te verdelen, om de code leesbaarder te maken.

Dus in plaats van dit:

Kunnen we dit schrijven:

Nog een voorbeeld:

Kunnen we zo schrijven:

Nogmaals, je moet een spatie invoegen vóór de underscore. Anders retourneert de VBA-compiler een fout.

Gegevens in één cel splitsen

Gegevens in één cel splitsen en weergeven per cel. In Kolom A en B staan gegevens. Er staan telkens twee namen in één cel bijvoorbeeld in A2 . In B2 staan twee steden. Zie afbeelding.

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
7. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbelklik op de macro naam om te starten

8 Tekens vanaf de linkerkant van een tekenreeks verwijderen

Met behulp van VBA code 8 tekens vanaf de linkerkant van een tekenreeks verwijderen. Tekenreeksen staan in kolom A.

Je moet wel opletten want een cijfer als 02 kapt Excel af en toont dat als 2. Hierdoor wordt de tekenreeks “AFT5985602” weergegeven als 2. Er zijn in dat geval 9 cijfers weggelaten. Daarom moet je de 2 aan het einde van de code niet veranderen

Voorbeeld data

Resultaat

Splits data en zet in één cel gescheiden door komma

In Kolom A namen van Employees waarbij dezelfde naam meerdere keren kan voorkomen. In Kolom B namen van Customers. Dezelfde Employee kan meerdere Customers hebben. Dat wil je weergeven vanaf cel A26. Zie afbeelding.

1. Kopieer de onderstaande code middels Ctrl + C.
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel.

De bovenstaande code is een functie die gebruikt wordt in de formule in B26

Plaats de volgende Formules:
A26:

=IFERROR(INDEX($A$2:$A$23;MATCH(0;INDEX(COUNTIF($A$25:A25;$A$2:$A$23)+(A$2:A$23="");0);0));"")

Invoeren met gewoon Enter en doorvoeren naar beneden.

B26:

=IF(A26="";"";MID(aconcat(IF($A$2:$A$23=A26;", "&$B$2:$B$23;""));3;999))

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.

Let op: Plaats die accolades { } niet handmatig
Doorvoeren naar beneden.