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

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

Herhaling les 1

• ToDo



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: les2_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: les2_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



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 uit één werkblad naar een ander verwijzen: ‘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: les2_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 • Open werkboek: les2_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 • Oplossing:

Oefening 4 • Open werkboek: les2_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: les2_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

Programmeren in Excel VBA – les 3 • Korte herhaling les 1 en 2 • ToDo

Bedankt voor jullie aandacht!

09

Recommend Documents