09

Programmeren in Excel VBA Karel Nijs 2010/09

Leswijze • Eerst wat theorie • Begeleid met (korte) oefeningen – Ms Excel 2003

• Online hulp: – – – –

http://www.ozgrid.com/VBA/ http://msdn.microsoft.com/en-us/library/sh9ywfdk(vs.80).aspx http://www.microsoft.com/technet/scriptcenter/topics/office/vba.mspx http://www.kbcopenleren.be/

• Offline hulp (op CD-ROM): – Boek "VBA voor Word, Excel en Access" – VBA-handleiding - vanuit Excel.doc

• Korte pauze rond 20u

Leswijze • Voorkennis: – Een goede kennis van MS Excel is vereist – Opleiding 'Leren programmeren: Inleiding VB en VBA - code 279' gevolgd – Of u heeft reeds dezelfde kennis uit ervaring

Hier is voorkennis wél toegelaten...

Doel van deze cursus

VBA leren gebruiken zodat je bepaalde repetitieve handelingen in Ms Excel 2003 kan automatiseren

Wat heb je hiervoor nodig? • • • • • • •

Weten hoe Excel werkt Weten hoe Excel intern werkt Macro’s kunnen opnemen Beperkte programmeerkennis Referentie: online of offline Vermogen om te interpreteren en af te leiden Gezond verstand

Programmeren in Excel VBA • • • • • • • •

Visual Basic Intro De Visual Basic Editor (VBE) Debuggen in VBA Macro’s Het object Range Werkmappen en werkbladen Het object Application Event macro's

Programmeren in Excel VBA – les 1 • • • • • • •

Visual Basic Intro De Visual Basic Editor (VBE) Debuggen in VBA Macro’s Het object Range Range > Verwijzen naar het juiste cellenbereik Range > Opvullen & bewerken

Visual Basic intro • Visual Basic for Applications (VBA) – Afgeleide van VB – Géén standalone taal, leeft bij host-applicatie • Voorbeeld hosts: Ms Office, Visio, AutoCAD, ...

– Laat toe de host-applicatie aan te passen – Gebruiksgemak: codegeneratie mbv. macro's – Script-taal (géén compilatie)

Visual Basic intro

Visual Basic

Visual Basic for Applications

VBscript

Visual Basic intro Source Code (HLL) VB

VB

COMPILER

VBA

Native Code

INTERPRETER HARDWARE

slide van Peter Demasure

Visual Basic intro • Subsets – VBA is een gelaagde programmeertaal – Basisfunctionaliteit is hetzelfde voor elke toepassing – Elke toepassing heeft deelverzameling van instructies en mogelijkheden – Programmeren binnen de basislaag werkt in elke subset – Subset A code werkt niet automatisch in Subset B

Visual Basic intro • Subsets

Subset Ms Word

Subset Ms Excel Subset Ms Access VBA Basisfunctionaliteit (Microsoft en anderen)

tekening uit boek "VBA voor Word, Excel en Access"

Subset Subset Product Product Y X

subset specifieke code

uniforme code

Visual Basic intro • Visual Basic for Applications: voordelen – – – –

Gebruiksgemak (tov. VB): code generatie mbv. macro's Host-applicatie uitbreiden met niet-standaard mogelijkheden Repetitief werk automatiseren Vanuit één applicatie de andere besturen • Bv. Ms Word documenten aanmaken vanuit Ms Excel



De Visual Basic Editor (VBE) • Ontwikkelingsomgeving beschikbaar in elke subset • ~ editor voor Visual Basic • De programmacode wordt opgeslagen bij je document net zoals bij macro's! • De VBE bevat alle hulpmiddelen voor programmatie – – – –

Objecten browser Immediate window Debugger ...

De Visual Basic Editor (VBE) •

Demo - opstarten van de VBE: 1. Start Ms Excel 2. Toon de "Visual Basic" werkbalk

3. Docent geeft basisuitleg

•

Demo – opstarten van de object browser

•

Demo – het immediate window

De Visual Basic Editor (VBE) • Scopes: Project Bundeling van code Scope = Werkblad Bundeling van code Scope = Werkboek Bundeling van code Scope = Project Bv. module voor aansturing MindStorms

Functies en subroutines •

Procedure – Procedures steken altijd in modules •

