• H1. De basis
• 1.1 Starten
• 1.2 Wijzigen
• 1.3 Selecteren
• 1.4 Kolombreedte
• 1.5 Vulgreep
• 1.6 Werkmappen
• 1.7 Opslaan

• H2 Formules
• 2.1 Autosom
• 2.2 Formules kopiëren
• 2.3 Functies plakken
• 2.4 Formules met als
• 2.5 Absolute celverwijzing
• 2.6 Naam bereik
• 2.7 Formules met meer werkbladen

• H3 Opmaak, kopiëren en verplaatsen
• 3.1 Opmaakwerkbalk
• 3.2 Celeigenschappen
• 3.3 Kopiëren
• 3.4 Tussenv./verwijderen
• 3.5 Transponeren

• H4 Grafieken
• 4.1 Grafiek in Excel 2003
• 4.2 Grafiek in Excel 2007
• 4.3 Grafiek aanpassen
• 4.4 Tekenen in grafiek
• 4.5 Wiskundige grafiek

• H5 Macro's, beveiligen, enz
• 5.1 Formules
• 5.2 Sorteren
• 5.3 Filteren
• 5.4 Beveiligen
• 5.5 Macro
• 5.6 Knop in werkblad
• 5.7 Knop op werkbalk
• 5.8 Knop verwijderen
• 5.9 Hyperlink

• H6 VBA
• 6.1 Macro bekijken
• 6.2 Een knop met VBA
• 6.3 Oefeningen met VBA
• 6.4 Knop voor invoer namen
• 6.5 Invoer tijden
• 6.6 Eindklassering in venster
• 6.7 Overzicht VBA-opdrachten

• H7 Vergelijkingen oplossen
• 7.1 Vergelijking
• 7.2 Hoeveel verkopen
• 7.3 Lineair progr.
• 7.4 Twee verg. met 2 onbekenden
• 7.5 Snijpunt

Hoofdstuk 6. VBA-programma's in Excel

  6.1 Een macro bekijken

In hoofdstuk 5 hebben we besproken hoe je een macro kunt maken voor het sorteren.
Je kunt ook bekijken hoe Excel de macro vastlegt.
Een macro wordt vastgelegd als een programma, en als je dat programma wilt bekijken en/of veranderen dan moet je het volgende doen:

• Klik op Extra, dan op Macro, dan op Macro's, kies Eindsortering uit, en klik op Bewerken.
  In Excel 2007 klik je op Beeld → Macro's → Macro's weergeven, kies Eindsortering uit, en klik op Bewerken.
  Dan krijg je het volgende venster in beeld:

Je ziet dat er in de titelbalk Visual Basic staat. Er wordt dus vanuit Excel naar VBA overgeschakeld.
Wil je weer terug naar Excel dan moet je dit venster wegklikken, het venster van Excel staat er nog wel achter.

  6.2. Een knop met VBA maken

• Klik op Beeld, dan op Werkbalken, en dan op Visual Basic.
  In Excel 2007 klik je op de Officeknop linksboven en dan op Opties voor Excel → Populair. En dan zet je een vinkje voor Tabblad Ontwikkelaars op het lint weergeven en je klikt op OK
  Daarna klik je in het lint op het tabblad Ontwikkelaars, en dan zie je op het lint bij de groep Besturingselementen de icoontjes die bij Excel 2003 op de werkbalk Visual Basic staan.
  
  
• Klik nog eens op Beeld, dan op Werkbalken, en dan op Werkset Besturingselementen.
  
  In Excel 2007 klik je in het lint op het tabblad Ontwikkelaars, en dan bij de groep Besturingselementen op Invoegen.
  
  Dan zie je in Excel 2003 het volgende venster: In Excel 2007 ziet het er wat anders uit, maar het komt op hetzelfde neer)

  
  
  

• Nu klik je in de Visual Basic-werkbalk op het rechter icoontje : Ontwerpen, en dan kun je een VBA-programma ontwerpen.
  In Excel 2007 klik je in het lint bij de groep Besturingselementen op Ontwerpmodus
  
  
• Vervolgens klik je in de werkset op de knop CommandButton (=opdrachtknop, zie hiernaast), en dan kun je een button op het werkblad zetten.
  
  
• Klik dan in de werkset (of in Excel 2007 klik je er in het lint bij de groep Besturingselementen op) op Eigenschappen (zie hiernaast, rechtsboven), dan kun je de eigenschappen van die knop instellen.
  
• In het eigenschappenvenster (zie het venster hieronder) vul je bij Caption Eindsortering in.
  
• En bij Name vul je in: bEindsortering in. (de b van button zetten we ervoor).

  
  
  

• Dan heb je de interface klaar.
  
