donderdag 25 september 2014

VBA - Object Workbooks

In vorige items hebben we via VBA iets weggeschreven in een cel van een werkblad. Hiervoor konden we de objecten Range en Cells gebruiken. Met het object Worksheets konden we ook een werkblad selecteren. In dit punt wensen we via VBA iets weg te schrijven in een bepaald excel-document. Hiervoor gaan we het object Workbooks gebruiken. We werken verder met het excel-document "Invullen.xlsm" uit de vorige items. We maken eerst een nieuw excel-document.
  • Open het document "invullen.xlsm".
  • Klik op het tabblad "Bestand" in het lint.

  • Klik in de linkse kolom op het item "Nieuw".

  • Dubbelklik op het pictogram "Lege werkmap".
Een nieuw excel-document verschijnt. Deze werkmap gaan we een bestandsnaam geven via de opdracht "opslaan als".

  • Klik op het tabblad "Bestand" in het lint.
  • Klik in de linkse kolom op het item "Opslaan als".
Je kan dit bestand bewaren op verschillende locaties. Zelfs in een externe omgeving via "OneDrive". We gaan dit bestand op onze harde schijf plaatsen.
  • Klik op "Computer"
  • Klik op "Bladeren".
Het dialoogvenster "Opslaan als" verschijnt. Nu kan je eventueel een nieuwe map aanmaken of een bestaande locatie kiezen. Ook staat de filter "xlsx" aan. Hierdoor zie je het bestand "invullen.xlsm" niet. We plaatsen het document in de map "Documenten" en geven dit de bestandsnaam "opleiding".

  • Klik in het tekstvak "Bestandsnaam".
  • Typ "opleiding".

  • Klik op "OK".
We gaan nu naar onze visual basic editor.
  • Gebruik de sneltoets ALT F11 om naar de visual basic editor te gaan.

In ons geval worden de codes uit vorige punten zichtbaar. Deze bevinden zich in de modules "mdlInvullen" en "mdlInvullen2". De code in "mdlInvullen2" is reeds zichtbaar. We gaan daar de code uitbreiden met een nieuwe subroutine.

  • Plaats de cursor onder de subroutine "Invullen3".
  • Typ "Sub NaarDocument".
  • Druk op de toets "Enter".
Automatisch wordt "End Sub" toegevoegd.

We vermelden via tekst alle stappen die nodig zijn. Tekst begint met een aanhalingsteken '. Dit wordt niet aanzien als code. Het is belangrijk om informatie aan te brengen in de code, zodoende dat je zelf later of anderen begrijpen wat er in de code staat. Het kan ook een hulpmiddel zijn om alle stappen nu te gaan vertalen in vba.

De stappen zijn:

Het bestand "opleiding.xlsx" openen.

Een nieuwe werkblad aanbrengen in het geopend bestand "opleiding.xlsx".

De tekst "opleiding" plaatsen in cel A1 van dit nieuw werkblad.

De wijzigen bewaren.

Het bestand "opleiding.xlsx" sluiten.

We gaan nu elke stap omzetten in de VBA-code.

Eerst gaan we het bestand "opleiding.xlsx" openen.
  • Plaats de cursor onder de tekst "sub NaarDocument".
  • Typ "Workbooks.
Het hoofdobject is workbooks. Bij het aanbrengen van een punt verschijnen de eigenschappen en methoden voor dit object. Hier is de methode "Open" nodig.

  • Dubbelklik op het element Open uit het lijstje.

Wanneer je nu een spatie aanbrengt dan verschijnt informatie voor het gebruik van deze methode "Open".
  • Druk op de spatiebalk.

Het eerste argument is Filename. Hier komt de bestandsnaam te samen met het volledige "path". Via de verkenner van windows kan je eventueel achterhalen in welke folder het bestand "opleiding.xlsx" zich bevindt.
  • Start de verkenner via de toetsencombinatie Windows-logo en letter E.
  • Klik vooraan in de adresbalk op het pictogram.
In ons geval bevindt zich het document "opleiding.xlsx" in de folder "Claude". Het volledig path is hier "C:\Users\Claude\opleiding.xlsx".

  • Ga terug naar de visual basic editor.
  • Typ "C:\Users\Claude\opleiding.xlsx" na ".open".

Vervolgens brengen we de code aan om in het geopend bestand een nieuw werkblad te bekomen. De code hiervoor is Worksheets.add.
Na het aanbrengen van een spatie zie je de syntax. Met de argumenten "Before" en "After" kan je de plaats van het werkblad bepalen. Met bijvoorbeeld Worksheets(1) voor het eerste argument "Before" komt het nieuw werkblad helemaal vooraan.

Echter het nieuw werkblad krijgt een standaard naam. We gaan direct ook de naam van het werkblad bepalen.
  • Plaats de cursor onder de tekst 'maak een nieuw werkblad aan met de naam "test"'.
  • Typ worksheets.add.name = "test".
Meteen is dit werkblad "test" geselecteerd. En kunnen we een tekst aanbrengen.

  • Plaats de cursor onder de tekst 'plaats in cel A1 een titel'.
  • Typ Range("A1").value = "opleiding".