ThisWorkBook en Bladx zijn eigenlijk ook modules!

– Scope of “toegang” of “aanroepbaarheid” of … •

Public – Toegankelijk voor andere procedures buiten de module – Bv. procedure om robot te besturen

•

Private – Enkel toegankelijk voor andere procedures binnen de module – Code hiding – Bv. (interne) procedure om de snelheid te berekenen

De Visual Basic Editor (VBE) •

De code:

Option Explicit

declaratie van variabelen vereist procedure wordt opgeroepen bij elke save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean)

variabele declaratie

Dim a As Boolean a = MsgBox("Wil je écht opslaan?", vbYesNo) If a = vbNo Then Cancel = True End If End Sub

functie met terugkeerwaarde conditie die de variabele "a" test terugkeerwaarde opslaan



Debuggen in VBA • Verschillende methodes: – Immediate window – Debug.Print “...” – Debug.Assert( statement )

– – – –

Breakpoints Door code stappen Locals window Watch window

• Uitgebreide uitleg: http://www.cpearson.com/excel/Debug.htm

Debuggen in VBA • Het immediate window – – – –

Of ook het “venster direct” Opstarten via Beeld > Venster direct Rechtstreeks uitvoeren van commando’s at design time Tutorial: http://www.lynda.com/home/TutorialLanding.aspx?lpk4=68857

Debuggen in VBA • Debug.Print “” – Tekst afdrukken naar het Debug window – Enkel zichtbaar bij debuggen – Hindert verdere programmatie niet – Bv. Debug.Print “We beginnen met de lus”

• Debug.Assert( statement ) – Evalueren van een statement – Code stopt met uitvoeren als statement niet klopt – Bv. Dim X As Long X = 123 Debug.Assert (X < 100)

Debuggen in VBA • Breakpoints – Uitvoeren code pauzeert wanneer breakpoint bereikt wordt – Vanaf dan kan je inspecteren en verder door de code stappen

• Door code stappen – Vanaf een breakpoint – Gebruik van werkbalk: Beeld > Werkbalken > Foutopsporing

Debuggen in VBA • Locals window – Geeft alle variabelen van de huidige procedure + de globale variabelen weer

• Watch window – Inspecteren en opvolgen van variabelen



Macro’s If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module and can be run whenever you need to perform the task. For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps.

Macro’s • Waarom: – Opnemen van acties in Excel – Ideaal voor als je niet weet hoe je iets moet programmeren

• Wat: – Een macro is net zoals een publieke procedure (Public Sub): iedereen kan ze uitvoeren

• Voordelen – Eenvoudig – Hergebruik

• Nadelen: – Code is niet generisch: vaak wordt er bv. gewerkt met Selection – Nog steeds herwerk/tuning nodig

Macro’s • Uitleg: – http://office.microsoft.com/en-gb/excel-help/aboutmacros-in-excel-HP005201201.aspx

• Tutorial: – http://www.helpwithpcs.com/tipsandtricks/microsoftexcel-macro-tutorial.htm

Oefening 1 • Maak een nieuw werkboek aan les1_oef1_oplossing.xls • Voer de stappen op de volgende slide uit • Bestudeer de macro – – – – – –

Hoe wordt commentaar aangeduid? Wat betekent: Application.CutCopyMode = False? Wordt er enkel het Font en het Pattern gewijzigd? Welke regels zouden er weg mogen? In welke gevallen zou je deze laten staan? Welke instellingen worden gemaakt met Selection.Interior? – Zorg dat je de With ... End With structuur begrijpt. – Pas de code aan zodat je een With in een With gebruikt: With ... With ... End With End With

Slide op basis van cursus “Het gebruik van VBA in Ms Excel” van Theo Peek

Oefening 1 • • • • • • • • • • • • • • •

Zet tien willekeurige waarden in de cellen A1:A10, bijvoorbeeld de getallen 1, ..., 10; Start de Macro recorder met Tools > Macro > Record New Macro, geef de macro de naam ErgSimpel en Bevestig met OK; In beeld verschijnt de Stop Recording Toolbar, zie Figuur 2.1; Figuur 2.1: Stop Recording Toolbar. Selecteer de cellen A1:A10; Copy deze (Ctrl-C); Selecteer cel C1; Paste (Ctrl-V); De selectie is nu C1:C10. Kies Format Cells (Ctrl-1) en kies op het tabblad Font een blauwe kleur uit voor de letters; Kies het tabblad Patterns en kies een gele kleur uit als celachtergrond; Klik OK; Selecteer cel A1; Stop de macro recorder door op de Stopknop op de Stop Recording Toolbar te klikken; Start de Visual Basic Editor (VBE) (ALT-F11).