• Maar nu de programmacode nog.
  Daarvoor dubbelklik je op de knop met Eindsortering, dan verschijnt het programmavenster.
  
• Er staat al Private Sub bEindsortering_Click() en een eindje verder staat End Sub
  Op de regels daar tussen moet de programma-code worden geplaatst.
  Daar tik je het volgende in (of je plakt het er neer als je van tevoren de tekst uit de macro op het klembord hebt gezet) :
  

  Private Sub bEindsortering_Click()
      Range("A3:R6").Select
      Selection.Sort Key1:=Range("R3"), Order1:=xlAscending, Header:=xlGuess, _
          OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
      Range("A1").Select
  End Sub

  
  
• Als dat klaar is dan klik je het programmavenster en het Visual Basicvenster weg, dan klik je in de Visual Basic-werkbalk op het rechter icoontje : Ontwerpmodus afsluiten (of in Excel 2007 klik je er in het lint bij de groep Besturingselementen op) , en dan ben je klaar.

  6.3. Een paar eenvoudige oefeningen met VBA

Private Sub bOptellen_Click()
	Dim g As Integer
	g = Cells(4,1).value
	g = g+1
	Cells(4,1).value = g
End Sub

• Cells(4,1).value geeft de waarde van de cel in de vierde rij en de eerste kolom, dus cel A4
• Door de opdracht g = Cells(4,1).value krijgt g die waarde.
• De opdracht g = g + 1 heeft tot gevolg dat bij de waarde van g 1 wordt opgeteld.
• En door de opdracht Cells(4,1).value =g wordt die waarde in de cel A4 gezet.

Private Sub bOptellen_Click()
	Dim g, w As Integer

	g = Cells(4,1).value
	w = InputBox("VBA-test","Voer getal in")
	g = g + w
	Cells(4,1).value = g
End Sub

• g = Cells(4,1).value zorgt er voor dat de variabele g de waarde krijgt van de inhoud van cel A4
• w = InputBox("VBA-test", "Voer getal in") heeft tot gevolg dat er een invoervenster verschijnt.
  In de titelbalk van dat venster staat VBA-test, en in het venster zelf staat Voer getal in
  En als je dan een getal, bijv. 7, intikt en op OK klikt, dan krijgt w die waarde 7
• De opdracht g = g + w heeft tot gevolg dat bij de waarde van g de waarde van w wordt opgeteld.
• En door de opdracht Cells(4,1).value = g wordt de nieuwe waarde van g in de cel A4 gezet.

Private Sub bTellen_Click()
	Dim i, aantal As Integer
	dim g as string

	aantal = 0
	for i=1 to 100
    		g = Cells(i,1).value
    		if g <> "" then aantal = aantal+1
	next i
	msgbox("Het aantal ingevulde cellen is " + str(aantal))
End Sub

• Het aantal cellen waar iets in staat wordt vastgelegd m.b.v. de variabele aantal
  Daar moet in het begin, voor je begint te tellen, natuurlijk het getal 0 in staan.
  Daar dient de opdracht aantal = 0 voor, dat noem je de initialisatie.
• Als we er van uit gaan dat er in ieder geval vanaf de 101-ste rij niets meer ingevuld is, dan moet er in de 1-ste, de 2-de, de 3-de, tot en met de 100-ste cel worden gekeken of er iets staat.
  Daarom wordt de for-lus gebruikt: alles tussen de opdrachten for i=1 to 100 en next i wordt 100 keer herhaald, en daarbij krijgt i achtereenvolgens de waarden 1 t/m 100
• De eerste opdracht die wordt uitgevoerd is g = Cells(i,1).value
  De eerste keer heeft i de waarde 1, dus eigenlijk staat er dan g = Cells(1,1).value
  Dat betekent dat g de waarde krijgt die in de eerste cel staat.
• In de volgende opdracht staat if g <> "" then aantal = aantal+1
  Dat betekent dat als g niet niets is (twee aanhalingstekens vlak na elkaar betekent: niets en <> betekent: niet gelijk aan) wordt het aantal met één vermeerderd.
  De tweede keer heeft i de waarde 2, dus eigenlijk staat er dan g = Cells(2,1).value
  Dat betekent dat g de waarde krijgt die in de tweede cel staat.
  En if g <> "" then aantal = aantal+1 heeft dan weer tot gevolg dat als g niet niets is (dus als er wel iets is ingevuld) het aantal met één wordt vermeerderd.
  En zo gaat dat door tot en met de honderdste cel. (eventueel kun je ook tot de 1000-ste cel doortellen)
