09

Programmeren in Excel VBA beginners 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

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 beginners • • • • • • • • • • •

Visual Basic Intro De Visual Basic Editor (VBE) Debuggen in VBA Macro’s Programmeren in VBA Het object Range User Defined Functions Werkmappen en werkbladen Het object Application Event macro's Excel 2003 vs. 2007

• • • • • •

Variabelen Constanten Operatoren Selecties en iteraties Arrays Functies en subroutines

Programmeren in Excel VBA beginners – les 1

• • • • •

Visual Basic Intro De Visual Basic Editor (VBE) Debuggen in VBA Macro’s Programmeren in VBA – – – –

Variabelen Constanten Operatoren Selecties en iteraties

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


• • • • •



Programmeren in VBA > Variabelen •

Variabelen – Gegevens opslaan in geheugen – Slechts geldig tijdens uitvoeren van het programma – Verschillende gegevenstypes mogelijk: •

Boolean

•

Integer

•

Long

•

Double

•

Date

•

Object

•

String

•

Variant

•

...

true or false 16 bits -> van - 2^15 tot 2^15 -1 32 bits -> van - 2^31 tot 2^31 -1 64 bits -> van - 2^63 tot 2^63 -1 bestemd voor datums verwijzing naar object (bv. WorkSheet) tekengegevens (bv. "appel") kan alle types bevatten


Variabelen – Elke variabele heeft een unieke naam •

Conventies: – – – –

•

Eerste teken moet letter zijn Spaties, punten en komma's zijn niet toegelaten Leestekens #, %, !, &, $ zijn niet toegelaten Max lengte = 254 karakters

CamelCase + Hungarian notation: – Bv. strElkWoordEenNieuweHoofdletter

•

Voorbeeld: – strNaamKlant – iAantalJaren


Declaraties – Compiler informeren van bestaan: naam + gegevenstype – Keywoord = Dim •

Dim As

•

Bv. Dim strMijnNaam As String

– Niet verplicht om variabelen an sich te declareren • •

Veroorzaakt errors bij ontbreken Afdwingen door Option Explicit boven elke code te zetten – Eénmalig bovenaan in het bestand dat de code bevat

•

Zie ook oefening 1


Declaraties – Niet verplicht om type te declareren •

In plaats van: Dim strMijnNaam As String

schrijven: Dim strMijnNaam

•

Gevolgen: – Wordt achterliggend als Variant gedeclareerd: Dim strMijnNaam As Variant – Variant kan álle types bevatten

– Nadelen: » Trager » Meer geheugengebruik (te vermijden)


Declaraties – Afspraken: •

Naamgeving volgens combinatie CamelCase en Hungarian notation – Bv. strElkWoordEenNieuweHoofdletter

• •

Altijd Option Explicit bovenaan elke code bestand Altijd een gegevenstype declareren – Bv. Dim strMijnNaam As String


Oefening 2: – Pas de Workbook_BeforeSave methode aan zodat: • • •

Aan de gebruiker een eerste getal gevraagd wordt. Aan de gebruiker een tweede getal gevraagd wordt. Aan de gebruiker de som getoond wordt.

– Tips: • •

Een waarde aan de gebruiker kan je opvragen met de functie InputBox Om een bericht te tonen: zie oefening 1


Oefening 2:


Oefening 2 - uitbreiding: – Pas oefening 2 aan zodat: •

Wanneer je een getal ingeeft het programma een foutmelding toont en stopt

– Tips: •

De Information module in de VBA bibliotheek

Programmeren in VBA > Variabelen • Varia: – String concatenatie: • Bv. “Mijnheer

“ & strVoorNaam & strNaam

– Waardes tonen aan de gebruiker: • Bv. MsgBox

“Dit is het bericht”, , “Titel”

– Waardes vragen aan de gebruiker: • Bv. strWaarde

= InputBox(“Geef iets”, “Titel” )

– Meerdere declaraties op één regel: • Bv. Dim

iGetal1 As Integer, iGetal2 As Integer

– Commentaar met een enkele quote: • Bv.

‘dit is commentaar en wordt genegeerd


Declaratie – scope: –

Public: • • •

–

Declaratie op module niveau Beschikbaar binnen alle procedures in het project Bv. Public strNaam as String

Private: • • •

Declaratie op module niveau Enkel beschikbaar binnen alle procedures in de module Bv. Private strNaam as String


• • • • •


Variabelen Constanten Operatoren Selectiestructuren: If ... Then ... ElseIf ... End If

Programmeren in VBA > Constanten •

Declaratie – scope: –

Const: • • • •

Constante Waarde is niet wijzigbaar Afspraak: naam in HOOFDLETTERS Bv. Const NAAM as String = "karel" Voorbeeld:

Private Sub Demo() Const NAAM As String = "karel"

Declaratie als constante

MsgBox NAAM MessageBox toont "karel" NAAM = "nijs" End Sub


• • • • •



Programmeren in VBA > Operatoren • Expressies – = waarde of groep waarden die geëvalueerd wordt – Resultaat = waarde van de expressie – Bv. • Waarde = 4 + 5

– Kunnen opgebouwd worden uit: • • • •

Constanten, variabelen Operatoren Arrays en hun elementen (zie verder) Functies

Programmeren in VBA > Operatoren • Expressies – Kunnen opgebouwd worden uit: • Constanten, variabelen: – Bv. dPrijsInclusief = iPrijsExclusief * BTW