Vervolgens gaan we het document bewaren.
  • Plaats de cursor onder de tekst 'bewaar de werkmap'.
  • Typ activeworkbook.save

Tenslotte sluiten we het document.
  • Plaats de cursor onder de tekst 'sluit de werkmap'.
  • Typ activeworkbook.close

Nu kunnen we deze vba-code gaan testen.
  • Klik op het pictogram met groene pijl bovenaan in de werkbalk van de visual basic editor.

vrijdag 19 september 2014

Scenariobeheer

In een werkblad zijn een aantal waarden aangebracht voor verschillende maanden voor het jaar 2012. We wensen op een gemakkelijke manier de trend te berekenen voor het jaar 2013. Hiervoor kunnen we bijvoorbeeld het scenariobeheer gebruiken. In de vorige punten werden de verschillende berekeningen en het maken van de grafiek met trendlijn reeds besproken.


  • Klik op het tabblad "Gegevens".

  • Klik op "Wat-als-analyse" in de zone "Hulpmiddelen voor gegevens".

  • Klik op "Scenariobeheer...".
Het dialoogvenster "Scenariobeheer" verschijnt. Er zijn nog geen gegevens aangebracht.

  • Klik op de knop "Toevoegen".

Het dialoogvenster "Scenario bewerken" verschijnt.
  • Typ "jaar2012" in het tekstvak "Scenarionaam".

  • Klik in het tekstvak "Veranderende cellen".
  • Selecteer in het werkblad de cellen C2 tot C11.

  • Klik op "OK".

De waarden voor de verschillende maanden verschijnen in de verschillende tekstvakken van het dialoogvenster "Scenariowaarden".

We voegen de gegevens toe voor het jaar 2013.
  • Klik op de knop "Toevoegen".

Het dialoogvenster "Scenario toevoegen" verschijnt. Het tekstvak "Scenarionaam" is geselecteerd.

  • Typ "jaar2013".

  • Klik op "OK".
We vullen de waarden in voor de maanden van 2013, in de verschillende tekstvakken.


  • Klik op "OK".
In het dialoogvenster "Scenariobeheer" zien we de twee scenario's.
  • Klik op "Sluiten".

We kunnen de scenario's terug oproepen en de gegevens in het werkblad laten aanpassen naargelang het scenario.
  • Klik op het tabblad "Gegevens", indien dit nog niet geselecteerd is.
  • Klik op "Wat-als-analyse" in de zone "Hulpmiddelen voor gegevens".
  • Klik op "Scenariobeheer...".

Het dialoogvenster "Scenariobeheer" verschijnt. Met hierin onze twee scenario's.
  • Selecteer het scenario jaar2013.

  • Klik op de knop "Weergeven".

In de cellen C2 tot C11 verschijnen de waarden voor 2013. En alle berekeningen en grafiek passen zich aan.

woensdag 17 september 2014

Functie R.Kwadraat

In een werkblad zijn een aantal waarden aangebracht voor verschillende maanden. We wensen de parameter R.Kwadraat voor de vergelijking van de trendlijn via een formule te berekenen. Bij het aanbrengen van de trendlijn werd de formule reeds weergegeven. De vergelijking ziet er uit als y = mx + b. De waarde voor R.Kwadraat ligt tussen 0 en 1. Hoe dichter het resultaat bij de waarde 1 ligt, hoe nauwkeuriger de trendlijn.

  • Selecteer de cel H8.

  • Klik op het tabblad "Formules" in het lint.
  • Klik op de boek "Meer functies".
  • Klik op de boek "Statistisch".
  • Klik op de functie "R.Kwadraat".
Het dialoogvenster "Functieargumenten" verschijnt. Het tekstvak "Y-bekend" is reeds geselecteerd.

  • Klik in de cel "C2".

  • Gebruik de sneltoetsen CTRL SHIFT Pijltje-naar-beneden om de gegevens te selecteren.

  • Plaats de cursor in het tekstvak "X-bekend".
  • Selecteer de cellen B2 tot B11.

  • Klik op "OK".

De waarde voor R.Kwadraat verschijnt in de geselecteerde cel.

maandag 15 september 2014

Functie SNIJPUNT

In een werkblad zijn een aantal waarden aangebracht voor verschillende maanden. We wensen de parameter Snijpunt voor de vergelijking van de trendlijn via een formule te berekenen. Bij het aanbrengen van de trendlijn werd de formule reeds weergegeven. De vergelijking ziet er uit als y = mx + b. We bepalen de parameter b met behulp van de functie Snijpunt.


  • Klik in de cel H7.
  • Klik op het tabblad "Formules" in het lint.
  • Klik op het boek "Meer functies".
  • Klik op het boek "Statistisch".

  • Klik op de functie Snijpunt.
Het dialoogvenster "Functieargumenten" verschijnt. Het tekstvak "Y-bekend" is reeds geselecteerd.

  • Klik in de cel "C2".

  • Gebruik de sneltoetsen CTRL SHIFT pijltje-naar-beneden, om de gegevens te selecteren.

  • Plaats de cursor in het tekstvak "X-bekend".
  • Selecteer de cellen B2 tot B11.

  • Klik op "OK".

De waarde voor de parameter b van de trendlijn verschijnt in de geselecteerde cel.