Slide op basis van cursus “Het gebruik van VBA in Ms Excel” van Theo Peek



Het object Range • Range = bereik van cellen – – – –

Eén cel Rij of kolom van cellen Tabel Willekeurige selectie van cellen

• Cel is basissteen van Ms Excel – Data, draaitabellen, grafieken, ...

• Ranges stel je in met Set

Slide op basis van cursus “Programmeren in Excel 2007 VBA” van Peter Demasure

Het object Range • Objectstructuur: – Een Range zit in een Worksheet dat in een Workbook zit: • Range ∈ Worksheet ∈ Workbook

Workbook

Range van 1 cel Worksheet

Het object Range • Voorbeeld: Range("A1") = 50

beter! Workbooks("Kalender.xls").Worksheets("Januari").Range("A1") = 50

nog beter!

Dim WB As Workbook Dim WS As WorkSheet Dim rngCel As Range Set WB = ActiveWorkbook Set WS = ActiveSheet Set rngCel = WS.Range("A1") rngCel = 50 perfect!


Het object Range • Voorbeeld: Dim WB As Workbook Dim WS As WorkSheet Dim rngCel As Range Set WB = ActiveWorkbook Set WS = ActiveSheet Set rngCel = WS.Range("A1") rngCel = 50 Dim WB As Workbook perfect! Dim WS As WorkSheet Dim rngCel As Range Set WB = Workbooks("Kalender.xls") Set WS = WB.Worksheets("Januari") Set rngCel = WS.Range("A1") rngCel = 50 Slide op basis van cursus “Programmeren in Excel 2007 VBA” van Peter Demasure

Het object Range • Uitleg bij voorbeeld: – Standaard verwijst Range(“A1”) naar de cel A1 van het toevallig actieve werkblad in het toevallig actieve werkboek • Niet handig!

– Daarom: • Altijd Workbook specifiëren • Altijd Worksheet specifiëren

Het object Range • For Each ... Next: – Met VBA kan je collecties eenvoudig overlopen – Bijvoorbeeld: collectie type Variant For Each cell in ActiveSheet.Cells ‘... Next



Range > Verwijzen naar het juiste cellenbereik • Verwijzen naar vast bereik via celadres – Range( celadres ) – Rows( celadres ) – Columns( celadres )


Range > Verwijzen naar het juiste cellenbereik Het cijfermatig resultaat in cel C1

Range("A1") = 5 Range("B1") = 3 Range("C1")=Range("A1")+Range("B1")

De formule in cel C1 (eerste mogelijkheid) De formule in cel C1 (tweede mogelijkheid)

Range("C1")= "=A1 + B1" Range("C1").Formula= "=Sum(A1:B1)"

Vaste tabel van cel B4 tot en met cel E20

Range("B4:E20") of Range("B4","E20")

Vijfde kolom

Columns("E") of Columns("E:E") of Range("E3").EntireColumn

Vijfde rij

Rows("5") of Rows("5:5") of Range("F5").EntireRow

Kolom vijf tot acht

Columns("E:H")

Rij vijf tot acht

Rows("5:8")

Cellen B4 en E20, los van elkaar

Range("B4,E20")

Kolommen vijf en acht, los van elkaar

Range("E:E,H:H")

Rij vijf en acht los van elkaar

Range("5:5,8:8")


Range > Verwijzen naar het juiste cellenbereik • Verwijzen naar dynamisch bereik via celadres – Getallen zijn makkelijker te manipuleren dan letters – Verwijzen naar cel via: Cells( rij, kolom ) – Bijvoorbeeld: • Vast: • Dynamisch:

Range( “C5” ) Cells( 5, 3 )

– Alle cellen binnen een bereik overlopen: • Cells( index )


Range > Verwijzen naar het juiste cellenbereik De cel met adres D5

Cells(5,4)

Dynamische celverwijzing

Cells(lngRij, lngKolom)

Tabel opgemaakt in VBA met variabel aantal cellen en vaste bovenhoek

