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

Programmeren in Excel VBA beginners – les 3

• Herhaling les 1 + 2 • Range > Rijen verwijderen • Programmeren in VBA – Functies en subroutines

• • • • •

User Defined Functions Werkmappen en werkbladen Het object Application Event macro's Excel 2003 vs. 2007

Herhaling les 1 + 2 • Herhaling les 1 – 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

Herhaling les 1 + 2 • Herhaling les 2: – Besluitvormingsstructuren: • If … Then … End If • If … Then … Else … End If • If … Then … ElsIf … Else … End If 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

Herhaling les 1 + 2 • Herhaling les 2: – Besluitvormingsstructuren: • Select Case Dim iUur as Integer iUur = InputBox("Welk uur is het?") Select Case iUur Case 8 MsgBox "Goedemorgen!" Case 12 MsgBox "Goedemiddag!" Case 16 MsgBox "Goede namiddag!" Case Else MsgBox "Hallo!" End Select

Herhaling les 1 + 2 • Herhaling les 2: – Lusstructuren: For iTeller = 0 To 5 ... Loop

For iTeller = 0 To 5 Step 1 ... Loop

iTeller = 0 Do While ( iTeller < 6) ... iTeller = iTeller + 1 Loop

iTeller = 0 Do ... iTeller = iTeller + 1 Loop While ( iTeller < 6)

iTeller = 0 Do Until ( iTeller >= 6) ... iTeller = iTeller + 1 Loop

Herhaling les 1 + 2 • Objectstructuur – het object Range: – Range = bereik van cellen – Een Range zit in een Worksheet dat in een Workbook zit: • Range ∈ Worksheet ∈ Workbook – Ranges stel je in met Set – Ranges overloop je met For Each ... Next – Verwijzingen kunnen statisch of dynamisch zijn: • Statisch voorbeeld: • Dynamisch voorbeeld:

Range("A1") Cells(3,4)

Herhaling les 1 + 2 • Objectstructuur – het object Range: – 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

– Verwijzen naar een Range: Dim WB As Workbook 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


• Herhaling les 1 • Range > Rijen verwijderen • Programmeren in VBA – Functies en subroutines

• • • • •


Range > Rijen verwijderen • For ... Each is handig, ... maar niet voor het verwijderen

van cellen

For Each rngCel In rngKolom.Cells If UCase(rngCel) = "JAN" Then rngCel.EntireRow.Delete End If Next rngCel

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