• En daarna moet dat aantal nog getoond worden, anders heb je er niets aan.
  Dat gebeurt met de opdracht msgbox("Het aantal ingevulde cellen is " + str(aantal))
  Msgbox("----") heeft tot gevolg dat er een venstertje verschijnt waarin ---- staat.
  Als er in 15 cellen iets staat dan verschijnt er in de Messagebox uit het programma: Het aantal ingevulde cellen is 15
  + str(aantal) betekent dat het aantal (het aantal waar m.b.v. str(--) eerst een string van gemaakt is) achter de tekst Het aantal ingevulde cellen is geplakt wordt.

Private Sub bVoerin_Click()
Dim v As String
Dim kolom, rij As Integer

kolom=InputBox("Geef het nummer van de kolom die je in wilt vullen","VBA")
If (kolom < 1) Or (kolom > 10) Then Exit Sub

rij = 1
v = "iets"

while v <> ""
    v=InputBox("Welk woord in rij "+str(rij)+" en kolom  "+str(kolom))
    Cells(rij, kolom + 1 - 1).value = v
    rij = rij + 1
wend

End Sub

• De opdracht kolom=InputBox("Geef het nummer van de kolom die je in wilt vullen","VBA")
  heeft tot gevolg dat er een invulvenster verschijnt, waarin om het kolomnummer wordt gevraagd.
  Het nummer van die kolom wordt opgeslagen in de variabele kolom.
• En dan moet er steeds gevraagd worden welk woord er in de volgende cel moet verschijnen.
  Maar je weet van tevoren niet hoe vaak dat herhaald moet worden.
  Daarom kan het niet met de opdracht FOR i=1 TO ....
  Je weet wel dat er net zo lang doorgegaan moet worden tot er niets wordt ingevuld.
  Als je het ingevulde woord in de variabele v opslaat dan moet er dus net zo lang doorgegaan worden tot v leeg is.
  Je kunt ook zeggen dat er steeeds doorgegaan moet worden zolang v niet leeg is, dus zolang v <> "" is.
  De opdrachten tussen while v <> "" en wend worden dan ook net zolang herhaald tot v leeg is.
  
• Maar dan moet v in het begin niet leeg zijn, anders wordt er direkt gestopt.
  Daarom staat de opdracht v = "iets" er tussen, zodat v in het begin niet leeg is.
  
• Verder moet het volgende woord steeds in de volgende rij worden ingevuld, dus de rij die aan de beurt is moet worden vastgelegd. Dat gebeurt met de variabele rij.
  En elke keer als er iets is ingevuld wordt het rij-nummer opgehoogd m.b.v. de opdracht rij = rij + 1
• De opdracht Cells(rij, kolom + 1 - 1).value = v heeft tot gevolg dat het ingetypte woord in de juiste cel wordt geplaatst.
  Als je de opdracht invoert als Cells(rij, kolom).value = v dan zou het eigenlijk ook goed moeten gaan. Maar Excel geeft dan een foutmelding omdat er voor de sluithaakjes een getal wordt verwacht. Daarom heb ik er maar +1-1 tussen gezet, dan werkt het.

  6.4. Een knop voor het invoeren van extra namen

Private Sub bNamenInvoeren_Click()
Dim naam, oudenaam As String
Dim aantal As Integer
aantal = 0
naam = "x"
For r = 1 To 16
    If Cells(r + 2, 2) <> "" Then aantal = aantal + 1
Next r

If aantal = 16 Then
    MsgBox ("Meer namen invullen kan niet")
Else
    For r = 1 To 16
        oudenaam = Cells(r + 2, 2).Value
        If naam <> "" And naam <> "Onwaar" Then
          naam=InputBox("Deelnemer " & r &"","Voer namen in",oudenaam)
          If naam <> "" And naam <> "Onwaar" Then
                Cells(r + 2, 2).Value = naam
                Cells(r + 2, 1).Value = r
          End If
        End If
    Next r
End If

End Sub

aantal = 0
For r = 1 To 16
    If Cells(r + 2, 2) <> "" Then aantal = aantal + 1
Next r

• De middelste tekst komt in de titelbalk, dus in de titelbalk van dat venster staat : Voer namen in
  
• De eerste tekst verschijnt in het venster, daar staat de eerste keer: Deelnemer 1:, en als r 2 is dan staat er: Deelnemer 2:, enz.
  "Deelnemer " & r & ":" betekent dus dat het woord deelnemer wordt afgedrukt, dan de waarde van r, en dan de dubbele punt.
  
• De laatste tekst verschijnt alvast als invoer, zodat als er al een naam in de cel stond die naam ook in het invulvak staat, en dan hoeft er alleen maar op enter te worden gedrukt om die naam (weer) vast te leggen.
  

  6.5. Een programma om de tijden in te voeren.

Private Sub bTijdenInvoeren_Click()