Range(Cells(1,1), Cells(lngRij,lngKolom)) of Cells(1,1).Resize(lngRij,lngKolom)

Tabel opgemaakt in VBA met variabel aantal cellen en vlottende bovenhoek

Cells(lngRs,lngKs).Resize(lngRij,lngKolom)

Dynamische tabel opgemaakt in Excel met vaste linkerbovenhoek en met een dynamische linkerbovenhoek. CurrentRegion verwijst naar het gebied dat begrensd is door lege rijen en lege kolommen.

Range("A1").CurrentRegion Cells(lngRij, lngKolom).CurrentRegion

Vijfde kolom Dynamische kolom

Columns(5) of Cells(lngRij,5).EntireColumn Columns (lngIndex) of Cells(lngIndex).EntireColumn

Vijfde rij Dynamische rij

Rows(5) of Cells(5,lngKolom).EntireRow Rows(lngIndex) of Cells(lngRij,lngKolom).EntireRow

Kolom vijf tot acht

Columns(5).Resize(0,3)

Rij vijf tot acht

Rows(5).Resize(3,0)

De veldnamenrij van een dynamische tabel beginnend in de linkerbovenhoek van het werkblad

Range("A1").CurrentRegion.Rows(1)


Range > Verwijzen naar het juiste cellenbereik • Cellen en tabellen – Vast bereik: Set rngTabel = WS.Range("A1:E20")

– Dynamisch bereik Set rngTabel = WS.Cells(1,1).Resize(lngRij, lngKolom)

– Dynamisch in Excel Set rngTabel = WS.Range("A1").CurrentRegion