Range > Rijen verwijderen • Reden: – Wanneer je een hoger gelegen rij verwijdert, springen alle onderliggende rijen steeds een hoger – Hierdoor ontsnappen sommige rijen aan onze controle op “JAN” For Each rngCel In rngKolom.Cells If (UCase(rngCel) = "JAN“) Then rngCel.EntireRow.Delete End If Next rngCel

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

Oefening 1a • Maak een werkboek aan: les3_oef1a_oplossing.xls • Los het probleem van de vorige slide op door de rijen van onder naar boven te overlopen • Tip: – Via de knop “Maak test sheet aan” op sheet “BRON_DATA” kan je telkens een volledig nieuwe versie van sheet “Verwijder_Jan_Fout” aanmaken

Oefening 1b • Maak een werkboek aan: les3_oef1b_oplossing.xls • Uitbreiding: – Verwijder enkel de rijen met “Jan” als naam én de datum in oktober

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



• • • • •


Programmeren in VBA > Functies en subroutines •

Procedure – = stuk code omsloten door •

Sub … End Sub

•

Function … End Function

of

– Procedures steken altijd in modules


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


Sub ... End Sub – – – –

Subroutine Géén terugkeerwaarde Uitbreken met: Exit Sub Voorbeeld:

naam

begin

publieke toegang

parameters

Public Sub RijVooruit( ) ‘... If( ... ) Then Exit Sub ‘... End Sub einde

uitbreken


Function ... End Function – Functie – Met één terugkeerwaarde • •

Type te specifiëren in functiebeschrijving Toekenning terugkeer- of functiewaarde door:

= <waarde> – Uitbreken met: Exit Function


Function … End Function: – Voorbeeld: naam parameters begin

publieke toegang

terugkeertype

Public Function GeefSnelheid( ) As Double ‘... If( ... ) Then Exit Function ‘...

uitbreken

GeefSnelheid = 25.3 End Function einde

terugkeerwaarde toekennen

Programmeren in VBA > Functies en subroutines

•

Parameters – Soorten: geen, optionele en ‘normale’ – Bij zowel subroutines als functies – Type zelf mee te geven •

Als ontbreekt Variant

– ~ variabelen: •

Moeten gedeclareerd worden – Unieke naam met beperkingen – Afspraak: CamelCase en Hungarian notation

• • •

Scope = procedure zelf (na procedure is geheugen vrij) Dim keyword niet nodig Geen constanten


Procedure – Parameters – voorbeelden: •

Geen parameters: Sub Demo()

•

Wel, met type: Sub Demo( s1 As String, s2 As String )

•

Wel, zonder type: – Beide worden gedeclareerd als Variant

– Sub Demo( s1, s2 )



Optionele, zonder standaardwaarde – Als niet meegegeven standaardwaarde van type wordt gebruikt Bv. Integer 0

– Sub Demo( Optional s1 As String ) •

Optionele, mét standaardwaarde – Als niet meegegeven standaardwaarde van declaratie wordt gebruikt

– Sub Demo( Optional s1 As String = “blah” )



MsgBox functie

MsgBox "Dit is het bericht", , "Titel"

MsgBox "Dit is het bericht", vbCritical + vbOKCancel, "Titel"


Parameters – Doorgeven: •

Voorbeeld:

Public Sub RijVooruit( iAfstand As Integer) ‘... End Sub

Call RijVooruit( 20 ) Call RijVooruit() Call RijVooruit( 20, 10 )


•

Oproepen – Verschillende manieren: • • • • •

Bv. BerekenAfstand Bv. BerekenAfstand() Bv. Call BerekenAfstand Bv. Call BerekenAfstand() Bv. Call ZetSnelheid( 20 )

– Met Call: • • •

Duidelijker dat je procedure oproept Haakjes () niet verplicht wanneer parameters ontbreken Enkel bruikbaar als je géén terugkeerwaarde verwacht!


Oproepen – Als je een terugkeerwaarde hebt: • • •

Haakjes gebruiken! Call niet gebruiken bij oproepen Bv. waarde = InputBox( “Geef getal” )

– Zonder terugkeerwaarde: • • •

Haakjes niet verplicht Bv. MsgBox “Geef getal” Bv. Call MsgBox("Dit is het bericht", , "Titel")

– Optionele parameters weglaten •

Bv. MsgBox “Geef getal”, , “Titel”


•

Oproepen – Ook mogelijk om de parameters zelf te specifiëren – Voorbeeld: MsgBox prompt:="Dit is het bericht", _ title:="Titel" het “nieuwe regel” teken

– Waarom • •

Leesbaarder in sommige gevallen Geen komma’s nodig bij het ontbreken van optionele parameters Bv. MsgBox “Bericht”, , “Titel”


Parameters - geavanceerd –

ByVal • •

–

ByRef • •

–

Optional. Specifies that the procedure can modify the underlying variable element in the calling code the same way the calling code itself can. Bv. Public Sub Demo( ByVal sParam1 As String )

Optional. Specifies that the procedure cannot replace or reassign the variable element underlying the corresponding argument in the calling code. Bv. Public Sub Demo( ByRef sParam1 As String )

ParamArray •

•

Optional. Specifies that the last parameter in the parameter list is an optional array of elements of the specified data type. This lets the calling code pass an arbitrary number of arguments to the procedure. Bv. Public Sub Demo( ParamArray saParams() As Variant ) Call Demo( "a", "b", 5, Date )

http://msdn.microsoft.com/en-us/library/cbs7z96t.aspx


•

Samenvatting – – – – –

Procedures kunnen parameters hebben Procedures hebben een scope Er zijn twee soorten procedures: Sub en Function Functies hebben een terugkeerwaarde Procedure parameters: • •

Hebben een type Kunnen optioneel zijn

– Procedures kunnen op verschillende manieren opgeroepen worden



• • • • •


User Defined Functions

• • •

Functies die je zélf definieert Beschikbaar als functie in elke cel Voordelen: – – – – –

•

Je kan een complexe of eigen (wiskundige) functie maken. Vereenvoudigen van “mega formules” Diagnostiek: controleren van cel formaat Tekstmanipulatie Geavanceerde array en matrix functies

Opslaan in module


•

Beperkingen: – Niet “opneembaar” zoals een macro – Formaat of de structuur van een cel/werkblad niet veranderbaar – Moeten gebruikt worden zoals formules, niet zoals macro’s – Trager – Bij gebruik krijg je altijd de beveiligingswaarschuwing als je de XLS opent

•

Bron: http://www.vertex42.com/ExcelArticles/user-defined-functions.html


•

Voorbeeld:



• • • • •


Werkmappen en werkbladen • Werkmap – Aka. Workbook – Huidige werkboek: ActiveWorkbook

– Werkboek waarin je VBA procedure in uitgevoerd wordt: ThisWorkbook

– Als je met meerdere werkboeken werkt, een werkboek activeren: ThisWorkbook.Activate Windows(“boek.xls”).Activate

Werkmappen en werkbladen • Werkmap – Opslaan: Workbook.Save Workbook.SaveAs

– Sluiten: Workbook.Quit

– Sluiten zonder op te slaan: Workbook.Saved = True Workbook.Quit

Werkmappen en werkbladen • Werkblad – Aka. Worksheet – Huidige werkblad: ActiveSheet

– Werkblad waarin je VBA procedure in uitgevoerd wordt: ThisWorkbook.ActiveSheet

– Als je met meerdere werkboeken werkt, een werkboek activeren: ThisWorkbook.Sheets(“blad1”).Activate Windows(“boek.xls”).Sheets(“blad1”).Activate

Werkmappen en werkbladen • Werkblad – Werkblad selecteren: Worksheets(“blad2”).Select

– Je kan niet van op één werkblad een cel op een andere selecteren: ‘Stel we zitten op sheet “Opgave” Sheets("Results").Range("A1").Select

– Beter: ‘Stel we zitten op sheet “Opgave” Sheets("Results").Select Range("A1").Select

Werkmappen en werkbladen • Werkblad – Werkblad verbergen: Worksheets(“blad2”).Visible = False

– Verborgen werkblad selecteren: Worksheets(“blad2”).Visible = True Worksheets(“blad2”).Select //... Worksheets(“blad2”).Visible = False

Werkmappen en werkbladen • Werkblad – Werkblad écht verbergen: Worksheets(“blad2”).Visible = xlSheetVeryHidden

– Wordt enkel terug zichtbaar na volgende statement: Worksheets(“blad2”).Visible = True

Oefening 2 • Open werkboek: les3_oef2_opgave.xls • Maak een functie CloseOtherWorkbooks() die: – Alle geopende werkboeken sluit, behalve de huidige – Een optionele parameter bOpslaan heeft • Deze parameter bepaald of je de andere werkboeken opslaat of niet • Standaard waarde = True

– Tip: bekijk het Application object – Maak een procedure procMain() die deze functie oproept


Oefening 2 • Hoe testen? – Maak een nieuwe werkboek aan zonder wijzigingen – Maak een nieuwe werkboek aan met wijzigingen – Voer de procedure procMain() uit

• Vragen – Welk gedrag neem je waar voor de verschillende nieuwe workbooks? – Wat gebeurt er als je op Cancel drukt bij opslaan? – Werkt de optionele boolean?


Werkmappen en werkbladen • Optionele parameters: – Optionele booleans zijn altijd False! Private Sub CloseOtherWorkbooks(Optional bOpslaan As Boolean) If ( IsMissing(bOpslaan) ) Then bOpslaan = True End If '... End Sub

bOpslaan krijgt standaard de waarde False, dus methode IsMissing(bOpslaan) geeft ook altijd False terug, want bOpslaan is niet meer leeg!

Werkmappen en werkbladen • Optionele parameters: – Oplossing: • Ofwel als type Variant meegeven Private Sub CloseOtherWorkbooks(Optional bOpslaan As Variant)

• Ofwel als type Boolean meegeven, maar met standaardwaarde in functiedefinitie Private Sub CloseOtherWorkbooks(Optional bOpslaan As Boolean = True)

– Meer info: http://allenbrowne.com/ser-30.html

Oefening 3 (huiswerk) • Open werkboek: les3_oef3_opgave.xls • Maak een functie CreateWorkbook() die: – Een nieuw werkboek aanmaakt – De template sheet kopieert + hernoemt naar de maand (en dit voor elke maand van het jaar) – De titel van de gekopieerde sheet wijzigt

• De maanden steek je in een Array – Tip: ook arrays kan je overlopen met For Each ... Next – http://www.cpearson.com/excel/VBAArrays.htm

Oefening 3 (huiswerk) • Oplossing:

Oefening 4 • Open werkboek: les3_oef4_opgave.xls • Maak een private functie CheckIfWorksheetExists() die: – Controleert in de huidige werkboek of sheet met een bepaalde naam al bestaat – Een ja/nee waarde (boolean) als resultaat teruggeeft – Parameters: WB en sheetnaam – Maak een procedure procMain() die deze functie oproept

• Als de sheet nog niet bestaat: – Maak een nieuwe aan met de gekozen naam – De nieuwe sheet komt helemaal achteraan in de werkboek



• • • • •


Het object Application • "Hoogste" object in Excel – Bv. Application.WorkBooks(2).WorkSheest(3).Cell(2.1).select

• Applicatie afsluiten: Application.Quit

• Applicatie afsluiten zonder opmerking van opslaan: ActiveWorkbook.Saved = True Application.Quit

Het object Application • Voortgangsacties van macro verbergen – Bv. opvullen van tabel bij exporteren uit HP Service Center Application.ScreenUpdating = False

– Voordeel: geen zichtbaar geknipper voor de eindgebruiker – Nadeel: programma lijkt te hangen tijdens uitvoering – Demo: les2_demo_screenupdating.xls



• • • • •


Event macro's • Procedures gekoppeld aan gebeurtenissen in Excel – Werkboek • • • •

Nieuw werkboek Werkboek openen Werkboek opslaan ...

– Werkblad: • • • •

Activeren Aanklikken Wijzigen ...

– Alle events: http://www.mvps.org/dmcritchie/excel/event.htm

Event macro's • Werkboek Private Sub Workbook_BeforeClose(Cancel As Boolean) Private Sub Workbook_BeforePrint(Cancel As Boolean) Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Private Sub Workbook_NewSheet(ByVal Sh As Object) Private Sub Workbook_Open() Private Sub Workbook_SheetActivate(ByVal Sh As Object) Private Sub Workbook_SheetCalculate(ByVal Sh As Object) Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Event macro's • Werkblad Private Sub Worksheet_Activate() Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Private Sub Worksheet_Calculate() Private Sub Worksheet_Change(ByVal Target As Range) Private Sub Worksheet_Deactivate() Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Oefening 5 • Open werkboek: les3_oef5_opgave.xls • Zorg ervoor dat: – Elke keer als je het werkboek opent, • De sheet "LEGENDE" geselecteerd wordt • Een welkomstbericht getoond wordt:

– Bij opslaan op tabblad "log" een loglijn aangevuld wordt • Deze loglijn mag maar éénmaal per minuut geschreven worden • Tip: format()

Oefening 5 • Zorg ervoor dat: – Bij sluiten een afscheidsbericht getoond wordt – Als de gebruiker op "Nee" klikt, wordt het werkboek niet afgesloten • Tip: Cancel parameter instellen



• • • • •


Excel 2003 vs. 2007 • Overview: – http://technet.microsoft.com/en-us/library/cc179188(office.12).aspx

• Demo – http://mshelp.be/wat-zijn-de-verschillen-tussen-office-2003-en-office-200776.htm

Excel 2003 vs. 2007 • Verschillen voor VBA: – The previously problematic FileSearch has been dropped – Dealing with shapes requires care • See Shapes and VBA code in Excel 2007 • http://www.rondebruin.nl/shape.htm

– Toolbar customisation changes with the advent of the Ribbon • See Change the ribbon in Excel 2007 • http://www.rondebruin.nl/ribbon.htm

– Graphics handling is reportedly very slow • Perform a Google search for: graphics 2007

– Bron: http://www.eggheadcafe.com/software/aspnet/32278035/excel-2007-vbaversus-excel-2003.aspx

Excel 2003 vs. 2007 • The easiest way to find this information is to follow these steps in Excel 2007: – – – –

Press Alt+F11 to display the VBA Editor. Press F1. The VBA Help system starts. Click What's New. Click Object Model Changes Since Microsoft Office 2003.

– Bron: http://excel.tips.net/Pages/T006767_Excel_2007_VBA_Differences.html

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

Bedankt voor jullie aandacht!

09

Recommend Documents