• Operatoren (zie volgende slide): – Rekenkundige, vergelijkings, logische en samenvoegings

• Arrays en hun elementen (zie later): – Bv. dPrijsInclusief = aPrijzenLijst(1) * BTW

• Functies: – Bv. sNaam = InputBox(“Geef je naam in:”)

Programmeren in VBA > Operatoren • Operatoren: – Vergelijking: waarde Bv. prijs – Soorten:

= operand operator operand = prijs

*

• Rekenkundig: – +, -, *, /, \, ^ en Mod – Bv. a + 5

• Vergelijking: – =, <, <=, >, >=, <>, is en like – Bv. If( 5 < prijs )

BTW

Programmeren in VBA > Operatoren • Operatoren: – Soorten: • Logisch: – And, Or, Not, Xor, Eqv en Imp – Bv. If( 5 < prijs

And

prijs < 10 )

• Samenvoeging: –& – Bv. MsgBox "Mijnheer " & naam


• • • • •



Programmeren in VBA > Selecties en iteraties • Niet bij elk scenario moet elke code uitgevoerd worden – Bv. als de prijs 0 is, moet je geen factuur sturen – Bv. als de prijs < 0 is, moet je een rode tekst tonen

• Sommige code moet meermaals achter elkaar uitgevoerd worden – Bv. het overlopen van alle orders op een factuur om het totaal te berekenen

Programmeren in VBA > Selecties en iteraties • Beslissen met besluitsvormingsstructuren – – – –

If ... Then ... End If If ... Then ... Else ... End If If ... Then ... ElseIf ... Else ... End If Select Case

• Code herhalen met lusstructuren – – – –

For ... Next For Each ... Next Do While ... Loop Do Until ... Loop

Programmeren in VBA > Selecties en iteraties • If ... Then ... End If – Conditie – Als ... dan ... – Bv. begin van de conditie

expressie moet valideren als "waar" -> true

If ( tegoed < 0 ) Then MsgBox "Gelieve te betalen!" End If einde van de conditie

wordt enkel uitgevoerd expressie voldaan

Programmeren in VBA > Selecties en iteraties • If ... Then ... Else ... End If – Probleem: we willen ook iets (een alternatief) laten uitvoeren als niet aan de voorwaarde voldaan is If ( tegoed < 0 ) Then MsgBox "Gelieve te betalen!" End If If ( tegoed > 0 ) Then MsgBox "Correcte betaler!" End If

– Nadelen: • Veel typwerk • Niet duidelijk dat code bij elkaar hoort

Programmeren in VBA > Selecties en iteraties • If ... Then ... Else ... End If – Conditie – Als ... dan ..., anders ... – Bv. expressie moet valideren als "waar" true If ( tegoed < 0 ) Then MsgBox "Gelieve te betalen!" begin van de andere optie

wordt enkel uitgevoerd expressie voldaan

Else MsgBox "Correcte betaler!" End If

wordt enkel uitgevoerd expressie niet voldaan

Programmeren in VBA > Selecties en iteraties • If ... Then ... ElseIf ... Else ... End If – Wat met meerdere (> 2) alternatieven? – Als ... dan ..., anders dan ..., ..., anders ... – Structuur: If ( voorwaarde1 = True ) Then ...

ElseIf ( voorwaarde2 = True ) Then ...

ElseIf ( voorwaarde3 = True ) Then ...

... Else ... End If

nog meer voorwaarden? geen enkele voorwaarde voldaan

Programmeren in VBA > Selecties en iteraties • If ... Then ... ElseIf ... Else ... End If – Bv.

If ( tijd < 12u ) Then MsgBox "Goedemorgen!" ElseIf ( tijd < 15u ) Then MsgBox "Goedemiddag!" ElseIf ( tijd < 18u ) Then MsgBox "Goede namiddag!" ElseIf ( tijd < 22u ) Then MsgBox "Goede avond!" Else MsgBox "Goedenacht!" End If

Programmeren in VBA > Selecties en

iteraties • Debuggen – = zoeken naar fouten in een programma – Starten door een break point te plaatsen

– Vanaf dan: F8 voor elke volgende stap

– In VBE: View > Toolbars > Debug

Programmeren in VBA > Selecties en iteraties • Oefening 3: – Maak een applicatie die: • • • •

Een eerste getal vraagt aan de gebruiker Een tweede getal vraagt aan de gebruiker Een constante variabele met waarde 55 heeft Volgende berekening uitvoert: – (getal1 + constant getal) modulo getal2

Programmeren in VBA > Selecties en iteraties • Oefening 1:

Bv. (10 + 55) % 7 = 2 Kleine afwijking

– Maak een applicatie die: • Volgend bericht toont aan de gebruiker: – Als het resultaat = 0: "(+) % = <modulo> Perfecte deling" – Als het resultaat < 3: "(+) % = <modulo> Kleine afwijking" – Als het resultaat < 5: "(+) % = <modulo> Grotere afwijking" – Alle andere gevallen: "<modulo> is een te grote afwijking!"

• Testdata: – – – –

5 en 3 10 en 7 12 en 7 841 en 711


• Korte herhaling les 1 • Programmeren in Excel VBA – Select Case – Lussen

Bedankt voor jullie aandacht!

09

Recommend Documents