Range > Verwijzen naar het juiste cellenbereik • Cellen en tabellen – Dynamisch in Excel Dim rngTabel as Range Set rngTabel = WS.Range(“C6").CurrentRegion Range(“C6”)

rngTabel

Range > Verwijzen naar het juiste cellenbereik De eerste cel (linkerbovenhoek) van een werkblad (dit is steeds cel A1) Set rngCel = WS.Cells(1) De eerste cel (linkerbovenhoek) van een tabel (dit kan een der welke cel zijn!) Set rngCel = rngTabel.Cells(1) De laatste cel van een tabel Set rngCel = rngTabel.Cells(rngTabel.Cells.Count) De laatste kolom van een tabel Set rngCel = rngTabel.Columns(rngTabel.Columns.Count) De eerste rij onder de tabel (waar bijv. de som terecht komt) Set rngRij = rngTabel.Rows(rngTabel.Rows.Count).Offset(1) Het datablok van de tabel rngTabel.Resize(rngTabel.Rows.Count - 1).Offset(1) De eerste kolom van datablok van de tabel rngTabel.Resize(rngTabel.Rows.Count - 1).Offset(1).Columns(1) Een kolom toevoegen van de tabel rngTabel.Resize(,rngTabel.Columns.Count + 1)


Oefening 2 • Open opgave les1_oef2_opgave.xls • Maak een functie die de faculteit berekent – Tip: Google: wiki faculteit

• Input getal: – Mag niet hoger 15 zijn

• Output: – Kolom B (vanaf B3) bevat de faculteit iteratie – Kolom C (vanaf C3) bevat de n-de faculteit

• Gebruik named ranges en niet B1 en B3 – Tip: http://www.cpearson.com/excel/DefinedNames.aspx

• Koppel de macro aan een knop – Tip: http://www.mrexcel.com/tip068.shtml

Oefening 2 • Oplossing:

• Uitbreiding: – Bereken eerst de faculteit van 15 en dan van 5 – Wat gebeurt er (niet)? – Los dit probleem op

Oefening 3 • Open opgave les1_oef3_opgave.xls • Bestudeer de oplossing – Gebruik als hulp de volgende slide

• Voeg onderaan in de procedure procMain() code toe: – In een nieuwe kolom rechts van de tabel laat je de rijtotalen verschijnen onder de naam JAARTOTAAL. – Pas tevens de opmaak aan: • Alle totalen in het vet. • De cijfers in duizendtalnotatie. • De titels geblokkeerd zodat de totalen altijd bovenaan blijven


Oefening 3 De waarde van een cel

rngCel = 5 of rngCel = "Overzicht" rngCel.Value = 5 of rngCel.Value = "Overzicht"

Formule in een Cel steeds in het Engels Steeds met aanhalingsteken en gelijkheidsteken

rrgCel.Formula = "=Sum(A:A)"

Het lettertype van een cel

rngCel.Font.Name = "Arial"

De kleur van het lettertype

rngCel.Font.Color = vbWhite

De kleur van de opvulling

rngCel.Interior.Color = vbBlue

De veldnamenrij (kop van de tabel)

rngTabel.Rows(1) = _ Array("Nr", "Naam", "Voornaam", "Bedrag")

Een tabel benoemen

rgnTabel.Name = "Database"

Range rngBron kopiëren naar rngDoel rngDoel bestaat steeds uit één cel

rngBron.Copy Destination:= rngDoel of rngBron.Copy rngDoel

Een tabel verwijderen

rgnTabel.Delete

De volledige rij van een cel verwijderen

rngCel.EntireRow.Delete

Een volledige rij van een cel verbergen

rngCel.EntireRow.Hidden =True

De volledige rij van een cel verwijderen

rngCel.EntireRow.Delete

Het adres van een cel in relatieve termen Het adres van een cel in absolute termen

strAdres = rngCel.Address(False,False) strAdres = rngCel.Address

Alle cellen binnen een werkblad wissen

Cells.Clear

De getalnotatie (Formaat is Angelsaksisch)

Range.NumberFormat = "#,###"


Oefening 3 • Oplossing:




Range > Opvullen & bewerken • Arrays kan je in één keer in een bereik steken – Bijvoorbeeld arrMaanden() = Array("Januari", "Februari", ..., "December") ActiveSheet.Cells(1).Resize(, UBound(arrMaanden)) = arrMaanden


Range > Opvullen & bewerken • Omzetten naar verticale tabel – Bijvoorbeeld arrMaanden() = Array("Januari", "Februari", ..., "December") WS.Cells(1).Resize(UBound(arrMaanden)) = Application.WorksheetFunction.Transpose(arrMaanden)


Oefening 4a • Maak een nieuw werkboek aan: les1_oef4_oplossing.xls • Maak onderstaand Lotto formulier aan • Gebruik een aparte, herbruikbare procedure: – MaakLottoFormulier() – Private toegang – Parameters: startcel, aantal kolommen en aantal rijen – Deze procedure roep je op via een publieke procedure procMain()

• Oplossing:

Oefening 4b • Zorg ervoor dat de gebruiker via een knop getallen kan aankruisen • Maak hiervoor volgende procedures aan: – public VoerGetalIn() toont de InputBox – private KruisAan() kruist het getal aan • Oplossing:

Oefening 4c • Uitbreiding: – Zorg dat je procedure MaakLottoFormulier() meerdere formulieren kan aanmaken – Wanneer je nu op de knop “Kies getal” klikt, kan je ook het formulier kiezen

Oefening 4c • Oplossing:

Range > Opvullen & bewerken • Opmerkingen bij de oplossing: – Automatisch opvullen van groot bereik met For lus is niet efficiënt – Beter: formule gebruiken • Zie les1_oef3d_oplossing.xls 6 kolommen kolomindex

formule = (rij-index * aantal_kolommen) + kolomindex Bijvoorbeeld: (1 * 6) + 1 = 7

rij-index

Opgelet: rekening houden met offset: je start niet altijd op rij 1, kolom 1!


Range > Opvullen & bewerken • Opmerkingen bij de oplossing: – Code herbruikbaar maken door ze in aparte procedures te steken – Parameters: • Optionele: Private Sub test( param1 as Long, Optional param2 as Variant)

• Controleren of meegegeven is: If ( IsMissing(param2) ) Then param2 = “...” End If


Oefening 4d • Ga verder met je oplossing van oefening 4a/b/c • Zorg dat je de getallen niet opvult via een For lus, maar via een formule – Tip: start je tabel altijd in cel A1, dat rekent makkelijker

• Als dit lukt, zorg dan dat de eindgebruiker de formule niet meer kan zien, als hij in de cel staat – Tip: Paste Special

Programmeren in Excel VBA – les 2 • • • • •

Korte herhaling les 1 Range > Rijen verwijderen Werkmappen en werkbladen Het object Application Event macro's

Bedankt voor jullie aandacht!

09

Recommend Documents