Dim keuze, invoer, namen As String
Dim a, v As Integer

keuze = "1 = 500 m" + Chr(13) + "2 = 1500 m" + 
         Chr(13) + "3 = 5000 m" + Chr(13) + "4 = 10000 m"

a=InputBox(keuze,"Geef het nummer van de afstand die je in wilt vullen")
If a < 1 Or a > 5 Then Exit Sub

namen = ""
For r = 3 To 18
    If (Cells(r, 2) <> "") And 
       (Cells(r, 3 + (a - 1) * 4) = "") Then
        namen = namen + " " & (r - 2) & " " + Cells(r, 2) + Chr(13)
    End If
Next r

v = InputBox(namen,"Volgnummer voor invullen op de " + 
      Cells(1, 3 + (a - 1) * 4))

If a > 1 Then
    invoer=InputBox(Cells(v+2,2),Cells(1,3+(a-1)*4)+"tijd in minuten:")
    Cells(v + 2, 2 + (a - 1) * 4) = invoer
End If

invoer=InputBox(Cells(v+2,2),Cells(1,3+(a-1)*4)+"tijd in seconden:")
Cells(v+2,3+(a-1)*4)=invoer
invoer=InputBox(Cells(v+2,2),Cells(1,3+(a-1)*4)+"tijd in 1/100 sec:")
Cells(v+2,4+(a-1)*4)=invoer

End Sub

Wat er in de string keuze zit is vastgelegd door de opdracht
keuze = "1 = 500 m" + Chr(13) + "2 = 1500 m" + Chr(13) + "3 = 5000 m" + Chr(13) + "4 = 10000 m"
Chr(13) is de code van de entertoets, je kunt ook zeggen dat het de code is om op een nieuwe regel over te gaan.
Dus bevat de string keuze ten eerste "1 = 500 m", dan "2 = 1500 m" en dat wordt op de volgende regel gezet doordat Chr(13) er tussen staat, enz.

De opdracht
If a < 1 Or a > 5 Then Exit Sub heeft tot gevolg dat als er niet 1, 2, 3 of 4 wordt ingetikt er gestopt wordt met de procedure.

De opdracht
v = InputBox(namen, "Volgnummer voor invullen op de " + Cells(1, 3 + (a - 1) * 4))
heeft tot gevolg dat het volgende venster verschijnt:

Wat er in de string namen zit is vastgelegd door de opdrachten
namen = ""
For r = 3 To 18
If (Cells(r, 2) <> "") And (Cells(r, 3 + (a - 1) * 4) = "") Then
namen = namen + " " & (r - 2) & " " + Cells(r, 2) + Chr(13)
End If
Next r

Eerst de initialisatie-opdracht: (namen = "" ) namen wordt gevuld met de lege string

Daarna wordt er steeds een naam en een nummer aan de string namen toegevoegd: het nummer is (r-2) en de naam zit in de cel op regel r en in kolom 2 (Cells(r, 2) ) , en elke naam komt op een nieuwe regel doordat er Chr(13) aan toegevoegd is.

Maar een naam wordt er alleen aan toegevoegd als er echt een naam in cel (r,2) staat, want het gebeurt alleen als
Cells(r, 2) <> ""
en verder wordt de naam er ook alleen aan toegevoegd als er nog geen tijd is ingevuld voor de afstand waarom het gaat (als a = 1 dan is dat de 500 meter en dan staan de seconden in cel (r,3), enz.), want het gebeurt alleen als
Cells(r, 3 + (a - 1) * 4) = ""

Als het volgnummer van degene waarvan de tijd moet worden ingevuld is ingevoerd verschijnt er een nieuw venster zoals hieronder:

Alleen voor die persoon wordt gevraagd wat de tijd in minuten is (alleen als a>1).
Daarna wat de tijd in seconden is. En dan nog wat de tijd in honderdsten van seconden is.
En die tijden worden vastgelegd in de cellen die daarvoor bestemd zijn.

  6.6. Het eindklassement in een venster.

Private Sub bKlassement_Click()

Dim lijst As String
Dim r, l, q As Integer

Range("A3:R18").Select
    Selection.Sort Key1:=Range("R1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
lijst = ""
l = 0
For r = 3 To 18
  If Cells(r,5).Value>0 And Cells(r,9).Value>0 And 
                               Cells(r,13).Value>0 And Cells(r,17).Value>0 Then
    l = l + 1
    lijst=lijst+"" & l & ":"+Cells(r,2)+":"+Str(Int(Cells(r,18)*100)/100)+Chr(13)
  End If
Next r

Range("A3:R18").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
q = MsgBox(lijst, vbDefaultButton1, "Eindklassement")
     
End Sub

  6.7. Overzicht VBA-opdrachten